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