游标

1初识游标

概念

oracle在执行sql语句时,给sql语句分配了一个缓冲区
游标是指向缓冲区的一个地址

缓冲区中包含了sql语句的运行结果

• 其中对于select语句,保存的是查询结果
• 对于update,delete,insert语句,缓冲区保存的是影响的数据库条数

游标的分类

显式游标和隐式游标

显式游标

使用cursor语句显式定义的游标,游标被定义之后,需要打开并提取游标

隐式游标

由oracle为每一个不属于显式游标的sql dml语句都创建一个隐式的游标
由于隐式游标没有名字,因此叫做sql游标

简单使用游标

declare
  --声明一个游标指向emp表的所有数据
  cursor cur is select * from emp;
  --声明一个变量保存游标的记录
  v emp%rowtype;
begin
  --打开游标
  open cur;
  --访问游标中的数据
  fetch cur into v;
  --打印变量v的ename值
  dbms_output.put_line(v.ename);
  --访问游标中的数据
  fetch cur into v;
  --打印变量v的ename值
  dbms_output.put_line(v.ename);
  --关闭游标
  close cur;
end;

输出

june
S_HH%GGH

2游标的属性

2.1%isopen属性

判断对应的游标变量是否打开,如果游标变量打开,则返回true,否则返回false

declare
  --定义一个游标指向emp表中所有数据
  cursor emp_cur is select * from emp;
begin
  --显示游标状态
  if emp_cur%isopen then
    dbms_output.put_line('1游标已经打开');
  else
    dbms_output.put_line('1游标没有打开');
  end if;
  --如果游标没有打开,则打开
  if not emp_cur%isopen then
    open emp_cur;
  end if;
  --显示游标状态
  if emp_cur%isopen then
    dbms_output.put_line('2游标已经打开');
  else
    dbms_output.put_line('2游标没有打开');
  end if;
  close emp_cur;
end;

输出

1游标没有打开
2游标已经打开

通过代码可以看到,
在调用open方法显式打开游标之前,游标的%isopen属性为false,
调用open打开游标之后,%isopen属性为true

2.2%found

检查是否从结果集中提取到了数据
在调用fetch语句获取数据之前,%found会产生null值,
而此后每取得一条数据,其值会为true
如果最后一次取得数据失败,其值会变为false

declare
  --声明一个游标
  cursor emp_cur is select * from emp;
  --声明变量,存放游标记录
  v emp_cur%rowtype;
begin
  --打开游标
  open emp_cur;
  --在使用fetch提取游标数据之前,值为null
  if emp_cur%found is null then
    dbms_output.put_line('%found属性为null');
  end if;
  --循环提取游标数据
  loop
    --使用fetch...into...语句提取游标数据
    fetch emp_cur into v;
    --每循环一次判断%found属性值,如果该值为false,表示提取完成,退出循环
    exit when not emp_cur%found;
    --获取表中所有员工编号
    dbms_output.put_line(v.empno);
  end loop;
  close emp_cur;
end;

输出

%found属性为null
9123
7777
7369
7499
7521
7566
7654
...

2.3%notfound属性

该属性与%found属性相反,当没有从游标中提取数据时,该属性返回true,否则返回false
在loop循环内部,每fetch一次游标数据后,
都会使用exit when判断%notfound属性的值是否为true
如果是true,则表示游标已无任何数据可以提取

declare
  --声明一个游标
  cursor emp_cur is select * from emp;
  --声明变量,存放游标记录
  v emp_cur%rowtype;
begin
  --打开游标
  open emp_cur;
  --在使用fetch提取游标数据之前,值为null
  if emp_cur%found is null then
    dbms_output.put_line('%found属性为null');
  end if;
  --循环提取游标数据
  loop
    --使用fetch...into...语句提取游标数据
    fetch emp_cur into v;
    --每循环一次判断%found属性值,如果该值为false,表示提取完成,退出循环
    exit when emp_cur%notfound;
    --获取表中所有员工编号
    dbms_output.put_line(v.empno);
  end loop;
  close emp_cur;
end;

输出

%found属性为null
9123
7777
7369
7499
7521
7566

2.4%rowcount属性

返回到目前为止已经从游标中取出的记录的行数
当游标被打开时,%rowcount值为0,每取到一条数据,
%rowcount的值就加1

declare
  --声明一个游标变量
  cursor emp_cur is select * from emp;
  --声明一个变量,存放游标记录
  v emp_cur%rowtype;
begin
  --打开游标
  open emp_cur;
  --遍历循环
  loop
    --提取游标数据
    fetch emp_cur into v;
    --退出循环条件
    exit when emp_cur%notfound;
    --打印%rowcount值
    dbms_output.put_line('当前已提取'||emp_cur%rowcount);
  end loop;
  close emp_cur;
end;

输出

当前已提取1
当前已提取2
当前已提取3
...
当前已提取14
当前已提取15
当前已提取16

3游标参数

3.1单个参数

语法

cursor 游标(参数名 数据类型 [default 默认值])
is select语句

declare
  --定义一个游标
  cursor cur_emp(dno number) is select * from emp where deptno=dno;
  --定义一个变量,保存游标记录
  v cur_emp%rowtype;
begin
  --打开游标,并传值
  open cur_emp(10);
  loop
    --将游标中的一条记录保存到变量中
    fetch cur_emp into v;
    --判断退出条件
    exit when cur_emp%notfound;
    --打印变量的ename和deptno值
    dbms_output.put_line(v.empno||v.deptno);
  end loop;
  close cur_emp;
end;

输出

912310
778210
783910
793410

3.2多个参数

declare
  --定义一个游标
  cursor cur_emp(dno number default 20,vsal number) is select * from emp where deptno=dno and sal<vsal;
  --定义一个变量,保存游标记录
  v cur_emp%rowtype;
begin
  --打开游标,并传值
  open cur_emp(30,1000);
  loop
    --将游标中的一条记录保存到变量中
    fetch cur_emp into v;
    --判断退出条件
    exit when cur_emp%notfound;
    --打印变量的ename和deptno值
    dbms_output.put_line(v.empno||v.deptno||v.sal);
  end loop;
  close cur_emp;
end;

输出

790030950

3.3传变量

declare
  --定义一个游标
  cursor cur_emp(dno number) is select * from emp where deptno=dno;
  --定义一个变量,保存游标记录
  v cur_emp%rowtype; 
  --获取用户输入部门
  vdeno dept.deptno%type:=&部门编号;
begin
  --打开游标,并传值
  open cur_emp(vdeno);
  loop
    --将游标中的一条记录保存到变量中
    fetch cur_emp into v;
    --判断退出条件
    exit when cur_emp%notfound;
    --打印变量的ename和deptno值
    dbms_output.put_line(v.empno||v.deptno);
  end loop;
  close cur_emp;
end;

弹窗输入30,输出

749930
752130
765430
769830
784430
790030

4游标遍历

列出每个员工的姓名、部门名称并编程显示第10个到第20个记录

4.1loop遍历游标

declare
  --声明一个游标变量
  cursor cur_ed is select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno;
  --声明一个变量保存游标的记录
  v cur_ed%rowtype;
begin
  --打开游标
  open cur_ed;
  --遍历游标
  loop
    --访问游标中的数据
    fetch cur_ed into v;
    --退出loop循环条件
    exit when cur_ed%notfound;
    --根据条件,输出到屏幕
    if cur_ed%rowcount between 10 and 20 then
      dbms_output.put_line(v.ename||','||v.dname);
    end if;

  end loop;
  --关闭游标  
  close cur_ed;
end;

输出

SCOTT,RESEARCH
KING,ACCOUNTING
TURNER,SALES
ADAMS,RESEARCH
JAMES,SALES
FORD,RESEARCH
MILLER,ACCOUNTING

4.2while循环

declare
  --声明一个游标
  cursor cur_ed is select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno;
  --声明一个变量,保存游标的记录
  v cur_ed%rowtype;
begin
  --打开游标
  open cur_ed;
  --执行fetch into语句,让游标指向第一条数据
  fetch cur_ed into v;
  --根据条件进入循环
  while cur_ed%found loop
    --打印变量的值
    if cur_ed%rowcount between 10 and 20 then
      dbms_output.put_line(v.ename||','||v.dname);
    end if;
    --让游标指向下一条数据
    fetch cur_ed into v; 
  end loop;
  --关闭游标
  close cur_ed;
end;

输出

SCOTT,RESEARCH
KING,ACCOUNTING
TURNER,SALES
ADAMS,RESEARCH
JAMES,SALES
FORD,RESEARCH
MILLER,ACCOUNTING

4.3for循环

declare
  --声明一个游标变量
  cursor cur_ed is select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno;
begin
  for v in cur_ed loop
    --打印变量的值
    if cur_ed%rowcount between 10 and 20 then
      dbms_output.put_line(v.ename||','||v.dname);
    end if;
  end loop;
end;

输出

SCOTT,RESEARCH
KING,ACCOUNTING
TURNER,SALES
ADAMS,RESEARCH
JAMES,SALES
FORD,RESEARCH
MILLER,ACCOUNTING

5游标变量

前面游标的使用,
发现每定义一个游标,就为其绑定一个查询语句
这种游标称为静态游标
游标变量是另一种类型的游标,在定义时并不绑定到具体的查询,
而是可以打开任何类型兼容的查询,灵活性较好

5.1语法

游标类型的定义
type 类型名称 is ref cursor;

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

游标变量的使用

• 1,打开游标
  open 游标变量 for select 语句;
• 2,遍历游标
  fetch 游标变量 into 变量;
• 3,关闭游标
  close 游标变量;

注意:游标变量不能使用for循环遍历

loop

declare
  --定义一个游标类型
  type ctype is ref cursor;
  --声明一个游标变量
  cur ctype;
  --声明一个变量保存游标中的一条记录
  v dept%rowtype;
begin
  --打开游标
  open cur for select * from dept;
  --遍历游标
  loop
    --将游标中的数据保存到变量中
    fetch cur into v;
    --判断退出条件
    exit when cur%notfound;
    --打印变量值
    dbms_output.put_line(v.deptno||','||v.dname);
  end loop;
  --关闭游标
  close cur;
end;

输出

10,ACCOUNTING
20,RESEARCH
30,SALES
40,OPERATIONS

while

declare
  --声明一个游标类型
  type etype is ref cursor;
  --声明一个游标变量
  cur etype;
  --声明一个变量保存游标中的一条记录
  v emp%rowtype;
begin
  --打开游标
  open cur for select * from emp where deptno=20;
  --fetch into语句将游标指向第一条语句
  fetch cur into v;
  --遍历游标
  while cur%found loop
    --打印变量值
    dbms_output.put_line(v.deptno||','||v.ename);
    --循环控制
    fetch cur into v;
  end loop;
  --关闭游标
  close cur;
end;

输出

20,S_HH%GGH
20,SMITH
20,JONES
20,SCOTT
20,ADAMS
20,FORD

补充

隐式游标

隐式游标：在执行insert、update、delete语句时，oracle给它们分配了一个游标，游标名叫sql
可以调用游标的属性 %rowcount

begin
  update emp set sal=sal+500 where deptno=30;
  --打印update语句修改的数据条数
  dbms_output.put_line(sql%rowcount);
  rollback;
end;

输出6

oracle游标

原文：https://www.cnblogs.com/inmeditation/p/12070129.html