《2022年实验四Transact-SQL数据库编程答案.docx》由会员分享，可在线阅读，更多相关《2022年实验四Transact-SQL数据库编程答案.docx（8页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精选学习资料 - - - - - - - - - 试验四Transact-SQL 数据库编程1. 编一程序用于判定 XS数据表中是否有年龄小于 18 岁的同学数据, 假如有就显示同学的人数,否就显示“ 没有年龄小于 18 岁的同学数据”； set serveroutput on; declare n_count number10; begin select count* into n_count from xs where to_numberto_charsysdate,yyyy-to_numberto_charcssj,yyyy18; if n_count=90 显示“ 优”平均成果 =80
2、 显示“ 良”平均成果 =70 显示“ 中”平均成果 =60 显示“ 及格”平均成果 =90 then 优 when avgcj=80 then 良 when avgcj=70 then 中 when avgcj=60 then 及格 else 不及格 end 平均成果 from xs,xs_kc where xs.xh=xs_kc.xh group by xs.xh,xm; 3.创建一个用户自定义函数,以学号和课程名称为参数,返回该同学指定课程的学习成果；然后使用该函数查询学号“202201” 同学“ 运算机网络” 的成果；Create or replace function func1 名
3、师归纳总结 - - - - - - -第 1 页,共 5 页精选学习资料 - - - - - - - - - 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; RETURNn_cj; end func1; DECLARE man_num NUMBER; BEGIN man_num:=func1202201, 运算机网络 ; dbms_output.p
4、ut_lineto_charman_num; END; 4. 创建一个游标,返回运算机专业每个同学的学号、姓名、课程名称和成果；DECLARE v_xh char6; v_xm char8; v_kcm char20; v_cj number2; 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;
5、WHILE XS_CUR3%FOUND LOOP dbms_output.put_linev_xh| |v_xm| |v_kcm| |to_charv_cj; FETCH XS_CUR3 INTO v_xh,v_xm,v_kcm,v_cj; END LOOP; CLOSE XS_CUR3; 名师归纳总结 - - - - - - -第 2 页,共 5 页精选学习资料 - - - - - - - - - END; 5. 创建一个用户自定义函数,以学号为参数,返回指定同学的各科成果及总成果；然后使用该函数查询学号为“202201” 同学的各科成果及总成果；Create or replace func
6、tion func2 s_num in char return number As n_cj number; Begin DECLARE v_kcm char20; v_cj number2; 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_linev_kcm| |to_charv_c
7、j; FETCH XS_CUR3 INTO v_kcm,v_cj; END LOOP; CLOSE XS_CUR3; END; SELECT sumcj into n_cj FROM xs_kc,kc WHERE kc.kch=xs_kc.kch and xs_kc.xh=s_num; returnn_cj; end func2; DECLARE man_num number; BEGIN 名师归纳总结 - - - - - - -第 3 页,共 5 页精选学习资料 - - - - - - - - - man_num:=func2202201; dbms_output.put_line 总成果
8、| END; |to_charman_num; 6. 创建一用户自定义的函数,用于统计某一课程在每个分数段的人数；分数段分为：低于60 分, 6070 分, 70 80 分, 8090 分, 90100 分；课程名低于 60 60707080809090100运算机基础0 3 7 5 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 SE
9、LECT countxh INTO cj1 FROM xs_kc,kc where xs_kc.kch=kc.kch and kcm=v_kcm and cj=60 and cj=70 and cj=80 and cj=90 ; SELECT countxh INTO zs FROM xs_kc,kc where xs_kc.kch=kc.kch and kcm=v_kcm; RETURNzs; END rs; DECLARE kcm char10:= 运算机网络 ; zs number; cj1 number; 名师归纳总结 - - - - - - -第 4 页,共 5 页精选学习资料 -
10、- - - - - - - - cj2 number; cj3 number; cj4 number; cj5 number; BEGIN zs:=rskcm,cj1,cj2,cj3,cj4,cj5; dbms_output.put_line 运算机网络共有 | |to_charzs; dbms_output.put_line 不及格的有 | |to_charcj1; dbms_output.put_line 及格有 | |to_charcj2; dbms_output.put_line 中有 | |to_charcj3; dbms_output.put_line 良有 | |to_charcj4; dbms_output.put_line 优有 | |to_charcj5; END; 名师归纳总结 - - - - - - -第 5 页,共 5 页