CREATE OR REPLACE PROCEDURE SEL_plan (
I_JIXING IN EFF_PROCESSPLAN.JIXING%type , --機型輸入引數
cur out sys_refcursor
)
AS
/********************************
ACTION : 單機數量查詢存盤程序
AUTHORR: wzm
DATE : 2018-05-30
*********************************/
--初始化
TYPE R_TYPE IS RECORD(
V_EFF EFF_PROCESSPLAN.EFF%TYPE,
V_PPNUMBER EFF_PROCESSPLAN.PPNUMBER%TYPE);
v_sql varchar2(11000);--動態sql
v_sql1 varchar2(10000);
v_processPlanTableName clob DEFAULT '';-- 動態表名 根據機型自動生成
V_R1 R_TYPE;
--定義游標 (取出有效性EFF,動態表名后綴PPNUMBER)
CURSOR MYCURSOR IS
SELECT E.EFF,
E.PPNUMBER
FROM EFF_PROCESSPLAN E
WHERE E.JIXING =I_JIXING ;
BEGIN
--打開游標
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 ,sum(wholenumber) as wholenumber, gongyiluxian ,'''|| V_R1.V_EFF ||
''' as eff , ppname from ' || v_processPlanTableName|| ' group by ppnumber, gongyiluxian , ppname ' ;
v_sql1:='insert into tmp2 '|| v_sql;
---優化查詢，批量插入臨時表，資料量很大
DBMS_OUTPUT.PUT_LINE(v_sql1);
execute immediate v_sql1;
commit;
DBMS_OUTPUT.PUT_LINE(v_sql);
DBMS_OUTPUT.PUT_LINE(v_processPlanTableName);
DBMS_OUTPUT.PUT_LINE(V_R1.V_EFF);
END LOOP;
open cur for ' select ppnumber , wholenumber , gongyiluxian , wm_concat( distinct eff) as eff , ppname
from tmp2 group by ppnumber ,wholenumber , gongyiluxian , ppname ';
CLOSE MYCURSOR;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
end SEL_plan;
uj5u.com熱心網友回復：
v_sql:='select ppnumber ,sum(wholenumber) as wholenumber, gongyiluxian ,'''|| V_R1.V_EFF ||''' as eff , ppname from ' || v_processPlanTableName|| ' group by ppnumber, gongyiluxian , ppname ' ;
動態表中查詢 大量資料，表名取自游標中資料。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/66617.html
標籤：開發
上一篇：Oracle 存盤程序優化查詢
下一篇：新手請教關于資料庫連接的問題