在 SYS_REFCURSOR 中执行动态 sql 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2230428/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Executing a dynamic sql statement into a SYS_REFCURSOR
提问by user250643
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
是否可以在 plsql 中执行动态 sql 并将结果返回到 sys_refcursor 中？到目前为止，我已经粘贴了我的尝试，但无法正常工作，这是我通过我的 Java 应用程序遇到的错误
ORA-01006: bind variable does not exist ORA-06512: at "LIVEFIS.ERC_REPORT_PK", line 116 ORA-06512: at line 1
ORA-01006：绑定变量不存在 ORA-06512：在“LIVEFIS.ERC_REPORT_PK”，第 116 行 ORA-06512：在第 1 行
but that could be somthing misconstrued by java, everything seams to compile fine soo im not sure.
但这可能会被java误解，一切都可以很好地编译，所以我不确定。
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;
Im new to plsql and even newer to dynamic sql soo any help/ suggestions would be greatly apreciated.
我是 plsql 的新手，甚至是动态 sql 的新手，所以任何帮助/建议都将不胜感激。
Thanks Again
再次感谢
Jon
乔恩
回答by Vincent Malgrat
you will have to bind the parameters pAgeand pPostcode. In dynamic SQL you would prefix them with a colon (:). If you use EXECUTE IMMEDIATEor OPEN ... FOR, you will bind your parameters via position, this is why I renamed them :P1 and :P2 in the example:
您将必须绑定参数pAge和pPostcode。在动态 SQL 中，您可以用冒号 ( :)作为前缀。如果您使用EXECUTE IMMEDIATEor OPEN ... FOR，您将通过位置绑定您的参数，这就是我在示例中将它们重命名为 :P1 和 :P2 的原因：
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;
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.
注意：绑定变量的数量和位置必须在编译时知道，这就是为什么我经常使用上面的构造（即使不使用参数也将其添加到其位置）。向AND :P1 IS NULL查询添加重言式（如）不会影响其解释计划。
回答by RC.
You cannot assign a refcursor through the use of execute immediate.
您不能通过使用立即执行来分配引用。
You'll have to build the SQL into a string and then use open.
您必须将 SQL 构建为字符串，然后使用 open。
sql_str := 'SELECT * FROM...';
open pReport for sql_str;
回答by APC
Use the OPEN FOR syntax and bind variables.
使用 OPEN FOR 语法和绑定变量。
procedure all_carers_param_dy (pPostcode in carer.postcode%type, pAge Number
,pReport out SYS_REFCURSOR)
is
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(''%''||:1||''%'')';
open pReport for lsql using pPostcode;
elsif pPostcode is null and pAge > 0 then
lsql := lsql||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :1';
open pReport for lsql using pAge;
elsif pPostcode is not null and pAge > 0 then
lsql := lsql ||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :1'
||' AND c.postcode like upper(''%''||:2||''%'')';
open pReport for lsql using pAge, pPostcode;
end if;
end all_carers_param_dy;
/
Dynamic SQL is hard, hard to understand and hard to get right. One of the tricky areas is handling repetition. It is a good idea to declare repeating sections of bolierplate as constants. Also, note that we can split large strings over several lines without having to concatenate them with '||'. This reduces the maintenance overhead.
动态 SQL 很难，很难理解，也很难做对。棘手的领域之一是处理重复。将样板的重复部分声明为常量是一个好主意。另请注意，我们可以将大字符串拆分为多行，而无需将它们与'||'. 这减少了维护开销。
create or replace procedure all_carers_param_dy
(pPostcode in carer.postcode%type
, pAge Number
, pReport out SYS_REFCURSOR)
is
lsql varchar2(500) ;
root_string constant 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';
pc_string constant varchar2(256) :=
' AND c.postcode like upper(''%''||:pc||''%'')';
age_string constant varchar2(256) :=
' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :age';
begin
if pPostcode is not null and pAge <= 0 then
lsql := root_string || pc_string;
open pReport for lsql using pPostcode;
elsif pPostcode is null and pAge > 0 then
lsql := root_string || age_string;
open pReport for lsql using pAge;
elsif pPostcode is not null and pAge > 0 then
lsql := root_string || age_string
|| pc_string;
open pReport for lsql using pAge, pPostcode;
end if;
end all_carers_param_dy;
/
回答by t v
yes it's possible. Do like this:
是的，这是可能的。这样做：
v_sql := 'BEGIN OPEN :1 FOR :2 USING ';
v_bindvars := pPostcode ||', '||pAge; --this part you can create dynamically base on your if's
v_sql := v_sql||v_bindvars||' ; END;';
v_select := 'select yourdata from dual where 1 = :bind_first_var and 2 = :bind_second_var';
execute immediate v_sql using pReport, v_select;
