3,490
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
function row_to_col_func(when_value_null in varchar2 default null
)return sys_refcursor
as
sqlstr varchar2(2000):='select distinct prsqdh,wzid,wzmc,wzggxh,wzcz,wzjldw,prdpzsl,prbmmc,prsqr ';
v1 varchar2(100);
dh varchar2(200);
cur sys_refcursor;
type prsqdh_collect is table of view_rowtocol.prsqdh%type index by binary_integer;
prsqdh_collection prsqdh_collect;
type qzry_collect is table of view_rowtocol.qzry%type index by binary_integer;
qzry_collection qzry_collect;
begin
select distinct prsqdh bulk collect into prsqdh_collection from view_rowtocol order by prsqdh;
for i in 1..prsqdh_collection.count loop
dh:=prsqdh_collection(i);
select distinct qzry bulk collect into qzry_collection from view_rowtocol where prsqdh=''||dh||'';
for j in 1..qzry_collection.count loop
v1:=qzry_collection(j);
sqlstr:=sqlstr||chr(10)||','||'max(decode(to_char(qzry),'''||v1||''',qzry,'''||v1||''')) 审批人';
end loop;
open cur for sqlstr||' from view_rowtocol where prsqdh='||dh||' group by --这里有问题prsqdh,qzry,wzid,wzmc,wzggxh,wzcz,wzjldw,prdpzsl,prbmmc,prsqr order by prsqdh' ;
--准备在这里存入临时表(临时表名temp_CGRequest)
--cur游标,返回的结果集
sqlstr :='select distinct prsqdh,wzid,wzmc,wzggxh,wzcz,wzjldw,prdpzsl,prbmmc,prsqr ';
end loop ;
return cur;
end row_to_col_func;
END pkg_test;
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production
SQL> set serveroutput on
SQL> set linesize 30000
SQL> select * from tab;
DH HQR YJ
------ ------ ------
001 A a
001 B b
001 C c
002 A a
002 B b
002 C c
6 rows selected.
SQL> edit
Wrote file afiedt.buf
1 create or replace procedure row_to_col_func(cur out sys_refcursor)
2 as
3 sqlstr varchar2(2000):='select dh';
4 begin
5 for rs in (select distinct hqr,yj from tab ) loop
6 sqlstr:=sqlstr||chr(10)||','||'max(decode(to_char(hqr),'''||rs.hqr||''',hqr,'''')) 审批人';
7 sqlstr:=sqlstr||chr(10)||','||'max(decode(to_char(yj),'''||rs.yj||''',yj,'''')) 意见';
8 end loop ;
9 sqlstr:=sqlstr||chr(10)||'from tab group by dh ' ;
10 open cur for sqlstr;
11* end row_to_col_func;
12 /
Procedure created.
SQL>
SQL> var cur refcursor
SQL> exec row_to_col_func(:cur);
PL/SQL procedure successfully completed.
SQL> print cur
DH 审批人 意见 审批人 意见 审批人 意见
------ ------ ------ ------ ------ ------ ------
001 A a B b C c
002 A a B b C c
SQL> delete from tab where dh='001' and hqr='C';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from tab;
DH HQR YJ
------ ------ ------
001 A a
001 B b
002 A a
002 B b
002 C c
SQL> exec row_to_col_func(:cur);
PL/SQL procedure successfully completed.
SQL> print cur
DH 审批人 意见 审批人 意见 审批人 意见
------ ------ ------ ------ ------ ------ ------
001 A a B b
002 A a B b C c
SQL>