gpt4 book ai didi

mysql - Oracle:带有 IN 子句的参数化查询返回空值

转载 作者:行者123 更新时间:2023-11-29 04:38:48 24 4
gpt4 key购买 nike

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

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。如何让它运行并存储值是游标?

最佳答案

您可以将逗号分隔列表作为参数(绑定(bind)变量)传​​递,但您负责在子查询中解析它。基于 this thread 的解决方案使用 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;
/

程序简化了,但应该提供一个如何使用它的感觉。

相对于使用动态 SQL(字符串连接)的最大优势是您无需在每次执行时都解析语句。更不用说安全性(对 SQL 注入(inject)的恐惧)。

用法:

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 中使用这样的查询(即首先使用子查询分解将所有三个参数拆分到单独的子查询中,然后在你的查询中应用结果)

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

关于mysql - Oracle:带有 IN 子句的参数化查询返回空值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34497184/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com