Oracle数据库基础教程第10章人民邮电出版社.ppt

Oracle 10gOracle 10g数据库应用教程数据库应用教程 授课教师：授课教师： 职务：职务： 第10章 游标、存储过程和触发器 课程描述 介绍Oracle数据 库程序设计中经 常会用到的3个 概念，即游标、 存储过程和触发 器。 本章知识点 p 游标 p 存储过程管理 p 触发器管理 游标 p游标的基本概念 p游标控制语句 p游标属性 p游标FOR循环 游标的基本概念 p游标示意图 游标的基本概念 p使用显式游标 （1）说明游标。 （2）打开游标。 （3）读取数据。 （4）关闭游标。 游标的基本概念 p隐式游标 【例】使用SELECT语句声明隐式游标，从 HR.Departments表中读取Department_name字段的 值到变量DepName： SET ServerOutput ON; DECLARE DepName HR.Departments.Department_Name%Type; BEGIN SELECT Department_name INTO DepName FROM HR.Departments WHERE 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 = 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 -开始声明部分 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; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读 取的数据 END; -结束程序体 游标属性 （1）%ISOPEN属性 【例】下面的代码演示当使用未打开的游标时，将会出现错误： /* 打开显示模式 */ SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(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 |, 用户名: | varName); -显示读取的数据 END; -结束程序体 游标属性 【例】修改上面的程序，在使用游标之前，调 用%ISOPEN属性判断游标是否打开。 /* 打开显示模式 */ 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 IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 END; -结束程序体 游标属性 （2）%FOUND属性和%NOTFOUND属性 【例】%FOUND属性可以循环执行游标读取数据： /* 打开显示模式 */ 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(1); END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 WHILE MyCur%FOUND -如果当前游标有效，则执行循环 LOOP dbms_output.put_line(用户编号: | varId |, 用户名: | 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(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 |, 用户名: | varName); -显示读取的数据 IF 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; 游标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, UserName 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_UserRecord.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 /* 显示保存在记录变量var_UserRecord中的数据 */ dbms_output.put_line(用户编号: | var_UserRecord.UserId |, 用户名: | var_UserRecord.UserName); END LOOP; END; -结束程序体 10.2 存储过程管理 p 过程 p 函数 p 程序包 过程 pCREATE PROCEDURE语句来创建过程： CREATE OR REPLACE PROCEDURE IS | AS BEGIN END ; 过程 【例】创建示例过程ResetPwd，此过程的功能 是将表Users中指定用户的密码重置为111111 ： CREATE OR REPLACE PROCEDURE UserMan.ResetPwd ( UserId IN NUMBER) AS BEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = UserId; END; 过程 p存储过程管理 过程 p添加存储过程 过程 p修改存储过程 函数 pCREATE FUNCTION语句来创建函数： CREATE OR REPLACE FUNCTION RETURN IS | AS BEGIN RETURN END ; 函数 【例】下面介绍一个示例函数GetPwd，此函数 的功能是在表Users中根据指定的用户名返回 该用户的密码信息： CREATE FUNCTION UserMan.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; 函数 p函数管理 函数 p添加函数 函数 p修改函数 程序包 pCREATE PACKAGE语句来创建包的说明部分： CREATE OR REPLACE PACKAGE IS | AS END ; 程序包 【例】下面介绍一个示例创建程序包MyPack， 它包含前面2小节中的过程ResetPwd和函数 GetPwd： 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; 程序包 p程序包管理 程序包 p添加程序包 程序包 pCREATE PACKAGE BODY语句来创建包体部分： CREATE PACKAGE BODY IS | AS END ; 程序包 【例】下面创建程序包MyPack的包体体部分： CREATE PACKAGE BODY UserMan.MyPack IS PROCEDURE ResetPwd ( UserId IN NUMBER) AS BEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = UserId; END; 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; END MyPack; 程序包 p 程序包管理查看MyPack程序包 体 程序包 p添加程序包 程序包 p调用程序包中的过程 p调用程序包中的函数： p【例】调用UserMan.MyPack.GetPwd函数，返回指定 用户的密码信息： SET ServerOutput ON; DECLARE varPwd Users.UserPwd%Type; BEGIN varPwd:= UserMan.MyPack.GetPwd(Admin); dbms_output.put_line(varPwd); END; 程序包 pDROP PACKAGE BODY命令删除程序包体： DROP PACKAGE BODY UserMan.MyPack; pDROP PACKAGE命令删除程序包的说明部分 ： DROP