Oracle:带IN子句的参数化查询返回空值 [英] Oracle: Paramterized Query with IN clause return null value

问题描述

带IN子句的参数化查询不起作用；它无法替换值

The Paramterized Query with IN clause is not working; it not able to replace the value

PROCEDURE p_getdata(A IN LONG,B IN LONG,C IN LONG, cur OUT c_data)
AS
l_query LONG;

   BEGIN
 IF C IS NULL THEN
open cur for  'select firstname, lastname, streetname, city
               from mytable 
               where zip IN(:A) AND streetnumber IN(:B) AND apt_num in(:C)' using A, B,C;

 END IF;
END;

这里

       a ='202020';
        b='12','13','10','92','02','02'
        c='A','B'

在Db中，所有数据类型均为varchar2.如何使它运行并存储游标的值?

In Db all datatype is varchar2. How to make it run and store the value is cursor?

推荐答案

您可以将逗号分隔的列表作为参数(绑定变量)传​​递，但是您需要在子查询中对其进行解析.基于此线程的解决方案使用regexp_substr.

You may pass the comma separated list as a parameter (bind variable), but you are responsible for parsing it in a subquery. A solution based on this thread uses regexp_substr.

CREATE or REPLACE PROCEDURE p_getdata(A IN VARCHaR2, cur OUT sys_refcursor)
AS
BEGIN
open cur for  'with t1 as (select :A col from dual),
     t2 as (select level lvl,to_number(regexp_substr(col,''[^,]+'', 1, level)) col 
              from t1 connect by regexp_substr(col, ''[^,]+'', 1, level) is not null)
select col as id from t2' using A;
END;
/

该过程被简化，但是应该给人一种使用它的感觉.

The procedure is simplified, but should provide a feeling how to use it.

使用动态SQL(字符串连接)的最大好处是，您不必在每次执行时都解析该语句.更不用说安全性(担心SQL注入).

The big advantage against the use of dynamic SQL (concatenation of strings) is that you need not parse the statement on each execution. Not to mention the security (scare of SQL injection).

用法:

DECLARE 
  l_cur SYS_REFCURSOR;
  l_id NUMBER;
BEGIN 
  p_getdata('1,1000,282828,4',l_cur);
 LOOP
    FETCH l_cur INTO l_id ;
    EXIT WHEN l_cur%NOTFOUND;
    dbms_output.put_line(l_id);
 END LOOP;
END;
/


1
1000
282828
4

更新

简化了上述过程，为了获得功能，您应该在CURSOR中使用这样的查询(即，首先使用子查询因子分解将所有三个参数拆分到单独的子查询中，然后在查询中应用结果)

The above procedure is simplified, to get your functionality you should use a query like this in the CURSOR (i.e. first split all three parameters in separate subqueries using subquery factoring, than apply the results in your query)

CREATE or REPLACE PROCEDURE p_getdata(A IN VARCHAR2, B in VARCHAR2, c in VARCHAR2, cur OUT sys_refcursor)
AS
BEGIN
open cur for  'with ta1 as (select :A col from dual),
     ta2 as (select level lvl,to_number(regexp_substr(col,''[^,]+'', 1, level)) col 
              from ta1 connect by regexp_substr(col, ''[^,]+'', 1, level) is not null),
 tb1 as (select :B col from dual),
     tb2 as (select level lvl,to_number(regexp_substr(col,''[^,]+'', 1, level)) col 
              from tb1 connect by regexp_substr(col, ''[^,]+'', 1, level) is not null),
 tc1 as (select :C col from dual),
     tc2 as (select level lvl,to_number(regexp_substr(col,''[^,]+'', 1, level)) col 
              from tc1 connect by regexp_substr(col, ''[^,]+'', 1, level) is not null)              
select firstname, lastname, streetname, city
from mytable 
where zip IN (select col from ta2) AND 
      streetnumber IN (select col from tb2) AND 
      apt_num in (select col from tc2)' using A, B, C;
END;
/

测试通过

DECLARE 
  l_cur SYS_REFCURSOR;
  l_firstname VARCHAR2(20);
  l_lastname VARCHAR2(20);
  l_streetname VARCHAR2(20);
  l_city VARCHAR2(20);
BEGIN 
  p_getdata('1100,,1200','1,2','11,12' ,l_cur);
 LOOP
    FETCH l_cur INTO l_firstname, l_lastname, l_streetname, l_city;
    EXIT WHEN l_cur%NOTFOUND;
    dbms_output.put_line(l_firstname|| ' ' || l_lastname || ' ' || l_streetname  || ' ' || l_city);
 END LOOP;
END;
/

这篇关于Oracle:带IN子句的参数化查询返回空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！