游标、存储过程和触发器 数据库课件

1、第10章 游标、存储过程和触发器,第10章 游标、存储过程和触发器,介绍Oracle数据库程序设计中经常会用到的3个概念，即游标、存储过程和触发器。,第10章 游标、存储过程和触发器,本章学习目标,理解游标的基本概念； 掌握游标的基本操作、属性操作和循环游标； 掌握PL/SQL语言的三种存储过程； 理解触发器的概念，掌握创建和使用触发器的方法；,第10章 游标、存储过程和触发器,本章知识点,游标 存储过程管理 触发器管理,第10章 游标、存储过程和触发器,游标,游标的基本概念 游标控制语句 游标属性 游标FOR循环,第10章 游标、存储过程和触发器,游标的基本概念,游标：游动的光标。游标是映射

2、在结果集中一行数据上的位置实体，有了游标，用户就可以访问结果集中的任意一行数据了。将游标放置到某行后，即可对该行数据进行操作，最常见的操作是提取当前行数据。游标分两种： 显式游标 隐式游标,第10章 游标、存储过程和触发器,游标的基本概念,游标示意图,游标,第10章 游标、存储过程和触发器,游标的基本概念,隐式游标 不需要声明，使用时也不需要执行打开和关闭操作。实际上，就是在Select语句中增加了INTO子句，把结果集自动读取到指定的比变量中。 【例】使用SELECT语句声明隐式游标，从Students表中读取Sname字段的值到变量VSname： DECLARE VSname Studen

3、ts.Sname%Type; BEGIN SELECT Sname INTO VSname FROM Students WHERE Sdept=Automation; dbms_output.put_line(VSname); END;,第10章 游标、存储过程和触发器,游标的基本概念,显式游标 显式游标需要声明，在使用之前需要打开游标，使用完成后要关闭游标。使用显式游标的步骤包括： （1）声明游标。 （2）打开游标。 （3）读取数据。 （4）关闭游标。,第10章 游标、存储过程和触发器,游标控制语句,（1）声明游标语句CURSOR： DECLARE CURSOR () IS ; 【例】声明一

4、个游标MyCur，读取指定类型的用户信息： DECLARE CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;,第10章 游标、存储过程和触发器,游标控制语句,（2）打开游标语句OPEN： OPEN () ; 【例】打开游标MyCur，读取类型为1的用户信息： OPEN MyCur(1);,第10章 游标、存储过程和触发器,游标控制语句,（3）游标取值语句FETCH。游标取值语句FETCH的基本语法结构如下： FETCH INTO ; 【例】在打开的游标MyCu

5、r的当前位置读取数据： FETCH MyCur INTO varId,varName; （4）关闭游标语句CLOSE： CLOSE ; 【例】关闭游标MyCur： CLOSE MyCur;,第10章 游标、存储过程和触发器,游标控制语句,【例】下面介绍一个完整的游标应用实例： /* 打开显示模式 */ SET ServerOutput ON; DECLARE -开始声明部分 varId NUMBER; -声明变量,用来保存游标中的用户编号 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyC

6、ur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType; BEGIN -开始程序体 OPEN MyCur(1); -打开游标,参数为1,表示读取用户类型编号为1的记录 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 END; -结束程序体,第10章 游标、存储过程和触发器,The

7、End,第10章 游标、存储过程和触发器,下 课 啦 ！,第10章 游标、存储过程和触发器,游标属性,（1）%ISOPEN属性 判断游标是否被打开，如果打开，则%ISOPEN等于TRUE，否则等于FALSE。 【例】下面的代码演示当使用未打开的游标时，将会出现错误： /* 打开显示模式 */ SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(va

8、rType 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; -结束程序体,第10章 游标、存储过程和触发器,游标属性,【例】修改上面的程序，在使用游标之前，调用%ISOPEN属性判断游标是否打开。 /* 打开显

9、示模式 */ SET ServerOutput ON; 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 I

10、F; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 END; -结束程序体,第10章 游标、存储过程和触发器,游标属性,（2）%FOUND属性和%NOTFOUND属性 %FOUND属性用来判断游标所在的行是否有效，如果有效，返回TRUE，无效时返回FALSE。 【例】%FOUND属性可以循环执行游标读取数据： /* 打开显示模式 */ SET ServerOutput ON; DECL

11、ARE -开始声明部分 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(1); END IF; FETCH MyCur INTO varId, varNa

12、me; -读取当前游标位置的数据 WHILE MyCur%FOUND -如果当前游标有效，则执行循环 LOOP dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 END LOOP; CLOSE MyCur; -关闭游标 END; -结束程序体,第10章 游标、存储过程和触发器,游标属性,（3）%ROWCOUNT属性 返回到当前位置为止游标读取的记录行数。 【例】只读取前2行记录： /* 打开显示模式 */ SET Server

13、Output ON; DECLARE -开始声明部分 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;,第10章 游标、存储过程和触发器,游标属性,BEGIN -开始程序体 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1);

14、END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 WHILE MyCur%FOUND -如果当前游标有效，则执行循环 LOOP dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 IF MyCur%ROWCOUNT = 2 THEN EXIT; END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 END LOOP; CLOSE MyCur; -关闭游标 END; -结束程序体,第10章 游标、存储过程和触发

15、器,第10章 游标、存储过程和触发器,游标FOR循环,游标FOR循环是显式游标的一种快捷使用方式，它使用FOR循环依次读取结果集中的行数据。 当FOR循环开始时，游标被自动打开（不再需要OPEN语句）； 每循环一次，系统自动读取游标当前行的数据（不需要使用FETCH语句）； 当退出循环时，游标自动关闭（不需要使用CLOSE语句）。,第10章 游标、存储过程和触发器,游标FOR循环,游标FOR循环通常与PL/SQL记录一起使用。 PL/SQL记录（RECORD）是由一组数据构成的逻辑单元，并不保存在数据库中，与变量一样，保存在内存空间中。 使用记录时，需要先定义记录的结构，然后声明记录变量。 定

16、义记录类型的基本语法如下： TYPE IS RECORD (字段声明 ,字段声明 ); 定义记录变量的方法与定义普通变量的方法相同，语法如下： ,第10章 游标、存储过程和触发器,游标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;,第10章 游标、存储过程和触发器,游标FOR循环,【例】PL

17、/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, UserName

18、 FROM Users WHERE UserType = varType;,第10章 游标、存储过程和触发器,游标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(用户编号

19、: | var_UserRecord.UserId |, 用户名: | var_UserRecord.UserName); END LOOP; CLOSE MyCur; -关闭游标 END; -结束程序体,第10章 游标、存储过程和触发器,第10章 游标、存储过程和触发器,游标FOR循环,典型游标FOR循环需要先对游标进行声明，然后才可以使用。典型游标FOR循环的语法说明如下： FOR IN LOOP 语句1； 语句2； 语句n; END LOOP;,第10章 游标、存储过程和触发器,游标FOR循环,带子查询的典型游标FOR循环的语法说明如下： FOR IN LOOP 语句1； 语句2； 语句

20、n; END LOOP;,第10章 游标、存储过程和触发器,游标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 /* 显示保存在记录变量var_UserRecord中的数据 */ dbms_output.put_line(用户编号:

21、| var_UserRecord.UserId |, 用户名: | var_UserRecord.UserName); END LOOP; END; -结束程序体,第10章 游标、存储过程和触发器,游标FOR循环,第10章 游标、存储过程和触发器,游标FOR循环,【例】带子查询游标FOR循环的例子： /* 打开显示模式 */ SET ServerOutput ON; BEGIN -开始程序体 FOR var_UserRecord IN (SELECT UserId,UserName FROM Users WHERE UserType=1) LOOP /* 显示保存在记录变量var_UserRe

22、cord中的数据 */ dbms_output.put_line(用户编号: | var_UserRecord.UserId |, 用户名: | var_UserRecord.UserName); END LOOP; END; -结束程序体,第10章 游标、存储过程和触发器,10.2 存储过程管理,过程 函数 程序包,第10章 游标、存储过程和触发器,过程,CREATE PROCEDURE语句来创建过程： CREATE OR REPLACE PROCEDURE IS | AS BEGIN END ;,第10章 游标、存储过程和触发器,过程,【例】创建示例过程ResetPwd，此过程的功能是将表

23、Users中指定用户的密码重置为111111： CREATE OR REPLACE PROCEDURE ResetPwd ( VUserId IN NUMBER) AS BEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = VUserId; END;,第10章 游标、存储过程和触发器,过程,过程的调用 可以使用EXECUTE命令调用过程。如 EXECUTE ResetPwd(1);-将编号为1的用户密码重置 SELECT UserName,UserPwd FROM Users; 过程的删除 可以使用DROP PROCEDURE命令删除过程

24、。 【例】删除过程ResetPwd。 DROP PROCEDURE ResetPwd,第10章 游标、存储过程和触发器,过程,存储过程管理,第10章 游标、存储过程和触发器,过程,添加存储过程,第10章 游标、存储过程和触发器,过程,修改存储过程,第10章 游标、存储过程和触发器,函数,CREATE FUNCTION语句来创建函数： CREATE OR REPLACE FUNCTION RETURN IS | AS BEGIN RETURN END ;,第10章 游标、存储过程和触发器,函数,【例】下面介绍一个示例函数GetPwd，此函数的功能是在表Users中根据指定的用户名返回该用户的密码

25、信息： CREATE FUNCTION 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;,第10章 游标、存储过程和触发器,函数,函数的调用 SET ServerOutput on; DECLARE varPwd Users.UserPwd%TYPE; BEGIN varPwd:=GetP

26、wd(Admin); dbms_output.put_line(varPwd); End;,第10章 游标、存储过程和触发器,函数,函数的删除 删除函数用DROP语句 语法格式为： DROP FUNCTION 【例】删除函数GetPwd; DROP FUNCTION GetPwd,第10章 游标、存储过程和触发器,函数,函数管理,第10章 游标、存储过程和触发器,函数,添加函数,第10章 游标、存储过程和触发器,函数,修改函数,第10章 游标、存储过程和触发器,程序包,CREATE PACKAGE语句来创建包的说明部分： CREATE OR REPLACE PACKAGE IS | AS EN

27、D ;,第10章 游标、存储过程和触发器,程序包,【例】下面介绍一个示例创建程序包MyPack，它包含前面2小节中的过程ResetPwd和函数GetPwd： CREATE OR REPLACE PACKAGE MyPack IS PROCEDURE ResetPwd ( VUserId IN NUMBER); FUNCTION GetPwd ( name IN Users.UserName%Type ) RETURN Users.UserPwd%Type; END MyPack;,第10章 游标、存储过程和触发器,程序包,程序包管理,第10章 游标、存储过程和触发器,程序包,添加程序包,第10

28、章 游标、存储过程和触发器,程序包,CREATE PACKAGE BODY语句来创建包体部分： CREATE PACKAGE BODY IS | AS END ;,第10章 游标、存储过程和触发器,程序包,【例】下面创建程序包MyPack的包体体部分： CREATE PACKAGE BODY MyPack IS PROCEDURE ResetPwd ( VUserId IN NUMBER) AS BEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = VUserId; END; FUNCTION GetPwd ( name IN Users

29、.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章 游标、存储过程和触发器,程序包,程序包管理查看MyPack程序包体,第10章 游标、存储过程和触发器,程序包,添加程序包,第10章 游标、存储过程和触发器,程序包,调用程序包中的过程 . 调用程序包中的函数： . 【例】调用MyPack.GetPwd

30、函数，返回指定用户的密码信息： SET ServerOutput ON; DECLARE varPwd Users.UserPwd%Type; BEGIN varPwd:=MyPack.GetPwd(Admin); dbms_output.put_line(varPwd); END;,第10章 游标、存储过程和触发器,程序包,程序包中过程的调用方法： 【补充例】调用MyPack. ResetPwd过程，修改指定用户的密码信息： SET ServerOutput ON; DECLARE BEGIN MyPack.ResetPwd(3); END;,第10章 游标、存储过程和触发器,程序包,DRO

31、P PACKAGE BODY命令删除程序包体： DROP PACKAGE BODY UserMan.MyPack; DROP PACKAGE命令删除程序包的说明部分： DROP PACKAGE UserMan.MyPack;,第10章 游标、存储过程和触发器,10.3 触发器管理,触发器的基本概念 创建及使用触发器,第10章 游标、存储过程和触发器,触发器的基本概念,触发器是一种特殊的存储过程，当指定表中的数据发生变化时自动运行。 触发器与普通存储过程的不同之处在于：触发器的执行是由事件触发的，而普通存储过程是由命令调用的。,第10章 游标、存储过程和触发器,触发器的基本概念,按触发事件不同，

32、可分为： INSERT。当指定的表发生插入（INSERT）操作时执行触发器。 UPDATE。当指定的表发生修改（UPDATE）操作时执行触发器。 DELETE。当指定的表发生删除（DELETE）操作时执行触发器。,第10章 游标、存储过程和触发器,触发器的基本概念,按触发时间不同，可分为： BEFORE。在指定的事件发生之前执行触发器。 AFTER。在指定的事件发生之后执行触发器。 按触发级别不同，可分为： 行触发。对触发事件影响的每一行执行触发器。 语句触发。对于触发事件只能触发一次，而且不能访问受触发器影响的每一行的值。,第10章 游标、存储过程和触发器,创建及使用触发器,CREATE T

33、RIGGER语句来创建触发器： CREATE OR REPLACE TRIGGER BEFORE | AFTER ON FOR EACH ROW WHEN ,第10章 游标、存储过程和触发器,创建及使用触发器,【例】创建一个触发器MyTrigger，它的作用是当表USERMAN.UserType中TypeId列的值发生变化时，自动更新表USERMAN.Users中的UserType列的值，从而保证数据的完整性： CREATE OR REPLACE TRIGGER MyTrigger AFTER UPDATE ON UserType FOR EACH ROW BEGIN UPDATE Users SET UserType = :new.TypeId WHERE UserType = :old.TypeId;