oracle游标的概念和简单应用实例讲解
概念:
游标用于处理select语句的查询结果(结果集 resultset) ,
有些人也认为 游标就是查询结果
使用步骤与语法格式(非滚动游标)
1. 声明游标
申明区:
cursor 游标名称 is select语句;
2. 打开游标
open 游标名称;
3. 提取一行数据(游标向下移动)
fetch 游标名称 into 变量;--将游标中的某一行赋值给一个变量!
4. 关闭游标
close 游标名称;
1. 资源为什么要关闭:
句柄数量 1024
2. 已关闭的资源可以重复关闭吗?
游标的打开与关闭 不可重复执行, 否则报错
例子: 定义一个游标 , 用来存放s_emp表中的 id, last_name,salary ,然后提取游标的前五条数据, 并打印 , 最后关闭游标!
set serveroutput on;
declare
/*声明游标*/
cursor s_emp_c is select id,last_name,salary from s_emp;
/*声明一个类型, 匹配结果集中一行的数据类型*/
type myemp is record(
id s_emp.id%type,
last_name s_emp.last_name%type,
salary s_emp.salary%type
);
/*声明一个变量,用来承接游标取出的数据*/
var_me myemp;
begin
/*打开游标*/
open s_emp_c;
/*游标向下移动一行, 并取出数据*/
for i in 1..5 loop
fetch s_emp_c into var_me;
dbms_output.put_line('这个员工的编号为:'||var_me.id||',名称为:'||var_me.last_name||',他的月薪为:'||var_me.salary);
end loop;
close s_emp_c;
end;
使用 游标名称%rowtype
定义一个游标 , 用来存放s_emp表中的 id, last_name,salary ,然后提取游标的前五条数据, 并打印 , 最后关闭游标!
set serveroutput on;
declare
/*声明游标*/
cursor s_emp_c is select id,last_name,salary from s_emp;
/*声明一个变量, 通过游标名称%rowtype 获取一个匹配当前游标中数据类型订单record变量
*/
var_me s_emp_c%rowtype;
begin
/*打开游标*/
open s_emp_c;
/*游标向下移动一行, 并取出数据*/
for i in 1..5 loop
fetch s_emp_c into var_me;
dbms_output.put_line('这个员工的编号为:'||var_me.id||',名称为:'||var_me.last_name||',他的月薪为:'||var_me.salary);
end loop;
close s_emp_c;
end;
/
通过游标 获取 多个表中的数据
查询s_dept 中的name字段 和 s_region中的name字段 ,通过游标进行操作
set serveroutput on;
declare
cursor dr_c is select d.name dn,r.name rn from s_dept d,s_region r where d.region_id=r.id;
var_dr dr_c%rowtype;
begin
open dr_c;
fetch dr_c into var_dr;
dbms_output.put_line('部门名称:'||var_dr.dn||',地区名称:'||var_dr.rn);
close dr_c;
end;
/
游标属性
使用一些属性, 进行循环遍历游标操作
1. 游标名称%found
如果游标提取到了新数据 , 则返回true , 否则返回false .
2. 游标名称%notfound
如果游标提取不到新数据 , 则返回true , 提取到了数据返回false
-------------------------------------------
上面的两个属性, 想要使用, 必须满足两个前提条件:
1. 游标 必须 是打开状态 ! 否则出现非法游标操作
2. 游标 必须 执行过 fetch (游标在结果集中任意一行) , 否则返回null
通过游标获取s_emp表格中的(id,last_name,salary), 获取所有行
declare
cursor myemp is select id,last_name,salary from s_emp;
var_me myemp%rowtype;
begin
open myemp;
loop
fetch myemp into var_me;
exit when myemp%notfound;
dbms_output.put_line('员工编号:'||var_me.id||',姓名:'||var_me.last_name||',员工月薪:'||var_me.salary);
end loop;
close myemp;
end;
智能循环遍历游标
for循环 , 在进行游标的迭代时 ,
会自动定义变量,
自动打开游标,
自动提取数据,
自动关闭游标 .
使用for循环 获取s_emp表格中的id,last_name,salary
declare
cursor myemp is select id,last_name name,salary from s_emp;
begin
for var_me in myemp loop
/*
在这个循环中 , var_me就是每次循环迭代时的 每一行的数据
*/
dbms_output.put_line('id='||var_me.id||', name='||var_me.name||', 月薪='||var_me.salary);
end loop;
end;
/
带参数的游标
一个游标在定义时 ,
可以设计,在打开时需要传递参数 , 这个参数是 可以在select语句中使用的
格式:
cursor 游标名称(参数列表) is select语句
1. 参数的类型不能使用长度修饰 , 可以通过%type传递类型
2. 参数传递的时机:
在打开游标时 传入 ,
例如:
open 游标名称(参数);
根据用户输入的id , 查询一个员工的信息(id,last_name_salary)
set serveroutput on;
declare
cursor myemp(var_id number) is select id,last_name name,salary from s_emp where id=var_id;
var_me myemp%rowtype;
var_input number;
begin
var_input :=&请输入您要查询的员工的id;
open myemp(var_input);
fetch myemp into var_me;
dbms_output.put_line(var_me.name);
close myemp;
end;
/
使用智能循环时 ,如何传递参数
根据用户输入的id , 查询员工编号大于用户输入的员工编号的员工的信息(id,last_name_salary)
set serveroutput on;
declare
cursor myemp(var_id number) is select id,last_name name,salary from s_emp where id>var_id;
var_input number;
begin
var_input :=&请输入员工的id;
for var_me in myemp(var_input) loop
dbms_output.put_line('id='||var_me.id);
end loop;
end;
/
参考游标 ref cursor
概念:
游标对应的select语句, 不必在申明区就指定, 可以在打开游标时 指定sql语句
语法格式:
1. 申明区定义一个参考游标的类型
type 参考游标类型名称 is ref cursor;
2. 定义一个变量
变量名称 参考游标类型;
3. 打开游标, 并关联select语句
open 游标变量名称 for 'select语句';
例子:
根据用户输入的id , 查询员工编号大于用户输入的员工编号的员工的信息
之前进行游标的遍历时 ,我们通过游标的名称%rowtype获取类型
在参考游标中 时无法这样去做的, 因为在申明区, 游标还不知道自己的行类型
set serveroutput on;
declare
type mc is ref cursor;
var_mc mc;
-- 这个字符串 , 就是用来做sql查询语句的
var_select varchar2(3000);
var_input number;
type myemp is record(
id s_emp.id%type,
salary s_emp.salary%type
);
var_me myemp;
begin
var_input:=&请输入员工id;
var_select:='select id,salary from s_emp where id>'||var_input;
open var_mc for var_select;
loop
fetch var_mc into var_me;
exit when var_mc%notfound;
dbms_output.put_line('员工的id:'||var_me.id||',员工的薪资:'||var_me.salary);
end loop;
end;
/