使用Oracle创建用于搜索的动态查询 [英] Create dynamic query for search using Oracle

问题描述

我需要使用基于下拉列表的过滤器来搜索Oracle Database.因此，总共有3个下拉列表和一个textbox用于搜索参数.现在我想要的是.

I need to search in my Oracle Database using filters based on the dropdown's. So there are total 3 dropdowns and one textbox for searching parameters. Now what I want is.

如果用户从第一个下拉列表中选择APPLICATION，从第二个下拉列表中选择Project Name，从第三个下拉列表中选择LIKE条件.应该创建一个动态查询，并在此基础上从数据库中过滤出记录.为此，我在下面尝试了

If User Selects APPLICATION from the first dropdown and Project Name from second dropdown and LIKE condition from third dropdown. A dynamic query should be created and based on that it should filter the record from the database. For that What I tried is below

PROCEDURE FILTER_SEARCH_DATA
 (
 P_SEARCH_TYPE IN NVARCHAR2,
 P_PARAM_TYPE IN NVARCHAR2,
 P_OPERATOR IN NVARCHAR2,
 P_TEXTVAL IN NVARCHAR2,
 P_RETURN OUT SYS_REFCURSOR 
 )

AS
STR NVARCHAR2(400):='';
STROP NVARCHAR2(400):='';
STREX NVARCHAR2(4000):='';
BEGIN

  IF(P_OPERATOR ='LIKE') THEN
  BEGIN
   STR:=STR || '  WHERE  AM.APPLICATIONNAME ' || P_OPERATOR || '''%' || P_TEXTVAL ||'%''';
  END;
  ELSE
  BEGIN
      STR:=STR || 'WHERE AM.APPLICATIONNAME ' || P_OPERATOR  || P_TEXTVAL ;
  END;

  END IF;

  DBMS_OUTPUT.PUT_LINE('STR'|| STR);
 IF P_SEARCH_TYPE = 'APPLICATION' THEN
 DBMS_OUTPUT.PUT_LINE('START APPLICATION');
 STREX:='OPEN P_RETURN FOR SELECT AM.APPLICATIONNAME, AM.PROJECTNO, AM.VSS_FOLDER_LOC FROM APPLICATION_MASTER AM 
INNER JOIN APPLICATION_DETAILS AD
ON AM.APP_MST_ID = AD.APP_MST_ID'   || str ||';';

  DBMS_OUTPUT.PUT_LINE('STREX '|| STREX);
END IF;

 END FILTER_SEARCH_DATA;

但是它并不能满足我的需求.

But it is not working accordingly for what I want.

如果您有任何与此相关的问题，请告诉我.

Let me know if you have any issues related to this.

推荐答案

您的问题在这里:

STREX:='OPEN P_RETURN FOR SELECT AM.APPLICATIONNAME, AM.PROJECTNO, AM.VSS_FOLDER_LOC FROM APPLICATION_MASTER AM 
INNER JOIN APPLICATION_DETAILS AD
ON AM.APP_MST_ID = AD.APP_MST_ID'   || str ||';';

  DBMS_OUTPUT.PUT_LINE('STREX '|| STREX);

此代码仅将字符串输出到标准输出中.您需要使用此:

This code just outputs a string into the standard output. You need to use this:

PROCEDURE FILTER_SEARCH_DATA
 (P_SEARCH_TYPE IN NVARCHAR2,
  P_PARAM_TYPE IN NVARCHAR2,
  P_OPERATOR IN NVARCHAR2,
  P_TEXTVAL IN NVARCHAR2,
  P_RETURN OUT SYS_REFCURSOR ) AS

  STR NVARCHAR2(400):='';
  STROP NVARCHAR2(400):='';
  STREX VARCHAR2(4000):='';
  val NVARCHAR2(4000);
BEGIN
  IF (P_OPERATOR ='LIKE') THEN
      val := '%' || P_TEXTVAL ||'%';
  ELSE
      val := P_TEXTVAL;
  END IF;

  DBMS_OUTPUT.PUT_LINE('STR'|| STR);
  IF P_SEARCH_TYPE = 'APPLICATION' THEN
     DBMS_OUTPUT.PUT_LINE('START APPLICATION');
     STREX:='SELECT AM.APPLICATIONNAME, AM.PROJECTNO, AM.VSS_FOLDER_LOC 
               FROM APPLICATION_MASTER AM 
                    INNER JOIN APPLICATION_DETAILS AD
                       ON AM.APP_MST_ID = AD.APP_MST_ID 
              WHERE AM.APPLICATIONNAME ' || P_OPERATOR  || ' :PARAM';

     DBMS_OUTPUT.PUT_LINE('STREX '|| STREX);
     open P_RETURN for STREX using val;
END IF;

END FILTER_SEARCH_DATA;

此外，我添加了使用参数(SQL代码中的:PARAM)，它有助于提高性能并避免SQL注入.

Also, I added using of parameters (:PARAM in the SQL code), it helps with performance and allows to avoid SQL injections.

另一件事:在代码中，仅当P_SEARCH_TYPE等于'APPLICATION'时，才打开游标.在其他情况下，您需要执行某些操作，否则您的过程将返回关闭的游标.

Another one important thing: in your code you open the cursor only if P_SEARCH_TYPE equals to 'APPLICATION'. You need to do something in other cases, or your procedure will return closed cursor.

UPD
STREX应该是VARCHAR2，而不是NVARCHAR2.

UPD
STREX should be VARCHAR2, not NVARCHAR2.

这篇关于使用Oracle创建用于搜索的动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！