Oracle 存储过程

一 存储过程的优点：
1 SQL存储过程执行起来比SQL命令文本快得多。
当一个SQL语句包含在存储过程中时，服务器不必每次执行它时都要分析和编译它。

2 调用存储过程，可以认为是一个三层结构。
程序易于维护。如果程序需要做某些改动，你只要改动存储过程即可

3 你可以在存储过程中利用Transact-SQL的强大功能。
一个SQL存储过程可以包含多个SQL语句。可以使用变量和条件。
这意味着你可以用存储过程建立非常复杂的查询，以非常复杂的方式更新数据库

4 安全，不会有SQL语句注入问题。

------------------------------

二 创建存储过程

1 语句格式：

create or replace procedure 过程名(var_name_1 in type,var_name_2 out type) as

变量1 数据类型 --声明变量(变量名 变量类型)

begin

--存储过程的执行体

end 过程名;

eg:
create or replace procedure test(workDate in Date) is
begin
--打印输出信息
 dbms_output.put_line('The input date is :' || to_date(workDate,'yyyy-MM-dd') );
end test;


2 变量赋值

变量名 ：= 值;

eg:
create or replace procedure test(workDate in Date) as
x number(4,2);
begin
x := 1;
end test;

3 判断语句

if 比较式 then begin 执行语句 end;end if;

eg:
create or replace procedure test(x in number) as
begin
    if x > 0 then
    begin
        x := x-1;
    end;
end if;
end test;

eg:
create or replace procedure test(num in number) as
begin
      if mod(num,400)=0 then
          leap:=true;
      elsif mod(num,100)=0 then
         leap:=false;
      elsif  mod(num,4)=0  then
          leap:=true;
      else
          leap:=false;
      end if;
end test;


4 case语句
create or replace procedure test(num in integer) as
begin
    case num
    when 1 then
    dbms_output.put_line('小于100');
    when 2 then
    dbms_output.put_line('大于100小于200');
    else
    dbms_output.put_line('大于200');
    end case;
end test;

运行：
set serveroutput on 这一句需要在SQLPLUS 下或者 PL/SQL DEV的command窗口下运行
eg:
set serveroutput on;
begin
test(2);
end;
    

5 for 循环

格式：
for ... in ... loop
--执行语句
end loop;

eg:循环遍历游标：
create or replace procedure test as
Cursor cursor is select name from tbl_student;
name varchar(20);
begin
    for name in cursor loop
        begin
            dbms_output.put_line(name);
        end;
    end Loop;

end test;

eg:循环遍历数组
create or replace procedure test(varArray in myPackage.TestArray) as
i number;
begin
--注意：oracle 数组遍历是从1开始，因为他是表，只能从第一个数据开始
    i :=1;
    for i in 1..varArray.count loop
        dbms_output.put_line(The No.||i||record in varArray is:||varArray(i));
    end loop;
end;
end test;

6 while 循环

格式：
while 条件语句 loop
    begin
    --语句
    end;
end loop;

eg：
create or replace procedure test(i in number) as
begin
    while i<10 loop
        begin
            --执行语句
            i:=i+1;
        end;
    end loop;

end test;

--还有一种直接循环
create or replace procedure test as
num integet;
begin
    num :=10;
    loop
        dbms_output.put_line(num);
        num := num+1;
        exit when num > 100;
    end loop;
end test;
    


7 数组

Oracle 没有数组的概念，数组其实是一张表（Table），每个元素就是表中的记录
使用数组时，可以使用Oracle已经定义好的数组，或根据需要自定义数组。

--使用Oracle自带的数组
x array; --使用时需要进行初始化
 
eg;
create or replace procedure test(y out array) as
x array;
begin
    x:=new array();
    y:=x;
end test;

--自定义数组类型

eg:
使用数组:
create or replace procedure test(varArray in myPackage.TestArray) as
...

自定义的数组
Public type declarations type info is record(name varchar(20),y number);

type TestArray is table of info index of binary_integer;
--此处声明了一个TestArray类型数组，其实他是一张存储info数据的一张表，表中有
两个字段:
name，y
index of binary_integer  --编制该table的索引，可以不写。上面呈现这样：
type TestArray is table of info;--那么需要初始化：
varArray myPackage.TestArray;
varArray := new myPackage.TestArray();
end TestArray;

eg:
create or replace package myPackage is
begin
    type TestArray is table of info index of binary_integer;
end myPackage;



8 游标的使用
一般用于遍历临时表的查询结果

(1)Cursor 型游标（不能用于参数传递）
使用场景：DML 和单行查询

create or replace procedure test is
cursor_1 Cursor is select stu_name from tbl_student where ....;--Cursor使用方式一
cursor_2 Cursor;
begin
select class_name into cursor_2 from tbl_class where ....;--Cursor使用方式二
--遍历游标
for name in cursor_1 loop
    begin
        dbms_output.put_line(name);
    end;
    end Loop;
end test;

(2)SYS_REFCURSOR型游标(Oracle 预先定义的游标，可以传递参数)
create or replace procedure test(rsCursor out SYS_REFCURSOR) is
cursor SYS_REFCURSOR;name varchar(20);
begin
    open cursor for select name from tbl_student where ...;--SYS_REFCURSOR只能通过open方法打开和赋值
    loop
        fetch cursor into name   --SYS_REFCURSOR只能通过fetch into 来打开和遍历
        exit when cursor %NOTFOUND;
        --SYS_REFCURSOR中三种状态属性：%NOTFOUND 未找到记录  %FOUND 找到记录 %ROWCOUNT 当前游标指向的行位置
        dbms_output.put_line(name);
    end loop;
    rsCursor := cursor;
end test;


游标属性：
%ROWCOUNT :返回到目前为止，已经从游标中取出的记录数量，也就是DML语句成功执行的数据行数；
%FOUND ：如果成功取到数据返回true，否则返回false;
%NOTFOUND :未找到记录;
%ISOPEN ：如果游标打开返回true，否则返回false;
%BULK_ROWCOUNT ：返回forall语句修改的记录数量;
%BULK_EXCEPTIONS ：返回forall语句修改记录时的异常信息.

下面是一个完整的显式游标操作实例：
一个这样的游标可以被多次open进行使用，显式游标是静态游标，它的作用域是全局的，
但也必须明白，静态游标也只有pl/sql代码才可以使用她。
eg；

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  -- declare a cursor
  CURSOR cur_chief IS
      SELECT first_name,
             last_name,
             department_name
      FROM employees e
      INNER JOIN departments d ON d.manager_id = e.employee_id;
  r_chief cur_chief%ROWTYPE;
BEGIN
  OPEN cur_chief;
  LOOP
    -- fetch information from cursor into record
    FETCH cur_chief INTO r_chief;
 
    EXIT WHEN cur_chief%NOTFOUND;
 
    -- print department - chief
    DBMS_OUTPUT.PUT_LINE(r_chief.department_name || ' - ' ||
                         r_chief.first_name || ',' ||
                         r_chief.last_name);
  END LOOP;
  -- close cursor cur_chief
  CLOSE cur_chief;
END;

解析：
%type与%rowtype区别：
%TYPE：属性的一种方式是在变量声明中使用。%TYPE是用于取得表或游标中的字段类型,便于维护。
DEALARE
V_first_name   cur_chief.first_name%TYPE;
V_last_name    cur_chief.last_name%TYPE;

%ROWTYPE
使用%ROWTYPE属性可以声明一个基于表或游标的行对象，也称为记录对象。
r_chief.department_name 就是去行记录对象中department_name字段下的记录。


----
9 存储过程异常

eg；
create or replace procedure test is
begin
    select name,age,gender from tbl_student where ....;
    exception
    when NO_DATA_FOUND then
    dbms_output.put_line('未找到记录');
    when TO_MANY_ROWS then
    dbms_output.put_line('返回值多于1行');
    when others then
    --异常处理
end test;
    
10 存储过程的若干问题

(1) 表名不能用as

(2) select 某一字段，后面必须加into

(3) 在使用select into 必须确保有记录，否则抛NO_DATA_FOUND异常
可以用select count(*) from 表名，查看记录是否存在

(4) 定义的参数名或变量名不能与字段名相同，否则会编译通过，但运行会报错

(5) 空值null处理

eg:
create or replace procedure test is
fcount number(8):=0;
begin
    select sum(vount) into fcount from tbl_A where bid='xxxx';
    --假如无记录，那么fcount 值为null，要加一个处理null语句
    if fount is null then
        fount := 0;
    end if;
end test;

--
11 oracle中的布尔型
create table tbool (bool char check (bool in ('N','Y'));
insert into tbool values ('N');
insert into tbool values ('Y');