CREATE OR REPLACE PROCEDURE proc2 (v_xh IN varchar2, v_kcm in varchar2, v_cj OUT number ) AS BEGIN
SELECT cj
INTO v_cj FROM XS,KC,XS_KC
WHERE XS.XH=XS_KC.XH and kc.kch=xs_kc.kch and xs.xh=v_xh and
kcm=v_kcm; END proc2; DECLARE v_cj number; begin
proc2('200901','计算机网络',v_cj); dbms_output.put_line(to_char(v_cj)); end;
3. 创建一存储过程proc3，显示其指定课程的成绩在指定范围内的学生学号、姓名、课程
名和成绩，并返回其人数，然后执行该存储过程。 create or replace procedure proc3 (s_kcm in varchar2, cj_start in number, cj_end in number, rs out number) is
v_xh char(6); v_name char(8); v_kcm char(20); v_cj number; cursor bf is
Select xs.xh,xm,kcm,cj From xs,kc,xs_kc
Where xs.xh=xs_kc.xh and kc.kch=xs_kc.kch and kcm=s_kcm and cj>=cj_start and cj<=cj_end; begin
open bf; loop
fetch bf into v_xh,v_name,v_kcm,v_cj;
dbms_output.put_line(v_xh||' '||v_name||' '||v_kcm||' '||to_char(v_cj)); exit when bf%NOTFOUND; end loop;
Select count(*) into rs
From xs,kc,xs_kc
Where xs.xh=xs_kc.xh and kc.kch=xs_kc.kch and kcm=s_kcm and cj>=cj_start and cj<=cj_end;
dbms_output.put_line(to_char(rs)); end proc3; declare rs number; begin
proc3('计算机网络',60,90,rs); end;
4. 创建一存储过程xs_update,用于对指定学号指定课程名称的学生成绩进行修改。
CREATE OR REPLACE PROCEDURE xs_update (v_xh IN varchar2, v_kcm in varchar2, v_cj in number ) AS BEGIN
update xs_kc
set cj=v_cj where xh=v_xh
and kch in (select kch from kc where kcm=v_kcm); END xs_update;
5. 创建一存储过程，用于统计每个专业计算机基础成绩在各个分数段的学生人数。分数段
划分为：低于60分，60～70分，70 ～80分，80～90分，90～100分。
专业 计算机 ?? 低于60 0 ?? 60～70 3 ?? 70～80 7 ?? 80～90 5 ?? 90～100 4 ?? CREATE OR REPLACE PROCEDURE xs_tj (v_kcm IN varchar2 ) is
v_zym char(20); v_count_cj1 number; v_count_cj2 number; v_count_cj3 number; v_count_cj4 number; v_count_cj5 number;
cursor bf is Select distinct zym From xs; begin open bf; loop
fetch bf into v_zym;
select count(*) into v_count_cj1 from xs,xs_kc,kc
where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch and kcm=v_kcm and cj<60;
select count(*) into v_count_cj2 from xs,xs_kc,kc
where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch and kcm=v_kcm and cj<70 and cj>=60;
select count(*) into v_count_cj3 from xs,xs_kc,kc
where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch and kcm=v_kcm and cj<80 and cj>=70;
select count(*) into v_count_cj4 from xs,xs_kc,kc
where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch and kcm=v_kcm and cj<90 and cj>=80;
select count(*) into v_count_cj5 from xs,xs_kc,kc
where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch and kcm=v_kcm and cj>=90;
dbms_output.put_line(v_zym||' '||to_char(v_count_cj1)||' '||to_char(v_count_cj2)||' '||to_char(v_count_cj3)||' '||to_char(v_count_cj4)||' '||to_char(v_count_cj5)); exit when bf%NOTFOUND; end loop;
END xs_tj;
exec xs_tj('计算机网络')；