MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

1 MySQL存储过程和函数

过程和函数,它们被编译后保存在数据库中,称为持久性存储模块(Persistent Stored Module,PSM),可以反复调用,运行速度快。

1.1 存储过程

存储过程是由过程化 SQL 语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,因此称它为存储过程,使用时只要调用即可。

1.2 函数

这里指自定义函数,因为是用户自己使用过程化 SQL 设计定义的。函数和存储过程类似,都是持久性存储模块。函数的定义和存储过程也类似,不同之处是函数必须指定返回类型。

MySQL 命令的执行过程: 
MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

存储过程和函数可以简化语法分析和编译的过程,提高运行速度。

2 我的 MySQL 数据表

数据库名:peng 
表名: imooc_goddess

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

我的创建数据库、建表和插入数据 SQL 语句:

  1.  
    /*
  2.  
    Navicat MySQL Data Transfer
  3.  
     
  4.  
    Source Server : localhost_3306
  5.  
    Source Server Version : 50712
  6.  
    Source Host : localhost:3306
  7.  
    Source Database : peng
  8.  
     
  9.  
    Target Server Type : MYSQL
  10.  
    Target Server Version : 50712
  11.  
    File Encoding : 65001
  12.  
     
  13.  
    Date: 2016-10-04 20:53:44
  14.  
    */
  15.  
     
  16.  
    SET FOREIGN_KEY_CHECKS=0;
  17.  
     
  18.  
    -- ----------------------------
  19.  
    -- Table structure for imooc_goddess
  20.  
    -- ----------------------------
  21.  
    DROP TABLE IF EXISTS `imooc_goddess`;
  22.  
    CREATE TABLE `imooc_goddess` (
  23.  
    `id` int(11) NOT NULL AUTO_INCREMENT,
  24.  
    `user_name` varchar(30) NOT NULL,
  25.  
    `sex` int(11) DEFAULT NULL,
  26.  
    `age` int(11) DEFAULT NULL,
  27.  
    `birthday` date DEFAULT NULL,
  28.  
    `email` varchar(30) DEFAULT NULL,
  29.  
    `mobile` varchar(11) DEFAULT NULL,
  30.  
    `create_user` varchar(30) DEFAULT NULL,
  31.  
    `create_date` date DEFAULT NULL,
  32.  
    `update_user` varchar(30) DEFAULT NULL,
  33.  
    `update_date` date DEFAULT NULL,
  34.  
    `isdel` int(11) DEFAULT NULL,
  35.  
    PRIMARY KEY (`id`)
  36.  
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
  37.  
     
  38.  
    -- ----------------------------
  39.  
    -- Records of imooc_goddess
  40.  
    -- ----------------------------
  41.  
    INSERT INTO `imooc_goddess` VALUES ('2', '小彭', null, '23', null, null, null, null, null, null, null, null);
  42.  
    INSERT INTO `imooc_goddess` VALUES ('6', '胖子', '0', '78', '2016-10-03', 'hongming@qq.com', '1211555599', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
  43.  
    INSERT INTO `imooc_goddess` VALUES ('7', '小溪', '0', '34', '2016-10-03', 'hongming@qq.com', '1211555599', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
  44.  
    INSERT INTO `imooc_goddess` VALUES ('9', '小霞', null, '23', '1990-09-09', 'xiaoxia@qq.com', '232445455', null, '2016-10-03', null, '2016-10-03', null);
  45.  
    INSERT INTO `imooc_goddess` VALUES ('10', 'hh', '1', '23', '1990-09-09', 'jkjfskf', '12323', 'Admin', '2016-10-03', 'admin', '2016-10-03', '1');
  46.  
    INSERT INTO `imooc_goddess` VALUES ('11', '平', '1', '23', '2998-04-09', 'jjjj@ww.com', '1323', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
  47.  
    INSERT INTO `imooc_goddess` VALUES ('12', '航母', '1', '23', '2333-09-09', 'jkksjkjf', '1232', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
  48.  
    INSERT INTO `imooc_goddess` VALUES ('13', '胖纸', '1', '23', '1991-09-09', 'jjijijij', '1323244', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
  49.  
    INSERT INTO `imooc_goddess` VALUES ('14', '校长', '1', '18', '1998-09-09', 'jkjijij@qq.com', '112323424', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');

3 存储过程的创建模板和调用模板

3.1 创建存储过程模板

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

[ ] 表示可以省略 
DEFINER 创建者,省略为默认用户 
sp_name 为过程名 
sp_name 的参数(proc_paramenter) 可以无或多个 
routine_body 过程体 
proc_paramenter 类型 :

IN 必须在调用存储过程时指定 
OUT 可以被存储过程改变,并且可以返回 
INOUT 调用时指定,并且可以被改变和返回

过程体如果是复合结构,则使用 BEGIN …END 语句。

  1.  
    CREATE PROCEDURE sp_name(proc_paramenter)
  2.  
    BEGIN
  3.  
    routine_body
  4.  
    END

3.2 调用存储过程

  1.  
    1. CAll sp_name([parameter[,.....]])
  2.  
     
  3.  
    2. CALL sp_name[()]
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

4 创建的存储过程

存储过程的创建的方式有很多,但是存储过程的创建所需的代码都大同小异。

  • 在 电脑的 CMD 命令行创建
  • 在 Navicat 表中函数模块新建函数
  • 在 Navicat 新建查询(类似命令行方式)

其实 Navicat for mysql 只是一个图形化界面而已,其中的原理都是一样的,CMD 方式还是最经典的,最基础的。

示例

当我们登录到 MySQL 数据库中时,可以通过SELECT VERSION();,查询到当前 MySQL 版本信息,如下

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

下面是把 SELECT VERSION(); 作为一个过程体,写入到新建的一个存储过程中的三种方式。

方式一 CMD命令行方式

因为数据库本来就有自己的函数,创建存储过程名,尽量避免使用数据库相关的名词,可以使用比较独特的名词。

创建名为 v1 的存储过程,存储在数据库中,创建其他存储过程时,就不能使用 v1这个名字创建了,否则会发生重名错误。

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

方式二 Navicat 新建查询

创建名为 v3 的存储过程

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

方式三 Navicat 新建函数

创建名为 v2 的存储过程

1.在函数上右击新建函数。

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

2.选择过程。 
MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

3.这个存储过程没有参数,不填写,点完成。 
MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

4.在过程体中输入SELECT VERSION();,点击保存,输入过程名 v2 ,确认。 
MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

还是GIF图来的快 
MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

当然,调用这 3 种存储过程,都可以在 Navicat 新建查询或 CMD 中通过

  1.  
    CALL v1();
  2.  
    CALL v2();
  3.  
    CALL v3();
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

来调用

4.1 创建不带参数的存储过程

在 CMD 中执行下面的语句。 
MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

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

DELIMITER 是MySQL的一个定界符,是告诉mysql解释器,该段命令是否已经结束了,mysql可以执行了 
但是,在复合结构中,sql 语句用“;”结尾,但是我们并没有结束输入命令,所以通过 
DELIMITER // 
修改 默认为 “;”的定界符为”//”, 以后输入 “//”代表命令输入结束,mysql 可以执行了。 
DELIMITER ; 改回默认的定界符。

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

参数名不能和数据表名相同,上面例子的 p_id不能写成 id。 
不能更改已经保存的存储过程的函数体,必须先删除该存储过程,再创建新的存储过程。

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

这是一个有输入值和返回值的存储过程。输入要删除的行号 p_id ,之后数据库计算剩余行数 返回到 userNums 中。

1.要输入的值为 p_id,返回的值为 userNums。 
MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

2.在过程体中输入

  1.  
    DELETE FROM imooc_goddess WHERE id = p_id;
  2.  
    SELECT COUNT(id) FROM imooc_goddess INTO userNums;
  • 1
  • 2
  • 1
  • 2
  • 1
  • 2

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

3.点击进入函数,点击运行 
MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

结果:

  1.  
    时间: 00:00.08
  2.  
     
  3.  
    Procedure executed successfully
  4.  
    受影响的行: 1
  5.  
     
  6.  
    Parameters: IN `p_id` int,OUT `userNums` int
  7.  
    14,@nums
  8.  
    Return values: 14, 4
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

1.在 CMD 命令行的写法

  1.  
    DELIMITER //
  2.  
    CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
  3.  
    BEGIN
  4.  
    DELETE FROM imooc_goddess WHERE id = p_id;
  5.  
    SELECT COUNT(id) FROM imooc_goddess INTO userNums;
  6.  
    END
  7.  
    DELIMITER ;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

注意如果数据中含有 removeUserAndReturnUserNums 存储过程,更改名称,再创建新的存储过程。

  1. 在 CMD 命令行调用

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

4.4 创建带有多个OUT类型的存储过程

输入年龄,返回删除的行数,和剩余行数。

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

SQL 语句

  1.  
    CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT
  2.  
    deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)
  3.  
    BEGIN
  4.  
    DELETE FROM imooc_goddess WHERE age = p_age;
  5.  
    SELECT ROW_COUNT() INTO deleteUsers;
  6.  
    SELECT COUNT(id) FROM imooc_goddess INTO userCounts;
  7.  
    END
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

5 自定义函数

用户自定义函数(user-defined function,UDP)是一种对MySQL 扩展的途径,其用法与内置函数相同。

5.1 创建自定义函数模板

  1.  
    CREATE FUNCTION function_name
  2.  
    RETURNS
  3.  
    {STRING|INTEGER|REAL|DECIMAL}
  4.  
    routine_body
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4

关于函数体

函数体由合法的SQL语句构成 
函数体可以是简单的SELECT或INSERT语句 
函数体如果为复合结构则使用 BEGIN….END 语句 
复合结构可以包含声明,循环,控制结构

5.2 自定义函数的创建和调用

1 自定义不带参数的函数

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

  1.  
    CREATE FUNCTION f1() RETURNS VARCHAR(30)
  2.  
    RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日:%H时:%i分:%s秒');
  • 1
  • 2
  • 1
  • 2
  • 1
  • 2
SELECT f1();
  • 1
  • 1
  • 1

在 Java 项目上调用函数(函数有返回值)时

CallableStatement cs = conn.prepareCall("{?= call f1()}");
  • 1
  • 1
  • 1

2 自定义两个参数的函数

输入两个值,计算其平均值。

  1.  
    CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
  2.  
    RETURNS FLOAT(10,2) UNSIGNED
  3.  
    RETURN (num1+num2)/2;
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

3 创建具有复合机构函数体的函数

插入一条新数据,user_name,返回该行的id。

  1.  
    CREATE FUNCTION adduser(username VARCHAR(20))
  2.  
    RETURNS INT UNSIGNED
  3.  
    BEGIN
  4.  
    INSERT imooc_goddess(user_name) VALUES (username);
  5.  
    return LAST_INSERT_ID();
  6.  
    END
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

6 通过 Navicat 查看存储过程和函数的创建语句

在 Navicat 中点开函数栏,选择你要查看的函数,右击选择对象信息。 
MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

选择DDL栏(数据定义语言)

MySQL存储过程和自定义函数、Navicat for mysql、创建存储过程和函数、调用存储过程和函数的区别

  1.  
    CREATE DEFINER=`root`@`localhost` PROCEDURE `selectAll`()
  2.  
    BEGIN
  3.  
    SELECT * FROM imooc_goddess;
  4.  
    END
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4

语句中的

DEFINER=`root`@`localhost`
  • 1
  • 1
  • 1

是数据库自己默认添加的。

复制数据定义语言创建过程时-发生错误

当我想直接复制数据定义语言,更改一个名称创建另一个存储过程时,总是创建不成功。 
1、更改名称(Error)

  1.  
    CREATE DEFINER=`root`@`localhost` PROCEDURE `myselectAll`()
  2.  
    BEGIN
  3.  
    SELECT * FROM imooc_goddess;
  4.  
    END
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4

2、修改DEFINER(下面的语句都是不能成功创建)

  1.  
    CREATE DEFINER={'root`@`localhost`} PROCEDURE `myselectAll`()
  2.  
    BEGIN
  3.  
    SELECT * FROM imooc_goddess;
  4.  
    END
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  1.  
    CREATE DEFINER={'root`@`%`} PROCEDURE `myselectAll`()
  2.  
    BEGIN
  3.  
    SELECT * FROM imooc_goddess;
  4.  
    END
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  1.  
    CREATE DEFINER={root|localhost} PROCEDURE `myselectAll`()
  2.  
    BEGIN
  3.  
    SELECT * FROM imooc_goddess;
  4.  
    END
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  1.  
    CREATE DEFINER={root} PROCEDURE `myselectAll`()
  2.  
    BEGIN
  3.  
    SELECT * FROM imooc_goddess;
  4.  
    END
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  1.  
    CREATE DEFINER=root PROCEDURE `myselectAll`()
  2.  
    BEGIN
  3.  
    SELECT * FROM imooc_goddess;
  4.  
    END
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  1.  
    CREATE DEFINER=`CURRENT_USER`() PROCEDURE `myselectAll`()
  2.  
    BEGIN
  3.  
    SELECT * FROM imooc_goddess;
  4.  
    END
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  1.  
    CREATE DEFINER=CURRENT_USER PROCEDURE `myselectAll`()
  2.  
    BEGIN
  3.  
    SELECT * FROM imooc_goddess;
  4.  
    END
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4

3、删除DEFINER 
删除DEFINER,可以创建成功

  1.  
    CREATE PROCEDURE `myselectAll`()
  2.  
    BEGIN
  3.  
    SELECT * FROM imooc_goddess;
  4.  
    END
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4

然而创建函数时却可以带 DEFINER 
下面的语句可以创建成功

  1.  
    CREATE DEFINER=`root`@`localhost` FUNCTION `f2`(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED) RETURNS float(10,2) unsigned
  2.  
    RETURN (num1+num2)/2
  • 1
  • 2
  • 1
  • 2
  • 1
  • 2

复制数据定义语言,创建存储过程要删除DEFINER。

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

    • 存储过程实现的功能要复杂一些,而函数的的针对性更强

    • 存储的过程可以返回多个值,函数只能有一个返回值

    • 存储过程一般独立的执行,而函数可以作为其他 SQL语句的组成部分来实现。

    • 过程 通俗易懂的说法:它只是将其中的程序执行一遍

    • 函数 通俗易懂的说法:它不但将其中的程序执行一遍,还返回一个值

上一篇:牛客练习赛18E pocky游戏 状压dp


下一篇:有关Java字符集编码的问题