【Oracle】第16天游标

Life lies in diligence.

已于 2022-05-07 10:48:17 修改

阅读量403

点赞数

分类专栏： sql 文章标签：数据库 sql

于 2021-03-11 21:45:43 首次发布

本文链接：https://blog.youkuaiyun.com/csdn_okcheng/article/details/114663470

版权

sql 专栏收录该内容

17 篇文章

订阅专栏

定义

在 oracle运行sql语句时会给sql语句分配一个缓冲区，游标是指向这个缓冲区的地址，可以通过游标获取到sql语句的执行结果

显式游标

多用于DQL语句

定义语法：

CURSOR 游标名称 IS SELECT语句;

游标属性

DECLARE
	--定义一个游标，指向DEPT表的查询结果集
	CURSOR CUR IS SELECT * FROM DEPT;
	--三种声明变量的方式（1）查询出几列声明几个变量来保存
	--（2）使用DEPT%ROWTYPE 
	--(3)使用CUR%ROWTYPE --建议使用这种方式
	V CUR%ROWTYPE;
	
BEGIN
	OPEN CUR;
	FETCH CUR INTO V;
	--取游标
	IF CUR%ISOPEN  THEN 
		DBMS_OUTPUT.PUT_LINE('游标已打开');
	ELSE
		DBMS_OUTPUT.PUT_LINE('游标未打开');
	END IF;
	
	IF CUR%FOUND  THEN 
		DBMS_OUTPUT.PUT_LINE('有数据');
	ELSE
		DBMS_OUTPUT.PUT_LINE('没有数据');
	END IF;

	IF CUR%NOTFOUND  THEN 
		DBMS_OUTPUT.PUT_LINE('没有数据');
	ELSE
		DBMS_OUTPUT.PUT_LINE('有数据');
	END IF;
	--在没有执行FETCH INTO 语句之前，%FOUND与%NOTFOUND值不准确
	FETCH CUR INTO V;
	DBMS_OUTPUT.PUT_LINE(V.DEPTNO||','||V.DNAME||','||V.LOC);
	FETCH CUR INTO V;
	DBMS_OUTPUT.PUT_LINE(V.DEPTNO||','||V.DNAME||','||V.LOC);
	CLOSE CUR;
END;

DECLARE
	--定义一个游标，指向DEPT表的查询结果集
	CURSOR CUR IS SELECT * FROM DEPT;
	V CUR%ROWTYPE;
BEGIN
	OPEN CUR;
	FETCH CUR INTO V;
	--取游标
	DBMS_OUTPUT.PUT_LINE(V.DEPTNO||','||V.DNAME||','||V.LOC);
	FETCH CUR INTO V;
	DBMS_OUTPUT.PUT_LINE(V.DEPTNO||','||V.DNAME||','||V.LOC);
	FETCH CUR INTO V;
	DBMS_OUTPUT.PUT_LINE(V.DEPTNO||','||V.DNAME||','||V.LOC);
	CLOSE CUR;
END;

使用LOOP循环去遍历游标

DECLARE
	--定义一个游标，指向DEPT表的查询结果集
	CURSOR CUR IS SELECT * FROM DEPT;
	V CUR%ROWTYPE;
BEGIN
	OPEN CUR;
	LOOP
	FETCH CUR INTO V;
	--取游标
	EXIT WHEN CUR%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE(CUR%ROWCOUNT||','||V.DEPTNO||','||V.DNAME||','||V.LOC);
	END LOOP;
	CLOSE CUR;
END;

使用WHILE循环遍历游标

DECLARE
	--定义一个游标，指向DEPT表的查询结果集
	CURSOR CUR IS SELECT * FROM DEPT;
	V CUR%ROWTYPE;
BEGIN
	OPEN CUR;	
	FETCH CUR INTO V;
	WHILE CUR%FOUND LOOP 
		DBMS_OUTPUT.PUT_LINE(CUR%ROWCOUNT||','||V.DEPTNO||','||V.DNAME||','||V.LOC);
		FETCH CUR INTO V;
	END LOOP;
	CLOSE CUR;
END;

使用FOR循环遍历游标

DECLARE	
	CURSOR CUR IS SELECT * FROM DEPT;
BEGIN
	FOR V IN CUR LOOP 
	DBMS_OUTPUT.PUT_LINE(CUR%ROWCOUNT||','||V.DEPTNO||','||V.DNAME||','||V.LOC);
	END LOOP;
END;

缺点：出for循环，游标会自动关闭，关闭后不能使用%rowcount属性，并且不是全部的游标都可以用for循环遍历

隐式游标

多用于DML语句

隐式游标：在执行 insert, update, delete语句时，数据库会给分配一个游标，名称叫sql
sql%rowcount：取一个dml语句影响的数据库表数据条数

使用步骤

在这里插入图片描述

打开游标

OPEN游标名

读取游标中的数据

FETCH 游标 INTO 变量

将游标中的数据取出并保存到变量中

关闭游标

CLOSE 游标

属性

%ISOPEN

布尔类型变量，用来判断游标是否打开（可以在任何地方使用）

%FOUND

布尔类型，用来判断游标是否有数据，有则返回TRUE

%NOTFOUND

布尔类型，用来判断游标是否有数据，无则返回TRUE

只能在游标打开，并且执行了FETCH INTO 语句后才能使用

%ROWCOUNT

整数类型，可以表示游标中行号和数据总数

总结

(1)打开游标后 %found和%notfound都不用 %rowcount值是0
(2)执行 fetch into语句后，若游标中有数据，
% found=true
% notfound= false
在取数据的过程%rowcount每取1条数据自动+1
(3)关闭游标之后，只能使用%isopen属性

带参数的游标

语法

CURSOR游标（形参数据类型 [DEFAULT 默认值] ，形参数据类型…） is select语句（使用到参数）

参数传递方式

（1）传一个值
（2）传一个变量
（3）形参=>值

举例：根据部门编号查询部门下的员工，并打印员工信息

DECLARE 
	CURSOR CUR(DNO NUMBER) IS SELECT * FROM EMO WHERE DEPTNO=DNO;
	V CUR%ROWTYPE;
BEGIN
	OPEN CUR(10);
		LOOP
			FETCH CUR INTO V ;
			EXIT WHEN CUR%NOTFOUND ;
			DBMS_OUTPUT.PUT_LINE(V.EMPNO||','||V.ENAME||','||V.JOB||','||V.HIREDATE||','||V.SAL||','||V.COMM||','||V.DEPTNO)
		END LOOP;
	CLOSE CUR ;
END;

declare
  --定义一个游标
  cursor cur(dno number) is select * from emp where deptno=dno;
  --声名一个变量保存游标的一条记录
  v cur%rowtype;
  --声名一个变量接收键盘输入的一个部门编号
  v_deptno number:=&部门编号;
begin
  --打开游标
  open cur(v_deptno);
  --遍历
  fetch cur into v;
  while cur%found loop
    --循环体语句
    dbms_output.put_line(v.empno||','||v.ename||','||v.job||','||v.mgr||','||v.hiredate||','||v.sal||','||v.comm||','||v.deptno);
    --循环控制语句
    fetch cur into v;
  end loop;
  
  --关闭游标
  close cur;
end;

declare
    --定义一个游标
    cursor cur(dno number default 20) is select * from emp where deptno=dno;
begin
    for v in cur loop
      --循环体语句
      dbms_output.put_line(v.empno||','||v.ename||','||v.job||','||v.mgr||','||v.hiredate||','||v.sal||','||v.comm||','||v.deptno);
    end loop;
end;

写一个代码块，根据部门编号，和键盘输入的一个工资值，查询该部门下工资大于该值的员工的信息

declare
    --定义一个游标
    cursor cur(dno number default 20,v_sal number) is select * from emp where deptno=dno and sal>v_sal;
begin
    for v in cur(30,1500) loop
      --循环体语句
      dbms_output.put_line(v.empno||','||v.ename||','||v.job||','||v.mgr||','||v.hiredate||','||v.sal||','||v.comm||','||v.deptno);
    end loop;
end;
select * from emp where deptno=? and sal>?;
declare
    --定义一个游标
    cursor cur(dno number default 20,v_sal number) is select * from emp where deptno=dno and sal>v_sal;
begin
    for v in cur(v_sal=>1500) loop
      --循环体语句
      dbms_output.put_line(v.empno||','||v.ename||','||v.job||','||v.mgr||','||v.hiredate||','||v.sal||','||v.comm||','||v.deptno);
    end loop;
end;

游标类型和游标参数

第三种不能用for循环遍历的游标

游标类型的定义语法：

TYPE 类型名称 IS REF CURSOR;
游标类型变量名 类型名;

游标变量的使用
1.打开游标
OPEN 游标 FOR SELECT 语句
2.遍历游标
FETCH 游标 INTO 变量
3.关闭游标
CLOSE 游标

举例：写一个代码块，查询所有员工的姓名，并打印

select ename from emp;
declare
   --定义一个游标类型
   type ctype is ref cursor;
   --声名一个游标变量
   cur ctype;
   --声名一个变量保存游标中的一条记录
   v varchar2(30);
begin
   --打开游标
   open cur for select ename from emp;
   --loop循环遍历
   loop
     --fetch into语句
     fetch cur into v;
     --退出循环语句
     exit when cur%notfound;
     --循环体语句
     dbms_output.put_line(v);
   end loop;
   --关闭游标
   close cur;
end;