带参数的游标
与存储过程和函数相似,可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下:
CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
定义参数的语法如下:
Parameter_name [IN] data_type[{:=|DEFAULT} value]
与存储过程不同的是,游标只能接受传递的值,而不能返回值。参数只定义数据类型,没有大小。
另外可以给参数设定一个缺省值,当没有参数值传递给游标时,就使用缺省值。游标中定义的参数只是一个占位符,
在别处引用该参数不一定可靠。
在打开游标时给参数赋值,语法如下:
OPEN cursor_name[value[,value]....];
参数值可以是文字或变量。
例:
DECLARE
CURSOR c_dept IS
SELECT * FROM dept ORDER BY deptno;
CURSOR c_emp(p_dept emp.deptno%type) IS
SELECT ename, sal FROM emp WHERE deptno = p_dept ORDER BY ename;
r_dept DEPT%ROWTYPE;
v_ename EMP.ENAME%TYPE;
v_salary EMP.SAL%TYPE;
v_tot_salary EMP.SAL%TYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept
INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department:' || r_dept.deptno || '-' ||
r_dept.dname);
v_tot_salary := 0;
OPEN c_emp(r_dept.deptno);
LOOP
FETCH c_emp
INTO v_ename, v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name:' || v_ename || ' sal:' || v_salary);
v_tot_salary := v_tot_salary + v_salary;
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE('Toltal Sal for dept:' || v_tot_salary);
END LOOP;
CLOSE c_dept;
END;
游标FOR循环
在大多数时候我们在设计程序的时候都遵循下面的步骤:
1、打开游标
2、开始循环
3、从游标中取值
4、检查那一行被返回
5、处理
6、关闭循环
7、关闭游标
可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同,这就是FOR循环,
用于FOR循环的游标按照正常的声明方式声明,它的优点在于不需要显式的打开、关闭、取数据,测试数据的存在、定义存放数据的变量等等
。游标FOR 循环的语法如下:
FOR record_name IN
(corsor_name[(parameter[,parameter]...)]
| (query_difinition)
LOOP
statements
END LOOP;
下面我们用for循环重写上面的例子:
DECLARE
CURSOR c_dept IS
SELECT deptno, dname FROM dept ORDER BY deptno;
CURSOR c_emp(p_dept emp.deptno%type) IS
SELECT ename, sal FROM emp WHERE deptno = p_dept ORDER BY ename;
v_tot_salary EMP.SAL%TYPE;
BEGIN
FOR r_dept IN c_dept LOOP
DBMS_OUTPUT.PUT_LINE('Department:' || r_dept.deptno || '-' ||
r_dept.dname);
v_tot_salary := 0;
FOR r_emp IN c_emp(r_dept.deptno) LOOP
DBMS_OUTPUT.PUT_LINE('Name:' || r_emp.ename || ' sal:' ||
r_emp.sal);
v_tot_salary := v_tot_salary + r_emp.sal;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Sal for dept:' || v_tot_salary);
END LOOP;
END;
在游标FOR循环中使用查询
在游标FOR循环中可以定义查询,由于没有显式声明所以游标没有名字,记录名通过游标查询来定义。
DECLARE
v_tot_salary EMP.SAL%TYPE;
BEGIN
FOR r_dept IN (SELECT deptno, dname FROM dept ORDER BY deptno) LOOP
DBMS_OUTPUT.PUT_LINE('Department:' || r_dept.deptno || '-' ||
r_dept.dname);
v_tot_salary := 0;
FOR r_emp IN (SELECT ename, sal
FROM emp
WHERE deptno = r_dept.deptno
ORDER BY ename) LOOP
DBMS_OUTPUT.PUT_LINE('Name:' || r_emp.ename || ' salary:' ||
r_emp.sal);
v_tot_salary := v_tot_salary + r_emp.sal;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:' || v_tot_salary);
END LOOP;
END;
游标中的子查询
语法如下:
CURSOR C1 IS SELECT * FROM emp
WHERE deptno NOT IN (SELECT deptno
FROM dept
WHERE dname!='ACCOUNTING');
可以看出与SQL中的子查询没有什么区别。
游标中的更新和删除**************************************************************************POINT
在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。
PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法。
UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。
要使用这个方法,在声明游标时必须使用FOR UPDATE子串,当对话使用FOR UPDATE子串打开一个游标时,
所有返回集中的数据行都将处于行级(ROW-LEVEL)独占式锁定,其他对象只能查询这些数据行,
不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。
语法:
FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
[nowait]
在多表查询中,使用OF子句来锁定特定的表,如果忽略了OF子句,那么所有表中选择的数据行都将被锁定。
如果这些数据行已经被其他会话锁定,那么正常情况下ORACLE将等待,直到数据行解锁。
在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下:
WHERE{CURRENT OF cursor_name|search_condition}
例:
DECLARE
CURSOR c1 IS
SELECT empno, sal FROM test_emp WHERE comm IS NULL FOR UPDATE OF comm;
v_comm NUMBER(10, 2);
BEGIN
FOR r1 IN c1 LOOP
IF r1.sal < 500 THEN
v_comm := r1.sal * 0.25;
ELSIF r1.sal < 1000 THEN
v_comm := r1.sal * 0.20;
ELSIF r1.sal < 3000 THEN
v_comm := r1.sal * 0.15;
ELSE
v_comm := r1.sal * 0.12;
END IF;
UPDATE test_emp SET comm = v_comm WHERE CURRENT OF c1;
END LOOP;
END;