oracle 如何使用输入参数在存储过程中构建动态SQL查询？

z0qdvdin 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(162)

我们有一个需求，我们需要根据存储过程接收到的输入来构建SQL查询。为了简单起见，我减少了输入和选择字段的数量。面对下面提到的错误。
你能帮我解决这个错误，让我知道我如何执行这个查询。

CREATE OR REPLACE PROCEDURE MYAPP_AUDIT_GET_RECORDS
AS
BEGIN
    p_requestnumber    VARCHAR2(256);
    p_username         VARCHAR2(256);
    sql_query          VARCHAR2(1000);
BEGIN
    sql_query := 'select message_type, timestamp, request_number, username, useraction, component_name, module_name, process_name, task, version, response_code, response_message, audit_message from myapp_audit where ';

    if p_username is not null and p_requestnumber is not null then
        sql_query := sql_query || 'username = ' :p_username ' and request_number = ' :p_requestnumber;
    elsif p_username is null and p_requestnumber is not null then
        sql_query := sql_query || 'request_number =' :p_requestnumber;
    else
        sql_query := sql_query || 'username = ' :username;
    end if;

    DBMS_OUTPUT.PUT_LINE(sql_query);
END MYAPP_AUDIT_GET_RECORDS;

错误代码：
PLS-00103：当预期以下之一时，遇到符号“VARCHAR 2”：：= .（@ % ;符号“：=”取代“VARCHAR 2”以继续。
PLS-00103：当预期出现以下情况之一时，遇到符号“，”：.（* % & = - + ;< / > at in is mod remainder not rem <an exponent（**）> <> or！= or ~= >= <= <> and or like like 2 like 4 like c between||多重集成员子集

oracle

来源：https://stackoverflow.com/questions/77050417/how-to-build-dynamic-sql-query-inside-stored-procedure-using-input-parameters

1条答案

按热度按时间

ig9co6j11#

在不需要的情况下不要使用动态SQL：

CREATE OR REPLACE PROCEDURE MYAPP_AUDIT_GET_RECORDS(
  o_cur OUT SYS_REFCURSOR
)
AS
  p_requestnumber    VARCHAR2(256);
  p_username         VARCHAR2(256);
BEGIN
  OPEN o_cur FOR
    select message_type,
           timestamp,
           request_number,
           username,
           useraction,
           component_name,
           module_name,
           process_name,
           task,
           version,
           response_code,
           response_message,
           audit_message
    FROM   myapp_audit
    WHERE  ( p_username IS NULL OR username = p_username )
    AND    ( p_requestnumber IS NULL OR request_number = p_requestnumber )
    AND    ( p_username IS NOT NULL OR p_requestnumber IS NOT NULL);
END MYAPP_AUDIT_GET_RECORDS;

如果你确实需要使用动态SQL（在你的例子中没有），那么使用绑定参数，Never使用（未经清理的）字符串串联变量（因为这就是你引入SQL注入漏洞的方式）：

CREATE OR REPLACE PROCEDURE MYAPP_AUDIT_GET_RECORDS
AS
  p_requestnumber    VARCHAR2(256);
  p_username         VARCHAR2(256);
  sql_query          VARCHAR2(1000);
  somevariable       AN_APPROPRIATE_DATATYPE;
BEGIN
  sql_query := 'select message_type, timestamp, request_number, username, useraction, component_name, module_name, process_name, task, version, response_code, response_message, audit_message from myapp_audit where ';

  if p_username is not null and p_requestnumber is not null then
    sql_query := sql_query || 'username = :username and request_number = :requestnumber';
  elsif p_username is null and p_requestnumber is not null then
    sql_query := sql_query || ':username IS NULL AND request_number = :requestnumber';
  else
    sql_query := sql_query || 'username = :username AND :requestnumber IS NULL';
  end if;

  DBMS_OUTPUT.PUT_LINE(sql_query);
  EXECUTE IMMEDIATE sql_query
    BULK COLLECT INTO somevariable
    USING p_username, p_requestnumber;
END MYAPP_AUDIT_GET_RECORDS;

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 如何使用输入参数在存储过程中构建动态SQL查询？

1条答案

相关问题

热门标签

最新问答