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": 标识符无效。
问下应该怎么处理
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": 标识符无效。
问下应该怎么处理
解决方案 »
- 菜鸟:欲用脚本从oracle 10g升级到11g,出现问题!
- 急急急!从一个用户的表中提取所需字段到另一个用户下面建立表的问题。
- hibernate注解 oracle 自动生成序列的问题
- 这句SQL跑了5个小时才出结果。请教大侠:如何提高配置参数和优化SQL?
- 请大家看看这个表在设计上有什么问题
- 如何在oracle中对查询记录中的一个字段求平均值,要求用一个SQL语句完成(在线等)
- 如何设定Database SID?
- 数据导入时报错
- CONTROL配置问题~
- 请教
- hibernate根据某个字段搜索时需不需要加单引号
- oracle中存储过程一条查询语句怎么加锁可以在commit之后才可以再次执行这条查询语句
group_cur 你这个游标能正常查询吗?
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;
我把红字行打印出的语句放到下面,就可以正常执行。
-- 楼主参考下这个,看看能不能帮助你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 droppedSQL>