广东医学院数据库课程设计一(3)

DECLARE AVASCORE T_SCORE.SCORE%TYPE; BEGIN

SELECT AVG(SCORE) INTO AVASCORE FROM T_SCORE; DBMS_OUTPUT.PUT_LINE('平均分:'||AVASCORE); END; 76） 定义一个表变量stunames,请将几名学生的姓名放入，并打印; SET SERVEROUTPUT ON;

DECLARE STUNAMES T_STUDENT%ROWTYPE; RESULT T_STUDENT.STUNAME%TYPE;

CURSOR CUR_NAME IS SELECT STUNAME INTO STUNAMES.STUNAME FROM T_STUDENT; BEGIN

IF NOT CUR_NAME %IS OPEN; THEN OPEN CUR_NAME; END IF; LOOP

FETCH CUR_NAME INTO RESULT

EXIT WHEN CUR_NAME%NOT FOUND

DBMS_OUTPUT.PUT_LINE(‘姓名是：’||RESULT); END LOOP;

CLOSE CUR_NAME; END; 77） 查询郭莉芳的所有科目平均成绩，决定她的等级。90-100为优秀，70-89为良好，

60-69为及格，其他为差。请将其分数和等级打印出来;

SELECT AVG(SCORE),DECODE(SIGN(AVG(SCORE)-90),1,’优秀’,0,’优秀’,-1,DECORE(SIGN(AVG(SCORE)-70),1,’良好’,0,’良好’,-1,DECODE(SIGN(AVG(SCORE)-60),1,’及格’,0’及格’,-1,’不及格’)))

FROM T_SCORE,T_STUDENT

WHERE T_STUDENT.STUNO=T_SCORE.STUNO AND T_STUDENT.STUNAME=’郭莉芳’

78）

编写存储过程，打印全校所有学生平均分;

CREATE OR REPLACE PRODUCEDURE P_SCORE (TT_STUNO T_SCORE.STUNO%TYPE, TT_SCORE T_SCORE.SCORE%TYPE) AS BEGIN

SELECT AVG(SCORE) INTO TT_SCORE

FROM T_SCORE WHERE STUNO=TT_STUNO; END ; /

DECLARE TTT_SCORE CHAR(20); BEGIN

P_SCORE(‘S001’,TTT_SCORE);

DBMS_OUTPUT.PUT_LINE(‘平均成绩：’||TTT_SCORE) END; /

79） 编写一个存储过程，输出一个学生的学号，将这个学生所选的课程的数量、所有课

程的平均分存储在两个输出参数内;

CREATE OR REPLACE PROCEDURE P_NO (STUNOO T_SCORE.STUNO%TYPE, COUNT OUT INT,

SCOREE OUT FLOAT) AS BEGIN

SELECT COUNT(COURSENO) INTO COUNT FROM T_SCORE WHERE STUNO=STUNOO GROUP BY STUNO;

SELECT AVG(SCORE) INTO SCOREE

FROM T_SCORE WHERE STUNO=STUNOO GROUP BY STUNO; END; /

DECLARE COUNTT INT;SCOREEE FLOAT; P_NO(‘S001’,COUNTT,SCOREEE);

DBMS_OUTPUT.PUT_LINE(‘数量：’||COUNTT); DBMS_OUTPUT.PUT_LINE(‘平均分：’||SCOREEE); END; /

80） 修改某门课程的任课教师。如果任课教师室教授，不可以进行修改;

CREATE OR REPLACE TRIGGER CC

BEFORE UPDATE ON T_COURSE FOR EACH ROW DECLARE TEA_TITLE T_TEACHER.TEATITLE%TYPE; BEGIN

SELECT TEATITLE INTO TEA_TILE FROM T_TEACHER WHERE TEANO=:OLD.TEANO; IF TEA_TITLE=’教授’

THEN RAISE_APPLICATION_ERROR(-20000,’不可修改’); END IF;

END;

81） 显示教授的姓名，用空格隔开，保存在一个字符串内，打印; declare

cursor cur_name is

select 教师姓名 from T_TEACHER WHERE t_teacher.TEATITLE='教授';

con_name T_TEACHER.TEATITLE%type; result varchar2(200); begin

open cur_name; loop

fetch cur_name into con_name; exit when cur_name%notfound; result:=result||' '||con_name; end loop;

dbms_output.put_line(result); close cur_name; end;

82） 使用oem 或者SQLPLUS创建教师管理员admin,赋予此用户创建用户的权限并通

过此admin用户创建三个教师用户； create user adminljq identified by jay; grant create user to adminljq;

conn adminljq/jay@222.16.65.250/orcl grant connect to adminljq； grant dba to adminljq；

create user teaA identified by 123; create user teaB identified by 1234; create user teac identified by 12345; 83） 创建teacher角色，使teacher角色具有对表SCOTT.T_TEACHER所有操作权限，

并将admin用户赋予teacher角色。 create role teacher;

grant all privilege on t_teacher to teacher; grant teacher to adminljq;

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库广东医学院数据库课程设计一(3)在线全文阅读。