常用日期格式對照
JAN-1 FEB-2 MAR-3 APR-4 MAY-5 JUN-6
JUL-7 AUG-8 SEP-9 OCT-10 NOV-11 DEC-12
登入觸發器範例
DECLARE
flag VARCHAR2(80) := NULL;
cou NUMBER := 1;
n NUMBER;
BEGIN
LOOP
LOGON(username, password||'@'||connect, PROPERTY_FALSE, ...);
flag := GET_APPLICATION_PROPERTY(DATASOURCE);
EXIT WHEN cou > 8 OR flag = 'ORACLE';
cou := cou + 1;
END LOOP;
IF flag != 'ORACLE' THEN
SET_ALERT_PROPERTY('a_1', ALERT_MESSAGE_TEXT, '登錄失敗,請返回重試');
n := SHOW_ALERT('a_1');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
基表查詢技巧
SET_BLOCK_PROPERTY('block_name', DEFAULT_WHERE, 'WHERE ...');
GO_BLOCK('block_name');
EXECUTE_QUERY;
變數層級
- 區域：
:block.item
- 參數：
:parameter.v_name
- 全域：
:global.v_name
同步顯示
SYNCHRONIZE;
手動觸發 Trigger
EXECUTE_TRIGGER('trigger_name');
清除區塊
CLEAR_BLOCK(NO_VALIDATE);
彈出警示框並分支
DECLARE n NUMBER;
BEGIN
SET_ALERT_PROPERTY('alert_name', ALERT_MESSAGE_TEXT, 'message');
n := SHOW_ALERT('alert_name');
IF n = ALERT_BUTTON1 THEN ...;
ELSIF n = ALERT_BUTTON2 THEN ...;
END IF;
END;
視窗屬性控制
SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, WINDOW_STATE, MAXIMIZE|MINIMIZE);
SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, TITLE, 'TEXT');
SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, REMOVE_ON_EXIT, PROPERTY_FALSE|TRUE);
系統訊息等級
:SYSTEM.MESSAGE_LEVEL := '5|10|15|20';
Item 屬性動態設定
SET_ITEM_PROPERTY('block.item', ENABLED, PROPERTY_TRUE|FALSE);
SET_ITEM_PROPERTY('block.item', NAVIGABLE, PROPERTY_TRUE|FALSE);
SET_ITEM_PROPERTY('block.item', VISUAL_ATTRIBUTE, 'vname');
SET_ITEM_PROPERTY('block.item', DISPLAYED, TRUE|FALSE);
SET_ITEM_PROPERTY('block.item', POSITION, x, y);
SET_ITEM_PROPERTY('block.item', ITEM_SIZE, x, y);
SET_ITEM_PROPERTY('block.item', LABEL, 'MESSAGE');
List Item 操作
-- 建立
DECLARE n NUMBER;
BEGIN
CLEAR_LIST('b1.fkfs');
n := POPULATE_GROUP('fkfs');
POPULATE_LIST('b1.fkfs', 'fkfs');
END;
-- 新增/刪除元素
ADD_LIST_ELEMENT(list_name, index, label, value);
DELETE_LIST_ELEMENT(list_name, index);
-- 取得元素資訊
GET_LIST_ELEMENT_COUNT(list_name);
GET_LIST_ELEMENT_LABEL(list_name, index);
GET_LIST_ELEMENT_VALUE(list_name, index);
Timer 建立
DECLARE timer_id TIMER; one_minute NUMBER(5) := 60000;
BEGIN
timer_id := CREATE_TIMER('emp_timer', one_minute, REPEAT|NO_REPEAT);
END;
Editor / LOV 呼叫
-- Editor
DECLARE ed_id EDITOR; status BOOLEAN;
BEGIN
ed_id := FIND_EDITOR('edit_name');
IF NOT ID_NULL(ed_id) THEN
SHOW_EDITOR(ed_id, NULL, :block.item, status);
END IF;
END;
-- LOV
DECLARE lv_id LOV; status BOOLEAN;
BEGIN
lv_id := FIND_LOV('lov_name');
status := SHOW_LOV(lv_id, 10, 20);
END;
自訂 EXCEPTION
DECLARE err_1 EXCEPTION;
BEGIN
IF ... THEN RAISE err_1; END IF;
EXCEPTION
WHEN err_1 THEN ...;
END;
游標屬性
%ISOPEN、%NOTFOUND、%FOUND、%ROWCOUNT
常用 PL/SQL 函數速查
| 函數 | 說明 |
|---|
| UPPER/LOWER/INITCAP | 大小寫轉換 |
| LPAD/RPAD | 左右填充 |
| LTRIM/RTRIM | 左右去除字元 |
| INSTR | 尋找子字串位置 |
| ABS/MOD/ROUND/TRUNC/SIGN | 數學運算 |
| GREATEST/LEAST | 列表最大/最小 |
| FLOOR/CEIL | 向下/向上取整 |
| SUBSTR/LENGTH | 子字串/長度 |
| DECODE | 多重 IF-THEN-ELSE |
| NVL | 空值替換 |
| ASCII/TRANSLATE | 字元碼/字元轉換 |
| AVG/COUNT/MAX/MIN/SUM | 多行彙總 |
迴圈結構
LOOP ... EXIT WHEN ... END LOOP;
WHILE condition LOOP ... END LOOP;
FOR i IN 1..10 LOOP ... END LOOP;
FOR rec IN (SELECT ...) LOOP ... END LOOP;
預定義異常
WHEN NO_DATA_FOUND THEN ...
WHEN TOO_MANY_ROWS THEN ...
WHEN INVALID_NUMBER THEN ...
WHEN ZERO_DIVIDE THEN ...
WHEN DUP_VAL_ON_INDEX THEN ...
WHEN INVALID_CURSOR THEN ...
WHEN VALUE_ERROR THEN ...
WHEN OTHERS THEN ...
TEXT_IO 檔案操作
DECLARE out_file TEXT_IO.FILE_TYPE;
BEGIN
out_file := TEXT_IO.FOPEN('prn', 'w');
TEXT_IO.NEW_LINE(out_file, ' ');
TEXT_IO.PUT_LINE(out_file, ' ');
TEXT_IO.FCLOSE(out_file);
END;
刪除重複記錄
DELETE FROM emp e
WHERE e.rowid > (SELECT MIN(f.rowid) FROM emp f WHERE f.empno = e.empno);
