ORACLE存储过程,循环语法和游标
1、定义 2、存储过程的创建 (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表示输入参数,是参数的默认模式。 (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 declare var number:=1; begin while var <= 100 loop dbms_output.put_line(var); var := var + 1; end loop; end; 这个就相当于 JAVA里的while循环。
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 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; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |