数据库Chap5 高级SQL

#sql 2023-11-24

目录：

存储过程和函数
触发器
- 创建触发器
- 删除触发器
使用程序设计语言访问数据库
事务控制

存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合
存储过程和函数可以对一段代码进行封装，以便日后调用
数据库中创建存储过程的语句为CREATE PROCEDURE，并通过CALL语句加存储过程名来调用存储过程
数据库中创建函数的语句为CREATE FUNCTION，并通过函数名来调用函数

创建和调用存储过程

【例4.75】创建名为PROC_COURSE_GRADE的存储过程，输出某门课程的学生成绩。

DELIMITER //
CREATE PROCRDURE PROC_COURSE_GRADE(IN course_id CHAR(4))
BEGIN
SELECT * FROM SC WHERE Cno=course_id;
END//
DELIMITER ;
CALL PROC_COURSE_GRADE("1"); #调用，查询1号课程的学生成绩

第一条会将数据库的终止符修改为//，因为数据库的默认终止符为分号，为了避免与存储过程中SQL语句块中的终止符（即分号）冲突，需要先使用DELIMITER语句来修改终止符，并以“END //”结束存储过程

在调用已创建的存储过程时，需要先将数据库的终止符恢复为默认的分号，然后使用CALL语句调用存储过程。查询1号课程的学生成绩

【例4.76】创建名为PROC_COURSE_AVG_GRADE的存储过程，计算某门课程的平均学生成绩，并将结果存储在某个变量中。

DELIMITER //
CREATE PROCRDURE PROC_COURSE_AVG_GRADE(IN course_id CHAR(4),OUT avg_grade FLOAT)
BEGIN
SELECT AVG(GRADE) INTO avg_grade FROM SC WHERE Cno=course_id;
END//
DELIMITER ;

CALL_PROC_COURSE_AVG_GRADE("1",@avg_grade); #调用，结果存储在avg_grade变量中
SELECT @avg_grade;# 查询变量值

调用PROC_COURSE_AVG_GRADE存储过程，查询1号课程的平均学生成绩，并将结果存储在avg_grade变量中

创建和调用存储函数

创建函数的语法格式为：Returns返回数据类型；Return返回数据值

参数的格式与存储过程相似，但是只能是IN参数
RETURNS语句指定函数返回数据的类型

【例4.77】创建函数，返回某名学生选修的某门课程的成绩。

DELIMITER //
CREATE FUNCTION FUNC_STU_COU_GRADE(student_id CHAR(10),course_id CHAR(4))
RETURNS INT
RETURN (SELECT Grade FROM SC WHERE Sno=student_id AND Cno=course_id);
//

DELIMITER ;
SELECT FUNC_STU_COU_GRADE("2021310722","1");

调用FUNC_STU_COU_GRADE函数，查询学号2021310722的学生的1号课程成绩。

存储过程和函数的区别

返回值
- 存储过程可以通过OUT或INOUT参数返回多个值
- 函数只能返回RETURNS子句中指定的某一类型的单值或表对象。
参数
- 存储过程的参数可以为IN、OUT或INOUT
- 函数的参数只能是IN类型的。
调用
- 存储过程可以通过CALL语句作为一个独立的部分来调用和执行
- 函数可以作为查询语句的一部分来调用。另外，由于函数可以返回表对象，因此函数的返回结果也可以用在查询语句的FROM子句中。
创建函数时必须指定返回值数据类型，且函数体内必须有一个RETURNS语句。
存储过程中可以执行更新表的数据库操作，而函数则不可以。

变量和流程控制

在SQL存储过程和函数中也存在变量和流程控制的概念，从而实现一些复杂的功能
变量：可以在存储过程和函数中声明并使用变量
流程控制：可以用来改变存储过程和函数内部语句的执行顺序。包括IF语句、LOOP语句、WHILE语句、REPEAT语句、LEAVE语句等。

变量

作用范围是在BEGIN…END语句块中

变量定义：

变量名（列表）指定了定义的变量的名称
数据类型定义了这些变量的数据类型
当指定DEFAULT选项时，默认值为这些变量提供了一个默认值。如果未指定DEFAULT选项，变量的初始值为NULL

变量赋值：

流程控制

if

【例4.78】创建一个名为FUNC_GRADE_LEVEL的成绩等级评定函数，参数为学生学号与课程号，返回值为学生该门课程的成绩等级，包括：A[90-100），B[80-90），C[70-80），D[60-70），E（60以下）。

DELIMITER //

CREATE FUNCTION FUNC_GRADE_LEVEL(student_id CHAR(10)， course_id CHAR(4))
RETURNS VARCHAR(5)
BEGIN
DECLARE level VARCHAR(5) DEFAULT "E";
DECLARE grade INT;
SET grade=(SELECT Grade FROM SC WHERE Sno=student_id AND Cno=course_id);
IF grade>=90 THEN SET level="A”;
ELSEIF grade<90 AND grade>=80 THEN SET level="B";
ELSEIF grade<80 AND grade>=70 THEN SET evel="c";
ELSEIF grade<70 AND grade>=60 THEN SET level="D";
ELSE SET level="E";
END IF;
RETURN level;
END //

#调用FUNC_GRADE_LEVEL函数，查询学号2021310722的学生的1号课程成绩。
DELIMITER ;
SELECT FUNC_GRADE_LEVEL("2021310722","1");

loop

程序执行时，会重复执行LOOP后的语句块，直到循环被退出。
在LOOP语句中，使用LEAVE子句可跳出循环。
LOOP语句中必须包含LEAVE子句，否则会陷入死循环。
标签可以用来标志一个LOOP语句，为可选项。

【例4.79】创建一个名为FUNC_SUM的函数，参数为num，计算1+2+…+(num-1)+num的结果。

DELIMITER //
CREATE FUNCTION FUNC SUM(num INT)
RETURNS INT
BEGIN
DECLARE total INT DEFAULT O;

sum_loop: LOOP
SET total=total+num;
SET num=num-1;
IF num<=0 THEN LEAVE sum_loop;
END IF;
END LOOP sum_loop;

RETURN total;
END //

#调用FUNC_SUM函数，参数为10。

DELIMITER ;
SELECT FUNC_SUM(10);

while

【例4.80】创建一个名为FUNC_SUM2的函数，参数为num，计算1+2+…+(num-1)+num的结果。

DELIMITER //
CREATE FUNCTION FUNC_SUM2(num INT)
RETURNS INT
BEGIN
DECLARE total INT DEFAULT O;
WHILE num>0
DO SET total=total+num, num=num-1;
END WHILE;
RETURN total;
END //

# 调用FUNC_SUM2函数，参数为10。
DELIMITER ;
SELECT FUNC_SUM2(10);

删除`drop`存储过程和函数

触发器

触发器是与表相关的特殊的存储过程，在满足特定条件时，它会被触发执行
触发器是定义在基本表上的，当基本表被修改（比如插入、删除、更新数据）时，会激活定义在其上的触发器，该基本表称为触发器的目标表
触发器可以用来保证数据库的完整性

创建触发器

SQL中创建触发器的命令是CREATE TRIGGER：

触发器名：指定要创建的触发器的名字
触发时机：指定了触发执行的时间，可以为BEFORE（触发事件之前）或AFTER（触发事件之后）
触发事件：指定了当发生何种事件时，触发器会被激活。包括INSERT、DELETE、UPDATE
表名：指定了触发器是在哪张表上创建的
触发动作体：触发事件发生后要执行的SQL语句块
- 对于触发事件作用的每一行（FOR EACH ROW），会执行触发动作体
- 对于触发事件作用的每一行，在触发事件发生之前该行称之为OLD，在触发事件发生之后该行称之为NEW
- 可以使用OLD和NEW来访问触发事件发生前后的元组的值

【例4.81】创建一个名为TRI_PRO_STU的触发器，在每次对学生成绩进行更新时，判断该学生成绩是否提升，如果提升，将该学生学号存入Progressive_Student(Sno)中；否则，将该学生学号存入表Regressive_Student(Sno)中。

DELIMITER //
CREATE TRIGGER TRI_PRO_STU
AFTER UPDATE OF Grade ON SC
FOR EACH ROW
BEGIN
IF NEW.Grade>OLD.Grade THEN INSERT INTO Progressive_Student VALUES (NEW.Sno);
ELSE INSERT INTO Regressive_Student VALUES(NEW.Sno);
END IF;
END //
DELIMITER ;

删除触发器

使用程序设计语言访问数据库

嵌入式SQL（Embedded SQL，ESQL）是将SQL语句嵌入到程序设计语言中
- 被嵌入的程序设计语言称为宿主语言，如C、C++、Java等
- 通过调用宿主语言中可以用来访问数据库的包或库，可以达到使用嵌入式SQL来访问数据库的目的
JDBC： Java Database Connectivity，Java数据库连接
- JDBC提供Java程序访问数据库的标准接口，Java应用程序可以基于JDBC访问不同类型的数据库。
ODBC： Open Database Connectivity，开放数据库连接
- 有统一的访问数据库的接口，并为不同的数据库系统实现了相应的ODBC驱动程序。

嵌入式SQL

嵌入式SQL连接数据库：EXEC SQL CONNECT TO 数据库名字AS连接名字 USER用户名;
嵌入式SQL断开连接：EXEC SQL DISCONNECT 连接名字;或EXEC SQL DISCONNECT CURRENT;
嵌入式SQL提交与撤销：
- 执行提交语法为 EXEC SQL COMMIT WORK；
- 执行撤消语法为 EXEC SQL ROOLBACK WORK;
- 直接提交或者撤销后断开连接，即EXEC SQL COMMIT RELEASE;和 EXEC SQL ROOLBACK RELEASE;。
嵌入式SQL查询：嵌入式SQL可以将SQL查询结果赋值给高级语言的变量。

EXEC SQL BEGIN DECLARE SECTION; //开始声明
char cSname[10], queryName[10]=“李博”; int cSage; //声明变量
EXEC SQL END DECLARE SECTION; //结束声明
EXEC SQL SELECT Sname, Sage INTO :cSname, :cSage FROM Student WHERE Sname = :queryName ; 定义的变量可以输入给SQL（例如queryName），也可以从SQL获取值（例如cSName和cSage）。