@H_404_0@在 PL/sql 程序中,对于 处理多行记录的事务经常使用游标来实现。 @H_404_0@其中,游标又分为显式游标和隐式游标,这里重点说明显式游标的用法。
显示游标
显示游标的处理
@H_404_0@四个PL/sql步骤: @H_404_0@1)定义游标 :就是定义一个游标名,以及与其相对应的 SELECT 语句。 @H_404_0@格式: @H_404_0@CURSOR cursor_name[(parameter[,parameter]…)] IS select_statement; @H_404_0@游标参数只能为输入参数,其格式为: @H_404_0@parameter_name [IN] datatype [{:= | DEFAULT} expression] @H_404_0@在指定数据类型时,不能使用长度约束。如 NUMBER(4)、CHAR(10) 等都是错误的。 @H_404_0@2)打开游标 :就是执行游标所对应的 SELECT 语句,将其查询结果放入工作区,并且指针指向工作区的首部,标识游标结果集合。如果游标查询语句中带有 FOR UPDATE 选项,OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。 @H_404_0@格式: @H_404_0@OPEN cursor_name[([parameter =>] value[,[parameter =>] value]…)]; @H_404_0@在向游标传递参数时,可以使用与函数参数相同的传值方法,即位置表示法和名称表示 法。PL/sql程序不能用 OPEN 语句重复打开一个游标。 @H_404_0@3)提取游标数据 :就是检索结果集合中的数据行,放入指定的输出变量中。 @H_404_0@格式: @H_404_0@FETCH cursor_name INTO {variable_list | record_variable }; @H_404_0@4)对该记录进行处理,直到活动集合中没有记录; @H_404_0@5) 关闭游标 :当提取和处理完游标结果集合数据后,应及时关闭游标,以释放该游标所占用的系统资源,并使该游标的工作区变成无效,不能再使用 FETCH 语句取其中数据。关闭后的游标可以使用 OPEN 语句重新打开。 @H_404_0@格式: @H_404_0@CLOSE cursor_name; @H_404_0@注: 定义的游标不能有 INTO 子句@H_404_0@
@H_404_0@游标属性: @H_404_0@%FOUND 布尔型属性,当最近一次读记录时成功返回,则值为 TRUE; @H_404_0@%NOTFOUND 布尔型属性,与%FOUND 相反; @H_404_0@%ISOPEN 布尔型属性,当游标已打开时返回 TRUE; @H_404_0@%ROWCOUNT 数字型属性,返回已从游标中读取的记录数。
@H_404_0@游标属性调用方式:游标名%属性
SET SERVEROUTPUT ON -- 打印出 80 部门的所有的员工的工资 salary DECLARE --声明一个记录类型 TYPE EMP_RECORD IS RECORD( V_SAL EMPLOYEES.SALARY%TYPE,V_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE ); --声明一个记录类型的变量 V_EMP_RECORD EMP_RECORD; --定义游标 CURSOR EMP_SAL_CURSOR IS SELECT E.SALARY,E.EMPLOYEE_ID FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = 80; BEGIN --打开游标 OPEN EMP_SAL_CURSOR; --提取游标 FETCH EMP_SAL_CURSOR INTO V_EMP_RECORD; WHILE EMP_SAL_CURSOR%FOUND LOOP DBMS_OUTPUT.put_line(V_EMP_RECORD.V_EMPID || ':' || V_EMP_RECORD.V_SAL); FETCH EMP_SAL_CURSOR INTO V_EMP_RECORD; END LOOP; --关闭游标 CLOSE EMP_SAL_CURSOR; END; /
游标的 FOR 循环
@H_404_0@游标 FOR 循环语句,自动执行游标的 OPEN 、FETCH 、CLOSE 语句和 循环 语句的功能;当进入循环时,游标 FOR 循环语句 自动 打开游标,并提取第一行游标数据,当程序处理完 当前所提取的数据而进入下一次循环时,游标 FOR 循环语句自动提取下一行数据供程序处理,当提取完结果集合中的所有数据行后结束循环,并自动关闭游标。 @H_404_0@这里把各种使用操作都封装起来了,十分的方便,一般都是用FOR循环使用游标。SET SERVEROUTPUT ON -- 打印出 80 部门的所有的员工的工资 salary DECLARE --定义游标 CURSOR EMP_SAL_CURSOR(DEP_ID NUMBER DEFAULT 80) IS SELECT E.SALARY,E.EMPLOYEE_ID FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = DEP_ID; BEGIN FOR C IN EMP_SAL_CURSOR(DEP_ID => 100) LOOP DBMS_OUTPUT.put_line(C.EMPLOYEE_ID || ':' || C.SALARY); END LOOP; END; /@H_404_0@上面都是用游标读取数据,如果需要更新数据,这需要在游标声明的后面添加FOR UPDATE。 这里给个例子:将所有员工中工资小于5000的人全部更新为10000。
SET SERVEROUTPUT ON DECLARE CURSOR EMP_SAL_CURSOR IS SELECT E.EMPLOYEE_ID,E.SALARY FROM EMPLOYEES E FOR UPDATE; V_EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE; V_SAL EMPLOYEES.SALARY%TYPE; V_NEW_SALARY EMPLOYEES.SALARY%TYPE; BEGIN FOR C IN EMP_SAL_CURSOR LOOP IF C.SALARY < 5000 THEN DBMS_OUTPUT.put_line('调整前:'||C.SALARY); UPDATE EMPLOYEES E SET E.SALARY = 10000 WHERE CURRENT OF EMP_SAL_CURSOR; SELECT E2.SALARY INTO V_NEW_SALARY FROM EMPLOYEES E2 WHERE E2.EMPLOYEE_ID = C.EMPLOYEE_ID; DBMS_OUTPUT.put_line('调整后:'|| V_NEW_SALARY); END IF; END LOOP; END; /