SQLTeam.com | Weblogs | Forums

Problem retrieving data before insert statement in procedure


#1

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 ?? -- , SEQ.NEXTVAL) 

    END LOOP;

END IF;

<<label>>
END;

Could you help me please ?


#2

As you are using Oracle (and this is Microsoft SQL Server forum), you will get better help in an Oracle forum.

In mssql we would do something like:

create table table1(col1 int,col2 int,col3 int,col4 int,col5 int,col6 int,col7 int);
insert into table1(col1,col2,col3,col4,col5,col6,col7)
 values(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)
;
go

create function copy_columns(@p_col1      int
                            ,@p_from_col2 int
                            ,@p_from_col3 int
                            ,@p_to_col2   int
                            ,@p_to_col3   int
                            ,@p_flg1      varchar(10)
                            ,@p_flg2      varchar(10)
                            ,@p_flg3      varchar(10)
                            )
returns @xx table(col1 int
                 ,col2 int
                 ,col3 int
                 ,col4 int
                 ,col5 int
                 ,col6 int
                 ,col7 int
                 )
as
begin
   insert into @xx
   select @p_col1 as col1
         ,@p_to_col2 as col2
         ,@p_to_col3 as col3
         ,a.col4
         ,a.col5
         ,a.col6
         ,c.m+row_number() over(order by a.col4) as col7
     from table1 as a
          left outer join table1 as b
                       on b.col1=a.col1
                      and b.col2=@p_to_col2
                      and b.col3=@p_to_col3
                      and b.col4=a.col4
    cross apply (select max(col7) as m
                   from table1
                ) as c
    where a.col1=@p_col1
      and a.col2=@p_from_col2
      and a.col3=@p_from_col3
      and b.col1 is null
   ;
   return;
end;
go

insert into table1
select * from copy_columns(600,140,2,150,3,null,null,null);

select * from table1;

drop function copy_columns;
drop table table1;

#3

Thanks a lot for your answer


#4

What is exactly the c.m ?


#5

In the cross apply, I find max value of col7 and call it m, and as I name the table c, it will be c.m


#6

Maybe there is no seqName.NextVal statement as Oracle. So , I was confused. Thanks again


#7

Normally we'd use identity column for that.