oracle3968607246

1、 第第10章章 PL/SQL 程序设计程序设计 本章知识点本章知识点p 复合数据类型复合数据类型 p 游标游标p 过程和函数过程和函数 p 子程序和包子程序和包p 触发器触发器10.1 复合数据类型复合数据类型1.1.PL/SQL PL/SQL 记录（记录（RECORDRECORD）记录有以下特点：1.每个记录内可以有许多字段2.记录可以赋初值，NOT NULL可以限定记录3.无初值的记录为NULL4.可以使用保留字 DEFAULT5.可以在任意一个块、子程序、或包中的声明部分定义RECORD类型并声明用户子定义的记录。6.可以声明并引用嵌套的记录2.2.创建创建PL/SQLPL/SQL记录记

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

3、 SELECT INTO 或或 FETCH INTO FETCH INTO 语句语句 SELECT ename,job,sal INTO emp_record FROM emp WHERE ename=SMITH;列名必须按照与记录中字段相同的顺序出现列名必须按照与记录中字段相同的顺序出现10.1 复合数据类型复合数据类型4. %ROWTYPE4. %ROWTYPE 为表或试图中的列的集合数据类型。可以定义同表一样的数为表或试图中的列的集合数据类型。可以定义同表一样的数据类型，而且不必知道数据库中表列的数量和类型与长度据类型，而且不必知道数据库中表列的数量和类型与长度 声明语法： DECLAR

4、E 记录变量 表名%ROWTYPE 例如：利用%ROWTYPE声明一个记录型变量emp_record,并将从emp 表中选出的数据行存入记录emp_record。 DECLARE emp_record emp%ROWTYPE; BEGIN SELECT * INTO emp_record FROM emp; WHERE END;10.1 复合数据类型复合数据类型5. 5. 嵌套记录嵌套记录 PL/SQL 容许声明并引用嵌套记录。也就是说一个记录可以是另一个记录的组件。TYPE timetype IS RECORD (tminute SMALLINT, thour SMALLINT);TYPE

5、meetingtype IS RECORD (mday DATE, mtime timetype);meeting meetingtype;seminar meetingtype;TYPE partytype IS RECORD (pday DATE, ptime timetype);party partytype; 10.1 复合数据类型复合数据类型pPL/SQL 容许嵌套记录赋值给另一个具有相同数据类型记录 seminar.mtime:=meeting.mtime;p属性不同记录类型的属性之间可以相互赋值 party.ptime:=meeting.mtime; 10.2 游标游标(CURS

6、OR) p游标的基本概念游标的基本概念 p游标控制语句游标控制语句 p游标属性游标属性 p游标游标FORFOR循环循环 10.2.1 游标的基本概念游标的基本概念p游标作用游标作用 游标游标(CURSOR)(CURSOR)可以将多条查询记录进行逐行提取，可以将多条查询记录进行逐行提取，并逐个处理这些数据。是并逐个处理这些数据。是PL/SQLPL/SQL的一种控制结构，的一种控制结构，ORACLEORACLE服务器使用专用服务器使用专用SQLSQL工作区来存储处理这些查工作区来存储处理这些查询记录数据。询记录数据。p游标有两种形式游标有两种形式 隐式游标隐式游标-也叫也叫SQLSQL游标，由游标

7、，由ORACLEORACLE创建。创建。 显示游标显示游标用户自己显示声明。用户自己显示声明。2/21/2022Oracle 10g管理及应用10.2.1 游标的基本概念（续）游标的基本概念（续）1.1.隐式游标隐式游标 隐式游标是指在PL/SQL程序在执行一个SQL查询语句时，Oracle服务器自动创建的未命名的游标。隐式游标是内存中处理此查询语句的工作区域。与显式游标不同的是，隐式游标不需要声明、不能使用不能使用OPEN、CLOSE、FETCH 语句，但可以使用游标属性从最近执行的语句，但可以使用游标属性从最近执行的SQL语语句中获取记录。句中获取记录。 注意：由于SELECT INTO语

8、句只能读取一行数据到记录变量或一组变量，所以隐式游标只能用于只有一行数据需要处理的情况，对于有多行数据需要处理时，只能使用显示游标而不能使用隐式游标。 10.2.1 游标的基本概念（续）游标的基本概念（续）2 2、显式游标、显式游标 显式游标显式游标对表的行数据进行处理的操作过程，主要包括以下四步：声明游标、打开游标、提取数据和关闭游标。 1声明游标 声明游标就是声明变量，使变量成为指定的PL/SQL控制结构。2打开游标在游标声明以后，读取数据之前，必须先打开游标才能使用游标，打开游标使用OPEN语句3提取数据 提取游标处理表中的各数据行，放到对应结构的变量中，提取数据的命令为FETCH。4关

9、闭游标在游标使用完之后，必须要关闭游标。关闭游标使用CLOSE语句。10.2.2 游标控制语句游标控制语句 1 1、声明游标。、声明游标。 2 2、打开游标。、打开游标。 3 3、读取数据。、读取数据。 4 4、关闭游标。、关闭游标。10.2.2 游标控制语句（续）游标控制语句（续）1 1 声明游标语句：声明游标语句：DECLARE CURSOR () IS;【例例】声明两个游标分别是声明两个游标分别是emp_cursor,dept_cursor DECLARE CURSOR emp_cursor IS SELECT empno,ename FROM emp; CURSOR dept_curs

10、or IS SELECT * FROM dept WHERE deptno=10; BEGIN10.2.2 游标控制语句（续）游标控制语句（续）【例例】游标可以使用子查询。游标可以使用子查询。DECLARE CURSOR my_cursor IS SELECT t1.deptno,t1dname,t2.STAFF FROM dept t1, (SELECT deptno,count(*) STAFF FROM emp GROUP BY deptno) t2 WHERE t1.deptno=t2.deptno AND T2.STAFF=5;游标作用：游标作用：游标建立了数据集合，包括部门号，部门

11、名以及工人数不小于5的部门的员工人数10.2.2 游标控制语句（续）游标控制语句（续）【例例】声明一个带参数的游标声明一个带参数的游标MyCurMyCur，读取指，读取指定类型的用户信息：定类型的用户信息：DECLARE CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;注意：注意：参数仅可用于游标的SELECT 语句的输入，在查询中常量出现的位置上出现10.2.2 游标控制语句（续）游标控制语句（续）2 2 打开游标语句：打开游标语句：OPEN () ;发生一

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

13、rMyCur的当前位置读取数据：的当前位置读取数据：FETCH MyCur INTO varId, varName;10.2.2 游标控制语句（续）游标控制语句（续）【例例】利用游标依次检索利用游标依次检索1010个员工的编号和姓名，保存在变量中：个员工的编号和姓名，保存在变量中： 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.1

14、0 LOOP FETCH c1 INTO v_empno, v_ename; DBMS_OUTPUT.PUT_LINE(empno is|v_empno|and ename is|v_ename); END LOOP; END;10.2.2 游标控制语句（续）游标控制语句（续）4 4 关闭游标语句：关闭游标语句：CLOSE ;关闭游标后，所以和游标相关的资源全部释放如果再用还可以再打开。可以多次创建活动集，同时打开游标数由OPEN_CURSORS参数决定，缺省值为50任何对关闭了的游标操作都会引发INVALID_CLURSOR错误【例例】关闭游标关闭游标MyCurMyCur：CLOSE MyC

15、ur;10.2.2 游标控制语句游标控制语句(续续)【例【例】下面介绍一个完整的游标应用实例：下面介绍一个完整的游标应用实例：SET ServerOutput ON; DECLARE -开始声明部分 varId NUMBER; -声明变量,用来保存游标中的用户编号 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -

16、开始程序体 OPEN MyCur(1); -打开游标,参数为1,表示读取用户类型编号为1的记录 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据END; -结束程序体10.2.2 游标控制语句游标控制语句(续续)5 5使用游标更新数据使用游标更新数据 在声明游标时，需要应用FOR UPDATE选项，并通过UPDATE语句完成更新数据操作，此时游标声明的语法格式如下所示： CURSOR IS

17、 SELECT 语句 FOR UPDATE OF NOWAIT; NOWAIT 作用，如果其他事务锁定了这一行，就返回一个ORACLE 错误 FOR UPDATE 子句声明游标，则 WHERE CURRENT OF 可以用于UPDATE 或 DELETE 语句中 10.2.2 游标控制语句游标控制语句(续续)例如：利用游标检索部门号为例如：利用游标检索部门号为3030的每一个员工，将工资提高的每一个员工，将工资提高10%10% DECLARE var_sal emp.sal%TYPE; CURSOR sal_cursor(var_deptno number) IS SELECT sal FRO

18、M 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_cursor%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 9.4.2 游标的属性操作游标的属性操

19、作 在游标的使用过程中，经常需要应用到游标的四个属性，以确定游标当前和总体状态 ：属属 性性 名名说说 明明%ISOPEN逻辑值，判断游标是否已打开，如游标未打开其值为false，否则为true%FOUND逻辑值，判断游标是否指向数据行。如游标当前指向一行数据则返回true，否则为false。本属性常用于控制游标循环的结束。%NOTFOUND逻辑值，判断游标是否没指向数据行。其值是%FOUND属性值的非。本属性常用于控制游标循环的结束。%ROWCOUNT返回游标当前已提取的记录的行数，每成功提取一次数据行其值加110.2.3 游标属性游标属性 （1 1）%ISOPEN%ISOPEN属性属性 【

20、例例】下面的代码演示当使用未打开的游标时，将会出现错误：下面的代码演示当使用未打开的游标时，将会出现错误：/* 打开显示模式 */SET ServerOutput ON; DECLARE varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -开始程序体

21、FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName);END; 10.2.3 游标属性游标属性(续续)【例例】修改上面的程序，在使用游标之前，调用修改上面的程序，在使用游标之前，调用%ISOPEN%ISOPEN属性判断游标是否打开。属性判断游标是否打开。SET ServerOutput ON; DECLARE varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER

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

23、varId |, 用户名: | varName); -显示读取的数据END; 10.2.3 游标属性游标属性(续续)（2 2）%FOUND%FOUND属性和属性和%NOTFOUND%NOTFOUND属性属性【例】%FOUND属性可以循环执行游标读取数据：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50); -保存游标中的用户名 varId NUMBER; -保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELEC

24、T 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; -读取当前数据 WHILE MyCur%FOUND -如果当前游标有效，则执行循环 LOOP dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 FETCH MyCur INTO varId,

25、varName; -读取当前游标位置的数据 END LOOP; CLOSE MyCur; -关闭游标END; 10.2.3 游标属性游标属性(续续)（3 3）%ROWCOUNT%ROWCOUNT属性属性 【例例】只读取前只读取前2 2行记录：行记录：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50); -用来保存游标中的用户名 varuserId NUMBER; -用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS

26、 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; -读取当前游标位置的数据 WHILE MyCur%FOUND -如果当前游标有效，则执行循环 LOOP dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 IF

27、 MyCur%ROWCOUNT = 2 THEN EXIT; END IF; FETCH MyCur INTO varuserId, varName; -读取当前游标位置的数据 END LOOP; CLOSE MyCur; -关闭游标END; -结束程序体【例例】PL/SQL记录可以与游标结合使记录可以与游标结合使用用 SET ServerOutput ON; -打开显示模式 DECLARE -开始声明部分/* 声明记录类型 */TYPE User_Record_Type IS RECORD ( UserId Users.UserId%Type, UserName Users.UserName

28、%Type); var_UserRecord User_Record_Type;-定义记录变量 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;【例例】PL/SQL记录可以与游标结合使用记录可以与游标结合使用(续续) BEGIN IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; LOOP FETCH MyCur INTO var_UserRecord;

29、 -读取当前游标位置的数据到记录变量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循环循环 (续续)【例例】典型游标典型游标FORFOR循环的例子：循环的例子：/* 打开显示模式 */SET ServerOutput ON; DECLARE CURSOR MyCur(var

30、Type 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.UserName); END LOOP;END; 10.2.4 游标游标FOR循环循环 (续续)p游标游标FORFOR循环语法如下：循环语法如下

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

32、OM 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)

33、 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循环循环 (续续)例如：利用游标检索部门号为例如：利用游标检索部门号为3030的每一个员工，将工资提高的每一个员工，将工资提高10%10% DECLARE CURSOR sal_cursor(var_deptno number) IS SELECT sal FROM emp WHERE deptno=var_deptno FOR UPDATE OF sal

34、NOWAIT; BEGIN FOR emp_record IN sal_cursor(30) LOOP UPDATE emp SET sal=emp_record.sal*1.1 WHERE CURRENT OF sal_cursor; END LOOP; COMMIT;END;10.3过程和函数过程和函数 p PL/SQL程序块有两类：程序块有两类： 匿名块匿名块以DECLARE、BEGIN开始，每次提交时都编译，不存储在数据库中，也不能直接从其他的PL/SQL块中调用. 命名块命名块存储在数据库中，预编译。可以在PL/SQL块中调用。包括：过程和函数过程和函数, 运行方式类似其他3GL的过

35、程和函数例如：例如：一个向员工信息表emp插入一条新记录的过程。该记录包括员工编号、姓名、工作、上司编号、雇佣时间、工资、奖金、和部门编号。 CREATE 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

36、.deptno%TYPE) ASBEGIN INSERT INTO emp(empno,ename,job,mgr, hiredate,sal,comm,deptno) VALUES(p_empno,p_ename,p_job,p_mgr, p_hiredate,p_sal,p_comm,p_deptno);END hirenewemployee;在其他在其他PL/SQLPL/SQL块中调用块中调用：BEGIN hirenewemployee(7439,Brush,Analyst,7566, 01-9月-01,3000,NULL,20);END;10.3.1 过程过程pCREATE PROCE

37、DURECREATE PROCEDURE语句来创建过程：语句来创建过程：CREATE OR REPLACE PROCEDURE ( IN | OUT | IN OUT , IN | OUT | IN OUT )IS | AS BEGIN END ;10.3.1 过程过程(续续)【例例】创建示例过程创建示例过程ResetPwdResetPwd，此过程的功能是将表，此过程的功能是将表UsersUsers中指定中指定用户的密码重置为用户的密码重置为111111111111：CREATE OR REPLACE PROCEDURE UserMan.ResetPwd( UserId IN NUMBER )

38、ASBEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = UserId;END;在SQL*PLUS中执行过程：SQL UserMan.ResetPwd(1001);10.3.2 函数【例】编写一个员工应交的个人所得税金的函数，个人所得税等于本人工资的8%。CREATE OR REPLACE FUNCTION tax(p_empno IN NUMBER)RETURN NUMBER ISv_sal NUMBER;v_returnvalue NUMBER;BEGIN SELECT sal INTO v_sal FROM emp WHERE em

39、pno=p_empno; v_returnvalue:=v_sal*0.08; RETURN v_returnvalue;END tax; 10.3.2 10.3.2 函数( (续续) )【例】PL/SQL块调用tax函数。DECLARE CURSOR c_employee IS SELECT empno,ename 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

40、(v_emprecord.empno)|.); END LOOP; END; 10.3.2 10.3.2 函数( (续续) )pCREATE FUNCTIONCREATE FUNCTION语句来创建函数：语句来创建函数：CREATE OR REPLACE FUNCTION ( IN | OUT | IN OUT ， IN | OUT | IN OUT ) RETURN IS | AS BEGIN RETURN END ;10.3.2 10.3.2 函数( (续续) )【例】编写一个员工应交的个人所得税金的函数，当工资低于800元时，个人所得税为0，当工资在800至1499元之间时，个人所得税为

41、工资的5%，当工资在1500至1999元之间时，个人所得税为为工资的10%，当工资在2000至2999元之间时，个人所得税为为工资的15%，当工资在3000时，个人所得税为为工资的20% 。CREATE OR REPLACE FUNCTION taxinfo(p_empno IN NUMBER)RETURN NUMBER ISv_sal NUMBER;v_returnvalue NUMBER;BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=p_empno;10.3.2 10.3.2 函数( (续续) )IF v_sal=800 AND v_sa

42、l=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 10.3.2 函数函数( (续续) )【例】PL/SQL块调用tax函数。 DECLARE CURSOR c_employee IS SELECT empno,ename FROM emp; BEGIN FOR v_emprecord IN c_employee LOOP -Output all the employeestax DBMS_OUTPUT.PUT_LI

43、NE(v_emprecord.ename| tax is| TO_CHAR(taxinfo(v_emprecord.empno)|.); END LOOP; END;10.3.2 10.3.2 函数函数( (续续) )【例例】下面介绍一个示例函数下面介绍一个示例函数GetPwdGetPwd，此函数的功能是在表，此函数的功能是在表UsersUsers中根据指定的用户名返回该用户的密码信息：中根据指定的用户名返回该用户的密码信息：CREATE OR REPLACE FUNCTION UserMan.GetPwd( name IN Users.UserName%Type )RETURN Users.

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

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

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

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

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

49、能使用。 10.4.2 包的声明(续)【例例】创建包头，它包含有关包的内容信息。然而，该部分中不包扩任何子程创建包头，它包含有关包的内容信息。然而，该部分中不包扩任何子程序代码。序代码。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_IN

50、TEGER;-游标定义 CURSOR order_sal RETURN emprectype;-聘用员工子程序定义 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 PROCEDU

51、RE fireemployee(p_empnofireemployee(p_empno emp.empno%TYPEemp.empno%TYPE););-解雇员工子程序引起的异常定义e_employeenothirede_employeenothired EXCEPTION; EXCEPTION;-统计函数定义FUNCTION FUNCTION count_emp(p_deptnocount_emp(p_deptno emp.deptno%TYPE)RETURNemp.deptno%TYPE)RETURN INTEGER; INTEGER;-返回一个包含所有部门的PL/SQL表PROCEDUR

52、E PROCEDURE deptmentlist(p_deptsdeptmentlist(p_depts OUT OUT t_departmentnotable,p_numdepartmentst_departmentnotable,p_numdepartments IN OUT BINARY_INTEGER); IN OUT BINARY_INTEGER);END END employeepackageemployeepackage; ;/ /10.4.2 包的声明(续)【例例】下面介绍一个示例创建程序包下面介绍一个示例创建程序包MyPackMyPack，它，它包含前面包含前面2 2小节中的

53、过程小节中的过程ResetPwdResetPwd和函数和函数GetPwdGetPwd：CREATE OR REPLACE PACKAGE UserMan.MyPackISPROCEDURE ResetPwd( UserId IN NUMBER);FUNCTION GetPwd ( name IN Users.UserName%Type )RETURN Users.UserPwd%Type;END MyPack;10.4.2 包的声明(续)包头说明：包头说明：1、包元素的位置可以任何顺序出现，但对象必须在 引用前声明。2、所有类型元素不必都出现。3、任何过程和函数的声明都必须是预先声明的，预先声

54、明仅仅描述子程序及参数，而不包括代码。pCREATE PACKAGE BODYCREATE PACKAGE BODY语句来创建包体部分：语句来创建包体部分：CREATE PACKAGE BODY IS | AS END ;10.4.3 包体例如：创建包体，包体是一个独立于包头的数据字典对例如：创建包体，包体是一个独立于包头的数据字典对象，包体只能在包头完成编译之后才能进行编译。象，包体只能在包头完成编译之后才能进行编译。CREATE OR REPLACE PACKGAE BODY employeepackage AS-游标定义CURSOR order_sal RETURN emprectype

55、 IS SELECT empno,sal FROM emp ORDER BY sal; 10.4.3 包体(续续)-招聘雇员子程序代码招聘雇员子程序代码 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,p_sal emp.sal%TYPE,p_comm m%TYPE,p_deptno emp.deptno%TYPE) ISBEGIN INSERT INTO emp(em

56、pno,ename,job,mar,hirdate,sal,comm,deptno)VALUES(p_empno,P_ename,p_job,p_mgr,p_hiredate,p_sal,p_comm,p_deptno);END hireEmployee;10.4.3 包体(续续)-解雇雇员子程序代码Procedure FireEmployee(p_empNO,emp.empno%TYPE) ISBEGIN DELETE FROM emp WHERE empno=p_empno; -Check to see if the DELETE operatiojn was -successful. I

57、f it didnt match any rows.raise an error. IF SQL%NOTFOUND THEN RAISE e_employeenothired; END IF;END fireemployee;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; RETUR

58、N num;END count_emp;10.4.3 包体(续续)-返回一个所有部门的返回一个所有部门的PL/SQL表子程序代码表子程序代码PROCEDURE departmentlist(p_depts OUT t_departmentnotable, 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; OPE

59、N c_departments; LOOP FETCH c_departments INTO v_departmentno; EXIT WHEN c_departments%NOTFOUND; p_numdepartments:=p_numdepartments+1; p_depts(p_numdepartments):=v_departmentsno; END LOOP;END departmentlist;END employeepackage; 10.4.3 包体(续续)10.4.3 包体 (续续)【例例】下面创建程序包下面创建程序包MyPackMyPack的包体体部分：的包体体部分：C

60、REATE PACKAGE BODY UserMan.MyPackISPROCEDURE ResetPwd( UserId IN NUMBER)ASBEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = UserId;END;FUNCTION GetPwd( name IN Users.UserName%Type )RETURN Users.UserPwd%TypeASoutpwd Users.UserPwd%Type;BEGIN SELECT UserPwd INTO outpwd FROM Users WHERE UserName=|n