MySql 自定义函数,存储过程,游标的使用

最新推荐文章于 2024-06-23 15:15:52 发布

小平果118

最新推荐文章于 2024-06-23 15:15:52 发布

阅读量5.1k

点赞数 1

分类专栏： MYSQL 文章标签： mysql 自定义函数存储过程游标条件控制语句

本文链接：https://blog.csdn.net/i10630226/article/details/52157346

版权

MYSQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

首先整理一下mysql内置的一些函数

MySQL内置函数

一、字符函数

（1）CONCAT()//字符连接
（2）CONCAT_WS()//使用指定的分隔符进行字符连接
（3）FORMAT()//数字格式化
（4）LOWER()//转化小写
（5）UPPER()//转换大写
（6）LEFT()//获取左侧字符
（7）RIGHT()//获取右侧字符
（8）LENGTH()//取得字符串长度
（9）LTRIM(),RTRIM(),TRIM()//删除前导、后续空格或者指定字符
（10）REPLACE()//替换
（11）SUBSTRING()//字符串截取
（12）[NOT] LIKE//模式匹配——百分号%代表任意个字符；下划线_代表任意一个字符

二、数值函数

（1）CEIL()//进一取整
（2）FLOOR()//舍一取整
（3）DIV//整数除法
（4）MOD//取余数，与%一样
（5）POWER()//幂运算
（6）ROUND()//四舍五入
（7）TRUNCATE()//截断

三、比较函数

（1）[NOT] BETWEEN…AND… //[不]范围之内
（2）NOT IN() //[不]在列出值范围内
（3）IS [NOT] NULL //[不]为空

四、日期时间函数

（1）SELECT NOW(); // 当前日期时间
（2）SELECT CURDATE(); // 当前日期
（3）SELECT CURTIME(); // 当前时间
（4）SELECT DATE_ADD();//日期的变化
（5）DATEDIFF(); // 日期的差值
（6）DATE_FORMAT(); // 日期格式化

五、信息函数

（1）SELECT CONNECTION_ID() //连接ID
（2）SELECT DATABASE() //当前数据库
（3）SELECT LAST_INSERT_ID() //最后插入记录的ID
（4）SELECT USER() //当前用户
（5）SELECT VERSION() //版本信息

六、聚合函数

（1）AVG()//平均值
（2）COUNT()//计数
（3）MAX()//最大值
（4）MIN()//最小值
（5）SUM()//求和

七、加密函数

（1）MD5();//信息摘要算法
（2）PASSWORD();//加密算法，主要用途修改当前用户密码

用户自定义函数

简称UDF（user-defined function），其用法与内置函数相同

函数可以返回任意类型的值，也可以接受这些类型的参数，参数不能超过1024个；

创建自定义函数：

CREATE FUNCTION function_name

RETURNS

{STRING/INTEGER/REAL/DECIMAL}

routine_body - 函数体

函数体：

(1)函数体由合法的SQL语法构成;
(2)函数体可以是简单的SELECT或INSERT语句;
(3)函数体如果为复合结构则使用BEGIN…END语句；
(4)复合结构可以包括声明，循环，控制结构。
（5）RETURNS TYPE语句表示函数返回数据的类型；

注意：RETURNS CHAR(50)数据类型的时候，RETURNS 是有S的，而RETURN (SELECT 语句)的时候RETURN是没有S的

如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句中指定类型的值，返回值将被强制转换为恰当的类型。

例如，如果一个函数返回一个SET或ENUM值，但是RETURN语句返回一个整数，对于SET成员集的相应ENUM成员，从函数返回的值是字符串。

（FUNCTION中总是默认是IN参数）RETURNS子句对FUNCTION做指定，对函数而言这是强制的。他用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句

创建不带参数的自定义函数

1. 自定义函数f1

DROP FUNCTION  IF EXISTS f1
CREATE FUNCTION f1()

RETURNS VARCHAR(30)

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

调用函数f1()

SELECT f1();

2.定义带参数的函数

CREATE FUNCTION f2(a SMALLINT UNSIGNED, b SMALLINT UNSIGNED)

RETURNS FLOAT(10,2) UNSIGNED

RETURN (a+b)/3

SELECT f2(10, 15)

定义带有多参数，多条语句的自定义函数

修改分隔符：DELEMITER 分隔符

例：DELIMITER // /* 将分隔符修改为 '//'

当函数体内需要执行的是多条语句时，要使用BEGIN...END语句

且当编写函数体内容的时候，需要使用 DELIMITER 关键字将分隔符先修改为别的，否则编写语句的时候写到 ‘;’ 的时候会直接执行，导致函数编写失败

删除函数：DROP FUNCTION [IF EXISTS] function_name

例：

DELIMITER //

CREATE FUNCTION ADD_USER(p_id SMALLINT,username VARCHAR(20))

RETURNS INT UNSIGNED

BEGIN

INSERT user(p_id,username) VALUES(p_id,username);

RETURN LAST_INSERT_ID();

END

3.带有in的存储过程

//创建带有INT类型参数的存储过程

DESC users;

DELIMITER //

CREATE PROCEDURE removeUserByID(IN id INT UNSIGNED) //id = id将会导致误解

BEGIN

DELETE FROM users WHERE id = id;

END

//

DELIMITER ;

CALL removeUserById(3); //参数名称最好不要和表中的字段相同

SELECT * FROM users; //全删除了

DELIMITER //

CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)

BEGIN DELETE FROM users WHERE id = p_id;

END

//

DELIMITER ;

SELECT * FROM users;

CALL removeUserById(22);

SELECT * FROM users WHERE id = 22;

4.带有in|out 的存储过程

创建带有IN OUT类型参数的存储过程

DELIMITER //

    CREATE PROCEDURE removerUserAndReturnUserName(IN showID INT UNSIGNED,OUT showName INT UNSIGNED)

    BEGIN

    DELETE FROM user WHERE id = showID;

    SELECT count(ID) FROM user INTO showName;

    END

    //

SELECT count(ID) FROM user INTO showName; /* 该语句中的 INTO 含义就是将 SELECT 语句结果的表达式返回到 showName 变量中 /

CALL removerUserAndReturnUserName(10,@nums); /* @nums 所代表的就是用户变量，可用 SELECT @nums 输出 /

用 DECLARE 声明的变量是局部变量，局部变量只能存在于 BEGIN...END 之间，且声明时必须置于 BEGIN...END 的第一行

而通过 SELECT...INTO.../SET @id = 07 这种方法设置的变量我们称之为用户变量，只能存在于当前用户所使用的客户端有效。

CALL rmUserAndRtUserNums(27, @nums);

SELECT @nums; //@nums - 就是用户变量

DECLARE声明的变量都是在BEGIN与END之间，是局部变量

SET @i = 7; //通过@或SET设置的变量称为用户变量

自定义存储过程

语法

CREATE PROCEDURE sp_name ([ proc_parameter ]) [ characteristics..] routine_body

proc_parameter指定存储过程的参数列表，列表形式如下：

[IN|OUT|INOUT] param_name type

其中in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型

1.创建带有多个OUT类型参数的存储过程

先使用一个方法，ROW_COUNT（）

INSERT test(username) VALUES('A'),('B,'),('C');

SELECT ROW_COUNT(); //返回被插入的记录总数

SELECT * FROM test;

UPDATE test SET username = CONTCAT(username, '--immoc') WHERE id <= 2;

SELECT row_COUNT(); //返回更新的记录总数

创建多个输出参数的存储过程

DELIMITER //

CREATE PROCEDURE rmUserByAgeAndRtInfos(IN p_age SMALLINT UNSIGNED, OUT delNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)

BEGIN

DELETE FROM users WHERE age = p_age; //注意变量不同

SELECT ROW_COUNT() INTO delNums; //返回插入、删除和更新的受影响的记录总数

SELECT COUNT(id) FROM users INTO leftNums;

END

//



DELIMITER ;

SELECT * FROM users;

SELECT COUNT(id) FROM users WHERE age = 20;

CALL rmUserByAgeAndRtInfos(20, @a, @b);

SELECT * FROM users;

SELECT COUNT(id) FROM users WHERE age = 23;

CALL rmUserByAgeAndRtInfos(23, @a, @b);

SELECT @a, @b;

注意：“DELIMITER //”语句的作用是将MYSQL的结束符设置为//，因为MYSQL默认的语句结束符为分号;，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，并以“END//”结束存储过程。当使用DELIMITER命令时，应该避免使用反斜杠（\）字符，因为反斜杠是MYSQL的转义字符！！！

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

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

A、存储过程实现的功能相对复杂，函数针对性较强
B、存储过程可以返回多个值，函数只能有一个返回值
C、存储过程一般独立执行，函数可以作为 sql 语句的组成部分来出现

2.修改存储过程：存储过程只能修改简单的特性，并不能修改过程体

alter procedure sp_name [characteristic ...]

comment 'string'

{contains sql no sql reads sql data modifies sql data} sql security {definer invoker}

3.删除存储过程：

DROP PROCEDURE [IF EXISTS] sp_name;

光标

MYSQL里叫光标，SQLSERVER里叫游标，实际上一样的

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。

光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。

1．声明光标

MySQL中使用DECLARE关键字来声明光标。其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement ;

其中，cursor_name参数表示光标的名称；select_statement参数表示SELECT语句的内容，返回一个用于创建光标的结果集

下面声明一个名为cur_employee的光标。代码如下：

DECLARE cur_employee CURSOR FOR SELECT name, age FROM employee ;

上面的示例中，光标的名称为cur_employee；SELECT语句部分是从employee表中查询出name和age字段的值。

2．打开光标

MySQL中使用OPEN关键字来打开光标。其语法的基本形式如下：

OPEN  cursor_name ;

其中，cursor_name参数表示光标的名称。

下面打开一个名为cur_employee的光标，代码如下：

OPEN  cur_employee ;

3．使用光标

MySQL中使用FETCH关键字来使用光标。其语法的基本形式如下：

FETCH cur_employee INTO var_name[,var_name…] ;

其中，cursor_name参数表示光标的名称；var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。

下面使用一个名为cur_employee的光标。将查询出来的数据存入emp_name和emp_age这两个变量中，代码如下：

FETCH  cur_employee INTO emp_name, emp_age ;

上面的示例中，将光标cur_employee中SELECT语句查询出来的信息存入emp_name和emp_age中。emp_name和emp_age必须在前面已经定义。

4．关闭光标

MySQL中使用CLOSE关键字来关闭光标。其语法的基本形式如下：

CLOSE  cursor_name ;

其中，cursor_name参数表示光标的名称。关闭之后就不能使用FETCH来使用光标了。

注意：MYSQL中，光标只能在存储过程和函数中使用！！

5、游标的作用及属性

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

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



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



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

流程控制的使用

存储过程和函数中可以使用流程控制来控制语句的执行。

MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

每个流程中可能包含一个单独语句，或者是使用BEGIN…END构造的复合语句，构造可以被嵌套

1．IF语句

IF语句用来进行条件判断。根据是否满足条件，将执行不同的语句。其语法的基本形式如下：

IF search_condition THEN statement_list 

[ELSEIF search_condition THEN statement_list] ... 

[ELSE statement_list] 

END IF

其中，search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。

注意：MYSQL还有一个IF()函数，他不同于这里描述的IF语句

下面是一个IF语句的示例。代码如下：

IF age>20 THEN SET @count1=@count1+1;  

ELSEIF age=20 THEN SET @count2=@count2+1;  

ELSE SET @count3=@count3+1;  

END IF;

该示例根据age与20的大小关系来执行不同的SET语句。

如果age值大于20，那么将count1的值加1；如果age值等于20，那么将count2的值加1；

其他情况将count3的值加1。IF语句都需要使用END IF来结束。

2．CASE语句

CASE语句也用来进行条件判断，其可以实现比IF语句更复杂的条件判断。CASE语句的基本形式如下：

CASE case_value 

WHEN when_value THEN statement_list 

[WHEN when_value THEN statement_list] ... 

[ELSE statement_list] 

END CASE

其中，case_value参数表示条件判断的变量；

when_value参数表示变量的取值；

statement_list参数表示不同when_value值的执行语句。

CASE语句还有另一种形式。该形式的语法如下：

CASE 

WHEN search_condition THEN statement_list 

[WHEN search_condition THEN statement_list] ... 

[ELSE statement_list] 

END CASE

其中，search_condition参数表示条件判断语句；

statement_list参数表示不同条件的执行语句。

下面是一个CASE语句的示例。代码如下：

CASE age 

WHEN 20 THEN SET @count1=@count1+1; 

ELSE SET @count2=@count2+1; 

END CASE ;

代码也可以是下面的形式：

CASE 

WHEN age=20 THEN SET @count1=@count1+1; 

ELSE SET @count2=@count2+1; 

END CASE ;

本示例中，如果age值为20，count1的值加1；否则count2的值加1。CASE语句都要使用END CASE结束。

注意：这里的CASE语句和“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句

并且用END CASE替代END来终止！！

3．LOOP语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。

但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。

LOOP语句的语法的基本形式如下：

[begin_label:] LOOP 

statement_list 

END LOOP [end_label]

其中，begin_label参数和end_label参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；

statement_list参数表示需要循环执行的语句。

下面是一个LOOP语句的示例。代码如下：

add_num: LOOP  

SET @count=@count+1;  

END LOOP add_num ;

该示例循环执行count加1的操作。因为没有跳出循环的语句，这个循环成了一个死循环。

LOOP循环都以END LOOP结束。

4．LEAVE语句

LEAVE语句主要用于跳出循环控制。其语法形式如下：

LEAVE label

其中，label参数表示循环的标志。

下面是一个LEAVE语句的示例。代码如下：

add_num: LOOP 

SET @count=@count+1; 

IF @count=100 THEN 

LEAVE add_num ; 

END LOOP add_num ;

该示例循环执行count加1的操作。当count的值等于100时，则LEAVE语句跳出循环。

5．ITERATE语句

ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。

ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。

ITERATE语句的基本语法形式如下：

ITERATE label

其中，label参数表示循环的标志。

下面是一个ITERATE语句的示例。代码如下：

add_num: LOOP 

SET @count=@count+1; 

IF @count=100 THEN 

LEAVE add_num ; 

ELSE IF MOD(@count,3)=0 THEN 

ITERATE add_num; 

SELECT * FROM employee ; 

END LOOP add_num ;

该示例循环执行count加1的操作，count值为100时结束循环。如果count的值能够整除3，则跳出本次循环，不再执行下面的SELECT语句。

说明：LEAVE语句和ITERATE语句都用来跳出循环语句，但两者的功能是不一样的。

LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环，然后进入下一次循环。

使用这两个语句时一定要区分清楚。

6．REPEAT语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：

[begin_label:] REPEAT 

statement_list 

UNTIL search_condition 

END REPEAT [end_label]

其中，statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，满足该条件时循环结束。

下面是一个REPEAT语句的示例。代码如下：

REPEAT 

SET @count=@count+1; 

UNTIL @count=100 

END REPEAT ;

该示例循环执行count加1的操作，count值为100时结束循环。

REPEAT循环都用END REPEAT结束。

综合实例

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

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

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 < 100 THEN  

 INSERT  INTO infologs(msg)  

 VALUES  (prd_code);  

 END  IF;  

 FETCH  cur_product INTO prd_code;  



 UNTIL  no_more_products = 1  

 END REPEAT;  

 CLOSE  cur_product;  /*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/  

 SELECT *  FROM infologs;  

 DROP TABLE  infologs;  

END // 



DELIMITER ;