PLSQL 101

基礎 PLSQL 指令

SELECT

SELECT * FROM KT_TEST_CHI;

FOR LOOP

BEGIN

    FOR i IN 1..10
    LOOP
        INSERT INTO KT_TEST_CHI VALUES('1',i,'1');
    END LOOP;
END;

INSERT ALL

INSERT ALL
          INTO table_reference [(column [,column...])]
                    [VALUES ({expr | default} [,{expr | default}...])]
          INTO table_reference [(column [,column...])]
                    [VALUES ({expr | default} [,{expr | default}...])]
          (subquery);

GROUP BY (HAVING)

SELECT NAME, SUM(CARD_NUM), COUNT(*)
    FROM KT_TEST_CHI 
    GROUP BY NAME
    ;
    --HAVING SUM(CARD_NUM)>6; --針對函數下條件

ORDER BY [ASC, DESC]

SELECT *
    FROM KT_TEST_CHI 
    ORDER BY NAME DESC; --降冪: 大到小

JOIN

SELECT *
    FROM KT_TEST_CHI table1
    INNER JOIN
    --LEFT JOIN
    --RIGHT JOIN
    --FULL OUTER JOIN
    (SELECT * FROM KT_TEST_CHI2) table2
    ON table2.C_ID = table1.C_ID;

UPDATE

UPDATE KT_TEST_CHI
    SET NAME=3
    WHERE C_ID=1;

UNION

UNION 指令的目的是將兩個 SQL 語句的結果合併起來。
UNION 的一個限制是兩個 SQL 語句所產生的欄位需要是同樣的資料種類。

[SQL 語句 1]
UNION
[SQL 語句 2];

SELECT DISTINCT

知道這個表格/欄位內有哪些不同的值，而每個值出現的次數並不重要。

SELECT DISTINCT "欄位名" 
FROM "表格名";

SELECT DISTINCT Store_Name FROM Store_Information;

Subquery

Where clause

SELECT *
    FROM KT_TEST_CHI 
    WHERE KT_TEST_CHI.C_ID IN
    (SELECT C_ID
    FROM KT_TEST_CHI2 table2
    where table2.C_ID='1')
    ;

IN 會逐行比較，也就是說Subquery撈出幾筆，就會全部掃過一次(但也只需要執行一次)，而且該欄位如果沒有unique，還必須做Distinct
但是EXISTS 只要比對到一個符合條件就會跳出，可是main query 執行幾次，subquery就得執行幾次
因此，exists 遇到 subquery 越大的情況時，表現就會比 in 來得好
反之，subquery越小，in的效能越好
大部分用到IN 和 EXISTS 的Case，都可以寫成標準的Join來改善效能

From clause

###### Inline view
SELECT *
    FROM KT_TEST_CHI table1,
    (SELECT C_ID FROM KT_TEST_CHI2)table2
    WHERE table2.C_ID = table1.C_ID;

Select clause

SELECT table1.NAME ,
    (SELECT count(*) FROM KT_TEST_CHI2)table2
    FROM KT_TEST_CHI table1;

COUNT

表內總比數
COUNT(*)
COUNT(1)

查詢效能：
COUNT(1) = COUNT(index_not_null) >= COUNT(*) = COUNT(index_nullable) > COUNT(column)

ROWNUM

WHERE ROWNUM=1 –第一筆資料

NVL

NVL（表達式1，表達式2）
如果表達式1為空值，NVL返回值為表達式2的值，否則返回表達式1的值。

此例子若NAME是null就不會被SELECT進來

SELECT *
    FROM KT_TEST_CHI 
    WHERE NAME>=0;

若一樣需要篩選出NULL的資料可以如下修改。

SELECT *
    FROM KT_TEST_CHI 
    WHERE NVL(NAME,0)>=0;

Decode

依據條件更改輸出值。
條件值可以多個，不過記得一個條件配一個輸出值。

SELECT DECODE( 欲判斷的資料, 判斷值1, 相等的設定值1
                           , 判斷值2, 相等的設定值2
                           , ...
                           , 不相等的設定值
             )
  FROM ...;

SELECT DECODE(NAME, '1', '一',
                    '2', '二'
                    , 'NONE'
            ) TEXT1
    , NAME
    FROM KT_TEST_CHI;

Case When

SELECT CASE NAME
        WHEN '1' THEN '一'
        WHEN '2' THEN '二'
        ELSE 'NONE'
    END TEXT1
    , NAME
    FROM KT_TEST_CHI;

Note

DELETE前先改成SELECT確認資料是否正確

null!=null

The null has a memory reference. So, one null value cannot be equal to another null value

select 
    case when null=null
        then 'Yes'
        else 'No'
    end as Result
from dual;

// Result=No

印PLSQL(記得打開”DBMS輸出”)

dbms_output.put_line('姓名:' || i.NAME);

常PLSQL切換和TSQL很花時間

所以可以
1.SELECT
2.PLSQL處理資料
3.一次bulk insert

LOOP

DECLARE
i NUMBER:=0;
BEGIN
    LOOP
        i:=i+1;
    EXIT WHEN i>10;
    END LOOP;
    dbms_output.put_line(i);  

END;

FOR LOOP

DECLARE
BEGIN

    FOR i IN 1..10
    LOOP
        dbms_output.put_line(i);
    END LOOP;
END;

WHILE LOOP

DECLARE
i NUMBER:=0;
BEGIN
    WHILE i<10
    LOOP
        i:=i+1;
    END LOOP;
    dbms_output.put_line(i);  
END;

宣告等同其他欄位型態

DATA_DATE EDLS.TT_LN_BASE.DATA_DATE%type

暫存表

WITH TT_TEST AS
(
SELECT FROM KT_TEST_CHI
)SELECT FROM TT_TEST;

Cursor 種類

Implicit cursors(隱式)
自動建立 by Oracle
不可控

    SQL Cursor attribute
    %FOUND
    %NOTFOUND
    %ISOPEN
    %ROWCOUNT

    語法： SQL%attribute_name
    Ex. SQL%ROWCOUNT


Explicit cursors(顯示)
使用者建立
可控

    --1. Declare Open Fetch Close
    DECLARE
        CURSOR CURSOR_NAME IS
            SELECT_STATEMENT;

        CURSOR_NAME_REC CURSOR_NAME%ROWTYPE;
    BEGIN
        OPEN CURSOR_NAME;
        FETCH CURSOR_NAME INTO CURSOR_NAME_REC;
        CLOSE CURSOR_NAME;
        dbms_output.put_line('姓名:' || CURSOR_NAME_REC.NAME);
    END;

    --2. LOOP
    BEGIN
        OPEN CURSOR_NAME;
        LOOP
            FETCH CURSOR_NAME INTO CURSOR_NAME_REC;
            EXIT WHEN CURSOR_NAME%NOTFOUND;
            dbms_output.put_line('姓名:' || CURSOR_NAME_REC.NAME);
        END LOOP;
        CLOSE CURSOR_NAME;

    END;
    --3. FOR LOOP (效能近似 Bulk Collect)
    BEGIN
        FOR CURSOR_ROW IN CURSOR_NAME
        LOOP
            dbms_output.put_line('姓名:' || CURSOR_ROW.NAME);
        END LOOP;
    END;

SELECT FOR UPDATE

DECLARE
CURSOR CURSOR_NAME
IS
    SELECT_STATEMENT FOR UPDATE [OF COLUMN_LIST] [NOWAIT];
BEGIN

使用時機：
    取出某筆資料作處理，並且不希望其他Session去變更
    Row lock / 需要commit, rollback 才能釋放

1. OF COLUMN_LIST 主要用在多表關聯時，說明鎖定哪個Table，沒有添加的話，所有的相關的Table都會Lock
2. NOWAIT ： 當有一個Transaction Lock 住某筆資料時，而我們又想Lock那筆資料，就會發生衝突，添加了 NOWAIT 就不會在那邊等待，會直接報錯！ 
    Ora-00054 resource busy and acquire with NOWAIT specified

Cursor variable

Strong type的Ref Cursor執行時錯誤較少，因為PL/SQL編譯器在編譯時若發現查詢結果的資料型態與Ref Cursor定義的回傳形態不同就會先報錯。

Weak Type

Type CURSOR_TYPE IS REF CURSOR;
CURSOR_VAR CURSOR_TYPE;

Strong Type

Type CURSOR_TYPE IS REF CURSOR RETURN table%ROWTYPE;
CURSOR_VAR CURSOR_TYPE;

Record

DECLARE
TYPE TYPE_NAME IS RECORD
(
FIELD_NAME1 DATATYPE1 [NOT NULL] [:= DEFAULT EXPRESSION],
…
);
RECORD_NAME TYPE_NAME;
BEGIN
END;

Collection (Table)

DECLARE
TYPE TYPE_NAME IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
TYPE_LIST TYPE_NAME;
name VARCHAR2(20);
BEGIN
TYPE_LIST(‘aaa’):=1;
TYPE_LIST(‘bbb’):=2;

name := TYPE_LIST.FIRST;
WHILE name IS NOT null LOOP
dbms_output.put_line(name|| ' ' || TYPE_LIST(name));
    name := TYPE_LIST.NEXT(name);
END LOOP;

END;

批次處理

Row-by-row switching is “slow-by-slow processing”
降低PLSQL Engine 和 SQL Engine溝通次數(context switch)

Bulk Collect
Forall

例子: 中間需要檢核
checked BOOLEAN;
BEGIN
FOR REC_NAME –SELECT進來的資料
IN(SELECT_STATEMENT)
LOOP
check(,,checked); –檢核
IF checked
THEN
UPDATE_STATEMENT; –更新
END IF;
END LOOP;

END;

更好的方法 —->

REC_NAME1 TYPE_NAME; --SELECT進資料
REC_NAME2 TYPE_NAME; --處理後要放進的資料

SELECT FIELD_NAME BULK COLLECT INTO REC_NAME1
FROM ...
WHERE ...;

--檢核
FOR idx IN 1..REC_NAME1.COUNT LOOP

    check(,,checked);
    IF checked
    THEN
        REC_NAME2(REC_NAME2.COUNT+1):=REC_NAME1(idx); --塞入檢核成功的資料
    END IF;


END LOOP;
-- 一次更新所有檢核過的資料
FORALL idx IN 1..REC_NAME2.COUNT
    UPDATE_STATEMENT;

LIMIT

若電腦性能無法一次將所有資料bulk collect into到record，可以分批做

cursor cur is
SELECT_STATEMENT;

BEGIN

OPEN cur;
LOOP
    FETCH cur BULK COLLECT INTO REC_NAME2 LIMIT 100;
    EXIT WHEN cur%NOTFOUND;
END LOOP;

END;

FORALL IN INDICES OF

FORALL idx IN 1..REC_NAME2.COUNT
——>
FORALL idx IN INDICES OF REC_NAME2

若REC_NAME2中間有些欄位檢核過後被刪除，
此集合的index 就不會是連續的 1~N
原本的FORALL INDX IN 1..集合.COUNT 就會出現錯誤
所以可以使用 INDICES OF 集合

例外處理

架構
DECLARE

exception_1 EXCEPTION;    --自訂例外

BEGIN

RAISE exception_1; --拋出例外

…
EXCEPTION

WHEN exception_1 THEN
    null;
WHEN exception_n THEN
    null;
WHEN OTHERS THEN
    null;

END;

自訂例外並統一拋到 others 處理
DECLARE
BEGIN

RAISE_APPLICATION_ERROR(-20001,'error!!');

…
EXCEPTION

WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE||''||SQLRPM);

END;

FORALL (SAVE EXCEPTIONS)
若總共insert 1000筆，9001發生DUP_VAL_ON_INDEX
若Commit就還有999筆還沒insert
若rollback就要重做

——>

可以透過SAVE EXCEPTIONS，當FORALL發生錯誤，
把錯誤存在SQL%BULK_EXCEPTIONS
PLSQL繼續執行，當所有Statement執行完畢，PLSQL會Raise ORA-24381 Error

BEGIN
FORALL idx IN 1..REC_NAME2.COUNT SAVE EXCEPTIONS
UPDATE_STATEMENT;

EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE=-24381 THEN
            FOR idx IN 1..SQL%BULK_EXCEPTIONS.COUNT
            LOOP
                dbms_output.put_line(
                SQL%BULK_EXCEPTIONS(idx).ERROR_INDEX
                ||':' 
                ||SQL%BULK_EXCEPTIONS(idx).ERROR_CODE);

            END LOOP;

END;