第三章 数据库编程

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

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

3.1 MySQL编程基础

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

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

3.1.1 常量与变量

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

1. 常量

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

1)字符串常量

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

2)数值常量

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

3)日期和时间常量

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

4)布尔值常量

​ 只有 truefalse 两个值,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_vhiredate_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) :返回mn除后的余数
  • 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种参数模式,即 INOUTINOUT

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

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 语句是对表或视图的擦汗寻语句;可以带 WHEREORDER BYGROUP 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)错误处理类型:错误处理类型只有 CONTINUEEXIT 两种;

  • 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 表示对所有没有被 SQLWARNINGNOT 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 INTOFETCH 语句之后的主变量称为“输出主变量”
  • 除了SELECT INTOFETCH 语句以外的其他SQL语句称为“输入主变量”

1. 主变量的定义

在使用主变量之前,必须在SQL语句 BEGIN DECLARE SECTIONEND 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 语句名;