Home » SQL & PL/SQL » SQL & PL/SQL » select_question (PL/SQL)
select_question [message #564020] Mon, 20 August 2012 08:02 Go to next message
Zara
Messages: 3
Registered: August 2012
Junior Member
Hi All

Guys , would you tell me is there any function that helps to select the rows from a table with the same particular field in PL/SQL.
Actually I don't want to write two loops one inserted into another.
Re: select_question [message #564021 is a reply to message #564020] Mon, 20 August 2012 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Your question is not clear.
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" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: select_question [message #564022 is a reply to message #564020] Mon, 20 August 2012 08:19 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Sara,

You can write pl/sql cursor like :


 DECLARE

   CURSOR c1
   IS
     
     SELECT   *
     FROM      tab1;

   CURSOR C2 
   IS

     SELECT   *
     FROM      tab2;

 BEGIN

  FOR i IN C1
  LOOP
   
   // implement your logic
  
    FOR j IN C2
    LOOP
   
      //implement your logic

    END LOOP;
  
  END LOOP

END;



[Updated on: Mon, 20 August 2012 08:20]

Report message to a moderator

Re: select_question [message #564024 is a reply to message #564022] Mon, 20 August 2012 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And OP said: "Actually I don't want to write two loops one inserted into another." and you posted EXACTLY what it is excluded, congratulations.
Now people know what must NOT be posted, thanks.

Regards
Michel
Re: select_question [message #564092 is a reply to message #564024] Tue, 21 August 2012 06:50 Go to previous messageGo to next message
Zara
Messages: 3
Registered: August 2012
Junior Member
thank you guys for replying
I just didn't want to write my code this way and thought that maybe there is a function, which works faster than double loop, cause I have a row consisting of for about 2 million rows and this code is too slow


declare 
count_MAP_MANS number; 
--declare a variable that holds the number of rows with particular DOC_NUMBER 
cur_rec REPADMIN.MAP_MANS%rowtype;
cursor cur_map_mans is select * from REPADMIN.MAP_MANS; 
-- declare a cursor that loops throw map_mans table
cur_rec_same REPADMIN.MAP_MANS%rowtype;
cursor cur_map_mans_same is select * from REPADMIN.MAP_MANS;



BEGIN
  open cur_map_mans;
  LOOP
  fetch cur_map_mans into cur_rec;
   EXIT WHEN cur_map_mans%NOTFOUND;
   LOOP
     fetch cur_map_mans_same into cur_rec_same;
     EXIT WHEN cur_map_mans_same%NOTFOUND;
  
   insert when cur_rec_same.DOC_NUMBER = cur_rec.DOC_NUMBER then into ZARA.TEMP_SAME_DOC_NUM 
values(cur_rec.F_NAME, cur_rec.L_NAME, cur_rec.M_NAME,
  cur_rec.SEX, cur_rec.BIRTH_DATE, cur_rec.DOC_NUMBER, cur_rec.DOC_TYPE,
  cur_rec.EXP_DATE, cur_rec.APPROX_BIRTH_DATE, cur_rec.ID_MAN);
  
  -- do some other job 
  
  delete * from TEMP_SAME_DOC_NUM 
  
     END LOOP;

  END LOOP;
END;

[Updated on: Tue, 21 August 2012 07:55] by Moderator

Report message to a moderator

Re: select_question [message #564094 is a reply to message #564092] Tue, 21 August 2012 06:54 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can just combine the selects into a single cursor. There's nothing complicated about that.
Depending what else your code does you may be able to rewrite it as a single insert select.
Re: select_question [message #564270 is a reply to message #564092] Wed, 22 August 2012 07:49 Go to previous messageGo to next message
Zara
Messages: 3
Registered: August 2012
Junior Member
Please would you write what you said as a code.

Also , I can 'group by' by DOC_NUMBER, but I don't know how to put this grouped part of the table MAP.MANS into ZARA.TEMP_SAME_DOC_NUM table.
Re: select_question [message #564274 is a reply to message #564270] Wed, 22 August 2012 08:22 Go to previous message
Michel Cadot
Messages: 59499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you want to group by? There is no group by in your code!
There is no specification of what you want to do how could someone write anything to achieve it? Explain!

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" button to verify.

Regards
Michel
Previous Topic: ORA-29280: invalid directory path - Using UTL_FILE
Next Topic: Unable to view package body in SQLplus using DBA_SOURCE table
Goto Forum:
  


Current Time: Fri Oct 31 01:18:32 CDT 2014

Total time taken to generate the page: 0.08769 seconds