oracle基础知识积累-oracle游标教程

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

<pre class="has">

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.附本例使用表一

  
  
  
  
  

```sql
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.附本例使用表二

  
  
  
  
  
```
```sql
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);
```

  
  
  
以上,感谢.