看到这个帖子:
http://bbs.csdn.net/topics/330039676
想使用函数的方法实现,根据连接中的内容步骤如下:
先创建function函数: create or replace function row_to_col_func(tabname in varchar2,
group_col in varchar2,
column_col in varchar2,
value_col in varchar2,
Aggregate_func in varchar2 default 'max',
colorder in varchar2 default null,
roworder in varchar2 default null,
when_value_null in varchar2 default null
)return sys_refcursor
Authid Current_User
as
sqlstr varchar2(2000):='select '||group_col||' ';
c1 sys_refcursor;
v1 varchar2(100);
cur sys_refcursor;
begin
open c1 for 'select distinct '||column_col||' from '||tabname||case when colorder is not null then ' order by '||colorder end;
loop
fetch c1 into v1;
exit when c1%notfound;
sqlstr:=sqlstr||chr(10)||','||case when when_value_null is not null then 'nvl(' end||
Aggregate_func||'(decode(to_char('||column_col||'),'''||v1||''','||value_col||'))'||
case when when_value_null is not null then chr(44) ||when_value_null||chr(41) end||'"'||v1||'"';
end loop;
close c1;
open cur for sqlstr||' from '||tabname||' group by '||group_col||case when roworder is not null then ' order by '||roworder end;
return cur;
end row_to_col_func;然后创建表并写入数据
--测试数据
create table rowtocol_test as
select 2009 year,1 month,'部门1' dept,50000 expenditure from dual
union all select 2009,2,'部门1',20000 from dual
union all select 2009,2,'部门1',30000 from dual
union all select 2010,1,'部门1',35000 from dual
union all select 2009,2,'部门2',40000 from dual
union all select 2009,3,'部门2',25000 from dual
union all select 2010,2,'部门3',60000 from dual
union all select 2009,2,'部门3',15000 from dual
union all select 2009,2,'部门3',10000 from dual;以上,数据和函数都有了,但是在pl/sql中执行以下语句会报错:select
row_to_col_func('rowtocol_test','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0')
from dual;
ROW_TO_COL_FUNC('ROWTOCOL_TEST
<Cursor>
引号内的字符串没有正确结束, 很少用oracle,还不晓得怎么用function,所以希望大伙帮忙看看. 谢谢
http://bbs.csdn.net/topics/330039676
想使用函数的方法实现,根据连接中的内容步骤如下:
先创建function函数: create or replace function row_to_col_func(tabname in varchar2,
group_col in varchar2,
column_col in varchar2,
value_col in varchar2,
Aggregate_func in varchar2 default 'max',
colorder in varchar2 default null,
roworder in varchar2 default null,
when_value_null in varchar2 default null
)return sys_refcursor
Authid Current_User
as
sqlstr varchar2(2000):='select '||group_col||' ';
c1 sys_refcursor;
v1 varchar2(100);
cur sys_refcursor;
begin
open c1 for 'select distinct '||column_col||' from '||tabname||case when colorder is not null then ' order by '||colorder end;
loop
fetch c1 into v1;
exit when c1%notfound;
sqlstr:=sqlstr||chr(10)||','||case when when_value_null is not null then 'nvl(' end||
Aggregate_func||'(decode(to_char('||column_col||'),'''||v1||''','||value_col||'))'||
case when when_value_null is not null then chr(44) ||when_value_null||chr(41) end||'"'||v1||'"';
end loop;
close c1;
open cur for sqlstr||' from '||tabname||' group by '||group_col||case when roworder is not null then ' order by '||roworder end;
return cur;
end row_to_col_func;然后创建表并写入数据
--测试数据
create table rowtocol_test as
select 2009 year,1 month,'部门1' dept,50000 expenditure from dual
union all select 2009,2,'部门1',20000 from dual
union all select 2009,2,'部门1',30000 from dual
union all select 2010,1,'部门1',35000 from dual
union all select 2009,2,'部门2',40000 from dual
union all select 2009,3,'部门2',25000 from dual
union all select 2010,2,'部门3',60000 from dual
union all select 2009,2,'部门3',15000 from dual
union all select 2009,2,'部门3',10000 from dual;以上,数据和函数都有了,但是在pl/sql中执行以下语句会报错:select
row_to_col_func('rowtocol_test','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0')
from dual;
ROW_TO_COL_FUNC('ROWTOCOL_TEST
<Cursor>
引号内的字符串没有正确结束, 很少用oracle,还不晓得怎么用function,所以希望大伙帮忙看看. 谢谢
2,function 直接exec就好。
3,这里的Aggregate_func => 'sum'不知道你在搞什么。
在我本地的环境下执行是成功的最后我发现你的错误在这里:第三步执行的应该是:select
row_to_col_func('rowtocol_test','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0')
from dual;
而不是:select
row_to_col_func('rowtocol_test','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0')
from dual;
ROW_TO_COL_FUNC('ROWTOCOL_TEST
<Cursor>
下面的
ROW_TO_COL_FUNC('ROWTOCOL_TEST
<Cursor>
是人家给的DEMO例子的查询结果。
SELECT SIN(30) FROM DUAL;
上面的SIN()就是系统内置的函数。
要点击游标右面的 ... 才可以看到啊能不能直接执行完select 就看到结果呢
在SQLPLUS环境下,如果开启了SERVEROUTPUT的话,是可以直接看到结果的,如下:11:27:17 ChenZw> select row_to_col_func('rowtocol_test','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',
when_value_null => '0')from dual;ROW_TO_COL_FUNC('ROW
--------------------
CURSOR STATEMENT : 1CURSOR STATEMENT : 1 YEAR MONTH 部门1 部门2 部门3
---------- ---------- ---------- ---------- ----------
2009 1 50000 0 0
2009 2 50000 40000 25000
2009 3 0 25000 0
2010 1 35000 0 0
2010 2 0 0 60000已选择5行。
已选择 1 行。已用时间: 00: 00: 00.15
这个只是SQLPLUS 和PL/SQL DEVELOPER的对于sys_refcursor类型的两种不同的处理形式而已了..