过程用于返回特定操作
CREATA [OR REPLACE] PROCEDURE procedure_name
[(arg1 [MODE] DATATYPE,
agr2 [MODE] DATATYPE)...] --当指定参数类型时,不能指定其长度;MODE:IN、OUT、IN OUT(未指定时为IN)
IS|AS --开始一个PL/SQL块
BEGIN
statement
END;
/
一、无参过程
SQL>CREATE OR REPLACE PROCEDURE get_time
IS
BEGIN
dbms_output.put_line(sysdate);
END;
/
1、使用exec调用过程
SQL> set serveroutput on;
SQL> exec get_time;
24-7月 -13
PL/SQL procedure successfully completed
2、使用call调用过程
SQL> call get_time();
Method called
二、带有IN参数的过程
SQL>CREATE OR REPLACE PROCEDURE add_employee
(eno NUMBER,name VARCHAR2,sal NUMBER,
job VARCHAR2 default 'CLERK',
dno NUMBER)
IS
BEGIN
INSERT INTO EMP(EMPNO,ename,sal,job,deptno,hiredate)
VALUES(eno,name,sal,job,dno,sysdate);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001,'雇员号重复');
END;
/
调用过程
SQL> exec add_employee(1017,'DSX',7000,'CLEAK',10);
PL/SQL procedure successfully completed
SQL> exec add_employee(1017,'DSX',7000,'CLEAK',10);
begin add_employee(1017,'DSX',7000,'CLEAK',10); end;
ORA-20001: 雇员号重复
ORA-06512: 在 "UIADM.ADD_EMPLOYEE", line 11
ORA-06512: 在 line 2
三、带有OUT参数的过程
带有OUT、IN OUT参数的过程可以用于输入数据
CREATE OR REPLACE PROCEDURE que_sal
(eno NUMBER,name OUT VARCHAR2,sal OUT NUMBER)
IS
BEGIN
SELECT ename,sal INTO name,sal FROM emp WHERE empno=eno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'该雇员号不存在');
END;
/
调用过程
SQL> var name varchar2;
SQL> var sal number;
SQL> exec que_sal(1002,:name,:sal);
PL/SQL procedure successfully completed
name
---------
ALLEN
sal
---------
1600
SQL> exec que_sal(3832,:name,:sal); --输入不存在的雇员号
begin que_sal(3832,:name,:sal); end;
ORA-20001: 该雇员号不存在
ORA-06512: 在 "UIADM.QUE_SAL", line 8
ORA-06512: 在 line 2
四、带有IN OUT参数的过程
CREATE OR REPLACE PROCEDURE exch_num
(num1 IN OUT NUMBER,num2 IN OUT NUMBER)
IS
v_temp NUMBER;
BEGIN
v_temp:=num1;
num1:=num2;
num2:=v_temp;
END;
/
SQL> var v_1 number;
SQL> var v_2 number;
SQL> exec :v_1 :=1;
PL/SQL procedure successfully completed
v_1
---------
1
SQL> exec :v_2 :=2;
PL/SQL procedure successfully completed
v_2
---------
2
SQL> exec exch_num(:v_1,:v_2);
PL/SQL procedure successfully completed
v_1
---------
2
v_2
---------
1
五、参数传递变量和过程
CREATE OR REPLACE PROCEDURE add_dept
(dno NUMBER,
name VARCHAR2 DEFAULT NULL,
loc VARCHAR2 DEFAULT NULL)
IS
BEGIN
INSERT INTO dept(deptno,dname,loc)
VALUES(dno,name,loc);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001,'部门号重复');
END;
/
1、按位置传递参数
位置传递是指调用时按照参数定义的顺序依次为参数指定相应变量或数值
SQL> exec add_dept(50,'SALES','NY');
SQL> exec add_dept(60,NULL,'NY');
SQL> exec add_dept(70,'ACCOUNTING');
2、按名称传递参数
SQL> exec add_dept(dno=>80,loc=>'BJ');
SQL> exec add_dept(dno=>90);
3、组合传递参数
SQL> exec add_dept(98,loc=>'SH');
SQL> exec add_dept(99,name=>'RESEARCH','SH'); --
begin add_dept(98,name=>'RESEARCH','SH'); end;
ORA-06550: 第 2 行, 第 36 列:
PLS-00312: 一个定位相关参数没有说明其相关性
SQL> exec add_dept(99,name=>'RESEARCH',loc=>'SH');
六、查看过程源代码
函数建立之后,Oracle会将当前用户的函数名及源代码信息保存到USER_SOURCE数据字典中
SQL> desc user_source;
Name Type Nullable Default Comments
---- -------------- -------- ------- --------------
NAME VARCHAR2(30) Y Name of the object
TYPE VARCHAR2(12) Y Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"
LINE NUMBER Y Line number of this line of source
TEXT VARCHAR2(4000) Y Source text
SQL> SELECT text FROM USER_SOURCE WHERE NAME='ADD_EMPLOYEE' AND TYPE='PROCEDURE';
TEXT
--------------------------------------------------------------------------------
PROCEDURE add_employee
(eno NUMBER,name VARCHAR2,sal NUMBER,
job VARCHAR2 default 'CLERK',
dno NUMBER)
IS
BEGIN
INSERT INTO EMP(EMPNO,ename,sal,job,deptno,hiredate)
VALUES(eno,name,sal,job,dno,sysdate);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001,'雇员号重复');
END;
七、删除过程
SQL>drop procedure add_dept;
八、查看当前用户子程序
SQL> SELECT object_name,created,status FROM user_objects WHERE object_type IN ('PROCEDURE','FUNCTION');
OBJECT_NAME CREATED STATUS
------------------------------ ----------- -------
GET_HELLO 2013-07-23 VALID
GET_MAX 2013-07-24 VALID
GET_TIME 2013-07-24 VALID
ADD_EMPLOYEE 2013-07-24 VALID
QUE_SAL 2013-07-24 VALID
EXCH_NUM 2013-07-25 VALID
ADD_DEPT 2013-07-25 VALID
九、查看子程序的代码
SQL> SELECT text FROM user_source WHERE name='ADD_DEPT';
TEXT
--------------------------------------------------------------------------------
PROCEDURE add_dept
(dno NUMBER,
name VARCHAR2 DEFAULT NULL,
loc VARCHAR2 DEFAULT NULL)
IS
BEGIN
INSERT INTO dept(deptno,dname,loc)
VALUES(dno,name,loc);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001,'部门号重复');
END;
十、查看子程序编译错误
如果对象编译成功,会显示消息"过程(函数)已建立",否则会显示"创建过程(函数)带有编译错误"。
查看具体错误原因可使用show errors命令或查看user_error数据字典视图
SQL> CREATE OR REPLACE PROCEDURE get_sal(eno number)
IS
BEGIN
SELECT sal FROM emp WHERE empno=en0
END;
/
Warning: Procedure created with compilation errors
1、show errors
SQL> show errors procedure get_sal;
Errors for PROCEDURE UIADM.GET_SAL:
LINE/COL ERROR
-------- ---------------------------------------------------
4/39 PL/SQL: ORA-00933: SQL 命令未正确结束
4/3 PL/SQL: SQL Statement ignored
6/0 PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
<< close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
2、user_errors确定错误原因
SQL> desc user_errors;
Name Type Nullable Default Comments
-------------- -------------- -------- ------- -----------------------------------------------
NAME VARCHAR2(30) Name of the object
TYPE VARCHAR2(12) Y Type: "TYPE", "TYPE BODY", "VIEW", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE BODY", "TRIGGER",
"JAVA SOURCE" or "JAVA CLASS"
SEQUENCE NUMBER Sequence number used for ordering purposes
LINE NUMBER Line number at which this error occurs
POSITION NUMBER Position in the line at which this error occurs
TEXT VARCHAR2(4000) Text of the error
ATTRIBUTE VARCHAR2(9) Y
MESSAGE_NUMBER NUMBER Y
SQL> select line||'/'||position line,text error from user_errors where name='GET_SAL';
LINE ERROR
------------------------------------------ --------------------------------------------
4/39 PL/SQL: ORA-00933: SQL 命令未正确结束
4/3 PL/SQL: SQL Statement ignored
6/0 PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
<< close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
十一、依赖关系
建立存储对象(过程、函数、包、视图、触发器)时往往需要引用其他对象,这时存储对象被称为对象依赖(dependent object),而将引用的对象称为引用对象(referenced object),对象依赖又包含直接依赖与间接依赖两种情况
无论是直接依赖还是间接依赖,当修改被引用对象的结构时,都会使相关以来对象转变成INVALID状态,
1、使用user_dependencies确认直接依赖关系
SQL> desc user_dependencies;
Name Type Nullable Default Comments
-------------------- ------------- -------- ------- ----------------------------------------------------------
NAME VARCHAR2(30) Name of the object
TYPE VARCHAR2(17) Y Type of the object
REFERENCED_OWNER VARCHAR2(30) Y Owner of referenced object (remote owner if remote object)
REFERENCED_NAME VARCHAR2(64) Y Name of referenced object
REFERENCED_TYPE VARCHAR2(17) Y Type of referenced object
REFERENCED_LINK_NAME VARCHAR2(128) Y Name of dblink if this is a remote object
SCHEMAID NUMBER Y
DEPENDENCY_TYPE VARCHAR2(4) Y
SQL> SELECT name,type FROM user_dependencies WHERE referenced_name='EMP';
NAME TYPE
------------------------------ -----------------
ADD_EMPLOYEE PROCEDURE
QUE_SAL PROCEDURE
GET_SAL PROCEDURE
2、使用工具视图deptree和ideptree确认直接依赖与间接依赖关系
需要首先运行utldtree.sql创建视图与deptree_file过程
SQL> @%ORACLE_HOME%\RDBMS\ADMIN\utldtree.sql
SQL> exec deptree_fill('TABLE','SCOTT','EMP');
PL/SQL procedure successfully completed
SQL> select nested_level,name,type from deptree;
NESTED_LEVEL NAME TYPE
------------ ------------------------------ -------------------
0 EMP TABLE
SQL> select * from ideptree;
DEPENDENCIES
---------------------------------------------------------------
TABLE SCOTT.EMP
十二、重新编译子程序
当修改被应用对象的结构时,会将相关依赖对象转化为无效(INVALID)状态
SQL> select object_name,object_type from user_objects where status='INVALID';
OBJECT_NAME OBJECT_TYPE
---------------------------------------------------- -------------------
ADD_EMPLOYEE PROCEDURE
QUE_SAL PROCEDURE
GET_SAL PROCEDURE
SQL> select object_name,object_type from user_objects where status='INVALID';
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------- -------------------
ADD_EMPLOYEE PROCEDURE
QUE_SAL PROCEDURE
GET_SAL PROCEDURE
SQL> alter procedure ADD_EMPLOYEE compile;
Procedure altered
SQL> alter procedure QUE_SAL compile;
Procedure altered
SQL> alter procedure GET_SAL compile;
Procedure altered