oracle園地

+, -, *, /, **

<>, !=, ^=, >, <, =

(), ;,‘, :=, ||, –, /*

a.字首一定要為字母
b.只能用 $, # 或 _ 等特殊符號
c.變數不能超過 30 個字
d.變數裡不能有空白

LOOP, WHILE…

a.VCRCHAR2(max_length)
max_length: 1 ~ 32767
b.NUMBER(precision, scale)
precision: 1 ~ 38
ex. num NUMBER(10, 2)
表 num 這個變數有 8 整數, 2 位小數
c.DATE
d.BOOLEAN

a.區塊結構
DECLARE
/PROCEDURE procedure_name(w_char IN OUT VARCHAR2,…) IS
/FUNCTION function_name(w_char IN OUT VARCHAR2,…) RETURN Boolean IS
… ( 宣告區塊 )
BEGIN
…
EXCEPTION
… ( 例外區塊 )
END;
b.宣告區塊
(1)固定型態
w_variable NUMBER(1);
(2)變動型態 ( Complier 時決定 )
w_variable table.field%TYPE;
rec_variable table%ROWTYPE;

(3)自訂型態
TYPE record_type IS RECORD
( user_default_field_name1 NUMBER(1)
user_default_field_name1 table.field%TYPE,
…
);
record_name record_type;

(1)IF 邏輯結構
* IF – THEN – END IF
* IF – THEN – ELSE – END IF
* IF – THEN – ELSIF – END IF
* 空結構
(2)迴圈
* LOOP迴圈
LOOP
EXIT WHEN ;
END LOOP;
* WHILE迴圈
WHIEL LOOP

EXIT WHEN ;
END LOOP;
* FOR迴圈
FOR variable IN min_num .. max_num LOOP
EXIT WHEN ;
END LOOP;

錯誤發生時會進入此區塊處理
EXCEPTION
WHEN NO_DATA_FOUND THEN
…
WHEN OTHERS THEN
…
END;
(1)系統預設
(2)NO_DATA_FOUND
TOO_MANY_ROWS
DUP_VAL_ON_INDEX
VALUE_ERROR
(3)自行宣告
user_default_exception EXCEPTION;

DECLARE
CURSOR cursor_name IS
SELECT …
rec_cursor_record cursor_name%ROWTYPE;
BEGIN
…
OPEN cursor_name;
LOOP
FETCH cursor_name INTO rec_cursor_record;
EXIT WHEN cursor_name%NOTFOUND;
END LOOP;
CLOSE cursor_name;
…
END;
* CURSOR 宣告技巧
* CURSOR 參數
%NOTFOUND
%FOUND
%ROWCOUNT
%ISOPEN
* 動態 CURSOR
DECLARE
TYPE type_name IS REF CURSOR RETURN record_type;
cursor_name type_name;
record_name type_name;
BEGIN
OPEN cursor_name FOR
SELECT …
END;

* 結構
DECLARE
TYPE trec_chart IS RECORD
(chart_no chart.chart_no%TYPE,
pt_name chart.pt_name%TYPE );
TYPE ttab_chart IS TABLE OF trec_chart%ROWTYPE INDEX BY BINARY_INTEGER;
tab_chart ttab_chart;
BEGIN
…
END;
* 用法
tab_chart(index).chart_no := 1; ( i 可為不連續的整數 )
tab_chart.EXISTS(index)
tab_chart.COUNT
tab_chart.FIRST
tab_chart.LAST
tab_chart.PRIOR(index)
tab_chart.NEXT(index)
tab_chart.DELETE
tab_chart.DELETE(index)
tab_chart.DELETE(start_ index, end_ index)

index := tab_chart.FIRST;
WHILE index IS NOT NULL LOOP
…
index := tab_chart.NEXT(i);
END LOOP;
* EXCEPTION 狀況
IF tab_chart.chart_no(index) THEN
此敘述若 tab_chart.chart_no(index) 不存在則會觸發 NO_DATA_FOUND