- 存储过程 包含三部分: 声明,执行部分,异常。
- 可以有无参数程序和带参数存储过程。
- 无参程序语法
- 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_Appcode OUT VARCHAR2, --执行代码
Prm_Errormsg OUT VARCHAR2 --错误消息
) is
v_root_id varchar2(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 where b.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;