--使用存储过程
--创建无参存储过程
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;
oracle存储过程(有参、无参)
最新推荐文章于 2024-08-06 11:48:43 发布