動態SQL一(EXECUTE IMMEDIATE、USING) (PL/SQL 二十四)

所謂動態SQL，簡單來說就是SQL是組合起來的

※錯誤的例子

CREATE OR REPLACE FUNCTION dynamic_sql(xxx_table VARCHAR2) RETURN NUMBER IS
    xxx_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO xxx_count FROM USER_TABLES WHERE TABLE_NAME = UPPER(xxx_table);
    IF xxx_count = 0 THEN
        CREATE TABLE xxx_table(
            ID NUMBER,
            NAME VARCHAR2(10),
            CONSTRAINT XXX_PK_' || xxx_table || ' PRIMARY KEY(ID)
        );
    END IF;
    
    SELECT COUNT(*) INTO xxx_count FROM xxx_table;
    RETURN xxx_count;
END;
    
    
    
BEGIN
    DBMS_OUTPUT.PUT_LINE(dynamic_sql('xxx'));
END;

※執行時，出現「PLS-00103: 發現了符號 "CREATE" 當您等待下列事項之一發生時:~~」，也就是沒辦法直接在裡面寫CREATE，所以修改如下：

※EXECUTE IMMEDIATE

※範例一

CREATE OR REPLACE FUNCTION dynamic_sql(xxx_table VARCHAR2) RETURN NUMBER IS
    xxx_count NUMBER;
    dynamic_create VARCHAR2(100);
BEGIN
    SELECT COUNT(*) INTO xxx_count FROM USER_TABLES WHERE TABLE_NAME = UPPER(xxx_table);
    IF xxx_count = 0 THEN
    dynamic_create := 'CREATE TABLE ' || xxx_table || -- 注意CREATE TABLE後要加空格
    '(
        ID NUMBER,
        NAME VARCHAR2(10),
        CONSTRAINT XXX_PK_' || xxx_table || ' PRIMARY KEY(ID)
    )';
    EXECUTE IMMEDIATE dynamic_create;
    END IF;
    
    dynamic_create := 'SELECT COUNT(*) FROM ' || xxx_table; -- 注意FROM後要加空格
    EXECUTE IMMEDIATE dynamic_create INTO xxx_count;
    RETURN xxx_count;
END;

※執行時如果出現權限不足，使用sys登入
CONN sys as SYSDBA-->密碼
GRANT CREATE ANY TABLE TO 帳號名稱

※CONSTRAINT一定要加xxx_table，不然執行第二次會發現名字重覆

※範例二

DECLARE
    dynamic_sql VARCHAR2(200);
    xxx_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO xxx_count FROM USER_TABLES WHERE TABLE_NAME='XXX_TABLE';
    IF xxx_count = 0 THEN
        dynamic_sql := '
            CREATE TABLE XXX_TABLE(
                id NUMBER PRIMARY KEY,
                name VARCHAR2(10)
            )';
        EXECUTE IMMEDIATE dynamic_sql;
    ELSE
        dynamic_sql := 'TRUNCATE TABLE XXX_TABLE';
        EXECUTE IMMEDIATE dynamic_sql;
    END IF;
    
    dynamic_sql := '
        BEGIN
            FOR i IN 1..10 LOOP
                INSERT INTO XXX_TABLE VALUES(i, ''xxx'' || i);
            END LOOP;
        END;';
    EXECUTE IMMEDIATE dynamic_sql;
    COMMIT;
END;

※XXX_TABLE一定要大寫

※BEGIN~END字串裡最後一定要有「;」

※因為已經是在「'」裡面，所以裡面要用時，只好用跳脫字元，兩個「'」就是一個「'」

※dynamic_sql我本來設100，但出「ORA-06502: PL/SQL: 數字或值錯誤: 字元字串緩衝區太小」這個錯，所以加到200就ok了

※USING

USING只能用在DML，可以依照順序對應到自定的參數

※用法

DECLARE
    dynamic_sql VARCHAR2(200);
    xxx_deptno DEPT.DEPTNO%TYPE := 80;
    xxx_dname DEPT.DNAME%TYPE := 'aaa';
    xxx_loc DEPT.LOC%TYPE := 'bbb';
BEGIN
    dynamic_sql := '
        INSERT INTO DEPT VALUES(:p1, :p2, :p3)
    ';
    EXECUTE IMMEDIATE dynamic_sql USING xxx_deptno, xxx_dname, xxx_loc;
    COMMIT;
END;

※USING xxx_deptno, xxx_dname, xxx_loc，可以直接將值寫在裡面，如
USING 80, 'aaa', 'bbb'，如果有null，要用空，如USING 80, 'aaa', ''，
不能顯示的寫NULL，如USING 80, 'aaa', NULL

※和巢狀表配合使用

DECLARE
    dynamic_sql VARCHAR2(200);
    TYPE dno IS TABLE OF DEPT.DEPTNO%TYPE NOT NULL;
    TYPE dna IS TABLE OF DEPT.DNAME%TYPE NOT NULL;
    xxx_nested dno := dno(10, 20, 30);
    ooo_nested dna := dna('apple', 'banana', 'strawberry');
BEGIN
    dynamic_sql := '
        UPDATE DEPT SET dname = :p1 WHERE deptno = :p2
    ';
    FOR i IN 1..xxx_nested.LAST LOOP
        EXECUTE IMMEDIATE dynamic_sql USING ooo_nested(i), xxx_nested(i);
    END LOOP;
    -- COMMIT;
END;

※COMMIT我先註解掉，不然會蓋掉原有的值

※查詢

DECLARE
    dynamic_sql VARCHAR2(200);
    xxx_eno EMP.EMPNO%TYPE := 7499;
    xxx_emp EMP%ROWTYPE;
BEGIN
    dynamic_sql := '
        SELECT * FROM EMP WHERE EMPNO = :p1
    ';
    EXECUTE IMMEDIATE dynamic_sql INTO xxx_emp USING xxx_eno;
    DBMS_OUTPUT.PUT_LINE(xxx_emp.empno || ' ' || xxx_emp.ename);
END;