[PL/SQL]10g PL/SQL學習筆記（三）

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@]