create or replace package body PKG_REPORT_QUERY_TEST is
--定義取數的function,
function f_get_query_report(I_JIXING IN EFF_PROCESSPLAN.JIXING%type)
return t_query_rpt_test as
--回傳型別為t_query_rpt_test
m_table t_query_rpt_test;
LK/;
--結果集行數初始值
m_row_num number := 0;
v_processPlanTableName clob DEFAULT '';-- 動態表名 根據機型自動生成
type cur_type is REF CURSOR;--定義游標型別
rtn_cur cur_type; --定義游標變數
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),
eff varchar2(100)
);
--一些變數的定義
v_sql varchar2(11000);--動態sql
V_R1 R_TYPE;
dwry_record dwry_type;
CURSOR MYCURSOR IS
SELECT E.EFF,
E.PPNUMBER
FROM EFF_PROCESSPLAN E
WHERE E.JIXING =I_JIXING ;
begin
--結果集初始化
m_table := t_query_rpt_test();
--打開游標
open MYCURSOR;
loop
--回圈遍歷游標內的每一個資料
fetch MYCURSOR
into V_R1;
exit when MYCURSOR%NOTFOUND;
v_processPlanTableName:= concat('zzz_processplan_',V_R1.V_PPNUMBER);
---需要優化這個sql
v_sql:='select ppnumber ,ppname, sum(wholenumber) as wholenumber, gongyiluxian ,'''|| V_R1.V_EFF ||
''' as eff from ' || v_processPlanTableName || ' group by ppnumber, ppname, gongyiluxian' ;
open rtn_cur for v_sql;
loop
--回圈游標，將記錄放至dwry_record
fetch rtn_cur into dwry_record;
exit when rtn_cur%NOTFOUND;
--擴充結果集
m_table.extend;
--結果集行數自增1
m_row_num := m_row_num + 1;
--設定結果集中每個欄位的值
m_table(m_row_num) := query_rpt_test(dwry_record.ppnumber,dwry_record.ppname,dwry_record.wholenumber,dwry_record.eff,dwry_record.gongyiluxian);
end loop;
end loop;
--回圈結束，關閉游標
close MYCURSOR;
--回傳結果集
return m_table;
end f_get_query_report;
end PKG_REPORT_QUERY_TEST;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/66616.html
標籤：開發