oracle cursor (游标)

d2m9dc94pt

--ref 游标
/*
显示游标 多行
隐含游标 数据操纵语言 DML 和单行select into语句
1.定义游标
指定游标所对应的select结果集语句
CURSOR cursor_name IS select_statement;
select_statement:查询语句
2.打开游标
OPEN cursor_name;
执行游标所对应的select_statement,将结果暂时存放在游标结果集中
3.提取数据
使用fetch语句提取游标数据，fetch每次只能提取一行；fetch..bulk collect into可以提取多行
FETCH cursor_name into variable...;
variable 接受游标数据的变量
FETCH cursor_name BULK COLLECT INTO collection...;
collection 接收游标数据的集合变量
4.关闭游标
提取完数据，关闭游标，释放结果集
CLOSE cursor_name;

*游标属性
cursor_name%ISOPEN  游标是否已打开
cursor_name%FOUND   FETCH语句执行后，用来判断是否有数据被提取
cursor_name%NOTFOUND 同上
cursor_name%ROWCOUNT 目前已经fetch的行数
*/
--隐含游标
DECLARE
v_name varchar2(30);
BEGIN
select ename into v_name from emp where empno = 7788;
CASE
WHEN SQL%FOUND THEN
dbms_output.put_line('隐身游标的行数肯定是：'||SQL%ROWCOUNT||'，查询的结果:'||v_name);
--隐身游标的行数肯定是：1，查询的结果:SCOTT
END CASE;
END;
--显示游标
--fetch提取一行数据
DECLARE
CURSOR emp_cs is select ename,job from emp;--声明游标
v_name emp.ename%type;
v_job  emp.job%type;
BEGIN
OPEN emp_cs;
LOOP--因为fetch只提取一条数据，所以用循环多次提取
FETCH emp_cs INTO v_name,v_job;
EXIT WHEN emp_cs%NOTFOUND;--当游标中没有数据时，退出循环
dbms_output.put_line(emp_cs%ROWCOUNT||'--'||v_name||'--'||v_job);
END LOOP;
CLOSE emp_cs;
END;
--FETCH ...BULK COLLECTION 提取多行数据
DECLARE
--声明存储多行数据的集合类型
TYPE v_table_type IS TABLE OF VARCHAR2(30);
name_table v_table_type;
job_table v_table_type;
CURSOR emp_cs IS select ename,job from emp;
BEGIN
OPEN emp_cs;
FETCH emp_cs BULK COLLECT INTO name_table,job_table; --两个嵌套表类型不需要初始化
FOR i IN 1..name_table.count LOOP
dbms_output.put_line(name_table(i)||'--'||job_table(i));
END LOOP;
CLOSE emp_cs;
END;
--分页提取，每次提取固定条数
DECLARE
type v_table_type IS TABLE OF int;
no_tab v_table_type;
pageNo int :=5;--5条一页
nums int:=0;--已经输出的条数
CURSOR emp_cs IS select empno from emp;
BEGIN
--OPEN emp_cs;
IF NOT emp_cs%ISOPEN THEN
OPEN emp_cs;
END IF;
LOOP--LIMIT语句限制每次提请的条数，从上次游标rowcount处开始
FETCH emp_cs BULK COLLECT INTO no_tab LIMIT pageNo;
--打印刚获取的数据（5条），右边界计算方法：游标总共fetch的条数-已经输出的条数
FOR I IN 1..(emp_cs%ROWCOUNT - nums) LOOP
dbms_output.put_line(no_tab(i));
END LOOP;
dbms_output.new_line;
nums := emp_cs%ROWCOUNT;--从新设置已输出的条数
/*
--这一句放到最后，因为知道emp表中共有14条记录，可以分为3页，第3次Fecth只有4条数据，
--这时候已经是NOTFOUND了，如果放到输出前面的话，后4条就不能被输出了
*/
EXIT WHEN emp_cs%NOTFOUND;
END LOOP;
CLOSE emp_cs;
END;

--游标 记录变量
DECLARE
CURSOR emp_cs IS select ename,job from emp;
v_record emp_cs%ROWTYPE;--游标行类型定义record
BEGIN
OPEN emp_cs;
LOOP
FETCH emp_cs INTO v_record;
EXIT WHEN emp_cs%NOTFOUND;
dbms_output.put_line(v_record.ename||'--'||v_record.job);
END LOOP;
CLOSE emp_cs;
END;

--参数游标 就是差不多相当与函数了 根据不同的参数值有不同的游标数据
--参数一般会在游标的select语句中使用，否则也就没意义了
/*
CURSOR cursor_name(parametr_name datatype...) IS select_statement;
*参数只能定义类型，不能定义长度，
*/
DECLARE
CURSOR emp_cs(param1 varchar2) IS select ename from emp where job =param1;
v_name emp.ename%type;
v_job varchar2(30) := '&job';
BEGIN
OPEN emp_cs(v_job);
LOOP
FETCH emp_cs INTO v_name;
EXIT WHEN emp_cs%NOTFOUND;
dbms_output.put_line(v_name);
END LOOP;
CLOSE emp_cs;
END;

--使用游标更新或删除数据
/*
游标不仅可以查询数据，也可以修改数据，由于游标是操作的多条数据更新，为
保证事物的原子性，我也不知道怎么称呼，反正就是防止游标中的数据行在修改的过程中
有其它的事务修改这些列 要使用FOR UPDATE 子句
如：游标中有5条数据，你这时正执行修改第3条数据，而这时另一个事务修改了第4条数据。
而游标是临时缓存结果集，4的修改就不会被看到。这样就乱套了
CURSOR cursor_name(param datatype) IS
select_statement FOR UPDATE [OF column_reference][NOWAIT];
FOR UPDATE在游标结果集数据行上加行共享锁，防止其它用户在相应
行上执行DML操作。
*/
DECLARE
CURSOR emp_cs IS select comm from emp FOR UPDATE;
v_comm number(7,2);
BEGIN
OPEN emp_cs;
LOOP
FETCH emp_cs INTO v_comm;
EXIT WHEN emp_cs%NOTFOUND;
IF v_comm is null THEN
UPDATE emp SET comm=1 WHERE CURRENT OF emp_cs;
END IF;
END LOOP;
CLOSE emp_cs;
END;
commit;
/*
游标for循环
简化了对游标的处理，使用游标for循环时，oracle会隐含的打开游标、
提取数据并关闭游标
*/
--1
DECLARE
CURSOR emp_cs IS select ename,job from emp;
BEGIN
FOR emp_record IN emp_cs LOOP--emp_record是隐含定义的记录变量名
dbms_output.put_line(emp_cs%ROWCOUNT||'-'||emp_record.ename);
END LOOP;
END;
--2 在游标for循环中直接是哦那个select语句定义游标
--不过这样就不能使用游标属性了，因为没有游标名字
BEGIN
FOR emp_record IN (select ename,job from emp) LOOP
dbms_output.put_line(emp_record.ename);
END LOOP;
END;

/*
使用游标变量
使用显示游标时，需要在定义部分指定所对应的静态select语句；
当使用游标变量时，就可在打开游标变量时指定其所对应的select语句。
定义游标变量
TYPE ref_type IS REF CURSOR[RETURN return_type];
cursor_var ref_type;
*/
--exp1
DECLARE
TYPE emp_cursor_type IS REF CURSOR; --定义一个游标变量类型
emp_cs emp_cursor_type;--使用这个游标类型定义一个游标变量
emp_record emp%ROWTYPE;
BEGIN
OPEN emp_cs FOR select * from emp;--打开游标变量时指定其所对应的select语句
LOOP
FETCH emp_cs INTO emp_record;--取数据就和普通的游标一样了
EXIT WHEN emp_cs%NOTFOUND;
dbms_output.put_line(emp_record.ename);
END LOOP;
CLOSE emp_cs;
END;
--exp2 定义游标变量是指定返回类型（智能是record）
DECLARE
TYPE v_record_type IS RECORD(
name varchar2(10),sal number
);
TYPE v_cursor_type IS REF CURSOR RETURN v_record_type;--返回上面定义的record类型
--声明一个record变量，一个cursor变量
v_record v_record_type;
v_cursor v_cursor_type;
BEGIN
OPEN v_cursor FOR select ename,sal from emp where deptno=10;
LOOP
FETCH v_cursor INTO v_record;
EXIT WHEN v_cursor%NOTFOUND;
dbms_output.put_line(v_record.name||'-'||v_record.sal);
END LOOP;
CLOSE v_cursor;
END;