-
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
/
-
whats the problem you are having?
-
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.
-
so what's the problem? all you have done is put some code down and not said what the problem with it is
-
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?
-
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.
-
Declaring the two tables I need under the declare section with %ROWTYPE...
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|