首页 > 数据库 > MySQL >

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

2016-09-15

创建具有复合结构函数体的自定义函数(向数据表插入一条数据)复合结构的函数体要包含在BEGIN END 中间。存储过程是SQL语句与控制语句的预编译集合(只在第一次编译),以一个名称存储并作为一个单元处理。

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语句的组成部分来出现。

相关文章
最新文章
热点推荐