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
--此过程中指定了缺省的输入值,即部门号为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 comp;
/
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 comp;
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.
转自:http://blog.csdn.net/robinson_0612/article/details/6063434
一、过程定义
二、过程调用
三、参数及其传递方式:
四、过程管理
转自:http://blog.csdn.net/robinson_0612/article/details/6063434