Home » SQL & PL/SQL » SQL & PL/SQL » cursor parameters
cursor parameters [message #270127] Tue, 25 September 2007 17:18 Go to next message
apollo
Messages: 20
Registered: September 2007
Junior Member
I'm just beginning to look into cursor parameters. I have a cursor who's data depends on the value of another cursor. I'm wondering if cursor parameters can accomplish this. I'm making up the following code to show what I'd like to do. Let's say cursor_1 holds several store_id's, and cursor_2 holds department_id's and it uses one store_id at a time. Would cursor_2 loop through every store_id? (I'm making this code up so please excuse syntax errors).

DECLARE
  cursor cursor_1 is
  select store_id from stores;
  cursor cursor_2(storeId number(10)) is
  select department_id from departments;
BEGIN
  for c_1 as cursor_1 loop
    for c_2 as cursor_2(c_1.store_id) loop
      ...
    end loop;
  end loop;
END
Re: cursor parameters [message #270130 is a reply to message #270127] Tue, 25 September 2007 17:30 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
You are Passing a parameter to the cursor, and then not using it. Have you tried to run this code? What happened when you did?
Re: cursor parameters [message #270147 is a reply to message #270127] Tue, 25 September 2007 22:41 Go to previous messageGo to next message
apollo
Messages: 20
Registered: September 2007
Junior Member
I have not had a chance to try it yet. I was just wondering if that would work in theory.
Re: cursor parameters [message #270165 is a reply to message #270147] Wed, 26 September 2007 00:36 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That would work in THEORY, just use "select department_id from departments where departments.store_id=storeId".
But this is likely not the better way to do it.
You first have to ask you: "can I do it with only SQL?"
If not, then: "can I do it with BULK operations?"

Regards
Michel
Previous Topic: Unique Key when Not null behaves same as Priamary Key....Then what is difference.
Next Topic: utl_file : invalid_path error
Goto Forum:
  


Current Time: Sat Dec 10 05:09:37 CST 2016

Total time taken to generate the page: 0.09028 seconds