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": 識別符號無效。
問下應該怎么處理
uj5u.com熱心網友回復：
你這是要做什么？ TY_TBL_VIT 是怎么定義的？uj5u.com熱心網友回復：
TY_TBL_VIT是一個自定義的table，我現在是要把查詢的結果集進行行列轉換，上面省略的代碼里我把結果集已經存盤到了這個結果集里面后面是生成動態的sql陳述句去進行行列轉換，但是v_type無法正常決議，我想知道這個應該怎樣寫才能識別uj5u.com熱心網友回復：
v_type TY_TBL_VIT這樣定義變數應該可以但是 := TY_TBL_VIT();這樣直接初始話能行嗎？group_cur 你這個游標能正常查詢嗎？
uj5u.com熱心網友回復：
可以沒有問題，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;
我把紅字行列印出的陳述句放到下面，就可以正常執行。
uj5u.com熱心網友回復：
-- 樓主參考下這個，看看能不能幫助你
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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/66573.html
標籤：基礎和管理
上一篇：ODI如何批量同步資料