ORACLE存储过程，循环语法和游标（转）

原作者 https://www.cnblogs.com/lgx5/p/11297563.html

1、定义
所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过
编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数
来调用并执行它，从而完成一个或一系列的数据库操作。

2、存储过程的创建
Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

（1）无参存储过程语法

1 2 3 4 5 6 7 8 9 10 11 12 13 14

create or replace procedure NoParPro as //声明  ; begin // 执行  ; exception//存储过程异常    when too_many_rows then    ***    when no_data_found then    ***    when others then    ***  ; end;

　　

（2）带参存储过程实例　

1 2 3 4 5 6 7 8 9

create or replace procedure queryempname(sfindno emp.empno%type)   as     sName emp.ename%type;     sjob emp.job%type;  begin         ....  exception         ....  end;

（3）带参数存储过程含赋值方式

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

create or replace procedure runbyparmeters        (isal in emp.sal%type,        sname out varchar,       sjob in out varchar)   as       icount number;   begin        select count(*) into icount from emp where sal>isal and job=sjob;        if icount=1 then          ....        else         ....       end if;  exception       when too_many_rows then       DBMS_OUTPUT.PUT_LINE('返回值多于1行');       when others then       DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');  end;

其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。

（4）存储过程中游标定义使用

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

as //定义(游标一个可以遍历的结果集)   CURSOR cur_1 IS     SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,           SUM(usd_amt)/10000 usd_amt_sn     FROM BGD_AREA_CM_M_BASE_T     WHERE ym >= vs_ym_sn_beg          AND ym <= vs_ym_sn_end     GROUP BY area_code,CMCODE;           begin //执行（常用For语句遍历游标）       FOR rec IN cur_1 LOOP      UPDATE xxxxxxxxxxx_T      SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn      WHERE area_code = rec.area_code      AND CMCODE = rec.CMCODE      AND ym = is_ym;   END LOOP;

（5）游标的定义　　

1 2 3 4 5 6 7 8 9 10 11 12

--显示cursor的处理 declare  ---声明cursor,创建和命名一个sql工作区 cursor cursor_name is      select real_name from account_hcz;     v_realname varchar2(20); begin     open cursor_name;---打开cursor,执行sql语句产生的结果集     fetch cursor_name into v_realname;--提取cursor,提取结果集中的记录     dbms_output.put_line(v_realname);     close cursor_name;--关闭cursor end;

3、在Oracle中对存储过程的调用 

（1）过程调用方式一　　

1 2 3 4 5 6 7 8 9 10 11

declare        realsal emp.sal%type;        realname varchar(40);        realjob varchar(40);  begin   //过程调用开始        realsal:=1100;        realname:='';        realjob:='CLERK';        runbyparmeters(realsal,realname,realjob);－－必须按顺序        DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);  END;  //过程调用结束

（2）过程调用方式二

1 2 3 4 5 6 7 8 9 10 11 12

declare       realsal emp.sal%type;       realname varchar(40);       realjob varchar(40);  begin    //过程调用开始       realsal:=1100;       realname:='';       realjob:='CLERK';       －－指定值对应变量顺序可变       runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);                DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);  END;  //过程调用结束

（3）过程调用方式三（SQL命令行方式下）　　　

1 2 3 4

1、SQL>exec  proc_emp('参数1','参数2');//无返回值过程调用  2、SQL>var vsal number     SQL> exec proc_emp ('参数1',:vsal);// 有返回值过程调用  //或者：call proc_emp ('参数1',:vsal);// 有返回值过程调用

相当与do  while ........先执行一次再循环

循环基本语法

1. while total <= 100
loop
.......
total := total + 1;
end loop;
例

1 2 3 4 5 6 7 8 9

declare var number:=1; begin   while var <= 100     loop        dbms_output.put_line(var);        var := var + 1;     end loop; end;

这个就相当于 JAVA里的while循环。

2. for i in 1..100
loop
语句序列;
end loop;
例

1 2 3 4 5 6 7

declare begin   for i in 1..100     loop       dbms_output.put_line(i);     end loop; end;

进阶小例子

1 2 3 4 5 6 7 8 9 10 11 12

declare   var number; begin   select count(1)   into var   from test_procedure;      for i in 1..var     loop       dbms_output.put_line(i);     end loop; end;

就相当于for 循环。

 

3. loop
语句；
exit when 条件;
语句;
end loop;
例

1 2 3 4 5 6 7 8 9

declare var number :=1; begin   loop     dbms_output.put_line(var);     exit when var > 1;     var := var + 1;     end loop; end;

　　

语法：

cursor 游标名 [ (参数名    数据类型,参数名   数据类型,...)]  is select 语句;

例：

cursor c1 is select ename from emp;

游标的属性：

                %isopen        是否打开        boolean类型

                %rowcount    影响的行数    不是总行数，例如总数100，已经取了10条，那么这个数为10

                %found         是否找到        boolean类型

                %notfound    是否没找到     boolean类型

简单实例1：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

declare   cursor p is select t.name,t.money from test_procedure t;       pname test_procedure.name%type;   pmoney test_procedure.money%type; begin   --使用前要开启光标   open p;    loop        --取当前记录        fetch p into pname,pmoney;              exit when p%notfound;     dbms_output.put_line(pname||'薪水是'||pmoney);   end loop;   --结束要关闭光标   close p; end;

简单实例2：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

declare   cursor p is select * from test_procedure t;    var test_procedure%rowtype;  begin   --使用前要开启光标   open p;    loop        --取当前记录        fetch p into var;           exit when p%notfound;        dbms_output.put_line(var.name||'薪水是'||var.money);    end loop;    --结束要关闭光标   close p; end;

简单实例3：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

declare   cursor p is select * from test_procedure t;     var test_procedure%rowtype; begin   --使用前要开启光标   open p;    loop        --取当前记录        fetch p into var;               if var.job = '后端工程师'           then update test_procedure set money = money+10000 where name = var.name;        elsif var.job = '前端工程师'          then update test_procedure set money = money+5000 where name = var.name;        else          update test_procedure set money = money+50000 where name = var.name;        end if;     exit when p%notfound;         dbms_output.put_line(var.name||'薪水是'||var.money);     end loop;     --结束要关闭光标   close p;   --提交事务   commit;     dbms_output.put_line('完成'); end;

带参数的游标用法

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

--查询某个部门员工的姓名 declare   --形参   --在游标定义一个参数   cursor temp(pjob varchar2) is select name from test_procedure where job = pjob;   pname test_procedure.name%type; begin   --传参   --开启游标传递参数   open temp('后端工程师');              loop                   fetch temp into pname;                  exit when temp%notfound;                  dbms_output.put_line(pname);        end loop;   close temp; end;