存储过程

来源：互联网发布：bugzilla linux 下载编辑：程序博客网时间：2024/06/04 23:18

过程用于返回特定操作

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

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)

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)

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)

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

---------

SQL> exec :v_2 :=2;

PL/SQL procedure successfully completed

v_2

---------

SQL> exec exch_num(:v_1,:v_2);

PL/SQL procedure successfully completed

v_1

---------

v_2

---------

五、参数传递变量和过程

CREATE OR REPLACE PROCEDURE add_dept

(dno NUMBER,

name VARCHAR2 DEFAULT NULL,

loc VARCHAR2 DEFAULT NULL)

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)

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)

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)

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>

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

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