--=====================
-- 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 基础--> ROLLUP与CUBE运算符实现数据汇总
SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
有关PL/SQL请参考