Oracle cols_as_rows 比對資料

壹頁書發表於2015-04-28
Oracle
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;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1609294/，如需轉載，請註明出處，否則將追究法律責任。

相關文章