Oracle 存储过程游标查询优化，插入临时表

[wzmltt]

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;

[wzmltt]

v_sql:='select ppnumber ,sum(wholenumber) as wholenumber, gongyiluxian ,'''|| V_R1.V_EFF || ''' as eff , ppname from ' || v_processPlanTableName|| ' group by ppnumber, gongyiluxian , ppname ' ; 动态表中查询 大量数据，表名取自游标中数据。