oracle中存储过程详解

oracle中存储过程的使用

过程是指用于执行特定操作的PL/SQL块。如果客户应用经常需要执行特定操作，那么可以考虑基于这些操作建立过程。通过使用过程，不仅可以简化客户应用的开发和维护，而且可以提高应用程序的运行机能。
一、 使用过程参数
当建立过程时，既可以指定过程参数，也可以不提供任何参数。过程参数包括输入参数、输出参数和输入输出参数，其中输入参数（IN）用于接收调用环境的输入数据，输出参数（OUT）用于将输出数据传递到调用环境，而输入输出参数（IN OUT）不仅要接收输入数据，而且还要输出数据到调用环境。建立过程是使用CREATE OR REPLACE PROCEDURE命令来完成的，语法如下：
CREATE [OR REPLACE] PROCEDURE PROCEDURE_name
    (argument1 [mode1] datatype1,argument2 [mode2] datatype2, …)
IS [AS]
PL/SQLBlock；
如上所示，procedure_name 用于指定过程名，argument 用于指定过程参数，IS 或 AS用于开始PL/SQL块。注意，当定义参数时，只能指定数据类型，不能指定长度。下面通过例子说明建立过程，并使用各种过程参数的方法。

1建立无参数的过程  
下面以建立用于输出当前日期和时间的过程OUT_TIME为例，说明建立无参数过程的方法。
create or replace procedure out_time
is
begin
dbms_session.set_nls('nls_date_format','''yyyy_mm_dd hh24:mi:ss''');
dbms_output.put_line(sysdate);
end;
/
2 建立带有输入参数的过程
通过使用输入参数，可以将应用程序数据传递到过程。当定义过程参数时，默认参数模式是输入参数，另外可以使用IN关键字显式定义输入参数。下面以建立用于增加雇员的过程ADD_EMP为例，说明建立具有输入参数过程的方法。
create or replace procedure add_emp(
empno employee.empno%type,
ename employee.ename%type,
job employee.job%type,
sal employee.sal%type,
comm employee.comm%type,
deptno employee.deptno%type)
is
begin
insert into employee
values(empno,ename,job,sal,comm,depno);
end;
/

3 建立带有输出参数的过程
通过在过程中使用输出参数，可以将数据或消息传递到调用环境和应用程序。当定义输出参数时，需要指定参数模式OUT。下面以用于更新雇员工资并输出雇员姓名的过程UPDATE_SAL为例，说明带有输出参数过程的方法。
create or replace procedure update_sal(
eno number,salary number,name out varchar2) is
begin
update employee set sal=salary where empno=eno
returning ename into name;
end;
/

4 建立带有输入输出参数的过程
通过在过程中使用输入输出参数，可以在调用过程时输入数据到过程，在调用结束后输出数据到调用环境和应用程序。当定义输入输出参数时，需要指定参数模式为IN OUT。下面以建立用于计算两数相除结果的过程DIVIDAL为例，说明建立带有输入输出参数过程的方法。
create or replace procedure divide
(num1 in out number,num2 in out number) is
v1 number;
v2 number;
begin
v1:=trunc(num1/num2);
v2:=mod(num1,num2);
num1:=v1;
num2:=v2;
end;
/
二、 调用过程
当在SQL*PLUS中调用过程时，需要使用CALL或者EXCUTE命令，而在PL/SQL块中过程可以直接引用。当调用过程时，如果过程无参数，那么直接引用过程名；如果过程带有输入参数，那么需要为输入参数提供数据值；如果过程带有输出参数，那么需要使用变量接收输出结果；如果过程带有输入输出参数，那么在调用时需要使用具有值的变量。当为参数传递变量或者数据时，可以采用位置传递、名称传递和组合传递三种方法。下面通过示例说明调用过程的各种方法。

1 调用无参数的过程
当调用无参数的过程时，直接引用过程名即可。下面以调用过程OUT_TIME为例。说明使用无参数过程的方法。

exec out_time
2008_04_03 19:54:39

2 调用带有输入参数的过程

当调用带有输入参数的过程时，需要为输入参数提供数据值。下面以调用过程ADD_EMP增加雇员MARY为例，说明调用带有输入参数的方法。

exec add_emp(12,’MARY’,’销售人员’,2000,500,4)

3 调用带有输出参数的过程
当调用带有输出参数的过程时，需要使用变量接受输出参数的数据值。下面以调用过程UPDTAE_SAL为例，说明调用带有输出参数过程的方法。
declare
      v_name employee.ename%type;
    begin
      update_sal(&eno,&salary,v_name);
      dbms_output.put_line('姓名:'||v_name);
    end;
/

输入eno 的值：2
输入salary的值：1500
姓名：李丽

4 调用带有输入输出参数的过程
当调用带有输入输出参数的过程时，在调用之前需要定义变量存放输入值，并且在调用结束之后使用变量输出数据。下面以调用过程DIVIDE为例，说明调用带有输入输出参数过程的方法。
declare
n1 number:=&n1;
   n2 number:=&n2;
begin
divide(n1,n2);
dbms_output.put_line('商:'||n1||',余数:'||n2);
end;
/
输入n1的值：10
输入n2的值：3
商:3,余数:1

5 使用位置传递为参数传递变量和数据
位置传递是指按照参数定义顺序依次为每个参数提供变量或者数据值。当使用位置传递时，必须为所有参数提供变量或者输入数据。下面以调用过程ADD_EMP增加雇员MIKE为例，说明调用带有输入输出参数过程的方法。
call add_emp(13,’MIKE’,’程序员’,2000,500,1)

6 使用名称传递为参数传递变量和数据
名称传递是指按照参数名为其提供变量或者数据。当使用名称传递时，需要使用关联符号“=>”为参数提供变量或者数值。下面以调用过程ADD_EMP增加雇员JOHN为例，说明调用带有输入输出参数过程的方法。
exec add_emp(empno=>16,ename=>’JOHN’,job=>NULL,sal=>NULL,deptno=>null)

7 使用组合传递为参数传递变量和数据
组合传递是指在调用子程序时结合使用位置传递和名称传递.下面以调用过程ADD_EMP增加代码为4444、姓名为AGASI的雇员为例，说明调用组合传递的方法。
Exec add_emp(4444,’AGASI’,’CLERK’,null,sal=>1200,comm.=>0,deptno=>5)
三、 维护过程
1 删除过程
使用DROP PROCEDURE命令可以删除过程。
DROP PROCEDURE add_department;

2 显示编译错误
     当建立过程时，如果编译成功，则会显示消息“过程已建立”，否则会显示消息“警告：创建的过程有编译错误。”为了确定错误原因以及错误所在行，可以查询数据字典USER?_ERRORS，或者执行SHOW ERRORS命令。
SQL> show errors
Errors for PROCEDURE TEST.ADD_EMP:
    
LINE/COL ERROR
-------- --------------------------------------------
2/42     PLS-00302: 必须说明 'ENAME5' 组件
0/0      PL/SQL: Compilation unit analysis terminated

3确定过程状态
当使用ALTER TABLE 命令改变表结构时，会将基于该表的子程序改为INVALID状态。为了确定处于INVALID状态的对象，可以查询数据字典USER_OBJECTS。下面以列出处于INVALID状态的所有的过程名为例，说明使用该数据字典的方法。
select object_name from user_objects
    where status='invalid' and object_type='procedure';

OBJECT_NAME
--------------------------------------------------------------------------
GET——EMP

4 编译过程
     为了避免过程的运行错误，应该重新编译处于INVALID状态的过程，使用ALTER PROCEDURE 命令可以编译过程。
     ALTER PROCEDURE get_emp COMPILE；
5 查看过程代码
     通过查询数据字典USER_SOURCE，可以取得当前用户所拥有的子程序名称以及源代码。下面以显示过程OUT_TIME的源代码为例，说明使用该数据字典的方法。
    
select text from user_source where name=upper('out_time');

TEXT
----------------------------------------------------------------procedure out_time
is
begin
dbms_session.set_nls('nls_date_format','''yyyy_mm_dd hh24:mi:ss''');
dbms_output.put_line(sysdate);
end;

6 rows selected