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

最新推荐文章于 2024-09-02 16:43:54 发布

井言7

最新推荐文章于 2024-09-02 16:43:54 发布

阅读量400

点赞数

文章标签： mysql自定义函数游标

本文链接：https://blog.csdn.net/weixin_29204205/article/details/113281873

版权

本文详细介绍了MySQL的内置函数，包括字符、数值、比较、日期时间、信息和聚合函数。接着讲解了自定义函数的创建、使用方法，强调了函数体的编写规则。还探讨了存储过程，包括带有不同参数类型（如IN, OUT, INOUT）的过程，以及流程控制（如IF, CASE, LOOP, LEAVE, ITERATE, REPEAT）。最后，文章深入讨论了游标的概念、声明、打开、使用和关闭，并展示了游标在处理多条记录时的应用，以及如何处理'no data to fetch'错误的条件处理函数。" 109390716,7852262,PostgreSQL 游标与存储过程实战教程,"['postgresql', '数据库管理', 'SQL', '存储过程']

摘要由CSDN通过智能技术生成

首先整理一下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。先看看我们的表格内容：