Oracle 游标的各种写法

最新推荐文章于 2024-09-24 11:22:08 发布

weixin_33758863

最新推荐文章于 2024-09-24 11:22:08 发布

阅读量409

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/fubinhnust/p/9928881.html

本文详细介绍了PL/SQL中游标的使用方法，包括隐式游标、显示游标、BULK COLLECT批量收集及动态SQL的实现方式。通过具体的代码示例，展示了如何遍历查询结果，进行数据处理和输出。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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