oracle存储过程（有参、无参）

--使用存储过程
--创建无参存储过程
create or replace procedure showAllStudentInfo
--参数变量
as
--自定义变量
s_name varchar(20);
s_score number(6);
cursor cur_student is select * from student;
begin
  dbms_output.put_line('您调用了showAllStudentInfo...');
  for c_student in cur_student loop
    s_name:=c_student.stuname;
    s_score:=c_student.stuscore;
    dbms_output.put_line(s_name||':'||s_score);
  end loop;
end;
--调用无参存储过程
begin
  showAllStudentInfo;
end;
-----------------------------------
-----------------------------------
--创建有输入参存储过程
create or replace procedure showStudentInfoByScore
       --参数变量
       (
       min_score in number,
       max_score in number
       )
as
--自定义变量
s_name varchar(20);
s_score number(6);
cursor cur_student is select * from student where stuscore between min_score and max_score;
begin
  dbms_output.put_line('showStudentInfoByScore...');
  for c_student in cur_student loop
    s_name:=c_student.stuname;
    s_score:=c_student.stuscore;
    dbms_output.put_line(s_name||':'||s_score);
  end loop;
end;
--调用有输入参存储过程
declare 
  min_score number(2) := 60;
  max_score number(2) := 80;
begin
  showStudentInfoByScore(min_score,max_score);
end;
-----------------------------------
-----------------------------------
--创建有输入有输出参存储过程
create or replace procedure showStudentInfoByScore_2
       --参数变量
       (
       min_score in number,
       max_score in number,
       stu_count out number,
       stu_avg out number
       )
as
--自定义变量
s_name varchar(20);
s_score number(6);
cursor cur_student is select * from student where stuscore between min_score and max_score;
begin
  dbms_output.put_line('showStudentInfoByScore_2...');
  for c_student in cur_student loop
    s_name:=c_student.stuname;
    s_score:=c_student.stuscore;
    dbms_output.put_line(s_name||':'||s_score);
  end loop;
  --使用聚合函数做数据统计
  
  select 
      avg(stuscore) into stu_avg
  from student 
  where stuscore between min_score and max_score; 
  select
  count(*) into stu_count
  from student 
  where stuscore between min_score and max_score; 
  
end;
--调用有输入有输出参存储过程
declare 
  min_score number(2) := 60;
  max_score number(2) := 99;
  stu_count number;
  stu_avg number;
begin
  showStudentInfoByScore_2(
          min_score,
          max_score,
          stu_count,
          stu_avg);
  dbms_output.put_line(stu_count);
  dbms_output.put_line(stu_avg);
end;