Programs Knowledge

CREATE OR REPLACE TRIGGER TriggerName

AFTER INSERT or UPDATE on TableName --新增或更新此table後觸發

FOR EACH ROW

CALL ProcedureName(:NEW.ColumnName1, :NEW.ColumnName2); --指定兩個column新增或更新

後的值傳送至Procedure

CREATE OR REPLACE PROCEDURE ProcedureName(paramter1 VARCHAR2, paramter2 VARCHAR2)

AS

varName1 number; --宣告變數

varName2 varchar2(10);

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

--將參數update至某table中

EXECUTE IMMEDIATE 'UPDATE TableName SET ColumnName = ''' || paramter1 || ''' WHERE...';

COMMIT;

--其他procadure語法

--變數用法

varName1 := 0; --將varName1設為零

varName1 := varName1 + 1; --將varName1+1

varName2 := paramter1;

--For loop寫法

FOR point IN (select * from TableName where ...)

varName2 := point.colName;

END LOOP;

--將SQL command產生的值存入變數

select sum(colName) into varName1 from TableName where ... --將該column加總後放入變數中

IF varName1 IS NULL THEN

varName1 := 0;

END IF;

END