过程化sql学习之存储过程和函数（阶段2）

frist word

已于 2022-03-01 17:34:07 修改

阅读量993

点赞数

分类专栏：数据库文章标签：数据库 sql

于 2021-12-06 15:55:48 首次发布

本文链接：https://blog.csdn.net/qq_51315420/article/details/121735622

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

前面介绍了PL/SQL的概念，我们对过程化sql有了大致的了解，同时介绍了过程化sql中的变量和流程控制语句。这有助于下面的学习。

首先说明一点存储过程和函数有些相似，但又有不同之处。可以对比记忆学习，这样可以深入理解。

1、存储过程

1.1、介绍存储过程

过程化sql程序的基本结构是块。所有的过程化sql程序都是由块组成的，这些块之间可以互相嵌套，每个块完成一个逻辑操作。

过程化sql块主要有两种类型：命名块和匿名块。匿名块是就是普通的sql语句块，每次执行时都要进行编译，它不能被存储到数据库中，也不能在其他过程化sql中调用。存储过程和函数是命名块，它们被编译后保存在数据库中（和数据库相关连），称为持久性存储块，可以被反复调用，运行速度快。

存储过程（Stored Procedure ）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。存储过程是经过语法检查和编译的SQL语句（一次编译后永久有效），所以执行速度比普通的SQL语句的执行速度快。

估计你还是没有明白什么是存储过程，下面我举个例子。

假如你每天要开车完成一些列重复的操作：车钥匙启动车，倒车。现在出现了一款新车，可以自动的完成这些重复的工作。每次你上车以后，车就自动帮助你倒出来了。同样的，在工作里也会经常遇到重复性的工作，这时候就可以把常用的QL写好存储起来，这就是存储过程。这样下次遇到同样的问题，直接使用存储过程就可以了，就不需要再重新写一遍QL了，这就极大的提高了工作效率。

看了这个例子你是否对存储过程有了一点理解？不理解也没有关系，我们接下来深入的学习存储过程。

2、创建存储过程

2.1、创建存储过程

创建存储过程使用create procedure语句，语法如下：

delimiter $$    #修改语句结束符

create procedure [数据库名.]过程名([参数列表]) [特性]
begin
    代码内容;
end $$    #对应于上面的语句结束符

delimiter ;

create procedure为创建存储过程的关键字。

[数据库名.]这一部分指明在那个数据库上创建存储过程。如果当前所在数据库就是要准备建立存储过程的数据库可以不写，但是我建议还是写上为好。

参数列表的语法形式：[in | out | inout] 参数名称数据类型 存储过程也可以不使用任何参数

其中，in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；
注意存储过程没有返回值，但是我们可以通过out型参数来向外部传递数据。

begin...end用于表示代码语句的开始和结束。

特性一般不写，但某些情况下可以使用，这一点先了解以下，之后可以深入学习并使用，主要有以下取值：

COMMENT 'string':注释信息，可以用来描述存储过程或函数。
LANGUAGE SQL:说明存储过程代码内容部分是由SQL语句组成的，当前系统支持的语言为SOL,SOL是LANGUAGE特性的唯一值。
[NOT] DETERMINISTIC:指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SOL表明子程序不包含SOL语句；READS SOL DATA说明子程序包含读数据的语句;MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。

这些只做了解，现阶段不要求使用，之后有用到的地方可以查看本文章学习使用，收藏好本文章。

示例，在银行转账中，我们可创建一个存储过程来提高效率：

DELIMITER $$
CREATE PROCEDURE banks.transfer(IN outCustomerId INT,IN inCustomerId INT,IN mount DECIMAL,OUT message VARCHAR(20))
#outCustomerId为转出账户
#inCustomerId为转入账户
#message为结果信息
BEGIN
	DECLARE balance DECIMAL DEFAULT 0;
	SELECT money INTO balance FROM account WHERE id=outCustomerId;
	IF mount<0 THEN 
		SET message="转账钱数不能为负数";
	ELSEIF mount>balance THEN
		SET message="账户余额不足";
	ELSE 
		UPDATE account SET money=money-mount WHERE id=outCustomerId;
		UPDATE account SET money=money+mount WHERE id=inCustomerId;
		SET message="转账成功";
	END IF;
END$$
DELIMITER ;

2.2、调用存储过程

存储过程已经创建好了，接下来就是调用存储过程了。语法格式：

call [数据库名.]存储过程名(参数1,参数2,...);

[数据库名.]这一部分可以不写，和刚才创建存储过程是一个道理，如果要调用当前所在数据库中的存储过程可以不写，但是要调用其他数据库中的存储过程必须要写（因为存储过程和数据库相关连）。

例如调用刚才创建的transfer银行转账存储过程：

mysql> SET @message=""; #创建一个会话变量来存储结果
Query OK, 0 rows affected (0.00 sec)

mysql> call banks.transfer(1,2,500,@message);  #调用存储过程
Query OK, 1 row affected (0.03 sec)

mysql> select @message;
+--------------+
| @message     |
+--------------+
| 转账成功     |
+--------------+
1 row in set (0.00 sec)

注意这个存储过程在正常情况下可以正常使用，但是在一些极端情况下就不适合了，比如出现系统故障导致转出账户转出了钱，但是转入账户去没有收到钱。这个问题会涉及到数据库恢复技术。接下来的文章中我会讲解。

3、函数

3.1、介绍函数

数据库中的函数包括内置函数和自定义函数，内置函数是诸如SUM()，COUNT()，AVG()等数据库内置的函数。一般我们编写的函数都属于自定义函数。

3.2、常用内置函数：

有关数字的

select rand(); -- 生成随机数
-- 数据库实现抽奖！
/*从student表中随机抽取三个人排序*/
select * from student order by rand() limit 3;

select abs(-2); -- 取绝对值。2

select ceil(3.1);-- 向上取整。4

select floor(3.1);-- 向下取整。3

select round(3.1);-- 四舍五入。3

select truncate(3.1415926,2);-- 截取小数。3.14

有关字符串的

select ucase('hello');-- 小写变成大写。HELLO

select lcase('HELLO');-- 大写变成小写。hello

select left('hello',2);-- 从左向右截取字符串，第2个参数为截取的个数。he

select right('hello',2);-- 从右向左截取字符串，第2个参数为截取的个数。lo

select substring('hello',2,2);-- 从指定位置（第2参数）截取指定长度（第3参数）字符串。el

select concat('hello',' world');-- 拼接字符串。hello world

/*会把student表中的name和age两列一起输出*/
select concat(name,'|',age) from student;

CHAR_LENGTH（） -- 统计字符串中的字符个数
LENGTH（）LENGTH()  -- 统计存储指定字符串所需的字节数(y。注意和上面的区别

mysql> select char_length("中国"),length("中国");
+-----------------------+------------------+
| char_length("中国")   | length("中国")   |
+-----------------------+------------------+
|                     2 |                6 |  
+-----------------------+------------------+
1 row in set (0.00 sec)  #因为utf8存储一个汉字为3个字节，所以length(“中国”)结果为6.

instr(源字符串,目标字符串)#返回目标字符串在源字符串中的位置。mysql中，字符串下标从1开始。
mysql> select instr("你好，中国",'中国'),instr("你好，中国",'A');
+--------------------------------+------------------------------+
| instr("你好，中国",'中国')       | instr("你好，中国",'A')      |
+--------------------------------+------------------------------+
|                              4 |                            0 |
+--------------------------------+------------------------------+
1 row in set (0.00 sec)

有关时间的

unix_timestamp() --时间戳

from_unixtime(数值) -- 将时间戳转换成时间日期

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1638715426 |
+------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1638715426);
+---------------------------+
| from_unixtime(1638715426) |
+---------------------------+
| 2021-12-05 22:43:46       |
+---------------------------+
1 row in set (0.00 sec)

now() -- 返回当前日期和时间

curdate() -- 返回当前日期

curtime() -- 返回当前时间

year() --取年份

month() --取月份

day() --取天数

mysql> select year(now()) year,month(now()) month,day(now()) day;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2021 |    12 |    5 |
+------+-------+------+
1 row in set (0.00 sec)

date_add(日期时间字符串,interval 数值 type) 
-- 在日期时间字符串的基础上，增加指定type的数值
-- type有：year、month、...、second

mysql> select date_add('2021-11-01',interval 10 day);
+----------------------------------------+
| date_add('2021-11-01',interval 10 day) |
+----------------------------------------+
| 2021-11-11                             |
+----------------------------------------+
1 row in set (0.00 sec)

datediff(日期字符串1,日期字符串2); --判断两个日期之间的天数差距。

mysql> select datediff('2021-07-01','1921-07-01') as '建党百年';
+--------------+
| 建党百年     |
+--------------+
|        36525 |
+--------------+
1 row in set (0.00 sec)

mysql> select datediff(curdate(),'1921-07-01') as '建党百年';
+--------------+
| 建党百年     |
+--------------+
|        36682 |
+--------------+
1 row in set (0.00 sec)

其他

select sha('123456');  -- 对数据加密。

mysql> select sha('123456');
+------------------------------------------+
| sha('123456')                            |
+------------------------------------------+
| 7c4a8d09ca3762af61e59520943dc26494f8941b |
+------------------------------------------+
1 row in set (0.00 sec)

4、创建函数

4.1、创建函数

在创建函数之前最好使用 show VARIABLES like '%fun%'; 语句来查看当前数据库是否支持函数功能，如果为OFF，则需要执行 set global log_bin_trust_function_creators=1; 开启才能使用函数。

这里创建的是自定义函数。语法格式如下：

delimiter $$    #修改语句结束符

create function [数据库名.]函数名([参数列表]) returns 数据类型 [特性]  #记住是returns不是return
begin
    代码部分;
end$$

delimiter ;

[数据库名.]这一部分和存储过程中的一样如果当前所在数据库即为要创建函数的数据库可以不写。

参数列表中的参数命名语法：

参数1 数据类型1,参数2 数据类型2, ...

特性，函数的特性取值与存储过程的特性取值一样。可以查看上面存储过程那一节中的特性介绍。

注意：函数一定要有返回值。

示例，创建一个函数，函数的功能是返回不同性别的学生数。

DELIMITER $$

CREATE FUNCTION countBySex(sex CHAR(2))RETURNS INT
BEGIN
    DECLARE n INT DEFAULT 0;
    SELECT COUNT(*) INTO n FROM student WHERE Ssex=sex;
    RETURN n;
END$$

DELIMITER ;

4.2、调用函数

调用函数使用的是select语句，这和调用存储过程的call语句不同。语法格式如下：

select [数据库名.]函数名(参数1,参数2,...);

[数据库名.]和之前类似，不再赘述。

例如调用刚才创建的countBySex函数：

mysql> select countBySex("男");
+-------------------+
| countBySex("男")  |
+-------------------+
|                 2 |
+-------------------+
1 row in set (0.02 sec)

5、查看、修改、删除存储过程和函数

5.1、查看存储过程和函数

5.1.1、使用show procedure|function status查看存储过程和函数

show {procedure | function} status [like 'pattern']\G

提示：{}中的内容为必填项，[]中的内容可写可不写。

示例使用show procedure status [like 'pattern']\G;查看刚才创建的transfer存储过程：

mysql> show procedure status like "transfer"\G
*************************** 1. row ***************************
                  Db: banks
                Name: transfer
                Type: PROCEDURE
             Definer: skip-grants user@skip-grants host
            Modified: 2021-12-05 20:02:34
             Created: 2021-12-05 20:02:34
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

5.1.2、使用show create 查看存储过程和函数

show create procedure 存储过程名;
#补充一点这个语句还可以用来查看函数，只需要把procedure换成function
#将存储过程名换成函数名即可

示例使用show create procedure 存储过程名;查看刚才创建的transfer存储过程：

可以看到许多的信息。

5.1.3、从information schema.Routines表中查看存储过程的信息

information schema.Routines表中不仅可以查看存储过程还能查看函数的信息。该表存储所有存储过程和函数的定义。

语法如下：

SELECT * FROM information_schema.Routines 
WHERE ROUTINE_NAME='name' [and ROUTINE_TYPE='function' | 'procedure']\G

其中，ROUTINE_NAME字段中存储的是存储过程和函数的名称；name参数表示存储过程或函数的名称。

如果有存储过程和函数名称相同的情况，则需要同时指定ROUTINE_TYPE字段表名查询的是存储过程还是函数。存储过程ROUTINE_TYPE字段的值是procedure,函数ROUTINE_TYPE字段的值是function。

5.2、修改存储过程和函数

使用alter语句可以修改存储过程或函数的特性，语法如下：

alter [procedure | function] name [特性]

特性有：

COMMENT 'string'表示注释信息。
CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句。
NO SOL表示子程序中不包含SQL语句。
READS SQL DATA表示子程序中包含读数据的语句。
MODIFIES SQL DATA表示子程序中包含写数据的语句。
SOL SECURITY{DEFINER INVOKER}指明谁有权限来执行。
DEFINER表示只有定义者自己才能够执行。
INVOKER表示调用者可以执行。

例如为刚才的transfer添加注释信息：

mysql> alter procedure banks.transfer comment '转账';
Query OK, 0 rows affected (0.00 sec)

mysql> show procedure status like 'transfer'\G
*************************** 1. row ***************************
                  Db: banks
                Name: transfer
                Type: PROCEDURE
             Definer: skip-grants user@skip-grants host
            Modified: 2021-12-05 21:09:33
             Created: 2021-12-05 20:02:34
       Security_type: DEFINER
             Comment: 转账    #已经被修改
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

5.3、删除存储过程

其实删除存储过程和函数的语法类似，都是使用drop语句，其语法格式如下：

drop [procedure | function] name;

name参数表示存储过程或函数的名称。

8、存储过程和函数的优缺点

8.1、存储过程的优缺点

8.1.1、存储过程的优点

运行效率高。存储过程只在创建时进行编译，以后执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
封装操作。当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来。
提高效率。如果重复性工作比较多，存储过程会比较实用。
简化sql语句。可以在过程中调用另一个存储过程。可以在存储过程中调用函数。这可以简化一系列复杂语句。
降低了客户机和服务器之间的通信量。客户机上的应用程序只要通过网络向服务器发出调用存储过程的名称和参数，就可以让数据库管理系统执行其中的多条sql语句并进行数据处理。只有最终的处理结果才返回客户端。
安全性高,可设定只有某用户才具有对指定存储过程的使用权。
方便实施企业规则。可以把企业规则的运算程序写成存储过程放入数据库服务器中，由数据库管理系统管理，即有利于集中控制，又能够方便地进行维护。当企业规则发生变化时只用修改存储过程即可，无须修改其他应用程序。

8.1.2、存储过程的缺点

不可移植性，每种数据库的内部编程语法都不太相同，当需要兼容多种数据库时，最好不要用存储过程。
业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试成本。

最后补充一点：存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

8.2、函数的优缺点

8.2.1、函数的优点

可在SQL语句（DML或SELECT）中调用函数。由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面，而存储过程不行。
一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

8.2.2、函数的缺点

函数只能返回一个变量，而存储过程可以返回多个。

有关存储过程和函数的知识可能有些不恰当的地方，希望读者能理解。还要告诉读者一点，学习知识不要“尽信书”，一定要多看多实践，这样才能适应未来行业的变化。

frist word

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
过程化sql学习之存储过程和函数（阶段2）

前面介绍了PL/SQL的概念，我们对过程化sql有了大致的了解，同时介绍了过程化sql中的变量和流程控制语句。这有助于下面的学习。1、存储过程1.1、介绍存储过程过程化sql程序的基本结构是块。所有的过程化sql程序都是由块组成的，这些块之间可以互相嵌套，每个块完成一个逻辑操作。过程化sql块主要有两种类型：命名块和匿名块。匿名块是就是普通的sql语句块，每次执行时都要进行编译，它不能被存储到数据库中，也不能在其他过程化sql中调用。存储过程和函数是命名块，它们被编译后保存在数据库中（和数据
复制链接

扫一扫