linux查看open_cursors,Double Cursors

1)double cursors

DECLARE

CURSOR cursor_dept IS

SELECT DISTINCT department_id, department_name

FROM departments

WHERE department_id < 100;

CURSOR cursor_emp( dept_id NUMBER ) IS

SELECT last_name, job_id, hire_date, salary

FROM employees

WHERE employee_id < 120

AND department_id = dept_id;

cur_r_dept  cursor_dept%ROWTYPE;

cur_r_emp    cursor_emp%ROWTYPE;

BEGIN

--first cursor begin

OPEN cursor_dept;

LOOP     --<>

FETCH cursor_dept INTO cur_r_dept;

EXIT WHEN cursor_dept%NOTFOUND

OR cursor_dept%NOTFOUND is null;

--second cursor begin

IF cursor_emp%ISOPEN THEN CLOSE cursor_emp; END IF;

OPEN cursor_emp( cur_r_dept.department_id );

LOOP       --<>

FETCH cursor_emp INTO cur_r_emp;

EXIT WHEN cursor_emp%NOTFOUND

OR cursor_emp%NOTFOUND is null;

DBMS_OUTPUT.put_line(cur_r_emp.last_name ||' '

||cur_r_emp.job_id || ' '

||cur_r_emp.hire_date || ' '

||cur_r_emp.salary );

END LOOP;  --<>

IF cursor_emp%ISOPEN THEN CLOSE cursor_emp; END IF;

END LOOP; --<>

IF cursor_emp%ISOPEN THEN CLOSE cursor_emp; END IF;

CLOSE cursor_dept;

END;

/

2)double cursors, change record

DECLARE

CURSOR dept_cursor IS

SELECT department_id, department_name

FROM departments

WHERE department_id < 100 ORDER BY department_id;

CURSOR emp_cursor( v_deptno NUMBER) IS

SELECT last_name, job_id, hire_date, salary

FROM employees

WHERE department_id = v_deptno

AND employee_id < 120;

v_current_deptno  departments.department_id%TYPE;

v_current_dname   departments.department_name%TYPE;

v_ename           employees.last_name%TYPE;

v_job             employees.job_id%TYPE;

v_hiredate        employees.hire_date%TYPE;

v_sal             employees.salary%TYPE;

BEGIN

--First cursor begin

OPEN dept_cursor;

LOOP

FETCH dept_cursor INTO v_current_deptno, v_current_dname;

EXIT WHEN dept_cursor%NOTFOUND;

DBMS_OUTPUT.put_line('Department Number: ' || v_current_deptno

|| ' Department Name: ' || v_current_dname);

DBMS_OUTPUT.put_line(chr(13));

--Second cursor begin

IF emp_cursor%ISOPEN THEN CLOSE emp_cursor; END IF;

OPEN emp_cursor( v_current_deptno);

LOOP

FETCH emp_cursor INTO v_ename,v_job,v_hiredate,v_sal;

EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.put_line(v_ename ||', '

||v_job||', '

||v_hiredate ||', '

||v_sal );

END LOOP;

IF emp_cursor%ISOPEN THEN CLOSE emp_cursor; END IF;

DBMS_OUTPUT.put_line(chr(13));

END LOOP;

IF emp_cursor%ISOPEN THEN CLOSE emp_cursor; END IF;

CLOSE dept_cursor;

END;

/