I_JIXING IN EFF_PROCESSPLAN. JIXING % type, -- --
model input parameters
Cur out sys_refcursor
)
AS
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
ACTION: single number query stored procedure
AUTHORR: WZM
DATE: 2018-05-30
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
Initialization,
TYPE R_TYPE IS RECORD (
V_EFF EFF_PROCESSPLAN EFF % TYPE,
V_PPNUMBER EFF_PROCESSPLAN. PPNUMBER % TYPE);
V_sql varchar2 (11000); Dynamic SQL
-
V_sql1 varchar2 (10000);
V_processPlanTableName clob DEFAULT '; - dynamic table name according to the model automatically generate
V_R1 R_TYPE;
Effectiveness - definition cursor (take out the EFF, dynamic table name suffix PPNUMBER)
CURSOR MYCURSOR IS
The SELECT E.E FF,
E.P PNUMBER
The FROM EFF_PROCESSPLAN E
WHERE E.J IXING=I_JIXING;
The BEGIN
- open the cursor
The OPEN MYCURSOR;
LOOP
The FETCH MYCURSOR
INTO V_R1;
EXIT the WHEN MYCURSOR % NOTFOUND;
- splicing dynamic table name
V_processPlanTableName:=concat (' zzz_processplan_ 'V_R1. V_PPNUMBER);
Dynamic SQL
-
V_sql:='select ppnumber, sum (wholenumber) as wholenumber, gongyiluxian,' '| | V_R1. V_EFF | |
"' as the eff, ppname from '| | v_processPlanTableName | |' group by ppnumber, gongyiluxian, ppname ';
V_sql1:='insert into tmp2' | | v_sql;
- optimized query, bulk insert temporary table, great amount of data
DBMS_OUTPUT. PUT_LINE (v_sql1);
The 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;
The open cur for 'select ppnumber wholenumber, gongyiluxian, wm_concat (distinct eff) as the eff, ppname
The from tmp2 group by ppnumber wholenumber, gongyiluxian ppname ';
The CLOSE MYCURSOR;
The EXCEPTION
The WHEN OTHERS THEN
ROLLBACK;
End SEL_plan;
CodePudding user response:
V_sql:='select ppnumber, sum (wholenumber) as wholenumber, gongyiluxian,' '| | V_R1. V_EFF | |"' as the eff, ppname from '| | v_processPlanTableName | |' group by ppnumber, gongyiluxian, ppname ';
Dynamic query large amounts of data in the table, the table name data from a cursor,