sql 调用 自定义function，出现问题。

Question

我自定义函数：
create or replace function ver2hor
return varchar2 is
  horSql varchar2(5000);
  i  int;
begin
  horSql := '';
  i := 0;
    declare 
    cursor c1 is 
    select t.year from qgwaterenv_product t group by t.year order by t.year ;
    begin
      for r_c1 in c1 loop
      i := i+1;
      horSql:= horSql||'max(decode(tt.year,';
      horSql := horSql||''||r_c1.year||','||'tt.thewlevel,null)) f'||to_char(i)||',';
    end loop;
      horSql := substr(horSql,1,length(horSql)-1);
    end;
      
  return(horSql);
end ver2hor;返回字符串：
max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
这个字符串放到sql中
select 
  tt.wissue,
max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
  from
      (
      select t.wissue,t.waterplace,t.year,max(t.thewlevel) thewlevel 
      from qgwaterenv_product t 
      where t.waterplace = '山西忻州万家寨水库'
      group by t.waterplace, t.year, t.wissue
      order by t.wissue
      )tt
  group by tt.wissue
  order by tt.wissue
中，可以查询出结果。但是写成下面这样就不行。
select 
  tt.wissue,
ver2hor()
  from
      (
      select t.wissue,t.waterplace,t.year,max(t.thewlevel) thewlevel 
      from qgwaterenv_product t 
      where t.waterplace = '山西忻州万家寨水库'
      group by t.waterplace, t.year, t.wissue
      order by t.wissue
      )tt
  group by tt.wissue
  order by tt.wissue查出的结果是：
1 1 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
2 2 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
3 3 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
4 4 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
5 5 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
6 6 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
7 7 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
8 8 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
9 9 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
10 10 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
11 11 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
12 12 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
13 13 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
14 14 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
15 15 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4
16 16 max(decode(tt.year,2005,tt.thewlevel,null)) f1,max(decode(tt.year,2006,tt.thewlevel,null)) f2,max(decode(tt.year,2007,tt.thewlevel,null)) f3,max(decode(tt.year,2008,tt.thewlevel,null)) f4显然是把function 返回的东西当成字符串了。
怎么办呢？

Answer 1

拼字符串,再动态执行.v_sql := 'select tt.wissue,'|| ver2hor() ||
  'from 
      ( 
      select t.wissue,t.waterplace,t.year,max(t.thewlevel) thewlevel 
      from qgwaterenv_product t 
      where t.waterplace = ''山西忻州万家寨水库'' 
      group by t.waterplace, t.year, t.wissue 
      order by t.wissue 
      )tt 
  group by tt.wissue 
  order by tt.wissue';EXECUTE IMMEDIATE v_sql;

Answer 2

那是另一种思路。
我想知道 sql 调用function，sql会把返回值当作字符串处理。
好像是我function 返回值 varchar2 有问题。我这种思路能行通吗？

Answer 3

lz的思路行不通
SELECT MAX(1) FROM DUAL;
SELECT 'MAX(1)' FROM DUAL;
两句的结果是不一样的。
只能用1楼的方法。