The create or replace procedure TestType (cur out sys_refcursor,
Pid varchar2,
Bt in varchar2,
Et in varchar2,
The interval in varchar2) is

Cout number;
V_type TY_TBL_VIT: TY_TBL_VIT=();
Cursor group_cur is select the name from the table (v_type) where the name is not null group by name;
SQLSTR varchar2 (3000);
The begin
- the middle part of the code to omit
For n in group_cur loop
SQLSTR: SQLSTR=| | ', the SUM (DECODE (name, ' ' '| | n.n ame | |' ' ', the name, 0)) '| | n.n ame.
end loop;
SQLSTR:='select' | | substr (SQLSTR, 2, length (SQLSTR) - 1) | | 'from table (v_type) group by which the order by which';
DBMS_OUTPUT. PUT_LINE (SQLSTR);
The open cur for SQLSTR;

End TestType;



Call a stored procedure, the execution of the last line "open cur for SQLSTR;" When prompted ORA - 00904: "V_TYPE" : identifier is invalid,
Ask next should be how to deal with the

CodePudding user response:

What are you going to do? How to define TY_TBL_VIT?

CodePudding user response:

TY_TBL_VIT is a custom table, I now is to get the query result set to convert the ranks, omit the code above, I put in the result set has already been stored in the result set is followed by generating dynamic SQL statements to the ranks of conversion, but v_type unable to properly parse, I want to know how this should be written to identify

CodePudding user response:

V_type TY_TBL_VIT so should be able to define a variable but:=TY_TBL_VIT (); Initial words can do so directly?
The cursor can be normal query group_cur you?

CodePudding user response:

Can not have a problem,

For n in group_cur loop
SQLSTR: SQLSTR=| | ', the SUM (DECODE (name, "' | | n.n ame | |
"', the name, null)) '| | n.n ame.
end loop;
SQLSTR:='select' | | substr (SQLSTR, 2, length (SQLSTR) - 1) | |
'the from table (v_type) group by which the order by which';
DBMS_OUTPUT. PUT_LINE (SQLSTR);
The open cur for
Select SUM (DECODE (name, 'R' name, null)) R,
The SUM (DECODE (name, 'BloodSugar' name, null)) BloodSugar,
The SUM (DECODE (name, 'PR' name, null)) PR,
The SUM (DECODE (name, MBP, name, null)) MBP,
The SUM (DECODE (name, 'HR' name, null)) HR,
The SUM (DECODE (name, CVP, name, null)) CVP,
The SUM (DECODE (name, 'RectalT' name, null)) RectalT,
The SUM (DECODE (name, 'SpO2, name, null)) SpO2,
The SUM (DECODE (name, 'SBP, name, null)) SBP,
The SUM (DECODE (name, 'T', the name, null)) T,
The SUM (DECODE (name, 'DBP, name, null)) DBP
From the table (v_type)
Group by which
The order by which;
I put the red line to print out the statement in the following, can normal execution,

CodePudding user response:

 
- reference under the building Lord, and see if I can help you 

SQL> 
SQL> The create type t_type is table of varchar2 (30); 
2/
Type created 
SQL> The 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 the begin 
6 
7 v_sql:='select count (*) from the table (1)'; 
8 the execute immediate v_sql into v_count using v_type; 
9 dbms_output. Put_line (' v_count='| | v_count); 
10 the end; 
11/
V_count=4 
PL/SQL procedure successfully completed 
SQL> Drop type t_type; 
Type dropped 

SQL>