【Oracle】PL/SQL——游标CURSOR

前端之家收集整理的这篇文章主要介绍了【Oracle】PL/SQL——游标CURSOR前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。


在 PL/sql 程序中,对于 处理多行记录的事务经常使用游标来实现。

其中,游标又分为显式游标和隐式游标,这里重点说明显式游标的用法

显示游标

显示游标的处理

四个PL/sql步骤:

1)定义游标 :就是定义一个游标名,以及与其相对应的 SELECT 语句。

格式:

CURSOR cursor_name[(parameter[,parameter]…)] IS select_statement;

游标参数只能为输入参数,其格式为:

parameter_name [IN] datatype [{:= | DEFAULT} expression]

在指定数据类型时,不能使用长度约束。如 NUMBER(4)、CHAR(10) 等都是错误的。

2)打开游标 :就是执行游标所对应的 SELECT 语句,将其查询结果放入工作区,并且指针指向工作区的首部,标识游标结果集合。如果游标查询语句中带有 FOR UPDATE 选项,OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。

格式:

OPEN cursor_name[([parameter =>] value[,[parameter =>] value]…)];

在向游标传递参数时,可以使用与函数参数相同的传值方法,即位置表示法和名称表示 法。PL/sql程序不能用 OPEN 语句重复打开一个游标。

3)提取游标数据 :就是检索结果集合中的数据行,放入指定的输出变量中。

格式:

FETCH cursor_name INTO {variable_list | record_variable };

4)对该记录进行处理,直到活动集合中没有记录;

5) 关闭游标 :当提取和处理完游标结果集合数据后,应及时关闭游标,以释放该游标所占用的系统资源,并使该游标的工作区变成无效,不能再使用 FETCH 语句取其中数据。关闭后的游标可以使用 OPEN 语句重新打开。

格式:

CLOSE cursor_name;

注: 定义的游标不能有 INTO 子句


游标属性

%FOUND 布尔型属性,当最近一次读记录时成功返回,则值为 TRUE;

%NOTFOUND 布尔型属性,与%FOUND 相反;

%ISOPEN 布尔型属性,当游标已打开时返回 TRUE;

%ROWCOUNT 数字型属性,返回已从游标中读取的记录数。

游标属性调用方式:游标名%属性

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 循环

游标 FOR 循环语句,自动执行游标的 OPEN 、FETCH 、CLOSE 语句和 循环 语句的功能;当进入循环时,游标 FOR 循环语句 自动 打开游标,并提取第一行游标数据,当程序处理完 当前所提取的数据而进入下一次循环时,游标 FOR 循环语句自动提取下一行数据供程序处理,当提取完结果集合中的所有数据行后结束循环,并自动关闭游标。

这里把各种使用操作都封装起来了,十分的方便,一般都是用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;
/  

上面都是用游标读取数据,如果需要更新数据,这需要在游标声明的后面添加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;  
/

隐式游标

显式游标主要是用于对查询语句的处理,尤其是在查询结果为多条记录的情况下;而对于非查询语句,如修改删除操作,则由 ORACLE 系统自动地为这些操作设置游标并创建其工作区,这些 由系统隐含创建的游标称为隐式游标, 隐式游标的名字为 sql,这是由 ORACLE 系统定义的。

隐式游标的属性和显式游标的属性相同,而调用方式则是为:sql%属性名。

原文链接:https://www.f2er.com/oracle/209643.html

猜你在找的Oracle相关文章