Oracle SQL&PLSQL课件：第20章 游标.ppt

单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Oracle SQL&PL/SQL,第,20,章,游标,区分隐式游标和显式游标。,掌握如何创建游标,会编写游标式的,FOR,循环,会编写带有参数的游标,在游标中使用,FOR UPDATE,子句,在游标中使用,WHERE CURRENT OF,子句,本章要点,游标,Oracle,服务器所执行的每一个,SQL,语句都有唯一的游标与之相对应：,隐式游标：为每一个,DML,语句和,PL/SQL,语句都声明一个隐式的游标。,显式游标,:,由程序员声明并命名。,显式游标的功能,活动集,当前行,游标,7369SMITHCLERK,7566JONESMANAGER,7788SCOTTANALYST,7876ADAMSCLERK,7902FORDANALYST,显式游标的功能,能够一行接一行地处理查询返回的行,跟踪当前正在处理的那一行,允许程序员在,PL/SQL,块中手工控制这些显式游标,不能对游标赋值，也不能在表达式中使用游标名,显式游标可以有参数,游标参数可以出现在查询中常量出现的任何位置上,可以将游标参数初始化为默认值,可以将实参的不同值传递到游标，按需要采用或替换默 认值,游标参数的范围对于游标来讲是局部变量,当游标为,OPENED,时，游标参数的值可以用于相关的查询,显式游标的控制,创建一个命名的,SQL,区,DECLARE,标识活动集,OPEN,把当前行载入变量中,FETCH,测试现有的行,EMPTY?,如果找到行则返回到,FETCH,语句,No,释放活动集,CLOSE,Yes,显式游标的控制,打开游标,游标,指针,从游标中提取一行,游标,指针,为空时退出提取,游标,指针,关闭游标,游标的声明,语法,:,在游标声明中不能使用,INTO,子句。,如果需要按指定的次序处理行，可在查询中使用,ORDER,子句。,CURSOR,cursor_name,IS,select_statement;,游标的声明,举例：,DECLARE,CURSOR,emp,_cursor IS,SELECT,empno,ename,FROM,emp,;,CURSOR dept_cursor IS,SELECT*,FROM dept,WHERE,deptno,=10;,BEGIN,.,打开游标,语法：,打开游标来执行查询并标识活动集。,如果没有要返回的行，不会出现异常。,提取后，使用游标的属性检测输出。,OPEN,cursor_name,;,从游标中提取数据,语法,:,检索当前行,把值赋给变量。,包含相同数量的变量。,将每一个变量和相应的列进行位置匹配。,检测游标中是否包含行。,FETCH,cursor_name,INTO,variable1,variable2,.,|record_name,;,从游标中提取数据,举例,FETCH,emp,_cursor INTO v_,empno,v_,ename,;,.,OPEN,defined_cursor;,LOOP,FETCH,defined_cursor,INTO,defined_variables,EXIT WHEN.;,.,-Process the retrieved data,.,END;,关闭游标,语法：,处理完活动集中的数据后，应该关闭游标。,如果需要，可以再次打开该游标。,游标一旦关闭，不可再从游标中提取数据。,CLOSE,cursor_name,;,显式游标的属性,属性类型 描述,%ISOPEN,布尔 如果游标是打开的，其值为,TRUE,%NOTFOUND,布尔,如果,FETCH,语句没有返回记录，,其,值为,TRUE,%FOUND,布尔,如果,FETCH,语句返回一行记录，其值,为,TRUE,；与,%NOTFOUND,相反,%ROWCOUNT,数量返回迄今为止已经从游标中取出的记,录数目,多重提取的控制,使用循环从显式游标中处理多行数据。,每次仅提取一行数据。,使用,%NOTFOUND,属性来检测一个不成功的提取操作。,使用显式游标的属性检测每一次提取是否成功。,%ISOPEN,属性,仅当游标处于打开状态时才可以从中提取数据。,在执行提取操作之前，使用,%ISOPEN,游标属性，检测游标是否已被打开,举例,：,IF NOT,emp,_cursor%ISOPEN THEN,OPEN,emp,_cursor;,END IF;,LOOP,FETCH,emp,_cursor.,%NOTFOUND,和,%ROWCOUNT,属性,使用,%ROWCOUNT,游标属性检测行的确切数量。,使用,%NOTFOUND,游标属性决定何时退出循环。,游标和记录,将提取的行值存入一个,PL/SQL RECORD,中能方便地处理活动集中的行。,举例：,DECLARE,CURSOR,emp,_cursor IS,SELECT,empno,ename,FROM,emp,;,emp,_record,emp,_cursor%ROWTYPE;,BEGINOPEN,emp,_cursor;,LOOP,FETCH,emp,_cursor INTO,emp,_record;,.,语法：,游标,式,的,FOR,循环可以更方便地处理显式游标,。,隐式地打开，提取和关闭游标。,隐式声明记录。,游标式的,FOR,循环,FOR,record_name,IN,cursor_name,LOOP,statement1,;,statement2,;,.,END LOOP;,含有游标的,FOR,循环,举例：,依次检索所有员工,DECLARE,CURSOR,emp,_cursor IS,SELECT,ename,deptno,FROM,emp,;,BEGIN,FOR,emp,_record IN,emp,_cursor LOOP,-implicit open and implicit fetch occur,IF,emp,_record.,deptno,=30 THEN,.,END LOOP;-implicit close occurs,END;,使用子查询的游标,式,FOR,循环,不需声明游标。,举例：,BEGIN,FOR,emp,_record IN(SELECT,ename,deptno,FROM,emp,)LOOP,-implicit open and implicit fetch occur,IF,emp,_record.,deptno,=30 THEN,.,END LOOP;-implicit close occurs,END;,带有参数的游标,语法：,在游标处于打开状态时，执行查询语句，将参数的值传递给游标。,多次打开显式变量，每次活动集都不同。,CURSOR,cursor_name,(,parameter_name,datatype,.),IS,select_statement,;,带有参数的游标,举例：,将部门号和工作名传递给,WHERE,子句。,DECLARE,CURSOR,emp,_cursor,(p_,deptno,NUMBER,p_job VARCHAR2)IS,SELECT,empno,ename,FROM,emp,WHERE,deptno,=p_,deptno,AND job=p_job;,BEGIN,OPEN,emp,_cursor(10,CLERK);,.,FOR UPDATE,子句,语法：,在事务执行期间可以显式锁定以拒绝访问。,在更新或删除行时要锁定该行。,SELECT.,FROM.,FOR UPDATE OF,column_reference,NOWAIT;,FOR UPDATE,子句,举例：,DECLARE,CURSOR,emp,_cursor IS,SELECT,empno,ename,sal,FROM,emp,WHERE,deptno,=30,FOR UPDATE OF,sal,NOWAIT;,WHERE CURRENT OF,子句,语法：,使用游标更新或删除当前行。,首先要在游标中使用,FOR UPDATE,子句锁定行。,使用,WHERE CURRENT OF,子句从显式游标中引用当前行,WHERE CURRENT OF,cursor;,WHERE CURRENT OF,子句,DECLARE,CURSOR,sal,_cursor IS,SELECT,sal,FROM,emp,WHERE,deptno,=30,FOR UPDATE OF,sal,NOWAIT;,BEGIN,FOR,emp,_record IN,sal,_cursor LOOP,UPDATE,emp,SET,sal,=,emp,_record.,sal,*1.10,WHERE CURRENT OF,sal,_cursor;,END LOOP;,COMMIT;,END;,举例：,使用子查询的游标,DECLARE,CURSOR my_cursor IS,SELECT t1.,deptno,t1.,dname,t2.STAFF,FROM dept t1,(SELECT,deptno,count(*)STAFF,FROM,emp,GROUP BY,deptno,)t2,WHERE t1.,deptno,=t2.,deptno,AND t2.STAFF=5;,举例：,小结,游标类型：,隐式游标：为每一个,DML,语句和单行查询都声明一个隐式的游标。,显式游标：用来查询空行、一行或多行。,可以操作显式游标。,使用游标属性可以检测游标的状态。,可以使用游标式的,FOR,循环。,小结,使用带有参数的游标可以返回不同的活动集。,可以定义带有子查询或有相互联系的子查询的游标。,可以使用下面的命令操作显式游标：,FOR UPDATE,子句,WHERE CURRENT OF,子句,