初印象:假设你要用PL/SQL查询某个部门的所有员工的姓名和工资,就要用到游标。
先看一个小例子,然后带着问题去了解游标。
要求:打印部门号是80的所有员工的工资。
declare
v_sal emp.salary%type;
v_empid emp.employee_id%type;
//定义游标
cursor emp_sal_cursor is
select salary,employee_id from emp where dept_id=80;
begin
//打开游标
open emp_sal_cursor;
//提取游标
fetch emp_sal_cursor into V_sal,employee_id;
while emp_sal_cursor%found loop
dbms_output.put_line('姓名:'||v_empid||'工资是:'||v_sal);
fetch emp_sal_cursor into V_sal,employee_id;
end loop;
//关闭游标
close emp_sal_cursor;
end;
其实上面这么多代码等价于:
SQL>select employee_id,salary from emp where dept_id=80;
1.什么是游标?
官方定义:
游标(cursor)是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果。
通俗的说:
游标是一段私有的工作区,也就是一段内存区域,用于暂时存放受SQL语句影响的数据。通俗理解就是将受影响的数据暂时放到一个内存区域的虚表中,而这个虚表就是游标。
加深理解:
1.PL/SQL中,游标是用来处理多条数据的。
2.游标是一个指向上下文的句柄(handle)或指针。
3.游标能遍历结果集中的所有行,但他一次只指向一行。
2.游标的作用:
1.数据库是具有回滚功能的,游标在其中有重要的作用。由于对数据库的操作会暂时放在游标中,只要在提交的时候出现异常,我们就可以根据游标就行回滚,在一定意义上有利于数据库的安全。
2.在Oracle中,PL/SQL只能返回单行数据,而游标的出现,可以返回多条数据。
3.游标的属性:
属性 | 返回值 | 意义 |
---|---|---|
%rowcount | 整型 | 代表增删改语句成功执行的数据行数 |
%found | boolean | 返回true,代表插入、删除、更新或单行查询操作成功 |
%notfound | boolean | 返回true,代表插入、删除、更新或单行查询操作失败 |
%isopen | boolean | 游标是否打开 |
4.使用游标的四个步骤:
步骤 | 关键词 | 说明 |
---|---|---|
1 | 在DECLARE中cursor | 声明游标 |
2 | Open | 打开游标 |
3 | Fetch | 取出游标中的一条记录装入变量 |
4 | Close | 关闭游标 |
5.游标的类型:
游标的类型分为两种:显示游标和隐式游标。
(1)隐式游标:
隐式游标是oracle自动创建的,执行以下操作会使用隐式游标:
- 插入操作:INSERT。
- 更新操作:UPDATE。
- 删除操作:DELETE。
- 单行查询操作:SELECT … INTO …
注意:通过SQL游标名总是只能访问前一个插入、删除、更新操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后,立即使用SQL游标名来访问属性。
【小例子】: 使用隐式游标的属性,判断对雇员工资的修改是否成功。
步骤1:输入和运行以下程序:
SET SERVEROUTPUT ON
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=1234;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('成功修改雇员工资!');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('修改雇员工资失败!');
END IF;
END;
运行结果为:
修改雇员工资失败!
PL/SQL 过程已成功完成。
步骤2:将雇员编号1234改为7788,重新执行以上程序:
运行结果为:
成功修改雇员工资!
PL/SQL 过程已成功完成。
说明:本例中,通过SQL%FOUND属性判断修改是否成功,并给出相应信息。
(2)显示游标:
由开发人员通过程序显式控制,用于从表中取出多行数据,并将多行数据一行一行的单独进行处理.
使用游标的4个步骤:
1.声明游标:
CURSOR 游标名[(参数1 数据类型[,参数2 数据类型…])]
IS SELECT语句;
(a).参数是可选部分,所定义的参数可以出现在select语句的where字句中。如果定义了参数,则必须在打开游标时传递相应的实际参数。
(b).select语句可以带where条件,group by,order by等子句,但不能使用into子句。
(c).在SELECT语句中可以使用在定义游标之前定义的变量。
2.打开游标:
OPEN 游标名[(实际参数1[,实际参数2…])];
打开游标时,select语句的查询结果就被送到了游标工作区。
3.提取数据:
fetch 游标名 INTO 变量名1[,变量名2…];
或
fetch 游标名 into 记录变量;
游标打开后,有一个指针指向数据区,fetch语句一次返回指针所指的一行数据,若要返回多行,可用循环。
4.关闭游标:
close 游标名;
游标一旦关闭,游标占用的资源就被释放,游标变成无效,必须重新打开才能使用。