DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: PL/SQL help with loops, method and explanation for Pl/SQL loops

  1. #1
    Join Date
    Dec 2007
    Posts
    9

    PL/SQL help with loops, method and explanation for Pl/SQL loops

    All,

    I am new to PL/SQL and just can't seem to get the syntax right.

    I am a PL/SQL newbie trying to create a package that will get a result (ID) from a query from two tables and then turn around and loop through the one or more records found and do an insert into a another based on
    a select from the previous query. This is what I have now:



    DECLARE
    rec_clarity_tdl clarity_tdl%ROWTYPE
    rec_clarity_ser clarity_ser%ROWTYPE


    BEGIN

    CURSOR my_cursor IS

    SELECT performing_prov_id from clarity_tdl
    MINUS
    SELECT not in clarity_ser_billing;
    --returns performing_prov_id
    --550130


    LOOP

    FOR prov_rec IN my_cursor
    INSERT INTO provider_addl
    VALUES (select * from provider where provid = prov_rec
    -- one record by one from cursor
    END LOOP;

    END
    /

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    whats the problem you are having?

  3. #3
    Join Date
    Dec 2007
    Posts
    9
    Simply put, I am trying to get the results of one query and use it loop through and put it into an insert on another table.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    so what's the problem? all you have done is put some code down and not said what the problem with it is

  5. #5
    Join Date
    Dec 2007
    Posts
    9
    I am trying to create a package that will get the results of a query (a field on a table) and then turn around and get those results and plug them into a looping insert stmt on another table. Do I declare both tables?

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    define "declare"
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Dec 2007
    Posts
    9
    Declaring the two tables I need under the declare section with %ROWTYPE...

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    why would you do that?

    I understand your business logic is something like 1) load a cursor -where you have to declare something then 2) loop into the cursor inserting into a second table something you get from a third table. Is that correct?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width