第三章 数据库编程

Posted on | In , ,

标准SQL是非过程化的查询语言，具有操作统一，面向集合、功能丰富、使用简单等多项优点；

但它的缺点在于——缺少流程控制能力，难以实现应用业务中的逻辑控制；所以引入了SQL编程；

3.1 MySQL编程基础

需要将频繁使用的业务逻辑封装成存储程序；

MySQL提供了用于编写结构化程序的数据类型、常量、变量、运算符和表达式等；

3.1.1 常量与变量

程序本身不能改变的数据成为常量；程序运行中可以改变其值的数据成为变量；

1. 常量

常量的格式取决于其数据类型，常用的常量包括字符串常量、数值常量、日期和时间常量、布尔值常量和NULL值；

1）字符串常量

​ 用单引号与双引号括起来的字符序列。推荐使用单引号；

2）数值常量

​ 可以分为整数常量和小数常量；

3）日期和时间常量

​ 使用特定格式的字符日期值表示，单引号括起来。例如：’2018/0717’、’2018-07-17 10:30:20’

4）布尔值常量

​ 只有 true 和 false 两个值，SQL命令的运行结果用1代表 true ，用0代表 false

5）NULL 值

​ 适用于各种字段类型，表示“不确定的值”。NULL值参与的运算，结果仍为 NULL 值；

2. 变量

变量有名字和数据类型两个属性。MySQL中存在两种变量，一种是系统定义和维护的全局变量，通常名称前加 @@ 符号；另一种是用户定义的用来存放中间结果的局部变量，通常在名称前加 @ 符号；

1）局部变量

​ 局部变量作用范围限制在程序内部

​ （1）局部变量的定义与赋值：使用 SET 语句定义局部变量，并为其赋值；语法格式：

1
SET @局部变量名 = 表达式1[, @局部变量名 = 表达式2，··· ];

​ 注意：可同时定义多个变量；

​ （2）局部变量的显示：使用 SELECT 语句显示局部变量。语法格式：

1
SELECT @局部变量名 [ ,@局部变量名,··· ];

【例】查询emp中雇员SMITH的sal值赋给变量salary，并显示其值

1
2
SET @salary = (SELECT sal FROM emp WHERE ename = 'SMITH');
SELECT @salary;

【例】查询emp中雇员SMITH的job和hiredate值赋给变量job_v，hiredate_v并显示两个变量的值；

1
2
3
SELECT job,hiredate INTO @job_v, @hiredate_v
	FROM emp WHERE ename = 'SMITH';
SELECT @job_v, @hiredate_v;

2）全局变量

全局变量是MySQL系统提供并赋值的变量。用户不能定义全局变量，只能使用。

常用系统全局变量及说明如下表：

全局变量名称 说 明
@@back_log 返回MySQL主要连接请求的数量
@@basedir 返回MySQL安装基准目录
@@license 返回服务器的许可类型
@@port 返回服务器侦听TCP/IP连接所用的端口
@@storage_engine 返回存储引擎
@@version 返回服务器版本号

3.1.2 常用系统函数

函数是一组编译好的SQL语句，定义了一组操作，返回数值、数值集合，或执行一些操作；

MySQL 的系统函数包括：字符串函数、数学函数、日期和时间函数、系统信息函数等；

1. 字符串函数

1）计算字符串字符数和字符串长度的函数

  • CHAR_LENGTH(str) ：返回字符串 str 所包含的字符个数；
  • LENGTH(str) ：返回字符串的字节长度。一个汉字是3个字节，一个数字或字母是1个字节；

【例】计算字符串字符数和字符串长度

1
2
3
4
5
SELECT `CHAR_LENGTH('CHINA'), LENGTH('CHINA');
%  5   5  %

SELECT `CHAR_LENGTH('中国'), LENGTH('中国');
%  2   6  %

2）合并字符串函数

  • CONCAT(s1,s2,···) ：返回连接参数产生的字符串，若任一参数为NULL，则返回NULL；
1
SELECT CONCAT('MySQL版本： ', @@version);

3）字符串大小写转换函数

  • LOWER(str) ：把str中字母字符串全转换成小写字母
  • UPPER(str) ：把str中字母字符串全转换成大写字母

4）删除空格函数

  • LTRIM(str) ：返回删除前导空格的字符串

  • RTRIM(str) ：返回删除后导空格的字符串

  • TRIM(str) ：返回删除两侧空格的字符串

5）取子串函数

  • SUBSTRING(str, start, length)

2. 数学函数

  • ABS(x)
  • PI()
  • SQRT() ：非复数的二次方根
  • mod(m,n) ：返回m被n除后的余数
  • ROUND(x,y) ：把 x 四舍五入到 y 指定的精度返回。y 为负数，则将保留 x 值到小数点左边 y 位；

3. 日期和时间函数

1）获取当前系统的日期及取日期的年、月、日的函数；

  • CURDDATE
  • year(d)、MONTH(d)、DAY(d) ：分别返回日期或日期时间 d 的年、月、日的值；

2）获取当前系统的日期时间的函数；

  • CURRENT_TIMESTAMP()
  • LOCALTIME()
  • NOW()
  • SYSDATE()

上面几个函数作用相同，均返回当前系统的日期时间；格式为：YYYY-MM-DD HH:MM:SS ；

4. 系统信息函数

  • USER() ：返回当前登录的用户名
  • DATABASE() ：返回当前所使用数据库的名
  • VERSION() ：返回 MySQL 服务器版本号

5. 条件控制函数

1）IF()函数：格式为 IF(条件表达式, v1, v2) ，如果表达式为真，则函数返回 v1 值，否则返回 v2 值。

1
2
SELECT ename, IF(comm IS NULL, 0, comm) 奖金
	FROM emp LIMIT 5;

2）CASE()函数

1
2
3
4
5
6
CASE 表达式
	WHEN v1 THEN r1
	WHEN v2 THEN r2
	...
	[ ELSE rn ]
END

6. 数据类型转换函数

CASE(x AS 新类型) 和 CONVERT(x 新类型) 两个函数作用相同，都是把一种类型的值转换为另一种类型的值；

3.2 程序控制流语句

MySQL有编写过程化代码的语法结构，可进行顺序、分支、循环、存储过程、存储函数、触发器等程序设计；

3.2.1 语句块、注释和重置命令结束标记

1. 语句块

​ BEGIN ··· END 用于定义SQL语句块，语法格式：

1
2
3
BEGIN
	SQL语句 | SQL语句块
END

说明：

（1）BEGIN ··· END 语句块包含了该程序块的所有处理操作，允许语法块嵌套

（2）单独使用 BEGIN ··· END 语法块没有任何意义，主要用于存储过程、存储函数、触发器等存储程序内部才有意义；

2. 注释

单行注释和多行注释

1）单行注释 ：使用 “ # ” 符号

2）多行注释： /* 和 */ 括起来是多行注释

3. 重置命令结束标记

为什么要重置命令结束标记？

因为MySQL中，服务器处理的语句是以分号为结束标记的；但在创建存储函数、存储过程的时候，在函数体或存储过程体中可以包含多个SQL语句，每个SQL语句都是以分号为结尾的，而服务器处理程序遇到分号则结束程序的执行；所以需要重置命令结束标记；

MySQL使用 DELIMITER 语句将MySQL语句的结束标记修改为其他符号

1
DELIMITER 符号

说明

（1）符号可以是一些特殊符号，例如两个“#”、两个“@”、两个“$”、两个“%”等。

（2）恢复使用分号为结束标记，执行 “ DELIMITER ; ” 即可；

3.2.2 存储函数

用MySQL编写程序可以根据应用程序的需要创建存储函数

1. 存储函数的创建

创建存储函数的，要用到 CREATE FUNCTION 语句， 语法为:

1
2
3
4
5
6
CREATE FUNCTION 函数名 ( [ 参数名 参数数据类型 [ ,··· ] ] )
RETURNS 函数返回值的数据类型
BEGIN
	函数体;
	RETURN 语句;
END

2. 调用存储函数

对新创建的存储函数，调用方法与调用系统函数相同，语法格式：

1
SELECT 函数名 ( [ 参数值 [ ,··· ] ] )

接下来的例子涉及了 delimeter 在定义存储函数时的使用：

1
2
3
4
5
6
7
8
9
10
DELIMITER @@
CREATE FUNCTION name_fn(dno DECIMAL(2))
RETURNS VARCHAR(14)
BEGIN
	RETURN(SELECT dname FROM dept
			WHERE deptno = dno );
END@@

DELIMITER ;
SELECT name_fn(20);

3. 删除存储函数

用 DROP FUNCTION 语句删除：

1
DROP FUNCTION 函数名;

3.2.3 条件判断语句

1. 程序中变量的使用

局部变量可在程序中声明并使用，这些变量的作用范围是 BEGIN···END 语法块

1） 声明变量

在存储程序（例如存储函数、存储过程、触发器等）中需要使用 DECLARE 语句声明局部变量，语法格式：

1
DECLARE 局部变量名 [ ,局部变量名,··· ] 数据类型 [ DEFAULT 默认值 ];

说明

（1）DECLARE 声明的局部变量，变量名前不能加 @

（2）DEFAULT 子句提供了一个默认值，如果没有给默认值，则局部变量的初始值默认为 NULL ；

2）为变量赋值

变量声明后，用 SET 命令为变量赋值；

1
SET 局部变量名 = 表达式1 [, 局部变量名 = 表达式2,··· ];

2. IF语句

IF 有两种形式：

1）形式一

1
2
3
4
5
IF <条件> THEN
	SQL语句块 1;
[ELSE
	SQL语句块 2; ]
END IF;

2）形式二

1
2
3
4
5
6
7
8
IF <条件1> THEN
	SQL语句块 1;
ELSEIF <条件2> THEN
	SQL语句块 2;
···
ELSE
	SQL语句块n;
END IF;

3. CASE语句

1）形式一

1
2
3
4
5
6
7
CASE <表达式>
	WHEN <表达式1> THEN SQL语句块1;
	WHEN <表达式2> THEN SQL语句块2；
	···
	WHEN <表达式n> THEN SQL语句块n;
	[ ELSE SQL语句块n+1; ]
END;

2）形式二

1
2
3
4
5
6
7
CASE
	WHEN <条件1> THEN SQL语句1;
	WHEN <条件2> THEN SQL语句2;
	···
	WHEN <条件n> THEN SQL语句n;
	ELSE SQL语句块 n+1;
END;

3.2.4 循环语句

1. LOOP循环

LOOP循环为无条件循环，如果没有指定 LEAVE 语句，循环将一直运行，成为死循环；通常是结合条件语句使用；条件语句里嵌入 LEAVE 语句；条件为真，跳出循环；

1
2
3
4
5
6
标签: LOOP		# # 标签 是自定义的
	SQL语句块;
	IF <条件表达式> THEN	
		LEAVE 标签;	# # 通过自己定义的标签结束 LOOP 循环
	END IF;
END LOOP;

2. WHILE循环

1
2
3
WHILE <条件表达式> DO
	SQL语句块
END WHILE;

3. REPEAT循环

1
2
3
4
REPEAT
	SQL语句块;
	UNTIL <条件表达式>
END REPEAT;

3.3 存储过程

概念：存储过程就是一条或多条SQL语句的集合；利用这些SQL语句完成一个或多个逻辑功能；

环境：存储过程可以被赋予参数，存储在数据库中，可以被用户调用，也可以被JAVA 或 C# 等调用；

特性：并且存储过程都是编译好的，调用时不用再次编译，提高了程序的运行效率；

3.3.1 创建存储过程

要用 CREATE PROCEDURE 语句

1
2
3
4
CREATE PROCEDURE 存储过程名()
BEGIN
	过程体;
END

3.3.2 调用存储过程

使用 CALL 语句直接调用存储过程。

1
CALL 存储过程名();

3.3.3 存储过程的参数

存储过程可以接受和返回 0 到多个参数，使程序变得灵活； MySQL 有3种参数模式，即 IN、 OUT、 INOUT。

创建带参数的存储过程的语法格式如下：

1
2
3
4
5
6
7
8
CREATE PROCEDURE 存储过程名(
	[ IN|OUT|INOUT ] 参数1 数据类型
	[ IN|OUT|INOUT ] 参数2 数据类型
	...
)
BEGIN
	过程体;
END

1. IN参数

IN 参数作为输入参数，该参数值由调用者传入，并且只能够被存储过程读取；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER @@
CREATE PROCEDURE dept_pl(
	IN p_deptno DECIMAL(2,0),
	IN p_dname VARCHAR(14),
	IN p_loc VARCHAR(13)
)
BEGIN
INSERT INTO dept
	VALUES (p_deptno, p_name, p_loc);
END@@

DELIMITER ;
CALL dept_pl(50, 'HR', 'CHINA');
SELECT * FROM dept WHERE deptno = 50;

2. OUT参数

OUT参数为输出参数，该类型的参数值由存储过程写入；

比较适合于向调用者返回多条信息的过程；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER @@
CREATE PROCEDURE dept_p2(
	IN i_no DECIMAL(2,0),
	OUT o_name VARCHAR(14),
	OUT o_loc VARCHAR(13)
)
BEGIN
SELECT dname, loc INTO o_name, o_loc FROM dept
	WHERE deptno = i_no;
END@@

DELIMITER ;
CALL dept_p2(10, @v_name, @v_loc);
SELECT @v_dname, @v_loc;

3. INOUT参数

INOUT 类型得参数同时具有 IN , OUT 的特性，在过程中可以读取和写入该类型参数；

IN 参数可以接收一个值，但不能修改这个值；

OUT参数被调用时为空，在执行过程中将这个参数指定一个值，过程执行结束后返回；

使用 INOUT 参数实现两个数的交换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DELIMITER @@
CREATE PROCEDURE swap(
	INOUT p_num1 int,
	INOUT p_num2 int
)
BEGIN
	DECLARE var_temp int;
	SET var_temp = p_num1;
	SET p_num1 = p_num2;
	SET p_num2 = var_temp;
END@@

DELIMITER ;
SET @v_num1 = 1;
SET @v_num2 = 2;
CALL swap(@V_num1, @v_num2);
SELECT @v_num1, @v_num2;

3.3.4 删除存储过程

指删除数据库中已存在的存储过程；使用 DROP PROCEDUER 语句来删除存储过程；

1
DROP PROCEDURE 存储过程名;

3.4 游标

当通过 SELECT 语句查询时，返回的是一个由多行记录组成的集合；而程序设计语言不能处理以集合形式返回的数据，为此SQL提供了游标机制；

游标充当指针使用，使应用程序设计语言一次只能处理查询结果中的一行；

3.4.1 游标的定义和使用

游标是在存储程序中使用包含 SELECT 语句

1. 声明游标

使用 DECLARE 语句；语法形式：

1
2
DECLARE 游标名 CURSOR
	FOR SELECT 语句;

说明：

（1）声明游标的作用是得到一个 SELECT 查询结果集。游标为用户提供逐行处理的途径；

（2）SELECT 语句是对表或视图的擦汗寻语句；可以带 WHERE，ORDER BY，GROUP BY 等子句；但不能使用INTO

2. 打开游标

打开游标使用OPEN语句，语句形式：

1
OPEN 游标名;

游标必须先声明后打开，在打开游标时，SELECT语句的查询结果被传送到了游标工具区，供用户读取；

3. 提取数据

打开游标后，使用 FETCH 语句将游标工作区中的数据读取到变量中，语法格式如下：

1
FETCH 游标名 INTO 变量名1[, 变量名2···];

成功打开游标时，游标指针在第一行之前，而 FETCH 语句使游标指向下一行。

可以在循环中使用 FETCH 语句，每次循环都会从表中读取一行数据；

4. 关闭游标

游标使用完后，需要用 CLOSE 语句关闭，语法形式:

1
CLOSE 游标名;

3.4.2 异常处理

在存储过程中出现错误可能导致程序终止。

错误发生时希望不要终止执行，而是通过MySQL的错误处理机制帮助控制程序流程；

存储过程中的错误处理通过 DECLARE HANDLER 语句实现，语法如下：

1
DECLARE 错误处理类型 HANDLER FOR 错误触发条件 自定义错误处理程序;

说明

（1）一般情况下，异常处理语句置于存储程序中才有意义；

（2）异常处理语句必须放在所有变量和游标定义之后，所有MySQL表达式之前；

（3）错误处理类型：错误处理类型只有 CONTINUE 和 EXIT 两种；

  • CONTINUE 表示错误发生后MySQL立即执行自定义错误处理程序，然后忽略该错误继续执行其他的程序；
  • EXIT 表示错误发生后 MySQL 立即执行自定义错误处理程序，然后立即停止其他MySQL语句的执行；

（4）错误触发条件：错误除法条件定义了自定义错误处理程序运行的时机；错误触发条件的形式：

1
2
3
4
5
SQLSTATE 'ANSI标准错误代码'
|MySQL错误代码
|SQLWARNING
|NOT FOUND
|SQLEXCEPTION
  • 错误触发条件支持标准的SQLSTATE定义，也支持MySQL的错误代码；
  • SQLWARNING表示对所有以01开头的SQLSTATE代码的速记；
  • NOT FOUND表示对所有以02开头的SQLSTATE代码的速记
  • SQLEXCEPTION 表示对所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码的速记；

（5）自定义错误处理程序 错误发生后，MySQL会立即执行自定义错误处理程序中的 MySQL 语句；

// 这个过程比较抽象；我觉得需要结合代码来认识；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DELIMITER @@
CREATE PROCEDURE emp_up_pro()
	BEGIN
		DECLARE v_empno DECIMAL(4,0);
		DECLARE v_sal DECIMAL(7,2);
		DECLARE v_comm DECIMAL(7,2);
		DECLARE flag BOOLEAN DEFAULT TRUE;
		DECLARE comm_cur CURSOR
			FOR SELECT empno, sal FROM emp_c;
		DECLARE CONTINUE HANDLER FOR NOT FOUND
			SET flag = FALSE;
		OPEN comm_cur;
		WHILE flag DO
			FETCH comm_cur INTO v_empno, v_sal;
			IF v_sal < 500 THEN SET v_comm = v_sal * 0.25;
			ELSEIF v_sal < 1000 THEN SET v_comm = v_sal * 0.2;
			ELSEif v_sal < 3000 THEN SET v_comm = v_sal * 0.15;
			ELSE SET v_comm = v_sal * 0.12;
			END IF;
			UPDATE emp_c SET comm = v_comm
				WHERE empno = v_empno;
		END WHILE;
		CLOSE comm_cur;
	END@@

这里面的异常处理用于处理在游标到达空指针时使得程序正常结束；

3.5 嵌入式SQL

被嵌入的程序设计语言被称为宿主语言，简称主语言；

3.5.1 SQL与宿主语言接口

嵌入式SQL语句一般采用预编译方法处理，由RDBMS的预处理程序对源程序进行扫描，识别出ESQL语句，把他们转换成主语言调用语句，以使主语言编译程序能够识别它们，最后由主语言的编译程序将整个源程序编译成目标码；（SQL语句被转为了一种供主语言调用的语句）

1. 区分主语言语句与SQL语句

在嵌入式SQL中需要区分主语言语句与SQL语句；所以需要把所有的SQL语句加前缀；C语言时的形式为：

1
EXEC SQL SQL语句;

2. 嵌入式SQL语句与主语言的通信

程序执行过程中，主语言需要与SQL语句进行信息交换，其间的通信过程如下：

  • SQL语句将执行状态信息传递给主语言；

    • 主语言得到状态信息后，可以根据这个状态信息来控制程序流程；
    • 向主语言传递SQL执行信息，主要用SQL通信区实现；

  • 主语言需要提供一些变量参数给SQL语句；

    • 在主语言中定义主变量，在SQL语句中使用主变量，将参数值传递给SQL语句；
  • 将SQL语句查询数据库的结果返回给主语言做进一步处理；
    • 如果SQL语句返回的时一条数据库记录，可使用主变量；若返回值为多条记录的集合，则使用游标；

3.5.2 SQL通信区

SQL语句执行后要返回给应用程序若干信息，主要包括描述系统当前状态和运行环境的各种参数；这些信息被送到SQL通信区——SQLCA中。

主语言的应用程序从SQLCA中取出这些状态信息，据此决定后面语句的执行；

SQLCA是一个数据结构，其中有一个系统变量SQLCODE，用来存放每次执行SQL语句后返回的代码；

应用程序每执行一条SQL语句后均测试一下SQLCODE的值，了解SQL语句的执行情况并作相应的处理；如果SQLCODE等于预定义的常量SUCCESS，则表示成功，否则在SQLCODE中存放错误代码。

3.5.3 主变量的定义与使用

在嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据。在SQL语句中使用的主语言程序变量简称为主变量。

主变量根据作用不同，分为输入变量和输出变量；

  • 在 SELECT INTO 和 FETCH 语句之后的主变量称为“输出主变量”
  • 除了SELECT INTO 和 FETCH 语句以外的其他SQL语句称为“输入主变量”

1. 主变量的定义

在使用主变量之前，必须在SQL语句 BEGIN DECLARE SECTION 与 END DECLARE SECTION 之间声明；

声明后可以在SQL语句中任何一个能够使用表达式的地方出现，为了与数据库对象名区别，应在SQL语句中的主变量名前加冒号（:）；

注意

（1）主变量使用前，必须在嵌入SQL语句的说明部分明确定义；

（2）主变量定义时，所用的数据类型为主语言提供的数据类型；注意大小写；

（3）在SQL语句中使用主变量时，必须在主变量前加一个冒号（:），在主语言语句中不需要加冒号

（4）主变量不能是SQL命令的关键字；

（5）在一条SQL语句中，主变量只能使用一次；

2. 在SELECT语句中使用主变量

在嵌入式SQL中，如果查询结果为单记录，则 SELECT 语句需要用 INTO 子句指定查询结果的存放地点——主变量；

3. 在INSERT语句中使用主变量

在 INSERT 语句的 VALUES 子句中，可以使用主变量指定插入的值：

1
2
EXEC SQL INSERT INTO grade(学号,课号,分数)
	VALUES(:hsno, :hcno, :hgrade);

4. 在UPDATE语句中使用主变量

在 UPDATE 语句的 SET 子句和 WHERE 子句中，均可使用主变量；

1
2
3
EXEC SQL UPDATE grade
	SET 分数 = :mgrade
	WHERE 学号 = :msno AND 课号 = :mcno;

5. 在DELETE语句中使用主变量

在 DELETE 语句的 WHERE 子句中，可以使用主变量指定删除条件：

1
2
EXEC SQL DELETE FROM grade
	WHERE 学号 = :msno;

3.5.4 嵌入式SQL中游标的定义与使用

用嵌入式 SQL 语句查询数据分为两类情况：一类是多行结果，一类是多行结果；

对于单行结果，可以使用 SELECT INTO 语句；

对于多行结果，必须使用游标来完成；

包括四步：声明游标，打开游标，提取数据，关闭游标；

1. 声明游标

1
2
EXEC SQL DECLARE 游标名 CURSOR
	FOR SELECT 语句;

2. 打开游标

1
EXEC SQL OPEN 游标名;

3. 提取数据

1
EXEC SQL FETCH 游标名 INTO 主变量[,主变量，···];

4. 关闭游标

1
EXEC SQL CLOSE 游标名;

3.5.5 动态SQL语句

动态SQL技术主要有以下两个SQL语句；

1）动态SQL预备语句；

1
EXEC SQL PREPARE 动态 SQL 语句名 FROM 共享变量或字符串;

2）动态SQL执行语句

1
EXEC SQL EXECUTE 动态 SQL 语句名;