问题描述
是否可以在 plsql 中执行动态 sql 并将结果返回到 sys_refcursor 中?到目前为止,我已经粘贴了我的尝试,但无法正常工作,这是我通过 Java 应用程序时出现的错误
is it possible to execute a dynamic piece of sql within plsql and return the results into a sys_refcursor? I have pasted my attempt soo far, but dosnt seam to be working, this is the error im getting throught my java app
ORA-01006:绑定变量没有存在 ORA-06512: 在LIVEFIS.ERC_REPORT_PK",第 116 行ORA-06512:在第 1 行
ORA-01006: bind variable does not exist ORA-06512: at "LIVEFIS.ERC_REPORT_PK", line 116 ORA-06512: at line 1
但这可能会被java误解,一切都可以很好地编译,所以我不确定.
but that could be somthing misconstrued by java, everything seams to compile fine soo im not sure.
procedure all_carers_param_dy (pPostcode in carer.postcode%type, pAge Number
,pReport out SYS_REFCURSOR) is
begin
declare
lsql varchar2(500) :='SELECT c.id FROM carer c, cared_for cf,carer_cared_for ccf '
||' where c.id = ccf.carer_id (+)'
||' AND cf.id (+) = ccf.cared_for_id';
begin
if pPostcode is not null and pAge <= 0 then
lsql := lsql||' AND c.postcode like ''%''|| upper(pPostcode)||''%''';
elsif pPostcode is null and pAge > 0 then
lsql := lsql||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = pAge';
elsif pPostcode is not null and pAge > 0 then
lsql := lsql ||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = pAge'
||' AND c.postcode like ''%''|| upper(pPostcode)||''%''';
end if;
execute immediate lsql
into pReport;
end;
end;
我是 plsql 的新手,甚至是动态 sql 的新手,所以任何帮助/建议都将不胜感激.
Im new to plsql and even newer to dynamic sql soo any help/ suggestions would be greatly apreciated.
再次感谢
乔恩
推荐答案
你必须绑定参数 pAge
和 pPostcode
.在动态 SQL 中,您可以用冒号 (:
) 作为前缀.如果您使用 EXECUTE IMMEDIATE
或 OPEN ... FOR
,您将通过位置绑定参数,这就是我在示例中将它们重命名为 :P1 和 :P2 的原因:
you will have to bind the parameters pAge
and pPostcode
. In dynamic SQL you would prefix them with a colon (:
). If you use EXECUTE IMMEDIATE
or OPEN ... FOR
, you will bind your parameters via position, this is why I renamed them :P1 and :P2 in the example:
DECLARE
lsql VARCHAR2(500) := 'SELECT c.id
FROM carer c, cared_for cf, carer_cared_for ccf
WHERE c.id = ccf.carer_id (+)
AND cf.id (+) = ccf.cared_for_id';
BEGIN
IF pPostcode IS NULL THEN
lsql := lsql || ' AND :P1 IS NULL';
ELSE
lsql := lsql || ' AND c.postcode like ''%''|| upper(:P1)||''%''';
IF pPostcode pAge > 0 THEN
lsql := lsql || ' AND :P2 = ROUND((MONTHS_BETWEEN(sysdate,
c.date_of_birth)/12))';
ELSE
lsql := lsql || ' AND nvl(:P2, -1) <= 0';
END IF;
OPEN pReport FOR lsql USING pPostcode, pAge;
END;
注意:绑定变量的数量和位置必须在编译时知道,这就是为什么我经常使用上面的构造(即使不使用也将参数添加到其位置).向查询添加重言式(如 AND :P1 IS NULL
)不会影响其解释计划.
Note: The number and position of bind variables has to be known at compile time, this is why I often use the construct above (adding the parameter to its position even if it is not used). Adding a tautology (as in AND :P1 IS NULL
) to a query won't affect its explain plan.
这篇关于在 SYS_REFCURSOR 中执行动态 sql 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!