数据库实验七（头歌）

注:1.根据多谢部分修改老师代码。

     2.实验六老师已公布答案，可根据任务要求中老师所给步骤书写。

第1关 使用 IF EXISTS..ELSE 流程控制语句

/* 请在指定处（Begin和End之间）补全PL/SQL语句，不要改动其他代码 */

\set SQLTERM /
DECLARE
r record;
BEGIN
    /******* Begin ******/
	IF EXISTS( SELECT *from class where gnum>='37')   --补全1
	THEN
    	FOR r IN( select gname,dept from class where gnum>='37')   --补全2
        	LOOP
            	RAISE NOTICE '%', r;
	        END LOOP;
	ELSE
        RAISE NOTICE '没有人数在37以上的班级';   --补全3
    /******* End ******/
	END IF;
END;
/

第2关 使用CASE流程控制语句

/* 请在指定处（Begin和End之间）补全PL/SQL语句，不要改动其他代码 */

SELECT tno, tname,
    /******* Begin ******/
	CASE sex
		WHEN '男' THEN 'M'      --补全1
		WHEN '女' THEN 'F'      --补全2
	ELSE '其他'        --补全3
	END
	/******* End ******/
FROM teacher;

第3关 使用FOR流程控制语句

 

/* 请在指定处（Begin和End之间）补全PL/SQL语句，不要改动其他代码 */

\set SQLTERM /
DECLARE mviews RECORD;
BEGIN
    /******* Begin ******/
 	FOR   gno IN  (select *from class where gnum='35')  --补全1
        LOOP
	        UPDATE class SET  gnum=gnum+1  --补全2
	        WHERE  dept='软件' ;    --补全3
	    END LOOP;
    /******* End ******/
END;
\set SQLTERM ; 

第4关 按要求创建自定义函数 1

/* 自定义函数：请补全下列PL/SQL语句，不要改动其他代码 */
\set SQLTERM /
CREATE OR REPLACE FUNCTION PUBLIC.fun1(Teacher_zc  VARCHAR  )  --补全1
RETURNS    SETOF teacher  --补全2
AS
BEGIN
	RETURN QUERY
	SELECT * FROM   teacher   --补全3
	WHERE title=Teacher_zc;       --补全4
END;
\set SQLTERM ; 

/* 执行自定义函数 */
SELECT * FROM PUBLIC.fun1('教授');

第5关 按要求创建自定义函数 2

/* 自定义函数：请补全下列PL/SQL语句，不要改动其他代码 */
\set SQLTERM /
CREATE OR REPLACE FUNCTION PUBLIC.fun2(grope_id CHAR )  --补全1
RETURNS    SETOF class   --补全2
AS
BEGIN
	RETURN QUERY EXECUTE
	'SELECT * FROM class WHERE    gnum>=37 and gno=0131901'  USING grope_id  ;   --补全3
END;
\set SQLTERM ; 

/* 执行自定义函数 */
SELECT gname,grade,gnum  FROM PUBLIC.fun2('19级');

第6关 按要求创建自定义函数 3

/* 自定义函数：请补全下列PL/SQL语句，不要改动其他代码 */
\set SQLTERM /
CREATE OR REPLACE  FUNCTION PUBLIC.fun3(v_tno varchar ,v_tname varchar ,v_sex varchar , v_title varchar , v_birthday date ) --补全1
RETURNS VOID
 AS 
 BEGIN 
	 INSERT INTO  teacher  --补全2
	 VALUES(v_tno,v_tname,v_sex,v_title,v_birthday);        --补全3
	 RETURN;
 END;
\set SQLTERM ; 

/* 执行自定义函数 */
CALL PUBLIC.fun3(  '0017','崔洛','女','副教授','1974-04-25' );   --补全4

第7关 删除自定义函数

---------- BEGIN ---------- 
DROP FUNCTION fun1;

---------- END ---------- 

第8关 创建存储过程 1

/* 自定义存储过程：请补全下列PL/SQL语句，不要改动其他代码 */
\set SQLTERM /
CREATE OR REPLACE PROCEDURE PUBLIC.PROC_1(Teacher_zc  varchar ) --补全1
AS
DECLARE
r teacher%rowtype;
/* 定义游标cur_t */
cursor cur_t  is select *from teacher where title =Teacher_zc      ;  --补全2
BEGIN
    /* 打开游标cur_t */
    OPEN cur_t;      --补全3
    LOOP
        /* 使用游标cur_t */
        FETCH cur_t INTO r;      --补全4
        IF cur_t%FOUND THEN
	        RAISE NOTICE '%',r;
         ELSE
            EXIT;
        END IF;
    END LOOP;
    /* 关闭游标cur_t */
    CLOSE cur_t;       --补全5
END;
\set SQLTERM ; 

/* 执行存储过程 */
CALL PUBLIC.PROC_1('教授');

第9关 创建存储过程 2

/* 自定义存储过程：请补全下列PL/SQL语句，不要改动其他代码 */
\set SQLTERM /
CREATE OR REPLACE PROCEDURE PUBLIC.PROC_2(Class_gd  varchar) --补全1
AS
DECLARE r record;
/* 定义游标cur_t */
cursor cur_t is select  grade,gname,gnum from class where gnum>='37' and gno='0131901';  --补2
BEGIN
    OPEN cur_t;
    LOOP
        /* 使用游标cur_t */
        FETCH cur_t INTO r ;   --补全3
        IF   cur_t%FOUND     --补全4      
        THEN
	        RAISE NOTICE '%',r;
         ELSE
            EXIT;
        END IF;
    END LOOP;
    CLOSE cur_t;
END;
\set SQLTERM ; 

/* 执行存储过程 */
CALL PUBLIC.PROC_2('19级');

第10关 创建存储过程 3

/* 自定义存储过程：请补全下列PL/SQL语句，不要改动其他代码 */
\set SQLTERM /
CREATE OR REPLACE PROCEDURE proc_3(v_tno CHAR ,v_tname VARCHAR ,v_sex CHAR , v_title VARCHAR , v_birthday DATE ) --补全1
AS
BEGIN
	INSERT INTO teacher(tno,tname,sex,title,birthday)   --补全2
	VALUES (v_tno,v_tname,v_sex,v_title,v_birthday);        --补全3
END;
\set SQLTERM ; 

/* 执行存储过程 */
CALL proc_3(  '0099','曲枫','女','讲师','1994-07-04'  );   --补全4

第11关 删除存储过程

---------- BEGIN ---------- 

DROP PROCEDURE proc_2;
---------- END ---------- 

第12关 创建触发器 1

/* 自定义触发器：请补全下列PL/SQL语句，不要改动其他代码 */
\set SQLTERM /
CREATE OR REPLACE TRIGGER tr1
AFTER   UPDATE ON teaching    --补全1
FOR EACH ROW
AS
BEGIN
    RAISE NOTICE '%班级%课程的上课地点发生了变化', new.gno,NEW.cno ;  --补全2
END;
\set SQLTERM ; 

/* 执行触发事件 */
UPDATE teaching SET classroom='A102' WHERE cno='G001';

第13关 创建触发器 2

/* 自定义触发器：请补全下列PL/SQL语句，不要改动其他代码 */
\set SQLTERM /
CREATE OR REPLACE FUNCTION fun_tr2() RETURNS SETOF teaching --补全1
AS 
BEGIN
RAISE NOTICE '%教师在%教室讲授%课程',NEW.tno,NEW.classroom,NEW.cno;  --补全2
END;

CREATE TRIGGER tr2
AFTER  INSERT ON teaching   --补全3
FOR   EACH ROW AS BEGIN RAISE NOTICE '%教师在%教室讲授%课程',NEW.tno,NEW.classroom,NEW.cno;
END ;   --补全4
\set SQLTERM ; 

/* 执行触发事件 */
INSERT INTO teaching VALUES ('R003','0193','0211903','2','B001'), ('S023','0014','0131901','2','S001');

第14关 删除触发器

---------- BEGIN ---------- 

DROP TRIGGER tr1 ON teaching;
---------- END ----------