数据库编程

数据库编程

---

---

第一节 存储过程

一、存储过程的基本概念

​ 存储过程是一组为了完成某项特定功能的SQL语句集，其实质上就是一段存储在数据库中的代码，它可以由声明式的SQL语句（如CREATE、UPDATE和SELECT等语句）和过程式SQL语句（如IF…THEN…ELSE控制结构语句）组成。

​ 使用存储过程的优点：

​ 1、提高运行速度；

​ 2、增强了SQL的功能和灵活性。

​ 3、可以降低网络的通信量

​ 4、减轻了程序编写的工作量；

​ 5、间接实现安全控制功能。

二、创建存储过程

语法格式：

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body[BEGIN......END]
1

​ 其中，proc_parameter的参数如下：

[IN | OUT | INOUT] param_name type
1

​ characteristic特征如下：

LANGUAGE SQL | [NOT] DETERMINISTIC | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER | INVOKER} | COMMENT 'string'
1

​ DELIMITER语法格式：

DELIMITER $$
1

​ 修改结束符为$$

说明：因为MySQL玉兴命令中结束符号为分号，编写存储过程中为了避免冲突，修改结束符号。

delimiter $$
create procedure pro_add (IN p_no varchar(10),IN p_name varchar(255))
begin
	insert into pro (pno,pname) values (p_no,p_name);
end $$
1
2
3
4
5

三、存储过程体

1、局部变量

​ 在存储过程中可以声明局部变量，它们可以用来存储时结果。要声明局部变量必须使用DECLARE语句。在声明局部变量的同时也可以对其赋一个初始值。
​ DECLARE语法格式如下：

DECLARE var_name[,...] type [DEFAULT value]
1

​ 说明：var_name为变量名；type为变量类型；

​ DEFAULT子句给变量指定一个默认值，如果不指定默认为NULL的话。

​ 例如：DECLARE sno CHAR(10)

delimiter $$
create procedure pro_add (IN p_no varchar(10),IN p_name varchar(255))
begin
declare a int default 0;

	insert into pro (pno,pname) values (p_no,p_name);
end $$

1
2
3
4
5
6
7
8

2、SET语句

​ 要给局部变量赋值可以使用SET语句，SET语句也是SQL本身的一部分。

​ 语法格式为：

SET var_name=expr[,var_name=expr]...
1

delimiter $$
create procedure pro_add (IN p_no varchar(10),IN p_name varchar(255))
begin
declare a int;
set a=10;


	insert into pro (pno,pname) values (p_no,p_name);
end $$

1
2
3
4
5
6
7
8
9
10

3、SELECT…INTO语句

​ 使用这个SELECT…INTO语法可以把选定的列值直接存储到变量中。因此，返回的结果只能有一行。

​ 语法格式为：

SELECT col_name[,...] INTO var_name[,...] table_expr
1

​ 说明：col_name是列名，var_name是要赋值的变量名。table_expr是SELECT语句中的FROM子句及后面的部分。

delimiter $$
create procedure pro_add (IN p_no varchar(10),IN p_name varchar(255))
begin
declare a int;
select Id into a from pro where pno='001';

	insert into pro (pno,pname) values (p_no,p_name);
end $$

1
2
3
4
5
6
7
8
9

4、流程控制语句

（1）IF语句

​ IF-THEN-ELSE语句可根据不同的条件执行不同的操作。

​ 语法格式为：

IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list]...[ELSE statement_list] END IF
1

​ 说明：search_condition是判断的条件，statement_list中包含一个或多个SQL语句。当search_condition的条件为真时，就执行相应的SQL语句。区别IF()函数，不要混淆。

delimiter $$
create procedure pro_add (IN p_no varchar(10),IN p_name varchar(255))
begin
declare a int;
select Id into a from pro where pno='001';

if p_no = '003' then
	delete from pro;
end if;

	insert into pro (pno,pname) values (p_no,p_name);
end $$


1
2
3
4
5
6
7
8
9
10
11
12
13
14

（2）CASE语句

​ 说明：一个CASE语句经常可以充当一个IF-THEN-ELSE语句。

delimiter $$
create procedure pro_add (IN p_no varchar(10),IN p_name varchar(255))
begin
declare a int;
select Id into a from pro where pno='001';

if p_no = '003' then
	delete from pro;
end if;
case p_no
	when '001' then
	when '002' then
	else
	
end case
	insert into pro (pno,pname) values (p_no,p_name);
end $$

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

（3）循环语句

​ MySQL支持3条用来创建循环的语句：WHILE、REPEAT和LOOP语句。在存储过程中可以定义0个、1个或多个循环语句。

​ 循环语句说明：

​ ① WHILE先判断，REPEAT后判断，WHILE条件为真进行循环，REPEAT条件为真结束循环。

​ ② 在LOOP循环内的语句一直重复至循环被退出，退出时通常伴随着一个LEAVE语句

​ ③ LEAVE语句经常和BEGIN…END或循环一起使用

​ ④ LEAVE label

5、游标

​ 一条SELECT…INTO语句返回的是带有值的一行，这样可以把数据读取到存储过程中。但是常规的SELECT语句返回的是多行数据，如果要处理它需要引入游标这一概念。

​ MySQL支持简单的游标。在MySQL中，游标一定要在存储过程或函数中使用，不能单独在查询中使用。名字要唯一游标本身是执行SELECT检索出来的结果集。

（1）声明游标

​ 语法格式：

DECLARE cursor_name CURSOR FOR select_statement
1

​ 说明：cursor_name是游标的名称，游标名称使用与表名同样的规则。select_statement是一个SELECT语句，返回是一行或多行的数据。

（2）打开游标

​ 声明游标后，要使用游标从中提取数据，就必须先打开游标。在MySQL中，使用OPEN语句打开游标，格式为：

OPEN cursour_name
1

​ 在程序中，一个游标可以打开多次，由于其他的用户或程序本身已经更新了表，所以每次打开结果可能不同。

（3）读取数据

​ 游标打开后，就可以使用FETCH…INTO语句从中读取数据。

​ 语法格式：

FETCH sursor_name INTO var_name[,var_name]...
1

​ 说明：FETCH…INTO语句与SELECT…INTO语句具有相同的意义，FETCH语句是将游标指向的一行数据赋给一些变量，子句中变量的数目必须等于声明游标时SELECT子句中列的数目。var_name是存放数据的变量名。

（4）关闭游标

​ 游标使用完以后，要及时关闭。关闭游标使用CLOSE语句，格式为：

CLOSE sursour_name
1

​ 语句参数的含义与OPEN语句中相同。

delimiter $$
create procedure pro_num (OUT ROWS INT)
begin
declare cid varchar(10);
declare FOUND BOOLEAN DEFAULT TRUE;
declare cur_cid CURSOR FOR
select pno from pro;

declare continue handler for NOT FOUND;
set FOUND=false;
set ROWS=0;
open cur_cid;
fetch cur_cid into cid;
while FOUND do
set ROWS=ROWS+1;
	fetch cur_cid into cid;
end while;
close cur_cid;

end $$

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

四、调用存储过程

​ 存储过程创建完后，可以在程序、触发器或者存储过程中被调用，但是都必须使用到CALL语句。
​ 语法格式：

CALL sp_name([parameter[,...]])
1

​ 说明：sp_name为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。parameter为调用该存储过程使用的函数，这条语句中的参数个数必须总是等于存储过程的参数个数。

mysql> call pro_add('007','产品7');
1

五、删除存储过程

​ 存储过程创建后需要删除时使用DROP PROCEDURE语句。在此之前，必须确认该存储过程没有任何依赖关系否则会导致其他与之关联的存储过程无法运行。
​ 语法格式为：

DROP PROCEDURE [IF EXISTS] sp_name
1

​ 说明：sp_name是要删除的存储过程的名称。IF EXISTS子句是MySQL的扩展，如果程序或函数不存在，防止错误。

mysql> drop proceduce pro_add;

-- 查看存储过程的定义
mysql> show create procedure pro_num;
+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure                                                                                                                                                                     | character_set_client | collation_connection | Database Collation |
+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| pro_num   |          | CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_num`(OUT ROWS INT)
begin
declare cid varchar(10);
declare FOUND BOOLEAN DEFAULT TRUE;
declare cur_cid CURSOR FOR
select pno from pro;

end | utf8mb4              | utf8mb4_general_ci   | utf8_unicode_ci    |
+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.07 sec)

mysql> 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

第二节 存储函数

一、创建存储函数

​ 创建存储函数使用CREATE FUNCTION语句。要查看数据库中有哪些存储函数，可以使用SHOW FUNCTION STATUS令。
​ CREATE FUNCTION语法格式：

CREATE FUNCTION sp_name([func_parameter[,...]])
	RETURNS type
	[characteristic ...] routine_body
1
2
3

​ 说明：存储函数的定义格式和存储过程相差不大。

delimiter $$
create function pro_add(p_no varchar(10))
return varchar(10)
deterministic
begin
	insert into pro(pno) values(p_no);
	return (p_no)
end$$

1
2
3
4
5
6
7
8
9

二、调用存储函数

​ 存储函数创建完后，就如同系统提供的内置函数（如VERSION（）），所以调用存储函数的方法也差不多，都是使用SELECT关键字。
​ 语法格式为：

SELECT sp_name ([func_parameter[,...]])
1

select pro add('008');
1

三、删除存储函数

​ 删除存储函数的方法与删除存储过程的方法基本一样，使用DROP FUNCTION语句。
​ 语法格式为：

DROP FUNCTION [IF EXISTS] sp_name
1

show function status;
drop function pro_add;
drop function IF EXISTS pro_add;
1
2
3