Oracle_PL_SQL讲解2(游标)

PL/SQL2homebackfirstprevnextlast目录•显式游标简介•显式游标属性•游标FOR循环•带参数的游标•使用游标更新数据•使用多个游标3homebackfirstprevnextlast显式游标简介•此部分将讲解：–区分显式游标和隐式游标–在PL/SQL中为什么、何时使用显式游标–如何声明、使用显式游标声明游标打开游标获取数据关闭游标4homebackfirstprevnextlast显式游标简介•通过前面学习我们知道，在PL/SQL中使用SQLSELECT语句一次只能获取一条记录.•如果你需要执行返回多行记录的SELECT语句呢?例如,生成有关所有员工的报表.•要返回多行记录,需要使用显式游标.5homebackfirstprevnextlast游标和上下文区域•Oracle服务为SQL语句分配一块私有内存区域用于保存相关数据，此内存区称为上下文区域（contextarea）.–每个上下文区域(因而也就是每条SQL语句)都有一个关联的游标.–你可把游标看作上下文区域的标签或指针.事实上,游标两者都是.6homebackfirstprevnextlast隐式和显式游标•有两种游标:–隐式游标（Implicitcursors）:Oracle自动为所有SQLDML语句(INSERT,UPDATE,DELETEandMERGE)和单行SELECT语句定义.–显式游标（Explicitcursors）:程序员为多行查询语句声明.可使用显式游标命名上下文区域并访问其中的数据.7homebackfirstprevnextlast隐式游标的限制•EMPLOYEES表有多条记录:DECLAREv_salaryemployees.salary%TYPE;BEGINSELECTsalaryINTOv_salaryFROMemployees;DBMS_OUTPUT.PUT_LINE('Salaryis:'||v_salary);END;8homebackfirstprevnextlast显式游标•通过显式游标,你可从数据库获取多条记录,得到一个可指向每行记录的指针,逐行处理每条记录.•下面是使用显式游标的部分原因:–它是PL/SQL中一次从数据表获取多条记录的唯一方法.–程序语句逐行获取每行记录,程序员可更灵活的处理数据.9homebackfirstprevnextlast显式游标示例•下例使用显式游标获取亚洲国家的国家名和国庆节日期.10homebackfirstprevnextlast显式游标•多行查询返回的记录集称为活动集（activeset）,保存在上下文区域中.•包括符合查询条件的所有记录.11homebackfirstprevnextlast显式游标•把上下文区域(通过cursor命名)看成一个盒子,活动集看作盒子内内容.–获取数据,必须OPEN盒子–每次一行获取所有数据（FETCH）.–数据取完必须CLOSE盒子.12homebackfirstprevnextlast显式游标13homebackfirstprevnextlast显式游标14homebackfirstprevnextlast显式游标•游标的活动集由声明游标时给出的语句决定.•语法:•语法中:–cursor_name是PL/SQL标识符–select_statement是没有INTO子句的SELECT语句15homebackfirstprevnextlast显式游标•游标emp_cursor用于从employees表获取部门30的员工的employee_id和last_name.16homebackfirstprevnextlast显式游标•游标dept_cursor用于获取location_id为1700的所有部门信息.•我们想按部门名称升序排列并处理数据.17homebackfirstprevnextlast显式游标•游标中的SELECT语句可包含联接,分组函数和子查询.•下例要获取至少有两名员工的部门，包括部门名称和员工人数.18homebackfirstprevnextlast显式游标•游标的SELECT语句中不能包含INTO子句，因为后面用FETCH语句获取数据时会有.•如果需要按一定顺序处理记录,则在游标定义查询中使用ORDERBY子句.•游标的SELECT语句可为任意合法的SELECT语句,可包含联接,分组函数和子查询.•如果游标中用到了PL/SQL变量,变量要先于游标定义.19homebackfirstprevnextlast示例•游标中可引用变量DECLAREv_dept_idNUMBER:=90;CURSORcur_empISSELECTsalaryFROMemployeesWHEREdepartment_id=v_dept_id;v_salaryNUMBER;BEGINOPENcur_emp;LOOPFETCHcur_empINTOv_salary;EXITWHENcur_emp%NOTFOUND;dbms_output.put_line(v_salary);ENDLOOP;END;20homebackfirstprevnextlast打开游标•OPEN语句执行游标中的查询语句,得到活动集,把游标指针指向活动集第一行.•OPEN语句应当在PL/SQL块的执行部分.21homebackfirstprevnextlast打开游标•OPEN语句执行以下操作:–1.分配上下文区域(创建盒子)–2.执行游标的SELECT语句,返回结果放入活动集(用数据填充盒子)–3.把游标指针指向活动集第一行.22homebackfirstprevnextlast获取数据•FETCH语句每次从游标获取一行记录.•获取完成，游标指向活动集中下一条记录.•变量v_empno和v_lname,用于存放从游标获取的数据.23homebackfirstprevnextlast获取数据•你现在成功获取了一行记录•但是,部门30有6名员工.你只获取了一行.要获取所有记录，必须使用循环.24homebackfirstprevnextlast获取数据指南•FETCH..INTO子句中变量和SELECT语句中的列个数必须相同,按顺序匹配，数据类型要兼容.•变量和列按位置顺序匹配.•要测试游标是否包含数据.如果fetch没有取回数据,活动集中就不再有记录需要处理，也不会发生错误.最后获取的一行记录会在循环中被重复处理.•可使用%NOTFOUND游标属性作为循环退出条件.25homebackfirstprevnextlast获取数据•下例有什么错误?DECLARECURSORemp_cursorISSELECTemployee_id,last_name,salaryFROMemployeesWHEREdepartment_id=30;v_empnoemployees.employee_id%TYPE;v_lnameemployees.last_name%TYPE;v_salemployees.salary%TYPE;BEGINOPENemp_cursor;LOOPFETCHemp_cursorINTOv_empno,v_lname;EXITWHENemp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_empno||''||v_lname);ENDLOOP;END;26homebackfirstprevnextlast获取数据•部门10只有一名员工.下例执行会有什么结果?DECLARECURSORemp_cursorISSELECTemployee_id,last_nameFROMemployeesWHEREdepartment_id=10;v_empnoemployees.employee_id%TYPE;v_lnameemployees.last_name%TYPE;BEGINOPENemp_cursor;LOOPFETCHemp_cursorINTOv_empno,v_lname;DBMS_OUTPUT.PUT_LINE(v_empno||''||v_lname);ENDLOOP;END;27homebackfirstprevnextlast关闭游标•CLOSE语句关闭游标,释放上下文区域和活动集.•FETCH语句获取处理完数据后，关闭游标.•需要时可重新打开游标.•关闭游标就象清空和关闭盒子,不再能从中取物.28homebackfirstprevnextlast关闭游标指南•游标只有被关闭才能再次被打开.从关闭的游标获取数据会产生INVALID_CURSOR异常.•再次打开游标,会重新执行SELECT语句，上下文区域会被最新获取的数据填充.29homebackfirstprevnextlast显式游标•下例中用游标获取亚洲国家的名称和国庆节日期.30homebackfirstprevnextlast显式游标简介•此部分讲解了：–区分显式游标和隐式游标–在PL/SQL中为什么、何时使用显式游标–如何声明、使用显式游标声明游标打开游标获取数据关闭游标31homebackfirstprevnextlast显式游标属性•此部分将讲解：–使用%ROWTYPE定义记录–在PL/SQL中使用记录变量处理活动集–使用游标属性获取显式游标状态32homebackfirstprevnextlast显式游标属性•使用显式游标可以更灵活的处理数据.•本部分讨论如何更有效的使用显式游标.–游标记录使你可以声明一个变量就能获取游标里的所有字段.–游标属性使你可以获取关于显示游标状态的信息.33homebackfirstprevnextlast游标和记录•下面游标每条记录只取两列:•如果要取6、7、8，甚至20列呢?34homebackfirstprevnextlast游标和记录•下面游标获取员工表的所有列:•代码非常繁琐，是不是?35homebackfirstprevnextlast游标和记录•下面两段代码有何不同?36homebackfirstprevnextlast游标和记录•右侧代码使用%ROWTYPE基于游标声明了一个记录（record）结构.•记录是PL/SQL中的复合数据类型.37homebackfirstprevnextlastPL/SQL记录的结构•记录是复合数据类型,由一组字段（域）组成，每个域有自己的名称和数据类型.•可通过记录名.域名引用域.•通过%ROWTYPE可根据游标声明与游标有相同域的记录.38homebackfirstprevnextlastcursor_name%ROWTYPE的结构39homebackfirstprevnextlast游标和%ROWTYPE•%ROWTYPE便于处理活动集记录，因为一个变量可获取一行数据.DECLARECURSORemp_cursorISSELECT*FROMemployeesWHEREdepartment_id=30;v_emp_recordemp_cursor%ROWTYPE;BEGINOPENemp_cursor;LOOPFETCHemp_cursorINTOv_emp_record;EXITWHENemp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id||'-'||v_emp_record.last_name);ENDLOOP;CLOSEemp_cursor;END;40homebackfirstprevnextlast游标和%ROWTYPEDECLARECURSORemp_dept_cursorISSELECTfirst_name,last_name,department_nameFROMemployeese,departmentsdWHEREe.department_id=d.department_id;v_emp_dept_recordemp_dept_cursor%RO