ORACLE PL/SQL 为每个传递表名到过程 [英] ORACLE PL/SQL for each passing tablename to procedure
问题描述
我需要在过程中执行 FOR EACH 循环,但我需要动态传递表名.
I need to do a FOR EACH loop in a procedure, but I need to pass the table name dynamically.
这是声明
CREATE OR REPLACE PROCEDURE MIGRATE_PRIMITIVES_PROPS
(
FromTable IN VARCHAR2,
ToTable IN VARCHAR2
)
当我尝试这样做时FOR EachRow IN (SELECT * FROM FromTable)
它说表格无效
When I try and do this
FOR EachRow IN (SELECT * FROM FromTable)
It says the table isn't valid
进入程序的表是动态的,列一直在添加和删除,所以我无法拼出列并使用游标来填充它们.
The table coming into the procedure is dynamic, columns are added and deleted all the time so I can't spell out the columns and use a cursor to populate them.
推荐答案
您必须使用动态 SQL 来查询您在编译时不知道名称的表.您可以使用动态游标来做到这一点:
You have to use dynamic SQL to query a table whose name you don't know at compile time. You can do that with a dynamic cursor:
as
l_cursor sys_refcursor;
begin
open l_cursor for 'select * from ' || fromtable;
loop
fetch l_cursor into ...
...但随后它崩溃了,因为您无法定义记录类型以根据弱引用游标获取/a>;并且您不知道您真正感兴趣的列名称或类型 - 您正在使用 select *
并且有特定名称要排除,不包括.您提到了一个可以工作并获取列名的内部循环,但是在该游标中无法引用字段也可以动态变量.
... but then it breaks down because you can't define a record type to fetch into based on a weak ref cursor; and you don't know the column names or types you're actually interested in - you're using select *
and have specific names to exclude, not include. You mentioned an inner loop that works and gets the column names, but there is no way to refer to a field in that cursor variable dynamically either.
所以你必须更加努力地工作并使用 dbms_sql
包,而不是本地动态 SQL.
So you have to work a bit harder and use the dbms_sql
package instead of native dynamic SQL.
这是一个基本版本:
create or replace procedure migrate_primitives_props
(
fromtable in varchar2,
totable in varchar2
)
as
l_cursor pls_integer;
l_desc_tab dbms_sql.desc_tab;
l_columns pls_integer;
l_value varchar2(4000);
l_status pls_integer;
begin
l_cursor := dbms_sql.open_cursor;
-- parse the query using the parameter table name
dbms_sql.parse(l_cursor, 'select * from ' || fromtable, dbms_sql.native);
dbms_sql.describe_columns(l_cursor, l_columns, l_desc_tab);
-- define all of the columns
for i in 1..l_columns loop
dbms_sql.define_column(l_cursor, i, l_value, 4000);
end loop;
-- execute the cursor query
l_status := dbms_sql.execute(l_cursor);
-- loop over the rows in the result set
while (dbms_sql.fetch_rows(l_cursor) > 0) loop
-- loop over the columns in each row
for i in 1..l_columns loop
-- skip the columns you aren't interested in
if l_desc_tab(i).col_name in ('COL_NAME', 'LIB_NAME', 'PARTNAME',
'PRIMITIVE', 'PART_ROW')
then
continue;
end if;
-- get the column value for this row
dbms_sql.column_value(l_cursor, i, l_value);
-- insert the key-value pair for this row
execute immediate 'insert into ' || totable
|| '(key, value) values (:key, :value)'
using l_desc_tab(i).col_name, l_value;
end loop;
end loop;
end;
/
我假设您知道 ToTable 中的列名,但仍然使用动态插入语句,因为该表名未知.(这看起来很奇怪,但是...)
I've assumed you know the column names in your ToTable but still used a dynamic insert statement since that table name is unknown. (Which seems strange, but...)
创建和填充示例表,然后使用它们的名称调用过程:
Creating and populating sample tables, and then calling the procedure with their names:
create table source_table (col_name varchar2(30), lib_name varchar2(30),
partname varchar2(30), primitive number, part_row number,
col1 varchar2(10), col2 number, col3 date);
create table target_table (key varchar2(30), value varchar2(30));
insert into source_table (col_name, lib_name, partname, primitive, part_row,
col1, col2, col3)
values ('A', 'B', 'C', 0, 1, 'Test', 42, sysdate);
exec migrate_primitives_props('source_table', 'target_table');
最终目标表包含:
select * from target_table;
KEY VALUE
------------------------------ ------------------------------
COL1 Test
COL2 42
COL3 2015-05-22 15:29:31
这是基本的,因为它没有清理输入(查找 dbms_assert
包),并且不对不同的数据类型进行任何特殊处理.在我的示例中,我的源表有一个日期列;目标表根据调用会话的 NLS_DATE_FORMAT 设置获取该日期值的字符串表示形式,这并不理想.有一种简单但稍微有点麻烦的方法来获得一致的日期格式,还有一种更好但更复杂的方法;但您可能没有日期值,所以这可能已经足够了.
It's basic because it isn't sanitising the inputs (look up the dbms_assert
package), and isn't doing any special handling for different data types. In my example my source table had a date column; the target table gets a string representation of that date value based on the calling session's NLS_DATE_FORMAT setting, which isn't ideal. There's a simple but slightly hacky way to get a consistent date format, and a better but more complicated way; but you may not have date values so this might be good enough as it is.
这篇关于ORACLE PL/SQL 为每个传递表名到过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!