PL/SQL 动态SQL

• EXECUTE IMMEDIATE sqlstr语句规则

  1. 若sqlstr是一个普通的select，则使用
     EXECUTE IMMEDIATE sqlstr INTO v USING v1, v2...
     或者
     EXECUTE IMMEDIATE sqlstr BULK COLLECT INTO v USING v1, v2...

  2. 若sqlstr是一个匿名的plsql语句，则使用
     EXECUTE IMMEDIATE sqlstr USING OUT v, v1, v2...

• PL/SQL 动态Sql拼接where条件

DECLARE

    SQLSTR VARCHAR(200) := 'SELECT * FROM hr.employees where 1=1';

    TYPE EMPCURTYP IS REF CURSOR;
    V_EMP_CURSOR EMPCURTYP;
    EMP_RECORD   HR.EMPLOYEES%ROWTYPE;

    TYPE EMP_REC IS TABLE OF HR.EMPLOYEES%ROWTYPE;
    EMP_REC2 EMP_REC;

    PROCEDURE QUERYEMP(NAME_ VARCHAR, SALARY NUMBER) IS
    BEGIN
        -- Wrong exmaple
        --IF NAME_ IS NOT NULL THEN
        --   SQLSTR := SQLSTR || ' and first_name = :name_ ';
        --END IF;
        --IF SALARY IS NOT NULL THEN
        --    SQLSTR := SQLSTR || ' and SALARY = :SALARY ';
        --END IF;

        -- By using IF-END, Not recommended. Because you have to write extra if-else conditions
        --IF NAME_ IS NOT NULL THEN
        --    SQLSTR := SQLSTR || ' and first_name = :name_ ';
        --ELSE
        --    SQLSTR := SQLSTR || ' and :name_ IS NULL';
        --END IF;

        -- By using OR, recommended way
        SQLSTR := SQLSTR || ' and (:name_ IS NULL OR first_name = :name_) ';
        SQLSTR := SQLSTR || ' and (:salary IS NULL OR salary = :salary) ';

        -- print sqlstr
        DBMS_OUTPUT.PUT_LINE('sqlstr = ' || SQLSTR);

        -- If the dynamic SQL statement is a SELECT statement that returns multiple rows, 
        -- native dynamic SQL gives you these choices: 

        -- 1.Use the EXECUTE IMMEDIATE statement with the BULK COLLECT INTO clause.
        EXECUTE IMMEDIATE SQLSTR BULK COLLECT
            INTO EMP_REC2
            USING NAME_, NAME_, SALARY, SALARY;
        FOR I IN 1 .. EMP_REC2.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID = ' || EMP_REC2(I)
                                 .EMPLOYEE_ID);
        END LOOP;
        -- 1.END

        -- 2.Use the OPEN FOR, FETCH, and CLOSE statements
        OPEN V_EMP_CURSOR FOR SQLSTR
            USING NAME_, NAME_, SALARY, SALARY;

        LOOP
            FETCH V_EMP_CURSOR
                INTO EMP_RECORD;
            EXIT WHEN V_EMP_CURSOR%NOTFOUND;

            DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID = ' ||
                                 EMP_RECORD.EMPLOYEE_ID);
        END LOOP;

        CLOSE V_EMP_CURSOR;
        -- 2.END

    END QUERYEMP;


BEGIN

    QUERYEMP('Steven', NULL);
    -- 
    --TT('');
    -- QUERYEMP2('Steven');
END;
/