【oracle】PL/SQL显示游标、隐示游标、动态游标
Oracle显示游标，游标的基本原理:
在Oracle中,在执行一个有SELECT、INSERT、UPDATE和DELETE语句PL/SQL块时,Oracle会在内存中为其分配一个缓冲区,将执行结果放在这个缓冲区中，而游标是该区的一个指针。
游标分类:
静态游标 动态游标。
静态游标又分隐式游标和显示游标。
在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。
对于不同的SQL语句，游标的使用情况不同：
SQL语句 游标
非查询语句 隐式的
结果是单行的查询语句 隐式的或显示的
结果是多行的查询语句 显示的
显示游标的使用步骤:
1.声明游标
CURSOR cursor_name [(parameter[,parameter]...)]
[RETURN return_type] IS select_statement;
cursor_name指游标的名称
parameter用于为游标指定输入参数。在指定数据类型时,不能使用长度约束。例如NUMBER(4)、CHAR(10)等都是错误的。
return_type用于定义游标提取的行的类型。
select_statement指游标定义的查询语句。
2.打开游标
OPEN cursor_name[(parameters)];
3.提取游标
FETCH cursor_name INTO variables;
cursor_name值游标的名称
variables是变量名。
4.关闭游标
CLOSE cursor_name;
显示游标的属性:
%FOUND:只有在DML语句影响一行或多行时,%FOUND属性才会返回TRUE。
%NOTFOUND:%NOTFOUND属性与%FOUND属性的作用正好相反。如果DML语句没有影响任何行,则%NOTFOUND属性返回TRUE。
%ROWCOUNT:%ROWCOUNT属性返回DML语句影响的行数。如果DML语句没有影响任何行,则%ROWCOUNT属性将返回0。
%ISOPEN:%ISOPEN属性返回游标是否已打开。
处理显式游标
例：
declare
CURSOR c4(v_fact_no varchar2, v_fact_odr_no VARCHAR2) --1、声明游标,有参数没有返回值
is
select PO_NO, ODR_QTY from odrm
WHERE fact_no = v_fact_no AND fact_odr_no = v_fact_odr_no;
--基于游标定义记录变量，比声明记录类型变量要方便，不容易出错
v_odrm_record c4%ROWTYPE;
begin
OPEN c4('216M', 'A6710087 '); --2、打开游标,传递参数值
LOOP
FETCH c4 INTO v_odrm_record; --3、提取游标fetch into
if C4%FOUND then
DBMS_OUTPUT.PUT_LINE(V_ODRM_RECORD.PO_NO||'的数量是'
||v_odrm_record.odr_qty);
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
END IF;
END LOOP;
CLOSE c4; --4、关闭游标
END;
退出LOOP或者用：
EXIT WHEN c4%NOTFOUND;
游标属性：
Cursor_name%FOUND 布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；
Cursor_name%NOTFOUND 布尔型属性，与%FOUND相反；——注意区别于DO_DATA_FOUND(select into抛出异常)
Cursor_name%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；
Cursor_name%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。
游标的for循环
PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；
- 当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据；
- 当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理；
- 当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。
格式：
-- 游标数据处理代码
END LOOP;
其中：
index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据。
例：
declare
CURSOR c_cursor(v_odr_yymm varchar2 DEFAULT '201707')
is
SELECT fact_odr_no, odr_qty FROM odrm WHERE odr_yymm = v_odr_yymm;
BEGIN
--当dept_no参数值为30
for C1_REC in C_CURSOR('201708') LOOP
DBMS_OUTPUT.PUT_LINE(c1_rec.fact_odr_no||'---'||c1_rec.odr_qty);
END LOOP;
--使用默认的dept_no参数值10
for C1_REC in C_CURSOR LOOP
DBMS_OUTPUT.PUT_LINE(c1_rec.fact_odr_no||'---'||c1_rec.odr_qty);
END LOOP;
END;
或者可以在游标FOR循环语句中使用子查询
begin
FOR c1_rec IN(SELECT fact_odr_no, odr_qty FROM odrm where odr_yymm = '201708') LOOP
DBMS_OUTPUT.PUT_LINE(c1_rec.fact_odr_no||'---'||c1_rec.odr_qty);
end LOOP;
END;
处理隐式游标
显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；
而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，隐式游标的名字为SQL，这是由ORACLE 系统定义的。
对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。
格式调用为： SQL%
DECLARE
v_rows NUMBER;
BEGIN
--更新数据
update ODRM set ODR_QTY = ODR_QTY + 5
WHERE fact_no = '216M' AND odr_yymm = '201407';
--获取默认游标的属性值
V_ROWS := sql%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('更新了201807订单年月'||v_rows||'笔订单数量都追加了5双');
DELETE FROM odrm WHERE odr_yymm='201807';
if sql%NOTFOUND then
DBMS_OUTPUT.PUT_LINE('没有201807月的订单资料');
end if;
END;
更新或删除当前游标数据
游标查询语句中必须使用FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。
如果另一个会话已对活动集中的行加了锁，那么SELECT FOR UPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作；对于这种情况，当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：
ORA-0054 ：resource busy and acquire with nowait specified.
declare
V_odr_yymm odrm.odr_yymm%TYPE :=&p_odr_yymmo;
CURSOR odrm_cursor
is
select FACT_ODR_NO, ODR_QTY
FROM odrm WHERE odr_yymm=V_odr_yymm
FOR UPDATE NOWAIT; --1、for update
begin
for ODRM_RECORD in ODRM_CURSOR LOOP
if ODRM_RECORD.ODR_QTY < 30 then
UPDATE odrm SET odr_qty=15
WHERE CURRENT OF ODRM_CURSOR; --2、WHERE CURRENT OF cursor_name子句
END IF;
end LOOP;
END;
动态游标
与游标一样，动态游标（游标变量）也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。
游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。
DECLARE
--定义一个游标数据类型
TYPE odrm_cursor_type IS REF CURSOR;
--声明一个游标变量
c1 odrm_cursor_type;
--声明两个记录变量
V_ODRM_RECORD ODRM%ROWTYPE;
v_custom_record custom%ROWTYPE;
begin
OPEN c1 FOR SELECT * FROM odrm WHERE odr_yymm = '201708';
LOOP
FETCH c1 INTO v_odrm_record;
EXIT when C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_ODRM_RECORD.FACt_ODR_NO||'订单的数量是'
||v_odrm_record.odr_qty);
END LOOP;
--将同一个游标变量对应到另一个SELECT语句
OPEN c1 FOR SELECT * FROM custom WHERE custom_no IN ('608001','516242');
LOOP
FETCH c1 INTO v_custom_record;
EXIT when C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_CUSTOM_RECORD.CUSTOM_NO||'客户'
||v_custom_record.custom_name);
END LOOP;
CLOSE c1;
END;