MySQL管理与优化(9):存储过程和函数

Wesley13
• 阅读 789

存储过程和函数

  • 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。

存储过程或函数的相关操作

创建,修改存储过程或函数

  • 相关语法

    CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name type type: Any valid MySQL data type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }

    routine_body: Valid SQL routine statement

  • 范例

    DELIMITER //

    -- 创建存储过程 mysql> CREATE PROCEDURE cityname_by_id(IN cid INT, OUT total INT) -> READS SQL DATA -> BEGIN -> SELECT id, city FROM city WHERE id=cid; -> -> SELECT FOUND_ROWS() INTO total; -> END // Query OK, 0 rows affected (0.06 sec)

    -- 调用存储过程 mysql> CALL cityname_by_id(2, @res); +----+----------+ | id | city | +----+----------+ | 2 | NeiJiang | +----+----------+ 1 row in set (0.00 sec)

    Query OK, 1 row affected (0.01 sec)

    mysql> SELECT @res; +------+ | @res | +------+ | 1 | +------+ 1 row in set (0.00 sec)

删除存储过程或函数

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

查询存储过程或函数

mysql> SHOW PROCEDURE status like 'cityname_by_id'\G
*************************** 1. row ***************************
                  Db: mysqltest
                Name: cityname_by_id
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2014-06-17 15:22:11
             Created: 2014-06-17 15:22:11
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

-- 查看存储过程或函数的定义
mysql> SHOW CREATE PROCEDURE cityname_by_id\G
*************************** 1. row ***************************
Procedure: cityname_by_id
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `cityname_by_id`(IN cid INT, OUT total INT)
    READS SQL DATA
    BEGIN
        SELECT id, city FROM city WHERE id=cid;

        SELECT FOUND_ROWS() INTO total;
    END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

或者通过系统表information_schema.routines来查询:

mysql> SELECT * FROM information_schema.routines WHERE ROUTINE_NAME='cityname_by_id'\G

变量的使用

  • 变量的定义:仅在BEGIN...END块中,语法为:

    DECLARE var_name[,...] type [DEFAULT_VALUE]

    DECLARE last_month_start DATE;

  • 变量的赋值:可以直接赋值或查询赋值

    SET var_name = expr [, var_name = expr] ...

    表达式赋值

    SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)

    SELECT INTO

    SELECT .. FROM .. INTO var_name

  • 定义条件和处理

    -- 条件的定义 DECLARE condition_name CONDITION FOR condition_value

    condition_value: SQLSTATE [VALUE] sqlstate_value| mysql_error_code -- 条件的处理 DECLARE handler_type HANDLER FOR condition_value[, ...] sp_statement

    handler_type: CONTINUE | EXIT | UNDO condition_value: SQLSTATE [VALUE] condition_name| SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

范例:

-- 创建存储过程
mysql> CREATE PROCEDURE city_insert()
    -> BEGIN
    ->  INSERT INTO city VALUES (200, 'Beijing');
    ->  INSERT INTO city VALUES (200, 'Beijing');
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)
-- 调用存储过程,第二句时报错
mysql> CALL city_insert()//
ERROR 1062 (23000): Duplicate entry '200' for key 'PRIMARY'

-- 修改存储过程,支持异常处理
DROP PROCEDURE IF EXISTS city_insert
mysql> CREATE PROCEDURE city_insert()
    -> BEGIN
    ->  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x = 1;
    ->  INSERT INTO city VALUES (300, 'ShangHai');
    ->  INSERT INTO city VALUES (300, 'ShangHai');
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

-- 再次调用,将不会抛出错误
mysql> CALL city_insert()//
Query OK, 0 rows affected, 1 warning (0.09 sec)

光标的使用

  • 在存储过程和函数中可以使用光标对结果集进行循环的处理。

    -- 声明光标 DECLARE cursor_name CURSOR FOR select_statement

    -- OPEN 光标 OPEN cursor_name

    -- FETCH 光标 FETCH cursor_name INTO var_name [, var_name]

    -- CLOSE 光标 CLOSE cursor_name

  • 范例

    -- 定义存储过程 mysql> CREATE PROCEDURE city_stat() -> BEGIN -> DECLARE cid INT; -> DECLARE cname VARCHAR(20); -> DECLARE cur_city CURSOR FOR SELECT * FROM city; -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_city; -> -> SET @x1 = 0; -> SET @x2 = 0; -> -> OPEN cur_city; -> -> REPEAT -> FETCH cur_city INTO cid, cname; -> IF cid <= 4 THEN -> SET @x1 = @x1 + cid; -> ELSE -> SET @x2 = @x2 + cid * 2; -> END IF; -> UNTIL 0 END REPEAT; -> -> CLOSE cur_city; -> -> END; -> // Query OK, 0 rows affected (0.06 sec)

    -- 执行存储过程 mysql> SELECT * FROM city; +-----+----------+ | id | city | +-----+----------+ | 2 | NeiJiang | | 3 | HangZhou | | 10 | ChengDu | | 200 | Beijing | | 300 | ShangHai | +-----+----------+ 5 rows in set (0.00 sec)

    mysql> CALL city_stat(); Query OK, 0 rows affected, 1 warning (0.00 sec)

    mysql> SELECT @x1, @x2; +------+------+ | @x1 | @x2 | +------+------+ | 5 | 1020 | +------+------+ 1 row in set (0.00 sec)

  • 变量,条件,处理程序,光标的声明是有顺序的,变量和条件必须在最前面声明,然后是光标的声明,最后是处理程序的生命。

流程控制

具体相关的细节可参考:

http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

不吝指正。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
3年前
MySQL数据库表设计规范
一、数据库设计1、一般都使用INNODB存储引擎,除非读写比率<1%,才考虑使用MYISAM存储引擎;其他存储引擎请在DBA的建议下使用。2、Storedprocedure(包括存储过程,函数,触发器)对于MYSQL来说还不是很成熟,没有完善的出错记录处理,不建议使用。3、UUID(),USER()这样的
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
11个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这