动态SQL表名称作为变量 [英] Dynamic SQL table name as variable
本文介绍了动态SQL表名称作为变量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这个正在运行的过程,调用过程可以传递2个参数,并执行选择查询.
I have this procedure which is working, 2 parameters can be passed when calling procedure and it executes the select query.
create or replace procedure dynamic_sql
(input1 varchar2, input2 varchar2)
as begin
execute immediate
'select :variable1, :variable2 from emp'
using input1,input2;
end;
/
exec dynamic_sql('ename','job');
以相同的方式,我尝试添加第三个变量,它将替换表Emp,但它不起作用,传入的表名是100%正确的.这是无效的代码(ORA-00903:无效的表名):
In the same way I try to add third variable which will replace the table Emp, but it doesn't work, passed in table name is 100% correct. This is the code that doesn't work (ORA-00903: invalid table name):
create or replace procedure dynamic_sql
(input1 varchar2, input2 varchar2,input_table varchar2)
as begin
execute immediate
'select :variable1, :variable2 from :variable3'
using input1,input2,input_table;
end;
/
exec dynamic_sql('ename','job','emp');
推荐答案
尝试如下操作:这是由于在执行之前先解析了表名.
Try something like this: Its due to the parsing of the table name before execution.
create or replace procedure dynamic_sql
(input1 varchar2, input2 varchar2,input_table varchar2)
as
str varchar2(1000) := NUll;
begin
str := 'select '||input1||','|| input2 ||' from '||input_table;
execute immediate str;
end;
/
exec dynamic_sql('ename','job','emp');
Procedure created.
PL/SQL procedure successfully completed.
这篇关于动态SQL表名称作为变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文