oracle3968607246.ppt

1、,Oracle 10g数据库应用教程,授课教师： 职务：,第10章 PL/SQL 程序设计,课程描述 介绍Oracle数据库程序设计中经常会用到的5个概念，即游标、过程、函数、包、和触发器。,本章知识点,复合数据类型 游标 过程和函数 子程序和包 触发器,10.1 复合数据类型,PL/SQL 记录（RECORD） 记录有以下特点： 每个记录内可以有许多字段 记录可以赋初值，NOT NULL可以限定记录 无初值的记录为NULL 可以使用保留字 DEFAULT 可以在任意一个块、子程序、或包中的声明部分定义RECORD类型并声明用户子定义的记录。 可以声明并引用嵌套的记录 创建PL/SQL记录 T

2、YPE IS RECORD (字段名 数据类型，字段名 数据类型); 记录变量 记录类型名； 数据类型：可以是PL/SQL的数据类型或%TYPE和%ROWTYPE属性,10.1 复合数据类型,3. 为PL/SQL记录赋值 记录变量.字段名 对记录赋值可以逐个为记录中的字段赋值也可以一次对所有字段值赋值 通过将一个记录赋值给另一个具有相同类型的记录 emp_record1:=emp.record2 2. 使用SELECT INTO 或 FETCH INTO 语句 SELECT ename,job,sal INTO emp_record FROM emp WHERE ename=SMITH; 列名

3、必须按照与记录中字段相同的顺序出现,10.1 复合数据类型,4. %ROWTYPE 为表或试图中的列的集合数据类型。可以定义同表一样的数据类型，而且不必知道数据库中表列的数量和类型与长度 声明语法： DECLARE 记录变量 表名%ROWTYPE 例如：利用%ROWTYPE声明一个记录型变量emp_record,并将从emp 表中选出的数据行存入记录emp_record。 DECLARE emp_record emp%ROWTYPE; BEGIN SELECT * INTO emp_record FROM emp; WHERE END;,10.1 复合数据类型,5. 嵌套记录 PL/SQL 容

4、许声明并引用嵌套记录。也就是说一个记录可以是另一个记录的组件。 TYPE timetype IS RECORD (tminute SMALLINT, thour SMALLINT); TYPE meetingtype IS RECORD (mday DATE, mtime timetype); meeting meetingtype; seminar meetingtype; TYPE partytype IS RECORD (pday DATE, ptime timetype); party partytype;,10.1 复合数据类型,PL/SQL 容许嵌套记录赋值给另一个具有相同数据类型

5、记录 seminar.mtime:=meeting.mtime; 属性不同记录类型的属性之间可以相互赋值 party.ptime:=meeting.mtime;,10.2 游标(CURSOR),游标的基本概念 游标控制语句 游标属性 游标FOR循环,10.2.1 游标的基本概念,游标作用 游标(CURSOR)可以将多条查询记录进行逐行提取，并逐个处理这些数据。是PL/SQL的一种控制结构，ORACLE服务器使用专用SQL工作区来存储处理这些查询记录数据。 游标有两种形式 隐式游标-也叫SQL游标，由ORACLE创建。 显示游标用户自己显示声明。,18.09.2020,Oracle 10g管理及

6、应用,10.2.1 游标的基本概念（续）,隐式游标 隐式游标是指在PL/SQL程序在执行一个SQL查询语句时，Oracle服务器自动创建的未命名的游标。隐式游标是内存中处理此查询语句的工作区域。与显式游标不同的是，隐式游标不需要声明、不能使用OPEN、CLOSE、FETCH 语句，但可以使用游标属性从最近执行的SQL语句中获取记录。 注意：由于SELECT INTO语句只能读取一行数据到记录变量或一组变量，所以隐式游标只能用于只有一行数据需要处理的情况，对于有多行数据需要处理时，只能使用显示游标而不能使用隐式游标。,10.2.1 游标的基本概念（续）,2、显式游标 显式游标对表的行数据进行处理

7、的操作过程，主要包括以下四步：声明游标、打开游标、提取数据和关闭游标。,1声明游标 声明游标就是声明变量，使变量成为指定的PL/SQL控制结构。 2打开游标 在游标声明以后，读取数据之前，必须先打开游标才能使用游标，打开游标使用OPEN语句 3提取数据 提取游标处理表中的各数据行，放到对应结构的变量中，提取数据的命令为FETCH。 4关闭游标 在游标使用完之后，必须要关闭游标。关闭游标使用CLOSE语句。,10.2.2 游标控制语句,1、声明游标。 2、打开游标。 3、读取数据。 4、关闭游标。,10.2.2 游标控制语句（续）,1 声明游标语句： DECLARE CURSOR () IS ;

8、 【例】声明两个游标分别是emp_cursor,dept_cursor DECLARE CURSOR emp_cursor IS SELECT empno,ename FROM emp; CURSOR dept_cursor IS SELECT * FROM dept WHERE deptno=10; BEGIN ,10.2.2 游标控制语句（续）,【例】游标可以使用子查询。 DECLARE CURSOR my_cursor IS SELECT t1.deptno,t1dname,t2.STAFF FROM dept t1, (SELECT deptno,count(*) STAFF FROM

9、 emp GROUP BY deptno) t2 WHERE t1.deptno=t2.deptno AND T2.STAFF=5; 游标作用：游标建立了数据集合，包括部门号，部门名以及工人数不小于5的部门的员工人数,10.2.2 游标控制语句（续）,【例】声明一个带参数的游标MyCur，读取指定类型的用户信息： DECLARE CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType; 注意：参数仅可用于游标的SELECT 语句的输入，在查询中常量出现的位置上出现,1

10、0.2.2 游标控制语句（续）,2 打开游标语句： OPEN () ; 发生一系列动作： 为查询数据动态分配内存 执行查询语句 绑定输入参数，并为参数变量赋值 标识活动集，游标指针指向活动集的第一行 【例】打开游标 OPEN emp_cursor； OPEN my_cursor； OPEN MyCur(1);,10.2.2 游标控制语句（续）,3 游标取值语句。游标取值语句FETCH的语法 结构如下： FETCH INTO ; 一次从活动集中提取一行记录 每执行一次语句后，游标指针指向活动集的下一行 变量列表中的变量要与SELECT语句中的列的数量相同，并数据类型也要一致 【例】在打开的游标M

11、yCur的当前位置读取数据： FETCH MyCur INTO varId, varName;,10.2.2 游标控制语句（续）,【例】利用游标依次检索10个员工的编号和姓名，保存在变量中： SET SERVEROUTPUT ON; DECLARE v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; i NUMBER:=1; CURSOR c1 IS SELECT empno,ename FROM emp; BEGIN OPEN c1; FOR i IN 1.10 LOOP FETCH c1 INTO v_empno, v_ename; DBMS_O

12、UTPUT.PUT_LINE(empno is|v_empno|and ename is|v_ename); END LOOP; END;,10.2.2 游标控制语句（续）,4 关闭游标语句： CLOSE ; 关闭游标后，所以和游标相关的资源全部释放 如果再用还可以再打开。可以多次创建活动集，同时打开游标数由OPEN_CURSORS参数决定，缺省值为50 任何对关闭了的游标操作都会引发INVALID_CLURSOR错误 【例】关闭游标MyCur： CLOSE MyCur;,10.2.2 游标控制语句(续),【例】下面介绍一个完整的游标应用实例： SET ServerOutput ON; DEC

13、LARE -开始声明部分 varId NUMBER; -声明变量,用来保存游标中的用户编号 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType; BEGIN -开始程序体 OPEN MyCur(1); -打开游标,参数为1,表示读取用户类型编号为1的记录 FETCH MyCur INTO varId, varName; -读取当前

14、游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 END; -结束程序体,10.2.2 游标控制语句(续),5使用游标更新数据 在声明游标时，需要应用FOR UPDATE选项，并通过UPDATE语句完成更新数据操作，此时游标声明的语法格式如下所示： CURSOR IS SELECT 语句 FOR UPDATE OF NOWAIT; NOWAIT 作用，如果其他事务锁定了这一行，就返回一个ORACLE 错误 FOR UPDATE 子句声明游标，则 WHERE CUR

15、RENT OF 可以用于UPDATE 或 DELETE 语句中,10.2.2 游标控制语句(续),例如：利用游标检索部门号为30的每一个员工，将工资提高10% DECLARE var_sal emp.sal%TYPE; CURSOR sal_cursor(var_deptno number) IS SELECT sal FROM emp WHERE deptno=var_deptno FOR UPDATE OF sal NOWAIT; BEGIN OPEN sal_cursor(30); LOOP FETCH sal_cursor INTO var_sal; EXIT WHEN sal_cur

16、sor%NOTFOUND OR sal_cursor%NOTFOUND IS NULL; UPDATE emp SET sal=var_sal*1.1 WHERE CURRENT OF sal_cursor; END LOOP; CLOSE sal_cursor ; END;,9.4.2 游标的属性操作,在游标的使用过程中，经常需要应用到游标的四个属性，以确定游标当前和总体状态 ：,10.2.3 游标属性,（1）%ISOPEN属性 【例】下面的代码演示当使用未打开的游标时，将会出现错误： /* 打开显示模式 */ SET ServerOutput ON; DECLARE varName VAR

17、CHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType; BEGIN -开始程序体 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用

18、户名: | varName); END;,10.2.3 游标属性(续),【例】修改上面的程序，在使用游标之前，调用%ISOPEN属性判断游标是否打开。 SET ServerOutput ON; DECLARE varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType; B

19、EGIN -开始程序体 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(2); END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 END;,10.2.3 游标属性(续),（2）%FOUND属性和%NOTFOUND属性 【例】%FOUND属性可以循环执行游标读取数据： /* 打开显示模式 */ SET ServerOutput ON; DECL

20、ARE -开始声明部分 varName VARCHAR2(50); -保存游标中的用户名 varId NUMBER; -保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;,10.2.3 游标属性(续),BEGIN IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; FETCH MyCur INTO varId, varName; -读

21、取当前数据 WHILE MyCur%FOUND -如果当前游标有效，则执行循环 LOOP dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 END LOOP; CLOSE MyCur; -关闭游标 END;,10.2.3 游标属性(续),（3）%ROWCOUNT属性 【例】只读取前2行记录： /* 打开显示模式 */ SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(5

22、0); -用来保存游标中的用户名 varuserId NUMBER; -用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;,10.2.3 游标属性(续),BEGIN -开始程序体 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; FETCH MyCur INTO varuserId, varName; -读取当前游标位置的数据

23、WHILE MyCur%FOUND -如果当前游标有效，则执行循环 LOOP dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 IF MyCur%ROWCOUNT = 2 THEN EXIT; END IF; FETCH MyCur INTO varuserId, varName; -读取当前游标位置的数据 END LOOP; CLOSE MyCur; -关闭游标 END; -结束程序体,【例】PL/SQL记录可以与游标结合使用,SET ServerOutput ON; -打开显示模式 DECLARE -开始声明部分

24、 /* 声明记录类型 */ TYPE User_Record_Type IS RECORD ( UserId Users.UserId%Type, UserName Users.UserName%Type); var_UserRecord User_Record_Type;-定义记录变量 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;,【例】PL/SQL记录可以与游标结合使用(续),BEGIN IF M

25、yCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; LOOP FETCH MyCur INTO var_UserRecord; -读取当前游标位置的数据到记录变量var_UserRecord EXIT WHEN MyCur%NOTFOUND; -游标指向结果集结尾时退出循环 dbms_output.put_line(用户编号: | var_UserRecord.UserId |, 用户名: | var_UserRecord.UserName); END LOOP; CLOSE MyCur; END;,10.2.4 游标FOR循环 (续),【例】典型游

26、标FOR循环的例子： /* 打开显示模式 */ SET ServerOutput ON; DECLARE CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType; BEGIN FOR var_UserRecord IN MyCur(1) LOOP /* 显示保存在记录变量var_UserRecord中的数据 */ dbms_output.put_line(用户编号: | var_UserRecord.UserId |, 用户名: | var_UserRecord.Us

27、erName); END LOOP; END;,10.2.4 游标FOR循环 (续),游标FOR循环语法如下： FOR 记录类型变量IN 游标名LOOP END LOOP; 游标式的FOR循环包含了多种语句功能： 声明了%ROWTYPE 记录类型变量 隐式打开游标 从活动集循环获取数据行，当提取最后一行后，自动终止循环 当所有行数据处理完，关闭游标,10.2.4 游标FOR循环 (续),例如：利用游标FOR循环依次检索emp表中在部门编号30中的雇员姓名信息。 SET ServerOutput ON; DECLARE CURSOR emp_cursor IS SELECT ename.dept

28、no FROM emp; BEGIN FOR emp_record IN emp_cursor LOOP IF emp_record.deptno=30 THEN DBMS_OUTPUT.PUT_LINE(ename is|emp_record.ename); END IF; END LOOP; END;,10.2.4 游标FOR循环 (续),例如：游标FOR循环可以直接使用SELECT语句。修改上面例题。 SET ServerOutput ON; BEGIN FOR emp_record IN (SELECT ename,deptno FROM emp) LOOP IF emp_record

29、.deptno=30 THEN DBMS_OUTPUT.PUT_LINE(ename is|emp_record.ename); END IF; END LOOP; END;,10.2.4 游标FOR循环 (续),例如：利用游标检索部门号为30的每一个员工，将工资提高10% DECLARE CURSOR sal_cursor(var_deptno number) IS SELECT sal FROM emp WHERE deptno=var_deptno FOR UPDATE OF sal NOWAIT; BEGIN FOR emp_record IN sal_cursor(30) LOOP

30、UPDATE emp SET sal=emp_record.sal*1.1 WHERE CURRENT OF sal_cursor; END LOOP; COMMIT; END;,10.3过程和函数,PL/SQL程序块有两类： 匿名块以DECLARE、BEGIN开始，每次提交时都编译，不存储在数据库中，也不能直接从其他的PL/SQL块中调用. 命名块存储在数据库中，预编译。可以在PL/SQL块中调用。包括：过程和函数, 运行方式类似其他3GL的过程和函数 例如：一个向员工信息表emp插入一条新记录的过程。该记录包括员工编号、姓名、工作、上司编号、雇佣时间、工资、奖金、和部门编号。 CREATE

31、 OR REPLACE PROCEDURE hirenewemployee ( p_empno emp.empno%TYPE, p_ename emp.ename%TYPE, p_job emp.job%TYPE, p_mgr emp.mgr%TYPE, p_hiredate emp.hiredate%TYPE,10.3过程和函数 (续),p_sal emp.sal%TYPE, p_comm m%TYPE, p_deptno emp.deptno%TYPE) AS BEGIN INSERT INTO emp(empno,ename,job,mgr, hiredate,sal,comm,dept

32、no) VALUES(p_empno,p_ename,p_job,p_mgr, p_hiredate,p_sal,p_comm,p_deptno); END hirenewemployee; 在其他PL/SQL块中调用： BEGIN hirenewemployee(7439,Brush,Analyst,7566, 01-9月-01,3000,NULL,20); END;,10.3.1 过程,CREATE PROCEDURE语句来创建过程： CREATE OR REPLACE PROCEDURE ( IN | OUT | IN OUT , IN | OUT | IN OUT ) IS | AS

33、BEGIN END ;,10.3.1 过程(续),【例】创建示例过程ResetPwd，此过程的功能是将表Users中指定用户的密码重置为111111： CREATE OR REPLACE PROCEDURE UserMan.ResetPwd ( UserId IN NUMBER ) AS BEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = UserId; END; 在SQL*PLUS中执行过程： SQL UserMan.ResetPwd(1001);,10.3.2 函数,【例】编写一个员工应交的个人所得税金的函数，个人所得税等于本人工资

34、的8%。 CREATE OR REPLACE FUNCTION tax(p_empno IN NUMBER) RETURN NUMBER IS v_sal NUMBER; v_returnvalue NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=p_empno; v_returnvalue:=v_sal*0.08; RETURN v_returnvalue; END tax;,10.3.2 函数(续),【例】PL/SQL块调用tax函数。 DECLARE CURSOR c_employee IS SELECT empno,en

35、ame FROM emp; BEGIN FOR v_emprecord IN c_employee LOOP -Output all the employees tax DBMS_OUTPUT.PUT_LINE(v_emprecord.ename| tax is| TO_CHAR(tax(v_emprecord.empno)|.); END LOOP; END;,10.3.2 函数(续),CREATE FUNCTION语句来创建函数： CREATE OR REPLACE FUNCTION ( IN | OUT | IN OUT ， IN | OUT | IN OUT ) RETURN IS |

36、 AS BEGIN RETURN END ;,10.3.2 函数(续),【例】编写一个员工应交的个人所得税金的函数，当工资低于800元时，个人所得税为0，当工资在800至1499元之间时，个人所得税为工资的5%，当工资在1500至1999元之间时，个人所得税为为工资的10%，当工资在2000至2999元之间时，个人所得税为为工资的15%，当工资在3000时，个人所得税为为工资的20% 。 CREATE OR REPLACE FUNCTION taxinfo(p_empno IN NUMBER) RETURN NUMBER IS v_sal NUMBER; v_returnvalue NUMBE

37、R; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=p_empno;,10.3.2 函数(续),IF v_sal=800 AND v_sal=1500 AND v_sal=2000 AND v_sal=3000 THEN v_returnvalue:=v_sal*0.20; RETURN v_returnvalue; ENDIF; END taxinfo;,10.3.2 函数(续),【例】PL/SQL块调用tax函数。 DECLARE CURSOR c_employee IS SELECT empno,ename FROM emp; BEGI

38、N FOR v_emprecord IN c_employee LOOP -Output all the employeestax DBMS_OUTPUT.PUT_LINE(v_emprecord.ename| tax is| TO_CHAR(taxinfo(v_emprecord.empno)|.); END LOOP; END;,10.3.2 函数(续),【例】下面介绍一个示例函数GetPwd，此函数的功能是在表Users中根据指定的用户名返回该用户的密码信息： CREATE OR REPLACE FUNCTION UserMan.GetPwd ( name IN Users.UserNa

39、me%Type ) RETURN Users.UserPwd%Type AS outpwd Users.UserPwd%Type; BEGIN SELECT UserPwd INTO outpwd FROM Users WHERE UserName=name; RETURN outpwd; END;,10.3.2 函数(续),【例】用查询语句调用UserMan.GetPwd函数。 SQLSELECT UserMan.GetPwd(Admin) 密码 2 FROM dual; 【例】用PL/SQL块调用UserMan.GetPwd函数 SQLDECLARE 2 v_pwd user.pwd%TY

40、PE; 3 BEGIN 4 SET v_pwd:= UserMan.GetPwd(Admin) 5 DBMS_OUTPUT.PUT_LINE(v_pwd); 6 END; 7 /,10.3.2 函数(续),删除过程和函数 DROP PROCEDURE DROP FUNCTION 过程函数的参数传递 1、参数模式 何为形参和实参？ 形参 -过程函数中声明的参数 实参 在PL/SQL块中声明的变量，包含了过程函数被调用时传递给过程和函数的值，当调用过程和函数时形参被赋予实参值 IN 默认,过程被调用时，实参的值将传入该过程形参，在过程内不。能再赋值,该值只读不能修改，调用结束时返回调用环境，实参没

41、有改变。 OUT 过程被调用时，实参的值不能传入该过程形参，形参在过程体内必须被赋值,调用结束时返回调用环境,形参的内容将赋予对应的实参，形参具有读写性。 IN OUT 为IN 和OUT 的组合,10.3.2 函数(续),2、形参和实参之间传递数值 按值传递时-实参的值将赋予对应的形参(OUT,IN OUT), 按引用传递时-一个指向实参的指针将被传递的值到对应的形参(IN) 3、对形参的约束 过程函数中声明的形参的数据类型不能指定长度和精度。应为这些约束可以从实参中获得。 过程与函数比较 通过设置OUT参数，过程和函数都可以返回一个以上的值 过程和函数都具有声明、执行、异常处理部分 都可以使

42、用位置或名称对应调用过程和函数 函数可以在表达式和SQL语句中调用，过程不行 一般来说：返回一个值用函数，一个以上用过程,10.4 子程序和包,10.4.1 包的概念 10.4.2 包的声明 10.4.3 包体 10.4.4 重载封装子程序 10.4.5 包的管理 10.4.6 系统预定义包,18.09.2020,Oracle 10g管理及应用,10.4.1 包的概念,在Oracle中，对于逻辑上相关的类型、变量及子程序等可以集成在一起，组成命名的PL/SQL程序块，这种特殊的程序块称为包。使用包可以有以下优点： （1）有效地隐藏信息 （2）实现集成化模块程序设计 （3）有利于PL/SQL程序

43、的维护和升级 包有两个独立的部分：包的声明（包头）和包体。将分别存储在数据字典中。包头在包中是必不可少的组成部分，但包体有时可以不出现。在包中所有的子程序和游标等必须在包头中进行声明，然后，再在包体中使用。,10.4.2 包的声明,CREATE PACKAGE语句来创建包的说明部分： CREATE OR REPLACE PACKAGE IS | AS , , END ; 包头通常用来存储一些共享变量，实现对静态数据值的引用。在编译过程中，由于先进行包头的编译，所以如果包头编译不成功，则包体必定不能编译成功，只有包头和包体全部都编译成功后包才能使用。,10.4.2 包的声明(续),【例】创建包头

44、，它包含有关包的内容信息。然而，该部分中不包扩任何子程序代码。 CREATE OR REPLCACE PACKAGE employeepackage AS -类型定义 TYPE emprectype IS RECORD ( empno NUMBER(4), salary NUMBER ); -变量定义 p1 VARCHAR2(20); TYPE t_departmentnotable IS TABLE OF dept.deptno%TYPE INDEX BY BINARY_INTEGER; -游标定义 CURSOR order_sal RETURN emprectype; -聘用员工子程序定义

45、 PROCEDURE hireemployee(p_empno emp.empno%TYPE, p_ename emp.ename%TYPE, p_job emp.job%TYPE, p_mgr emp.emgr%TYPE, p_hiredate emp.hiredate%TYPE,10.4.2 包的声明(续),p_sal emp.sal%TYPE, p_comm m%TYPE, p_deptno emp.deptno%TYPE); -解雇员工子程序定义 PROCEDURE fireemployee(p_empno emp.empno%TYPE); -解雇员工子程序引起的异常定义 e_empl

46、oyeenothired EXCEPTION; -统计函数定义 FUNCTION count_emp(p_deptno emp.deptno%TYPE)RETURN INTEGER; -返回一个包含所有部门的PL/SQL表 PROCEDURE deptmentlist(p_depts OUT t_departmentnotable,p_numdepartments IN OUT BINARY_INTEGER); END employeepackage; /,10.4.2 包的声明(续),【例】下面介绍一个示例创建程序包MyPack，它包含前面2小节中的过程ResetPwd和函数GetPwd：

47、CREATE OR REPLACE PACKAGE UserMan.MyPack IS PROCEDURE ResetPwd ( UserId IN NUMBER); FUNCTION GetPwd ( name IN Users.UserName%Type ) RETURN Users.UserPwd%Type; END MyPack;,10.4.2 包的声明(续),包头说明： 1、包元素的位置可以任何顺序出现，但对象必须在 引用前声明。 2、所有类型元素不必都出现。 3、任何过程和函数的声明都必须是预先声明的，预先声明仅仅描述子程序及参数，而不包括代码。,CREATE PACKAGE BO

48、DY语句来创建包体部分： CREATE PACKAGE BODY IS | AS END ;,10.4.3 包体,例如：创建包体，包体是一个独立于包头的数据字典对象，包体只能在包头完成编译之后才能进行编译。 CREATE OR REPLACE PACKGAE BODY employeepackage AS -游标定义 CURSOR order_sal RETURN emprectype IS SELECT empno,sal FROM emp ORDER BY sal;,10.4.3 包体(续),-招聘雇员子程序代码 PROCEDURE hireEmployee(p_empno emp.emp

49、no%TYPE, p_ename emp.ename%TYPE, p_job emp.job%TYPE, p_mgr emp.emgr%TYPE, p_hiredate emp.hiredate%TYPE, p_sal emp.sal%TYPE, p_comm m%TYPE, p_deptno emp.deptno%TYPE) IS BEGIN INSERT INTO emp(empno,ename,job,mar,hirdate,sal,comm,deptno) VALUES(p_empno,P_ename,p_job,p_mgr,p_hiredate,p_sal,p_comm,p_dept

50、no); END hireEmployee;,10.4.3 包体(续),-解雇雇员子程序代码 Procedure FireEmployee(p_empNO,emp.empno%TYPE) IS BEGIN DELETE FROM emp WHERE empno=p_empno; -Check to see if the DELETE operatiojn was -successful. If it didnt match any rows.raise an error. IF SQL%NOTFOUND THEN RAISE e_employeenothired; END IF; END fi

51、reemployee;,10.4.3 包体(续),-统计某部门的员工人数子程序代码 FUNCTION count_emp(p_deptno emp.deptno%TYPE) RETURN INTEGER IS num INTEGER; BEGIN SELECT COUNT(*) INTO num FROM emp WHERE deptno=p_deptno; RETURN num; END count_emp;,10.4.3 包体(续),-返回一个所有部门的PL/SQL表子程序代码PROCEDURE departmentlist(p_depts OUT t_departmentnotable,

52、 p_numdepartments INOUT BINARY_INTEGER) IS v_deopartmentno dept.deptno%TYPE; CURSOR c_departments IS SELECT deptno FROM dept;,10.4.3 包体(续),BEGIN p_numdepartments:=0; OPEN c_departments; LOOP FETCH c_departments INTO v_departmentno; EXIT WHEN c_departments%NOTFOUND; p_numdepartments:=p_numdepartments

53、+1; p_depts(p_numdepartments):=v_departmentsno; END LOOP; END departmentlist; END employeepackage;,10.4.3 包体(续),10.4.3 包体 (续),【例】下面创建程序包MyPack的包体体部分： CREATE PACKAGE BODY UserMan.MyPack IS PROCEDURE ResetPwd ( UserId IN NUMBER) AS BEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = UserId; END; FU

54、NCTION GetPwd ( name IN Users.UserName%Type ) RETURN Users.UserPwd%Type AS outpwd Users.UserPwd%Type; BEGIN SELECT UserPwd INTO outpwd FROM Users WHERE UserName=|name|; RETURN outpwd; END; END MyPack;,10.4.4 重载封装子程序,重载封装子程序 在包的内部，过程和函数可以被重载（overloading)，可以有一个以上相同名称但参数不同的过程和函数。 例如：假设我们使用被解雇员工的编号或姓名从e

55、mp表中删除该员工的信息。对包FireEmployeePackage修改如下： CREATE OR REPLACE PACKAGE FireEmployeePackage AS -解雇雇员子程序引起的异常定义 e_EmployeeNotHired EXCEPTION; -根据雇员编号删除员工子程序定义 Procedure FireEmployee(p_empNO,emp.empno%TYPE)； -根据雇员姓名删除员工子程序定义 Procedure FireEmployee(p_eName,emp.ename%TYPE); END FireEmployeePackage;,10.4.4 重载封

56、装子程序(续),CREATE OR REPLACE PACKAGE BODY FireEmployeePackage AS -根据雇员编号删除员工子程序定义 PROCEDURE FireEmployee(p_empNO,emp.empno%TYPE); BEGIN DELETE FROM emp WHERE empno=p_empno; -Check to see if the DELETE operatiojn was successful. If it didnt match any rows.raise an error. IF SQL%NOTFOUND THEN RAISE e_emp

57、loyeenothired; END IF; END FireEmployee;,10.4.4 重载封装子程序(续),-根据雇员姓名删除员工子程序代码 PROCEDURE FireEmployee(p_eName,emp.ename%TYPE)IS BEGIN DELETE FROM emp WHERE ename=p_Ename; -Check to see if the DELETE operatiojn was successful. If it didnt match any rows.raise an error. IF SQL%NOTFOUND THEN RAISE e_emplo

58、yeenothired; END IF; END FireEmployee;,10.4.4 重载封装子程序(续),-删除雇员编号7973和雇员姓名为Lilly 的两名员工： BEGIN FireEmployeePackage.FireEmployee(7939); END； BEGIN FireEmployeePackage.FireEmployee(Lilly); END；,10.4.4 重载封装子程序(续),重载仍要受到下列限制： 两个子程序的参数仅在模式和名称上不同，则不能重载 PROCEDURE overloadMe(p_TheParameter IN NUMBER); PROCEDURE overloadMe(p_TheParameter OUT NUMBER); 仅两个子程序的返回类型不同，则不能重载 PROCEDURE overloadMeToo RETURN DATE; PROCEDURE overloadMeToo RET