Oracle游标的使用

游标的定义：

游标的作用：获取查询返回的记录。在通过查询将记录检索到游标中，可以一次从游标中取一行

使用游标的5个步骤：

（1）、声明变量，用于保存记录的列值

（2）、声明游标，并制定查询

（3）、打开游标

（4）、一次从游标中获取一个记录，并将列值存储在（1）步声明的变量中

（5）、关闭游标

游标就像一个存放结果集的集合，他能让你更加轻松的获取其中的结果

set serveroutput on//设置服务器可见输出，这个很重要，要不然将看不到输出的结果

DECLARE

v_product_id products.product_id%TYPE;

v_name      products.name%TYPE;

v_price     products.price%TYPE;

 

CURSOR v_product_cursor IS

SELECT product_id,name,price

 FROM   products

ORDER BY product_id;

BEGIN

OPEN v_product_cursor;

 

LOOP

FETCH v_product_cursor

INTO v_product_id,v_name,v_price

EXIT WHEN v_product_cursor%NOTFOUND;

 

DBMS_OUTPUT.PUT_LINE('v_product_id= '|| v_product_id ||',v_name= '||v_name||',v_price= '||v_price);

END LOOP;

 

CLOSE v_product_cursor;

END;

 

上面这个例子就是关于游标使用的完整示例

隐式游标

    当执行取一条记录的操作时，就是执行一次隐式游标。

隐式游标使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性.

SQL%FOUND,SQL%NOTFOUND是布尔值，SQL%ROWCOUNT是整数值。

SQL%FOUND和SQL%NOTFOUND

在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，SQL%FOUND的属性值将是：

. TRUE:INSERT

. TRUE:DELETE和UPDATE，至少有一行被DELETE或UPDATE.

. TRUE:SELECT INTO至少返回一行

当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。

SQL%ROWCOUNT

在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的

为1,如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常NO_DATA_FOUND.

SQL%ISOPEN

SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE,这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。

显示游标

当查询返回结果超过一行时，就需要一个显式游标，此时用户不能使用select into语句。PL/SQL管理隐式游标，当查询开始时隐式游标打开，查询结束时隐式游标自动关闭。显式游标在PL/SQL块的声明部分声明，在执行部分 或异常处理部分打开，取数据,关闭。下表显示了显式游标和隐式游标的差别：

表1 隐式游标和显式游标

隐式游标	显式游标
PL/SQL维护，当执行查询时自动打开和关闭	在程序中显式定义、打开、关闭，游标有一个名字。
游标属性前缀是SQL	游标属性的前缀是游标名
属性%ISOPEN总是为FALSE	%ISOPEN根据游标的状态确定值
SELECT语句带有INTO子串，只有一行数据被处理	可以处理多行数据，在程序中设置循环，取出每一行数据。

 

 

游标与for循环

利用for循环可以访问游标中的记录，当使用for循环时，可以不显示地打开和关闭游标——for循环会自动执行这些操作，这是简化语句的一个方法，请看下面示例

DECLARE

CURSOR c_product_cursor IS

SELECT product_id,name,price FROM products ORDER BY product_id;

BEGIN

FOR v_product IN c_product_cursor

LOOP

DBMS_OUTPUT.PUT_LINE

('Product_id:'||v_product.product_id||'  Name:'||v_product.name||'  Price:'||v_product.price);

END LOOP;

END;

注意上面红色的字是临时使用的变量，无需定义，这便节省了一部分代码

OPEN-FOR 语句

可以对游标使用OPEN-FOR语句，由于可以将游标分配给不同的查询，因此可以更加灵活地处理游标。也就是说定义了一个游标的量，能够更加灵活的更换查询等的语句，从而达到灵活改变结果

DECLARE

TYPE t_product_cursor IS

REF CURSORRETURN products%ROWTYPE;//定义一种游标的类型，并且具有返回类型

v_product_cursort_product_cursor;//应用此类型，定义一个游标

v_product roducts%ROWTYPE;

BEGIN

OPEN

v_product_cursor

FOR

select *from products where product_id<5;

 

LOOP

FETCH v_product_cursor INTO v_product;

EXIT WHEN v_product_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(

   'Product_id: '||v_product.product_id||

   ' Name: '||v_product.name||

   ' Price: '||v_product.price);

END LOOP;

CLOSEv_product_cursor;

END;

/

游标中的更新和删除

在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法。
UPDATE 或DELETE语句中的WHERE CURRENTOF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，不能进行 UPDATE、DELETE或SELECT...FOR UPDATE操作。
语法：

FOR UPDATE [OF [schema.]table.column[,[schema.]table.column].. [nowait]

在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。
在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下：

WHERE{CURRENT OF cursor_name|search_condition}

例：

DELCARE CURSOR c1 IS SELECT empno,salary FROM emp WHERE comm IS NULL FOR UPDATE OF comm; v_comm NUMBER(10,2); BEGIN FOR r1 IN c1 LOOP IF r1.salary<500 THEN v_comm:=r1.salary*0.25; ELSEIF r1.salary<1000 THEN v_comm:=r1.salary*0.20; ELSEIF r1.salary<3000 THEN v_comm:=r1.salary*0.15; ELSE v_comm:=r1.salary*0.12; END IF; UPDATE emp SET comm=v_comm WHERE CURRENT OF c1; END LOOP; END