Forum Stats

  • 3,734,032 Users
  • 2,246,861 Discussions
  • 7,857,003 Comments

Discussions

procedure for inserting rows

User_G6P8Z
User_G6P8Z Member Posts: 129 Blue Ribbon
edited August 2010 in SQL & PL/SQL
dear members,

i have a table with some missing numbers in a col1.
how can i add rows containing missing numbers.
starting and ending number is given in the procedure.
let us say starting from 1 to 10.

table
id col1
1 2
2 3
3 4
4 9


Required Result after running the procedure.

table
id col1

1 2
2 3
3 4
4 9
5 1
6 5
7 6
8 7
9 8
10 10


can i have that procedure.



regards


teefu.

Edited by: user_teefu on Aug 23, 2010 2:19 PM

Best Answer

  • 780914
    780914 Member Posts: 1,095
    Accepted Answer
    Check this procedure.....

    create or replace procedure p1 (p_st number, p_end number) is
    cursor c1 is
    select col1 from tabname;
    k number;
    begin
    for i in p_st..p_end
    loop
       for j in c1
       loop
       if j.col1 = i then
       k := 1;
       exit;
       else
       k := 0;
       end if;
       end loop;
       if k=0 then
       insert into tabname(col1) values (i);
       end if;
    end loop;
    end;
    
    
    SQL> select * from tabname;
    
          COL1
    ----------
             1
             4
             5
    
    SQL> exec p1(1,10);
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from tabname;
    
          COL1
    ----------
             1
             4
             5
             2
             3
             6
             7
             8
             9
            10
    
    10 rows selected.

Answers

  • 788101
    788101 Member Posts: 11
    You cannot add rows containing missing numbers
    using sequence.

    You can create a new sequence
  • User_G6P8Z
    User_G6P8Z Member Posts: 129 Blue Ribbon
    dear user

    the primary key is the col. ID

    and the missing numbers is the column col1.




    regards.



    teefu.
  • If you know the max value of tab.col_1, you can write the following query in a cursor and insert it with the sequence you use for tab.id, in a loop.

    select *
    from
    (
    select level col_1
    from dual
    connect by level <=*100*) t2
    WHERE NOT EXISTS ( select 1
    from tab t1
    where t2.col_1 = t1.col_1)

    This is assuming 100 is the maximum value available for col_1
  • 780914
    780914 Member Posts: 1,095
    Accepted Answer
    Check this procedure.....

    create or replace procedure p1 (p_st number, p_end number) is
    cursor c1 is
    select col1 from tabname;
    k number;
    begin
    for i in p_st..p_end
    loop
       for j in c1
       loop
       if j.col1 = i then
       k := 1;
       exit;
       else
       k := 0;
       end if;
       end loop;
       if k=0 then
       insert into tabname(col1) values (i);
       end if;
    end loop;
    end;
    
    
    SQL> select * from tabname;
    
          COL1
    ----------
             1
             4
             5
    
    SQL> exec p1(1,10);
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from tabname;
    
          COL1
    ----------
             1
             4
             5
             2
             3
             6
             7
             8
             9
            10
    
    10 rows selected.
  • 788101
    788101 Member Posts: 11
    Yes,

    then you can create a new sequence from last number of sequence.
  • kordirko
    kordirko Member Posts: 227
    This works on Oracle 10 and above
    select * from xxx;
    
    ID                     COLL                   
    ---------------------- ---------------------- 
    1                      2                      
    2                      3                      
    3                      4                      
    4                      9 
    
    
    variable p_from NUMBER;
    variable p_to NUMBER;
    
    BEGIN
      :p_from := 1;
      :p_to := 10;
    END;
    /
    
    INSERT INTO XXX
    SELECT ROWNUM + nvl(( SELECT MAX( ID ) FROM xxx ), 0 ) id ,
           COLL
    FROM (
      SELECT COLL
      FROM (
          SELECT LEVEL + :p_from - 1 COLL
          FROM dual
          CONNECT BY LEVEL <= :p_to 
      )
      MINUS
      SELECT COLL FROM XXX
    );
    
    select * from xxx;
    
    ID                     COLL                   
    ---------------------- ---------------------- 
    1                      2                      
    2                      3                      
    3                      4                      
    4                      9                      
    5                      1                      
    6                      5                      
    7                      6                      
    8                      7                      
    9                      8                      
    10                     10                     
    
    10 rows selected
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    edited August 2010
    SQL> create table mytable (id,col1)
      2  as
      3  select 1, 2 from dual union all
      4  select 2, 3 from dual union all
      5  select 3, 4 from dual union all
      6  select 4, 9 from dual
      7  /
    
    Table created.
    
    SQL> create sequence myseq start with 5
      2  /
    
    Sequence created.
    
    SQL> create procedure p
      2  ( p_start in pls_integer
      3  , p_end   in pls_integer
      4  )
      5  is
      6  begin
      7    insert into mytable
      8    ( id
      9    , col1
     10    )
     11    select myseq.nextval
     12         , n
     13      from ( select p_start - 1 + level as n
     14               from dual
     15            connect by level <= p_end - p_start + 1
     16              minus
     17             select col1
     18               from mytable
     19              order by 1
     20           )
     21    ;
     22  end p;
     23  /
    
    Procedure created.
    
    SQL> exec p(1,10)
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from mytable
      2  /
    
            ID       COL1
    ---------- ----------
             1          2
             2          3
             3          4
             4          9
             5          1
             6          5
             7          6
             8          7
             9          8
            10         10
    
    10 rows selected.
    Regards,
    Rob.
    Rob van Wijk
  • User_G6P8Z
    User_G6P8Z Member Posts: 129 Blue Ribbon
    dear SBH
    its working with your set of syntax.

    can u chech with my table and ammended procedure that what is the error.
    i am sending extractions.

    CREATE TABLE temp
    (id NUMBER,
    plot_no VARCHAR2(20),
    phase NUMBER,
    block VARCHAR2(10))
    )
    /


    PROCEDURE NEW_PLOTS_TMP(p_block varchar2,p_phase number,p_st number, p_end number) is
    cursor c1 is
    select plot_no from temp
    where block=p_block
    and phase=p_phase;

    k number;
    v_id number;

    begin

    for i in p_st..p_end
    loop
    for j in c1
    loop
    if j.plot_no = i then
    k := 1;
    exit;
    else
    k := 0;
    end if;
    end loop;
    if k=0 then

    select nvl(max(id),0)+1 into v_id from temp;

    insert into temp(id,plot_no)
    values (v_id,i);

    v_id := v_id + 1;

    end if;
    end loop;
    commit;
    end;




    regards,


    teefu
  • Nicosa-Oracle
    Nicosa-Oracle Member Posts: 1,319 Employee
    edited August 2010
    Hi,

    I would do something like that :
    create or replace procedure t_addrows(
            p_min integer,
            p_max integer)
    as
            l_inserted integer;
    begin
            insert into t (id, col1)
            select
                    idstart+rownum id,
                    col1
            from (
                    select idstart, p_min+level-1 as col1
                    from (
                            select nvl(max(id),0) idstart
                            from t
                    )
                    connect by level <= p_max-p_min+1
            ) n     
            where not exists (
                    select null
                    from t
                    where t.col1=n.col1
            );
            l_inserted := SQL%ROWCOUNT;
            dbms_output.put_line('inserted : '||l_inserted);
    end;
    /
    It gets the max(id) from the table to generate "following" ids.
    it uses the connect by level to generate integers between min and max parameters provided.
    it does a NOT EXISTS to avoid primary key errors.

    Example :
    [email protected] SQL>select * from t;
    
            ID       COL1
    ---------- ----------
             1          2
             2          3
             3          4
             4          9
    
    [email protected] SQL>begin
      2  t_addrows(1,10);
      3  end;
      4  /
    inserted : 6
    
    PL/SQL procedure successfully completed.
    
    [email protected] SQL>select * from t;
    
            ID       COL1
    ---------- ----------
             1          2
             2          3
             3          4
             4          9
             5          5
             6          8
             7          1
             8         10
             9          6
            10          7
    
    10 rows selected.
    NB :_ I didn't include a COMMIT in the procedure. You decide wheither it should be there or not.
  • 780914
    780914 Member Posts: 1,095
    select nvl(max(id),0)+1 into v_id from temp;


    This SELECT , you must outside the main for loop, just after the BEGIN. This is because selection of maximum existing id has to be done one time only. Next time, it has to be just incremented by 1.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited August 2010
    I like model clause B-)
    And Tabibitosan method.
    with t(ID,col1) as(
    select 1,2 from dual union
    select 2,3 from dual union
    select 3,4 from dual union
    select 4,9 from dual)
    select *
      from t
     model
    dimension by(ID)
    measures(col1,0 as rn,0 as willSet)
    rules iterate (10) (
    rn[any]= Row_Number() over(order by col1),
    willSet[iteration_number+1] = nvl(max(case when rn = col1
                                               then rn+1 end)[any]
                                     ,1),
    col1[iteration_number+1] = case when col1[cv()] is present
                                    then col1[cv()]
                                    else willSet[cv()] end);
    
    ID  col1    rn  willSet
    --  ----  ----  -------
     1     2     2        1
     2     3     3        1
     3     4     4        1
     4     9     9        1
     5     1     1        1
     6     5     5        5
     7     6     6        6
     8     7     7        7
     9     8     8        8
    10    10  null       10
  • User_G6P8Z
    User_G6P8Z Member Posts: 129 Blue Ribbon
    Dear SBH

    thanks for helping me out of this.

    the rest of the replies, i will try and then mark as helpful or not.



    thanks forum


    teefu.
  • User_G6P8Z
    User_G6P8Z Member Posts: 129 Blue Ribbon
    dear Ron Van Wrik,

    thanks for your concern. I have already marked SBH as correct. and its rather easy approach.
    anyways once again thanks for the help.


    regards


    teefu.
This discussion has been closed.