Re: Create a view using OUT values from a procedure - how?

From: David Fitzjarrell <fitzjarrell_at_cox.net>
Date: 8 Oct 2004 05:21:27 -0700
Message-ID: <9711ade0.0410080421.36960303@posting.google.com>

"Mike" < none_at_nospan.com> wrote in message news:25tcm0p4mridsv9urf7mejkr8ri4g3vpb4_at_4ax.com...

> On Thu, 07 Oct 2004 18:29:15 -0700, Daniel Morgan
> <damorgan_at_x.washington.edu> wrote:
> 

>>Mike wrote:
>>
>>> Hi, I¡¦m creating a view and would like to have two of the columns
to
>>> contain data obtained from a procedure which has two out values.
I'm using
>>> Oracle 9.2
>>>
>>> For example
>>>
>>> Create view student.v_grades
>>> (str_student, studentID, str_higrade, str_lowgrade)
>>> AS
>>> Select studentdb.student,
>>> Studentdb.studentID,
>>> /* How do I get student grades from the procedure */
>>> FROM studentdb
>>> WHERE ( ¡K)
>>>
>>>
>>> e.g. procedure:
>>>
>>> Create Procedure PR_getgrades(
>>> P_higrade OUT VARCHAR2,
>>> P_lowgrade OUT VARCHAR2
>>> P_studentID IN Number)
>>> IS
>>> BEGIN
>>>
>>> /*process data*/
>>>
>>> P_higrade := result1;
>>> P_lowgrade :=result2;
>>>
>>> End;
>>> END PR_getgrades;
>>> /
>>>
>>> Thanks,
>>>
>>> Mike
>>
>>It can not be done.
>>
>>Hasn't your instructor taught you anything about how Oracle works?
>>
>>There are solutions ... but as this is clearly classwork you need
>>to figure them out for yourself. So what you get is a hint.
>>
>>Hint: Use functions.

> 
> 
> Hi, I am not a student, although I am a noob to Oracle PL/SQL. Using
> functions does not solve my problem, because a function can only
> return one value, and I need two values. I could create two functions
> and do double the processing, but this seems inefficient, but
> according to this group this is the only way. True?
> 
> Thanks,
> 
> Mike
> 

Why do you see a two functions as 'double the procesing'? Yes, it would generally require two functions to return the data, but how difficult can it be to return the max() and min() grades for a given student? Especially when all you're using is the student id to retrieve them?

create or replace function highgrade(p_StudentId in number) return number is

   higrd number;
begin

   select max(grade) into higrd from studentdb where studentid = p_StudentId;

   return higrd;
end;
/

create or replace function lowgrade(p_StudentId in number) return number is

   logrd number;
begin

   select min(grade) into logrd from studentdb where studentid = p_StudentId;

   return logrd;
end;
/

Create view student.v_grades
(str_student, studentID, str_higrade, str_lowgrade) AS
Select studentdb.student,

       Studentdb.studentID,
       highgrade(studentdb.studentID),
       lowgrade(studentdb.studentID)

FROM studentdb
WHERE ... I can't see the problem, or the issue.

David Fitzjarrell Received on Fri Oct 08 2004 - 07:21:27 CDT