Transact-SQL 数据库编程，创建自定义函数
1. 编一程序用于判断XS数据表中是否有年龄小于18岁的学生数据，如果有则显示学生的
人数，否则显示“没有年龄小于18岁的学生数据”。 set serveroutput on;
declare
n_count number(10); begin select
count(*)
into
n_count
from
xs
where
to_number(to_char(sysdate,'yyyy'))-to_number(to_char(cssj,'yyyy'))<18; if n_count<1 then
DBMS_OUTPUT.PUT_LINE('没有年龄小于18岁的学生数据'); else
DBMS_OUTPUT.PUT_LINE('大于18岁的学生'||to_char(n_count)||'人'); end if; end;
2. 编一程序显示每个学生的学号、姓名、平均成绩及成绩等级，成绩等级的判断条件如下：
平均成绩>=90 显示“优” 平均成绩<90 and平均成绩>=80
显示“良”
平均成绩<80 and平均成绩>=70 显示“中” 平均成绩<70 and平均成绩>=60 显示“及格” 平均成绩<60
select xs.xh,xm,avg(cj) 平均成绩,case when avg(cj)>=90 then '优' when avg(cj)>=80 then '良' when avg(cj)>=70 then '中' when avg(cj)>=60 then '及格' else '不及格' end 平均成绩 from xs,xs_kc where xs.xh=xs_kc.xh group by xs.xh,xm;
3. 创建一个用户自定义函数，以学号和课程名称为参数，返回该学生指定课程的学习成绩。
然后使用该函数查询学号“200901”同学“计算机网络”的成绩。 Create or replace function func1 (s_num in char,s_kcm in char)
显示“不及格”
return number As
n_cj number; Begin
select cj into n_cj from xs_kc,kc
where xs_kc.kch=kc.kch and xh=s_num and kcm=s_kcm; RETURN(n_cj); end func1;
DECLARE
man_num NUMBER; BEGIN
man_num:=func1('200901','计算机网络'); dbms_output.put_line(to_char(man_num));
END;
4. 创建一个游标，返回计算机专业每个同学的学号、姓名、课程名称和成绩。
DECLARE v_xh char(6); v_xm char(8); v_kcm char(20); v_cj number(2); CURSOR XS_CUR3 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 ZYM='计算机'; BEGIN
OPEN XS_CUR3;
FETCH XS_CUR3 INTO v_xh,v_xm,v_kcm,v_cj; WHILE XS_CUR3%FOUND LOOP
dbms_output.put_line(v_xh||' '||v_xm||' '||v_kcm||' '||to_char(v_cj)); FETCH XS_CUR3 INTO v_xh,v_xm,v_kcm,v_cj;
END LOOP; END;
CLOSE XS_CUR3;
5. 创建一个用户自定义函数，以学号为参数，返回指定学生的各科成绩及总成绩。然后使
用该函数查询学号为“200901”学生的各科成绩及总成绩。 Create or replace function func2 (s_num in char) return number As
n_cj number; Begin DECLARE v_kcm char(20); v_cj number(2); CURSOR XS_CUR3 IS
SELECT kcm,cj FROM xs_kc,kc
WHERE kc.kch=xs_kc.kch and xs_kc.xh=s_num; BEGIN OPEN XS_CUR3;
FETCH XS_CUR3 INTO v_kcm,v_cj; WHILE XS_CUR3%FOUND LOOP
dbms_output.put_line(v_kcm||' '||to_char(v_cj)); FETCH XS_CUR3 INTO v_kcm,v_cj; END LOOP; CLOSE XS_CUR3; END;
SELECT sum(cj) into n_cj FROM xs_kc,kc
WHERE kc.kch=xs_kc.kch and xs_kc.xh=s_num; return(n_cj); end func2;
DECLARE
man_num number; BEGIN
man_num:=func2('200901');
dbms_output.put_line('总成绩'||' '||to_char(man_num));
END;
6. 创建一用户自定义的函数，用于统计某一课程在每个分数段的人数。分数段分为：低于
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 FUNCTION rs
(v_kcm in char,cj1 out number,cj2 out number,cj3 out number,cj4 out number,cj5 out number) RETURN NUMBER AS
zs NUMBER; BEGIN
SELECT count(xh) INTO cj1 FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm and cj<60; SELECT count(xh) INTO cj2 FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm and cj>=60 and cj<70; SELECT count(xh) INTO cj3 FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm and cj>=70 and cj<80; SELECT count(xh) INTO cj4 FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm and cj>=80 and cj<90; SELECT count(xh) INTO cj5 FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm and cj>=90 ; SELECT count(xh) INTO zs FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm; RETURN(zs); END rs;
DECLARE
kcm char(10):='计算机网络'; zs number; cj1 number; cj2 number; cj3 number;
cj4 number; cj5 number; BEGIN
zs:=rs(kcm,cj1,cj2,cj3,cj4,cj5);
dbms_output.put_line('计算机网络共有'||' '||to_char(zs)); dbms_output.put_line('不及格的有'||' '||to_char(cj1)); dbms_output.put_line('及格有'||' '||to_char(cj2)); dbms_output.put_line('中有'||' '||to_char(cj3)); dbms_output.put_line('良有'||' '||to_char(cj4)); dbms_output.put_line('优有'||' '||to_char(cj5)); END;
创建存储过程
1. 创建一存储过程xscj_show，显示每个学生的学号、姓名、课程名、成绩，然后执行该
存储过程。
set serveroutput on;
create or replace procedure xscj_show 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; begin open bf;
fetch bf into v_xh,v_name,v_kcm,v_cj; while bf%FOUND loop
dbms_output.put_line(v_xh||' '||v_name||' '||v_kcm||' '||to_char(v_cj)); fetch bf into v_xh,v_name,v_kcm,v_cj; end loop; end xscj_show;
exec xscj_show;
2. 创建一存储过程proc2，显示指定学生指定课程的成绩，然后执行该存储过程.