-- 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 顯示游標 + limitDECLARE 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 collectDECLARE 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

總結

以上是生活随笔為你收集整理的Oracle 游标的各种写法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。