oracle 如何在plsql中调用一个过程中的变量？

2ic8powd 于 2022-11-03 发布在 Oracle

关注(0)|答案(1)|浏览(195)

我试图在另一个过程中调用2个变量，该过程应在如下列中给予最小值和最大值：

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;

这会给我一个如下的错误消息：

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

下面是minmaxgpa的过程：

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;

我知道如何用函数来实现，但不知道如何用过程来实现。你能帮我吗？

oracle

来源：https://stackoverflow.com/questions/74046729/how-to-call-a-variable-inside-a-procedure-in-plsql

1条答案

按热度按时间

cunj1qz11#

使用非常简单的示例表（只是为了使过程编译）：

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.

程序代码（我没有更改）：

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.

“新的”ClassEnrollmentReport应如下所示：您必须遵循MinMaxGPA过程的描述-它接受3个参数（一个输入，两个输出）：

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.

如果我们进行测试：

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

PL/SQL procedure successfully completed.

SQL>

赞(0）回复(0）举报 2022-11-03

我来回答

oracle 如何在plsql中调用一个过程中的变量？

1条答案

相关问题

热门标签

最新问答