Mike wrote:
> 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
If this is correct you did yourself a huge disservice as yes I have
been absolutely convinced you were a student. So either post the
real problem or forget it.
One thing though ... I have rarely had to use a procedure, function, or
other stored code in this manner and doubt you actually do too. But do
take a look at Pipelined Table functions.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sat Oct 09 2004 - 14:40:38 CDT
