【MySQL】MySQL函数、自定义函数、存储过程

Mysql运算符和函数

字符函数

删除指定字符串

删除前指定字符串

删除后续的

删除前后的

字符串替换

截取字符串，mysql中字符从1开始

查询包含%的字串

_下划线代表任意一个字符

数值运算符和函数

日期时间函数

日期格式化

SELECT DATE_FORMAT('2014-3-2','%m/%d/%Y');

信息函数

聚合函数

加密函数

自定义函数（UDF，user-defined function）

CREATE FUNCTION function_name

RETURNS

{STRING | INTEGER | REAL | DECIMAL}

Routine_body

 

关于函数体

1.      函数体由合法的SQL语句组成；

2.      函数体可以是简单的SELECT或INSERT语句；

3.      函数体如果为复合结构则使用BEGIN … END 语句；

4.      复合结构可以包含声明，循环，控制结构；

 

例1：创建不带参数的自定义函数(格式化当前日期)

CREATE FUNCTION f1()

RETURNS VARCHAR(30)

RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');

例2: 创建带有参数的函数（求两个数的平均值）

CREATE FUNCTION f2(num1 SMALLINTUNSIGNED,num2 SMALLINT UNSIGNED)

RETURNS FLOAT(10,2) UNSIGNED

RETURN (num1+num2)/2;

例3：创建具有复合结构函数体的自定义函数(向数据表插入一条数据)复合结构的函数体要包含在BEGIN END 中间

Tip : DELIMITER // 设置mysql的结束符，这里设置为两条斜线结束。

 

CREATE FUNCTION addpname(pname VARCHAR(20))

RETURNS INT UNSIGNED

BEGIN

INSERT province(pname) VALUES(pname);

RETURN LAST_INSERT_ID();

END

//

删除函数

DROP FUNCTION [IFEXISTS] function_name

 

存储过程

 

SQL命令 –》MySQL引擎分析-》语法正确-》执行可识别命令-》执行结果-》客户端

 

存储过程是SQL语句与控制语句的预编译集合（只在第一次编译），以一个名称存储并作为一个单元处理。

1.      增强了SQL语句的功能和灵活性

2.      实现了较快的速度。

3.      减少了网络流量。

 

创建存储过程

CREATE

[DEFINED = {user| CURRENT_USER}]

PROCEDUREsp_name([proc_parameter[,…]])

[characteristic …]routine_body

Proc_parameter:

[IN | OUT |INOUT] param_name type

 

参数

IN,表示该参数的值必须在调用存储过程时指定。

OUT，表示该参数的值可以被存储过程改变，并且可以返回。

INOUT，表示该参数调用时指定，并且可以被改变和返回。

过程体

         过程体由合法的SQL语句构成。

         过程体可以是任意SQL语句。

         过程体如果为复合结构则使用BEGIN…END 语句。

         复合结构可以包含声明，循环，控制结构。

 

例1：创建不带参数的存储过程

CREATE PROCEDURE sql()

SELECT VERSION();

例2：带有IN 类型参数的存储过程

CREATE PROCEDUREremoveById(IN p_id INT UNSIGNED)

BEGIN

DELETE FROMprovince WHERE id = p_id;

END

//

删除存储过程

DROP PROCEDUREprocedure_name

 

例3：创建带有IN 和OUT 类型的参数的存储过程

这个存储过程可以删除指定id的数据并且返回剩下的总数

CREATE PROCEDUREremoveUserAndReturnUserNums(

IN p_id INTUNSIGNED ,OUT userNums INT UNSIGNED)

BEGIN

DELETE FROMcounty WHERE id=p_id;

SELECT count(id)FROM county INTO userNums;

END

//

1.    用户变量：以"@"开始，形式为"@变量名"
用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生

2.    全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名  或者  set @@global.变量名
对所有客户端生效。只有具有super权限才可以设置全局变量

3.    会话变量：只对连接的客户端有效。

4.    局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量
declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量

例4：创建带有多个OUT类型参数的存储过程

 

ROW_COUNT(); 增加或删除的数量表示最后一次操作的数量

 

删除一个表的数据，并返回删除了几条，还剩下几条数据。

CREATE PROCEDURE removeUserByAgeAndReturnInfos(

IN p_age SMALLINT,

OUT deleteUsers SMALLINT UNSIGNED,

OUT userCounts SMALLINT UNSIGNED)

BEGIN

DELETE FROM  user WHEREage=p_age;

SELECT ROW_COUNT() INTO deleteUsers;

SELECT COUNT(id) FROM user INTO userCounts;

END

//

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

1.      存储过程实现的功能要复杂一些；而函数的针对性更强。

2.      存储过程可以返回多个值；函数只能有一个返回值。

3.      存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现。