Home » SQL & PL/SQL » SQL & PL/SQL » Read different number of records.
Read different number of records. [message #576003] Wed, 30 January 2013 08:05 Go to next message
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 #576004 is a reply to message #576003] Wed, 30 January 2013 08:10 Go to previous messageGo to next message
joy_division
Messages: 4515
Registered: February 2005
Location: East Coast USA
Senior Member
Use two cursors?
Re: Read different number of records. [message #576005 is a reply to message #576004] Wed, 30 January 2013 08:31 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
Your workflow isn't very clear for me - may be you could use cursors with parameters (natonality)?
Re: Read different number of records. [message #576006 is a reply to message #576003] Wed, 30 January 2013 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2031
Registered: January 2010
Senior Member
ErnstErnst wrote on Wed, 30 January 2013 09:05
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.


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 #576079 is a reply to message #576007] Thu, 31 January 2013 06:46 Go to previous messageGo to next message
ErnstErnst
Messages: 17
Registered: January 2013
Junior Member
Thank you, Solomon!

But I don't know the numbers of each nationality. This kind of question must be a basic problem when reading from data tables.

Get the first nationality, if any, continue to read until a new nationality. Process the data of the first nationality,then start to read again where you stopped earlier.
I believe it should be possible to solve without declaring a special "remembering" variable for the new nationality. I got stuck with this problem and PL/SQL syntax is unfamiliar to me.

Nested loops is the answer I suppose. Thanks if you could help me with the crucial detail.
Re: Read different number of records. [message #576080 is a reply to message #576079] Thu, 31 January 2013 06:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2031
Registered: January 2010
Senior Member
That is why I wrote a stored procedure. When you find out the numbers you simply call SP:

> process_persons(10,25,99);

Then it will process 100 germans, 25 french and 99 englishmen.

SY.
Re: Read different number of records. [message #576094 is a reply to message #576080] Thu, 31 January 2013 07:27 Go to previous messageGo to next message
ErnstErnst
Messages: 17
Registered: January 2013
Junior Member
Sorry, I don't want to use a stored procedure. It should be possible "my way".
Re: Read different number of records. [message #576102 is a reply to message #576094] Thu, 31 January 2013 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you are learning PL/SQL, post what you tried and tell us why it does not work or where you are stuck.
And explain what is "your way".

Regards
Michel
Re: Read different number of records. [message #576106 is a reply to message #576102] Thu, 31 January 2013 08:48 Go to previous messageGo to next message
ErnstErnst
Messages: 17
Registered: January 2013
Junior Member
It's all there to read. If you really want to help me, I think you can in 30 seconds.

There is no use with posting code where all records are read.

Re: Read different number of records. [message #576109 is a reply to message #576106] Thu, 31 January 2013 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to learn, post what you did; we are not here to make your homework, we are here to help you to learn so that we will no more have to help you as you will know how to do it.

And in the end, post the final code so that the future readers can learn from your topic and posts.

Regards
Michel
Re: Read different number of records. [message #576168 is a reply to message #576109] Fri, 01 February 2013 02:11 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 30 January 2013 15:43
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...


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 #576170 is a reply to message #576169] Fri, 01 February 2013 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't know how to declare the new cursor. I want to get the variable Var1 into a vector for processing.


Michel Cadot wrote on Wed, 30 January 2013 15:43
...
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


[Updated on: Fri, 01 February 2013 02:20]

Report message to a moderator

Re: Read different number of records. [message #576193 is a reply to message #576170] Fri, 01 February 2013 04:40 Go to previous messageGo to next message
ErnstErnst
Messages: 17
Registered: January 2013
Junior Member
I got the message: "PL/SQL procedure successfully completed". My code looks (almost) like this:

Do you have any comment? If the code works it doesn't mean that it's the best way to solve this.

declare
cursor c_nat is select distinct nationality from table1;
r_nat c_nat%rowtype;

cursor cur(country varchar) is select * from table1
where nationality = country);
r_cur cur%rowtype;

begin
open c_nat;
loop
fetch c_nat into r_nat;
exit when c_nat%notfound;
end loop;
begin
open cur(r_nat.nationality);
loop
fetch cur into r_cur;
exit when cur%notfound;
end loop;
-- DO SOMETHING HERE
close cur;
end;
close c_nat;
end;


Re: Read different number of records. [message #576203 is a reply to message #576193] Fri, 01 February 2013 06:29 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Please read and follow How to use [code] tags and make your code easier to read? as you've already been asked to do.
2)
I don't see any need for 2 cursors. Plus you should read up on for loops:
BEGIN

  FOR rec IN (SELECT * FROM table1 ORDER BY nationality) LOOP
    
    --DO SOMETHING HERE

  END LOOP;

END;
Re: Read different number of records. [message #576204 is a reply to message #576203] Fri, 01 February 2013 06:31 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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 #576217 is a reply to message #576204] Fri, 01 February 2013 07:53 Go to previous messageGo to next message
ErnstErnst
Messages: 17
Registered: January 2013
Junior Member
I 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.

I have to use PL/SQL. I haven't chosen it myself, and NO it's not a homework This would be a simple problem to solve in an ordinary programming language like C#.

Re: Read different number of records. [message #576220 is a reply to message #576217] Fri, 01 February 2013 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is also a simple problem to solve in PL/SQL.
Besides just do it in the same that you would in C#.

Regards
Michel
Re: Read different number of records. [message #576222 is a reply to message #576217] Fri, 01 February 2013 08:00 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
ErnstErnst wrote on Fri, 01 February 2013 13:53
I 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 Go to previous messageGo to next message
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 #576224 is a reply to message #576223] Fri, 01 February 2013 08:37 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
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.
Re: Read different number of records. [message #576225 is a reply to message #576224] Fri, 01 February 2013 08:49 Go to previous messageGo to next message
ErnstErnst
Messages: 17
Registered: January 2013
Junior Member
This is probably a stupid answer.

I must know how many X-values(Var1) there is to process(in complicated ways).
Re: Read different number of records. [message #576226 is a reply to message #576225] Fri, 01 February 2013 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why?

Regards
Michel
Re: Read different number of records. [message #576228 is a reply to message #576224] Fri, 01 February 2013 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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 #576296 is a reply to message #576228] Sat, 02 February 2013 12:13 Go to previous messageGo to next message
ErnstErnst
Messages: 17
Registered: January 2013
Junior Member
As I said, the rules for processing the Var1 values are complicated and they depend on how many the Var1 values are.

If the number(N) are <= 10 I have rules A to follow, if 11 <= N <= 20, I have rules B to follow, and so on.
Re: Read different number of records. [message #576298 is a reply to message #576296] Sat, 02 February 2013 16:19 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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 #576363 is a reply to message #576298] Mon, 04 February 2013 01:37 Go to previous messageGo to next message
ErnstErnst
Messages: 17
Registered: January 2013
Junior Member
Thank you, cookiemonster.

In the PL/SQL documentation I have seen the cursor for loop:

FOR record_index in cursor_name
LOOP
{.statements.}
END LOOP;

Then I would think that "record_index" is an existing column in the cursor. Is that correct?

You have a SELECT statement instead of a cursor name. So what is your "rec"?

Re: Read different number of records. [message #576365 is a reply to message #576363] Mon, 04 February 2013 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"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
Re: Read different number of records. [message #576367 is a reply to message #576365] Mon, 04 February 2013 02:58 Go to previous messageGo to next message
ErnstErnst
Messages: 17
Registered: January 2013
Junior Member
The documentation says: "You must declare an explicit cursor for queries that return more than one row.".

So cookiemonsters code is missing a declaration, I suppose? But when you declare a cursor you make a SELECT statement. This confuses me.

Re: Read different number of records. [message #576368 is a reply to message #576367] Mon, 04 February 2013 03:06 Go to previous messageGo to next message
Littlefoot
Messages: 19626
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
"You must declare an explicit cursor for queries that return more than one row.".

Otherwise, as in PL/SQL you have to SELECT INTO, a query that returns more than a single record fails with TOO-MANY-ROWS. Therefore, you need to use a cursor.

Cookiemonster's code doesn't miss cursor declaration - he used a cursor FOR loop where "cursor declaration" goes into the FOR loop directly. See the difference (although the result is the same):
declare
  cursor c1 is select dname from dept;
begin
  for c1r in c1 loop
    ...
  end loop;
end;

begin
  for c1r in (select dname from dept) loop
    ... 
  end loop;
end;
Re: Read different number of records. [message #576369 is a reply to message #576367] Mon, 04 February 2013 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
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 #576375 is a reply to message #576369] Mon, 04 February 2013 04:29 Go to previous messageGo to next message
ErnstErnst
Messages: 17
Registered: January 2013
Junior Member
I use code like cookiemonsters

FOR rec IN (SELECT t.*, count(*) over (partition by nationality) count_per_nationality FROM table1 t ORDER BY nationality) LOOP
___

To see that everything works,I want to print a column from what is called t above. Let's say that the column is called X.

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".

If I declare it, I get the message "name is already used by an existing object".

Any comments on that?

Re: Read different number of records. [message #576376 is a reply to message #576375] Mon, 04 February 2013 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
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 Go to previous message
cookiemonster
Messages: 10961
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.
Previous Topic: need to different columns
Next Topic: Oracle to SQL Server
Goto Forum:
  


Current Time: Sat Sep 20 03:06:37 CDT 2014

Total time taken to generate the page: 0.07145 seconds