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;
