游标语法
显示定义游标
1)无参数游标
CURSOR company_cur
IS
SELECT company_id FROM company;
2)有参数游标
CURSOR company_cur (id_in IN NUMBER) IS
SELECT name FROM company
WHERE company_id = id_in;
打开游标
OPEN cursor_name [(argument [,argument ...])];
从游标里面取值
FETCH cursor_name INTO record_or_variable_list;
关闭游标
CLOSE cursor_name;
游标属性
%ISOPEN
TRUE if cursor is open.
FALSE if cursor is not open.
%FOUND
INVALID_CURSOR is raised if cursor has not been OPENed.
NULL before the first fetch.
TRUE if record was fetched successfully.
FALSE if no row was returned.
INVALID_CURSOR if cursor has been CLOSEd.
%NOTFOUND
INVALID_CURSOR is raised if cursor has not been OPENed.
NULL before the first fetch.
FALSE if record was fetched successfully.
TRUE if no row was returned.
INVALID_CURSOR if cursor has been CLOSEd.
%ROWCOUNT
INVALID_CURSOR is raised if cursor has not been OPENed.
The number of rows fetched from the cursor.
INVALID_CURSOR if cursor has been CLOSEd.
完整例子
DECLARE
CURSOR user_cur IS SELECT * from user_info;
user_rec user_info%rowtype;
BEGIN
OPEN user_cur;
LOOP
FETCH user_cur into user_rec; --读取游标当前值
EXIT WHEN user_cur%NOTFOUND; --读取完成时跳出循环
UPDATE user_info set last_accessed
DBMS_OUTPUT.put_line(user_rec.name);
END LOOP;
CLOSE user_cur;
END;
当使用代码块进行INSERT, UPDATE, DELETE, 和SELECT INTO 等操作时,隐式游标被自动使用。它不需要进行打开、关闭、取值等操作,可以通过SQL游标来获取其属性
%ISOPEN
Always FALSE since the cursor is opened implicitly and closed immediately after the statement is executed.
%FOUND
NULL before the statement.
TRUE if one or more rows were inserted, updated, or deleted or if only one row was selected.
FALSE if no row was selected, updated, inserted, or deleted.
%NOTFOUND
NULL before the statement.
TRUE if no row was selected, updated, inserted, or deleted.
FALSE if one or more rows were inserted, updated, or deleted.
%ROWCOUNT
The number of rows affected by the cursor.
%BULK_ROWCOUNT (Oracle8i)
A pseudo index-by table containing the numbers of rows affected by the statements executed in bulk bind operations. See the "Bulk Binds (Oracle8i)" section for more information on %BULK_ROWCOUNT.
代码例子
BEGIN
UPDATE activity SET last_accessed := SYSDATE
WHERE UID = user_id;
IF SQL%NOTFOUND THEN --引用隐式游标的NOTFOUND属性
INSERT INTO activity_log (uid,last_accessed)
VALUES (user_id,SYSDATE);
END IF
END;
利用SELECT FOR UPDATE 实现部分锁定游标打开的表
语法
SELECT ...
FROM ...
FOR UPDATE [OF column_reference] [NOWAIT];
例子
DECLARE
CURSOR hounds_in_stock_cur IS
SELECT pet.stock_no, pet.breeder, dog.size
FROM dog_breeds dog ,inventory pet
WHERE dog.breed = pet.breed
AND dog.class = 'HOUND'
FOR UPDATE OF pet.stock_no, pet.breeder;
BEGIN
上面的代码只锁定表pet而不会锁定表dog
使用WHERE CURRENT OF操作游标当前记录
语法
[UPDATE | DELETE ] ...
WHERE CURRENT OF cursor_name;
例子
DECLARE
CURSOR wip_cur IS
SELECT acct_no, enter_date FROM wip
WHERE enter_date < SYSDATE -7
FOR UPDATE;
BEGIN
FOR wip_rec IN wip_cur
LOOP
INSERT INTO acct_log (acct_no, order_date)
VALUES (wip_rec.acct_no, wip_rec.enter_
date);
DELETE FROM wip
WHERE CURRENT OF wip_cur;
END LOOP;
END;
游标变量
OPEN cursor_name FOR select_statement;