return mulitple rows

[ales]

Hi,
there are many ways.
You can create two procedures, the first returning students and the second returning exams for one student. Then simply go through the student-cursor and for each student call the second procedure to retrieve cursor with student's exams.
Another way could be returning students and their exams in one cursor. Then you have to use ORDER BY clause and in calling code test if student's name has changed.
BTW, criteria '%'||name||'%' in WHERE clause causes poor performance.
Without knowing more about your tables etc. its hard to suggest anything better or more specific.

Regards,
Ales

[mooks]

Sorry... I am totally lost! :( I tried a few ways but I still couldnt be able to retrieve the results that I want.

You wrote :
the first returning students and the second returning exams for one student. Then simply go through the student-cursor and for each student call the second procedure to retrieve cursor with student's exams.

It sounds good, but when I tried it, but I am encountering problems... coudl you guide me along with a small example pls?

I understand that '%'||name||'%' will cause a delay in teh wuery but I dun have a choice as I am searching for a particular word from the student name....

Your help is much appreciated......
Meanwhile I will just have to keep on trying !!

[mooks]

Hi I am sort of struck in the second cursor... could you help me pls? I am trying to pass the first record set into the second cursor... as shown in the following codes.

CREATE OR REPLACE PACKAGE pkg_test AS
TYPE student_Cursor IS REF CURSOR ;
TYPE exam_Cursor IS REF CURSOR ;
PROCEDURE get_student (p_id IN varchar2,
return_student OUT student_Cursor) ;
PROCEDURE get_exam (p_id IN varchar2,
return_exam OUT exam_Cursor) ;
END;

create or replace package body pkg_test AS
procedure get_student (p_id IN varchar2,
return_student OUT student_Cursor)
IS
BEGIN
select student_name
from ....
where ..... ;
END get_student;

procedure get_exam (p_id IN varchar2,
return_exam OUT exam_Cursor)
IS
BEGIN
-- How do I loop the first cursor and pass the data into the
-- variable v_student in following sql ???

select exam_id
from student_exam
where lower(student_name) like '%'||v_student||'%';

END get_exam;
END pkg_test;

[ales]

Hi,
this is a simple example for SQL*Plus:

scott@oracle> select * from students;



STUDENT_NAME

------------------------------

John

Paul

Mary



scott@oracle> select * from student_exam;



STUDENT_NAME                   EXAM_ID

------------------------------ ----------

John                           MATH

John                           GYM

Mary                           HIST



scott@oracle> create or replace package pack_test as

  2  type ty_cursor is ref cursor;

  3  procedure get_students (st_cursor in out ty_cursor);

  4  procedure get_exams (name in varchar2, ex_cursor in out ty_cursor);

  5  end pack_test;

  6  /



Package created.



scott@oracle> create or replace package body pack_test as

  2  procedure get_students (st_cursor in out ty_cursor) is

  3  begin

  4      open st_cursor for

  5      SELECT student_name FROM students;

  6  end get_students;

  7  procedure get_exams (name in varchar2, ex_cursor in out ty_cursor) is

  8  begin

  9      open ex_cursor for

 10      SELECT exam_id FROM student_exam

 11      WHERE student_name = name;

 12  end get_exams;

 13  end pack_test;

 14  /



Package body created.



scott@oracle> var st refcursor

scott@oracle> var ex refcursor

scott@oracle> exec pack_test.get_students(:st)



PL/SQL procedure successfully completed.



scott@oracle> print st



STUDENT_NAME

------------------------------

John

Paul

Mary



scott@oracle> exec pack_test.get_exams('John',:ex)



PL/SQL procedure successfully completed.



scott@oracle> print ex



EXAM_ID

----------

MATH

GYM



scott@oracle> exec pack_test.get_exams('Paul',:ex)



PL/SQL procedure successfully completed.



scott@oracle> print ex



no rows selected



scott@oracle> exec pack_test.get_exams('Mary',:ex)



PL/SQL procedure successfully completed.



scott@oracle> print ex



EXAM_ID

----------

HIST

---

Well, you have a package with two procedures, one for students, one for student's exams. The second procedure should have the IN parameter to pass student's name. Then you should place the parameter in WHERE clause to select exams of just one student.
I also suppose you have a code you want to call these procedures from.
What is it? SQL*Plus, java ... ?
You should post more about the purpose of the program.

Ales

[mooks]

Hi,

Thanks for the example... but there is a problem...

U wrote:

open ex_cursor for
SELECT exam_id FROM student_exam
WHERE student_name = name;

However I do not want to pass in name as an individual, the name is actually a set of name from the first cursor and pass into the second cursor. That's my biggest problem!! :(

I am going to use this in SQL*PLUS as well as perl script.
Thanks.



[Edited by mooks on 09-11-2001 at 06:01 AM]

[ales]

Hi,
then, IMHO, you don't need to use two or more cursors.
Just join all tables in one SELECT statement:

select students.student_name, student_exam.exam_id

from students, student_exam, ...

where students.student_name = student_exam.student_name

  and ... 

BTW, does it make sense to use a procedure for that?
One ordinary cursor opened from your client might be good enough.

If you posted more about your tables and expected results we'd be more specific.

Ales

[mooks]

Hi,

I think you have misunderstood my question...

I need the following:
1. Pass in student ID to procedure 1
2. Returns a set of subjects which belongs to the student from procedure 1
3. Pass in the set of values from point 2 into procedure 2
4. In procedure 2, retrieve another set of results with the set of values from procedure 1.
Note: I need to use the LIKE statement which is why I cant join the queries together.


I have posted the following codes before and maybe you have a closer look at it:

CREATE OR REPLACE PACKAGE pkg_test AS
TYPE student_Cursor IS REF CURSOR ;
TYPE exam_Cursor IS REF CURSOR ;
PROCEDURE get_student (p_id IN varchar2,
return_student OUT student_Cursor) ;
PROCEDURE get_exam (p_id IN varchar2,
return_exam OUT exam_Cursor) ;
END;

create or replace package body pkg_test AS
procedure get_student (p_id IN varchar2,
return_student OUT student_Cursor)
IS
BEGIN
select student_name
from ....
where ..... ;
END get_student;

procedure get_exam (p_id IN varchar2,
return_exam OUT exam_Cursor)
IS
BEGIN
-- How do I loop the first cursor and pass the data into the
-- variable v_student in following sql ???

-- LOOP first cursor
select exam_id
from student_exam
where lower(student_name) like '%'||v_student||'%';
-- pass in the value from first cursor into the variable v_student

END get_exam;
END pkg_test;

Please not the second procedure get_exam. I need to pass in the set of results from procedure 1 stored in the cursor and loop them through to retrieve another set of results before returning the result. Why I need 2 procedures is because I have to use the LIKE sign. I hope that I am not confusing you further. Thank you.

[ales]

Hi,
create one procedure instead of two. Inside open the cursor exactly as in you first procedure, go through and for each row open the cursor exactly as in your second procedure.

But then we're on the beginning again. You cannot pass set of cursors back to calling code ...

What's wrong with the LIKE operator and joins?
Try this:

-- here you get set of names

-- for clarify I call the table TTT

select student_name 

from TTT 

where TTT.id = p_id; 



-- here you want to pass set of names from the first query

-- v_student is TTT.student_name

select exam_id 

from student_exam 

where lower(student_name) like '%'||v_student||'%'; 



-- join of the two queries

select exam_id 

from student_exam , TTT

where lower(student_name) like '%'||TTT.student_name||'%'

and TTT.id = p_id; 

---

Ales

[mooks]

HI,

No No No... it is not that way... coz table 1 and table 2 are not related so they cannot be joined! I have no problems doing join table queries or using LIKE or equal operators.

The below will not works coz i need more than one student name. I also cannot use IN operator coz I need to search for the student name inside a string.

select exam_id
from student_exam , TTT
where lower(student_name) like '%'||TTT.student_name||'%'
and TTT.id = p_id;

Thanks anyway, I will try hard to figure it out..........

[ales]

Table1 and table2 aren't related?
Then, why do you select values from table1 and use them as select criteria for table2?

If you need more than one student name just change condition for Table1 in the WHERE clause.

I'm looking forward to find your solution here.
Good luck!

Ales