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;

　　

转载于:https://www.cnblogs.com/fubinhnust/p/9928881.html