PL/SQL 动态Sql拼接where条件

来源：互联网发布：淘宝网性用品编辑：程序博客网时间：2024/05/18 18:02

完整例子

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;/

拼接部分

IF NAME_ IS NOT NULL THENSQLSTR := SQLSTR || ' and first_name = :name_ ';END IF;IF SALARY IS NOT NULL THENSQLSTR := SQLSTR || ' and SALARY = :SALARY ';END IF;

如果写成这样，我们在using部分传入了name_和salary两个绑定变量，若name_为空，则剩下salary一个参数，绑定变量过多，会报ora-01006错误。
改进的方法是使用IF-ELSE方式拼接或者用OR连接，但是传入的绑定变量对应情况要根据拼接的sqlstr而定

0 0