Oracle cols_as_rows 比对数据

AskTom提供的脚本,用于比对数据.create or replace type myscalartype as object
（ rnum number, cname varchar2（30）, val varchar2（4000））
/
create or replace type mytabletype as table of myscalartype
/
create or replace
function cols_as_rows（ p_query in varchar2 ） return mytabletype
-- This function is designed to be installed ONCE per database, and
-- it is nice to have ROLES active for the dynamic sql, hence the
-- AUTHID CURRENT_USER.
authid current_user
-- This function is a pipelined function, meaning that it"ll send
-- rows back to the client before getting the last row itself.
-- In 8i, we cannot do this.
pipelined
as
l_thecursor integer default dbms_sql.open_cursor;
l_columnvalue varchar2（4000）;
l_status integer;
l_colcnt number default 0;
l_desctbl dbms_sql.desc_tab;
l_rnum number := 1;
begin
-- Parse, describe and define the query. Note, unlike print_table,
-- I am not altering the session in this routine. The
-- caller would use to_char（） on dates to format and if they
-- want, they would set cursor_sharing. This routine would
-- be called rather infrequently. I did not see the need
-- to set cursor sharing therefore.
dbms_sql.parse（ l_thecursor, p_query, dbms_sql.native ）;
dbms_sql.describe_columns（ l_thecursor, l_colcnt, l_desctbl ）;
for i in 1 .. l_colcnt loop
dbms_sql.define_column（ l_thecursor, i, l_columnvalue, 4000 ）;
end loop;
-- Now, execute the query and fetch the rows. iterate over
-- the columns and "pipe" each column out as a separate row
-- in the loop. Increment the row counter after each
-- dbms_sql row.
l_status := dbms_sql.execute（l_thecursor）;
while （ dbms_sql.fetch_rows（l_thecursor） > 0 ）
loop
for i in 1 .. l_colcnt
loop
dbms_sql.column_value（ l_thecursor, i, l_columnvalue ）;
pipe row
（myscalartype（ l_rnum, l_desctbl（i）.col_name, l_columnvalue ））;
end loop;
l_rnum := l_rnum+1;
end loop;
-- Clean up and return...
dbms_sql.close_cursor（l_thecursor）;
return;
end cols_as_rows;
/
create or replace function
cols_as_rows8i（ p_query in varchar2 ） return mytabletype
authid current_user
as
l_thecursor integer default dbms_sql.open_cursor;
l_columnvalue varchar2（4000）;
l_status integer;
l_colcnt number default 0;
l_desctbl dbms_sql.desc_tab;
l_data mytabletype := mytabletype（）;
l_rnum number := 1;
begin
dbms_sql.parse（ l_thecursor, p_query, dbms_sql.native ）;
dbms_sql.describe_columns（ l_thecursor, l_colcnt, l_desctbl ）;
for i in 1 .. l_colcnt loop
dbms_sql.define_column（ l_thecursor, i, l_columnvalue, 4000 ）;
end loop;
l_status := dbms_sql.execute（l_thecursor）;
while （ dbms_sql.fetch_rows（l_thecursor） > 0 ）
loop
for i in 1 .. l_colcnt
loop
dbms_sql.column_value（ l_thecursor, i, l_columnvalue ）;
l_data.extend;
l_data（l_data.count） :=
myscalartype（ l_rnum, l_desctbl（i）.col_name, l_columnvalue ）;
end loop;
l_rnum := l_rnum+1;
end loop;
dbms_sql.close_cursor（l_thecursor）;
return l_data;
end cols_as_rows8i;
/
以HR表为例,比对员工编号200和201的员工数据
column val format a20;
select a.cname,a.val,b.val from
table（cols_as_rows（"select * from hr.employees where employee_id=200"）） a,
table（cols_as_rows（"select * from hr.employees where employee_id=201"）） b
where a.cname=b.cname and （a.val is not null or b.val is not null）
order by a.cname;本文永久更新链接地址