oracle存储过程语法
[*]存储过程 包含三部分： 声明，执行部分，异常。
[*]可以有无参数程序和带参数存储过程。
[*]无参程序语法
[*]1 create or replace procedure NoParPro
[*]2 as ;
[*]3 begin
[*]4 ;
[*]5 exception
[*]6 ;
[*]7 end;
[*]8
[*]
[*] 带参存储过程实例
[*] 1 create or replace procedure queryempname(sfindno emp.empno%type) as
[*] 2 sName emp.ename%type;
[*] 3 sjob emp.job%type;
[*] 4 begin
[*] 5 ....
[*] 7 exception
[*] ....
[*]14 end;
[*]15
[*]
[*] 带参数存储过程含赋值方式
[*] 1 create or replace procedure runbyparmeters (isal in emp.sal%type,
[*] sname out varchar,sjob in out varchar)
[*] 2 as icount number;
[*] 3 begin
[*] 4 select count(*) into icount from emp where sal>isal and job=sjob;
[*] 5 if icount=1 then
[*] 6 ....
[*] 9 else
[*]10 ....
[*]12 end if;
[*]13 exception
[*]14 when too_many_rows then
[*]15 DBMS_OUTPUT.PUT_LINE('返回值多于1行');
[*]16 when others then
[*]17 DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
[*]18 end;
[*]19
[*]
[*] 过程调用
[*] 方式一
[*] 1 declare
[*] 2 realsal emp.sal%type;
[*] 3 realname varchar(40);
[*] 4 realjob varchar(40);
[*] 5 begin
[*] 6 realsal:=1100;
[*] 7 realname:='';
[*] 8 realjob:='CLERK';
[*] 9 runbyparmeters(realsal,realname,realjob); －－必须按顺序
[*]10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
[*]11 END;
[*]12
[*]
[*] 方式二
[*] 1 declare
[*] 2 realsal emp.sal%type;
[*] 3 realname varchar(40);
[*] 4 realjob varchar(40);
[*] 5 begin
[*] 6 realsal:=1100;
[*] 7 realname:='';
[*] 8 realjob:='CLERK';
[*] 9 runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); －－指定值对应变量顺序可变
[*]10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
[*]11 END;
[*]12
create or replace procedure prc_update_tb01(Prm_AppcodeOUT VARCHAR2,--执行代码 Prm_Errormsg OUT VARCHAR2 --错误消息 ) is v_root_idvarchar2(10):='510401';--一级网格值(攀枝花市) type td02_table_type is TABLE OF td02%ROWTYPE INDEX BY BINARY_INTEGER; td02_TABLE td02_table_type; --td02 网格信息 东区 td02_TABLE1 td02_table_type;-- 街道网格 td02_TABLE2 td02_table_type;-- 社区网格 -- 可变数组 type four_id_type is table of varchar2(100) index by binary_integer; v_four_id four_id_type;--四级网格(社区...) type three_id_type is table of four_id_type index by binary_integer; v_three_id three_id_type;--三级网格(街道办...) type two_id_type is table of three_id_type index by binary_integer; v_two_id two_id_type;--二级网格(东区...) type one_id_type is table of two_id_type index by binary_integer; v_one_id one_id_type;--一级网格(攀枝花市) v_sql varchar2(100); --sql;BEGIN update tb01 t set ytd021=v_root_id;--更新为攀枝花市 commit; SELECT B.* BULK COLLECT INTO td02_TABLE--东区 FROM td02 B where b.ytd024 =v_root_id;for a in 1.. td02_TABLE.count loop --更新东区，西区.. --var_array(a):=td02_TABLE(a).YTD021; update tb01 t set ytd021=td02_TABLE(a).YTD021 where t.aab004 like '%'||td02_TABLE(a).ytd022||'%'; v_sql:='update tb01 t set ytd021='||CHR(39) || td02_TABLE(a).YTD021 || CHR(39) ||' where t.aab004 like '||CHR(39) ||'%'||td02_TABLE(a).ytd022||'%'||CHR(39); dbms_output.put_line('更新SQL：'||v_sql); end loop; commit; for j in 1.. td02_TABLE.count loop --更新街道 SELECT B.* BULK COLLECT INTO td02_TABLE1 FROM td02 B whereb.YTD024 = td02_TABLE(j).ytd021; for c in 1.. td02_TABLE1.count loop --var_array(a):=td02_TABLE(a).YTD021; update tb01 t set ytd021=td02_TABLE1(c).YTD021 where t.aab004 like '%'||td02_TABLE1(c).ytd022||'%'; end loop; end loop; commit;for j in 1.. td02_TABLE1.count loop --更新社区 SELECT B.* BULK COLLECT INTO td02_TABLE2 FROM td02 B where b.YTD024 = td02_TABLE1(j).ytd021; for c in 1.. td02_TABLE2.count loop --var_array(a):=td02_TABLE(a).YTD021; update tb01 t set ytd021=td02_TABLE2(c).YTD021 where t.aab004 like '%'||td02_TABLE2(c).ytd022||'%'; end loop; end loop;commit;EXCEPTION WHEN OTHERS THEN Prm_Appcode:= 'fail'; Prm_Errormsg := 'update tb01 fail!'; dbms_output.put_line('更新tb01出错..'); RETURN;end prc_update_tb01;
页:
[1]