How to define OUT parameter without input in pl/sql?-CodePudding

I'm asked to save both min and max GPAs into 2 OUT maxStudentGPA and minStudentGPA. I can do this without the OUT restriction like this:

create or replace procedure MinMaxGPA
(
    p_CLASSNAME in class.classname%type
)
as
    maxStudentGPA student.gpa%type;
    minStudentGPA student.gpa%type;
begin
    select max(gpa) into maxStudentGPA
    from student
    where classno = (select classno from class where upper(classname) = upper(p_CLASSNAME));
    dbms_output.put_line(maxStudentGPA);
    
    select min(gpa) into minStudentGPA
    from student
    where classno = (select classno from class where upper(classname) = upper(p_CLASSNAME));
    dbms_output.put_line(minStudentGPA);
end MinMaxGPA;

But if I add OUT to them like this:

    maxStudentGPA out student.gpa%type;
    minStudentGPA out student.gpa%type;

It throw me an error message like this:

6/23      PLS-00103: Encountered the symbol "STUDENT" when expecting one of the following:     := . ( @ % ; not null range default character The symbol ":=" was substituted for "STUDENT" to continue. 
7/23      PLS-00103: Encountered the symbol "STUDENT" when expecting one of the following:     := . ( @ % ; not null range default character The symbol ":=" was substituted for "STUDENT" to continue.

Can you explain to me why won't it work?

CodePudding user response：

It doesn't work because you are trying to declare an OUT parameter in the declaration section of the procedure while that can only be done in the parameter_declaration section of the procedure. Check the docs for more details.

create or replace procedure MinMaxGPA
(
    -- *parameter declaration* 
    -- here you define the arguments for your procedure
    -- arguments can be IN (default), OUT or IN OUT
    p_CLASSNAME in class.classname%type
)
as
    -- *declare section*
    -- here you define variables that you will use in the procedure body
    -- they have nothing to do with IN or OUT
    maxStudentGPA student.gpa%type;
    minStudentGPA student.gpa%type;
...
<rest_of_code>

So I think what you're trying to do is:

create or replace procedure MinMaxGPA
(
    p_CLASSNAME in class.classname%type,
    p_maxStudentGPA OUT student.gpa%type,
    p_minStudentGPA OUT student.gpa%type

)
as
    maxStudentGPA student.gpa%type;
    minStudentGPA student.gpa%type;
begin
    select max(gpa) into maxStudentGPA
    from student
    where classno = (select classno from class where upper(classname) = upper(p_CLASSNAME));
    dbms_output.put_line(maxStudentGPA);
    
    select min(gpa) into minStudentGPA
    from student
    where classno = (select classno from class where upper(classname) = upper(p_CLASSNAME));
    dbms_output.put_line(minStudentGPA);
    -- assign the calculated variables to the out parameters.
    p_maxStudentGPA := maxStudentGPA;
    p_minStudentGPA := minStudentGPA;
end MinMaxGPA;
/