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

SELECT Statements in PL/SQL

必須使用INTO，只能選出一條記錄

Naming Conventions

避免變數名與列名相同，如相同，優先選擇列名。在where語句中會導致歧義。

DECLARE

hire_date employees.hire_date%TYPE;

sysdate hire_date%TYPE;

employee_id employees.employee_id%TYPE := 176;

BEGIN

SELECT hire_date, sysdate

INTO hire_date, sysdate

FROM employees

WHERE employee_id = employee_id;

END;

/

SQL Cursor

• A cursor is a pointer to the private memory area

allocated by the Oracle server.

• There are two types of cursors:

– Implicit: Created and managed internally by the

Oracle server to process SQL statements

– Explicit: Explicitly declared by the programmer

三種SQL Cursor Attributes

SQL%FOUND

SQL%NOTFOUND

SQL%ROWCOUNT

IF Statements

IF condition THEN

statements;

[ELSIF condition THEN

statements;]

[ELSE

statements;]

END IF;

CASE Expressions

CASE selector

WHEN expression1 THEN result1

WHEN expression2 THEN result2

...

WHEN expressionN THEN resultN

[ELSE resultN+1]

END;

/

TRUE AND NULL èNULL

TRUE OR NULL èTRUE

FALSE AND NULL èFALSE

FALSE OR NULL èNULL

NOT NULL èNULL

Basic Loops

LOOP

statement1;

. . .

EXIT [WHEN condition];

END LOOP;

WHILE Loops

WHILE condition LOOP

statement1;

statement2;

. . .

END LOOP;

FOR Loops(counter無需定義)

FOR counter IN [REVERSE]

lower_bound..upper_bound LOOP

statement1;

statement2;

. . .

END LOOP;

Guidelines for Loops

Basic loop:迴圈體中的語句至少需要被執行一次

While loop:必須將條件判斷放在每次迴圈之前

For loop:預先知道迭代次數

Nested Loops and Labels

迴圈可欠套使用，不同層的迴圈可使用labels來表示

<>

END LOOP outer_loop;

Creating a PL/SQL Record

TYPE type_name IS RECORD

(field_declaration[, field_declaration]…);

identifier type_name;

field_declaration:

field_name {field_type | variable%TYPE

| table.column%TYPE | table%ROWTYPE}

[[NOT NULL] {:= | DEFAULT} expr]

%ROWTYPE Attribute(無須另外定義record type)

DECLARE

identifier reference%ROWTYPE;

Updating a Row in a Table by Using a Record

SET SERVEROUTPUT ON

SET VERIFY OFF

DEFINE employee_number = 124

DECLARE

emp_rec retired_emps%ROWTYPE;

BEGIN

SELECT * INTO emp_rec FROM retired_emps;

emp_rec.leavedate:=SYSDATE;

UPDATE retired_emps SET ROW = emp_rec WHERE

empno=&employee_number;

END;

/

SELECT * FROM retired_emps;

Creating an INDEX BY Table

TYPE type_name IS TABLE OF

{column_type | variable%TYPE

| table.column%TYPE} [NOT NULL]

| table%ROWTYPE

[INDEX BY PLS_INTEGER | BINARY_INTEGER

| VARCHAR2()];

identifier type_name;

注意點：1.主鍵為interger型或string型(PLS_INTEGER or BINARY_INTEGER)

2.column為一個標量或一個record型

3.表的記錄數受主鍵的資料型別的範圍限制，可為負數

4.主鍵和column都不可以被命名

Using INDEX BY Table Methods

EXISTS

COUNT

FIRST and LAST

PRIOR

NEXT

DELETE

Nested Tables

TYPE type_name IS TABLE OF

{column_type | variable%TYPE

| table.column%TYPE} [NOT NULL]

| table.%ROWTYPE

說明：計數列為正數，從1開始引用。屬於資料庫的資料型別的一種

可使用constructor直接賦值

TYPE location_type IS TABLE OF locations.city%TYPE;

offices location_type;

offices := location_type('Bombay', 'Tokyo','Singapore','Oxford');

VARRAY

TYPE location_type IS VARRAY(3) OF locations.city%TYPE;

offices location_type;

說明：宣告時指定了最大值，可使用constructor直接賦值

[@more@]