1、参数化的游标
作用:多次打开相同的游标,返回不同的结果集。游标声明部分的形参必须与OPEN语句中提供的实参相对应(数量与数据类型)。
作用:多次打开相同的游标,返回不同的结果集。游标声明部分的形参必须与OPEN语句中提供的实参相对应(数量与数据类型)。
例1:
DECLARE
v_empno emp1.empno%TYPE;
v_ename emp1.ename%TYPE;
CURSOR emp_cursor
(p_deptno NUMBER, p_job VARCHAR2) IS ——定义参数时不需要指定精度
SELECT empno, ename
FROM emp1
WHERE deptno=p_deptno
AND job=p_job;
BEGIN
OPEN emp_cursor(30,'SALESMAN'); ——带参数使用的游标
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE('empno is '||v_empno||' and '||' ename is '||v_ename);
END LOOP;
CLOSE emp_cursor;
END;
DECLARE
v_empno emp1.empno%TYPE;
v_ename emp1.ename%TYPE;
CURSOR emp_cursor
(p_deptno NUMBER, p_job VARCHAR2) IS ——定义参数时不需要指定精度
SELECT empno, ename
FROM emp1
WHERE deptno=p_deptno
AND job=p_job;
BEGIN
OPEN emp_cursor(30,'SALESMAN'); ——带参数使用的游标
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE('empno is '||v_empno||' and '||' ename is '||v_ename);
END LOOP;
CLOSE emp_cursor;
END;
2、记录+FOR+游标参数
DECLARE
CURSOR emp_c (p_job VARCHAR2)
IS SELECT ename, sal FROM emp
WHERE job=p_job FOR UPDATE NOWAIT;
BEGIN
FOR e_record IN emp_c(UPPER('&jobs')) ——使用替代变量
LOOP
dbms_output.put_line(e_record.ename||'''s sal:'||e_record.sal);
END LOOP;
END;
/
CURSOR emp_c (p_job VARCHAR2)
IS SELECT ename, sal FROM emp
WHERE job=p_job FOR UPDATE NOWAIT;
BEGIN
FOR e_record IN emp_c(UPPER('&jobs')) ——使用替代变量
LOOP
dbms_output.put_line(e_record.ename||'''s sal:'||e_record.sal);
END LOOP;
END;
/
Enter value for jobs: manager
old 6: for e_record in emp_c(upper('&jobs'))
new 6: for e_record in emp_c(upper('manager'))
JONES's sal:2975
BLAKE's sal:2850
CLARK's sal:2450
old 6: for e_record in emp_c(upper('&jobs'))
new 6: for e_record in emp_c(upper('manager'))
JONES's sal:2975
BLAKE's sal:2850
CLARK's sal:2450
3、SELECT FOR UPDATE 游标
通常,SELECT语句不会在被访问的数据行上设置任何锁定,这允许连接到该数据库的其他会话可以改变被选择的数据。
然而结果集是一致的,因为在OPEN阶段,活动集被确定后,oracle做了个快照,在此之后发生的变化不会反映到结果集里,除非再一次打开游标。
可以使用FOR UPDATE子句在活动集的数据行上设置独占行锁定,直到该事务结束。
然而结果集是一致的,因为在OPEN阶段,活动集被确定后,oracle做了个快照,在此之后发生的变化不会反映到结果集里,除非再一次打开游标。
可以使用FOR UPDATE子句在活动集的数据行上设置独占行锁定,直到该事务结束。
例:
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;--------定义变量
CURSOR emp_cursor IS
SELECT empno, ename FROM emp1
FOR UPDATE NOWAIT;
--NOWAIT 如果数据行已经被其他事务锁定,OPEN将会返回一个错误
--returns an Oracle error if the rows are locked by another session
--也可以写成FOR UPDATE OF sal NOWAIT,只锁定sal列。
BEGIN
OPEN emp_cursor;
IF emp_cursor%ISOPEN THEN
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Empno is '||v_empno||' and ename is '||v_ename);
END LOOP;
DBMS_OUTPUT.PUT_LINE(emp_cursor%ROWCOUNT);
END IF;
CLOSE emp_cursor;
END;
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;--------定义变量
CURSOR emp_cursor IS
SELECT empno, ename FROM emp1
FOR UPDATE NOWAIT;
--NOWAIT 如果数据行已经被其他事务锁定,OPEN将会返回一个错误
--returns an Oracle error if the rows are locked by another session
--也可以写成FOR UPDATE OF sal NOWAIT,只锁定sal列。
BEGIN
OPEN emp_cursor;
IF emp_cursor%ISOPEN THEN
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Empno is '||v_empno||' and ename is '||v_ename);
END LOOP;
DBMS_OUTPUT.PUT_LINE(emp_cursor%ROWCOUNT);
END IF;
CLOSE emp_cursor;
END;
模拟锁定:update emp1 set ename='SMMTH' where empno=7369;
查看系统当前锁(sys)
select oracle_username, os_user_name,session_id from v$locked_object;
update emp1 set ename='SMMTH' where empno=7369;(会话不成功)
select oracle_username, os_user_name,session_id from v$locked_object;
update emp1 set ename='SMMTH' where empno=7369;(会话不成功)
3、WHERE CURRENT OF子句
引用显式游标的当前行,(如果游标定义时使用了FOR UPDATE的话)
DECLARE
CURSOR e_sal_cursor IS
SELECT sal FROM emp WHERE deptno=&&DEPTNO
FOR UPDATE NOWAIT;
BEGIN
FOR r IN e_sal_cursor LOOP
UPDATE emp SET sal=sal*1.1
WHERE CURRENT OF e_sal_cursor;
END LOOP;
END;
/
CURSOR e_sal_cursor IS
SELECT sal FROM emp WHERE deptno=&&DEPTNO
FOR UPDATE NOWAIT;
BEGIN
FOR r IN e_sal_cursor LOOP
UPDATE emp SET sal=sal*1.1
WHERE CURRENT OF e_sal_cursor;
END LOOP;
END;
/
错误的方式:
DECLARE
CURSOR e_sal_cursor IS
SELECT sal FROM emp WHERE deptno=&&DEPTNO
FOR UPDATE NOWAIT;
BEGIN
OPEN e_sal_cursor;
FOR r IN 1..e_sal_cursor%ROWCOUNT LOOP
UPDATE emp SET sal=sal*1.1
WHERE CURRENT OF e_sal_cursor;
END LOOP;
CLOSE e_sal_cursor;
END ;
/
DECLARE
CURSOR e_sal_cursor IS
SELECT sal FROM emp WHERE deptno=&&DEPTNO
FOR UPDATE NOWAIT;
BEGIN
OPEN e_sal_cursor;
FOR r IN 1..e_sal_cursor%ROWCOUNT LOOP
UPDATE emp SET sal=sal*1.1
WHERE CURRENT OF e_sal_cursor;
END LOOP;
CLOSE e_sal_cursor;
END ;
/
例子2
1 declare
2 cursor emp_c is
3 select * from emp1 where empno=&eno
4 for update;
5 begin
6 for r in emp_c loop
7 update emp1 set sal=sal*1.1
8 where current of emp_c; ——表示使用当前游标值
——where empno=&eno,效果和上面的一样,但是这表示再打开一个游标,这使系统负载变大
9 dbms_output.put_line(r.ename||'''s sal is'||r.sal);
10 end loop;
11* end;
SQL> /
Enter value for eno: 7788
old 3: select * from emp1 where empno=&eno
new 3: select * from emp1 where empno=7788
SCOTT's sal is3630
1 declare
2 cursor emp_c is
3 select * from emp1 where empno=&eno
4 for update;
5 begin
6 for r in emp_c loop
7 update emp1 set sal=sal*1.1
8 where current of emp_c; ——表示使用当前游标值
——where empno=&eno,效果和上面的一样,但是这表示再打开一个游标,这使系统负载变大
9 dbms_output.put_line(r.ename||'''s sal is'||r.sal);
10 end loop;
11* end;
SQL> /
Enter value for eno: 7788
old 3: select * from emp1 where empno=&eno
new 3: select * from emp1 where empno=7788
SCOTT's sal is3630
转载于:https://blog.51cto.com/19880614/1159330