在 Oracle 中执行立即选择语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24990913/
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
Execute immediate select statement in Oracle
提问by user3869208
I am using execute immediate statement in one of my queries.
我在我的一个查询中使用了执行立即语句。
procedure p1 (p_pk1_column, p_pk2_column , p_conv_table_name ,p_MODUE_NAME )
is
v_select_string := 'SELECT'''||p_MODUE_NAME||''',''' ||p_pk1_column || ''',''' || p_pk2_column ||''' FROM ' ||p_conv_table_name || v_where_condition;
execute immediate v_select_string ;
dbms_output.put_line('string:'||v_select_string );
end p1;
Here I am calling p1 procedure in another procedure p2
这里我在另一个过程 p2 中调用 p1 过程
PROCEDURE P2 IS
v_pk1_column:='a';
v_pk2_columnm:='b';
v_mod_name:='mOD1';
p1(v_pk1_column,v_pk2_columnm);
end p2;
/
In p2 procedure a, b are the column names of p_conv_table_name . I want to execute the select statement like select p_mod_name, a, b from p_conv_table_name where condition; so that it should give values for a and b columns in p_conv_table_name .
在 p2 过程 a 中，b 是 p_conv_table_name 的列名。我想执行 select p_mod_name, a, b from p_conv_table_name where condition 之类的 select 语句；以便它应该为 p_conv_table_name 中的 a 和 b 列提供值。
But it is executing like select p_mod, p_pk1_col,p_pk2_col from p_conv_table_name where condition;
So simply column names are selecting instead of values in that column.
但它的执行就像 select p_mod, p_pk1_col,p_pk2_col from p_conv_table_name where condition;
所以简单地选择列名而不是该列中的值。
Please suggest some approach to achieve values in that column.
请提出一些方法来实现该列中的值。
Thanks in advance
提前致谢
回答by Bob Jarvis - Reinstate Monica
When the SELECTstatement is built the column names are surrounded in single-quotes, which turns them into string literals. Change your procedure to something like:
当SELECT声明是建立在列名在单引号引起来，接着转成字符串文字。将您的程序更改为：
CREATE OR REPLACE PROCEDURE P1 (p_pk1_column IN VARCHAR2,
p_pk2_column IN VARCHAR2,
p_conv_table_name IN VARCHAR2,
p_MODUE_NAME IN VARCHAR2)
IS
v_select_string VARCHAR2(2000);
v_where_condition VARCHAR2(2000) := ' WHERE SOMETHING = SOMETHING_ELSE';
csr SYS_REFCURSOR;
v_val_1 VARCHAR2(2000);
v_val_2 VARCHAR2(2000);
v_mod_name VARCHAR2(2000);
BEGIN
v_select_string := 'SELECT ' || p_MODUE_NAME || ',' ||
p_pk1_column || ',' ||
p_pk2_column ||
' FROM ' || p_conv_table_name ||
v_where_condition;
dbms_output.put_line('string:' || v_select_string);
OPEN csr FOR v_select_string;
LOOP
FETCH csr INTO v_mod_name, v_val_1, v_val_2;
EXIT WHEN csr%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('v_mod_name=''' || v_mod_name || ''' ' ||
'v_val_1=''' || v_val_1 || ''' ' ||
'v_val_2=''' || v_val_2 || '''');
END LOOP;
CLOSE csr;
END P1;
I've also changed the code to OPENand FETCHa cursor rather than using EXECUTE IMMEDIATE. OPENand FETCHare generally more appropriate for use with a dynamic SELECTstatement.
我还将代码更改为OPEN和FETCH游标，而不是使用EXECUTE IMMEDIATE. OPEN并且FETCH通常更适合与动态SELECT语句一起使用。
Share and enjoy.
分享和享受。
