ORACLE 自定义函数_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
ORACLE 自定义函数
上传于||文档简介
&&O​R​A​C​L​E​ ​自​定​义​函​数
阅读已结束，如果下载本文需要使用0下载券
想免费下载更多文档？
定制HR最喜欢的简历
下载文档到电脑，查找使用更方便
还剩1页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢pianzif 的BLOG
用户名：pianzif
文章数：230
评论数：17
访问量：72810
注册日期：
阅读量：5863
阅读量：12276
阅读量：319045
阅读量：1030599
51CTO推荐博文
一：概念1.1：基本概念包，是一个逻辑集合，是由PL/SQL类型以及PL/SQL子程序的集合。PL/SQL类型包括table类型，record类型。PL/SQL项则包括游标，游标变量。PL/SQL子程序包括过程，函数等。可以说包可谓是包罗万象。是所有PL/SQL相关资源的汇总。包的使用可以简化应用程序设计，实现信息掩藏，子程序重载等功能。包的优点 & & & &1.模块化：将函数，子程序全部融合在一起，使得成为一个有机的整体，封装了相关的结构。 & & & &2.易于维护：整合了子程序，更易于维护。 & & & &3.简化应用程序设计：包的声明与包体内容相分离。 & & & &4.隐藏信息：私有对象不可访问，所有的包体内代码可以实现隐藏。 & & & &5.节省I/O：一次编译，多次使用。1.2：包的组成1.3：创建/定义语法：CREATE [OR REPLACE] PACKAGE package_name
--定义包头
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相同
private_variable_declarations |
private_type_declarations |
private_exception_declarations |
private_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]1.4：包的开发步骤1．将每个存储过程调式正确；2．用文本编辑软件将各个存储过程和函数集成在一起；3．按照包的定义要求将集成的文本的前面加上包定义；4．按照包的定义要求将集成的文本的前面加上包主体；5．使用SQLPLUS或开发工具进行调式。1.5：包的创建演示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;
--声明函数
2.创建包体
CREATE OR REPLACE PACKAGE BODY emp_package IS
--创建包体，注意，包体中包的名字必须与包头的名字相一致
FUNCTION validate_deptno(v_deptno NUMBER) RETURN BOOLEAN
--创建一个私有函数,注,此私有函数不能该包外子程序调用
v_temp INT;
SELECT 1 INTO v_temp FROM dept WHERE deptno = v_
RETURN TRUE;
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
PROCEDURE add_employee
--创建添加雇员的过程
(eno NUMBER, name VARCHAR2, salary NUMBER, dno NUMBER DEFAULT g_deptno) IS
IF validate_deptno(dno) THEN
--该过程调用了包内的一个函数validate_deptno来验证dno的有效性
INSERT INTO emp(empno, ename, sal, deptno) VALUES(eno, name, salary, dno);
RAISE_APPLICATION_ERROR(-20000, '不存在该部门');
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20011, '该雇员已存在');
PROCEDURE fire_employee(eno NUMBER) IS
--创建解除雇员的过程
DELETE FROM emp WHERE empno =
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20012, '该雇员不存在');
FUNCTION get_sal(eno NUMBER) RETURN NUMBER IS
--创建函数get_sal返回雇员的薪水
v_sal emp.sal%TYPE;
SELECT sal INTO v_sal FROM emp WHERE empno =
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20012, '该雇员不存在');
/1.6：创建只有包头的包CREATE OR REPLACE PACKAGE global_int
g_positive
CONSTANT NUMBER:=10;
g_negative CONSTANT NUMBER:=-10;
END global_二：包的调用对于包的私有对象只能在包内调用。如上面的例子中对包内私有函数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();
EMPNO ENAME
MGR HIREDATE
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
2222 Robinson
3333 Jackson
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
----------
310 & &4.以不同用户身份调用包.需要使用schema名字来调用，即：用户名.包名.包对象名scott@ORCL& conn lion/lion
--注意帐户需要具有执行所调用包的权限
lion@ORCL&exec scott.emp_package.fire_employee(2222); & &5.调用远程数据库包的公共对象。调用方法:包名.包对象名@数据库链接名sys@ASMDB&createdatabase link orcl
connect to lion identified by lion
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
DBMS_OUTPUT.PUT_LINE('Result is : '||2*global_int.g_positive);--使用包DBMS_OUTPUT来调用
Result is: 20
PL/SQL procedure successfully completed.
scott@ORCL&CREATEORREPLACEFUNCTION f_negative(m number)--将包嵌入到函数之中
RETURN NUMBER
RETURN(m*global_int.g_negative);
Function created.
scott@ORCL&EXEC DBMS_OUTPUT.PUT_LINE(f_negative(2));
PL/SQL procedure successfully completed.三：包的管理3.1：查看包scott@ORCL& select line,text from user_source
--查看包头
where name='EMP_PACKAGE' and type='PACKAGE';
---------- ------------------------------------------------------------
1 PACKAGE emp_package IS
g_deptno NUMBER(3) := 30;
PROCEDURE add_employee(eno NUMBER, name VARCHAR2, salary N
UMBER, dno NUMBER DEFAULT g_deptno);
PROCEDURE fire_employee(eno NUMBER);
FUNCTION get_sal(eno NUMBER) RETURN NUMBER;
6 END emp_
scott@ORCL& select line,text from user_source
--查看包体
where name='EMP_PACKAGE' and type='PACKAGE BODY';
---------- --------------------------------------------------------------------------------
1 PACKAGE BODY emp_package IS
FUNCTION validate_deptno(v_deptno NUMBER) RETURN BOOLEAN IS
v_temp INT;
SELECT 1 INTO v_temp FROM dept WHERE deptno = v_
RETURN TRUE;
......................3.2：查看包的参数scott@ORCL& desc emp_
PROCEDURE ADD_EMPLOYEE
Argument Name
In/Out Default?
------------------------------ ----------------------- ------ --------
PROCEDURE FIRE_EMPLOYEE
Argument Name
In/Out Default?
------------------------------ ----------------------- ------ --------
FUNCTION GET_SAL RETURNS NUMBER
Argument Name
In/Out Default?
------------------------------ ----------------------- ------ --------
IN3.3:包的删除--同时删除包体和包头
DROP PACKAGE package_name
scott@ORCL& DROP PACKAGE global_
--删除包体，保留包头
DROP PACKAGE BODY package_name
--删除包体
scott@ORCL& drop package body emp_
Package body dropped.3.4：包的编译 重新编译包规范和包体：alter package...compile & & & & &重新编译包规范：alter package...compile specification & & & & &重新编译包体：alter package...compile body3.5：注意创键包体之前应该先创建包头包头应当仅仅包含那些希望作为公共对象的部分包头的声明应包含尽可能少的结构信息任意包头的变更，需要重新编译该包内的子程序在包头内定义的任意公共对象可以被任意内部或外部子程序调用包体内的私有对象仅仅能被该包体内的子程序调用四：包的应用实例创建的包为DEMO_PKG,该包中包含一个记录变量DEPTREC、两个函数和一个过程。实现对dept表的增加、删除与查询。--包头CREATE OR REPLACE PACKAGE
DEPTREC DEPT%ROWTYPE;
--Add dept...
FUNCTION add_dept(
dept_name VARCHAR2,
RETURN NUMBER;
--delete dept...
FUNCTION delete_dept(dept_no NUMBER)
RETURN NUMBER;
--query dept...
PROCEDURE query_dept(dept_no IN NUMBER);
END DEMO_PKG;--包主体的创建方法，它实现上面所声明的包定义，并在包主体中声明一个私有变量flag和一个私有函数check_dept，由于在add_dept和remove_dept等函数中需要调用check_dpet函数，所以，在定义check_dept 函数之前首先对该函数进行声明，这种声明方法称作前向声明。CREATE OR REPLACE PACKAGE BODY DEMO_PKG
FUNCTION add_dept
dept_no NUMBER,
dept_name VARCHAR2,
location VARCHAR2
RETURN NUMBER
empno_remaining EXCEPTION; --自定义异常
PRAGMA EXCEPTION_INIT(empno_remaining, -1);
/* -1 是违反唯一约束条件的错误代码 */
INSERT INTO dept VALUES(dept_no, dept_name, location);
IF SQL%FOUND THEN
WHEN empno_remaining THEN
WHEN OTHERS THEN
RETURN -1;
FUNCTION delete_dept(dept_no NUMBER)
RETURN NUMBER
DELETE FROM dept WHERE deptno = dept_
IF SQL%FOUND THEN
WHEN OTHERS THEN
RETURN -1;
END delete_
PROCEDURE query_dept
(dept_no IN NUMBER)
SELECT * INTO DeptRec FROM dept WHERE deptno=dept_
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:数据库中没有编码为'||dept_no||'的部门');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('程序运行错误,请使用游标进行操作!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
END query_
END DEMO_PKG;--使用包调用DEMO_PKG包内函数对dept表进行插入、查询和删除操作，并通过DEMO_PKG包中的记录变量DEPTREC显示所查询到的数据库信息：DECLARE
Var NUMBER;
Var := DEMO_PKG.add_dept(90,'HKLORB', 'HAIKOU');
IF var =-1 THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
ELSIF var =0 THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:该部门记录已经存在！');
DBMS_OUTPUT.PUT_LINE('温馨提示:添加记录成功！');
DEMO_PKG.query_dept(90);
DBMS_OUTPUT.PUT_LINE(DEMO_PKG.DeptRec.deptno||'---'||
DEMO_PKG.DeptRec.dname||'---'||DEMO_PKG.DeptRec.loc);
var := DEMO_PKG.delete_dept(90);
IF var =-1 THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
ELSIF var=0 THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:该部门记录不存在！');
DBMS_OUTPUT.PUT_LINE('温馨提示:删除记录成功！');
END;创建包EMP_PKG，读取emp表中的数据--创建包说明
CREATE OR REPLACE PACKAGE EMP_PKG
TYPE emp_table_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
PROCEDURE read_emp_table (p_emp_table OUT emp_table_type);
END EMP_PKG;
--创建包体
CREATE OR REPLACE PACKAGE BODY EMP_PKG
PROCEDURE read_emp_table (p_emp_table OUT emp_table_type)
I BINARY_INTEGER := 0;
FOR emp_record IN ( SELECT * FROM emp ) LOOP
P_emp_table(i) := emp_
I := I + 1;
END read_emp_
END EMP_PKG;
E_table EMP_PKG.emp_table_
EMP_PKG.read_emp_table(e_table);
FOR I IN e_table.FIRST ..e_table.LAST LOOP
DBMS_OUTPUT.PUT_LINE(e_table(i).empno||'
'||e_table(i).ename);
END;创建包MANAGE_EMP_PKG：--创建序列从100开始,依次增加1
CREATE SEQUENCE empseq
START WITH 100
INCREMENT BY 1
ORDER NOCYCLE;
--创建序列从100开始,依次增加10
CREATE SEQUENCE deptseq
START WITH 100
INCREMENT BY 10
ORDER NOCYCLE;
-- *******************************************
-- 创建包说明
名：MANAGE_EMP_PKG
-- 功能描述：对员工进行管理(新增员工,新增部门
,删除员工,删除部门,增加工资与奖金等)
-- ******************************************
CREATE OR REPLACE PACKAGE MANAGE_EMP_PKG
--增加一名员工
FUNCTION hire_emp
(ename VARCHAR2, job VARCHAR2
, mgr NUMBER, sal NUMBER
, comm NUMBER, deptno NUMBER)
RETURN NUMBER;
--新增一个部门
FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2)
RETURN NUMBER;
--删除指定员工
PROCEDURE remove_emp(empno NUMBER);
--删除指定部门
PROCEDURE remove_dept(deptno NUMBER);
--增加指定员工的工资
PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER);
--增加指定员工的奖金
PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER);
END MANAGE_EMP_PKG;--创建包说明结束
-- *******************************************
-- 创建包体
名：MANAGE_EMP_PKG
-- 功能描述：对员工进行管理(新增员工,新增部门
,删除员工,删除部门,增加工资与奖金等)
-- ******************************************
CREATE OR REPLACE PACKAGE BODY MANAGE_EMP_PKG
total_emps
NUMBER; --员工数
total_depts NUMBER; --部门数
EXCEPTION;
EXCEPTION;
--增加一名员工
FUNCTION hire_emp(ename VARCHAR2, job VARCHAR2, mgr NUMBER,
sal NUMBER, comm NUMBER, deptno NUMBER)
RETURN NUMBER
--返回新增加的员工编号
new_empno NUMBER(4);
SELECT empseq.NEXTVAL INTO new_empno FROM
SELECT COUNT(*) INTO total_emps FROM--当前记录总数
INSERT INTO emp
VALUES (new_empno, ename, job, mgr, sysdate, sal, comm, deptno);
total_emps:=total_emps+1;
RETURN(new_empno);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
--新增一个部门
FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2)
RETURN NUMBER
new_deptno NUMBER(4); --部门编号
--得到一个新的自增的员工编号
SELECT deptseq.NEXTVAL INTO new_deptno FROM
SELECT COUNT(*) INTO total_depts FROM--当前部门总数
INSERT INTO dept VALUES (new_deptno, dname, loc);
total_depts:=total_
RETURN(new_deptno);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
--删除指定员工
PROCEDURE remove_emp(empno NUMBER)
no_result EXCEPTION; --自定义异常
DELETE FROM emp WHERE emp.empno=remove_emp.
IF SQL%NOTFOUND THEN
total_emps:=total_emps - 1; --总的员工数减1
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
END remove_
--删除指定部门
PROCEDURE remove_dept(deptno NUMBER)
no_result EXCEPTION; --自定义异常
exception_deptno_remaining EXCEPTION; --自定义异常
/*-2292 是违反一致性约束的错误代码*/
PRAGMA EXCEPTION_INIT(exception_deptno_remaining, -2292);
DELETE FROM dept WHERE dept.deptno=remove_dept.
IF SQL%NOTFOUND THEN
total_depts:=total_depts-1; --总的部门数减1
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
WHEN exception_deptno_remaining THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:违反数据完整性约束!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
END remove_
--给指定员工增加指定数量的工资
PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER)
curr_sal NUMBER(7, 2); --当前工资
--得到当前工资
SELECT sal INTO curr_sal FROM emp WHERE emp.empno=increase_sal.
IF curr_sal IS NULL THEN
UPDATE emp SET sal = sal + increase_sal.sal_incr --当前工资加新增的工资
WHERE emp.empno = increase_sal.
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
WHEN no_sal THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:此员工的工资不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
END increase_
--给指定员工增加指定数量的奖金
PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER)
curr_comm NUMBER(7,2);
--得到指定员工的当前资金
SELECT comm INTO curr_comm
FROM emp WHERE emp.empno = increase_comm.
IF curr_comm IS NULL THEN
UPDATE emp SET comm = comm + m_incr
WHERE emp.empno=increase_comm.
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
WHEN no_comm THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:此员工的奖金不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
END increase_
END MANAGE_EMP_PKG;--创建包体结束
SQL& variable empno number
SQL&execute
:empno:= manage_emp_pkg.hire_emp('HUYONG',PM,,10)
PL/SQL procedure successfully completed
105利用游标变量创建包 CURROR_VARIBAL_PKG。由于游标变量指是一个指针，其状态是不确定的，因此它不能随同包存储在数据库中，既不能在PL/SQL包中声明游标变量。但在包中可以创建游标变量参照类型，并可向包中的子程序传递游标变量参数。-- *******************************************
-- 创建包头
名：CURROR_VARIBAL_PKG
-- 功能描述：在包中引用游标变量
-- ******************************************
CREATE OR REPLACE PACKAGE CURROR_VARIBAL_PKG AS
TYPE DeptCurType IS REF CURSOR
RETURN dept%ROWTYPE; --强类型定义
TYPE CurType IS REF CURSOR;-- 弱类型定义
PROCEDURE OpenDeptVar(
Cv IN OUT DeptCurType,
Choice INTEGER DEFAULT 0,
Dept_no NUMBER DEFAULT 50,
Dept_name VARCHAR DEFAULT '%');
-- *******************************************
-- 创建包体
名：CURROR_VARIBAL_PKG
-- 功能描述：在包中引用游标变量
-- ******************************************
CREATE OR REPLACE PACKAGE BODY CURROR_VARIBAL_PKG
PROCEDURE OpenDeptvar(
Cv IN OUT DeptCurType,
Choice INTEGER DEFAULT 0,
Dept_no NUMBER DEFAULT 50,
Dept_name VARCHAR DEFAULT ‘%’)
IF choice =1 THEN
OPEN cv FOR SELECT * FROM dept WHERE deptno &= dept_
ELSIF choice = 2 THEN
OPEN cv FOR SELECT * FROM dept WHERE dname LIKE dept_
OPEN cv FOR SELECT * FROM
END CURROR_VARIBAL_PKG;
--包创建完成，以下是使用包
--定义一个过程
CREATE OR REPLACE PROCEDURE UP_OpenCurType(
Cv IN OUT CURROR_VARIBAL_PKG.CurType,
FirstCapInTableName CHAR)
--CURROR_VARIBAL_PKG.CurType采用弱类型定义
--所以可以使用它定义的游标变量打开不同类型的查询语句
IF FirstCapInTableName = 'D' THEN
OPEN cv FOR SELECT * FROM
OPEN cv FOR SELECT * FROM
END UP_OpenCurT
--定义一个应用
DeptRec Dept%ROWTYPE;
EmpRec Emp%ROWTYPE;
Cv1 CURROR_VARIBAL_PKG.
Cv2 CURROR_VARIBAL_PKG.
DBMS_OUTPUT.PUT_LINE('游标变量强类型定义应用');
CURROR_VARIBAL_PKG.OpenDeptVar(cv1, 1, 30);
FETCH cv1 INTO DeptR
WHILE cv1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname);
FETCH cv1 INTO DeptR
CLOSE cv1;
DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用');
CURROR_VARIBAL_PKG.OpenDeptvar(cv2, 2, dept_name =& 'A%');
FETCH cv2 INTO DeptR
WHILE cv2%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname);
FETCH cv2 INTO DeptR
DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用―dept表');
UP_OpenCurType(cv2, 'D');
FETCH cv2 INTO DeptR
WHILE cv2%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(deptrec.deptno||':'||deptrec.dname);
FETCH cv2 INTO
DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用―emp表');
UP_OpenCurType(cv2, 'E');
FETCH cv2 INTO EmpR
WHILE cv2%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(emprec.empno||':'||emprec.ename);
FETCH cv2 INTO
CLOSE cv2;
----------运行结果-------------------
游标变量强类型定义应用
10:ACCOUNTING
20:RESEARCH
游标变量弱类型定义应用
10:ACCOUNTING
游标变量弱类型定义应用―dept表
10:ACCOUNTING
20:RESEARCH
40:OPERATIONS
60:Developer
游标变量弱类型定义应用―emp表
7369:SMITH
7499:ALLEN
7566:JONES
7654:MARTIN
7698:BLAKE
7782:CLARK
7788:SCOTT
7844:TURNER
7876:ADAMS
7900:JAMES
7934:MILLER
PL/SQL procedure successfully completed五：包的重载（子程序重载）5.1：重载定义包的重载功能类似于C++中函数的重载功能，即拥有多个同名的子程序，每个同名子程序使用不同的参数。用户可以传递不同的参数来调用同名但参数不同的子程序，此即为包的重载功能。简言之，不管传递什么样的参数，所完成的任务是相同的。假定需要查询部门所在的位置，输入参数部门编号或部门名称都会返回同样的结果。对外部程序而言，似乎是调用的同一个子程序，但其始质调用了不同的子程序，执行了不同的代码。以下情况不能重载a.如果两个子程序的参数仅在名称和类型上不同,这两个程序不能重载。PROCEDURE overloadproc(o_parameter IN NUMBER);PROCEDURE overloadproc(o_parameter OUT NUMBER);IN,OUT 为参数类型,NUMBER 为数据类型.两个过程仅在类型上不同时不能重载。 & & & &b.函数使用不同的返回类型时不能进行重载FUNCTION overloadfunc(f_parameter NUMBER)RETURN DATE;FUNCTION overloadfunc(f_parameter VARCHAR2)RETURN NUMBER; & & & &c.重载子程序的参数的类族必须不同,如由于NUMBER 和INTEGER 属性同一类族,所以不能实现重载。PROCEDURE overloadproc(o_parameter &NUMBER);PROCEDURE overloadproc(o_parameter &INTEGER);5.2：实例PL/SQL 允许对包内子程序和本地子程序进行重载。所谓重载时指两个或多个子程序有相同的名称，但拥有不同的参数变量、参数顺序或参数数据类型。-- *******************************************
-- 创建包说明
名：DEMO_PKG1
-- 功能描述：创建包对子程序重载进行测试
-- WebSite: /huyong
-- ******************************************
CREATE OR REPLACE PACKAGE DEMO_PKG1
DeptRec dept%ROWTYPE;
V_sqlcode NUMBER;
V_sqlerr VARCHAR2(2048);
--两个子程序名字相同,但参数类型不同
FUNCTION query_dept(dept_no IN NUMBER)
RETURN INTEGER;
FUNCTION query_dept(dept_no IN VARCHAR2)
RETURN INTEGER;
END DEMO_PKG1;
-- *******************************************
-- 创建包体
名：DEMO_PKG1
-- 功能描述：创建包对子程序重载进行测试
-- ******************************************
CREATE OR REPLACE PACKAGE BODY DEMO_PKG1
FUNCTION check_dept(dept_no NUMBER)
RETURN INTEGER
deptCnt INTEGER; --指定部门号的部门数量
SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno = dept_
IF deptCnt & 0 THEN
END check_
FUNCTION check_dept(dept_no VARCHAR2)
RETURN INTEGER
deptCnt INTEGER;
SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno=dept_
IF deptCnt & 0 THEN
END check_
FUNCTION query_dept(dept_no IN NUMBER)
RETURN INTEGER
IF check_dept(dept_no) =1 THEN
SELECT * INTO DeptRec FROM dept WHERE deptno=dept_
END query_
FUNCTION query_dept(dept_no IN VARCHAR2)
RETURN INTEGER
IF check_dept(dept_no) =1 THEN
SELECT * INTO DeptRec FROM dept WHERE deptno = dept_
END query_
END DEMO_PKG1;--下面使用重载特性建立包头，包含了重载函数get_sal，以及重载过程fire_employee--包头
CREATEORREPLACE PACKAGE overload IS
FUNCTION get_sal(eno NUMBER)RETURN NUMBER;
FUNCTION get_sal(name VARCHAR2)RETURN NUMBER;
PROCEDURE fire_employee(eno NUMBER);
PROCEDURE fire_employee(name VARCHAR2);
--创建重载特性的包体
对于包中具有重载特性的函数或过程，需要依次对其创建不同的包体，即使用不同的执行代码。
对前面创建的包头，我们对其创建如下包体
通过调用get_sal函数来返回雇员的薪水，可以使用雇员编号或雇员名字作为参数
通过调用fire_employee来解雇雇员，可以使用雇员编号或雇员名字作为参数
CREATE OR REPLACE PACKAGE BODY overload IS
FUNCTION get_sal(eno NUMBER) RETURN NUMBER IS
v_sal emp.sal%TYPE;
SELECT sal INTO v_sal FROM emp WHERE empno =
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20020, 'The Employee is not exist !');
FUNCTION get_sal(name VARCHAR2) RETURN NUMBER IS
v_sal emp.sal%TYPE;
SELECT sal INTO v_sal FROM emp WHERE upper(ename) = upper(name);
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20020, 'The Employee is not exist !');
PROCEDURE fire_employee(eno NUMBER) IS
DELETE FROM emp WHERE empno =
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20020, 'The Employee is not exist !');
PROCEDURE fire_employee(name VARCHAR2) IS
DELETE FROM emp WHERE UPPER(ename) = UPPER(name);
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20020, 'The Employee is not exist !');
--重载子程序的调用
在对使用了重载特性的子程序进行调用时，PL/SQL会自动根据所提供的参数寻找同名且参数相符的子程序来执行其代码
scott@ORCL& var sal_1
scott@ORCL& var sal_2
scott@ORCL& exec :sal_1:=overload.get_sal('king');
scott@ORCL& exec :sal_2:=overload.get_sal(7788);
scott@ORCL& print sal_1 sal_2;
----------
----------
3900六：包的初始化包的初始化，也称之为包的构造过程。即当包被首次使用时，会自动执行其构造过程，并且该构造过程在同一会话内仅仅被执行一次。对于包的初始化，其通常的办法是包体的末尾增加一段匿名SQL代码。如下CREATEORREPLACE PACKAGE BODY package_name
PROCEDURE procedure_name
FUNCTION function_name
Initialization_-- 要运行的初始化代码
END--例如--下面首先声明包头
CREATE OR REPLACE PACKAGE emp_package IS
minsal NUMBER(6, 2);
--定义公共变量minsal，用于存放雇员最低薪水
maxsal NUMBER(6, 2);
--定义公共变量maxsal，用于存放雇员最高薪水
PROCEDURE add_employee(eno NUMBER, name VARCHAR2, salary NUMBER, dno NUMBER);
PROCEDURE upd_sal(eno NUMBER, salary NUMBER);
--对upd_sal过程实现重载
PROCEDURE upd_sal(name VARCHAR2, salary NUMBER);
--下面定义包体
CREATE OR REPLACE PACKAGE BODY emp_package IS
PROCEDURE add_employee(eno NUMBER, name VARCHAR2, salary NUMBER, dno NUMBER) IS
IF salary BETWEEN minsal AND maxsal THEN
INSERT INTO emp (empno, ename, sal, deptno) VALUES(eno, name, salary, dno);
RAISE_APPLICATION_ERROR(-20001, 'The salary is over specified range.');
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20002, 'The employee is exists.');
PROCEDURE upd_sal(eno NUMBER, salary NUMBER) IS
IF salary BETWEEN minsal AND maxsal THEN
UPDATE emp SET sal = salary WHERE empno =
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20003, 'The employee is not exists.');
RAISE_APPLICATION_ERROR(-20001, 'The salary is over specified range.');
PROCEDURE upd_sal(name VARCHAR2, salary NUMBER) IS
IF salary BETWEEN minsal AND maxsal THEN
UPDATE emp SET sal = salary WHERE UPPER(ename) = UPPER(name);
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20004, 'The employee is not exists.');
RAISE_APPLICATION_ERROR(-20001, 'The salary is over specified range.');
SELECT min(sal), max(sal) INTO minsal, maxsal FROM
--初始化公共变量minsal, maxsal
scott@ORCL& exec emp_package.add_employee(1234,'Henry',3500,20);
scott@ORCL& exec emp_package.upd_sal('Henry',3500);
scott@ORCL& exec emp_package.upd_sal('Henry',100);
--当范围超出最高和最小薪水则返回错误信息，且更新失败
BEGIN emp_package.upd_sal('Henry',100); END;
ERROR at line 1:
ORA-20001: The salary is over specified range.
ORA-06512: at "SCOTT.EMP_PACKAGE", line 34
ORA-06512: at line 1七：前置申明前置声明前置声明指的是在包体内，假定过程A调用了过程B，而B在A之后定义，这样的话，将会收到错误信息。对此，我们可以不改变过程A，B的书写顺序及其代码，而将B事先声明，此之为前置声明。如下面的例子：--未使用前置声明时的代码CREATEORREPLACE PACKAGE BODY forward_pack IS
PROCEDURE award_bonus(...)
cal_rating(...);--在此例中过程cal_rating在过程award_bonus之后定义，这样即为非法调用
PROCEDURE cal_rating(...)
END forward_--使用前置声明后的代码CREATEORREPLACE PACKAGE BODY forward_pack IS
PROCEDURE cal_rating(...)； --在此处增加一行用于声明过程cal_rating，仅仅列出过程名及参数信息
PROCEDURE award_bonus(...)
cal_rating(...);
PROCEDURE cal_rating(...)
END forward_八：函数纯度级别Oracle函数可以在SQL语句中调用，也可以作为表达式的一部分，基于函数的一些特殊性，在包中使用SQL语句调用公共函数时，同样也存在一些限制，其限制主要如下：公用函数不能包含DML语句公用函数不能读写远程包变量对此可以使用纯度级别来现在公用函数的某些操作定义语法 & & & &PRAGMA RESTRICT_REFERENCES(function_name,WNDS[,WNPS][,RNDS][RNPS]); & & & &WNDS:限制函数不能修改数据库(即执行DML操作) & & & &WNPS：限制函数不能修改包变量，即不能给包变量赋值 & & & &RNDS：限制函数不能读取数据库数据(即禁止SELECT操作) & & & &RNPS：限制函数不能读取包变量，即不能将包变量赋值给其它变量--下面的代码创建使用纯度即被的包头
CREATEORREPLACE PACKAGE purity IS
minsal NUMBER(6, 2);--定义公共变量minsal
maxsal NUMBER(6, 2);--定义公共变量maxsal
FUNCTION max_sal RETURN NUMBER;--定义公共函数
FUNCTION min_sal RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(max_sal, WNPS);--指定函数所使用的纯度级别
PRAGMA RESTRICT_REFERENCES(min_sal, WNPS);
--下面的代码创建使用纯度级别的包体
CREATEORREPLACE PACKAGE BODY purity IS
FUNCTION max_sal RETURN NUMBER IS
SELECTmax(sal)INTO maxsal FROM
FUNCTION min_sal RETURN NUMBER IS
SELECTmin(sal)INTO minsal FROM
--创建包体后，收到了如下的错误信息，因为两个公共函数指定了纯度级别为WNPS，而且函数内的代码对变量进行了赋值
scott@ORCL& show errors
Errors for PACKAGE BODY PURITY:
LINE/COL ERROR
-------- -----------------------------------------------------------------
PLS-00452: Subprogram 'MAX_SAL' violates its associated pragma
PLS-00452: Subprogram 'MIN_SAL' violates its associated pragma
--下面使用初始化包的方法来为变量赋值
CREATEORREPLACE PACKAGE BODY purity IS
FUNCTION max_sal RETURN NUMBER IS
RETURN--函数可以读取包初始化后变量的值
FUNCTION min_sal RETURN NUMBER IS
RETURN--函数可以读取包初始化后变量的值
SELECTmin(sal),max(sal)INTO minsal, maxsal FROM--对公共变量进行初始化
--下面调用限定的公用函数
scott@ORCL&
scott@ORCL&
scott@ORCL&exec:minsal:=purity.
scott@ORCL&exec:maxsal:=purity.
scott@ORCL&
----------
----------
5800九：包内游标一致性状态可以在包内定义一个公共游标，该包内的所有子程序调用该游标来实现相应的功能。如何确保子程序调用游标采取顺序一致性性调用，而不会出现获得重复的游标记录，下面给出的例子中说明了包内游标一致性状态的使用。--创建包头，并且定义了一个公共游标，两个公共过程
CREATEORREPLACE PACKAGE pack_cur
CURSOR cur IS
SELECT empno,ename FROM emp ORDERBY
PROCEDURE return1_3
PROCEDURE return4_6
--创建包体
CREATEORREPLACE PACKAGE BODY pack_cur
v_empno emp.empno%TYPE;--定义用于存储游标结果的变量
v_ename emp.ename%TYPE;--定义用于存储游标结果的变量
PROCEDURE return1_3rows
OPEN--在第一个过程中打开游标
DBMS_OUTPUT.PUT_LINE('Empno
FETCH cur INTO v_empno,v_
DBMS_OUTPUT.PUT_LINE(v_empno||'
'||v_ename);
EXITWHEN cur%ROWCOUNT&= 3;--指定游标退出的条件
END return1_3
PROCEDURE return4_6rows IS
DBMS_OUTPUT.PUT_LINE('Empno
FETCH cur INTO v_empno,v_--因为在第一个过程中游标已打开，在此可以直接从游标提取数据
DBMS_OUTPUT.PUT_LINE(v_empno||'
'||v_ename);
EXITWHEN cur%ROWCOUNT&= 6;--指定游标退出的条件
CLOSE--关闭游标
END return4_6
--调用示例及其结果
scott@ORCL&
scott@ORCL&exec pack_cur.return1_3
scott@ORCL&exec pack_cur.return4_6
WARD十：在包内使用自定义类型--创建包头
CREATE OR REPLACE PACKAGE cust_type IS
TYPE emp_tb_type IS TABLE OF emp%ROWTYPE
--定义一个PL/SQL索引表
INDEX BY BINARY_INTEGER;
PROCEDURE read_emp_table(p_emp_table OUT emp_tb_type);
--定义一个过程
--创建包体
CREATE OR REPLACE PACKAGE BODY cust_type IS
PROCEDURE read_emp_table(p_emp_table OUT emp_tb_type) IS
--定义了输出参数的类型为emp_tb_type
i BINARY_INTEGER:=0;
FOR emp_record IN (SELECT * FROM emp)
--提取记录使用FOR循环
p_emp_table(i):=emp_
--将提取的记录存放到PL/SQL索引表
i:= i + 1;
END read_emp_
--下面使用匿名的PL/SQL块来过程来调用包
v_emp_table cust_type.emp_tb_
cust_type.read_emp_table(v_emp_table);
DBMS_OUTPUT.PUT_LINE('An example: '||v_emp_table(3).ename);
An example: WARD转自： http://blog.csdn.net/leshami/article/details/6092097
了这篇文章
类别：┆阅读(0)┆评论(0)}