Skip to Main Content

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

Comments

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

            t2

          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

            t2

          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  /

      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

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  /

      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        70      1500        567          8
      600        150          3        90      1900        789          9

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        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        70      1500        567          8
      600        150          3        90      1900        789          9
      600        140          2        70
      600        140          2        90
      600        150          3        35

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

12 rows selected.

SQL>

SY.

Marked as Answer by Bufoss · Sep 27 2020
Bufoss

Thanks a lot for your help! It works great!

1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 17 2015
Added on Jul 19 2015
9 comments
1,163 views