游标的使用非常广。以下是举例了一些游标的使用。
----游标的使用:
---显式游标:
hr@PROD>DECLARE
2 CURSOR c_emp_cursor IS
3 SELECT employee_id, last_name
4 FROM employees WHERE department_id = 30;
5 v_empno employees.employee_id%TYPE;
6 v_lname employees.last_name%TYPE;
7 BEGIN
8 OPEN c_emp_cursor;
9 LOOP
10 FETCH c_emp_cursor
11 INTO v_empno, v_lname;
12 EXIT WHEN c_emp_cursor%NOTFOUND;
13 DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
14 END LOOP;
15 CLOSE c_emp_cursor;
16 END;
17 /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
PL/SQL procedure successfully completed.
#程序执行完成,返回6条记录。
--使用SQL语言查询做对比:
hr@PROD>select employee_id,last_name from employees
2 where department_id = 30;
EMPLOYEE_ID LAST_NAME
----------- -------------------------
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
6 rows selected.
---参数游标:(使用带参数变量)
hr@PROD>DECLARE
2 CURSOR c_emp_cursor(p_department_id NUMBER default 10) IS
3 SELECT employee_id, last_name
4 FROM employees
5 WHERE department_id = p_department_id;
11 v_empno employees.employee_id%TYPE;
12 v_lname employees.last_name%TYPE;
13 BEGIN
14 OPEN c_emp_cursor;
15 LOOP
16 FETCH c_emp_cursor
17 INTO v_empno, v_lname;
18 EXIT WHEN c_emp_cursor%NOTFOUND;
19 DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
20 END LOOP;
21 END;
22 /
200 Whalen
PL/SQL procedure successfully completed.
#程序执行完成,返回一条记录。
--直接使用SQL语句查询对比结果:
hr@PROD>select employee_id,last_name from employees
2 where department_id = 10;
EMPLOYEE_ID LAST_NAME
----------- -------------------------
200 Whalen
#结果是一样的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2128354/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2128354/