oracle園地

a_auth auth_var := auth_var ();
BEGIN
a_auth.EXTEND;
a_auth (1) := ‘Smith’;
a_auth.EXTEND;
a_auth (2) := ‘Jones’;
DBMS_OUTPUT.put_line (a_auth (1) ‘ and ‘ a_auth (2));
END;

第二種方法，從資料庫查出值給array

PROCEDURE p_varray_collection IS
TYPE auth_var IS VARRAY (20) OF author.author_last_name%TYPE;

a_auth auth_var := auth_var ();
indx NUMBER := 1;
BEGIN
FOR r_c1 IN (SELECT author_last_name
FROM author)
LOOP
IF indx < a_auth.LIMIT
THEN
a_auth.EXTEND;
a_auth (a_auth.LAST) := r_c1.author_last_name;
DBMS_OUTPUT.put_line (a_auth (indx));
indx := indx + 1;
END IF;
END LOOP;
END;
**************************************

LPAD(char,m,str) 向左補字元
RPAD(char,m,str) 向右補字元
LTRIM(char, str) 向左去空白
RTRIM(char, str) 向右去空白
LOWER(char) 轉小寫
UPPER(char) 轉大寫
REPLACE(char, str1, str2) 替換字元
SUBSTR(char, m, n) 切字元
INSTR(m,n) 傳回n字串在m字串中的位置(切符號分隔字串)
ex:INSTR(’ABCD’,'CD’)=3
LENGTH(char) 字元長度
GREATEST(n1,n2) 取大值
LEAST(n1,n2) 取小值

時間日期：
SYSDATE [from DUAL]
LAST_DAY(date) date的最後一天 return date
ADD_MONTHS(date, n) date的n個月後 return date

+, -, *, /, **

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

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

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