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

From: Mike <none_at_nospam.com>
Date: Fri, 8 Oct 2004 08:50:28 -0400
Message-ID: <4syhgrbsywpd.90wdk4tuxmp1$.dlg@40tude.net>

On 8 Oct 2004 05:21:27 -0700, David Fitzjarrell wrote:

> "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

Thanks for the reponse, but the problem is, the real world problem I am working on is much more complex, and I now realize the example I used was a foolish one because. 1) people think I'm doing a class assignment, and 2) the result appears easy to accomplish. The reality is, I will be calling a a function/procedure that is much more complex, yet the results are higly correlated.

Mike

Mike Received on Fri Oct 08 2004 - 07:50:28 CDT