西安邮电大学Oracle大型数据库第10章ppt课件

1、第10章 游标、存储过程和触发器 课程描画引见Oracle数据库程序设计中经常会用到的3个概念，即游标、存储过程和触发器。本章知识点 游标 存储过程管理 触发器管理 游标 游标的根本概念 游标控制语句 游标属性 游标FOR循环 游标的根本概念 游标表示图 游标的根本概念运用显式游标 1声明游标。2翻开游标。3读取数据。4封锁游标。游标的根本概念隐式游标 【例】运用SELECT语句声明隐式游标，从HR.Departments表中读取Department_name字段的值到变量DepName：SET ServerOutput ON;DECLARE DepName HR.Departments.De

2、partment_Name%Type;BEGINSELECT Department_name INTO DepNameFROM HR.DepartmentsWHERE Department_ID=10;dbms_output.put_line(DepName);END;类型描画符游标控制语句 1声明游标语句CURSOR：DECLARE CURSOR () IS;【例】声明一个游标MyCur，读取指定类型的用户信息：DECLARE CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType =

3、 varType;游标控制语句2翻开游标语句OPEN：OPEN () ;【例】翻开游标MyCur，读取类型为1的用户信息：OPEN MyCur(1);游标控制语句3游标取值语句FETCH。游标取值语句FETCH的根本语法构造如下：FETCH INTO ;【例】在翻开的游标MyCur的当前位置读取数据：FETCH MyCur INTO varI,d varName;4封锁游标语句CLOSE：CLOSE ;【例】封锁游标MyCur：CLOSE MyCur;游标控制语句【例】下面引见一个完好的游标运用实例：/* 翻开显示方式 */SET ServerOutput ON; DECLARE -开场声明部

4、分 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; -读取当前游标位置的数据 CLOS

5、E MyCur; -封锁游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据END; -终了程序体游标属性 1%ISOPEN属性 【例】下面的代码演示当运用未翻开的游标时，将会出现错误：/* 翻开显示方式 */SET ServerOutput ON; DECLARE -开场声明部分 varName VARCHAR2(50); -声明变量,用来保管游标中的用户名 varId NUMBER; -声明变量,用来保管游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varT

6、ype NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -开场程序体 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -封锁游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据END; -终了程序体游标属性【例】修正上面的程序，在运用游标之前，调用%ISOPEN属性判别游标能否翻开。/* 翻开显示方式 */SET ServerOutput O

7、N; DECLARE -开场声明部分 varName VARCHAR2(50); -声明变量,用来保管游标中的用户名 varId NUMBER; -声明变量,用来保管游标中的用户编号 -定义游标, 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,

8、 varName; -读取当前游标位置的数据 CLOSE MyCur; -封锁游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据END; -终了程序体游标属性2%FOUND属性和%NOTFOUND属性【例】%FOUND属性可以循环执行游标读取数据：/* 翻开显示方式 */SET ServerOutput ON; DECLARE -开场声明部分 varName VARCHAR2(50); -声明变量,用来保管游标中的用户名 varId NUMBER; -声明变量,用来保管游标中的用户编号 -定义游标, varTyp

9、e为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;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 |, 用户名:

10、 | varName); -显示读取的数据 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 END LOOP; CLOSE MyCur; -封锁游标END; -终了程序体游标属性3%ROWCOUNT属性 【例】只读取前2行记录：/* 翻开显示方式 */SET ServerOutput ON; DECLARE -开场声明部分 varName VARCHAR2(50); -声明变量,用来保管游标中的用户名 varId NUMBER; -声明变量,用来保管游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(v

11、arType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;游标属性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); -显示读取的数据 IF

12、MyCur%ROWCOUNT = 2 THEN EXIT; END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 END LOOP; CLOSE MyCur; -封锁游标END; -终了程序体游标FOR循环 【例】声明记录类型User_Record_Type和定义记录变量var_UserRecord：TYPE User_Record_Type IS RECORD ( UserId Users.UserId%Type, UserName Users.UserName%Type);var_UserRecord User_Record_Type;

13、游标FOR循环 【例】PL/SQL记录可以与游标结合运用：/* 翻开显示方式 */SET ServerOutput ON; DECLARE -开场声明部分/* 声明记录类型 */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, U

14、serName FROM Users WHERE UserType = varType;游标FOR循环 BEGIN -开场程序体 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; LOOP FETCH MyCur INTO var_UserRecord; -读取当前游标位置的数据到记录变量var_UserRecord EXIT WHEN MyCur%NOTFOUND; -当游标指向结果集结尾时退出循环 /* 显示保管在记录变量var_UserRecord中的数据 */ dbms_output.put_line(用户编号: | var_Use

15、rRecord.UserId |, 用户名: | var_UserRecord.UserName); END LOOP; CLOSE MyCur; -封锁游标END; -终了程序体游标FOR循环 【例】典型游标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 /* 显

16、示保管在记录变量var_UserRecord中的数据 */ dbms_output.put_line(用户编号: | var_UserRecord.UserId |, 用户名: | var_UserRecord.UserName); END LOOP;END; -终了程序体10.2存储过程管理 过程 一种根本的存储过程，由过程名、参数和程序体组成。 函数 与过程类似，只是函数有前往值。 程序包 一组相关的PL/SQL过程和函数，由包名、阐明部分和包体组成。 FUNCTION,PROCEDURE和PACKAGE区别 function 和procedure是PL/SQL代码的集合，通常为了完成一个

17、义务。procedure 不需求前往任何值而function将前往一个值，Package是为了完成一个商业功能的一组function和proceudre的集合过程CREATE PROCEDURE语句来创建过程：CREATE OR REPLACE PROCEDURE IS | AS BEGIN END ;参数声明格式： IN | OUT | IN OUT :=过程【例】创建例如过程ResetPwd，此过程的功能是将表Users中指定用户的密码重置为111111：CREATE OR REPLACE PROCEDURE UserMan.ResetPwd( varUserId IN NUMBER)AS

18、BEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = varUserId;END;过程存储过程管理 过程添加存储过程 过程修正存储过程 函数 CREATE FUNCTION语句来创建函数：CREATE OR REPLACE FUNCTION RETURN IS | AS BEGIN RETURN END ;CREATE OR REPLACE PROCEDURE IS | AS BEGIN END ;函数 【例】下面引见一个例如函数GetPwd，此函数的功能是在表Users中根据指定的用户名前往该用户的密码信息：CREATE FUNCTIO

19、N UserMan.GetPwd( name IN Users.UserName%Type )RETURN Users.UserPwd%TypeASoutpwd Users.UserPwd%Type;BEGIN SELECT UserPwd INTO outpwd FROM Users WHERE UserName = name; RETURN outpwd;END;函数 函数管理 函数 添加函数 函数 修正函数 程序包 CREATE PACKAGE语句来创建包的阐明部分：CREATE OR REPLACE PACKAGE IS | AS END ;-可以包括类型、变量、过程、函数和游标的阐明

20、程序包【例】下面引见一个例如创建程序包MyPack，它包含前面2小节中的过程ResetPwd和函数GetPwd：CREATE OR REPLACE PACKAGE UserMan.MyPackISPROCEDURE ResetPwd( UserId IN NUMBER);FUNCTION GetPwd ( name IN Users.UserName%Type )RETURN Users.UserPwd%Type;END MyPack;程序包程序包管理 程序包添加程序包 程序包CREATE PACKAGE BODY语句来创建包体部分：CREATE PACKAGE BODY IS | AS EN

21、D ;程序包【例】下面创建程序包MyPack的包体体部分：CREATE PACKAGE BODY UserMan.MyPackISPROCEDURE ResetPwd( varUserId IN NUMBER)ASBEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = varUserId;END;FUNCTION GetPwd( name IN Users.UserName%Type )RETURN Users.UserPwd%TypeASoutpwd Users.UserPwd%Type;BEGIN SELECT UserPwd INTO

22、 outpwd FROM Users WHERE UserName=|name|; RETURN outpwd;END;END MyPack;程序包 程序包管理查看MyPack程序包体 程序包添加程序包 程序包调用程序包中的过程.调用程序包中的函数：. 【例】调用UserMan.MyPack.GetPwd函数，前往指定用户的密码信息：SET ServerOutput ON;DECLARE varPwd Users.UserPwd%Type;BEGIN varPwd:= UserMan.MyPack.GetPwd(Admin); dbms_output.put_line(varPwd);END;程序包 DROP PACKAGE BODY命令删除程序包体：DROP PACKAGE