Oracle cols_as_rows 比对数据

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;

本文永久更新链接地址：