copy data after comparing a specific value

Question

I want to make a procedure with in parameters. To be more specific, I have tables like the folowing TABLE1

|COL1 | COL2 | COL3 | COL4 |  COL5 | COL6|
 ----------------------------------------
|600  |  140 |   2  |  10  |  1600 |  1  |
 ----------------------------------------
|600  |  140 |   2  |  20  |  1200 |  4  |
 ----------------------------------------
|600  |  140 |   2  |  15  |  1100 |  3  |
 ----------------------------------------
|600  |  140 |   2  |  35  |  1700 |  2  |
 ----------------------------------------
|600  |  140 |   3  |  10  |  1300 |  6  |
 ----------------------------------------
|600  |  140 |   3  |  15  |  1100 |  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=140/col3=3 Return 20 and 35

And insert in this table TABLE1 the rows 600 , 140 , 3, 20 , 1200 , 7 (seq number) 600 , 140 , 3, 35 , 1700 , 8 (seq number)

But I don't know how can I do the 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,                                      
                                     P_FLG4         IN      VARCHAR2,
                                     P_FLG5         IN      VARCHAR2
                                ) IS


    CURSOR C1 IS SELECT *
                   FROM A
                  WHERE COL1 = P_COL1;

    CURSOR C2 IS SELECT *
                   FROM B
                  WHERE COL1  = P_COL1
                    AND COL2  = P_COL2
                    AND COL3  = P_COL3 ;

    BEGIN

    IF P_FLG1='N' OR P_FLG2='N' OR P_FLG3='N' OR  P_FLG4 ='N' OR P_FLG5 = 'N' THEN
        GOTO label; --do nothing
    END IF;

    IF P_FLG1 = 'Y' THEN
        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
-- how to do insert ?

    END IF;

    IF P_FLG2 = 'Y' THEN
        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
    END IF;
    -- ..........
    <<label>>
    END;

Could you help me please do it ? Thank you

Ravi Ravi · Accepted Answer · 2015-07-18T12:04:44

If I'm not wrong, you are trying to get values from one table and trying to insert them into another table. Then, you could this way.

Insert into YourTable(col1,col2,coln) 
              SELECT col1,col2,coln from anotherTable;

Update

After reading your recent comment, it seems like you should use MERGE. Even you are not require to use cursor in this case.

MERGE INTO destination D
   USING (SELECT col1, col2 FROM source
   WHERE [condition check]) S
   ON (D.id = S.id)
   WHEN NOT MATCHED THEN INSERT (D.col3, D.col4)
     VALUES (S.col3, S.col4);

copy data after comparing a specific value

1 Answers