PL/SQL--存储过程

1.定义

存储过程子程序的一种类型，能够完成一些任务，作为schema对象存储于数据库。是一个有名字的PL/SQL代码块，支持接收或不接受参数

，同时也支持参数输出。一个存储过程通常包含定于部分，执行部分，Exception部分，可以被其他子程序调用，也可以被重用。

 

2.存储过程的创建及修改

语法：

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] datatype [{(:= | DEFAULT ) defaultvalue}] [, ...])]
{IS | AS}
BEGIN
procedure_body
END procedure_name;

语法解析：

IN:输入参数；

OUT:输出参数；

IN OUT：即可输入也可输出的参数；

datatype:参数的数据类型，此处不能带精度。

:=|DEFAULT：用于设置参数的默认值。

编写一个简单示例：

定义一个过程，以JOB为参数，查询该JOB的最高工资、最低工资、平均工资。

create or replace procedure query_sal(v_job emp.job%type)
as
v_min_sal emp.sal%type;
v_max_sal emp.sal%type;
v_avg_sal emp.sal%type;
begin
  select min(sal) into v_min_sal from emp where job=v_job;
  select max(sal) into v_max_sal from emp where job=v_job;
  select avg(sal) into v_avg_sal from emp where job=v_job;
  
  dbms_output.put_line('該部門最高工資：'||v_max_sal);
  dbms_output.put_line('該部門最低工資：'||v_min_sal);
  dbms_output.put_line('該部門平均工資：'||v_avg_sal);
  exception
    when no_data_found then
      dbms_output.put_line('NO RECORD FOUND');
end query_sal;

3.执行过程

 

SQL> set serveroutput on
SQL> exec query_sal('SALESMAN');
This job is minimum salary is 1250
This job is maximum salary is 1600
This job is average salary is 1400
PL/SQL procedure successfully completed.

例2：定义一个输入员工编号，修改记录，再返回修改后的结果（姓名和工资）。

create or replace procedure ed_emp
 (
 v_no in emp.empno%type,    --定义了一个in类型，二个out类型的参数
 v_name out emp.ename%type,
 v_sal out emp.sal%type)
 as
 begin
   update emp set sal = sal + 1000 where empno = v_no;
   select ename,sal into v_name,v_sal from emp where empno = v_no;
 exception
   when no_data_found then
     dbms_output.put_line('Not Data Found');

 end ed_emp;
 /
Procedure created.
SQL>VARIABLE t_name varchar2(20);
SQL>VARIABLE t_sal number;
SQL> exec ed_emp(7369,:t_name,:t_sal);
PL/SQL procedure successfully completed.
SQL> print t_name
T_NAME
--------------------------------------------------------------------------------
SMITH
SQL> print t_sal
     T_SAL
----------
       900

例：IN OUT类型参数的使用

create or replace procedure add_emp(
e_no emo.empno%type,
e_name emp.ename%type,
e_dept emp.detpno%type default 20)
as
begin
    insert into emp(empno,ename,deptno) Values(e_no,e_name,e_dept);
    exception
    when dup_val_on_index then
      dbms_output.put_line('Recourd is exist');
end add_emp;

例：IN OUT类型参数的使用

create or replace procedure comp
(num1 in out number,num2 in out number)
as
  v1 number;
  v2 number;
begin
  v1 := num1 + num2;
  v2 := num1 * num2;
  num1 := v1;
  num2 := v2;
end comp;
SQL> var v1 number
SQL> var v2 number
SQL> exec :v1 := 3
PL/SQL procedure successfully completed.
SQL> exec :v2 := 5
PL/SQL procedure successfully completed.
SQL> exec comp(:v1,:v2);
SQL> print v1 v2
        V1
----------
         8
        V2
----------
        15
SQL> exec comp(:v1,:v2);
PL/SQL procedure successfully completed.
SQL> print v1 v2
        V1
----------
        23
        V2
----------
       120

可以看到in out类型的参数既作为输入参数又作为输出参数。

4.查看系统过程信息

• DBA_OBJECTS


• DBA_PROCEDURES


• DBA_SOURCE

使用desc procedure_name 查看存储过程的参数信息

SQL>desc ed_emp;
 PROCEDURE ed_emp
 Argument Name Type In/Out Default?
 ------------------------------ ----------------------- ------ --------
 V_NO NUMBER(4) IN
 V_NAME VARCHAR2(10) OUT
 V_SAL NUMBER(7,2) OUT

从dba_objects获得存储过程的信息

select owner,object_name,object_type,status from dba_objects where object_name = 'ED_EMP';
 OWNER OBJECT_NAME OBJECT_TYPE STATUS
 ------------------------------ -------------------- --------------- -------
 SCOTT ED_EMP PROCEDURE VALID
 SQL>select object_name,procedure_name,interface,authid from user_procedures;
 OBJECT_NAME PROCEDURE_NAME INT AUTHID
 -------------------- ------------------------------ --- ------------
 DISPLAY_SAL NO DEFINER
 ED_EMP NO DEFINER

查看存储过程的源代码

SQL>select line, text from user_source where name='ED_EMP';
 LINE TEXT
 ---------- --------------------------------------------------------------------------------
 PROCEDURE ed_emp
 (
 v_no IN emp.empno%TYPE,
 v_name OUT emp.ename%TYPE,
 v_sal OUT emp.sal%TYPE
 )
 AS
 BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=v_no;
 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_no;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD!');
 END ed_emp;

查看错误信息
SHOW ERRORS

 

更多参考：

PL/SQL-->基础知识篇

PL/SQL-->游标