欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

ORACLE存储过程,循环语法和游标(转) 博客分类: Oracle  

程序员文章站 2024-03-22 21:52:52
...

原作者 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 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 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;