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

缺乏、安全感 2022-05-15 03:24 173阅读 0赞

\--============================

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

\--============================

包，是一个逻辑集合，是由 PL/SQL类型以及 PL/SQL子程序的集合。 PL/SQL类型包括 table类型， record类型。 PL/SQL项则包括游标，游标

变量。PL/SQL子程序包括过程，函数等。可以说包可谓是包罗万象。是所有PL/SQL相关资源的汇总。

包的使用可以简化应用程序设计，实现信息掩藏，子程序重载等功能。

包的优点

1.模块化：将函数，子程序全部融合在一起，使得成为一个有机的整体，封装了相关的结构。

2.易于维护：整合了子程序，更易于维护。

3.简化应用程序设计：包的声明与包体内容相分离。

4.隐藏信息：私有对象不可访问，所有的包体内代码可以实现隐藏。

5.节省I/O：一次编译，多次使用。

一、包的组成与创建语法

包头：用于定义包的公共组件，如函数头，过程头，游标等以及常量，变量等。包头中定义的公共组件可以在包内引用，也可以被其

它子程序引用。

包体：用于定义包头中定义过的过程和函数。可以单独定义私有组件，包括变量，常量，过程和函数等。私有组件只能在包内使用，而

不能被其它子程序所调用。

一言以蔽之，包头定义包的声明及描述部分，而包体则定义了对应包的具体执行部分。

创建包的语法：

CREATE \[OR REPLACE\] PACKAGE package\_name \--定义包头

\{ AS|IS\}

public\_variable\_declarations |

public\_type\_declarations |

public\_exception\_declarations |

public\_cursor\_declarations |

function\_declarations |

procedure\_specifications

END \[package\_name\]

CREATE \[OR REPLACE\] PACKAGE BODY package\_name \--定义包体，包体中的package\_name应当与包头中的package\_name相同

\{ AS|IS\}

private\_variable\_declarations |

private\_type\_declarations |

private\_exception\_declarations |

private\_cursor\_declarations |

function\_declarations |

procedure\_specifications

END \[package\_name\]

二、创建包

下面演示包的创建，基于用户scott创建，存储过程，函数等依赖于其下的对象

1.创建包头

CREATE OR REPLACE PACKAGE emp\_package IS \--创建包头，包的名字为emp\_package

g\_deptno NUMBER(3) := 30; \--定义一个公共变量g\_deptno

PROCEDURE add\_employee(eno NUMBER, name VARCHAR2, salary NUMBER, dno NUMBER DEFAULT g\_deptno); \--声明过程

PROCEDURE fire\_employee(eno NUMBER); \--声明过程

FUNCTION get\_sal(eno NUMBER) RETURN NUMBER; \--声明函数

END emp\_package;

2.创建包体

CREATE OR REPLACE PACKAGE BODY emp\_package IS \--创建包体，注意，包体中包的名字必须与包头的名字相一致

FUNCTION validate\_deptno(v\_deptno NUMBER) RETURN BOOLEAN \--创建一个私有函数,注,此私有函数不能该包外子程序调用

v\_temp INT;

BEGIN

SELECT 1 INTO v\_temp FROM dept WHERE deptno = v\_deptno;

RETURN TRUE;

EXCEPTION

WHEN NO\_DATA\_FOUND THEN

RETURN FALSE;

END;

PROCEDURE add\_employee \--创建添加雇员的过程

(eno NUMBER, name VARCHAR2, salary NUMBER, dno NUMBER DEFAULT g\_deptno) IS

BEGIN

IF validate\_deptno(dno) THEN \--该过程调用了包内的一个函数validate\_deptno来验证dno的有效性

INSERT INTO emp(empno, ename, sal, deptno) VALUES(eno, name, salary, dno);

ELSE

RAISE\_APPLICATION\_ERROR(-20000, '不存在该部门');

END IF;

EXCEPTION

WHEN DUP\_VAL\_ON\_INDEX THEN

RAISE\_APPLICATION\_ERROR(-20011, '该雇员已存在');

END;

PROCEDURE fire\_employee(eno NUMBER) IS \--创建解除雇员的过程

BEGIN

DELETE FROM emp WHERE empno = eno;

IF SQL%NOTFOUND THEN

RAISE\_APPLICATION\_ERROR(-20012, '该雇员不存在');

END IF;

END;

FUNCTION get\_sal(eno NUMBER) RETURN NUMBER IS \--创建函数get\_sal返回雇员的薪水

v\_sal emp.sal%TYPE;

BEGIN

SELECT sal INTO v\_sal FROM emp WHERE empno = eno;

RETURN v\_sal;

EXCEPTION

WHEN NO\_DATA\_FOUND THEN

RAISE\_APPLICATION\_ERROR(-20012, '该雇员不存在');

END;

END emp\_package;

3.创建仅包含包头的包(仅包含包头的包也可以被调用，具体参照后面的包的调用)

CREATE OR REPLACE PACKAGE global\_int

g\_positive CONSTANT NUMBER:=10;

g\_negative CONSTANT NUMBER:=-10;

END global\_int;

三、包的调用

对于包的私有对象只能在包内调用。如上面的例子中对包内私有函数validate\_deptno进行了直接调用

对于包的公共对象，既可以在包内调用，也可以由其他应用程序调用。使用其他应用程序调用时的方法：包名.包对象

1.调用包的公共变量

scott@ORCL> exec emp\_package.g\_deptno:=10;

2.调用包的公共过程

scott@ORCL> exec emp\_package.add\_employee(2222,'Robinson',3000); \--此调用未指定部门号，则使用缺省值,但前面执行了

\--exec emp\_package.g\_deptno:=10;故部门号变为

scott@ORCL> exec emp\_package.add\_employee(3333,'Jackson',4000,20);

scott@ORCL> select \* from emp where empno in(2222,3333);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

\---------- ---------- --------- ---------- --------- ---------- ---------- ----------

2222 Robinson 3000 10

3333 Jackson 4000 20

3.调用包的公共函数

scott@ORCL> var sal number

scott@ORCL> exec :sal:=emp\_package.get\_sal(7788);

PL/SQL procedure successfully completed.

scott@ORCL> print sal

SAL

\----------

310

4.以不同用户身份调用包.需要使用schema名字来调用，即：用户名.包名.包对象名

scott@ORCL> conn lion/lion \--注意帐户需要具有执行所调用包的权限

lion@ORCL> exec scott.emp\_package.fire\_employee(2222);

5.调用远程数据库包的公共对象。调用方法:包名.包对象名@数据库链接名

sys@ASMDB> create database link orcl

2 connect to lion identified by lion

3 using 'orcl';

Database link created.

sys@ASMDB> exec scott.emp\_package.add\_employee@orcl(4444,'Richard',4000);

BEGIN scott.emp\_package.add\_employee@orcl(4444,'Richard',4000); END;

ERROR at line 1:

ORA\-06550: line 1, column 7: \--注意远程调用时，对于缺省的参数不适用，需要明确指定参数

PLS\-00424: RPC defaults cannot include Package State

ORA\-06550: line 1, column 7:

PL/SQL: Statement ignored

sys@ASMDB> exec scott.emp\_package.add\_employee@orcl(4444,'Richard',4000,20); \--下面的调用被成功执行

PL/SQL procedure successfully completed.

6.无包体包的调用(使用前面创建的包global\_int)

scott@ORCL> BEGIN

2 DBMS\_OUTPUT.PUT\_LINE('Result is : '||2\*global\_int.g\_positive); \--使用包DBMS\_OUTPUT来调用

3 END;

4 /

Result is : 20

PL/SQL procedure successfully completed.

scott@ORCL> CREATE OR REPLACE FUNCTION f\_negative(m number) \--将包嵌入到函数之中

2 RETURN NUMBER

3 IS

4 BEGIN

5 RETURN(m\*global\_int.g\_negative);

6 END f\_negative;

7 /

Function created.

scott@ORCL> EXEC DBMS\_OUTPUT.PUT\_LINE(f\_negative(2));

\-20

PL/SQL procedure successfully completed.

四、包的管理

1.查看包

scott@ORCL> select line,text from user\_source \--查看包头

2 where name='EMP\_PACKAGE' and type='PACKAGE';

LINE TEXT

\---------- ------------------------------------------------------------

1 PACKAGE emp\_package IS

2 g\_deptno NUMBER(3) := 30;

3 PROCEDURE add\_employee(eno NUMBER, name VARCHAR2, salary N

UMBER, dno NUMBER DEFAULT g\_deptno);

4 PROCEDURE fire\_employee(eno NUMBER);

5 FUNCTION get\_sal(eno NUMBER) RETURN NUMBER;

6 END emp\_package;

scott@ORCL> select line,text from user\_source \--查看包体

2 where name='EMP\_PACKAGE' and type='PACKAGE BODY';

LINE TEXT

\---------- --------------------------------------------------------------------------------

1 PACKAGE BODY emp\_package IS

2 FUNCTION validate\_deptno(v\_deptno NUMBER) RETURN BOOLEAN IS

3 v\_temp INT;

4 BEGIN

5 SELECT 1 INTO v\_temp FROM dept WHERE deptno = v\_deptno;

6 RETURN TRUE;

......................

2.查看包的参数

scott@ORCL> desc emp\_package;

PROCEDURE ADD\_EMPLOYEE

Argument Name Type In/Out Default?

\------------------------------ ----------------------- ------ --------

ENO NUMBER IN

NAME VARCHAR2 IN

SALARY NUMBER IN

DNO NUMBER IN DEFAULT

PROCEDURE FIRE\_EMPLOYEE

Argument Name Type In/Out Default?

\------------------------------ ----------------------- ------ --------

ENO NUMBER IN

FUNCTION GET\_SAL RETURNS NUMBER

Argument Name Type In/Out Default?

\------------------------------ ----------------------- ------ --------

ENO NUMBER IN

3.包的删除

DROP PACKAGE package\_name \--同时删除包体和包头

scott@ORCL> DROP PACKAGE global\_int;

删除包体，保留包头

DROP PACKAGE BODY package\_name \--删除包体

scott@ORCL> drop package body emp\_package;

Package body dropped.

4.包的编译

重新编译包规范和包体：alter package...compile  
重新编译包规范：alter package...compile specification  
重新编译包体：alter package...compile body

五、总结

创键包体之前应该先创建包头

包头应当仅仅包含那些希望作为公共对象的部分

包头的声明应包含尽可能少的结构信息

任意包头的变更，需要重新编译该包内的子程序

在包头内定义的任意公共对象可以被任意内部或外部子程序调用

包体内的私有对象仅仅能被该包体内的子程序调用

六、更多参考

有关SQL请参考

[SQL 基础--> 子查询][SQL _--_]

[SQL 基础-->多表查询][SQL _--]

[SQL基础-->分组与分组函数][SQL_--]

[SQL 基础-->常用函数][SQL _-- 1]

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

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

有关PL/SQL请参考

[PL/SQL --> 语言基础][PL_SQL --_]

[PL/SQL --> 流程控制][PL_SQL --_ 1]

[PL/SQL --> 存储过程][PL_SQL --_ 2]

[PL/SQL --> 函数][PL_SQL --_ 3]

[PL/SQL --> 游标][PL_SQL --_ 4]

[PL/SQL -->隐式游标(SQL%FOUND)][PL_SQL --_SQL_FOUND]

[PL/SQL --> 异常处理(Exception)][PL_SQL --_ _Exception]

[PL/SQL --> PL/SQL记录][PL_SQL --_ PL_SQL]

[PL/SQL --> 包的创建与管理][PL_SQL --_ 5]

[PL/SQL --> 包重载、初始化][PL_SQL --_ 6]

[PL/SQL --> DBMS\_DDL包的使用][PL_SQL --_ DBMS_DDL]

[PL/SQL --> DML 触发器][PL_SQL --_ DML]

[PL/SQL --> INSTEAD OF 触发器][PL_SQL --_ INSTEAD OF]

[SQL _--_]: http://blog.csdn.net/robinson_0612/archive/2010/05/14/5592002.aspx
[SQL _--]: http://blog.csdn.net/robinson_0612/archive/2010/05/06/5563199.aspx
[SQL_--]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559110.aspx
[SQL _-- 1]: http://blog.csdn.net/robinson_0612/archive/2010/04/16/5495170.aspx
[SQL _--_ ROLLUP_CUBE]: http://blog.csdn.net/robinson_0612/archive/2010/06/01/5640031.aspx
[SQL_--_START BY ... CONNECT BY PRIOR]: http://blog.csdn.net/robinson_0612/archive/2010/05/22/5616877.aspx
[PL_SQL --_]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063399.aspx
[PL_SQL --_ 1]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063422.aspx
[PL_SQL --_ 2]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063434.aspx
[PL_SQL --_ 3]: http://blog.csdn.net/robinson_0612/archive/2010/12/11/6069531.aspx
[PL_SQL --_ 4]: http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078622.aspx
[PL_SQL --_SQL_FOUND]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092066.aspx
[PL_SQL --_ _Exception]: http://blog.csdn.net/robinson_0612/archive/2010/12/16/6080119.aspx
[PL_SQL --_ PL_SQL]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084390.aspx
[PL_SQL --_ 5]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084475.aspx
[PL_SQL --_ 6]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092097.aspx
[PL_SQL --_ DBMS_DDL]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098248.aspx
[PL_SQL --_ DML]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098258.aspx
[PL_SQL --_ INSTEAD OF]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098263.aspx