4)mysql自定义函数和存储过程

十、自定义函数

函数体

例子：

创建不带参数的自定义函数：
mysql> CREATE FUNCTION f1() RETURNS VARCHAR(30)
    -> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
Query OK, 0 rows affected (0.00 sec)

调用方式
mysql> SELECT f1();
+-------------------------------+
| f1()                          |
+-------------------------------+
| 2016年12月19日 17点:00分:03秒             |
+-------------------------------+
1 row in set (0.04 sec)

mysql>

带参数的函数，一个返回两个的和
create function f2 (num1 smallint unsigned,num2 smallint unsigned)
returns smallint unsigned
return (num1+num2);

调用
select f2(2,4);

删除函数

drop function f2;

创建具有复合结构函数体的自定义函数
修改分隔符：DELEMITER 分隔符
当函数体内需要执行的是多条语句时，要使用BEGIN…END语句
且当编写函数体内容的时候，需要使用 DELIMITER 关键字将分隔符先修改为别的，否则编写语句的时候写到 ‘;’ 的时候会直接执行，导致函数编写失败

创建一个函数，在调用函数的时候，插入一条记录到t_user表
DELIMITER //
CREATE FUNCTION ADD_USER(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
-- 插入数据
INSERT t_user(username) VALUES(username); 
-- 获取插入的数据的id
RETURN LAST_INSERT_ID();
END
//
DELIMITER ;

十一、存储过程

这里只是基本使用，更多关于存储过程，查看：
http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

11.1 mysql解析sql的流程

每次发送一个sql，都需要执行一次这个流程，分析语法的正确性，如果使用存储过程，那么只有在第一次的时候且分析语法正确性，然后放入缓存，从而提高效率

11.2 存储过程的特点

11.3 创建语法

• 存储过程的调用方式：
  1.CALL sp_name([parameter[,…]]) 如果存储过程包含参数，则必须有小括号
  2.CALL sp_name[()] 如果存储过程不包含参数，则小括号可有可无

• 删除存储过程：
  DROP PROCEDURE [IF EXISTS] sp_name

• 修改存储过程
  ALTER PROCEDURE sp_name [characteristic …]
  COMMENT ‘string’
  |{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  只能修改存储过程中的注释、当前内容的类型，并不能修改过程体
  因此，要修改过程体的话，需要先删除存储过程，然后重建

11.4 例子：

定义存储过程：根据id删除用户
mysql> delimiter //
mysql> create procedure removeUserById(in p_id int unsigned)
    -> begin
    -> delete from t_user where id=p_id;
    -> end
    -> //
Query OK, 0 rows affected (0.09 sec)

查看记录
mysql> select * from t_user;
    -> //
+----+----------+------+------+
| id | username | sex  | age  |
+----+----------+------+------+
|  1 | lhp      | 1    |   10 |
|  2 | lhp2     | 3    |   15 |
|  4 | lhp3     | 1    |   10 |
|  5 | lhp4     | 2    |   10 |
|  6 | lhp5     | 3    |   10 |
|  8 | lhp333   | 3    | NULL |
+----+----------+------+------+
6 rows in set (0.07 sec)

调用存储过程
mysql> delimiter ;
mysql> call removeUserById(8);
Query OK, 1 row affected (0.10 sec)

查看记录
mysql> select * from t_user;
+----+----------+------+------+
| id | username | sex  | age  |
+----+----------+------+------+
|  1 | lhp      | 1    |   10 |
|  2 | lhp2     | 3    |   15 |
|  4 | lhp3     | 1    |   10 |
|  5 | lhp4     | 2    |   10 |
|  6 | lhp5     | 3    |   10 |
+----+----------+------+------+
5 rows in set (0.00 sec)

mysql>

创建带有IN和OUT类型的参数：

删除一个用户，且返回删除之后的总数
mysql> delimiter //
mysql> create procedure removeAndReturn (in p_id int unsigned,out userNums int unsigned)
    -> begin
    -> delete from t_user where id = p_id;
    -> select count(id) from t_user into userNums;
    -> end
    -> //
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;
mysql> CALL removeAndReturn(27,@nums);
Query OK, 1 row affected (0.04 sec)

mysql> select @nums;
+-------+
| @nums |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

mysql>

说明：
调用方法：CALL removeAndReturn(27,@nums);—-@nums 传参数
查看返回值： SELECT @nums;

@nums为用户变量
- 1.用户变量：以”@”开始，形式为”@变量名”
用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
- 2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名 或者 set @@global.变量名
对所有客户端生效。只有具有super权限才可以设置全局变量
- 3.会话变量：只对连接的客户端有效。
- 4.局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量
declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量

十二、存储过程与自定义函数的区别

个人学习笔记（慕课网）