oracle存储过程语法

最新推荐文章于 2025-01-24 16:31:24 发布

原创最新推荐文章于 2025-01-24 16:31:24 发布 · 159 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #SQL #C #C++ #C#

oracle 专栏收录该内容

29 篇文章

订阅专栏

存储过程包含三部分：声明，执行部分，异常。
可以有无参数程序和带参数存储过程。
无参程序语法
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;