Hi,
I am new in plsql programming and I would like to make a procedure.I have tables like the following TABLE1
|COL1 | COL2 | COL3 | COL4 | COL5 | COL6 |COL7|
| 600 | 140 | 2 | 10 | 1300 | 500 | 1 |
| 600 | 140 | 2 | 20 | 1400 | 340 | 4 |
| 600 | 140 | 2 | 15 | 1400 | 230 | 3 |
| 600 | 140 | 2 | 35 | 1700 | 120 | 2 |
| 600 | 150 | 3 | 10 | 1300 | 166 | 6 |
| 600 | 150 | 3 | 15 | 1400 | 435 | 5 |
For the same COL1 and COL2/COL3 , check the select different values from COL4
For instance for COL1=600 , COL2=140/COL3=2 and COL2=150/COL3=3
Return 20 and 35 as not common values
Then insert in this table TABLE1 the rows
600 , 150 , 3, 20 , 1400 , 340, 7
600 , 150 , 3, 35 , 1700 , 120, 8
I am trying to make the procedure like below but I have problem how to retrieve data in insert statement
PROCEDURE COPY_COLUMNS ( P_COL1 IN A.COL1%TYPE,
P_FROM_COL2 IN B.COL2%TYPE,
P_FROM_COL3 IN B.COL3%TYPE,
P_TO_COL2 IN B.COL2%TYPE,
P_TO_COL3 IN B.COL3%TYPE,
P_FLG1 IN VARCHAR2,
P_FLG2 IN VARCHAR2,
P_FLG3 IN VARCHAR2
) IS
CURSOR CFL1 IS select COL4
FROM TABLE1
WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3
MINUS
select COL4
FROM TABLE1
WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3;
CURSOR CFL2 IS select COL4
FROM TABLE2
WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3
MINUS
select COL4
FROM TABLE2
WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3;
CURSOR CFL3 IS select COL4
FROM TABLE3
WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3
MINUS
select COL4
FROM TABLE3
WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3;
V_REC CFL1%ROWTYPE;
BEGIN
IF P_FLG1='N' OR P_FLG2='N' OR P_FLG3='N' THEN
GOTO label; --do nothing
END IF;
IF P_FLG1 = 'Y' THEN
OPEN CFL1;
FETCH CFL1 INTO V_REC;
CLOSE C1;
-- SELECT COL5, COL6
-- FROM TABLE1
-- WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3 AND COL4 = V_REC.COL4;
FOR REC IN CFL1 LOOP
INSERT INTO TABLE1
SELECT P_COL1, P_TO_COL2, P_TO_COL3, CFL1.COL4, -- COL5 ?? , COL6 ?? -- , SEQname2.NEXTVAL)
END LOOP;
END IF;
<<label>>
END;
Could you help me please do it ?
Thanks in advance