PLSQL 開發要點

PLSQL 開發要點[@more@]

PL/SQL 塊語法（匿名塊）

[DECLARE]

---declaration statements

BEGIN

---executable statements

[EXCEPTION]

---exception statements

END

Ø 函式

定義函式的語法如下：

FUNCTION name [{parameter[,parameter,...])] RETURN datatypes IS

[local declarations]

BEGIN

execute statements

[EXCEPTION

exception handlers]

END [name]

Ø 儲存過程

與函式不同，儲存過程沒有返回值，儲存過程

不能由SQL 語句直接使用，只能透過EXECUT 命令或PL/SQL 程式塊內部呼叫，定義存

儲過程的語法如下：

PROCEDURE name [(parameter[,parameter,...])] IS

[local declarations]

BEGIN

execute statements

[EXCEPTION

exception handlers ]

END [name]

Ø 宣告變數的語法如下：

Variable_name [CONSTANT] databyte [NOT NULL][:=|DEFAULT expression]

注意:可以在宣告變數的同時給變數強制性的加上NOT NULL 約束條件，此時變數在初

始化時必須賦值。

給變數賦值

給變數賦值有兩種方式：

1．直接給變數賦值

X:=200;

Y=Y+(X*20);

2．透過SQL SELECT INTO 或FETCH INTO 給變數賦值

SELECT SUM(SALARY),SUM(SALARY*0.1) INTO TOTAL_SALARY,TATAL_COMMISSION FROM EMPLOYEE WHERE DEPT=10;

Ø 常量

常量與變數相似，但常量的值在程式內部不能改變，常量的值在定義時賦予，他的聲

明方式與變數相似，但必須包括關鍵字CONSTANT。常量和變數都可被定義為SQL 和用

戶定義的資料型別。

ZERO_VALUE CONSTANT NUMBER:=0;

Ø LOB 資料型別

LOB(大物件,Large object) 資料型別用於儲存類似影像，聲音這樣的大型資料物件，

LOB 資料物件可以是二進位制資料也可以是字元資料，其最大長度不超過4G。LOB 資料類

型支援任意訪問方式，LONG 只支援順序訪問方式。LOB 儲存在一個單獨的位置上，同時

一個"LOB 定位符"(LOB locator)儲存在原始的表中，該定位符是一個指向實際資料的指標。

在PL/SQL 中操作LOB 資料物件使用ORACLE 提供的包DBMS_LOB.LOB 資料型別可分

為以下四類：

. BFILE

. BLOB

. CLOB

. NCLOB

Ø 運算子

= 等於運算子

!= 不等於運算子

<> 不等於運算子

:= 賦值操作

定義語言(Data Definition language)不能在執行部分中使用，DDL 語句與EXECUTE

IMMEDIATE 一起使用或者是DBMS_SQL 呼叫。

Ø IF判斷

IF condition THEN

Statements 1;

Statements 2;

....

ELSE

Statements 1;

Statements 2;

....

END IF

Ø 迴圈控制

LOOP

statements;

END LOOP

例：

X:=100;

LOOP

X:=X+10;

IF X>1000 THEN

EXIT;

END IF

END LOOP;

Y:=X;

EXIT WHEN 語句將結束迴圈，如果條件為TRUE，則結束迴圈。

X:=100;

LOOP

X:=X+10;

EXIT WHEN X>1000;

X:=X+10;

END LOOP;

Y:=X;

WHILE..LOOP

X:=100;

WHILE X<=1000 LOOP

X:=X+10;

END LOOP;

Y=X;

FOR...LOOP

語法：

FOR counter IN [REVERSE] start_range....end_range LOOP

statements;

END LOOP;

X:=100;

FOR v_counter in 1..10 loop

x:=x+10;

end loop

y:=x;

Ø PL/SQL 記錄

顯式定義記錄

TYPE record_type IS RECORD (field_definition_list);

例：

DELCARE

TYPE stock_quote_rec IS RECORD

(symbol stock.symbol%TYPE

, bid NUMBER(10,4)

, ask NUMBER(10,4)

, volume NUMBER NOT NULL:=0

, exchange VARCHAR2(6) DEFAULT 'NASDAQ'

);

real_time_quote stock_quote_rec;

variable

域定義時的%TYPE 屬性用於引用資料庫中的表或檢視的資料型別和大小。

隱式定義記錄

隱式定義記錄中，我們不用描述記錄的每一個域。這是因為我們不需要定義記錄的結構，

不需要使用TYPE 語句，相反在宣告記錄變數時使用%ROWTYPE 命令定義與資料庫表，

檢視，遊標有相同結構的記錄，與TYPE 命令相同的是它是一種定義獲得資料庫資料記錄

的好方法。

DECLARE

accounter_info accounts%ROWTYPR;

CURSOR xactions_cur(acct_no IN VARCHAR2) IS

SELECT action,timestamp,holding

FROM portfolios

WHERE account_nbr='acct_no';

xaction_info xactions_cur%ROWTYPE;

variable

有一些PL/SQL 指令在使用隱式定義記錄時沒有使用%ROWTYPE屬性，比如遊標

FOR 迴圈或觸發器中的:old 和:new 記錄。

DELCARE

CURSOR xaction_cur IS

SELECT action,timeamp,holding

FROM portfolios

WHERE account_nbr='37'

;

BEGIN

FOR xaction_rec in xactions_cur

LOOP

IF xactions_rec.holding='ORCL'

THEN

notify_shareholder;

END IF;

END LOOP;

Ø 給記錄賦值

1、使用SELECT INTO

DECLARE

stock_info1 stocks%ROWTYPE;

stock_info2 stocks%ROWTYPE;

BEGIN

SELECT symbol,exchange

INTO stock_info1.symbol,stock_info1.exchange FROM stocks

WHERE symbol='ORCL';

SELECT * INTO stock_info2 FROM stocks

WHERE symbol='ORCL';

2、使用FETCH

如果SQL 語句返回多行資料或者希望使用帶引數的遊標，那麼就要使用遊標，這種情

況下使用FETCH 語句代替INTO 是一個更簡單、更有效率的方法，但在安全性

較高的包中FETCH 的語法如下：

FETCH cursor_name INTO variable;

我們改寫上面的例子：

DECLARE

CURSOR stock_cur(symbol_in VARCHAR2) IS

SELECT symbol,exchange,begin_date

FROM stock

WHERE symbol=UPPER(symbol_in);

stock_info stock_cur%ROWTYPE

BEGIN

OPEN stock_cur('ORCL');

FETCH stock_cur INTO stock_info;

觸發器(Triggers)

觸發器是一種自動執行響應資料庫變化的程式。可以設定為在觸發器事件之前或之後觸

發或執行。能夠觸發觸發器事件的事件包括下面幾種：

DML 事件

DDL 事件

資料庫事件

DML 事件觸發器可以是語句或行級觸發器。DML 語句觸發器在觸發語句之前或之後觸

發DML 行級觸發器在語句影響的行變化之前或之後觸發。使用者可以給單一事件和型別定義

多個觸發器，但沒有任何方法可以增強多觸發器觸發的命令。下表列出了使用者可以利用的觸

發器事件：

INSERT 當向表或檢視插入一行時觸發觸發器

UPDATE 更新表或檢視中的某一行時觸發觸發器

DELETE 從表或檢視中刪除某一行時觸發觸發器

CREATE 當使用CREATE 語句為資料庫或專案增加一個物件時觸發觸發器

ALTER 當使用ALTER 語句為更改一個資料庫或專案的物件時觸發觸發器

DROP 當使用DROP 語句刪除一個資料庫或專案的物件時觸發觸發器

START 開啟資料庫時觸發觸發器，在事件後觸發

SHUTDOWN 關閉資料庫時觸發，事件前觸發

LOGON 當一個會話建立時觸發，事件前觸發

LOGOFF 當關閉會話時觸發，事件前觸發

SERVER 伺服器錯誤發生時觸發觸發器，事件後觸發

建立觸發器的語法如下：

CREATE [OR REPLACE] TRIGGER trigger_name

{before|after|instead of} event

ON {table_or_view_name|DATABASE}

[FOR EACH ROW[WHEN condition]]

trigger_body

只有DML 觸發器（INSERT、UPDATE、DELETE)語句可以使用INSTEAD OF 觸發

器並且只有表的DML 觸發器可以是BEFORE 或AFTER 觸發器。

Ø 遊標