Oracle中的游标
目录一、游标界说
二、游标分类
2.1静态游标
2.1.1. 隐式游标：
2.1.2. 显式游标：
2.1.3. 游标变量：
2.1.4. 游标参数：
2.2动态游标
2.2.1强类型游标
2.2.2弱类型游标
2.2.3平凡动态游标
三、游标的优缺点
优点：
缺点：
四、游标使用留意事项
五、游标的作用
一、游标界说
游标即cursor，是一种用于遍历效果集的数据类型。它是一个指向效果集的指针，可以用于遍历效果集中的每一行数据，但是一次只能指向一行。游标通常用于存储过程和函数中，以便在处理效果集时可以或许逐行处理数据。
游标提供了在逐行的基础上操纵表中数据的方法，游标的效果集是由select语句产生的。
拿个表来打比方，游标打开时会在第一行数据的上面每fetch一次，下去一行。
二、游标分类
游标有两种类型：静态游标和动态游标。接下来分别形貌各个类型的游标：
2.1静态游标
静态游标是一种在编译时就确定效果集的游标，其效果集不会随着时间或用户的不同而发生变化。在Oracle中，静态游标包括以下几种类型：
2.1.1. 隐式游标：
由SELECT语句主动创建的游标，其效果集在编译时就已经确定，不会随时间或用户的不同而发生变化。而且隐式游标是Oracle数据库默认使用的游标类型。它是一种只能在SQL语句中使用的游标，不能在PL/SQL代码中使用。隐式游标会在执行一条SELECT语句时主动创建，并且只能返回一行数据。例如：
SELECT column1, column2
FROM table1
WHERE column3 = 'value';
SELECT *
FROM employees
WHERE department_id = 10;
上面的SELECT语句会主动创建一个隐式游标，其效果集是所有部门ID为10的员工。由于效果集在编译时就已经确定，因此这是一种静态游标。
总结：
隐式游标不用声明，不用打开，不用赋值，不用关闭，这些都是oracle主动完成的。
常见的方式有 delete 、update、 insert select into 单行赋值（主要用来统计dml操纵了多少行）格式：
INSERT UPDATE DELETE SELECT..int隐式游标的四个属性
[*]sql%isopen 判断游标是否打开 ，永远返回假
[*]sql%found 判断指针里面是否有值 ，如果指针里曾经有值 ，返回的是真
[*]sql%notfound 和found相反
[*]sql%rowcount 返回指针指过的行数
举例阐明：
要求给emp1表的10部门员工工资增长500，并打印有多少人增长了 工资：
begin
update emp1 set sal=sal+500 where deptno=10;
dbms_output.put_line(sql%rowcount);
if sql%found then dbms_output.put_line('有人涨工资了');
end if;
end; 2.1.2. 显式游标：
由DECLARE、OPEN、FETCH和CLOSE等语句手动创建的游标，其效果集在界说游标时就已经确定，不会随时间或用户的不同而发生变化。并且显式游标是由步伐员在PL/SQL代码中显式声明和使用的游标类型。它可以在步伐中读取和处理多行数据。例如：
DECLARE
cursor_name CURSOR FOR
SELECT column1, column2
FROM table1
WHERE column3 = 'value';
var1 table1.column1%TYPE;
var2 table1.column2%TYPE;
BEGIN
OPEN cursor_name;
LOOP
FETCH cursor_name INTO var1, var2;
EXIT WHEN cursor_name%NOTFOUND;
-- 处理数据
END LOOP;
CLOSE cursor_name;
END;
-------------------------------------------------------------------------------
DECLARE
CURSOR c_emp IS SELECT * FROM employees WHERE department_id = 10;
v_emp employees%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_emp;
EXIT WHEN c_emp%NOTFOUND;
-- 对结果集进行处理
END LOOP;
CLOSE c_emp;
END;
上面的代码界说了一个名为c_emp的显式游标，其效果集是所有部门ID为10的员工。由于效果集在界说游标时就已经确定，因此这是一种静态游标。
其实表现游标又分为带参数的表现游标和不带参数的表现游标。
Ⅰ、不带参数的显式游标
声明方法：
cursor 游标名 is select 语句 例如：
打印部门dept表中的所有部门名称（dept表一共有4个部门）：
declare
cursor c1 is select dname from dept;
v_name varchar2(20);
begin
open c1;
fetch c1 into v_name;
dbms_output.put_line(v_name);
fetch c1 into v_name;
dbms_output.put_line(v_name);
fetch c1 into v_name;
dbms_output.put_line(v_name);
fetch c1 into v_name;
dbms_output.put_line(v_name);
close c1;
end; 循环打印dept表中的所有部门名称：
declare
cursor c1 is select dname from dept;
v_name varchar2(20);
begin
open c1;
for i in 1..4 loop
fetch c1 into v_name;
dbms_output.put_line(v_name);
end loop;
close c1;
end; 打印员工emp表所有的员工编号 （表内一共有14个员工信息）：
declare
cursor c1 is select empno from emp;
v_name varchar2(20);
begin
open c1;
for i in 1..14 loop
fetch c1 into v_name;
dbms_output.put_line(v_name);
end loop;
close c1;
end; 但是如果我们不知道一张表内某个列中值有多少行时，就得用到游标的四个属性 ：
[*]游标名%isopen 判断游标是否打开，是的话返回真 ，否则返回假
[*]游标名%found 判断游标的指针里是否有值，有值返回真 ，没值返回假
[*]游标名%notfound 判断游标的指针里是否没值，没值返回真 ，有值返回假
[*]游标名%rowcount 游标的指针指了多少行，返回的是行数
平凡循环搭配不带参数的显式游标的步调序次如下：
[*]1.打开游标
[*]2.loop
[*]3.fetch
[*]4.exit when
[*]5.打印
[*]6.end loop
[*]7.关闭游标
比如：
打印员工emp表中的所有员工编号 ：
declare
cursor c1 is select empno from emp;
v_empno number;
begin
open c1;
loop
fetch c1 into v_empno;
exit when c1%notfound;
dbms_output.put_line(v_empno);
end loop;
close c1;
end; 打印emp表司理是KING的员工姓名:
declare
cursor c1 is select ename
from emp
where mgr=(select empno
from emp
where ename='KING');
v_name varchar2(20);
begin
open c1;
loop
fetch c1 into v_name;
exit when c1%notfound;
dbms_output.put_line(v_name);
end loop;
close c1;
end; 打印 emp表司理是KING的姓名以及人数:
declare
cursor c1 is select ename
from emp
where mgr=(select empno
from emp
where ename='KING');
v_name varchar2(20);
begin
open c1;
loop
fetch c1 into v_name;
exit when c1%notfound;
dbms_output.put_line(v_name);
end loop;
dbms_output.put_line('有'||c1%rowcount||'人');
close c1;
end; while 循环和游标 -WHILE循环+%found的步调 ：
[*]1.open 打开游标
[*]2.fetch 先提取一次 以便进入循环
[*]3.while
[*]4.打印 进入循环先打印一次 制止遗漏第一条数据
[*]5.fetch
[*]6.end loop
[*]7.close
为什么这里要多打印一次呢，因为在open后fetch前，我们的游标%found是为假的，以是就无法进入while循环,必须先fetch一遍！！！
举例阐明：
打印dept表中的所有部门名称：
declare
cursor c1 is select dname from dept;
v_name varchar2(20);
begin
open c1;
fetch c1 into v_name; --先提取一次
while c1%found loop
dbms_output.put_line(v_name);
fetch c1 into v_name;
end loop;
close c1;
end; 要求 打印emp表职位是销售的员工信息，使用while循环和游标方法，则语句如下：
declare
cursor c is select *
from emp
where job='SALESMAN';
v emp%rowtype;
begin
open c;
fetch c into v;
while c%found loop
dbms_output.put_line(lpad(v.empno,9,' ')||''||
lpad(v.ename,9,' ')||''||
lpad(v.job,9,' ')||''||
lpad(v.mgr,9,' ')||''||
lpad(v.hiredate,9,' ')||''||
lpad(v.sal,9,' ')||''||
lpad(v.comm,9,' ')||''||
lpad(v.deptno,9,' ')
);
fetch c into v;
end loop;
close c;
end; Ⅱ、带参数的显式游标
声明参数时，只用写类型，不用写长度，参数可以在where条件后调用。使用时需要在open游标后加括号里面填上参数（多个参数用逗号隔开）。格式为：
CURSOR cur_name(参 类型,..) IS SELECT 语句(包含参); 比如：
要求打印emp表中部门编号是10的员工姓名，然后再打印emp表中部门编号是20的员工编号：
declare
cursor c1(v number) is
select ename,empno from emp where deptno=v;
vname varchar2(20);
vempno number;
begin
open c1(10);
loop
fetch c1 into vname,vempno;
exit when c1%notfound;
dbms_output.put_line(vname);
end loop;
close c1;
open c1(20);
loop
fetch c1 into vname,vempno;
exit when c1%notfound;
dbms_output.put_line(vempno);
end loop;
close c1;
end; 请打印emp表中职位是CLERK的姓名，职位是MANAGER的工资 ：
declare
cursor c1 (v varchar2) is
select ename,sal from emp where job=v;
v_name varchar2(20);
v_sal number;
begin
open c1('CLERK');
loop
fetch c1 into v_name,v_sal;
exit when c1%notfound;
dbms_output.put_line(v_name);
end loop;
close c1;
open c1('MANAGER');
LOOP
fetch c1 into v_name,v_sal;
exit when c1%notfound;
dbms_output.put_line(v_sal);
end loop;
close c1;
end; 2.1.3. 游标变量：
可以将查询效果集存储在游标变量中，以便在后续的处理中使用。游标变量在界说时需要指定效果集，其效果集也是在编译时就已经确定的。游标变量是一种PL/SQL变量，它可以存储一个游标对象。游标变量可以在步伐中传递和重复使用，可以在不同的步伐块中声明和使用。例如：
DECLARE
cursor_name SYS_REFCURSOR;
var1 table1.column1%TYPE;
var2 table1.column2%TYPE;
BEGIN
OPEN cursor_name FOR
SELECT column1, column2
FROM table1
WHERE column3 = 'value';
LOOP
FETCH cursor_name INTO var1, var2;
EXIT WHEN cursor_name%NOTFOUND;
-- 处理数据
END LOOP;
CLOSE cursor;
------------------------------------------------------------
DECLARE
TYPE t_emp IS RECORD (
employee_id employees.employee_id%TYPE,
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
email employees.email%TYPE,
phone_number employees.phone_number%TYPE
);
TYPE t_emp_list IS TABLE OF t_emp;
v_emp_list t_emp_list;
BEGIN
SELECT employee_id, first_name, last_name, email, phone_number
BULK COLLECT INTO v_emp_list
FROM employees
WHERE department_id = 10;
-- 对结果集进行处理
END;
上面的代码将所有部门ID为10的员工存储在名为v_emp_list的游标变量中。由于效果集在界说游标变量时就已经确定，因此这是一种静态游标。
2.1.4. 游标参数：
可以将查询效果集作为参数传递给存储过程或函数，以便在后续的处理中使用。游标参数在界说时需要指定效果集，其效果集也是在编译时就已经确定的。例如：
CREATE OR REPLACE PROCEDURE p_emp_list (p_dept_id IN NUMBER,
p_emp_list OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_emp_list FOR
SELECT * FROM employees WHERE department_id = p_dept_id;
END; 上面的代码界说了一个名为p_emp_list的存储过程，其参数p_dept_id是部门ID，p_emp_list是查询效果集。当调用p_emp_list存储过程时，会根据传入的部门ID返回相应的员工列表。该存储过程可以被其他步伐调用，并将查询效果作为输出参数返回。
总结：
静态游标是一种在编译时就确定效果集的游标，其效果集不会随着时间或用户的不同而发生变化。在实际应用中，可以根据需要选择不同类型的静态游标来处理数据。
2.2动态游标
动态游标是一种在 PL/SQL 中使用游标的方式，它允许在运行时动态地构建 SQL 查询语句，以便适应不同的查询需求。相比于静态游标，动态游标更加机动，可以根据不同的条件和参数天生不同的查询语句。
枚举使用动态游标的示例：
CREATE OR REPLACE PROCEDURE p_emp_list (p_dept_id IN NUMBER,
p_emp_list OUT SYS_REFCURSOR)
IS
v_query VARCHAR2(200);
BEGIN
v_query := 'SELECT * FROM employees WHERE department_id = ' || p_dept_id;
OPEN p_emp_list FOR v_query;
END;
在该存储过程中，使用一个 VARCHAR2 类型的变量 v_query 来构建动态查询语句。首先将一个固定的字符串 'SELECT * FROM employees WHERE department_id = ' 和输入参数 p_dept_id 拼接起来，天生一个完备的查询语句。然后使用 OPEN 语句打开游标 p_emp_list，并将动态查询语句作为参数传入。
通过使用动态游标，该存储过程可以根据不同的部门 ID 天生不同的查询语句，并返回不同的查询效果。这种机动性使得动态游标在复杂的查询场景中非常有用。
但是动态游标也有强弱之分，下面分别形貌：
2.2.1强类型游标
强游标（strong cursor）是指在游标打开时就将所有数据都读入到游标中，因此游标可以在任何时间访问数据，但是这种方式会占用大量的内存资源。而且强类型游标是可以更换效果集，但是不能换表结构。
比如：
要求打印emp表的10部门的员工姓名，并打印emp1职位是CLERK的员工编号，则语句如下：
declare
type ty1 is ref cursor return emp%rowtype;
c1 ty1;
vemp emp%rowtype;
begin
open c1 for select * from emp where deptno=10;
loop
fetch c1 into vemp;
exit when c1%notfound;
dbms_output.put_line(vemp.ename);
end loop;
close c1;
open c1 for select * from emp1 where job='CLERK';
loop
fetch c1 into vemp;
exit when c1%notfound;
dbms_output.put_line(vemp.empno);
end loop;
close c1;
end; 创建三个表分别为 empa empb empc ，内容均和emp表一样，打印empa中10部门的员工姓名和部门编号，empb中工资大于2000的员工姓名和工资，empc中职位是CLERK的员工姓名和职位 ，则详细语句如下：
create table empa as select * from emp;
create table empb as select * from emp;
create table empc as select * from emp;
declare
type ty1 is ref cursor return emp%rowtype;
c1 ty1;
v_emp emp%rowtype;
begin
open c1 for select * from empa where deptno=10;
loop
fetch c1 intov_emp;
exit when c1%notfound;
dbms_output.put_line(v_emp.ename||' '||v_emp.deptno);
end loop;
close c1;
open c1 for select * from empb where sal>2000;
loop
fetch c1 intov_emp;
exit when c1%notfound;
dbms_output.put_line(v_emp.ename||' '||v_emp.sal);
end loop;
close c1;
open c1 for select * from empc where job='CLERK';
loop
fetch c1 into v_emp;
exit when c1%notfound;
dbms_output.put_line(v_emp.ename||' '||v_emp.job);
end loop;
close c1;
end; 2.2.2弱类型游标
弱游标（weak cursor）是指游标只在需要访问数据时才会读取数据，因此占用内存资源较少，但是需要在访问数据时进行频繁的I/O操纵，因此效率较低。而且弱类型游标也可以更换效果集，而且可以换表结构。
比如：
要求打印emp表的员工姓名和dept表的部门所在地，并打印emp表每个部门的员工人数，则语句如下：
declare
type t1 is ref cursor;
c1 t1;
v1 varchar2(20);
v2 number;
begin
open c1for select ename from emp;
loop
fetch c1 into v1;
exit when c1%notfound;
dbms_output.put_line(v1);
end loop;
close c1;
open c1 for select loc from dept;
loop
fetch c1 into v1;
exit when c1%notfound;
dbms_output.put_line(v1);
end loop;
close c1;
open c1 for select count(1) from emp group by deptno;
loop
fetch c1 into v2;
exit when c1%notfound;
dbms_output.put_line(v2);
end loop;
close c1;
end; 总结：
在使用动态游标时，需要根据详细情况选择合适的游标类型，衡量内存资源和访问效率。
强类型游标和弱类型游标的区别：
[*]强类型有return open时for的效果集
[*]必须和return表结构一致
[*]弱类型没有return open时for的效果集比较自由
2.2.3平凡动态游标
是最常用的动态游标，语句格式：
declare
c1 sys_refcursor; --声明一个动态游标 比如：
打印emp表的员工姓名和dept表的部门所在地，并打印emp表每个部门的员工人数，则语句如下：
declare
c1 sys_refcursor;
v1 varchar2(20);
v2 number;
begin
open c1for select ename from emp;
loop
fetch c1 into v1;
exit when c1%notfound;
dbms_output.put_line(v1);
end loop;
close c1;
open c1 for select loc from dept;
loop
fetch c1 into v1;
exit when c1%notfound;
dbms_output.put_line(v1);
end loop;
close c1;
open c1 for select count(1) from emp group by deptno;
loop
fetch c1 into v2;
exit when c1%notfound;
dbms_output.put_line(v2);
end loop;
close c1;
end; 要求打印emp表名字包含A的员工姓名和人数，dept表包含A的部门名称和部门数，则语句如下：
declare
c1 sys_refcursor;
vname varchar2(20);
vcount number;
begin
open c1 for select ename from emp where ename like'%A%';
loop
fetch c1 into vname;
exit when c1%notfound;
dbms_output.put_line(vname);
end loop;
close c1;
open c1 forselect count(1) from emp where ename like'%A%';
loop
fetch c1 into vcount;
exit when c1%notfound;
dbms_output.put_line(vcount);
end loop;
close c1;
open c1 for select dname from dept where dname like'%A%';
loop
fetch c1 into vname;
exit when c1%notfound;
dbms_output.put_line(vname);
end loop;
close c1;
open c1 forselect count(1) from dept where dname like'%A%';
loop
fetch c1 into vcount;
exit when c1%notfound;
dbms_output.put_line(vcount);
end loop;
close c1;
end; for循环+游标
Ⅰ、有游标名的
声明一个显式游标，不用打开 ，不用赋值 ，不用关闭，因为有游标名 以是可以用游标的属性；
比如：
打印dept表的所有信息：
declare
cursor c1 is select * from dept;
begin
for i in c1 loop
dbms_output.put_line(i.deptno||' '||i.dname
||' '||i.loc);
end loop;
end; Ⅱ、没有游标名
不用声明 ，不用打开 ，不用赋值 ，不用关闭；不能和动态游标和带参数的显式游标一起使用 （直接for i in效果集） ；
比如：
打印dept表的所有信息：
begin
for i in (select * from dept) loop
dbms_output.put_line(i.deptno||' '||i.dname||
' '||i.loc);
end loop;
end; 三、游标的优缺点
优点：
[*]可以对大量数据进行有用的遍历和操纵，尤其是在数据量较大时；
[*]可以根据需要随时调解游标位置，机动性较高；
[*]可以在一些特定的场景下进步步伐的效率；
[*]使用游标可以执行多个不干系的操纵；
[*]使用游标可以进步脚本的可读性；
[*]使用游标可以创建命令字符串，可以传送表名，大概把变量传送到参数中，以便创建可以执行的命令字符串；
[*]提供基于游标位置而对表中数据进行删除或更新；
缺点：
[*]处理大数据量时可能会占用大量的系统资源，如内存和CPU，效率底下；
[*]可能会对数据库的性能产生一定的影响；
[*]可能会导致数据不一致的标题，如在游标遍历过程中修改了数据，但游标并不会立刻反映出来；
[*]游标使用时会对行加锁，可能会影响其他业务的正常进行。
四、游标使用留意事项
在使用游标时，需要留意以下几点：
[*]1. 游标必须在使用前声明，并且声明时需要指定游标的类型和返回效果集的SELECT语句。
[*]2. 游标必须在使用前打开，并在使用完毕后关闭。
[*]3. 游标可以使用FETCH语句获取效果集中的每一行数据。
[*]4. 游标可以使用%ROWTYPE属性来界说一个与效果集结构相同的变量，以便在遍历效果集时方便地存储每一行数据。
[*]5. 游标可以使用FOR循环结构来遍历效果集，制止手动进行游标的打开、获取和关闭操纵。
五、游标的作用
游标是一种数据库技术，通过游标可以在一个效果集中逐个地访问每一条记录，然后对记录进行操纵。游标可以用于以下几个方面：
5.1. 遍历数据：
游标可以逐条地遍历查询效果集，对每一条数据进行处理，比如筛选、修改、删除等操纵。
假设有一个包含学生姓名、年龄、成绩等信息的数据表，需要实现一个查询功能，查询所有成绩低于60分的学生姓名和年龄。通过游标可以实现：
[*] 界说一个游标，用于查询数据表中的所有记录。
[*] 使用FETCH语句逐条读取记录，然后对每条记录进行判断，如果成绩低于60分，则将学生姓名和年龄存储到一个临时表中。
[*] 等待游标读取完毕后，从临时表中获取所有符合条件的学生姓名和年龄，然后返回给用户。
5.2. 分页查询：
通过游标可以实现分页查询，每次查询指定数量的记录，以便于进步查询效率和淘汰网络传输的数据量。
假设有一个包含1000条记录的数据表，需要实现一个每页表现10条记录的分页查询功能。通过游标可以实现以下步调：
[*]1. 界说一个游标，用于查询数据表的所有记录。
[*]2. 使用FETCH语句逐条读取记录，然后将记录存储到一个缓存区中。
[*]3. 在缓存区中逐条读取记录，直到读取到10条记录，然后将这10条记录返回给用户。
[*]4. 等待用户哀求下一页数据，然后再次从缓存区中读取下10条记录，直到所有记录都被读取完毕。
通过游标实现分页查询功能可以大大进步查询效率和淘汰网络传输的数据量。而且，由于游标可以机动地控制查询进度，因此可以制止一些不必要的数据库操纵，从而进步系统的性能和稳固性。
5.3. 数据库事务：
游标可以用于管理数据库事务，它可以让开辟职员对每一条记录进行细粒度的控制，从而确保事务的安全性和一致性。
留意：
游标可以在事务中使用，但它并不是实现事务的关键。
5.4. 数据库备份和恢复：
游标可以用于数据库备份和恢复，通过游标可以逐条读取数据库中的数据，然后将数据写入备份文件中，大概将备份文件中的数据逐条恢复到数据库中。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]