oracle 存储过程语法

子程序是指被命名的PL/SQL块，这些块可以带有参数，可以在不同应用中多次调用，PL/SQL有两种类型子程序：过程和函数，其中过程是用于执行特定操作，而函数是用于返回特定数据。

 

结构：

 cursor

      1、静态cursor

          a)、显示cursor

          b)、隐示cursor

      2、动态cursor

          a)、refcursor

                 强类型： 规定返回类型

                 弱类型：  不规定返回类型，可是任何类型

  

一、开发过程
    过程用于执行特定操作，如果在应用程序中经常需要执行特定的操作，可以基于操作建立一个过程，通过使用过程，不仅可以简化客户端程序的开发和维护，而且还可以提高应用程序的性能，语法如下：

 

create [or replace]procedure procedure_name
(argument1[mode] dateType1,argument2[mode] dateType2........)
IS/AS 
PL/SQL block

如上所示：procedure_name用于指定过程名称，argument1、argument2用于指定过程的参数，mode用于指定参数模式，dateType1、dateType2用于指定过程参数类型；IS/AS用于表示开始一个PL/SQL块。当指定参数类型时不能指定参数长度，另外当建立过程时即可以指定输入参数(IN),也可以指定输出参数(OUT),又可以指定输入输出参数(INOUT),通过在过程中使用输入参数，可以将应用程序的数据传递到执行部分，通过使用过程中的输出参数，可以将执行部分的数据传递到应用程序中。定义子程序的参数时，如果不指定参数模式，则默认为输入参数，如果定义输出参数，那么需要指定OUT关键字，如果要定义输入输出参数，则需要指定INOUT关键字。
1、建立过程：不带参数
  示例如下：
  

 

   create or replace procedure procedure_test1
   is
   begin
       dbms_output.put_line(to_char(sysdate,'YYYY-MM-DD'));
   end;
   /
  

  建立了过程procedure_test1后，就可以调用该过程了。在SQL*Plus环境中调用过程有两种方法，一种是使用execute(简写为exe)命令，另一种是使用call命令，如下：
  示例一：使用execute命令调用过程
    

      set serveroutput on;
      execute procedure_test1;
    

  示例二：使用call命令调用过程
   

 

     SQL>set serveroutput on;
     SQL>call procedure_test1();
    

2、建立过程：带有IN参数
示例如下：

create or replace procedure procedure_in
(v_name varchar2,v_age varchar2,v_address varchar2,v_id number)
is
begin
  insert into cip_temps values(v_name,v_age,v_address,v_id);
end;
/

在创建过程时，如果参数指定了参数名和参数类型，则就不必定义其中的参数名和参数类型。
运用exec、call调用创建完毕的procedure_insert过程，代码如下：

SQL>exec procedure_in('888','888','888',888);
SQL>exec procedure_in('999','999','999',999);

3、建立过程：带有OUT参数
过程不仅可以用于执行特定操作，也可以用于输出数据，在过程中用于输出数据时使用OUT或INOUT参数完成的。
示例如下：

CREATE OR REPLACE procedure procedure_out
(v_name out varchar2,v_age out varchar2,v_address out varchar2,v_id number,error out varchar2)
is
begin
select name,age,address into v_name,v_age,v_address from cip_temps where id=v_id;
exception 
when no_data_found then
    error:='1';
end;
/

注意：
在创建过程时如果出现错误，但是没有标明是哪里出的错误，可以用"show error"命令查看出错的位置
如上所示：当在应用程序中调用该过程时，必须要定义变量接受输出参数的数据，下面在SQL*Plus中调用过程示例：

SQL> var name varchar2(10)
SQL> var age varchar2(10)
SQL> var address varchar2(10)
SQL> var error varchar2(10)
SQL> exec procedure_out(:name,:age,:address,888,:error);
SQL> print name age address error;
SQL> print error;

4、建立过程：带有INOUT参数
定义过程时，不仅可以定义IN和OUT参数，也可以指定IN OUT参数，IN OUT参数为输入输出参数，当使用这种参数时，在调用过程之前需要通过变量给该参数传递数据，在调用结束之后，oracle会通过该变量将过程结果传递给应用程序，示例如下：

create or replace procedure procedure_inout
(num1 in out number,num2 in out number)
is
v1 number;
v2 number;
begin
 v1:=num1/num2;
 v2:=num1+num2;
 num1:=v1;
 num2:=v2;
end;
/

调用过程

  

SQL> var num1 number
SQL> var num2 number
SQL> exec :num1:=12
SQL> exec :num2:=12
SQL> exec procedure_inout(:num1,:num2);
SQL> print num1 num2;

例：

create or replace procedure test_insert(P_USERID IN VARCHAR2, P_ORGID IN VARCHAR2) is
  TYPE TD13_TABLE_TYPE IS TABLE OF TD13%ROWTYPE INDEX BY BINARY_INTEGER; --td13表行类型的数组
    TD13_TABLE TD13_TABLE_TYPE; --td13 
    V_SQL      VARCHAR2(200);
    V_CURSOR   NUMBER; --定义光标
    N_COUNT    NUMBER(10);--待办事宜条数
    N_ROWS     NUMBER;
   
  BEGIN
    --查询该经办人可操作的菜单
    SELECT B.* BULK COLLECT 
      INTO TD13_TABLE
      FROM AD54 A, TD13 B
     WHERE A.YAE106 = B.YTD131 --AD54资源编号 等于 TD13菜单编号
       AND A.YAE093 = (SELECT C.YAE093   --根据操作人员编号获取 角色编号
                         FROM AD53A6 C, AD53A2 D
                        WHERE C.YAE093 = D.YAE093
                          AND D.YAE102 = '03'
                          AND C.YAE092 = P_USERID
                          AND ROWNUM < 2);
  
    FOR I IN 1 .. TD13_TABLE.COUNT LOOP
      DELETE TD14 --EXECUTE IMMEDIATE 'truncate table TD14 ; 删除以前表的记录
       WHERE AAE011 = P_USERID --经办机构编号
         AND YTD142 = TD13_TABLE(I).YTD131; --菜单编号
         commit;
      --N_COUNT  := 0;
      V_CURSOR := DBMS_SQL.OPEN_CURSOR; --为处理打开光标
      -- YTD136 如：ytb25t = '2'||用户ID
      V_SQL    := TD13_TABLE(I).YTD136 || ''''|| P_USERID || '''';
      dbms_output.put_line(V_SQL);
      
      DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE); --分析语句并加上一个where V_SQL变量值对应条件
      
      --定义列（N_COUNT）关联上上面申明的变量
      DBMS_SQL.DEFINE_COLUMN(V_CURSOR, 1, N_COUNT); --定义动态游标所能得到的对应值,其中V_CURSOR为动态游标,1为对应动态sql中的位置(从1开始),N_COUNT为该值所对应的变量,
      
      --对于非查询的语句，execute将执行该语句并返回处理了的行的个数。 对于查询，execute将确定活动集，返回0
      N_ROWS := DBMS_SQL.EXECUTE(V_CURSOR); --执行语句 
      LOOP
      --fetch_rows在结果集中移动游标，如果未抵达末尾，返回1。 
        IF DBMS_SQL.FETCH_ROWS(V_CURSOR) = 0 THEN
          EXIT;
        END IF;
        DBMS_SQL.COLUMN_VALUE(V_CURSOR, 1, N_COUNT); --将当前行的查询结果写入上面定义的列中。
        INSERT INTO TD14
        VALUES
          (P_ORGID, TD13_TABLE(I).YTD131, TD13_TABLE(I).YTD133, N_COUNT,P_USERID); --插入数据
        COMMIT;
      END LOOP;
      DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --关闭光标
    END LOOP;
  
end test_insert;

 

--在过程里调用另一存储过程
  
  begin
     --id, name为创建prc_test存储过程对应的里定义的参数名
   --local_id , local_name 为当前过程里申明的变量
   --用'=>' 符号 可以无序传参
  prc_test(id => local_id,name=>local_name);
    
   end

 

 

评论

1 楼 wenjinglian 2010-09-11  

不错很详细