Declaring the Cursor
CURSOR cursor_name IS
select_statement;
Open, Fetch&Close
OPEN emp_cursor;
FETCH emp_cursor INTO empno, lname;
CLOSE emp_cursor;
Cursors and Records
emp_record emp_cursor%ROWTYPE;
……
FETCH emp_cursor INTO emp_record;
Cursor FOR Loops
FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;
说明:无须显式地打开、fetch、关闭游标。无须定义record_name。
Explicit Cursor Attributes
%ISOPEN, %NOTFOUND, %FOUND, %ROWCOUNT
Cursor FOR Loops Using Subqueries
FOR emp_record IN (SELECT employee_id, last_name
FROM employees WHERE department_id =30)
说明:无须显式声明CURSOR变量。但不能直接引用cursor attributes。
Cursors with Parameters
CURSOR cursor_name
[(parameter_name datatype, ...)]
IS
select_statement;
OPEN cursor_name(parameter_value,.....) ;
说明:一般用于在查询表达式中代入不同的变量值,多用于用同一个cursor先后打开多个不同的结果集。
FOR UPDATE Clause
SELECT ...
FROM ...
FOR UPDATE [OF column_reference][NOWAIT | WAIT n];
显式地锁定某些记录,避免其他会话修改记录导致游标失效。
WHERE CURRENT OF Clause
WHERE CURRENT OF cursor ;
UPDATE employees
SET salary = ...
WHERE CURRENT OF emp_cursor;
与FOR UPDATE合用,一般用于update或delete当前游标所指向的记录。
Handling Exceptions with PL/SQL
抛出异常的两种方式:
– Implicitly by the Oracle server
– Explicitly by the program
处理异常的两种方式:
– By trapping it with a handler
– By propagating it to the calling environment
Exception Types
• Predefined Oracle server Implicitly raised
• Non-predefined Oracle server Implicitly raised
• User-defined Explicitly raised
Trapping Exceptions
EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
statement1;
statement2;
. . .
[WHEN exception3 [OR exception4 . . .] THEN
statement1;
statement2;
. . .]
[WHEN OTHERS THEN
statement1;
statement2;
. . .]
Trapping Predefined Oracle Server Errors
20种左右预先定义的常见错误,可以在exception模块中直接引用
Sample predefined exceptions:
– NO_DATA_FOUND
– TOO_MANY_ROWS
– INVALID_CURSOR
– ZERO_DIVIDE
– DUP_VAL_ON_INDEX
Trapping Non-Predefined Oracle Server Errors
1. Declare: Name the exception.
insert_excep EXCEPTION;
2. Associate: Use PRAGMA EXCEPTION_INIT.
PRAGMA EXCEPTION_INIT(insert_excep, -01400);
3. Reference: Handle the raised exception.
EXCEPTION
WHEN insert_excep THEN ……
Functions for Trapping Exceptions
SQLCODE, SQLERRM
不能直接在SQL中使用,而必须先赋给一个变量值
Trapping User-Defined Exceptions
1. Declare: Name the exception.
invalid_department EXCEPTION;
2. Raise: Explicitly raise the exception by using the RAISE statement.
RAISE invalid_department;
3. Reference: Handle the raised exception.
EXCEPTION
WHEN invalid_department THEN
Propagating Exceptions in a Subblock
在subblock中直接处理异常或到外层block中处理,在哪一层中处理就跳出哪一层继续执行
One advantage of this behavior is that you can enclose statements that require their own exclusive error handling in their own block, while leaving more general exception handling to the enclosing block.
RAISE_APPLICATION_ERROR Procedure
raise_application_error (error_number, message[, {TRUE | FALSE}]);
error_number在-20000到-20999之间
Procedures and Functions
存储在数据库中,创建时编译一次,以后发生修改才再次编译。
Procedure: Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .)]
IS|AS
procedure_body;
Invoking the Procedure
BEGIN
add_dept;
END;
/
You can also invoke a procedure with the SQL statement CALL .
Function: Syntax
CREATE [OR REPLACE] FUNCTION function_name
[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .)]
RETURN datatype
IS|AS
function_body;
Defining REF CURSOR Types
1.定义type(return有无-strong或weak)
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
2.定义变量
ref_cv ref_type_name;
Using the OPEN-FOR, FETCH, and CLOSE Statements
OPEN {cursor_variable | :host_cursor_variable} FOR dynamic_string
[USING bind_argument[, bind_argument]...];
FETCH {cursor_variable | :host_cursor_variable}
INTO {define_variable[, define_variable]... | record};
CLOSE {cursor_variable | :host_cursor_variable};
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8558093/viewspace-1014419/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8558093/viewspace-1014419/