存储过程——游标基础用法已经重要知识点

Oracle游标常用属性：

%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。

%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。

%ROWCOUNT：当前时刻已经从游标中获取的记录数量。

%ISOPEN：是否打开

 

--游标使用的几个重要点

使用%ROWTYPE属性可以声明一个基于表或游标的行对象，也称为记录对象

使用FETCH...BULK COLLECT INTO语句提取所有数据
在显式游标中使用FETCH ..BULK COLLECT INTO..LIMIT语句提取部分数据

 

 

使用隐式游标和显式游标：

1.查询返回单行记录时→隐式游标；

2.查询返回多行记录并逐行进行处理时→显式游标

 

--使用%ROWTYPE属性不仅可以基于表和视图定义记录变量，也可以基于游标定义记录变量

declare
CURSOR cur_emp IS SELECT * FROM emp;
row_emp cur_emp%ROWTYPE;
BEGIN
OPEN cur_emp;
FETCH cur_emp INTO row_emp;
WHILE cur_emp%FOUND
LOOP
dbms_output.put_line(row_emp.empno||'----'||row_emp.ename);
FETCH cur_emp INTO row_emp;
END LOOP;

close cur_emp;

END;

 

·注意：1、如果游标打开之前或关闭之后，使用游标属性，Oracle会抛出一个INVALID_CURSOR错误（ORA-01001）；

        2、如果在第一次fetch后结果集是空的，%found=false，%NotFound=true，%ROWCOUNT=0；

        3、如果使用了BULK COLLECT，那么%ROWCOUNT的值可能不是0或1，实际上他返回的是提取到相关集合的行数。

--Where Current Of语句允许你更新或者是删除最后由cursor取的记录

--使用显式游标修改数据(给所有的部门经理加薪1000)
DECLARE
CURSOR emp_cur IS
SELECT empno,ename,sal FROM emp WHERE job='MANAGER' FOR UPDATE;
emp_row emp_cur%ROWTYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_row;
IF emp_cur%NOTFOUND THEN
EXIT;
ELSE
UPDATE emp SET sal=sal+1000 WHERE CURRENT OF emp_cur;
END IF;
END LOOP;
COMMIT;
CLOSE emp_cur;
END;

 

--游标for循环，这个循环方式很实用（给所有的部门经理减薪1000）
DECLARE
CURSOR emp_cur IS
SELECT empno,ename,sal FROM emp WHERE job='MANAGER' FOR UPDATE;
BEGIN
FOR emp_row IN emp_cur
LOOP
UPDATE emp SET sal=sal-1000 WHERE CURRENT OF emp_cur;
END LOOP;
COMMIT;
END;

 

 

--还有一个游标for循环例子:有点像表结构对象或者理解成一个变量对象

begin

     for employee in (select empno ,ename ,job ,sal from emp where deptno = 20)

 

     loop

         dbms_output.put_line(''}}employee.sal(*每个属性都可以));

     end loop;

end;

 

 

--我们可以看到游标FOR循环确实很好的简化了游标的开发，我们不在需要open、fetch和close语句，不在需要用%FOUND属性检测是否到最后一条记录，这一切Oracle隐式的帮我们完成了。
--给经理加薪5000，其他加薪1000
DECLARE
CURSOR emp_cur IS
SELECT * FROM emp FOR UPDATE;
BEGIN
 FOR emp_row IN emp_cur
 LOOP
 IF emp_row.job='MANAGER' THEN
 UPDATE emp SET sal=sal+5000 WHERE CURRENT OF emp_cur;

 

 ELSE
 UPDATE emp SET sal=sal+1000 WHERE CURRENT OF emp_cur;
 END IF;
 END LOOP;
 END;

 

 

 --这个里面有一个将游标当成java  list 集合的用法(i)来获取对应下标的值
②在显式游标中，使用FETCH...BULK COLLECT INTO语句提取所有数据
DECLARE
CURSOR emp_cursor IS
SELECT ename FROM emp WHERE deptno=10;
TYPE ename_table_type IS TABLE OF VARCHAR2(10);
ename_table ename_table_type;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO ename_table;
FOR i IN 1..ename_table.COUNT LOOP
dbms_output.put_line(ename_table(i));
END LOOP;
CLOSE emp_cursor;
END;
/

 

--有oracle 集合的用法

 ③在显式游标中使用FETCH ..BULK COLLECT INTO..LIMIT语句提取部分数据
下面以每次提取5行数据为例，说明使用LIMIT子句限制提取行的方法
DECLARE
TYPE name_array_type IS VARRAY(5) OF VARCHAR2(10);
name_array name_array_type;
CURSOR emp_cursor IS SELECT ename FROM emp;
rows INT:=5;
v_count INT:=0;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor BULK COLLECT INTO name_array
LIMIT rows;
dbms_output.put('雇员名');
FOR i IN 1..(emp_cursor%ROWCOUNT-v_count) LOOP
dbms_output.put_line(name_array(i)||' ');
END LOOP;
dbms_output.put_line;//换行
v_count:=emp_cursor%ROWCOUNT;
EXIT WHEN emp_cursor%NOTFOUND;
END LOOP;
END;
/