游标

使用游标的步骤：

    ⑴声明一些变量，用于保存记录的列值；

    ⑵声明游标，并指定查询；

    ⑶打开游标；

    ⑷一次从游标中获取一个记录，并将列值存储在第⑴步中声明的变量中。然后对这些变量执行某些操作；

    ⑸关闭游标。

步骤一：声明用于保存列值的变量   这些变量必须与列的类型兼容

    DECLARE

      v_eno myemp.empno%TYPE;

      v_ename myemp.ename%TYPE;

      v_esal myemp.sal%TYPE;

步骤二：声明游标  由游标名和希望执行的查询组成 放在声明段中

    CURSOR cursor_name IS   --cursor_name表示游标名

      SELECT_statement;     --查询

    CURSOR v_emp_cursor IS

      SELECT empno,ename,sal FROM myemp ORDER BY empno;

    查询在打开游标之前并不会真正运行。

步骤三：打开游标  打开游标时使用的是OPEN语句，这个语句必须放置在代码块的可执行段中

    OPEN v_emp_cursor;

步骤四：从游标中取得记录

    FETCH cursor_name               --指定游标名

    INTO variable1[，variable2...]; --存储从游标中取出的列值的变量，需要为每个列值提供匹配的变量

    FETCH v_emp_cursor INTO v_eno,v_ename,v_esal;

    使用布尔变量v_emp_cursor%NOTFOUND确定循环是否结束：

        LOOP

          --fetch the rows from the cursor

          FETCH v_emp_cursor INTO v_eno,v_ename,v_esal;

          --exit the loop when there are no more rows,as indicated by the Boolean     

          --variable v_emp_cursor%NOTFOUND(=true when there are no more rows) 

          EXIT WHEN v_emp_cursor%NOTFOUND;

          --use DBMS_OUTPUT.PUT_LINE() to display the variables

          DBMS_OUTPUT.PUT_LINE('v_eno = '|| v_eno||', v_ename = '||v_ename ||',v_esal = ' || v_esal);

          END LOOP;

步骤五：关闭游标

    CLOSE v_emp_cursor;

示例：

DECLARE

  v_eno myemp.empno%TYPE;

  v_ename myemp.ename%TYPE;

  v_esal myemp.sal%TYPE;

  CURSOR v_emp_cursor IS

    SELECT empno, ename, sal FROM myemp ORDER BY empno;

BEGIN

  OPEN v_emp_cursor;

  FETCH v_emp_cursor

    INTO v_eno, v_ename, v_esal;

  LOOP

    --fetch the rows from the cursor

    FETCH v_emp_cursor

      INTO v_eno, v_ename, v_esal;

    --exit the loop when there are no more rows,as indicated by the Boolean

    --variable v_emp_cursor%NOTFOUND(=true when there are no more rows)

    EXIT WHEN v_emp_cursor%NOTFOUND;

    --use DBMS_OUTPUT.PUT_LINE() to display the variables

    DBMS_OUTPUT.PUT_LINE('v_eno = ' || v_eno || ', v_ename = ' || v_ename ||

                         ',v_esal = ' || v_esal);

  END LOOP;

  CLOSE v_emp_cursor;

END;

/

SET SERVEROUT ON;

/

-- Created on 2015/7/30 by MR.QZ

DECLARE

   --step1:declare some variables

   v_orgname yyjc_danwei.danwei_name%TYPE;

  v_orgno yyjc_danwei.ext_value1%TYPE;

  --step2:declace the cursor and select statement

  CURSOR v_org_cursor IS

   SELECT t.danwei_name,t.ext_value1 FROM yyjc_danwei t WHERE t.ext_value1 IS NOT NULL ORDER BY t.danwei_order ;

BEGIN

 --step3:open the cursor

   OPEN v_org_cursor ;

  LOOP

   --step4:get the record from the cursor

   FETCH v_org_cursor

   INTO v_orgname,v_orgno ;

   EXIT WHEN v_org_cursor%NOTFOUND ;

    DBMS_OUTPUT.PUT_LINE('v_orgname = '||v_orgname||' ,v_orgno = '||v_orgno) ;

  END LOOP ;

   --step5:close the cursor

  CLOSE v_org_cursor ;

END;

在存储过程中如何使用游标

/*此种方法需要先声明游标列值变量*/

create or replace procedure fxc_createDuoZhiBiaoMB_yue_v8(owner_in varchar2, period varchar2, pinduTxt varchar2, stat_cycle_s varchar2, scbh varchar2, corpcode_in varchar2) is 

  

  type pindu_cursor_type is ref cursor;

  pindu_cursor pindu_cursor_type;

  type danwei_cursor_type is ref cursor;

  danwei_cursor danwei_cursor_type;

  

  v_danwei sjdr_model_basedata_temp.org_no%type;

  v_danwei_name sjdr_model_basedata_temp.org_name%type;

  v_sql2 varchar2(4000); 

  v_sql3 varchar2(1000);

begin

  open pindu_cursor for

  select t.dim_val_name, t.dim_val_code

    from mwt_yyjc_dim_value t

   where t.dim_code = 'wp001'

     and dim_val_code = period;

  loop

     fetch pindu_cursor

       into v_pindu_name, v_pindu;

    exit when pindu_cursor%notfound;

    v_sql2:= 'select t.dim_val_name, t.dim_val_code from mwt_yyjc_dim_value t where dim_oldcode = ''DWFX165'' and dim_val_code = '''||corpcode_in||''' ';

    open danwei_cursor for v_sql2;

    loop

      fetch danwei_cursor

        into v_danwei_name, v_danwei;

      exit when danwei_cursor%notfound;

    end loop;

 end loop;

 commit;

end fxc_createDuoZhiBiaoMB_yue_v8;

/*此种方法不需要声明游标列值变量*/

-- Created on 2015/7/30 by MR.QZ

DECLARE

 CURSOR v_orginfo_cursor IS

        SELECT t.ext_value1,t.danwei_name FROM yyjc_danwei t WHERE t.ext_value1 IS NOT NULL ORDER BY t.danwei_order;

     

BEGIN

 

  FOR orginfo IN v_orginfo_cursor

 

  LOOP

   DBMS_OUTPUT.PUT_LINE('ORG_NO = '||orginfo.ext_value1||',ORG_NAME = '||orginfo.danwei_name);

   END LOOP;

 

END;

显示游标

---

定义:CURSOR <游标名> IS <SELECT 语句> [FOR UPDATE | FOR UPDATE OF 字段];

[FOR UPDATE | FOR UPDATE OF 字段] --给游标加锁,既是在程序中有"UPDATE","INSERT","DELETE"语句对数据库操作时。
游标自动给指定的表或者字段加锁，防止同时有别的程序对指定的表或字段进行"UPDATE","INSERT","DELETE"操作.
在使用"DELETE","UPDATE"后还可以在程序中使用CURRENT OF <游标名> 子句引用当前行.

操作:OPEN <游标名> --打开游标
    FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;
                或者
    FETCH <游标名> INTO 行对象;         --取出游标当前位置的值
    CLOSE <游标名> --关闭游标
属性:%NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE";
    %FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE";
    %ROWCOUNT --返回游标当前行的行数;
    %ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE";

使用:
LOOP循环
   示例:
DECLARE 
     cursor c_1 is select * from emp;     --定义游标
     r c_1%rowtype;      --定义一个行对象，用于获得游标的值
BEGIN
     if c_1%isopenthen
          CLOSE c_1;
     end if;               
     OPEN c_1;          --判断游标是否打开.如果开了将其关闭，然后在打开
     dbms_output.put_line('行号 姓名 薪水');
     LOOP
     FETCH c_1 INTO r;     --取值
     EXIT WHEN c_1%NOTFOUND;     --如果游标没有取到值，退出循环.
     dbms_output.put_line(c_1%rowcount||' '||r.ename||' '||r.sal);    --输出结果,需要 set serverout on 才能显示.
     END LOOP;
END;


FOR循环
   示例:
DECLARE 
     cursor c_1 is select ename,sal from emp;     --定义游标     
BEGIN
     dbms_output.put_line('行号 姓名 薪水');
     FOR i IN c_1         --for循环中的循环变量i为c_1%rowtype类型;
     LOOP
     dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal);    --输出结果,需要 set serverout on 才能显示.
     END LOOP;
END;

for循环使用游标是在循环开始前自动打开游标，并且自动取值到循环结束后，自动关闭游标.

游标加锁示例:
DECLARE 
     cursor c_1 is select ename,sal from emp for update of sal;     --定义游标对emp表的sal字段加锁.     
BEGIN
     dbms_output.put_line('行号 姓名 薪水');
     FOR i IN c_1         --for循环中的循环变量i为c_1%rowtype类型;
     LOOP
     UPDATE EMP set sal=sal+100 WHERE CURRENT OF c_1; --表示对当前行的sal进行跟新.
     END LOOP;
     FOR i IN c_1        
     LOOP
     dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal);    --输出结果,需要 set serverout on 才能显示.
     END LOOP;
END;



代参数的游标
定义:CURSOR <游标名>(参数列表) IS <SELECT 语句> [FOR UPDATE | FOR UPDATE OF 字段];
示例:
DECLARE 
     cursor c_1(name emp.ename%type) is select ename,sal from emp where ename=name;     --定义游标     
BEGIN
     dbms_output.put_line('行号 姓名 薪水');
     FOR i IN c_1('&name')         --for循环中的循环变量i为c_1%rowtype类型;
     LOOP
     dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal);    --输出结果,需要 set serverout on 才能显示.
     END LOOP;
END;


隐试游标

---

隐试游标游标是系统自动生成的。每执行一个DML语句就会产生一个隐试游标，起名字为SQL;

隐试游标不能进行"OPEN" ,"CLOSE","FETCH"这些操作;

属性:
    %NOTFOUND --如果DML语句没有影响到任何一行时，则该属性为"TRUE"，否则为"FALSE";
    %FOUND --如果DML语句影响到一行或一行以上时，则该属性为"TRUE"，否则为"FALSE";
    %ROWCOUNT --返回游标当最后一行的行数;

个人认为隐试游标的作用是判断一个DML语句；
示例：
BEGIN
    DELETE FROM EMP WHERE empno=&a;
    IF SQL%NOTFOUND THEN
        dbms_output.put_line('empno不存在');
    END IF;
   IF SQL%ROWCOUNT>0 THEN
        dbms_output.put_line('删除成功');
    END IF;
END;

强型REF游标

---

定义:TYPE <游标名> IS REF CURSOR RETURN<返回类型>;


操作:OPEN <游标名> For <select 语句> --打开游标
    FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;
                或者
    FETCH <游标名> INTO 行对象;         --取出游标当前位置的值
    CLOSE <游标名> --关闭游标
属性:%NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE";
    %FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE";
    %ROWCOUNT --返回游标当前行的行数;
    %ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE";

使用:
   示例:
DECLARE 
     type c_type is ref cursor return emp%rowtype;     --定义游标
     c_1 c_type;      --实例化这个游标类型
     r emp%rowtype;
BEGIN
     dbms_output.put_line('行号 姓名 薪水');
     open c_1 for select * from emp;
     loop 
     fetch c_1 into r;
     exit when c_1%notfound;
     dbms_output.put_line(c_1%rowcount||' '||r.ename||' '||r.sal);    --输出结果,需要 set serverout on 才能显示.
     END LOOP;
close c_1;
END;


弱型REF游标

---

定义:TYPE <游标名> IS REF CURSOR;


操作:OPEN <游标名> For <select 语句> --打开游标
    FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;
                或者
    FETCH <游标名> INTO 行对象;         --取出游标当前位置的值
    CLOSE <游标名> --关闭游标
属性:%NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE";
    %FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE";
    %ROWCOUNT --返回游标当前行的行数;
    %ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE";
示例：
set autoprint on;
var c_1 refcursor;
DECLARE
   n number;
BEGIN
   n:=&请输入;
   if n=1 then
         open :c_1 for select * from emp;
   else 
         open :c_1 for select * from dept;
   end if;
END;