Novice, encounter problems: from the first in the cursor to the data, in the second cursor parameters, don't know what to write, in addition, SCSM130_01 values may be more than one, so need UPDATE to UPDATE the insert table data, the be... Process is as follows:
The create or replace procedure sp_bi_scsu412 (P_CODE VARCHAR2, P_DATE DATE) is
L_scsu412_K1 SCSU412. SCSU412_K1 % TYPE;
L_SCSU412_K2 SCSU412. SCSU412_K2 % TYPE;
L_SCSU412_K3 SCSU412. SCSU412_K3 % TYPE;
L_SCSU412_K4 SCSU412. SCSU412_K4 % TYPE;
L_SCSU412_K5 SCSU412. SCSU412_K5 % TYPE;
L_SCSU412_01 SCSU412. SCSU412_01 % TYPE;
L_SCSU412_02 SCSU412. SCSU412_02 % TYPE;
L_SCSU412_03 SCSU412. SCSU412_03 % TYPE;
L_SCSU412_04 SCSU412. SCSU412_04 % TYPE;
L_SCSU412_05 SCSU412. SCSU412_05 % TYPE;
L_SCSU412_06 SCSU412. SCSU412_06 % TYPE;
L_SCSU412_07 SCSU412. SCSU412_07 % TYPE;
L_SCSU412_K6 SCSU412. SCSU412_K6 % TYPE;
TYPE a CUR IS REF CURSOR;
CURA CUR;
CURSOR CUR_1 IS
The SELECT A.S CSU410_02 - branch code
A.S CSU410_03 - branch name
A.S CSU410_04 - this code
A.S CSU410_05 - the department name
B.S CSU411_06 - store code
B.S CSU411_07 - store name
A.S CSU410_06 - the originator gonghaowu
A.S CSU410_07 - sponsor name
COUNT (A.S CSU410_01),
the target shop goods stores numberA.S CSU410_01 - reported monthly
B.S CSU411_08, - summary version
B.S CSU411_02 - virtual client code
The FROM CUX_K2_SCSU410 A, CUX_K2_SCSU411 B
WHERE Amy polumbo ROCINSTID=p. ROCINSTID
AND B.S CSU411_08=P_CODE
AND A.S CSU410_01=TO_CHAR (P_DATE, 'YYYYMM')
GROUP BY A.S CSU410_02,
A.S CSU410_03,
A.S CSU410_04,
A.S CSU410_05,
B.S CSU411_06,
B.S CSU411_07,
A.S CSU410_06,
A.S CSU410_07,
A.S CSU410_01,
B.S CSU411_08,
B.S CSU411_02,
B.S CSU411_06;
The BEGIN
The OPEN CUR_1;
LOOP
The FETCH CUR_1
INTO l_scsu412_K1,
L_SCSU412_01,
L_SCSU412_K2,
L_SCSU412_02,
L_SCSU412_K3,
L_SCSU412_03,
L_SCSU412_K4,
L_SCSU412_04,
L_SCSU412_05,
L_SCSU412_07,
L_SCSU412_K5,
L_SCSU412_K6;
EXIT the WHEN CUR_1 % NOTFOUND;
The OPEN CURA FOR SELECT TO_CHAR (L_SCSU412_07, 'YYYYMM'), sum (SCSU071_07)
The FROM SCSU070 SCSU071, SCSM130
WHERE SCSU070_K1=SCSU071_K1
AND SCSU070_K2=SCSU071_K2
AND SCSU070_06=L_SCSU412_K6
AND SCSU070_07=L_SCSU412_01
AND SCSU071_04=SCSM130_01
AND SCSM130_K1=L_SCSU412_K5;
IF L_SCSU412_06="' THEN
The SELECT TO_CHAR (L_SCSU412_07, 'YYYYMM'), sum (SCSU071_07)
The FROM SCSU070 SCSU071, SAIM530
WHERE SCSU070_K1=SCSU071_K1
AND SCSU070_K2=SCSU071_K2
AND SCSU070_06=L_SCSU412_K6
AND SCSU070_07=L_SCSU412_01
AND SCSU071_04=SAIM530_K2
AND SAIM530_K1=L_SCSU412_K5;
END IF;
LOOP
The FETCH CURA INTO c2rec;
EXIT the WHEN CURA % NOTFOUND;
SQL;

END LOOP;
The CLOSE CURA.
The BEGIN
The DELETE FROM SCSU412;
INSERT INTO SCSU412
(scsu412_K1,
SCSU412_K2,
SCSU412_K3,
SCSU412_K4,
SCSU412_01,
SCSU412_02,
SCSU412_03,
SCSU412_04,
SCSU412_05,
SCSU412_06,
SCSU412_07,
SCSU412_K5,
SCSU412_K6)
VALUES
(l_scsu412_K1,
L_SCSU412_K2,
L_SCSU412_K3,
L_SCSU412_K4,
L_SCSU412_01,
L_SCSU412_02,
L_SCSU412_03,
L_SCSU412_04,
L_SCSU412_05,
L_SCSU412_06,
L_SCSU412_07,
L_SCSU412_K5,
L_SCSU412_K6);
The EXCEPTION
The WHEN OTHERS THEN
NULL;
END;
COMMIT;
END LOOP;
End sp_bi_scsu412;
The above process should be wrong, but the basic can tell what is logical, beg god to help teach me how to write

CodePudding user response:

Don't have to cursor, the building Lord see the merge into

CodePudding user response:

Don't need: from the first in the cursor to the data, in the second cursor parameters,
There are two solutions:
1. Create a temporary table,
The FROM CUX_K2_SCSU410 A, CUX_K2_SCSU411 B
The FROM SCSU070 SCSU071, SCSM130
The FROM SCSU070 SCSU071, SAIM530
Write in the temporary table, then the associated query,
2. Use the merge into can also processing,

CodePudding user response:

Recommend using method 1,
Using the merge into code readability is poor, and very easy to get wrong,