Programs Knowledge

1. 變數用法

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

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

varName2 := paramter1;

其他用法:

declare

    v_table tableName%rowType; --建立row type

    v_col tableName.column1%TYPE; --建立column type

begin

    select column1 into v_col from tableName where ...;

    select * into v_table from tableName where ...;

end;

2. For loop

2.1

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

varName2 := point.colName;

END LOOP;

2.2

FOR point IN 1...100 LOOP

...

END LOOP;

2.3

LOOP

    ...

    exit when(...);

END LOOP;

...

END LOOP;

3. IF ELSIF

IF varName1 IS NULL THEN

varName1 := 0;

ELSIF other condition THEN

...

END IF;

4. Case When

case

when variable1 := 'a' then variable2 := 'a1';

when variable1 := 'b' then variable2 := 'a2';

...

else variable2 := 'ok';

4. Array

Type array_type is table of varchar2(100) index by binary_integer;

_array array_type;

_array(0) := 'a';

_array(1) := 'b';