oracle 游标怎么写,Oracle 游标的各种写法

-- for .. in `隐式游标`

BEGIN

FOR RS IN (SELECT column_1 FROM table_name) LOOP

dbms_output.put_line(RS.column_1);

END LOOP;

END;

-- RS 必须是单个字段

DECLARE

CURSOR RS IS SELECT column_1 FROM table_name;

column_1 VARCHAR2(50);

BEGIN

OPEN RS;

LOOP

FETCH RS INTO column_1;

EXIT WHEN RS%NOTFOUND;

dbms_output.put_line(column_1);

END LOOP;

CLOSE RS;

END;

/

-- bulk collect显示游标

DECLARE

CURSOR RS IS SELECT column_1 FROM table_name;

column_1_array dbms_sql.Varchar2_Table;

BEGIN

OPEN RS;

FETCH RS BULK COLLECT INTO column_1_array;

FOR i IN 1 .. column_1_array.count LOOP

dbms_output.put_line(column_1_array(i));

END LOOP;

CLOSE RS;

END;

-- bulk collect 显示游标 + limit

DECLARE

CURSOR RS IS SELECT column_1 FROM table_name;

column_1_array dbms_sql.Varchar2_Table;

BEGIN

OPEN RS;

LOOP

FETCH RS BULK COLLECT INTO column_1_array LIMIT 2;

EXIT WHEN RS%NOTFOUND;

FOR i IN 1 .. column_1_array.count LOOP

dbms_output.put_line(column_1_array(i));

END LOOP;

END LOOP;

CLOSE RS;

END;

- select into 隐式游标

DECLARE

column_1 VARCHAR2(100);

BEGIN

SELECT zone_code INTO column_1 FROM table_name WHERE ROWNUM = 1;

dbms_output.put_line(column_1);

END;

- 动态sql，select into 隐式游标

DECLARE

column_1 VARCHAR2(50);

l_table VARCHAR2(200);

l_sql VARCHAR2(200);

BEGIN

l_table := 'table_name';

l_sql := 'select column_1 from ' || l_table || ' where rownum = 1';

EXECUTE IMMEDIATE l_sql INTO column_1;

dbms_output.put_line(column_1);

END;

- 动态sql select into 隐式游标 + bulk collect

DECLARE

column_1_array dbms_sql.Varchar2_Table;

l_table VARCHAR2(20);

l_sql VARCHAR2(100);

BEGIN

l_table := 'table_name';

l_sql := 'select column_1 from ' || l_table;

EXECUTE IMMEDIATE l_sql BULK COLLECT INTO column_1_array;

FOR i IN 1 .. column_1_array .count LOOP

dbms_output.put_line(column_1_array(i));

END LOOP;

END;