Home » SQL & PL/SQL » SQL & PL/SQL » Cursor Help with a Select Statement (Oracle 10g)
icon7.gif  Cursor Help with a Select Statement [message #431889] Thu, 19 November 2009 18:39 Go to next message
jtrink
Messages: 8
Registered: November 2009
Junior Member
Hello. I want to know if it is possible to create a cursor and use that cursor's primary key to match another table's foreign key? I'm new to pl/sql so I'm going to need some help. I was thinking of something like this...



IS

cursor c1 is 
   select * from table1;

BEGIN

select * from table2 where table2.foreign_key = c1.primary_key;



I'm not sure what to put in the begin statement?

Any help would be greatly appreciated!
Re: Cursor Help with a Select Statement [message #431891 is a reply to message #431889] Thu, 19 November 2009 18:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
No need for PL/SQL; just do plain SQL

select * from table2 where table2.foreign_key IN (SELECT primary_key from table1);

Re: Cursor Help with a Select Statement [message #431892 is a reply to message #431889] Thu, 19 November 2009 19:01 Go to previous messageGo to next message
jtrink
Messages: 8
Registered: November 2009
Junior Member
well I need to pass this 'result table' into another procedure...

I thought the easiest way would be to create a cursor?

Is this possible though, "create a cursor and use that cursor's primary key to match another table's foreign key?"
Re: Cursor Help with a Select Statement [message #431893 is a reply to message #431892] Thu, 19 November 2009 19:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
DECLARE
   CURSOR C1 IS 
       select * from table2 where table2.foreign_key IN (SELECT primary_key from table1);
BEGIN



By the way, it is POOR programming to use wild card ("*") in Production code.

[Updated on: Thu, 19 November 2009 19:51]

Report message to a moderator

Re: Cursor Help with a Select Statement [message #431895 is a reply to message #431889] Thu, 19 November 2009 20:12 Go to previous messageGo to next message
jtrink
Messages: 8
Registered: November 2009
Junior Member
BlackSwan, thank you for your help. Yes, I realize about not using '*', I was just trying to type up an example quick.

I'll try and apply this logic to my problem and let you know how it turns out!
Re: Cursor Help with a Select Statement [message #431897 is a reply to message #431895] Thu, 19 November 2009 22:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I'll try and apply this logic to my problem
Illogic would be more applicable.

Every row from table2 will be returned & there is no need to query table1.

The ONLY way a row will can exist within a child table is when the PK exists in parent table.

Good Luck
Re: Cursor Help with a Select Statement [message #431898 is a reply to message #431897] Thu, 19 November 2009 22:25 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
May be a NOT NULL would be fine, as foreign key can have NULLs too.

By
Vamsi

[Updated on: Thu, 19 November 2009 22:51]

Report message to a moderator

Previous Topic: PLSQL newbie question
Next Topic: Execute procedure with arguments
Goto Forum:
  


Current Time: Sat Dec 10 08:41:19 CST 2016

Total time taken to generate the page: 0.08061 seconds