oracle有出参的存储过程写法,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;