自定义table在拼装sql语句时遇到的问题

[chinareny2k]

create or replace procedure TestType(cur out sys_refcursor,
pid varchar2,
bt in varchar2,
et in varchar2,
interval in varchar2) is

cout number;
v_type TY_TBL_VIT := TY_TBL_VIT();
cursor group_cur is select name from table(v_type) where name is not null group by name;
sqlstr varchar2(3000);
begin
----中间部分代码省略
for n in group_cur loop
sqlstr := sqlstr || ',SUM(DECODE(name,'''|| n.name ||''',name,0)) ' || n.name;
end loop;
sqlstr := 'select ' || substr(sqlstr,2,length(sqlstr)-1) || ' from table(v_type) group by which order by which';
DBMS_OUTPUT.PUT_LINE(sqlstr);
open cur for sqlstr;

end TestType;



调用存储过程，在执行最后一行“open cur for sqlstr;”的时候 提示 ORA-00904: "V_TYPE": 标识符无效。
问下应该怎么处理

[chinareny2k]

TY_TBL_VIT是一个自定义的table，我现在是要把查询的结果集进行行列转换，上面省略的代码里我把结果集已经存储到了这个结果集里面后面是生成动态的sql语句去进行行列转换，但是v_type无法正常解析，我想知道这个应该怎样写才能识别

[学海无涯-回头是岸]

v_type TY_TBL_VIT这样定义变量应该可以但是 := TY_TBL_VIT();这样直接初始话能行吗？ group_cur 你这个游标能正常查询吗？

[chinareny2k]

可以没有问题， for n in group_cur loop sqlstr := sqlstr || ',SUM(DECODE(name,''' || n.name || ''',name,null)) ' || n.name; end loop; sqlstr := 'select ' || substr(sqlstr, 2, length(sqlstr) - 1) || ' from table(v_type) group by which order by which'; DBMS_OUTPUT.PUT_LINE(sqlstr); open cur for select SUM(DECODE(name, 'R', name, null)) R, SUM(DECODE(name, 'BloodSugar', name, null)) BloodSugar, SUM(DECODE(name, 'PR', name, null)) PR, SUM(DECODE(name, 'MBP', name, null)) MBP, SUM(DECODE(name, 'HR', name, null)) HR, SUM(DECODE(name, 'CVP', name, null)) CVP, SUM(DECODE(name, 'RectalT', name, null)) RectalT, SUM(DECODE(name, 'SpO2', name, null)) SpO2, SUM(DECODE(name, 'SBP', name, null)) SBP, SUM(DECODE(name, 'T', name, null)) T, SUM(DECODE(name, 'DBP', name, null)) DBP from table(v_type) group by which order by which; 我把红字行打印出的语句放到下面，就可以正常执行。

[卖水果的net]

你这是要做什么？ TY_TBL_VIT 是怎么定义的？

[卖水果的net]

-- 楼主参考下这个，看看能不能帮助你

SQL> 
SQL> create type t_type is table of varchar2(30);
  2  /
Type created
SQL> set serverout on;
SQL> declare
  2   v_type t_type := t_type('bj','sh','gz','sz');
  3   v_count int;
  4   v_sql varchar(200);
  5  begin
  6  
  7    v_sql := 'select count(*) from table(:1)';
  8    execute immediate v_sql into v_count using v_type;
  9    dbms_output.put_line('v_count=' || v_count);
 10  end;
 11  /
v_count=4
PL/SQL procedure successfully completed
SQL> drop type t_type;
Type dropped

SQL>