sql 游标

sql 游标

一 什么是游标？

游标：
oracle在执行sql语句时，会给每个sql语句分配一个缓冲区，
用来临时存储sql语句和sql语句的结果及参数。
游标是指向这个缓冲区的一个指针（地址），
通过操作游标，可以处理sql的执行结果

布尔类型，boolean:它只有三个值，true,false,null，boolean类型不能直接打印，只能作为条件

游标分类

游标分为显式游标和隐式游标：**

游标的属性：
%found：返回一个boolean类型数据，当前游标指向的数据不为空时返回true，否则返回false
%notfound:和%found相反，当游标指向的数据为空时返回true,否则返回false
%isopen:返回一个boolean类型，当前游标如果是打开的返回true，否则返回false
%rowcount:返回一个数字，可以表示游标当前数据的数据行号，还可以表示游标指向的结果集数据条数。
游标属性的使用：
游标变量名%属性名

1.显式游标

自己定义使用的游标。

游标定义：
cursor 游标变量名 is select语句;

游标的使用：
1.打开游标
open 游标变量名;

2.使用fetch into语句让游标指向下一条数据，并把数据保存到一个变量中
fetch 游标变量 into 变量;

3.对当前变量中的数据进行处理（使用循环来遍历游标）

4.关闭游标
close 游标变量

通过游标打印emp表的所有的员工信息

--通过游标打印emp表的所有的员工信息
declare
   --定义一个游标，指向emp表的所有信息
   cursor c_emp is select * from emp;
   --声名一个变量，用来保存游标中的一条记录
   v_emp emp%rowtype;
begin
   --打开游标
   open c_emp;
   --loop循环遍历游标的结果集
   loop
      --执行fetch into 将游标指向下条数据
      fetch c_emp into v_emp;
      --判断游标是否为空，如果为空退出循环
      exit when c_emp%notfound;
      --处理当前v_emp变量中的数据
      dbms_output.put_line(c_emp%rowcount||','||v_emp.empno||','||v_emp.ename||','||v_emp.job||','||v_emp.sal); 
   end loop;
   dbms_output.put_line(c_emp%rowcount);
   --关闭游标
   close c_emp;
end;

while循环遍历游标

--while循环遍历游标
declare
   --定义一个游标，指向所有的员工信息
   cursor c_emp is select * from emp;
   --定义一个变量，用来保存游标的一条记录
   v_emp emp%rowtype;
begin
   --打开游标
   open c_emp;
   --将游标指向第一条数据
   fetch c_emp into v_emp;
   --循环遍历游标
   while c_emp%found loop
      --处理当前v_emp变量中的数据，
      dbms_output.put_line(c_emp%rowcount||','||v_emp.empno||','||v_emp.ename||','||v_emp.job||','||v_emp.sal); 
      --让游标指向下一条数据
      fetch c_emp into v_emp;
   end loop;
   --关闭游标
   close c_emp;
end;

遍历游标最简单的一种方式for循环遍历

--遍历游标最简单的一种方式for循环遍历
declare
   --定义一个游标变量，指向所有emp表的数据
   cursor c_emp is select * from emp;
begin
   for v_emp in c_emp loop
      --处理当前v_emp变量中的数据，
      dbms_output.put_line(c_emp%rowcount||','||v_emp.empno||','||v_emp.ename||','||v_emp.job||','||v_emp.sal); 
   end loop;
end;

2.隐式游标

隐式游标：它是在执行delete、update、insert语句时系统给这些语句自动分配一个游标，
游标的名字是sql，sql%found sql%notfound sql%rowcount sql%isopen;
begin
update emp set sal=sal+100;
dbms_output.put_line(sql%rowcount);
end;

3.游标参数

语法：
cursor 游标名(形参名 参数类型[:=默认值],…) is select语句
select语句中可以使用形参名作为查询条件

使用
打开游标
open 游标名(实参);
传参方式3种
1.传值
2.传变量，将变量的值传给游标
3.按位传值 ，形参名=>值
遍历游标和之间一样
关闭游标
close 游标名;

输入一个部门编号，查询出部门下的所有员工信息

--输入一个部门编号，查询出部门下的所有员工信息
declare
  --声名一个游标变量
  cursor cur is select * from emp where deptno=&deptno;
  --声名一个变量用来保存游标中的一条记录
  v_emp emp%rowtype;
  
begin
  --打开游标
  open cur;
  --遍历游标
  loop
    --将游标指向下一条数据
    fetch cur into v_emp;
    --判断游标是否为空
    exit when cur%notfound;
    --打印员工信息
    dbms_output.put_line(v_emp.ename||','||v_emp.job||','||v_emp.sal||','||v_emp.deptno);
  end loop;
  --关闭游标
  close cur;
end;

declare
  --声名一个游标变量
  cursor cur(v_dno number:=10) is select * from emp where deptno=v_dno;
  --声名一个变量用来保存游标中的一条记录
  v_emp emp%rowtype;
  --声名一个变量
  v_deptno emp.deptno%type;
begin
  --从键盘输入一个部门编号，存入v_deptno中
  v_deptno:=&deptno;
  --打开游标
  open cur(v_deptno); --open cur(10);
  --遍历游标
  loop
    --将游标指向下一条数据
    fetch cur into v_emp;
    --判断游标是否为空
    exit when cur%notfound;
    --打印员工信息
    dbms_output.put_line(v_emp.ename||','||v_emp.job||','||v_emp.sal||','||v_emp.deptno);
  end loop;
  --关闭游标
  close cur;
end;

declare
  --声名一个游标变量
  cursor cur(v_dno number:=10) is select * from emp where deptno=v_dno;
  --声名一个变量用来保存游标中的一条记录
  v_emp emp%rowtype;
  --声名一个变量
 -- v_deptno emp.deptno%type;
begin
  --从键盘输入一个部门编号，存入v_deptno中
  --v_deptno:=&deptno;
  --打开游标
  open cur; --open cur(10);
  --遍历游标
  loop
    --将游标指向下一条数据
    fetch cur into v_emp;
    --判断游标是否为空
    exit when cur%notfound;
    --打印员工信息
    dbms_output.put_line(v_emp.ename||','||v_emp.job||','||v_emp.sal||','||v_emp.deptno);
  end loop;
  --关闭游标
  close cur;
end;

declare
  --声名一个游标变量
  cursor cur(v_dno number:=10) is select * from emp where deptno=v_dno;
  --声名一个变量用来保存游标中的一条记录
  v_emp emp%rowtype;
  --声名一个变量
 -- v_deptno emp.deptno%type;
begin
  --从键盘输入一个部门编号，存入v_deptno中
  --v_deptno:=&deptno;
  --打开游标
  open cur(10); --open cur(10);
  --遍历游标
  loop
    --将游标指向下一条数据
    fetch cur into v_emp;
    --判断游标是否为空
    exit when cur%notfound;
    --打印员工信息
    dbms_output.put_line(v_emp.ename||','||v_emp.job||','||v_emp.sal||','||v_emp.deptno);
  end loop;
  --关闭游标
  close cur;
  
  --打开游标
  open cur(20); --open cur(10);
  --遍历游标
  loop
    --将游标指向下一条数据
    fetch cur into v_emp;
    --判断游标是否为空
    exit when cur%notfound;
    --打印员工信息
    dbms_output.put_line(v_emp.ename||','||v_emp.job||','||v_emp.sal||','||v_emp.deptno);
  end loop;
  --关闭游标
  close cur;
end;

4.游标类型的定义

定义游标类型语法：
type 类型名 is ref cursor; --声名一个游标类型

声名游标变量
游标变量名 游标类型名;

–游标变量的使用，只能用loop循环或while循环去变量
打开游标变量：
open 游标变量名 for select语句;
遍历和之间一样
关闭游标变量
close 游标变量

游标类型和游标变量，可以动态地指定游标所指向的结果集合

declare
  --声名一个游标类型
  type cur_type is ref cursor;
  --声名一个游标变量
  cur cur_type;
  --声名一个变量用来保存dept表中的一条记录
  v_dept dept%rowtype;
  --声名一个变量用来保存emp表中的一条记录
  v_emp emp%rowtype;
begin
  --查询所有的部门信息
  open cur for select * from dept;
  --遍历游标
  loop
    --将游标指向下一条数据
    fetch cur into v_dept;
    --判断退出条件
    exit when cur%notfound;
    --打印部门信息
    dbms_output.put_line(v_dept.deptno||','||v_dept.dname);
  end loop;
  --关闭游标
  close cur;
  if cur%isopen then
     dbms_output.put_line('游标已打开');
  end if;
  --查询所有的员工信息
  --打开游标
  open cur for select * from emp;
  --遍历游标
  loop
    --游标指向下一条记录
    fetch cur into v_emp;
    --判断退出条件
    exit when cur%notfound;
    --打印员工信息
    dbms_output.put_line(v_emp.ename||','||v_emp.job||','||v_emp.sal||','||v_emp.deptno);
  end loop;
  --关闭游标
  close cur;
end;