例如:SELECT * FROM employees WHERE sex='M'会返回所有性别为男的雇员,在初始的时候,游标被放置在结果集中第一行的前面。使游标指向第一行,要执行FETCH。当游标指向结果集中一行的时候,可以对这行数据进行加工处理,要想得到下一行数据,要继续执行FETCH。FETCH操作可以重复执行,直到完成结果集中的所有行
声明游标、打开游标、根据需要一次一行,讲游标指向的数据取到本地变量(local variables)中、结束时关闭游标
>>-DECLARE--cursor-name--CURSOR----+------------+--------->
'-WITH HOLD--'
| .-TO CALLER--. |
'-WITH RETURN--+------------+--'
'-TO CLIENT--'
'-statement-name---'
FETCH语法:
>>-FETCH--+-------+---cursor-name---------->
'-FROM--'
V |
>------INTO-----host-variable---+----------><
FETCH语句使游标指向结果集中的下一行,并且将游标现在的位置赋值给特定的过程变量
1.公司中除了总裁(president)外,所有人都会至少增加p_min的薪水
2.任何奖金(bonus)高于$600的员工都会另增加4%
3.员工的佣金(commission)越高,增加越少。佣金(commission)少于$2000的另增加3%,佣金(commission)在$2000到$3000的增加另2%
4.佣金(commission)高于$3000的另增加1%
5.无论每个员工增加多少,增加比例不能高于p_max
CREATE PROCEDURE total_raise ( IN p_min DEC(4,2)
, IN p_max DEC(4,2)
, OUT p_total DEC(9,2) )
LANGUAGE SQL
SPECIFIC total_raise
tr: BEGIN
-- Declare variables
DECLARE v_salary DEC(9,2);
DECLARE v_bonus DEC(9,2);
DECLARE v_comm DEC(9,2);
DECLARE v_raise DEC(4,2);
DECLARE v_job VARCHAR(15) DEFAULT 'PRES';
-- Declare returncode
DECLARE SQLSTATE CHAR(5);
-- Procedure logic
DECLARE c_emp CURSOR FOR
SELECT salary, bonus, comm
FROM employee
WHERE job != v_job; -- (1)这里的SELECT定义了结果集中的行和
SET p_total = 0;
SET v_raise = p_min;
SET v_raise = v_raise + 0.04;
END IF;
IF ( v_comm < 2000 ) THEN
SET v_raise = v_raise + 0.03;
ELSEIF ( v_comm < 3000 ) THEN
SET v_raise = v_raise + 0.02;
ELSE
SET v_raise = v_raise + 0.01;
END IF;
IF ( v_raise > p_max ) THEN
SET v_raise = p_max;
END IF;
SET p_total = p_total + v_salary * v_raise;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; -- (4)在WHILE逻辑中得到
END WHILE;
CLOSE c_emp; -- (5)
END tr
DECLARE c_tmp CURSOR FOR
SELECT c1
FROM t1;
FETCH FROM c_emp INTO v_c1;
CLOSE c_emp;
应当用有FETCH FIRST 1 ROW ONLY的子句的SQL语句:SELECT c1 INTO v_c1 FROM t1 FETCH FIRST 1 ROW ONLY;
一个用于删除的游标(a deletable cursor)应该符合以下的要求:
1.每个outer fullselect中的FROM子句只跟一个表有关
2.outer fullselect不包含VALUES, GROUP BY, 或者HAVING子句,并且不包括列函数
3.outer fullselect的select列表中不包含DISTINCT
4.select语句不包含ORDER BY或FOR READ ONLY子句
5.游标是静态定义的,或者明确了FOR UPDATE子句
>>-DELETE FROM-|----table-name---------|--------------->
+-----view-name---------+
例如:在emp_act表中,如果记录的时间比输入参数p_date早的话,就将该记录删除,并返回删除记录总数
CREATE PROCEDURE cleanup_act ( IN p_date DATE
, OUT p_deleted INT )
LANGUAGE SQL
SPECIFIC cleanup_act
ca: BEGIN
-- Declare variable
DECLARE v_date DATE;
-- Declare returncode
DECLARE SQLSTATE CHAR(5);
-- Procedure logic
DECLARE c_emp CURSOR FOR -- (1)和上面那种read-only cursor语法
SELECT emendate
FROM emp_act
FOR UPDATE;
OPEN c_emp;
FETCH FROM c_emp INTO v_date; --注意此处,不要落了
SET p_deleted = 0;
WHILE ( SQLSTATE = '00000' ) DO
DELETE FROM emp_act
WHERE CURRENT OF c_emp; -- (2)
END IF;
END WHILE;
END ca
直接用DELETE语句删除而不用游标被称作searched delete。像上例这种情况,采用searched delete会比使用positioned delete效率更高。但用positioned delete可以处理更复杂的逻辑
一个用于更新的游标(A cursor is updatable)应该The cursor is deletable
+-view-name--------------------+
>-----SET--| assignment-clause |--------------------------->
>-----WHERE CURRENT OF--cursor-name-----------------------><
CREATE PROCEDURE upd_raise ( IN p_min DEC(4,2)
, IN p_max DEC(4,2) )
LANGUAGE SQL
SPECIFIC upd_raise
-- Declare variables
DECLARE v_salary DEC(9,2);
DECLARE v_bonus DEC(9,2);
DECLARE v_comm DEC(9,2);
DECLARE v_raise DEC(4,2);
-- Declare returncode
DECLARE SQLSTATE CHAR(5);
DECLARE c_emp CURSOR FOR
SELECT salary, bonus, comm
FROM employee
WHERE job!='PRES'
FOR UPDATE OF salary; -- (1)如果只是更新表中的一部分字段,可以利用
SET v_raise = p_min;
SET v_raise = v_raise + 0.04;
END IF;
SET v_raise = v_raise + 0.03;
SET v_raise = v_raise + 0.02;
ELSE
SET v_raise = v_raise + 0.01;
END IF;
SET v_raise = p_max;
END IF;
SET salary = v_salary * (1 + v_raise)
WHERE CURRENT OF c_emp;
END WHILE;
END ur
使用游标时候的COMMIT和ROLLBACK:
数据库程序中很重要的一点就是事务处理(transaction或者the unit of work(UOW))。事务当中的任何一部分失败,整个事物就会失败。利用COMMIT和ROLLBACK进行适当的事务控制对于保证数据完整性来说是至关重要的。
当在使用游标的时候使用COMMIT或者ROLLBACK语句时,游标的行动取决于是否在生命的时候加了WITH HOLD子句。如果一个游标在声明的时候没有指定WITH HOLD,那么它的所有资源(游标,锁,大对象数据类型或者LOB locators)都将在COMMIT或者ROLLBACK之后被释放。因此,如果需要在完成一个事务之后使用游标,就必须重新打开游标,并从第一行开始执行。如果定义了一个游标WITH HOLD
定义了WITH HOLD的游标在COMMIT之后
1.仍然保证是打开(open)的
2.游标指向下一个满足条件的行之前
3.在COMMIT语句之后只允许FETCH和CLOSE
4.Positioned delete和positioned update只在同一事务中fetch的行上可用
5.所有的LOB locators会被释放
6.除了保存声明为WITH HOLD的游标位置的锁,其他锁都会释放
7.当执行了数据修改语句或者含有WITH HOLD游标的修改语句被commit的时候
所有定义为WITH HOLD的游标在ROLLBACK之后:
1.所有游标会被关闭
2.所有在该事务中的锁会被释放
3.所有的LOB locators会被freed
CREATE PROCEDURE update_department ( )
LANGUAGE SQL
SPECIFIC upd_dept
-- Declare variable
DECLARE v_deptno CHAR(3);
-- Declare returncode
SELECT deptno
FROM department
FOR UPDATE OF location;
DECLARE CONTINUE HANDLER FOR SQLSTATE '24504', SQLSTATE '24501'
L1: LOOP -- (1)
LEAVE L1;
END LOOP;
OPEN c_dept; --打开游标,指向第一行前面的位置
UPDATE department SET location='FLOOR1' WHERE CURRENT OF c_dept; -- (3)
COMMIT; -- (4)因为该游标声明为WITH
COMMIT; -- (6)COMMIT后游标指向第三行之
UPDATE department SET location='FLOOR2' WHERE CURRENT OF c_dept; -- (7)这行
UPDATE department SET location='FLOOR3' WHERE CURRENT OF c_dept; -- (9)成功
COMMIT; -- (10)
UPDATE department SET location='FLOOR4' WHERE CURRENT OF c_dept; -- (12)成
ROLLBACK; -- (13)第四行的值还原。
UPDATE department SET location='FLOOR5' WHERE CURRENT OF c_dept; -- (15)
END ud
上述存储过程执行前:
DEPTNO LOCATION
------ --------
A00 -
B01 -
C01 -
D01 -
D11 -
上述存储过程执行后:
DEPTNO LOCATION
------ --------
A00 FLOOR1
B01 -
C01 FLOOR3
D01 -
D11 -
如果上例中的游标没有声明为WITH HOLD,从(5)到(15)的执行都会失败。因为游标会在COMMIT或ROLLBACK之后隐性关闭
>>- SAVEPOINT--savepoint-name----+--------+--------------------->
'-UNIQUE-'
>--ON ROLLBACK RETAIN CURSORS--+--------------------------+----><
'-ON ROLLBACK RETAIN LOCKS-'
savepoint-name不能以'SYS'开头,否则会报SQLSTATE 42939的错误。UNIQUE选项表示这个save point name不会在Save Point活动期中被reused。ON ROLLBACK RETAIN CURSORS使游标在rollback发生之后还被保留。附加的ON ROLLBACK RETAIN LOCKS防止在ROLLBACK之后锁丢失
在一个事务中,可以定义多个save points
使用save points的伪代码:
savepoint A;
Do program logic;
savepoint B;
Do more program logic;
savepoint C;
Do even more program logic;
之后就可以用含有SAVE POINT的ROLLBACK: ROLLBACK TO SAVEPOINT savepoint-name
如果ROLLBACK到了最后一个save point,那么这个save point之前的save point都还是活动的(active),你依旧可以ROLL BACK到更早的save point
例如:
savepoint a;
Do program logic;
savepoint b;
Do more program logic;
savepoint c;
Do even more program logic;
ROLLBACK TO SAVEPOINT c; (1)将事务数据返回到save point c
Do some new logic;
ROLLBACK TO SAVEPOINT a; (2)将事务数据返回到save point a
Do some more logic;
ROLLBACK TO SAVEPOINT b; (3)错误,因为此时save point b已经不存在了
比如:
CREATE PROCEDURE read_emp ( )
LANGUAGE SQL
SPECIFIC read_emp
DYNAMIC RESULT SETS 1 --(1)如果想用游标返回结果集到一个应用程序,必须声明DYNAMIC RESULT SETS
re: BEGIN
-- Procedure logic
DECLARE c_emp CURSOR WITH RETURN FOR --(2)
FROM employee
WHERE job!='PRES';
END re
此时只返回了所有符合条件的员工的salary, bonus和commission字段。之后,结果集就可以被另外的存储过程或客户端程序调用
CREATE PROCEDURE create_and_return ( )
LANGUAGE SQL
SPECIFIC create_and_return
cr: BEGIN
-- Procedure logic
CREATE TABLE mytable (sid INT);
INSERT INTO mytable VALUES (2);
DECLARE c_cur CURSOR WITH RETURN
FOR SELECT *
FROM mytable;
OPEN c_cur; --(2)
END; --(3)OPEN必须在这个嵌套的BEGIN ... END中,因为游
END cr
1.CREATE PROCEDURE中的DYNAMIC RESULT SETS子句写明想返回的结果集的数量
2.为每一个结果集声明含有WITH RETURN的游标
3.保证所有游标返回给客户端是打开的
例如:
CREATE PROCEDURE read_emp_multi ( )
LANGUAGE SQL
SPECIFIC read_emp_multi
DYNAMIC RESULT SETS 3 --(1)
re: BEGIN
-- Procedure logic
DECLARE c_salary CURSOR WITH RETURN FOR
SELECT salary
FROM employee;
SELECT bonus
FROM employee;
SELECT comm
FROM employee;
OPEN c_bonus;
OPEN c_comm;
END re
游标打开的顺序反映了结果集返回给客户端的顺序
锁模式(Lock Modes):
Table Row Lock Descriptions | ||
Lock Mode | Applicable Object Type | Description |
S (Share) | Rows, blocks, tables | The lock owner and all concurrent applications can read, but not update, the locked data. |
U (Update) | Rows, blocks, tables | The lock owner can update data. Other UOW can read the data in the locked object, but cannot attempt to update it. |
X (Exclusive) | Rows, blocks, tables, bufferpools | The lock owner can both read and update data in the locked object. Only uncommitted read applications can access the locked object. |
一.概念:
游标:用来查询数据库,获取记录集合(结果集)的指针,可以让开发者一次访问一行结果集,在每条结果集上作操作。
二.分类:
1.静态游标:
分为显式游标和隐式游标。
2.REF游标:
是一种引用类型,类似于指针。
三.详细内容:
1.显式游标:
CURSOR游标名(参数) [返回值类型] IS
Select语句
生命周期:
a.打开游标(OPEN)
解析,绑定。。。不会从数据库检索数据
b.从游标中获取记录(FETCH INTO)
执行查询,返回结果集。通常定义局域变量作为从游标获取数据的缓冲区。
c.关闭游标(CLOSE)
完成游标处理,用户不能从游标中获取行。还可以重新打开。
选项:参数和返回类型
set serveroutput on
declare
cursor emp_cur ( p_deptid in number) is
select * from employees where department_id = p_deptid;
l_emp employees%rowtype;
begin
dbms_output.put_line('Getting employees from department 30');
open emp_cur(30);
loop
fetch emp_cur into l_emp;
exit when emp_cur%notfound;
dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ');
dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
end loop;
close emp_cur;
dbms_output.put_line('Getting employees from department 90');
open emp_cur(90);
loop
fetch emp_cur into l_emp;
exit when emp_cur%notfound;
dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ');
dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
end loop;
close emp_cur;
end;
/
2.隐式游标:
不用明确建立游标变量,分两种:
a.在PL/SQL中使用DML语言,使用ORACLE提供的名为SQL的隐示游标
b.CURSOR FOR LOOP,用于for loop语句
a.举例:
declare
begin
update departments set department_name=department_name;
--where 1=2;
dbms_output.put_line('update '|| sql%rowcount ||' records');
end;
/
b.举例:
declare
begin
for my_dept_rec in ( select department_name, department_id from departments)
loop
dbms_output.put_line(my_dept_rec.department_id || ' : ' || my_dept_rec.department_name);
end loop;
end;
/
c.举例:
单独select
declare
l_empno emp.EMPLOYEE_ID%type;
-- l_ename emp.ename%type;
begin
select EMPLOYEE_ID
into l_empno
from emp;
--where rownum =1;
dbms_output.put_line(l_empno);
end;
/
使用INTO获取值,只能返回一行。
游标属性:
%FOUND:变量最后从游标中获取记录的时候,在结果集中找到了记录。
%NOTFOUND:变量最后从游标中获取记录的时候,在结果集中没有找到记录。
%ROWCOUNT:当前时刻已经从游标中获取的记录数量。
%ISOPEN:是否打开。
Declare
Cursor emps is
Select * from employees where rownum<6 order by 1;
Emp employees%rowtype;
Row number :=1;
Begin
Open emps;
Fetch emps into emp;
Loop
If emps%found then
Dbms_output.put_line('Looping over record '||row|| ' of ' || emps%rowcount);
Fetch emps into emp;
Row := row + 1;
Elsif emps%notfound then
Exit; ---exit loop, not IF
End if;
End loop;
If emps%isopen then
Close emps;
End if;
End;
/
显式和隐式游标的区别:
尽量使用隐式游标,避免编写附加的游标控制代码(声明,打开,获取,关闭),也不需要声明变量来保存从游标中获取的数据。
3.REF CURSOR游标:
动态游标,在运行的时候才能确定游标使用的查询。分类:
1.强类型(限制)REF CURSOR,规定返回类型
2.弱类型(非限制)REF CURSOR,不规定返回类型,可以获取任何结果集。
TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]
Declare
Type refcur_t is ref cursor;
Type emp_refcur_t is ref cursor return employee%rowtype;
Begin
Null;
End;
/
强类型举例:
declare
--声明记录类型
type emp_job_rec is record(
employee_id number,
employee_name varchar2(50),
job_title varchar2(30)
);
--声明REF CURSOR,返回值为该记录类型
type emp_job_refcur_type is ref cursor
return emp_job_rec;
--定义REF CURSOR游标的变量
emp_refcur emp_job_refcur_type;
emp_job emp_job_rec;
begin
open emp_refcur for
select e.employee_id,
e.first_name || ' ' ||e.last_name "employee_name",
j.job_title
from employees e, jobs j
where e.job_id = j.job_id and rownum < 11 order by 1;
fetch emp_refcur into emp_job;
while emp_refcur%found loop
dbms_output.put_line(emp_job.employee_name || '''s job is ');
dbms_output.put_line(emp_job.job_title);
fetch emp_refcur into emp_job;
end loop;
end;