列名作为 PL/SQL ORACLE 中的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25182858/
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
Column name as variable in PL/SQL ORACLE
提问by griboedov
I want to have a code where i would declare a column name as variable and then use this variable to retrieve desired column from a certain table.
我想要一个代码，我将在其中将列名声明为变量，然后使用此变量从某个表中检索所需的列。
DECLARE
col_n VARCHAR (100) := 'X' ;
BEGIN
select col_n from my_table;
END;
What is the most easy and explicit way for that in Oracle?
在 Oracle 中，最简单和明确的方法是什么？
回答by Klaus Byskov Pedersen
You can use dynamic sqlto execute a query that you construct as a string. It would go something along these lines:
您可以使用动态 sql来执行您构造为字符串的查询。它将沿着这些方向发展：
DECLARE
col_n VARCHAR (100) := 'X' ;
plsql_block VARCHAR2(500);
BEGIN
plsql_block := 'select ' || col_n || ' from my_table';
EXECUTE IMMEDIATE plsql_block;
END;
回答by neshkeev
You can use dynamic sql:
您可以使用动态sql：
DECLARE
col_n VARCHAR (100) := 'X' ;
l_cursor sys_refcursor;
l_temp number(10); -- won't work if the col_n column has different type
BEGIN
open l_cursor for 'select '|| col_n ||' from my_table';
loop
fetch l_cursor into l_temp;
exit when l_cursor%NOTFOUND;
...
end loop;
END;
The problems is you have to know for sure the type of your column.
问题是您必须确定您的色谱柱类型。
Actually, there is one more way to do it, if you use SQL*Plus environment:
实际上，如果您使用 SQL*Plus 环境，还有一种方法可以做到：
SQL> select &&col_n from employees where &&col_n = 199;
Enter value for col_n: employee_id
old 1: select &&col_n from employees where &&col_n = 199
new 1: select employee_id from employees where employee_id = 199
EMPLOYEE_ID
-----------
199
回答by Rahul
You will have to use dynamic sql/query to achieve what you are trying to do. Something like below using EXECUTE IMMEDIATE. Concept taken from Here.
您将不得不使用动态 sql/query 来实现您想要做的事情。像下面这样使用EXECUTE IMMEDIATE. 从这里采取的概念。
DECLARE col_n VARCHAR(100) := 'X';
DECLARE sql VARCHAR(100);
BEGIN
sql := 'select :p1 from my_table;'
EXECUTE IMMEDIATE sql USING col_n;
END;
Like below for SQL *Plus
像下面的 SQL *Plus
DECLARE col_n VARCHAR(100) := 'X';
DECLARE sql VARCHAR(100);
BEGIN
sql := 'select ' || col_n || ' from my_table;'
EXECUTE IMMEDIATE sql;
END;
