从输入字符串动态地将其添加到PLSQL查询中

Question

CREATE OR REPLACE PROCEDURE STRING_CHECK
    ( QUERY_COND IN VARCHAR2, RESP_CODE OUT VARCHAR2, RESP_MSG OUT VARCHAR2 )
IS
    EM_NAME VARCHAR2(50);
BEGIN
    SELECT EMAIL INTO EM_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=110 QUERY_COND;
END;

在这个简单的过程中，query_cond具有我想在以下条件之后通过的字符串，如下所示：

DECLARE 
    RESP_CODE VARCHAR2(20) := '000';
    RESP_MSG VARCHAR2(50) := 'SUCCESS';
    QUERY_COND VARCHAR2(100) := 'AND HIRE_DATE BETWEEN ''15-JUN-2003'' AND ''25-MAY-2005''';
BEGIN
    DBMS_OUTPUT.PUT_LINE(QUERY_COND);
    STRING_CHECK(QUERY_COND,RESP_CODE,RESP_MSG);
END;

我只想知道是否可以做到这一点。如果完成，我该如何实现？

Answer 1

您可以使用Dynamic SQL进行操作；例如：

CREATE OR REPLACE PROCEDURE STRING_CHECK(QUERY_COND IN VARCHAR2,RESP_CODE OUT VARCHAR2,RESP_MSG OUT VARCHAR2)
IS
    EM_NAME VARCHAR2(50);
    vSQL    varchar2(1000);
BEGIN
    vSQL := 'SELECT EMAIL FROM EMPLOYEES WHERE EMPLOYEE_ID=110 ' || QUERY_COND;
    --
    execute immediate vSQL into EM_NAME;
END;

DECLARE 
    RESP_CODE VARCHAR2(20):='000';
    RESP_MSG VARCHAR2(50):='SUCCESS';
    QUERY_COND VARCHAR2(100):='AND HIRE_DATE BETWEEN date ''2003-06-15'' AND date ''2005-05-25''';
BEGIN
    DBMS_OUTPUT.PUT_LINE(QUERY_COND);
    STRING_CHECK(QUERY_COND,RESP_CODE,RESP_MSG);
END;

请注意，我更改了您处理日期的方式，以避免不给不给格式的转换。

另外，我不建议这样一种方法来根据某些参数构建查询；使用某些日期参数构建静态查询会更好，避免动态SQL。

例如，类似：

CREATE OR REPLACE PROCEDURE STRING_CHECK_2(dateStart IN     date, 
                                           dateEnd   IN     date,
                                           RESP_CODE    OUT VARCHAR2,
                                           RESP_MSG     OUT VARCHAR2
                                           )
IS
    EM_NAME VARCHAR2(50);
BEGIN
    SELECT EMAIL
    into EM_NAME
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID=110
      and HIRE_DATE BETWEEN dateStart and dateEnd;
    --
END;

当然，这必须进行完善以处理错误，而当两个日期都在输入中给出时，我更希望使用某些布尔逻辑的静态方法，而不是动态逻辑。

Answer 2

通常，您不应使用该方法，因为它可以将代码打开到各种SQL注入漏洞：

DECLARE 
  RESP_CODE VARCHAR2(20):='000';
  RESP_MSG VARCHAR2(50):='SUCCESS';
  QUERY_COND VARCHAR2(100):=' AND EXISTS( SELECT 1 FROM your_password_table WHERE user_id = ''007'') --';
BEGIN
    DBMS_OUTPUT.PUT_LINE(QUERY_COND);
    STRING_CHECK(QUERY_COND,RESP_CODE,RESP_MSG);
END;

如果007的user_id的表中有密码，将返回结果(几乎可以肯定不是您希望用户能够使用此查询来执行的操作(。

相反，您应该知道要过滤并接受哪些参数：

CREATE OR REPLACE PROCEDURE STRING_CHECK(
  date_start IN  EMPLOYEES.HIRE_DATE%TYPE,
  date_end   IN  EMPLOYEES.HIRE_DATE%TYPE,
  RESP_CODE  OUT VARCHAR2,
  RESP_MSG   OUT VARCHAR2
)
IS
  EM_NAME VARCHAR2(50);
BEGIN
  SELECT EMAIL
  INTO   EM_NAME
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID = 110
  AND    ( date_start IS NULL OR date_start <= hire_date )
  AND    ( date_end   IS NULL OR hire_date  <= date_end  );
  -- generate responses
END;
/