【文章內容簡介】 nd 。/create or replace procedure c_insert_gd_fk1(c_stu_no in %type,c_state_values_fk1 out number)asbeginselect count(*) into c_state_values_fk1 from studentwhere stu_no=c_stu_no。end 。/create or replace procedure c_insert_gd_fk2(c_sub_subject in %type,c_state_values_fk2 out number)asbeginselect count(*) into c_state_values_fk2 from subwhere sub_subject=c_sub_subject。end 。/create or replace procedure p_select_stu_no_info(p_stu_no in out %type,p_stu_name out %type,p_stu_sex out %type,p_stu_birthday out %type,p_stu_phone out %type,p_stu_cs_class out %type,p_cs_dept out %type,p_cs_grade out %type,c_state_values out number)asbeginc_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_gradefrom student, cswhere stu_cs_class=cs_class and stu_no=p_stu_no。exceptionwhen 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 %type , cursor_gd out sys_refcursor)asbeginopen cursor_gd forselect gd_sub_subject,gd_gradefrom gdwhere gd_stu_no=p_stu_no。end。/create or replace procedure p_select_stu_name(p_stu_name in %type , cursor_stu out sys_refcursor,c_state_values out number)asbeginc_state_values:=0。open cursor_stu forselect stu_no, stu_name,stu_sex ,stu_birthday ,stu_phone ,stu_cs_class ,cs_dept ,cs_grade from student, cswhere stu_name=p_stu_name and stu_cs_class=cs_class。exceptionwhen others then c_state_values:=2。end。/create or replace procedure p_pm_sum(cursor_gd_sum out sys_refcursor)asbeginopen cursor_gd_sum forselect distinct sno,stu_name,sumfrom student,(select gd_stu_no as sno,sum(gd_grade) as sum from gd group by gd_stu_no)where stu_no=snoorder by sum desc。end。/create or replace procedure p_pm_sub(p_gd_sub_subject in %type,cursor_gd_sub out sys_refcursor)asbeginopen cursor_gd_sub forselect gd_stu_no,stu_name,gd_gradefrom gd,studentwhere gd_sub_subject=p_gd_sub_subject and stu_no=gd_stu_noorder by gd_grade desc。end。/create or replace procedure p_pm_class(p_stu_cs_class in %type,cursor_gd_class out sys_refcursor)asbeginopen cursor_gd_class forselect distinct sno,stu_name,sumfrom 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_classorder by sum desc。end。/create or replace procedure p_fx(cursor_fx out sys_refcursor)asbeginopen cursor_fx forselect 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 youxiulvfrom gdgroup by gd_sub_subject。end。/create or replace procedure p_bujige(cursor_bujige out sys_refcursor)asbeginopen cursor_bujige forselect gd_stu_no,gd_sub_subject,gd_gradefrom gdwhere gd_grade60。end。/create or replace procedure p_update_stu(p_stu_no in %type,p_stu_name in %type,p_stu_sex in %type,p_stu_birthday in %type,p_stu_phone in %type,p_stu_cs_class in %type,s_state_values out number) asbegin update student set stu_name=p_stu_name,stu_sex=p_stu_sex,stu_birthday=p_stu_birthday,stu_phone=