Forum Stats

  • 3,780,546 Users
  • 2,254,409 Discussions
  • 7,879,381 Comments

Discussions

Inserting even numbers by procedure HELP

xiobijaph
xiobijaph Member Posts: 12
edited Jan 21, 2019 9:41AM in SQL & PL/SQL

Hi,
I would like to insert even numbers by procedure in compartment 1 to 100.

I tried but it doesn't work:

CREATE TABLE numbers(number INT);

/

SET SERVEROUTPUT ON

/

CREATE OR REPLACE PROCEDURE insert_even IS

p_od NUMBER:=1;

p_do NUMBER:=100;

BEGIN

LOOP

p_od:=p_od+1;

IF mod(number,2)=0

then

INSERT INTO numbers values(number);

exit when p_od=p_do;

end if;

end loop;

end;

Thanks for advance

Tagged:
ManikGaz in OzxiobijaphBrunoVroman

Best Answer

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Jan 21, 2019 5:44AM Accepted Answer

    This can be done in single SQL like

    INSERT INTO numbers

    select lvl from

    (select level lvl from dual

       connect by level <=100

    )

    where mod(lvl,2)=0 ;

    By the way DO NOT name a column as NUMBER, this is bad practice as that is a Oracle key word.

    Assuming this is homework and you need to do like what teacher suggested then you can do something like

    CREATE OR REPLACE PROCEDURE insert_even IS

    BEGIN

    for i in 1..100 loop

       IF mod(i,2)=0

       then

        INSERT INTO numbers values(i);

       end if;

    end loop;

    end;

    CREATE OR REPLACE PROCEDURE insert_even IS

    p_od NUMBER:=1;

    p_do NUMBER:=100;

    BEGIN

      loop

       IF mod(p_od,2)=0

       then

        INSERT INTO numbers values(p_od);

       end if;

       p_od := p_po+1;

       exit when p_od>p_do;

    end loop;

    end;

    NOTE: I haven't tested any of the above, So you need to write, debug and test.

    xiobijaph
«1

Answers

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited Jan 21, 2019 5:42AM

    Seeing as your create table doesn't work, that's hardly surprising.

    You can't have a column called number.

    Your code refers to a variable called number that doesn't exist - and couldn't exist if you actually declared it due to the name. You probably want to use p_od instead.

    Fix your naming and try again. If you're still having problems post the revised code, along with the details of any errors you get and we'll help you out.

    xiobijaph
  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Jan 21, 2019 5:44AM Accepted Answer

    This can be done in single SQL like

    INSERT INTO numbers

    select lvl from

    (select level lvl from dual

       connect by level <=100

    )

    where mod(lvl,2)=0 ;

    By the way DO NOT name a column as NUMBER, this is bad practice as that is a Oracle key word.

    Assuming this is homework and you need to do like what teacher suggested then you can do something like

    CREATE OR REPLACE PROCEDURE insert_even IS

    BEGIN

    for i in 1..100 loop

       IF mod(i,2)=0

       then

        INSERT INTO numbers values(i);

       end if;

    end loop;

    end;

    CREATE OR REPLACE PROCEDURE insert_even IS

    p_od NUMBER:=1;

    p_do NUMBER:=100;

    BEGIN

      loop

       IF mod(p_od,2)=0

       then

        INSERT INTO numbers values(p_od);

       end if;

       p_od := p_po+1;

       exit when p_od>p_do;

    end loop;

    end;

    NOTE: I haven't tested any of the above, So you need to write, debug and test.

    xiobijaph
  • Manik
    Manik Member Posts: 2,906 Gold Trophy
    edited Jan 21, 2019 5:57AM
    Saubhik wrote:This can be done in single SQL likeINSERT INTO numbersselect lvl from(select level lvl from dual connect by level <=100)where mod(lvl,2)=0 ;

    Without subquery:------------------------SELECT LEVEL * 2 lvl      FROM DUALCONNECT BY LEVEL <= 50;Another way using sequence :create sequence seq_even start with 2 increment by 2;

    Cheers,

    Manik.

  • xiobijaph
    xiobijaph Member Posts: 12
    edited Jan 21, 2019 5:56AM

    I changed names and still doesn't work
    pastedImage_0.png

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited Jan 21, 2019 6:01AM

    Of course it doesn't.

    IF mod(another,2) =0

    That refers to a PL/SQL variable/parameter called 'another'.

    There's no such variable declared.

    There's a column of that name but you have no use for it at that point.

    Likewise

    INSERT INTO excercise values(another)

    Trying to insert the column into itself doesn't really make sense does it?

    Meanwhile you've got a variable called p_od which you are incrementing in the loop. Don't you think it would make more sense to use that instead?

  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited Jan 21, 2019 6:05AM

    Hello,

    you have already seen previous answers, I won't repeat the same; but apart form using a simple statement (INSERT ... LEVEL ... -I would insert " 2 * LEVEL"-), two additional remarks:

    -A- writing code like "p_od := 1; LOOP p_od := p_od + 1: IF mod( p_od, 2 ) = 0 THEN ... EXIT WHEN p_od = p_do ..." looks really sad...

    To give simply one example: you might jump the odd numbers: FOR i IN 1..50 LOOP INSERT ... 2 * i ... END LOOP;

    -B- and please take the 2 minutes necessary to modify your "display name" to something more human-friendly, currently you look like a "bot"... Please have a look at

    Best regards,

    Bruno Vroman.

    P.S. better late than never: Season's greetings, Oracle-mathematical way

    Manikxiobijaph
  • xiobijaph
    xiobijaph Member Posts: 12
    edited Jan 21, 2019 6:10AM
    Saubhik napisał(-a):This can be done in single SQL likeINSERT INTO numbersselect lvl from(select level lvl from dual connect by level <=100)where mod(lvl,2)=0 ;

    Yeah, its work, but I need to use procedure

    I used your second option

    pastedImage_1.png
    No errors, but still 'no data found'

    And third example:pastedImage_2.png

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Jan 21, 2019 6:17AM

    You need to RUN the procedure you have created. Creating procedure is different from EXECUTING the procedure.

    BEGIN

    your_procedure_name;

    END;

    xiobijaph
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Jan 21, 2019 7:32AM

    Using a while loop, here's an example anonymous PL/SQL block demonstrating, well, a while loop:

    declare  p_od NUMBER := 2;  p_do NUMBER := 100;begin  while true loop      -- INSERT INTO numbers (p_od) VALUES (p_od);      dbms_output.put_line(p_od);      p_od := p_od + 2;      exit when p_od > p_do;  end loop;end;/

    ...your variable naming is not good. Naming variables the same as the columns in a table you are trying to manipulate will result in maintenance issues because the code looks ambiguous.

    Manikxiobijaph
  • Manik
    Manik Member Posts: 2,906 Gold Trophy
    edited Jan 21, 2019 7:41AM
    Gaz in Oz wrote:Using a while loop, here's an example anonymous PL/SQL block demonstrating, well, a while loop:
    1. declare
    2. p_odNUMBER:=2;
    3. p_doNUMBER:=100;
    4. begin
    5. whiletrueloop
    6. --INSERTINTOnumbers(p_od)VALUES(p_od);
    7. dbms_output.put_line(p_od);
    8. p_od:=p_od+2;
    9. exitwhenp_od>p_do;
    10. endloop;
    11. end;
    12. /
    declare  p_od NUMBER := 2;  p_do NUMBER := 100; begin  while true loop  -- INSERT INTO numbers (p_od) VALUES (p_od);  dbms_output.put_line(p_od);  p_od := p_od + 2;  exit when p_od > p_do;  end loop; end; /
    ...your variable naming is not good. Naming variables the same as the columns in a table you are trying to manipulate will result in maintenance issues because the code looks ambiguous.

    Even if you remove 'while true'  it would work

    Cheers,

    Manik.

    Gaz in Ozxiobijaph