The create or replace package body PKG_REPORT_QUERY_TEST is


- defines the function of access,
The function f_get_query_report (I_JIXING IN EFF_PROCESSPLAN. JIXING % type)
Return t_query_rpt_test as
- return type as t_query_rpt_test
M_table t_query_rpt_test;
LK/;
- the result set rows the initial value
M_row_num number:=0;
V_processPlanTableName clob DEFAULT '; - dynamic table name according to the model automatically generate

Type cur_type is REF CURSOR; - define the type of cursor
Rtn_cur cur_type; - define the cursor variable


TYPE R_TYPE IS RECORD (
V_EFF EFF_PROCESSPLAN EFF % TYPE,
V_PPNUMBER EFF_PROCESSPLAN. PPNUMBER % TYPE);


TYPE dwry_type is record (
Ppnumber varchar2 (100),
Ppname varchar2 (100),
Wholenumber number (38),
Gongyiluxian varchar2 (100),
The eff varchar2 (100)
);

- the definition of some variables
V_sql varchar2 (11000); Dynamic SQL
-V_R1 R_TYPE;
Dwry_record dwry_type;




CURSOR MYCURSOR IS
The SELECT E.E FF,
E.P PNUMBER
The FROM EFF_PROCESSPLAN E
WHERE E.J IXING=I_JIXING;


The begin
- the result set initialization
M_table: t_query_rpt_test=();
- open the cursor
The open MYCURSOR;
Loop
- every data within the loop through the cursor
The fetch MYCURSOR
Into V_R1;
Exit the when MYCURSOR % NOTFOUND;

V_processPlanTableName:=concat (' zzz_processplan_ 'V_R1. V_PPNUMBER);
- need to optimize the SQL
V_sql:='select ppnumber ppname, sum (wholenumber) as wholenumber, gongyiluxian,' '| | V_R1. V_EFF | |
"' as the eff from '| | v_processPlanTableName | |' group by ppnumber, ppname, gongyiluxian ';

The open rtn_cur for v_sql;
Loop
- loop cursor, put to the record dwry_record
The fetch rtn_cur into dwry_record;
Exit the when rtn_cur % NOTFOUND;
- the expansion of the result set
M_table. The extend;
- the result set rows on the 1
M_row_num:=m_row_num + 1;
- set the value of each field in a
M_table (m_row_num) :=query_rpt_test (dwry_record. Ppnumber, dwry_record. Ppname, dwry_record wholenumber, dwry_record. The eff, dwry_record. Gongyiluxian);

end loop;
end loop;
- the end of the cycle, close the cursor
The close MYCURSOR;
- result set
Return m_table;
End f_get_query_report;

End PKG_REPORT_QUERY_TEST;