数据库资料OraPlsqlTps2.ppt

应用于Web的面向对象关系型数据库管理系统：Oracle,游标和复合数据类型,2,2,本章目标,游标简介 游标类型 隐式游标 显式游标 游标功能 定义游标 操纵游标 游标属性,3,本章目标（续）,游标 FOR 循环 参数化游标 UPDATE OF 和 CURRENT OF 的游标 PL/SQL 表 PL/SQL 记录,4,游标简介,要处理 SQL 语句， PL/SQL 打开一个称作环境区域的工作区 PL/SQL 使用该区域执行 SQL 语句和存储处理信息 称作“游标”的 PL/SQL 结构允许您命名环境区域，访问其信息及在某些情况下控制其处理过程,5,游标简介（续）,查询返回多行时，可以将游标显式地定义为 在查询所返回的首行之外的处理 对当前正在处理的行进行跟踪 由多行查询返回的行集称作活动集,6,游标类型,PL/SQL 使用两类游标 隐式游标 由 Oracle 自动定义并打开，用于处理每个 SQL语句 最近打开的环境区域被称为“SQL%”游标 显式游标 如果查询返回多行，则可定义显式游标来对当前正在处理的行进行跟踪,7,游标功能,游标名是一个未声明的标识符，仅用于引用查询 不能对游标赋值，也不能在表达式中使用游标名 显式游标可以有参数 游标参数可以出现在查询中常量出现的任何位置上 可以将游标参数初始化为默认值,8,游标的功能（续）,可以将实参的不同值传递到游标，按需要采用或替换默认值 游标参数的范围对于游标来讲是局部变量 当游标为 OPENED 时，游标参数的值可以用于相关的查询,9,定义游标,可以通过在 PL/SQL 程序块的声明部分命名游标或将它与某个查询关联来定义一个游标 CURSOR IS ; 示例 CURSOR emp_cur IS SELECT empno, ename, job, sal FROM emp;,10,操纵游标,可以使用下列语句操纵游标 OPEN FETCH CLOSE 在使用 OPEN、 CLOSE 或 FETCH 语句引用这些语句之前必须对游标进行声明,11,OPEN 语句,初始化或打开游标 在查询返回任何行之前必须打开游标 打开游标将执行查询并识别活动集合 OPEN 示例 OPEN emp_cur;,12,FETCH 语句,一次只能在一个活动集合中检索行 可以重复执行，直到检索到了所有行 FETCH INTO var1, , varN; OR FETCH INTO record_variable; 示例 FETCH emp_cur INTO mrec;,13,CLOSE 语句,关闭游标并让活动集合成为未定义内容 CLOSE ; 示例 CLOSE emp_cur; 只要游标关闭，可以通过使用 OPEN 语句重新打开它,14,显式游标的属性,每个游标有四个属性可以用于访问游标的环境区域 %NOTFOUND %FOUND %ROWCOUNT %ISOPEN 要使用这些属性，只要简单地将它们添加到游标名后即可,15,显式游标的属性（续）,%NOTFOUND 如果因为没有多行而使最后的 FETCH 失败，则判定为 TRUE 如果最后的 FETCH 返回一行，则判定为 FALSE %FOUND 如果最后的 FETCH 返回一行，则判定为 TRUE 如果因为没有多行而使最后的 FETCH 失败，则判定为 FALSE,16,显式游标的属性（续）,%ROWCOUNT 返回当前从活动集合获取的行数 %ISOPEN 如果打开显示游标，则判定为 TRUE 如果关闭显示游标，则判定为 FALSE,17,显式游标的属性（续）,示例 IF emp_cur%ISOPEN THEN FETCH emp_cur INTO m_rec; IF emp_cur%FOUND THEN msr_no := emp_cur%ROWCOUNT; END IF; ELSE OPEN emp_cur; END IF;,18,游标 FOR 循环,将它的循环索引声明为 %ROWTYPE 的记录 隐式地打开游标 从活动集反复获取行的值并传送到记录中的字段 在处理完所有行，并且循环退出时，隐式地关闭游标,19,游标 FOR 循环（续）,为满足与游标名相关的查询的每一行执行循环结构中的语句 游标 FOR 循环用于简化代码编写,20,游标 FOR 循环（续）,DECLARE CURSOR emp_cur IS SELECT empno, ename, job, sal FROM emp; BEGIN FOR mrec IN emp_cur LOOP INSERT INTO temp VALUES( mrec.empno,mrec.ename, mrec.job, mrec.sal); END LOOP; END;,21,参数化的游标,游标也可接收参数 这些参数仅可用于游标的 SELECT 语句的输入 游标参数可以在查询中常量出现的任何位置上出现 可以在 OPEN 语句或游标 FOR 循环中提供参数值,22,参数化的游标（续）,DECLARE CURSOR emp_cur IS SELECT empno, ename, job, sal FROM emp WHERE job = pjob; mjob emp.job%TYPE BEGIN mjob := ,23,游标 FOR UPDATE OF 和 CURRENT OF,CURRENT OF 子句用于在 UPDATE 或 DELETE 语句中以引用游标当前行 必须使用 FOR UPDATE OF 子句声明游标，并在一行上打开并定位 如果游标没有打开，则 CURRENT OF 子句将产生错误,24,游标 FOR UPDATE OF 和 CURRENT OF （续）,如果游标已经打开，但没有处理 FETCH 或最后一个 FETCH 没有返回任何行 ，则将产生预定义例外 NO_DATA_FOUND,25,游标 FOR UPDATE OF 和 CURRENT OF （续）,DECLARE CURSOR emp_cur IS SELECT empno, ename, job, sal FROM emp WHERE job = pjob FOR UPDATE OF sal; BEGIN FOR mrec IN emp_cur LOOP UPDATE emp SET sal = sal * 0.15 WHERE CURRENT OF emp_cur; END LOOP; END;,26,隐式游标的属性,虽然 OPEN、 CLOSE 和 FETCH 语句不能用于操作 SQL% 游标，但属性可以用于访问游标的环境区域 在游标自动打开之前，属性判定为 NULL,27,隐式游标的属性（续）,下列四个游标属性可以用于访问 SQL% 游标的环境区域 SQL%NOTFOUND SQL%FOUND SQL%ROWCOUNT SQL%ISOPEN,28,隐式游标的属性（续）,SQL%NOTFOUND 如果 INSERT 、 UPDATE 或 DELETE 语句没有影响行，则判定为 TRUE ，否则判定为 FALSE SQL%FOUND 逻辑上与 SQL%NOTFOUND 相反 如果 INSERT 、 UPDATE 或 DELETE 影响了一行或多行，则判定为 TRUE ，否则判定为 FALSE,29,隐式游标的属性（续）,SQL%ROWCOUNT 返回 INSERT 、 UPDATE 或 DELETE 语句影响的行数 SQL%ISOPEN Oracle 执行完与其关联的 SQL语句后，将自动关闭隐式游标 对于隐式游标，总是将 SQL%ISOPEN 判定为 FALSE,30,隐式游标的属性（续）,示例 DELETE FROM emp WHERE empno = 7864; IF SQL%NOTFOUND THEN . . END IF;,31,PL/SQL 表,TABLE 类型的对象称为 PL/SQL 表 可以将其可视化为单维的、具有无限多个元素的垂直数组 表大小不受限制，即添加新行时它的大小将增加 PL./SQL 表使用主键，对行进行类似于数组那样的访问,32,PL/SQL 表（续）,PL/SQL 表的声明分为两个步骤 定义一个 TABLE 类型 TYPE IS TABLE OF 列类型 | 变量数据类型 NOT NULL INDEX BY BINARY_INTEGER; 声明该类型的 PL/SQL 表 ;,33,PL/SQL 表（续）,示例 TYPE name_tab_typ IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER; name_table name_tab_typ;,34,PL/SQL 表（续）,可以通过使用具有类似于数组语法的主键值来引用行 (I) 其中 I 是索引下标 示例 name_table(3),35,PL/SQL 表（续）,还可以将 PL/SQL 表达式的值赋予给特定行 (I) := plsql_expression; 示例 name_table(5) := MICKY;,36,PL/SQL 记录,类型为 RECORD 的对象称为 PL/SQL 记录 PL/SQL 记录具有唯一命名的字段，这些字段可以属于不同的数据类型,37,PL/SQL 记录（续）,通过两个步骤对 PL/SQL 记录进行了声明 定义一个 RECORD 类型 TYPE IS RECORD (fieldname1 NOT NULL : fieldnameN NOT NULL); (%TYPE 和 %ROWTYPE 可用于指定 声明该类型的 PL/SQL记录 ;,38,PL/SQL 记录（续）,只有对字段进行了初始化后才可以添加 NOT NULL 约束 示例 TYPE route_rec_type IS RECORD (route_code VARCHAR2(7) NOT NULL := SAN-LOU, first_fare fare.first_fare%TYPE, eco_fare fare.eco_fare%TYPE); route_rec route_rec_type;,39,PL/SQL 记录（续）,可以在声明中初始化记录 示例 TYPE timetype IS RECORD (tsecond SMALLINT :=0, tminute SMALLINT := 0, thour SMALLINT := 0); 声明了 timetype 类型的记录后，字段初始值为零,40,PL/SQL 记录（续）,可以使用点号引用记录中的字段 . 示例 route_rec.eco_fare,41,PL/SQL 记录（续）,可以一次对所有字段赋值 通过将一个记录赋值给另一个具有相同类型的记录 route_rec1 := route_rec2; 使用 SELECT INTO 或 FETCH INTO 语句 SELECT route_code, first_fare, eco_fare INTO route_rec1 FROM fare WHERE route_code = SAN-LOU; （列名称必须按照与记录中字段相同的顺序出现）,42,PL/SQL 记录（续）,一个记录可以是另一个记录的组件 PL/SQL 允许声明并引用嵌套记录,43,PL/SQL 记录（续）,示例 TYPE timetype IS RECORD (tminute SMALLINT, thour SMALLINT); TYPE meetingtype IS RECORD (mday DATE, mtime timetype); meeting meetingtype;