数据库实验6

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

实验要求

在 TESTDB 数据库中使用 IF EXISTS…ELSE 流程控制语句，实现：如果班级表中有班级人数在 37 人以上（包含 37）的班级，则将班级名、学院的详细信息进行显示，否则输出“没有人数在 37 人以上的班级”。

代码

/* 请在指定处（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流程控制语句

实验要求

在 TESTDB 数据库中使用 CASE 流程控制语句，实现：从教师表中读取工号、姓名和性别。如果性别字段值为“男”，则输出“M”；如果为“女”，则输出“F”；否则输出“其他”。

代码

/* 请在指定处（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流程控制语句

在 TESTDB 数据库中使用 FOR 流程控制语句，实现：修改班级表，将软件学院所有班级的人数都加1。

代码

/* 请在指定处（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

实验要求

在 TESTDB 数据库中按要求创建自定义函数。

1、函数实现功能：能够根据职称，查询所属该职称的教师信息。

2、调用函数：显示职称为教授的教师信息。

代码

/* 自定义函数：请补全下列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

实验要求

在 TESTDB 数据库中按要求创建自定义函数。

1、函数实现功能：能够根据年级名，查询该年级的班级人数，如果班级人数>=37，则输出年级名、班级名和班级人数。

2、调用函数：显示19级的班级信息。

代码

/* 自定义函数：请补全下列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

实验要求

在 TESTDB 数据库中按要求创建自定义函数。

1、函数实现功能：能够向教师表中添加一条记录。

2、调用函数：实现教师记录’0017’,‘崔洛’,‘女’,‘副教授’,'1974-04-25’的插入。

代码

/* 自定义函数：请补全下列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

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

实验要求

在 TESTDB 数据库中按要求创建自定义函数。

1、函数实现功能：能够向教师表中添加一条记录。

2、调用函数：实现教师记录’0017’,‘崔洛’,‘女’,‘副教授’,'1974-04-25’的插入。

代码

/* 自定义函数：请补全下列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关：删除自定义函数

实验要求

删除自定义函数fun1。。

代码

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

第8关：创建存储过程 1

实验要求

在 TESTDB 数据库中按要求创建存储过程。

1、存储过程实现功能：能够根据职称，查询所属该职称的教师信息。

2、执行存储过程：显示职称为教授的教师信息。

代码

/* 自定义存储过程：请补全下列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

实验要求

在 TESTDB 数据库中按要求创建存储过程。

1、存储过程实现功能：能够根据年级名，查询该年级的班级人数，如果班级人数>=37，则输出年级名、班级名和班级人数。

2、执行存储过程：显示19级的班级信息。

代码

/* 自定义存储过程：请补全下列PL/SQL语句，不要改动其他代码 */
\set SQLTERM /
CREATE OR REPLACE PROCEDURE PUBLIC.PROC_2(Class_gd VARCHAR) --补全1
AS
DECLARE r record;
/* 定义游标cur_t */
cursor cur_t for 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

实验要求

在 TESTDB 数据库中按要求创建存储过程。

1、存储过程实现功能：能够向教师表中添加一条记录。

2、执行存储过程：实现教师记录’0099’,‘曲枫’,‘女’,‘讲师’,'1994-07-04’的插入。

代码

/* 自定义存储过程：请补全下列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关：删除存储过程

实验要求

删除存储过程proc_2。

代码

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

实验收获：

1. 掌握了PL/SQL与应用的部分内容。
2. 掌握了流程控制语句以及自定义函数的使用。
3. 掌握了存储过程和触发器的创建和使用。

心得体会：

1. 通过学习第七章“PL/SQL”的内容，我完成了这次的实验报告，在这次报告中，我完成了对流程控制语句以及自定义函数的使用以及存储过程和触发器的相关操作，实现了IF EXISTS…ELSE、CASE、FOR流程控制语句的使用，以及自定义函数的创建与删除操作，以及存储过程的创建和删除，触发器的创建和删除等操作。
2. 在进行这次实验报告时候，我对PL/SQL的使用有了更深的理解，对程序设计的方法也有更深一步的了解。