从pl sql中的行类型变量中选择动态列值 [英] select dynamic column value from a rowtype variable in pl sql
问题描述
我需要从rowtype变量获取特定的列值.我的示例代码是
I need to get the specific column value from rowtype variable. My sample code is
declare
col_name varchar2(100);
col_val varchar2(100);
TYPE column_table_type IS TABLE OF COLUMN_TABLE%ROWTYPE;
column_table column_table_type ;
begin
SELECT * BULK COLLECT INTO column_table FROM COLUMN_TABLE;
for data_table in (select * from DATA_TABLE)
loop
for i in column_table.first .. column_table.count
loop
col_name=column_table(i).COLUMN_NAME;
/*
How to get the col_val for col_name from the data_table with out extra query.
*/
end loop;
end loop
end;
/
COLUMN_TABLE:
COLUMN_TABLE:
COLUMN_NAME
----------------
emp_name
emp_id
emp_addr
DATA_TABLE:
DATA_TABLE:
emp_name emp_id emp_addr
----------------------------
A 1 aaaa
B 2 bbbb
C 3 cccc
需要以最佳方式编写这段代码.每当我获得col_name的第一行集合的值为emp_name时,就获取值"A".
这里DATA_TABLE包含许多列,但是我只需要从列表中获取的特定列,并且需要列名和值以进行进一步处理.
Here DATA_TABLE contains so many columns, but i need only specific column which are getting from the column table and need column names and values for further processing.
请帮助我.
推荐答案
您不能通过另一个变量动态引用列/字段名称.
You can't refer to a column/field name dynamically via another variable.
您将需要动态处理整个data_table
查询;这是一个带有 dbms_sql
包的演示:
You will need to handle the whole data_table
query dynamically; this is a demo with the dbms_sql
package:
declare
l_col_name varchar2(100);
l_col_val varchar2(100);
type t_col_tab is table of column_table%rowtype;
l_col_tab t_col_tab;
-- for dbms_sql
l_c pls_integer;
l_col_cnt pls_integer;
l_desc_t dbms_sql.desc_tab;
l_rc pls_integer;
l_varchar varchar2(4000);
begin
select * bulk collect into l_col_tab from column_table;
-- create cursor and prepare from original query
l_c := dbms_sql.open_cursor;
dbms_sql.parse(c=>l_c, statement=>'select * from data_table',
language_flag=>dbms_sql.native);
dbms_sql.describe_columns(c => l_c, col_cnt => l_col_cnt,
desc_t => l_desc_t);
for i in 1..l_col_cnt loop
dbms_sql.define_column(c=>l_c, position=>i,
column=>l_varchar, column_size=>4000);
end loop;
l_rc := dbms_sql.execute(c=>l_c);
while dbms_sql.fetch_rows(c=>l_c) > 0 loop
for i in 1..l_col_cnt loop
for j in 1..l_col_tab.count loop
if l_desc_t(i).col_name = l_col_tab(j).column_name then
-- same column
dbms_sql.column_value(l_c, i, l_varchar);
dbms_output.put_line('Row ' || dbms_sql.last_row_count
|| ': ' || l_desc_t(i).col_name
|| ' = ' || l_varchar);
end if;
end loop;
end loop;
end loop;
dbms_sql.close_cursor(l_c);
end;
/
将解析并执行游标查询,并且表描述允许比较列名.这只是打印出信息,但是显然,您可以根据需要执行任何操作.
The cursor query is parsed and executed, and the table descriptions allow the column names to be compared. This is just printing out the info, but you can do whatever you need with it, obviously.
虚拟表创建为:
create table data_table(id number, column_1 date, column_2 varchar2(10), column_3 varchar2(10));
insert into data_table (id, column_1, column_2, column_3) values (1, date '2017-01-01', 'dummy', 'first');
insert into data_table (id, column_1, column_2, column_3) values (2, date '2017-02-01', 'dummy', 'second');
create table column_table (column_name varchar2(30));
insert into column_table (column_name) values ('ID');
insert into column_table (column_name) values ('COLUMN_3');
...这将得到输出:
... this gets output:
Row 1: ID = 1
Row 1: COLUMN_3 = first
Row 2: ID = 2
Row 2: COLUMN_3 = second
PL/SQL procedure successfully completed.
这篇关于从pl sql中的行类型变量中选择动态列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!