游标的使用实例

 

---带参数的循环游标的使用

DECLARE

CURSOR mycursor(num varchar2) IS

SELECT * FROM DEPT WHERE deptno=num;

BEGIN

FOR cur IN mycursor(10) LOOP

DBMS_OUTPUT.PUT_LINE('deptNum='||cur.deptno||' deptName='||cur.dname);

END LOOP;

END;

---使用游标进行过数据更新

DECLARE

d_name VARCHAR2(20);

CURSOR mycursor IS

SELECT dname FROM dept FOR UPDATE;

BEGIN

OPEN mycursor;

LOOP

FETCH mycursor INTO d_name;

EXIT WHEN mycursor%NOTFOUND;

UPDATE dept SET dname=RTRIM(dname,'_t') WHERE CURRENT OF mycursor;

END LOOP;

CLOSE mycursor;

END;

CURRENT OF+游标名：获取游标当前所指向的行

 ---隐式游标，不用 用cursor声明的游标

DECLARE

  V_SQL VARCHAR2(1000);

BEGIN

  FOR X IN (SELECT TABLE_NAME, PARTITION_NAME

              FROM USER_TAB_PARTITIONS

             WHERE TO_DATE(SUBSTR(REPLACE(PARTITION_NAME, '_', ''), -6),

                           'YY-MM-DD') <

                   ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 0)

               AND TABLE_NAME = 'T_UNIT_WORKINFO'

               AND PARTITION_NAME LIKE '%P_TBL_UNIT_WORKINFO_DAY_%') LOOP

    V_SQL := 'ALTER TABLE ' || X.TABLE_NAME || ' RENAME PARTITION "' ||

             X.PARTITION_NAME || '" TO "' || 'P_TBL_UNIT_WORKINFO_20' ||

             TO_CHAR(TO_DATE(SUBSTR(REPLACE(X.PARTITION_NAME, '_', ''), -6),

                             'YY-MM-DD'),

                     'YYMMDD') || '"';

    EXECUTE IMMEDIATE V_SQL;

  END LOOP;

END;

---显式游标

declare

cursor  cur_r is

select code as wy_no,substr(code,-3,3) as t_no  from t_zxf_DZ;

row_r cur_r%rowtype;

v_sql varchar2(2000);

v_counter number;

begin

  v_counter:=0;

  open cur_r;

  loop

  fetch cur_r into row_r;

   exit when cur_r%notfound ;

   v_sql :='insert into ytrep.t_zxf_waybillno_org_code_DZ select  waybill_no,mat_org_code,signoff_time

    from ytexp.t_exp_waybill_'||row_r.t_no||'@dblk_pub_27.yto56.com.cn'|| '  t_trace where  t_trace'||'.waybill_no='''||row_r.wy_no||'''

    and t_trace.signoff_time is not null and t_trace.signoff_time>= to_date(''2014-04-01'', ''YYYY-MM-DD'')

   and t_trace.signoff_time < to_date(''2014-06-01'', ''YYYY-MM-DD'')';

      execute immediate (v_sql);

      commit;

    end loop;

  close cur_r;

end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30018455/viewspace-1665415/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30018455/viewspace-1665415/