(3) Mysql

程序员文章站 2024-01-20 15:24:16

...

1、SQL存储过程的基础知识 ------------------------------------------------------------ 在深入理解MySq之前，我们先理下一些简单的问题 Q：什么是存储过程？（stored procedure） A：是一段写好的SQL代码，特别的就是它是存在数据库的目录里。所以外

1、SQL存储过程的基础知识------------------------------------------------------------

在深入理解MySq之前，我们先理下一些简单的问题

Q：什么是存储过程？（stored procedure）

A：是一段写好的SQL代码，特别的就是它是存在数据库的目录里。所以外部程序可以直接调用数据库里面定义好的存储过程，另外数据库内部的触发器(trigger)、或者其他存储过程也可以调用它。

Q：存储过程有什么好处？有什么坏处？

A：

先看看好处吧：

1、首先在性能上的提高，比起通过应用程序发送sql语句给数据库执行，让数据库自己内部执行存储过程效率更高、速度更快(存储过程将sql编译好后存在数据库目录下)；

2、存储过程还减少了应用程序同服务器之间的信息交互频率，可以想象在不是使用存储过程的情况，应用程序需要发送多条sql指令给服务器，而使用存储过程则只要一条调用存储过程的语句，然后获取需要的数据就ok了(一个存储过程中可以写N多的Sql语句)。

3、存储过程重用性比较高，并且是透明的，因为保存在数据库里面所以对任何应用来说都可以使用。新的应用只需要调用相应的存储过程就可以得到相应的数据服务。

4、存储过程也是种安全的做法，数据库管理员可以对那些没有权限访问数据库中的表格的应用，给他们使用存储过程的权限来获得数据服务，可以看到这个时候这些存储过程好像我们编程里面的”接口“这个概念。对于安全性要求很高的系统，例如银行，基本上常用的操作都是通过存储过程或者函数来进行的，这样完全对应用”隐藏“了表格。

当然也有坏处：

1、存储过程会使得数据库占用的系统资源加大(cpu、memory)，数据库毕竟主要用来做数据存取的，并不进行复杂的业务逻辑操作。

2、因为存储过程依旧是sql，所以没办法像编程语言那样写出复杂业务逻辑对应的存储过程。

3、存储过程不容易进行调试。

4、存储过程书写及维护难度都比较大。

了解这些优缺点对我们权衡使用存储过程有很大的帮助

2、第一个MySql存储过程的建立-----------------------------------------------------------

看看如何创建一个存储过程。虽然通过命令行可以创建，但基本通过MySQL提供的Query browser来创建。

1、首先我们通过Administrator在test数据库中创建一个简单的表名叫”products“的，里面包括下面这两个字段：

create TABLE if not exists products(
id int(10),
name VARCHAR(45)
);

（使用navicat工具创建MySQL存储过程 http://ysj5125094.iteye.com/blog/2090315）

2、然后打开Query Browser，并且在test中创建存储过程，存储过程名称为”GetAll“。

3、可以看到系统会默认加入一些内容。这些内容当然可以不用理会，我们的存储过程就是从BEGIN到END中间的那些。点击Continue可以把存储进程加入到test数据库的目录中去。当然这里系统提供的代码也比较简单，一个就是重新定义存储过程代码的分隔符为”$$“，因为默认的是”；“而分号却是SQL的分隔符所以要重新定义一下分隔符，然后等存储过程写完后在把分隔符恢复为”；“。接着分隔符定义的下一句就是如果当前存在同名的进程，则进行删除。

4、在begin和end之间写入一行select代码

delimiter $$
drop procedure if exists test.GetAll $$
create procedure test.GetAll()
BEGIN
#Routine body goes here...
select * from products;
END $$

delimiter ;

----------------------------------

delimiter $$
drop procedure if exists test.GetAllPjm $$
create procedure test.GetAllPjm()
BEGIN
#Routine body goes here...
select * from products;
END $$
delimiter ;

----------------------------------

(3) Mysql

5、在右边的窗口中双击GetAll这个存储进程，并且执行，则得到结果

----------------------------------

查询数据库中的存储过程

方法一：

select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'

方法二：

show procedure status;

查看存储过程或函数的创建代码

show create procedure proc_name;
show create function func_name;

删除存储过程（GetAll 不能加单引号''）

drop procedure GetAll ;

drop procedure test.GetAll ;

3、变量---------------------------------------------------------------------------------------------------------------------

1、变量的定义

在Mysql里面可以像我们写代码中一样定义变量来保持中间结果，看下面的格式：

[sql] view plaincopy

DECLARE variable_name datatype(size) DEFAULT default_value;

DECLARE相当于关键字，有点类似var，表示定义一个变量；然后variable_name是你指定的变量名称；datatype表示数据类型而括号内的size表示对应类型的格式比如varchar(50)这样子，这里需要注意的是datatype只能是MySql支持的那些基本数据类型；DEFAULT关键字指明了变量的默认值为default_value。

定义多个相同类型的变量可以这样子：

[sql] view plaincopy

DECLARE x, y INT DEFAULT 0

表示定义了两个变量x、y，整形，默认值为0

2、变量的赋值

同程序里面一样，定义了变量，我们也需要在程序中对他赋值，下面是示例代码：

DECLARE total_count INT DEFAULT 0
SET total_count = 10;

可以看到进行赋值时候需要在前面加上：SET 这个关键字。

当然由于存储过程的特殊性(用于数据库的数据操作)，所以除了SET这种赋值方法，还可以通过SELECT INTO语句将返回的值赋给变量：

DECLARE total_products INT DEFAULT 0；

SELECT COUNT(*) INTO total_products FROM products；

看到这里把products的记录数赋给了total_products这个变量。所以如果上面我们表格内是3个记录的话，这里total_products就是等于3了

3、变量的Scope(作用范围)

变量的作用范围同编程里面类似，在这里一般是在对应的begin和end之间。在end之后这个变量就没有作用了，不能使用了。这个同编程一样。

另外有种变量叫做会话变量(session variable)，也叫做用户定义的变量(user defined variable)。这种变量要在变量名称前面加上“@”符号，叫做会话变量，代表整个会话过程他都是有作用的，这个有点类似于全局变量一样。这种变量用途比较广，因为只要在一个会话内(就是某个应用的一个连接过程中)，这个变量可以在被调用的存储过程或者代码之间共享数据。下面看个简单的例子，以理解这种变量和本地变量的区别：

首先修改下之前的存储过程如下：

delimiter $$
drop procedure if exists test.GetAll $$
create procedure test.GetAll()
BEGIN
DECLARE t1 int DEFAULT 1;
set t1 = t1+1;
set @t2 =@t2+1;
SELECT t1,@t2;
END $$
delimiter ;

这里定义了一个是local的变量，一个是session的变量，对于session的变量，不需要定义，mysql根据你赋给该变量的值来确定类型(更详细的需要另一篇文章来解释)。

然后我们在Query browser上面先执行这样一条语句来对@t2进行赋初值：

set @t2 =1;

赋予往初值后我们调用我们的存储进程。每次执行这个存储进程我们发现t1的值是不变的，而@t2每次会增加1，因为我们这是在一个会话里面所以@t2就一直作用着，每次自己增加1.

CALL GetAll();

需要注意的是需要在同一个会话中才会有效。如果在Navicat中新建一个查询只执行call getAll() 那么此次会话没有执行对@t2的赋值操作。故查不到结果。

4、参数--------------------------------------------------------------------------------------------------------------------

1、参数分类

参数，同编程一样，MySql中存储过程也可以带参数，前面的例子中我们没有使用参数，在下面的例子中我们将使用到参数；MySql的参数分为三类:IN、OUT、INOUT，即使是字面上这三种类型的参数也很好理解：

——IN：这是个默认的类型，也就是如果参数没指定类型的话，那么默认就是IN类型了，这种参数主要是传递进去的值，这个值提供给存储过程使用，另外存储过程对其做的改变不会对传入的参数发生作用。

——OUT：这个主要是存储过程要传递出去的值，也就是存储过程给你将它改变，并且传回去给调用它的程序。

——INOUT：则把上面两个的特点合在一起了，即可以传递值给存储过程使用，同时存储过程也可以改变这个值在传给调用它的程序。

2、参数定义

参数的定义如下：

MODE param_name param_type(param_size)；

这里MODE可以是IN、OUT、INOUT；param_name就是参数名称；param_type则是类型。当然要注意的是名字不能和表格里的字段重名。

看看下面的使用例子：

delimiter $$
drop procedure if exists test.GetAll $$
create procedure test.GetAll(in containString varchar(255))
BEGIN
	#Routine body goes here...
        select * from products where name like CONCAT('%',containString,'%');
END $$
delimiter ;

这个例子里面，我们对存储过程传入了一个参数，这里参数是个字符串，我们希望通过这个存储过程来列出products表格中name字段中包含有传入的字符串的记录。这里数据主要用途就是传给存储过程使用，所以使用IN。下面是对应的执行结果：

call GetAll('ca')

注意到传递给了GetAll这个存储过程一个数据为“ca”的参数（ call GetAll(“ca”) 亦可）

在这个基础上我们把记录数作为存储过程输出给外部的数据来看一下OUT的使用。

delimiter $$
drop procedure if exists test.GetAll $$
create procedure test.GetAll(in containString varchar(255),OUT totalnum INT)
BEGIN
   select count(name) into totalnum 
   from products
   where name like CONCAT('%',containString,'%');
END $$
delimiter ;

上面把记录数保持到输出参数 totalNum中。

CALL test.GetAll('p',@totalRecords);
select @totalRecords;

这里使用一个会话变量作为输出参数，存储过程处理后，保持记录速到@totalRecords中。然后我们运行select @totalRecords，输出为2。

5、逻辑判断，条件控制---------------------------------------------------------------------------------------------------------------------

同编写程序类似，存储过程中也有对应的条件判断，功能类似于if、switch。在MySql里面对应的是IF和CASE

1、IF判断

IF判断的格式是这样的：

[sql] view plaincopy

IF expression THEN commands
[ELSEIF expression THEN commands]
[ELSE commands]
END IF;

这里expression是我们的判断表达式；ELSE IF 和ELSE都是可选的；command就是当条件为真(true为1，false为0)时执行的命令。

比如我们设计一个存储过程用于返回商品的价格，这里价格通过传入的参数来判断是要带税收的价格还是没有带税收的价格。先看看表的数据：

create table if not exists products2(
 id int,
 name VARCHAR(50),
 category  VARCHAR(50),
 price FLOAT
);
INSERT into products2 VALUE
(1,'cake','dessert',10.00),
(2,'cheese','dairy',12.50),
(3,'cookie','dessert',50.00),
(4,'carpet','furture',100.00),
(5,'couch','furture',1250.00);

然后下面是我们的存储过程：

delimiter $$
drop procedure if exists test.getPrice $$
CREATE PROCEDURE test.getPrice(in isTaxed boolean, in proname varchar(255))
BEGIN
 DECLARE finalPrice NUMERIC(10,2) DEFAULT 0;
 DECLARE taxRate NUMERIC(10,2) DEFAULT 1.07;
 
 SELECT  price into finalPrice 
 from test.products2 
 WHERE name = proname;
 
 if isTaxed THEN
    set finalPrice = finalPrice * taxRate;
 end IF;
 SELECT finalPrice;
END $$
delimiter ;

存储过程的话有两个输入参数，第一个isTaxed表示是不是要带税价格，第二个是产品的名称；

在存储过程里面定义了两个变量，finalPrice用来保存价格，而taxRate表示税率。

这里代码比较简单，就是判断下如果是要加税，就把原来的价格乘上税率。下面是测试结果：

call getPrice(FALSE,'couch'); 1250

call getPrice(True,'couch'); 1337.5

true的情况表示是带税的价格。

2、CASE的使用

同编程里面的那个switch ....case.....类似，使用CASE同编程一样也是当判断比较多时便于阅读和维护，我们也来看下CASE的语法：

CASE  case_expression
   WHEN when_expression THEN commands
   WHEN when_expression THEN commands
   ...
   ELSE commands
END CASE;

a、这里可以看到CASE好比我们编程里面的那个switch，后面的case_expression就好比switch后面跟着的表达式；

b、然后WHEN则类似编程里面的case，when_expression类似case后面跟着的值，commands则对应于相应的case下执行的命令；

c、最后一个 ELSE 则类似于default，就是如果都没在上面那些WHEN里面的情况时执行的命令。

下面我们假设不同类型的商品对应的税收税率是不一样的情况来看个例子，这里假设甜点的税率是0.05，奶制品的是0.1，家具类的是0.2:，

下面是存储过程：

(3) Mysql

上面增加了一个保存产品类型的一个变量proType，用来保存商品类型。然后使用CASE来进行判断来设置税率，下面是测试的部分：

可以看到沙发的价格是1250*1.2=1500，而蛋糕的价格是10*1.05 = 10.5

(3) Mysql

6、循环---------------------------------------------------------------------------------------------------------------------

在MySql的存储过程中可使用的循环有三种：WHILE、REPEAT、LOOP

1、WHILE

WHILE的格式是这样的：

[sql] view plaincopy

WHILE expression DO
Statements
END WHILE

下面是个例子

[sql] view plaincopy

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`WhileLoopProc` $$
CREATE PROCEDURE `test`.`WhileLoopProc` ()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
WHILE x
SET str = CONCAT(str,x,',');
SET x = x + 1;
END WHILE;
SELECT str;
END $$
DELIMITER ;

上面的例子最后输出“1,2,3,4,5,”这样的字符。

2、REPEAT

REPEAT的格式是这样的：

[sql] view plaincopy

REPEAT
Statements;
UNTIL expression
END REPEAT

REPEAT好比我们编程里面的do...while一样。

再来看一下第二个循环控制指令 REPEAT……END REPEAT。使用REPEAT循环控制语句编写下面这个存储过程：
mysql> create procedure pro11()
-> begin
-> declare i int default 0;
-> repeat
-> insert into t1(filed) values(i);
-> set i=i+1;
-> until i>=5
-> end repeat;
-> end;
Query OK, 0 rows affected (0.00 sec)
这个REPEAT循环的功能和前面WHILE循环不一样，区别在于它的执行后检查是否满足循环条件（until i>=5），而WHILE则是执行前检查（while i
不过要注意until i>=5后面不要加分号，如果加分号，就是提示语法错误。
编写完成后，调用一下这个存储过程，并查看结果：

mysql> call pro11()//
Query OK, 1 row affected (0.00 sec) #虽然在这里显示只有一行数据受到影响，但是下面选择数据的话，还是插入了5行数据。

mysql> select * from t1//
+——-+
| filed |
+——-+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+——-+
5 rows in set (0.00 sec)
一行就是执行结果，实际的作用和使用while编写的存储过程一样，都是插入5行数据。

3、LOOP 及 LEAVE、ITERATE

这里LOOP用来标记循环；而LEAVE表示离开循环，好比编程里面的break一样；ITERATE则继续循环，类型与编程里面的continue。

[sql] view plaincopy

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`LoopProc` $$
CREATE PROCEDURE `test`.`LoopProc` ()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
loop_label: LOOP
IF x > 10 THEN
LEAVE loop_label;
END IF;
SET x = x + 1;
IF (x mod 2) THEN // 0 false 1 true
ITERATE loop_label;
ELSE
SET str = CONCAT(str,x,',');
END IF;
END LOOP;
SELECT str;
END $$
DELIMITER ;

2,4,6,8,10, 上面代码输出10以内的偶数，用逗号分隔。这里注意到x>10的时候就LEAVE掉，如果遇到奇数则ITERATE，奇数对2取模为1，表示true

7、游标(Cursor)---------------------------------------------------------------------------------------------------------------------

1、游标的作用及属性

游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作；游标有下面这些属性：

a、游标是只读的，也就是不能更新它；

b、游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；

c、避免在已经打开游标的表上更新数据。

2、如何使用游标

使用游标需要遵循下面步骤：

a、首先用DECLARE语句声明一个游标

[sql] view plaincopy

DECLARE cursor_name CURSOR FOR SELECT_statement;

上面这条语句就对，我们执行的select语句返回的记录指定了一个游标

b、其次需要使用OPEN语句来打开上面你定义的游标

[sql] view plaincopy

OPEN cursor_name;

c、接下来你可以用FETCH语句来获得下一行数据，并且游标也将移动到对应的记录上(这个就类似java里面的那个iterator)。

[sql] view plaincopy

FETCH cursor_name INTO variable list;

d、然后最后当我们所需要进行的操作都结束后我们要把游标释放掉。

[sql] view plaincopy

CLOSE cursor_name;

在使用游标时需要注意的是，使用定义一个针对NOT FOUND的条件处理函数(condition handler)来避免出现“no data to fetch”这样的错误，条件处理函数就是当某种条件产生时所执行的代码，这里但我们游标指到记录的末尾时，便达到NOT FOUND这样条件，这个时候我们希望继续进行后面的操作，所以我们会在下面的代码中看到一个CONTINUE。先看看我们的表格内容：

(3) Mysql

下面的游标使用演示获取库存量小于100的产品的代码code，这个代码纯粹演示如何使用，在这里没有其他任何意义:)

[sql] view plaincopy

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`CursorProc` $$
CREATE PROCEDURE `test`.`CursorProc` ()
BEGIN
DECLARE no_more_products, quantity_in_stock INT DEFAULT 0;
DECLARE prd_code VARCHAR(255);
DECLARE cur_product CURSOR FOR SELECT code FROM products;
/*First: Delcare a cursor,首先这里对游标进行定义*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products = 1;
/*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/
/* for loggging information 创建个临时表格来保持*/
CREATE TEMPORARY TABLE infologs (
Id int(11) NOT NULL AUTO_INCREMENT,
Msg varchar(255) NOT NULL,
PRIMARY KEY (Id)
);
OPEN cur_product;
/*Second: Open the cursor 接着使用OPEN打开游标*/
FETCH cur_product INTO prd_code;
/*Third:now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了记录的第一行*/
REPEAT
SELECT quantity INTO quantity_in_stock
FROM products
WHERE code = prd_code;
IF quantity_in_stock THEN

相关标签： Mysql SQL 存储过程基础知识

上一篇： ORA-28547 故障处理一例

下一篇：三星发布全球首款27英寸240Hz电竞曲面显示器：3300元

(3) Mysql

MySQL基本概念

7.python3实用编程技巧进阶（二）

Opencv 读取灰度图像会识别为3通道问题

Python3.x学习资料汇总（待续）

mysql-查询性能优化

Mysql--子查询、分页查询、联合查询

Ionic3项目实践记录

实现基于SpringBoot+Maven+Mysql+Redis+RabbitMQ 的高并发秒杀系统(限时秒杀)

[mysql] Can't read from messagefile

mysql 开发进阶篇系列 28 数据库二进制包安装(centos系统准备)