内容发布更新时间 : 2024/12/23 0:45:34星期一 下面是文章的全部内容请认真阅读。
p_gd_grade in gd.gd_grade%type, s_state_values out number) as begin
insert into gd(gd_stu_no,gd_sub_subject,gd_grade) values(p_gd_stu_no,p_gd_sub_subject,p_gd_grade); if sql%rowcount=1 then s_state_values:=0; end if; exception when others then s_state_values:=1; if p_gd_grade=null then s_state_values:=2; end if; commit; end ; /
create or replace procedure c_insert_gd( p_gd_stu_no in gd.gd_stu_no%type,
p_gd_sub_subject in gd.gd_sub_subject%type, c_state_values out number) as begin
select count(*) into c_state_values from ( select gd_stu_no from gd
where gd_sub_subject=p_gd_sub_subject )
where gd_stu_no=p_gd_stu_no; end ; /
create or replace procedure c_insert_gd_fk1( c_stu_no in student.stu_no%type, c_state_values_fk1 out number) as begin
select count(*) into c_state_values_fk1 from student where stu_no=c_stu_no; end ; /
create or replace procedure c_insert_gd_fk2( c_sub_subject in sub.sub_subject%type, c_state_values_fk2 out number) as begin
select count(*) into c_state_values_fk2 from sub where sub_subject=c_sub_subject; end ; /
create or replace procedure p_select_stu_no_info( p_stu_no in out student.stu_no%type, p_stu_name out student.stu_name%type, p_stu_sex out student.stu_sex%type,
p_stu_birthday out student.stu_birthday%type, p_stu_phone out student.stu_phone%type, p_stu_cs_class out student.stu_cs_class%type, p_cs_dept out cs.cs_dept%type, p_cs_grade out cs.cs_grade%type, c_state_values out number) as begin
c_state_values:=0;
select stu_no, stu_name,stu_sex ,stu_birthday ,stu_phone ,stu_cs_class ,cs_dept ,cs_grade
into p_stu_no, p_stu_name, p_stu_sex, p_stu_birthday, p_stu_phone, p_stu_cs_class, p_cs_dept, p_cs_grade from student, cs
where stu_cs_class=cs_class and stu_no=p_stu_no; exception
when NO_DATA_FOUND then c_state_values:=1; when others then c_state_values:=2; end ; /
create or replace procedure p_select_stu_no_gd(p_stu_no in student.stu_no%type , cursor_gd out sys_refcursor) as begin
open cursor_gd for
select gd_sub_subject,gd_grade from gd
where gd_stu_no=p_stu_no; end; /
create or replace procedure p_select_stu_name( p_stu_name in student.stu_name%type , cursor_stu out sys_refcursor, c_state_values out number) as begin
c_state_values:=0;
open cursor_stu for
select stu_no, stu_name,stu_sex ,stu_birthday ,stu_phone ,stu_cs_class ,cs_dept ,cs_grade from student, cs
where stu_name=p_stu_name and stu_cs_class=cs_class; exception
when others then c_state_values:=2; end; /
create or replace procedure p_pm_sum( cursor_gd_sum out sys_refcursor ) as begin
open cursor_gd_sum for
select distinct sno,stu_name,sum
from student,(select gd_stu_no as sno,sum(gd_grade) as sum from gd
group by gd_stu_no )
where stu_no=sno order by sum desc; end; /
create or replace procedure p_pm_sub(
p_gd_sub_subject in gd.gd_sub_subject%type, cursor_gd_sub out sys_refcursor ) as begin
open cursor_gd_sub for
select gd_stu_no,stu_name,gd_grade from gd,student
where gd_sub_subject=p_gd_sub_subject and stu_no=gd_stu_no order by gd_grade desc; end; /
create or replace procedure p_pm_class( p_stu_cs_class in student.stu_cs_class%type, cursor_gd_class out sys_refcursor ) as begin
open cursor_gd_class for
select distinct sno,stu_name,sum
from student,(select gd_stu_no as sno,sum(gd_grade) as sum from gd
group by gd_stu_no )
where stu_no=sno and stu_cs_class=p_stu_cs_class order by sum desc; end; /
create or replace procedure p_fx( cursor_fx out sys_refcursor ) as begin
open cursor_fx for
select gd_sub_subject,max(gd_grade) as zuigao,min(gd_grade) as zuidi,avg(gd_grade) as pingjun,SUM(CASE WHEN gd_grade>=60 THEN 1 ELSE 0 END)/COUNT(*)*100 AS jigelv, SUM(CASE WHEN gd_grade>=80 THEN 1 ELSE 0 END)/COUNT(*)*100 AS youxiulv from gd
group by gd_sub_subject; end; /
create or replace procedure p_bujige( cursor_bujige out sys_refcursor ) as begin
open cursor_bujige for
select gd_stu_no,gd_sub_subject,gd_grade from gd
where gd_grade<60; end; /
create or replace procedure p_update_stu( p_stu_no in student.stu_no%type,
p_stu_name in student.stu_name%type, p_stu_sex in student.stu_sex%type,
p_stu_birthday in student.stu_birthday%type, p_stu_phone in student.stu_phone%type, p_stu_cs_class in student.stu_cs_class%type, s_state_values out number ) as begin update student set
stu_name=p_stu_name,stu_sex=p_stu_sex,stu_birthday=p_stu_birthday,stu_phone=p_stu_phone,stu_cs_class=p_stu_cs_class where stu_no=p_stu_no; if sql%rowcount=1 then s_state_values:=0; end if; exception when others then s_state_values:=1; commit; end ; /
create or replace procedure p_delete_stu( p_stu_no in student.stu_no%type, s_state_values out number) as begin
delete from gd where gd_stu_no=p_stu_no; delete from student where stu_no=p_stu_no; s_state_values:=0; exception when others then s_state_values:=1;rollback; commit; end; /
create or replace procedure p_update_gd( p_gd_stu_no in gd.gd_stu_no%type,
p_gd_sub_subject in gd.gd_sub_subject%type, p_gd_grade in gd.gd_grade%type, s_state_values out number) as begin
update gd
set gd_stu_no=p_gd_stu_no,gd_sub_subject=p_gd_sub_subject,gd_grade=p_gd_grade where gd_stu_no=p_gd_stu_no and gd_sub_subject=p_gd_sub_subject; if sql%rowcount=1 then s_state_values:=0; end if; exception when others then s_state_values:=1;rollback; commit; end ; /
create or replace procedure p_delete_gd( p_gd_stu_no in gd.gd_stu_no%type,