SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Retrieving data before insert statement in procedure

BufossJul 19 2015 — edited Jul 20 2015

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

This post has been answered by Solomon Yakobson on Jul 19 2015

Jump to Answer

Pavan Kumar

Hi,

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

I didn't get you on this logic, how did you selected 20 and 30, can you elaborate

Update : are you looking for this , just give insert into table and this query

SQL> with tab1 as
(
select '600' col1, '140' col2, '2' col3, '10' col4,'1300' col5,'500' col6,'1' col7 from dual union all
select '600' col1, '140' col2, '2' col3, '20' col4,'1400' col5,'340' col6,'1' col7 from dual union all
select '600' col1, '140' col2, '2' col3, '10' col4,'1400' col5,'230' col6,'1' col7 from dual union all
select '600' col1, '140' col2, '2' col3, '35' col4,'1700' col5,'120' col6,'1' col7 from dual union all
select '600' col1, '150' col2, '3' col3, '10' col4,'1300' col5,'166' col6,'1' col7 from dual union all
select '600' col1, '150' col2, '3' col3, '15' col4,'1400' col5,'435' col6,'1' col7 from dual
)
, grp1 as (
select *
from tab1
where col1 = 600
and   col2=140
)
, grp2 as (
select *
from tab1
where col1 = 600
and   col2=150
)
select distinct res.*
from grp1 res , (
select p1.*
from grp1 p1, grp2 p2
where p1.col1 = p2.col1
and p1.col4 = p2.col4
) ex
where res.col1 = ex.col1
and res.col2 = ex.col2
and res.col4 <> ex.col4  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31  ;
COL COL C CO COL5 COL C
--- --- - -- ---- --- -
600 140 2 20 1400 340 1
600 140 2 35 1700 120 1

- Pavan Kumar N

Solomon Yakobson

No need for procedure. Use, for example, full outer join:

with t1 as (

select *

from table1

where col1 = 600

and col2 = 140

and col3 = 2

t2 as (

select *

from table1

where col1 = 600

and col2 = 150

and col3 = 3

)

select nvl(t1.col4,t2.col4) col4

from t1

full join

on t2.col4 = t1.col4

where t1.col4 is null

or t2.col4 is null

      COL4
----------
        20
        35

SQL>

SY.

Eight Six

or may be like this?

select col1, col4

from table1

group by col1, col4

having count(distinct col2)=1 and count(distinct col3)=1;

Bufoss

@Pavan Kumar

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

I mean for source combination 600 , 140, 2 and destination combination 600, 150, 3 check the values of col4.

And bring me the col1, col2 , col3 from destination col4 from source that does not exist in destination , col5 , col6 from souce , and col7 from a sequence.

So, I want to insert on existing table tab1 2 rows :

600 , 150 , 3, 20 , 1400 , 340, 7

600 , 150 , 3, 35 , 1700 , 120, 8

not

600 140 2 20 1400 340 1

600 140 2 35 1700 120 1

-----------------------

@Solomon Yakobson

I need procedure because I will use it in adf (parameters will be bindings)

But this you answered to me I can do it only with minus. I have difficulty to extract all the values I need.

Solomon Yakobson

And what prevents you from useing bind variables in SQL:

with t1 as (

select *

from table1

where col1 = :P_COL1

and col2 = :P_FROM_COL2

and col3 = :P_FROM_COL3

t2 as (

select *

from table1

where col1 = :P_COL1

and col2 = :P_TO_COL2

and col3 = :P_TO_COL3

)

select nvl(t1.col4,t2.col4) col4

from t1

full join

on t2.col4 = t1.col4

where t1.col4 is null

or t2.col4 is null

SY.

P.S. Btw, by looking at your code all you need is MERGE statement.

Bufoss

I don't know how to use merge ..

Is it correct something like the following?

select :P_COL1

,:P_FROM_COL2

,:P_FROM_COL3

,a.col4

,a.col5

,a.col6

from :table_name as a

left outer join :table_name as b

on b.col1=a.col1

and b.col2=:P_TO_COL2

and b.col3=:P_TO_COL3

and b.col4=a.col4

where a.col1=:P_COL1

and a.col2=:P_from_COL2

and a.col3=:P_from_col3

and b.col1 is null;

I think Could I use the code in a function like do_copy returning a temp table and in procedure just write

insert into table1
select * from do_copy(600,140,2,150,3,'table1');

Solomon Yakobson

Sorry, even though MERGE can be used it wouldn't be right tool here. All you need is INSERT. The following replaces your procedure:

SQL> select *
2 from table1
3 /

6 rows selected.

SQL> insert
2    into table1
3    select :P_COL1,
4            :P_TO_COL2,
5            :P_TO_COL3,
6            col4,
7            null,
8            null,
9            null
10      from table1
11      where col1 = :P_COL1
12        and col2 = :P_FROM_COL2
13        and col3 = :P_FROM_COL3
14        and :P_FLG1 = 'Y'
15    minus
16      select :P_COL1,
17            :P_TO_COL2,
18            :P_TO_COL3,
19            col4,
20            null,
21            null,
22            null
23      from table1
24      where col1 = :P_COL1
25        and col2 = :P_TO_COL2
26        and col3 = :P_TO_COL3
27        and :P_FLG1 = 'Y'
28 /

2 rows created.

SQL> select *
2 from table1
3 /

      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
      600        150          3        20
      600        150          3        35

8 rows selected.

SQL>

SY.

Solomon Yakobson

Answer

And if you want to insert missing COL4 values both ways, use full join, as I already showed (slightly modified):

SQL> insert
2 into table1
3 values(600,150,3,70,1500,567,8)
4 /

1 row created.

SQL> insert
2 into table1
3 values(600,150,3,90,1900,789,9)
4 /

1 row created.

SQL> select *
2 from table1
3 /

8 rows selected.

SQL> insert
2    into table1
3    with t1 as (
4                select *
5                  from table1
6                  where col1 = :P_COL1
7                    and col2 = :P_FROM_COL2
8                    and col3 = :P_FROM_COL3
9                    and :P_FLG1 = 'Y'
10              ),
11        t2 as (
12                select *
13                  from table1
14                  where col1 = :P_COL1
15                    and col2 = :P_TO_COL2
16                  and col3 = :P_TO_COL3
17                    and :P_FLG1 = 'Y'
18              )
19    select :P_COL1 col1,
20            nvl2(t1.col4,:P_TO_COL2,:P_FROM_COL2) col2,
21            nvl2(t1.col4,:P_TO_COL3,:P_FROM_COL3) col3,
22            nvl(t1.col4,t2.col4) col4,
23            null,
24            null,
25            null
26      from      t1
27            full join
28                t2
29              on t2.col4 = t1.col4
30      where t1.col4 is null
31        or t2.col4 is null
32 /

4 rows created.

SQL> select *
2 from table1
3 /

COL1 COL2 COL3 COL4 COL5 COL6 COL7
---------- ---------- ---------- ---------- ---------- ---------- ----------
600 150 3 20

12 rows selected.

SQL>

SY.

Marked as Answer by Bufoss · Sep 27 2020

1 person found this helpful

Bufoss

Thanks a lot for your help! It works great!

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 17 2015

Added on Jul 19 2015

#procedure

9 comments

1,163 views

SQL & PL/SQL

Retrieving data before insert statement in procedure

Comments

Post Details