1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Add Another Insert

Discussion in 'SQL PL/SQL' started by Amit.Sagpariya, Apr 10, 2009.

  1. Amit.Sagpariya

    Amit.Sagpariya Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    Could you please let me know where to put Insert statement in following scenario. I have a procedure

    Code (Text):
    CREATE PROCEDURE...
    variable declaration
    Cursor declaration
    BEGIN
    OPEN cursor
        FETCH...
            IF (condition meet)

            BEGIN

                Insert into X values (xx, xy, xz)

            EXCEPTION
                exception handling;
            END
            END IF

    Close cursor
    end
    Now my doubt is once loading in X table completed successfully(without an error), i need to write a Insert statement { insert into Z values (xx, xz) }. I do not understand where to write this statement, as it should also handle the exception part. Could you please help me on this?
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    ok i am in a hurry so trying to reply quickly

    you need to do this
    Code (Text):

    create procedure procedure_name
    Cursor declaration;
    variable declaration;
    begin
    loop through cursor -- (ex. for c1rec in C1)
    if condition met
    insert into X values
    else
    --statements--
    end if;
    end loop;
    --NOW WRITE YOUR NEW INSERT STATEMENT
    Exception handling
    end procedurename;
     
    Sorry for the crude formatting, running late...

    if you have problems, post your procedure code and i will try to correct it when i am back.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Here OP Wants to Populate z with almost same kind of data as in X. then better process is to do along with X But after X.(And not outside the Loop)

    Code (Text):
    CREATE PROCEDURE...
    variable declaration
    Cursor declaration
    BEGIN
    OPEN cursor
       
         LOOP
               FETCH...
           IF (condition meet)

                    -- Begin Block for X
            BEGIN

                Insert into X values (xx, xy, xz)

            EXCEPTION
                exception handling;
            END

                    -- Begin Block for Z
            BEGIN

                Insert into X values (xx, xy, xz)

            EXCEPTION
                exception handling;
            END

           END IF

               EXIT WHEN cursor%notfound;

         END LOOP;

    Close cursor
    end
    Note : Please ensure there is Exit statement TO EXIT the loop.

    It is not recommended to populate the Full data in X and get exception for Z . As it will lead to data inconsistency in application .