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

发布时间：2020-12-30 23:14:14 所属栏目：站长百科来源：网络整理

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

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

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

（1）无参存储过程语法

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

（2）带参存储过程实例　

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

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

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）存储过程中游标定义使用

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）游标的定义　　

--显示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）过程调用方式一　　

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）过程调用方式二

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、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;
例

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;
例

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

进阶小例子

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;
例

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：

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：

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：

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;

带参数的游标用法

--查询某个部门员工的姓名
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;

（编辑：安卓应用网_ASP源码网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!