带参数的游标 与存储过程和函数相似,可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下:CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement; 定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value] 与存储过程不同的是,游标只能接受传递的值,而不能返回值。参数只定义数据类型,没有大小。 另外可以给参数设定一个缺省值,当没有参数值传递给游标时,就使用缺省值。游标中定义的参数只是一个占位符,在别处引用该参数不一定可靠。 在打开游标时给参数赋值,语法如下:OPEN cursor_name[value[,value]....]; 参数值可以是文字或变量。 例:DECALRECURSOR c_dept IS SELECT * FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
r_dept DEPT%ROWTYPE;
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
v_tot_salary EMP.SALARY%TYPE;BEGINOPEN 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||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
CLOSE c_dept;
END;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
r_dept DEPT%ROWTYPE;
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
v_tot_salary EMP.SALARY%TYPE;BEGINOPEN 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||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
CLOSE c_dept;
END;
type cur_type is ref cursor;
myCur cur_type;
v_sql string(200);
....
begin
v_sql:='select ....';
open myCur for v_sql;
...
create or replace procedure SP_CLEAR(V_TABLE IN STRING) IS
/*---------------------------------------------------------------
--Function:delete all records of tables in v_table //
--Author:Liaozq //
--Date:2002/10/8 //
--Explain:(1)v_table must like this:table1,table2,table3,... //
(2)Before delete records from tables,recorder thess //
tables.
---------------------------------------------------------------*/
TYPE cur_type IS REF CURSOR;
c_tab cur_type;
v_str STRING(2000);
v_tab t_clear.wlbmc%TYPE;
v_sql STRING(3000);
begin
--Get table from v_table
--Reorder these tables
v_str:=upper(v_table);
v_str:=REPLACE(v_str,',',''',''');
v_str:=''''||v_str||'''';
v_sql:='SELECT WLBMC FROM T_CLEAR WHERE TRIM(WLBMC) IN ('||v_str||') ORDER BY BZ DESC';
OPEN c_tab FOR v_sql;
LOOP
FETCH c_tab INTO v_tab;
EXIT WHEN c_tab%NOTFOUND;
v_sql:='delete from '||v_tab||'';
EXECUTE IMMEDIATE v_sql;
END LOOP;
COMMIT;
--deal with exception
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
end SP_CLEAR;
/
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec emp%ROWTYPE;
sql_stmt VARCHAR2(200);
my_job VARCHAR2(15) := 'CLERK';
BEGIN
sql_stmt := 'SELECT * FROM emp WHERE job = :j';
OPEN emp_cv FOR sql_stmt USING my_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
-- process record
END LOOP;
CLOSE emp_cv;
END;
动态游标可以随条件动态改变,以形成不同的查询例子他们都说了
declare
type t_sor is ref cursor;
v_sor t_sor; --必需的,通过对象变量实现.
...
begin
..
end;
/