oracle有for循环吗,oracle基础知识积累-oracle的for循环使用

场景:

基础知识查询

动机:

纯粹基础知识积累,便于后续查询.在很多场合,负责人们都在谈论去IOE话题,

所以,难免使用场合会或多或少会减少,于是按照习惯的思路记录一下.

环境:

Oracle Database 11g; PL/SQL Developer

1.for循环取出游标数据

CREATE OR REPLACE PROCEDURE PRO_TEST_CURSOR_FOR(ERRORMSG OUT VARCHAR2) IS

BEGIN

DECLARE

--定义游标

CURSOR LOG_INFO_CUR IS

SELECT * FROM F_LOG_INFO;

BEGIN

ERRORMSG := '';

--取出一行数据

FOR F_LOG_INFO_ROW IN LOG_INFO_CUR LOOP

INSERT INTO F_LOG_INFO_TEMP

(TASK_ID,

BEGIN_TIME,

END_TIME,

FLAG,

FAIL_INFO,

DATA_COUNT,

TABLE_NAME)

VALUES

(F_LOG_INFO_ROW.TASK_ID,

F_LOG_INFO_ROW.BEGIN_TIME,

F_LOG_INFO_ROW.END_TIME,

F_LOG_INFO_ROW.FLAG,

F_LOG_INFO_ROW.FAIL_INFO,

F_LOG_INFO_ROW.DATA_COUNT,

F_LOG_INFO_ROW.TABLE_NAME);

COMMIT;

END LOOP;

END;

EXCEPTION

WHEN OTHERS THEN

ERRORMSG := 'PRO_TEST_CURSOR_FOR抛出异常: ' || SQLERRM;

END PRO_TEST_CURSOR_FOR;

2.for循环取出数值

CREATE OR REPLACE PROCEDURE PRO_TEST_CURSOR_FOR2(ERRORMSG OUT VARCHAR2) IS

BEGIN

BEGIN

ERRORMSG := '';

--取出一行数据

FOR V_TASK_ID IN 1 .. 100 LOOP

INSERT INTO F_LOG_INFO_TEMP

(TASK_ID,

BEGIN_TIME,

END_TIME,

FLAG,

FAIL_INFO,

DATA_COUNT,

TABLE_NAME)

VALUES

(V_TASK_ID,

SYSDATE,

SYSDATE,

'FLAG',

'FAIL_INFO',

100,

'TABLE_NAME');

COMMIT;

END LOOP;

END;

EXCEPTION

WHEN OTHERS THEN

ERRORMSG := 'PRO_TEST_CURSOR_FOR抛出异常: ' || SQLERRM;

END PRO_TEST_CURSOR_FOR2;

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

以上,感谢.

标签：INFO,END,LOG,VARCHAR2,基础知识,CURSOR,循环,oracle,ROW

来源： https://blog.csdn.net/zhangbeizhen18/article/details/100127964