| Read different number of records. [message #576003] |
Wed, 30 January 2013 08:05  |
 |
ErnstErnst
Messages: 17 Registered: January 2013
|
Junior Member |
|
|
I know how to use cursors to read all the records in a table.
But how can records that should be processed together be read in a loop, using cursors?
If there were, for instance persons from different nationalities, French, German, English, ..., I woluld like to read the first unknown number of persons from France and process that data, and then continue with the unknown number of germans and process that, and so on.
I would be very grateful if I could get som help with this problem in PL/SQL, because I want to learn PL/SQL.
Ernst
|
|
|
|
|
|
|
|
| Re: Read different number of records. [message #576006 is a reply to message #576003] |
Wed, 30 January 2013 08:43   |
 |
Michel Cadot
Messages: 54244 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.
Use a join on the 2 tables and only one cursor (loop), there is no need of 2 cursors.
Note that we don't do homework. If you want to learn PL/SQL then post what you already tried and we help you to go further.
Hint: you have to use a parametrized cursor.
Read PL/SQL User's Guide and Reference, Chapter 6 Performing SQL Operations from PL/SQL, section Managing Cursors in PL/SQL
Regards
Michel
|
|
|
|
| Re: Read different number of records. [message #576007 is a reply to message #576003] |
Wed, 30 January 2013 09:27   |
Solomon Yakobson
Messages: 1404 Registered: January 2010
|
Senior Member |
|
|
ErnstErnst wrote on Wed, 30 January 2013 09:05I woluld like to read the first unknown number of persons from France and process that data, and then continue with the unknown number of germans and process that, and so on.
create or replace
procedure process_persons(
p_german number,
p_french number,
p_english number
)
is
cursor v_cur
is
with t as (
select ...,
row_number() over(partition by nationality order by 1) rn
from persons
where nationality in ('german','french','english')
)
select *
from t
where (nationality = 'german' and rn <= p_german)
or (nationality = 'french' and rn <= p_french)
or (nationality = 'english' and rn <= p_english)
order by case nationality
when 'french' then 1
when 'german' then 2
else 3
end,
rn;
begin
for v_rec in v_cur loop
do-processing
end loop;
end;
/
Now all you need is to call stored procedure and provide how mane rows for each nationality to process. However using separate cursors might be more efficient.
SY.
[Updated on: Wed, 30 January 2013 09:30] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Read different number of records. [message #576168 is a reply to message #576109] |
Fri, 01 February 2013 02:11   |
 |
ErnstErnst
Messages: 17 Registered: January 2013
|
Junior Member |
|
|
Table1 contains the columns Nationality and Var1. (Simplified)
With this code I get the different nationalities into r_nat.
Then I want to read the varying number of records for each r_nat.
Where is the best place to put the next loop, outside or inside the existing one? I don't know how to declare the new cursor. I want to get the variable Var1 into a vector for processing.
declare
cursor c_nat is select distinct nationality from table1;
r_nat c_nat%rowtype;
begin
open c_nat;
loop
fetch c_nat into r_nat;
exit when c_nat%notfound;
end loop;
close c_nat;
end;
[Edit MC: format code]
[Updated on: Fri, 01 February 2013 02:21] by Moderator Report message to a moderator
|
|
|
|
| Re: Read different number of records. [message #576169 is a reply to message #576168] |
Fri, 01 February 2013 02:17   |
 |
Michel Cadot
Messages: 54244 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Wed, 30 January 2013 15:43Welcome to the forum.
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals...
Note how you code is now easier to read after I formatted it.
Regards
Michel
[Updated on: Fri, 01 February 2013 02:20] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
| Re: Read different number of records. [message #576204 is a reply to message #576203] |
Fri, 01 February 2013 06:31   |
cookiemonster
Messages: 9169 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
|
Oh and your last sample code only processes the data for one random nationality. You would need to nest the loops. But since your just getting all the nationalities from the same table it seems pointless.
|
|
|
|
|
|
|
|
| Re: Read different number of records. [message #576222 is a reply to message #576217] |
Fri, 01 February 2013 08:00   |
cookiemonster
Messages: 9169 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
ErnstErnst wrote on Fri, 01 February 2013 13:53I want the values in column Var1 into a variable X.
If a certain nationality had eg 3 records then there should be the values X(1), X(2) and X(3) to process in different complicated ways. So I want to know that there is 3 to deal with. Then if the next nationality had 2 records then the values should be put into X(1) and X(2) and so on.
So load the data into an array. PL/SQL does have them.
I'm still failing to see why it matters how many records there are for a given nationality though. What do you do differently if there are two records vs three records?
|
|
|
|
| Re: Read different number of records. [message #576223 is a reply to message #576220] |
Fri, 01 February 2013 08:07   |
 |
ErnstErnst
Messages: 17 Registered: January 2013
|
Junior Member |
|
|
I don't know all the details in the syntax. That's the problem. By the way, I got a personal message from someone, criticizing me when I said that more experienced people could help me in 30 seconds.
Another problem is when your data are confidential, you have to construct false data.
[Updated on: Fri, 01 February 2013 08:23] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
| Re: Read different number of records. [message #576228 is a reply to message #576224] |
Fri, 01 February 2013 09:24   |
cookiemonster
Messages: 9169 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Do you really think this:
ErnstErnst wrote on Fri, 01 February 2013 14:49
I must know how many X-values(Var1) there is to process(in complicated ways).
anwers this:
cookiemonster wrote on Fri, 01 February 2013 14:37
I'd say the main problem is you still haven't told us what difference the number of rows for a given nationality actual makes to what the code is supposed to do.
Cause it doesn't.
If you just need to report the counts for each nationality then you can just count them as you go along, using my framework above and report them at the end.
If the counts make some difference to what the code actually does then you need to explain what that difference is.
We really can't help improve your code if we have no idea what it's supposed to do.
And at the moment we don't have any idea because you haven't told us.
We understand that the counts matter to you, but we need to understand why.
|
|
|
|
|
|
| Re: Read different number of records. [message #576298 is a reply to message #576296] |
Sat, 02 February 2013 16:19   |
cookiemonster
Messages: 9169 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
So use count in it's analytic form to work out how many records there are of each nationality as you go along. Something like:
BEGIN
FOR rec IN (SELECT t.*, count(*) over (partition by nationality) count_per_nationality FROM table1 t ORDER BY nationality) LOOP
IF rec.count_per_nationality <= 10 THEN
--DO SOMETHING HERE
ELSIF rec.count_per_nationality BETWEEN 11 AND 20 THEN
--DO SOMETHING ELSE HERE
ELSIF ........
END IF;
END LOOP;
END;
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Read different number of records. [message #576369 is a reply to message #576367] |
Mon, 04 February 2013 03:08   |
 |
Michel Cadot
Messages: 54244 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Please READ the link I pointed you to, it contains ALL information about cursor and cursor loop (for instance in the section "Querying Data with PL/SQL: Explicit Cursor FOR Loops").
You are wasting everyone time with your tutorial questions.
Forum is not a replacement for documentation, it you want to learn YOU need to read it, it is mandatory otherwise the only thing you will get is that you will see we know PL/SQL but you NO.
Regards
Michel
[Updated on: Mon, 04 February 2013 03:09] Report message to a moderator
|
|
|
|
|
|
| Re: Read different number of records. [message #576376 is a reply to message #576375] |
Mon, 04 February 2013 04:31   |
 |
Michel Cadot
Messages: 54244 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Mon, 04 February 2013 08:42 "rec" is the loop variable just like I in FOR I in 1..10 LOOP". It is a record containing all columns of your loop query.
Please READ the link I pointed you to, it contains ALL information about cursor and cursor loop (for instance in the section "Querying Data with PL/SQL: Explicit Cursor FOR Loops").
Regards
Michel
And ONLY these columns.
[Updated on: Mon, 04 February 2013 04:32] Report message to a moderator
|
|
|
|
| Re: Read different number of records. [message #576383 is a reply to message #576375] |
Mon, 04 February 2013 06:27  |
cookiemonster
Messages: 9169 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
ErnstErnst wrote on Mon, 04 February 2013 10:29
So I tried with both DBMS_OUTPUT.PUT_LINE(X) and DBMS_OUTPUT.PUT_LINE(t.X) inside the loop.
I get t´he message "identifier 'X' must be declared".
Should be:
DBMS_OUTPUT.PUT_LINE(rec.X)
rec is a record type that stores the results of the query. So to see the results of the query you have to reference rec.
You really need to read the documentation on how this works.
|
|
|
|