对于那些仅仅返回一行记录的SQL语句, 本身又没有显式地使用Open语句, 其实就是隐式游标.
Implicit cursors
For SQL queries returning single row PL/SQL declares implicit cursors. Implicit cursors are simple SELECT statements and are written in the BEGIN block (executable section) of the PL/SQL. Implicit cursors are easy to code, and they retrieve exactly one row. PL/SQL implicitly declares cursors for all DML statements. The most commonly raised exceptions here are NO_DATA_FOUND or TOO_MANY_ROWS.
Syntax:
SELECT ename, sal INTO ena, esa FROM EMP WHERE EMPNO = 7844;
Note: Ename and sal are columns of the table EMP and ena and esa are the variables
used to store ename and sal fetched by the query.
显式游标, 其实显式游标也可以分为显式打开的游标, 和隐式打开的游标, 对于前者, 应该在最后关闭游标.
这是一个显式打开的显式游标
DECLARE
CURSOR trip_cursor IS
SELECT bt_id_pk, bt_hotel_id
FROM business_trips;
trip_record trip_cursor%ROWTYPE
BEGIN
OPEN trip_cursor;
LOOP
FETCH trip_cursor INTO trip_record;
EXIT WHEN trip_cursor%NOTFOUND;
INSERT INTO copy_of_business_trips (bt_id_pk, bt_hotel_id)
VALUES (trip_record.bt_id_pk, trip_record.bt_hotel_id);
END LOOP;
CLOSE job_cursor;
END;
--这个是隐式打开的显式游标, 在使用之后, 你不必手动地关闭这个游标
--Retrieve all trips from the t_business_trips table using a cursor
and print only those with a duration of one day:
SET SERVEROUTPUT ON
DECLARE
CURSOR trip_cursor IS
SELECT bt_id_pk, bt_duration
FROM business_trips;
BEGIN
FOR trip_record IN trip_cursor LOOP
-- implicit open/fetch occur
IF trip_record.bt_duration = 1 THEN
DBMS_OUTPUT_LINE ('Trip Number ' || trip_record.bt_id_pk
|| ' is a one day trip');
END IF;
END LOOP; -- IMPLICIT CLOSE OCCURS
END;
参考1
http://www.dba-oracle.com/plsql/t_plsql_cursors.htm
http://www.exforsys.com/tutorials/oracle-9i/oracle-cursors.html
