Oracle数据库-SQL+PLSQL-Less19-显式游标的使用

1运行下列脚本，建立名为top_sals的数据库表。CREATETABLEtop_salsasselectename,salfromempwhere1=2;假设emp表中每个人的工资（sal）都不一样，从emp表中选出工资最多的n个人，将这些人的名字（ename）、工资插入到top_sals表中2.编写一个PL/SQL匿名块，删除以“TEST”，“EMP0”开头的表3什么是游标？•游标字面理解就是游动的光标。•用数据库语言来描述：游标是映射在结果集中一行数据上的位置实体，有了游标，用户就可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作，例如提取当前行的数据等。5关于游标•对于每一个SQL语句，Oracle服务器都为该语句建立了一个游标。可以分为下列两种形式：–隐式游标:对于每一个DML和PL/SQLSELECT语句的执行，Oracle服务器都缺省地为它创建一个游标。–显式游标:由程序员进行定义和操纵的。6控制显式游标•创建一个命名的SQL数据区DECLARE•打开活动数据集OPEN•将当前行载入到变量中FETCH•对当前存在的行进行检验空么?•如果发现了数据行，使用FETCH语句来获取数据行否•释放活动数据集CLOSE是8指针继续上面的操作，直到数据行为空.Cursor指针关闭游标.9游标的声明•语法：–注意在游标的定义当中，没有INTO子句的定义.–如果需要以某个确定的次序来处理各个行,需要在查询中使用ORDERBY子句.CURSORcursor_nameISselect_statement;10定义游标•例子：DECLARECURSORemp_cursorISSELECTename,deptnoFROMemp;BEGINFORemp_recordINemp_cursorLOOPIFemp_record.deptno=30THENdbms_output.put_line('whendeptnois30,enameis'||emp_record.ename);endif;ENDLOOP;END;/11打开游标•语法：–打开游标的作用是执行查询，并获得活动数据集.–查询不返回数据行是正常的，这并不是一个异常.–进行一次fetch操作后，使用游标属性来检验输出.OPENcursor_name;12从游标中获取（fetch）数据•语法：–将游标的当前行值取到变量中去.–变量应当与游标中的各个变量一一对应.–检验游标中是否存在数据行值.FETCHcursor_nameINTO[variable1,variable2,...]|record_name];13从游标中获取（fetch）数据•例子：DECLARECURSORdept_cursorISSELECTdeptno,dname,locFROMdeptWHEREdeptnoin(10,20);p_emp_cursordept_cursor%ROWTYPE;BEGINopendept_cursor;loopfetchdept_cursorintop_emp_cursor;exitwhendept_cursor%NOTFOUND;dbms_output.put_line(p_emp_cursor.deptno);dbms_output.put_line(p_emp_cursor.dname);dbms_output.put_line(p_emp_cursor.loc);endloop;closedept_cursor;end;/14关闭游标•语法：–在结束处理行后，关闭游标.–如果需要的话，可以重新打开数据行.–一旦一个游标被关闭了，就不能试图从游标中获取数据.CLOSEcursor_name;15显示游标的属性•获得一个游标的状态信息.属性类型描述%ISOPENBoolean如果游标是打开的，那么这个游标的值为TRUE%NOTFOUNDBoolean如果最近的一次fetch操作没有返回一行数据，那么此值为TRUE%FOUNDBoolean如果最近的一次fetch操作还有返回一行数据，那么此值为TRUE。正好与%NOTFOUND的值相反%ROWCOUNTNumber返回的行数的总和16操作–使用循环来控制显式游标中的多个数据行.–每循环一次，进行一次fetch操作–当一次fetch操作没有取到数据时，%NOTFOUND属性将设为TRUE,此时可以跳出循环.17属性–只有当游标在打开的状态下，才可以获取数据行。–在每一次fetch操作前，可以使用%ISOPEN游标属性来检查该游标是否已被打开.•例子：IFNOTemp_cursor%ISOPENTHENOPENemp_cursor;ENDIF;LOOPFETCHemp_cursor...18和%ROWCOUNT属性–使用%ROWCOUNT游标属性来获取游标中数据行数值.–使用%NOTFOUND游标属性来决定游标操作是否要继续执行下去.19游标和记录•可以将游标中的数据行很方便的赋给某个PL/SQL记录.•例子：DECLARECURSORemp_cursorISSELECTempno,enameFROMemp;emp_recordemp_cursor%ROWTYPE;BEGINOPENemp_cursor;LOOPFETCHemp_cursorINTOemp_record;...20循环游标（CursorFORLoops）•语法：–FOR循环游标是处理显式游标的捷径。–open,fetch,和close都暗含在语句里边。–存放数据行的记录是隐式声明的。FORrecord_nameINcursor_nameLOOPstatement1;statement2;...ENDLOOP;21循环游标•循环获取员工信息，直到所有员工的信息都取完.•例子：DECLARECURSORemp_cursorISSELECTename,deptnoFROMemp;BEGINFORemp_recordINemp_cursorLOOPIFemp_record.deptno=30THENdbms_output.put_line('whendeptnois30,enameis'||emp_record.ename);endif;ENDLOOP;END;/22循环游标中使用子查询•不需要显式声明一个游标.•例子BEGINFORemp_recordIN(SELECTename,deptnoFROMemp)LOOP--implicitopenandimplicitfetchoccurIFemp_record.deptno=30THEN...ENDLOOP;--implicitcloseoccursEND;23运行下列脚本，建立名为top_sals的数据库表。CREATETABLEtop_salsasselectename,salfromempwhere1=2;假设emp表中每个人的工资（sal）都不一样，从emp表中选出工资最多的n个人，将这些人的名字（ename）、工资插入到top_sals表中DECLARECURSORemp_cursorISselect*from(selectename,salfromemporderbysaldesc)whererownum=&rownumber;top_empemp_cursor%ROWTYPE;BEGINOPENemp_cursor;LOOPFETCHemp_cursorINTOtop_emp;EXITWHENemp_cursor%NOTFOUND;insertintotop_salsvalues(top_emp.ename,top_emp.sal);ENDLOOP;CLOSEemp_cursor;END;/24匿名块，删除以“TEST”，“EMP0”开头的表declarev_sqlvarchar2(1000);cursorc_nameisselecttable_name,tablespace_namefromuser_tableswheretable_namelike'TEST%'ortable_namelike'EMP0%';beginforf_nameinc_nameloopdbms_output.put_line(f_name.table_name);v_sql:='droptable'||f_name.table_name;executeimmediatev_sql;endloop;end;/