ORACLE -遍历表

 

declare
  v_whitelistid bankqc_mark_balance.usercardid%type;
  --1、定义游标
  cursor whitelist_cursor is select  usercardid from bankqc_mark_balance where whitelisttime<sysdate;
begin
  --2、打开游标
  open whitelist_cursor;
  --3、提取游标并循环
  fetch whitelist_cursor into v_whitelistid;
    while whitelist_cursor%found loop
      fetch whitelist_cursor into v_whitelistid;
      dbms_output.put_line(v_whitelistid);
    end loop;
  --4、关闭游标
  close whitelist_cursor;
end;

 /*简化版本*/

declare
  cursor whitelist_cursor is select usercardid from bankqc_mark_balance where whitelisttime<sysdate;
begin
  for whitelist in whitelist_cursor loop
        dbms_output.put_line(whitelist.usercardid); 
  end loop;
end;

/* Formatted on 2011-07-21 14:40:47 (QP5 v5.149.1003.31008) */
DECLARE
   CURSOR my_cursor
   IS
      SELECT * FROM dc_customer;
BEGIN
   OPEN my_cursor;

   LOOP
      FETCH my_cursor
      INTO v_table, v_col;
     
     

      DBMS_OUTPUT.put_line (v_table);
      EXIT WHEN my_cursor%NOTFOUND;
      v_sql :=
            'Insert into SCOTT.TCOL(A,B) select '
         || ''''
         || v_table
         || ''''
         || ','
         || ''''
         || v_col
         || ''''
         || ' from SCOTT.'
         || v_table
         || ' where '
         || v_col
         || '=1100';
      DBMS_OUTPUT.put_line (v_sql);

      EXECUTE IMMEDIATE v_sql;

      COMMIT;
   END LOOP;
END;