PL/SQL --> 存储过程

--=====================

-- PL/SQL --> 存储过程

--=====================

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

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

一、过程定义

CREATE ［OR REPLACE］PROCEDURE procedure_name

[(argument_name [IN | OUT | IN OUT] argument_type)]

AS | IS

BEGIN

procedure_body;

END [procedure_name];

存储过程中参数的类型

IN：表示是一个输入参数，可以指定缺省值。如省略参数类型，则缺省为in类型

OUT：表示是一个输出参数

IN OUT：既可以作为一个输入参数，也可以作为一个输出参数来输出结果

二、过程调用

EXECUTE ｜CALL procedure_name [(argument_list)]

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

CREATE OR REPLACE PROCEDURE display_sal(v_job emp.job%TYPE) --该形参缺省为in类型，数据类型为emp.job%TYPE

AS

v_avg_sal emp.sal%TYPE;

v_max_sal emp.sal%TYPE;

v_min_sal emp.sal%TYPE;

BEGIN

SELECT avg(sal) INTO v_avg_sal FROM emp WHERE job=v_job;

SELECT max(sal) INTO v_max_sal FROM emp WHERE Job=v_job;

SELECT min(sal) INTO v_min_sal FROM emp WHERE job=v_job;

DBMS_OUTPUT.PUT_LINE('DEPT '||v_job||' avg sal:'||v_avg_sal);

DBMS_OUTPUT.PUT_LINE('DEPT '||v_job||' max sal:'||v_max_sal);

DBMS_OUTPUT.PUT_LINE('DEPT '||v_job||' min sal:'||v_min_sal);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD!');

END display_sal;

/

scott@ORCL> set serveroutput on;

scott@ORCL> exec display_sal('SALESMAN');

DEPT SALESMAN avg sal:1400

DEPT SALESMAN max sal:1600

DEPT SALESMAN min sal:1250

PL/SQL procedure successfully completed.

三、参数及其传递方式：

在建立过程时，传递的参数为可选项，如果省略参数选项，则过程为无参过程（定义时不指定参数，调用时也不需要参数）。

如果指定参数选项，则过程为有参过程（定义时需要指定参数名字、模式、数据类型，调时时需要给出对应的参数值），定义时的参数，

称为形参，调用时的参数称为实参。

1.无参过程

CREATE OR REPLACE PROCEDURE display_systime

AS

BEGIN

DBMS_OUTPUT.PUT_LINE('CURRENT TIME IS '||sysdate);

END display_systime;

/

execute display_systime; --调用

2.有参过程

定义时需要指定参数的名字、模式、数据类型

--例：定义一个添加记录的过程（全部为输入参数）

CREATE OR REPLACE PROCEDURE add_emp

(

v_no IN emp.empno%TYPE,

v_name IN emp.ename%TYPE,

v_dept IN emp.deptno%TYPE default 20 --此过程中指定了缺省的输入值，即部门号为

)

AS

BEGIN

INSERT INTO emp (empno,ename,deptno) VALUES (v_no,v_name,v_dept);

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

DBMS_OUTPUT.PUT_LINE('Record Is Exist!');

END add_emp;

/

execute add_emp(8000,'TEST2',20); --调用

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

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

/

scott@ORCL> VARIABLE t_name varchar2(20);

scott@ORCL> VARIABLE t_sal number;

scott@ORCL> call ed_emp(7788,:t_name,:t_sal);

Call completed.

scott@ORCL> print t_name t_sal;

T_NAME

--------------------------------

SCOTT

T_SAL

----------

3100

--例：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;

/

scott@ORCL> var n1 number;

scott@ORCL> var n2 number;

scott@ORCL> exec :n1:=5;

scott@ORCL> exec :n2:=3;

scott@ORCL> exec comp(:n1,:n2);

scott@ORCL> print n1 n2;

N1

----------

8

N2

----------

15

存储过程参数的传递方式：

按位置传递：

实参按顺序将值传给形参

EXECUTE ED_EMP(7900,:t_name,:t_sal);

EXECUTE ED_EMP(8000,'TEST2',20);

按名字传递

EXECUTE ED_EMP(v_name=>'ABCDE',v_dept=>10,v_no=>8003);

混合传递

EXECUTE ED_EMP(8005,v_dept=>20,v_name=>'TEST5');

注意host variable 的使用

host 变量指的是一个绑定变量，也称之为全局变量

host 变量通常在存储过程之外被声明，如SQL*Plus使用variable来声明或使用Java来声明

host 变量在声明是使用variable关键字声明，如VARIABLE t_name varchar2(20)

host 变量在引用时使用:variable_name来引用该全局变量,如上面的引用为:t_name

可以被任意的匿名块调用并传入或传出数据值

四、过程管理

查看系统过程信息

DBA_OBJECTS

DBA_PROCEDURES

DBA_SOURCE

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

scott@ORCL> 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获得存储过程的信息

idle> 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

scott@ORCL> select object_name,procedure_name,interface,authid from user_procedures;

OBJECT_NAME PROCEDURE_NAME INT AUTHID

-------------------- ------------------------------ --- ------------

DISPLAY_SAL NO DEFINER

ED_EMP NO DEFINER

--查看存储过程的源代码

scott@ORCL> select line, text from user_source where name='ED_EMP';

LINE TEXT

---------- --------------------------------------------------------------------------------

1 PROCEDURE ed_emp

2 (

3 v_no IN emp.empno%TYPE,

4 v_name OUT emp.ename%TYPE,

5 v_sal OUT emp.sal%TYPE

6 )

7 AS

8 BEGIN

9 UPDATE emp SET sal=sal+100 WHERE empno=v_no;

10 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_no;

11 EXCEPTION

12 WHEN NO_DATA_FOUND THEN

13 DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD!');

14 END ed_emp;

--查看错误信息

SHOW ERRORS

scott@ORCL> CREATE OR REPLACE PROCEDURE comp

2 (num1 IN OUT NUMBER,num2 IN OUT NUMBER)

3 AS

4 v1 NUMBER;

5 v2 NUMMBER;

6 BEGIN

7 v1:=num1+num2;

8 v2:=num1*num2;

9 num1:=v1;

10 num2:=v2;

11 END;

12 /

Warning: Procedure created with compilation errors.

scott@ORCL> show errors;

Errors for PROCEDURE COMP:

LINE/COL ERROR

-------- -----------------------------------------------------------------

5/4 PL/SQL: Item ignored

5/4 PLS-00201: identifier 'NUMMBER' must be declared

8/3 PL/SQL: Statement ignored

8/3 PLS-00320: the declaration of the type of this expression is

incomplete or malformed

10/3 PL/SQL: Statement ignored

10/9 PLS-00320: the declaration of the type of this expression is

incomplete or malformed

删除过程

DROP PROCEDURE procedure_name

scott@ORCL> drop procedure comp;

Procedure dropped.

五、更多参考

有关SQL请参考

SQL 基础--> 子查询

SQL 基础-->多表查询

SQL基础-->分组与分组函数

SQL 基础-->常用函数

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

有关PL/SQL请参考

PL/SQL --> 语言基础

PL/SQL --> 流程控制

PL/SQL --> 存储过程

PL/SQL --> 函数

PL/SQL --> 游标

PL/SQL -->隐式游标(SQL%FOUND)

PL/SQL --> 异常处理(Exception)

PL/SQL --> PL/SQL记录

PL/SQL --> 包的创建与管理

PL/SQL --> 包重载、初始化

PL/SQL --> DBMS_DDL包的使用

PL/SQL --> DML 触发器

PL/SQL --> INSTEAD OF 触发器