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};