How to call a variable inside a procedure in plsql?-CodePudding

I'm trying to calling 2 variables inside another procedure that should give me the min and max in a column like this:

create or replace procedure ClassEnrollmentReport
(p_CLASSNAME in class.classname%TYPE)
as
begin
    dbms_output.put_line('Max gpa:');
    StudentWithGivenGPA(MinMaxGPA.p_maxStudentGPA(p_CLASSNAME));
    dbms_output.put_line('Min gpa:');
    StudentWithGivenGPA(MinMaxGPA.p_minStudentGPA(p_CLASSNAME));
end ClassEnrollmentReport;

Which gives me an error message like this:

6/5       PL/SQL: Statement ignored
6/35      PLS-00225: subprogram or cursor 'MINMAXGPA' reference is out of scope
8/5       PL/SQL: Statement ignored
8/35      PLS-00225: subprogram or cursor 'MINMAXGPA' reference is out of scope

Here's how the minmaxgpa procedure look like:

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));

    
    select min(gpa) into minStudentGPA
    from student
    where classno = (select classno from class where upper(classname) = upper(p_CLASSNAME));

    p_maxStudentGPA := maxStudentGPA;
    p_minStudentGPA := minStudentGPA;
end MinMaxGPA;

I know how to do this with a function, but having no idea how can I get this with procedure. Can you help me with this?

CodePudding user response：

With really simple sample tables (just to make the procedure compile):

SQL> create table class as select 'abc' classname, 100 clasno from dual;

Table created.

SQL> create table student as select 1 gpa, 100 classno from dual;

Table created.

Procedure code (I didn't change it):

SQL> create or replace procedure MinMaxGPA
  2  (
  3      p_CLASSNAME in class.classname%type,
  4      p_maxStudentGPA OUT student.gpa%type,
  5      p_minStudentGPA OUT student.gpa%type
  6
  7  )
  8  as
  9      maxStudentGPA student.gpa%type;
 10      minStudentGPA student.gpa%type;
 11  begin
 12      select max(gpa) into maxStudentGPA
 13      from student
 14      where classno = (select classno from class where upper(classname) = upper(p_CLASSNAME));
 15
 16
 17      select min(gpa) into minStudentGPA
 18      from student
 19      where classno = (select classno from class where upper(classname) = upper(p_CLASSNAME));
 20
 21      p_maxStudentGPA := maxStudentGPA;
 22      p_minStudentGPA := minStudentGPA;
 23  end MinMaxGPA;
 24  /

Procedure created.

Your "new" ClassEnrollmentReport should then look like this: you have to follow the MinMaxGPA procedure's description - it accepts 3 parameters (one in, two out):

SQL> create or replace procedure ClassEnrollmentReport
  2    (p_CLASSNAME in class.classname%TYPE)
  3  as
  4    v_mingpa number;
  5    v_maxgpa number;
  6  begin
  7      minmaxgpa(p_classname, v_mingpa, v_maxgpa);
  8      dbms_output.put_line('Max gpa: ' || v_maxgpa);
  9      dbms_output.put_line('Min gpa: ' || v_mingpa);
 10  end ClassEnrollmentReport;
 11  /

Procedure created.

If we test it:

SQL> set serveroutput on
SQL> exec classenrollmentreport('abc');
Max gpa: 1
Min gpa: 1

PL/SQL procedure successfully completed.

SQL>