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

女爷i 2022-07-19 02:17 275阅读 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_nam`e和`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。先看看我们的表格内容：

![6sK7Kby.png][]

下面的游标使用演示获取库存量小于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 ;

[6sK7Kby.png]: http://i.imgur.com/6sK7Kby.png