oracle基础知识积累-oracle游标

场景:
    基础知识查询
动机:
    纯粹基础知识积累,便于后续查询.在很多场合,负责人们都在谈论去IOE话题,
所以,难免使用场合会或多或少会减少,于是按照习惯的思路记录一下.
环境: 
    Oracle Database 11g; PL/SQL Developer
1.使用游标存储过程

CREATE OR REPLACE PROCEDURE PRO_TEST_CURSOR(ERRORMSG OUT VARCHAR2) IS
BEGIN
  DECLARE
    --定义游标
    CURSOR LOG_INFO_CUR IS
      SELECT TASK_ID, BEGIN_TIME, END_TIME FROM F_LOG_INFO;
    --定义变量,取类型从表字段取
    V_TASK_ID    F_LOG_INFO.TASK_ID%TYPE;
    V_BEGIN_TIME F_LOG_INFO.BEGIN_TIME%TYPE;
    V_END_TIME   F_LOG_INFO.END_TIME%TYPE;
  
  BEGIN
    ERRORMSG := '';
    --打开游标
    OPEN LOG_INFO_CUR;
    --从游标取出值
    FETCH LOG_INFO_CUR
      INTO V_TASK_ID, V_BEGIN_TIME, V_END_TIME;
    LOOP
      EXIT WHEN LOG_INFO_CUR%NOTFOUND;
      INSERT INTO F_LOG_INFO_TEMP
        (TASK_ID,
         BEGIN_TIME,
         END_TIME,
         FLAG,
         FAIL_INFO,
         DATA_COUNT,
         TABLE_NAME)
      VALUES
        (V_TASK_ID,
         V_BEGIN_TIME,
         V_END_TIME,
         '999',
         '999',
         999,
         'TABLE_NAME2019');
      COMMIT;
      FETCH LOG_INFO_CUR
        INTO V_TASK_ID, V_BEGIN_TIME, V_END_TIME;
    END LOOP;
    CLOSE LOG_INFO_CUR;
  END;
EXCEPTION
  WHEN OTHERS THEN
    ERRORMSG := 'PRO_TEST_CURSOR抛出异常: ' || SQLERRM;
    --ROLLBACK;
END PRO_TEST_CURSOR;

2.附本例使用表一

create table F_LOG_INFO
(
  task_id    NUMBER(16) not null,
  begin_time DATE,
  end_time   DATE,
  flag       VARCHAR2(8),
  fail_info  VARCHAR2(512),
  data_count NUMBER(16),
  table_name VARCHAR2(256)
);
alter table F_LOG_INFO
  add constraint PK_F_LOG_INFO primary key (TASK_ID);

3.附本例使用表二

create table F_LOG_INFO_TEMP
(
  task_id    NUMBER(16) not null,
  begin_time DATE,
  end_time   DATE,
  flag       VARCHAR2(8),
  fail_info  VARCHAR2(512),
  data_count NUMBER(16),
  table_name VARCHAR2(256)
);
alter table F_LOG_INFO_TEMP
  add constraint PK_F_LOG_INFO_TEMP primary key (TASK_ID);

以上,感谢.