在sql查询中动态查找表的列名 [英] Dynamically look up column names for a table while in an sql query
本文介绍了在sql查询中动态查找表的列名的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在写SQL(用于Oracle),
I'm writing SQL (for Oracle) like:
INSERT INTO Schema1.tableA SELECT * FROM Schema2.tableA;
其中Schema1.tableA和Schema2.tableA具有相同的列.但是,这似乎是不安全的,因为在SELECT中返回的列的顺序是不确定的.我应该做的是:
where Schema1.tableA and Schema2.tableA have the same columns. However, it seems like this is unsafe, since the order of the columns coming back in the SELECT is undefined. What I should be doing is:
INSERT INTO Schema1.tableA (col1, col2, ... colN)
SELECT (col1, col2, ... colN) FROM Schema2.tableA;
我正在使用一些脚本对很多表进行此操作,所以我想做的是编写如下内容:
I'm doing this for lots of tables using some scripts, so what I'd like to do is write something like:
INSERT INTO Schema1.tableA (foo(Schema1.tableA))
SELECT (foo(Schema1.tableA)) FROM Schema2.tableA;
其中foo是一些漂亮的魔术,它可以从表1中提取列名并以适当的语法打包它们.有想法吗?
Where foo is some nifty magic that extracts the column names from table one and packages them in the appropriate syntax. Thoughts?
推荐答案
此PL/SQL应该这样做:
This PL/SQL should do it:
declare
l_cols long;
l_sql long;
begin
for r in (select column_name from all_tab_columns
where table_name = 'TABLEA'
and owner = 'SCHEMA1'
)
loop
l_cols := l_cols || ',' || r.column_name;
end loop;
-- Remove leading comma
l_cols := substr(l_cols, 2);
l_sql := 'insert into schema1.tableA (' || l_cols || ') select '
|| l_cols || ' from schema2.tableA';
execute immediate l_sql;
end;
/
这篇关于在sql查询中动态查找表的列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文