Oraclecols_as_rows比对数据

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oraclecols_as_rows比对数据，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3827字，纯文字阅读大概需要6分钟。

内容图文

AskTom提供的脚本,用于比对数据. create or replace type myscalartype as object ( rnum number, cname varchar2(30), val varc

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;

本文永久更新链接地址：

，

内容总结

以上是互联网集市为您收集整理的Oraclecols_as_rows比对数据全部内容，希望文章能够帮你解决Oraclecols_as_rows比对数据所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---