精华内容
下载资源
问答
  • MySQL5.6存储过程和函数

    千次阅读 2016-03-13 22:18:17
    1.存储程序可以分为存储过程和函数,MySQL中创建存储过程和函数使用的语句分别是create procedurecreate function,使用call语句来调用存储过程,只能用输出变量返回值。函数可以从语句外调用(通过引用函数名),...

    1.存储程序可以分为存储过程和函数,MySQL中创建存储过程和函数使用的语句分别是create procedure和create function,使用call语句来调用存储过程,只能用输出变量返回值。函数可以从语句外调用(通过引用函数名),也能返回标量值。存储过程也可以调用其他的存储过程。
    2.存储过程的概念
    存储过程就是一条或者多条SQL语句集合,可视为批文件,但是其作用不仅仅限于批处理。
    3.存储过程创建
    (1)基本语法:

    create procedure sp_name([proc_parameter])
    [characteristics...] routine_body
    

    create procedure:是定义存储过程的关键字。
    sp_name:存储过程的名字
    proc_parameter:指定存储过程的参数列表

    [in|out|inout] param_name type
    

    in表示输入参数;out表示输出参数;inout表示可以输入也可以输出;
    param_name:表示参数名称;type:表示参数类型,可以是mysql中的任意类型
    characteristics指定存储过程的特性:
    a)language sql:说明routine body部分是由sql语句组成的,当前系统支持的语言为SQL,SQL是LANGUAGE特性的唯一值。
    b)[not]deterministic:指明存储过程执行的结果是否正确。DETERMINSTIC表示结果是确定的,每次执行存储过程时,相同的输入会得到相同的输出。NOT DETERMINSTIC表示结果是不确定的,相同的输入可能得到不同的输出,如果没有指定任意一个值,默认为NOT DETERMINISTIC。
    c){CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}:指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句,但是不包含读写数据的语句;NO SQL标明子程序不包含SQL语句;READS SQL DATA说明子程序包含读数据的语句;MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下,系统会指定为CONTAINS SQL。
    d)SQL SECUEITY{DEFINER|INVOKER}:指明谁有权限来执行,DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下系统指定为DEFINER。
    e)COMMENT’string’注释信息,可以用来描述存储过程或者函数。
    routine_body:是SQL代码内容,可以用BEGIN…AND来表示SQL代码的开始和结束。
    (2)DELIMITER//语句的作用就是将mysql的结束符设置为//,因为mysql默认的语句结束符号为分号,为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER改变存储过程的结束符,并以END//结束存储过程,存储过程定义完毕之后在使用中DELIMITER;恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。
    (3)当使用DELIMITER命令时,应该避免使用反斜杠字符(\),因为反斜线是mysql的转义字符。
    4.创建存储函数
    (1)基本语法:
    create function([func_parameter]) returns type [characteristic...] routine_body
    解释:
    create function是创建存储函数的关键字
    func_name:表示存储函数的名字
    func_parameter:为存储过程的参数列表,参数列表形式如下:[in|out|inout] param_type
    其中in表示参数输入,out表示参数输出,inout表示既可以输入也可以输出;param_name表示参数名称;type表示参数类型;该类型可以是mysql数据库中的任意类型。
    returns type:表示函数返回的数据类型;characteristic指定存储函数的特性,取值与创建存储过程时相同。
    (2)如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句中指定的类型值,返回值将被清只为恰当的类型。比如,如果一个函数返回一个ENUM或者SET值,但是RETURN语句返回一个整数,对于SET成员集相应的ENUM成员,从函数返回的值是字符串。
    (3)指定参数IN,OUT,INOUT只对PROCEDURE是合法的,RETURNS子句只能对FUNCTION做指定,对于函数而言,是强制的,他用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。
    5.变量
    (1)变量的作用范围BEGIN…AND之间
    (2)定义变量:

    DECLARE var_name,...data_type[default value];
    

    var_name:变量的名字
    default value:给变量提供一个默认值,除了可以声明为一个常数外,还可以指定为一个表达式,如果没有default子句,初始值为null。
    (2)变量赋值

    set var_name=expr[,var_name=expr]...;
    

    在存储程序中的set语句是一般SET语句的扩展版本,被参考变量可能是子程序内声明的变量,或是全局服务器变量。在存储过程程序中的SET语句作为预先存在的SET语法的一部分实现。
    6.定义条件和处理程序
    (1)定义条件概念:定义条件是事先定义程序执行过程中遇到的问题。
    (2)处理程序概念:处理程序定义了在遇到这些问题时应该采用的处理方式,并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力,避免程序有异常停止运行。
    (3)定义条件语法

    declare condition_name CONDITION FOR[condition_type]
    [condition_type]:
    SQLSTATE[VALUE]sqlstate_value|mysql_error_code
    

    condition_name:表示条件的名称;condition_type:条件的类型;
    sqlstate_value和mysql_error_code:mysql的错误;
    sqlstate_value:长度为5的字符串类型错误代码
    mysql_error_code:数值类型错误代码
    (4)定义处理程序

    delcare handler_type HANDLER FOR condition_value[,...] sp_statement
    handler_type:
    CONTINUE|EXIT|UNDO
    condition_value:
    	SQLSTATE[VALUE]sqlstate_value
    	|condition_name
    	|SQLWARNING
    	|NOT FOUND
    	|SQLEXCEPTION
    	|mysql_error_code
    

    handler_type:错误处理方式,三个取值CONTINUE,EXIT,UNDO。
    CONTINUE:遇到错误不处理;EXIT:遇到错误退出;UNDO:遇到错误后撤回之间的操作,MYSQL中暂时不支持这样的操作。
    condition_value表示错误类型,有这些取值:
    SQLSTATE[VALUE] sqlstate_value包含5个字符的字符串错误值
    condition_name表示DECLARE CONDITION定义的错误条件名称
    SQLWARNING:匹配所有以01开头的SQLSTATE错误代码
    NOT FOOUND匹配所有以02开头的SQLSTATE错误代码
    SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
    mysql_error_code匹配数值类型错误代码
    sql_statement参数为程序语句段,表示在遇到定义的错误时,需要执行的存储过程或者函数。
    7.光标
    (1)适用场景:
    当查询语句可能返回多条记录,如果数据量非常大,需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据.
    (2)光标必须在声明处理程序之前被声明,并且变量和条件还必须在声明光标或处理程序之前被声明。

    declare cursor_name cursor for select_statement
    

    cursor_name:表示游标的名字;select_statement表示select语句的内容,返回创建一个用于创建游标的结果集。
    (3)打开光标open cursor_name{光标名称}
    注意:一定要是先声明游标,然后再打开游标,否则会报错误。
    (4)使用游标

    fetch cursor_name into var_name [,var_name]...{参数名称}
    

    cursor_name游标的名称;var_name:表示将游标中的SELECT语句查询出来的信息存储在该参数中,var_name必须声明在游标之前就定义好。
    (5)关闭游标close cursor_name{游标名称};
    注意:这个语句关闭先前打开的游标,如果没有被明确的关闭,游标在它被声明的符合语句的末尾被关闭。
    (6)mysql中的游标只能在存储过程和函数中使用
    8.流程控制的使用
    (1)流程控制语句用来根据条件控制语句的执行。mysql中的用来构造控制流程的语句有:IF语句,CASE语句,LOOP语句,WHILE语句,LEAVE语句,ITERATE语句,REPEAT语句。每个流程中可能包含一个单独的语句或者是使用BEGIN…END构造的符合语句,构造可以被嵌套。
    (2)IF语句
    包含多个条件判断,根据判断的结果为TRUE或者FALSE执行相应的语句。

    IF expr_condition THEN statement_list
    [ELESEIF expr_condition THEN statement_list]...
    [ELESE statement_list]
    END IF
    

    (3)CASE语句
    另一个进行条件判断的语句,该语句有两种格式

    CASE case_expr
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
    END CASE
    
    CASE
    WHEN expr_condition THEN statement_list
    [WHEN expr_condition THEN statement_list]
    [ELSE statement_list]
    END CASE
    

    (4)LOOP语句
    LOOP循环语句用来重复执行某些语句。LOOP只是创建一个循环操作过程,并不进行条件判断。LOOP内的语句一直重复执行知道循环被退出,跳出循环过程,使用LEAVE子句,LOOP基本格式

    [loop:label:]LOOP
    statement_list
    END LOOP[loop_label]
    

    (5)LEAVE语句
    LEAVE语句用来退出任何被标注的流程控制构造,LEAVE语句基本格式如下:LEAVE label
    LEAVE和BEGIN…AND或循环一起使用。
    (6)ITERATE语句
    将执行顺序转到语句段开头处
    基本语法格式:ITERATE label
    ITERATE只可以出现在LOOP,REPEAT和WHILE语句内,ITERATE再次循序,这个语句必须跟在循环标志前面。
    (7)REPEAT语句
    创建一个带条件判断的循环过程,每次语句执行完毕后,会对条件表达式进行判断,如果表达式为真,则循环结束;否则重复执行循环中的语句。REPEAT语句基本格式:

    [repeat_label:]REPEAT
    statement_list
    UNTIL expr_condition
    END REPEAT[repeat_label]
    

    (8)WHILE语句
    创建一个待条件判断的循环过程,与REPEAT不同,WHILE在执行语句时,先对指定的表达式进行判断,如果为真,则执行循环内的语句,否则退出循环。
    WHILE语句的基本格式:

    [while_label:]WHILE expr_condition DO
    statement_list
    END WHILE[while_label]
    

    9.存储过程和函数的调用
    (1)存储过程必须使用CALL来调用,并且存储过程和数据库相关,如果要执行其他数据库中的存储过程,需要指定数据库名称,比如CALL dbname.proname。存储函数调用与MYSQL中预定义的函数的调用方式一样。
    (2)调用存储过程CALL sp_name([parameter[,...]])
    CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程,其中sp_name是存储过程的名称,parameter是存储过程的参数。
    (3)调用存储函数
    存储函数和Mysql内部函数调用方式时一样的,区别在于存储函数是用户自己定义的,mysql内部函数是有开发者定义的。
    10.查看存储过程和函数
    (1)查看存储过程和函数状态
    show {procedure|function} status [like 'pattern']
    like语句表示匹配存储过程或者函数的名字
    (2)查看存储过程和函数的定义

    show create {procedure|function} sp_name
    

    (3)从information_schema.Routines表中查看存储过程和函数的信息
    因为MYSQL中存储过程和函数的信息都存储在information_schema数据库下的routines表中。可以通过查询这个表的记录来查询存储过程和函数信息。

    SELECT * FROM information_schema.Routines
    WHER ROUTINE_NAME='sp_name';
    

    ROUTINE_NAME:存储的是存储过程和函数的名称;SP_NAME参数表示存储过程或者函数的名称。
    在Information_schema数据库下的Routines表中,存储所有的存储过程和函数的定义,SELECT语句查询Routines表中的存储过程和函数定义时,一定要使用ROUTINE_NAME字段指定存储过程或函数名称。否则将查询出所有的存储过程或函数的定义。如果存储过程和存储函数的名字相同,则需要同时指定ROUTINE_TYPE字段表名查询的是哪种类型的存储程序。
    11.修改存储过程和函数
    (1)基本语法:

    alter {procedure|function} sp_name [characteristic...] 
    

    characteristic的特性可能的取值:
    CONTAINS SQL:子程序包含SQL语句,但不包含读或写的语句
    NO SQL:子程序中不包含SQL语句
    READS SQL DATA:子程序中包含读数据的语句
    MODIFIES SQL DATA:子程序中包含写数据的语句
    SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行。
    DEFINER:表示只有定义者自己可以执行
    COMMENT’string’:表示注释信息
    12.删除存储过程和函数
    基本的语法:drop {procedure|function} [if exists] sp_name
    其中if exists子句是Mysql的扩展,如果程序或者函数不存储,他可防止发生错误,产生一个SHOW WARNINGS查看的警告。

    在这里插入图片描述

    展开全文
  • MySQL存储过程和函数过程和函数,它们被编译后保存在数据库中,称为持久性存储模块(Persistent Stored Module,PSM),可以反复调用,运行速度快。1.1 存储过程存储过程是由过程化 SQL 语句书写的过程,这个过程经...

    与你相遇 好幸运
    可我已失去为你泪流满面的权利
    但愿在我看不到的天际
    你张开了双翼


    1 MySQL存储过程和函数

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

    1.1 存储过程

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

    1.2 函数

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

    MySQL 命令的执行过程
    这里写图片描述

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

    2 我的 MySQL 数据表

    数据库名:peng
    表名: imooc_goddess

    这里写图片描述

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

    /*
    Navicat MySQL Data Transfer
    
    Source Server         : localhost_3306
    Source Server Version : 50712
    Source Host           : localhost:3306
    Source Database       : peng
    
    Target Server Type    : MYSQL
    Target Server Version : 50712
    File Encoding         : 65001
    
    Date: 2016-10-04 20:53:44
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for imooc_goddess
    -- ----------------------------
    DROP TABLE IF EXISTS `imooc_goddess`;
    CREATE TABLE `imooc_goddess` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_name` varchar(30) NOT NULL,
      `sex` int(11) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `birthday` date DEFAULT NULL,
      `email` varchar(30) DEFAULT NULL,
      `mobile` varchar(11) DEFAULT NULL,
      `create_user` varchar(30) DEFAULT NULL,
      `create_date` date DEFAULT NULL,
      `update_user` varchar(30) DEFAULT NULL,
      `update_date` date DEFAULT NULL,
      `isdel` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of imooc_goddess
    -- ----------------------------
    INSERT INTO `imooc_goddess` VALUES ('2', '小彭', null, '23', null, null, null, null, null, null, null, null);
    INSERT INTO `imooc_goddess` VALUES ('6', '胖子', '0', '78', '2016-10-03', 'hongming@qq.com', '1211555599', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
    INSERT INTO `imooc_goddess` VALUES ('7', '小溪', '0', '34', '2016-10-03', 'hongming@qq.com', '1211555599', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
    INSERT INTO `imooc_goddess` VALUES ('9', '小霞', null, '23', '1990-09-09', 'xiaoxia@qq.com', '232445455', null, '2016-10-03', null, '2016-10-03', null);
    INSERT INTO `imooc_goddess` VALUES ('10', 'hh', '1', '23', '1990-09-09', 'jkjfskf', '12323', 'Admin', '2016-10-03', 'admin', '2016-10-03', '1');
    INSERT INTO `imooc_goddess` VALUES ('11', '平', '1', '23', '2998-04-09', 'jjjj@ww.com', '1323', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
    INSERT INTO `imooc_goddess` VALUES ('12', '航母', '1', '23', '2333-09-09', 'jkksjkjf', '1232', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
    INSERT INTO `imooc_goddess` VALUES ('13', '胖纸', '1', '23', '1991-09-09', 'jjijijij', '1323244', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
    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 创建存储过程模板

    这里写图片描述

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

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

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

    CREATE PROCEDURE sp_name(proc_paramenter)
    BEGIN
    routine_body
    END

    3.2 调用存储过程

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

    4 创建的存储过程

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

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

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

    示例

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

    这里写图片描述

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

    方式一 CMD命令行方式

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

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

    这里写图片描述

    方式二 Navicat 新建查询

    创建名为 v3 的存储过程

    这里写图片描述

    方式三 Navicat 新建函数

    创建名为 v2 的存储过程

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

    这里写图片描述

    2.选择过程。
    这里写图片描述

    3.这个存储过程没有参数,不填写,点完成。
    这里写图片描述

    4.在过程体中输入SELECT VERSION();,点击保存,输入过程名 v2 ,确认。
    这里写图片描述

    还是GIF图来的快
    这里写图片描述

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

    CALL v1();
    CALL v2();
    CALL v3();

    来调用

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

    在 CMD 中执行下面的语句。
    这里写图片描述

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

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

    这里写图片描述

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

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

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

    1.要输入的值为 p_id,返回的值为 userNums。
    这里写图片描述

    2.在过程体中输入

    DELETE FROM imooc_goddess WHERE id = p_id;
    SELECT COUNT(id) FROM imooc_goddess INTO userNums;

    这里写图片描述

    3.点击进入函数,点击运行
    这里写图片描述

    结果:

    时间: 00:00.08
    
    Procedure executed successfully
    受影响的行: 1
    
    Parameters: IN `p_id` int,OUT `userNums` int
    14,@nums
    Return values: 14, 4

    1.在 CMD 命令行的写法

    DELIMITER // 
    CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
    BEGIN
    DELETE FROM imooc_goddess WHERE id = p_id;
    SELECT COUNT(id) FROM imooc_goddess INTO userNums;
    END
    DELIMITER ; 

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

    1. 在 CMD 命令行调用

    这里写图片描述

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

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

    这里写图片描述

    SQL 语句

    CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT 
    deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)
    BEGIN
    DELETE FROM imooc_goddess WHERE age = p_age;
    SELECT ROW_COUNT() INTO deleteUsers;
    SELECT COUNT(id) FROM imooc_goddess INTO userCounts;
    END

    5 自定义函数

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

    5.1 创建自定义函数模板

    CREATE FUNCTION function_name
    RETURNS
    {STRING|INTEGER|REAL|DECIMAL}
    routine_body

    关于函数体

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

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

    1 自定义不带参数的函数

    这里写图片描述

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

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

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

    2 自定义两个参数的函数

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

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

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

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

    CREATE FUNCTION adduser(username VARCHAR(20))
    RETURNS INT UNSIGNED
    BEGIN
    INSERT  imooc_goddess(user_name) VALUES (username);
    return LAST_INSERT_ID();
    END

    这里写图片描述

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

    在 Navicat 中点开函数栏,选择你要查看的函数,右击选择对象信息。
    这里写图片描述

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

    这里写图片描述

    CREATE DEFINER=`root`@`localhost` PROCEDURE `selectAll`()
    BEGIN
    SELECT * FROM imooc_goddess;
    END

    语句中的

    DEFINER=`root`@`localhost`

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

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

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

    CREATE DEFINER=`root`@`localhost` PROCEDURE `myselectAll`()
    BEGIN
    SELECT * FROM imooc_goddess;
    END

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

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

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

    CREATE  PROCEDURE `myselectAll`()
    BEGIN
    SELECT * FROM imooc_goddess;
    END

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

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

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

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

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

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

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

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

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

    展开全文
  • 数据库 存储过程和函数

    千次阅读 2018-04-15 12:55:24
    存储过程和函数的引入 存储过程和函数是在数据库中定义一些 SQL 语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的 SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。而且,存储...

    存储过程和函数的引入

    存储过程和函数是在数据库中定义一些 SQL 语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的 SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。而且,存储过程和函数是在 MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输;

    创建存储过程和函数

    1,创建存储过程

    CREATE PROCEDURE sp_name([proc_parameter[,…]])
    [characteristic…] routine_body
    sp_name 参数是存储过程的名称;
    proc_parameter 表示存储过程的参数列表;
    characteristic 参数指定存储过程的特性;
    routine_body 参数是 SQL 代码的内容,可以用 BEGIN…END 来标志 SQL 代码的开始和结束。
    proc_parameter 中的每个参数由 3 部分组成。这 3 部分分别是输入输出类型、参数名称和参数类型。
    [ IN | OUT | INOUT ] param_name type
    其中,IN 表示输入参数;OUT 表示输出参数;INOUT 表示既可以是输入,也可以是输出;param_name 参数是存储过程的参数名称;type 参数指定存储过程的参数类型,该类型可以是 MySQL 数据库的任意数据类型;
    Characteristic 参数有多个取值。其取值说明如下:
    LANGUAGE SQL:说明 routine_body 部分是由 SQL 语言的语句组成,这也是数据库系统默认的语言。
    [ NOT ] DETERMINISTIC :指明存储过程的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每
    次执行存储过程时,相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是非确定的,相同的输入可能得到不同的输出。默认情况下,结果是非确定的。
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } :指明子程序使用 SQL 语句的限制;
    CONTAINS SQL 表示子程序包含 SQL 语句,但不包含读或写数据的语句;NO SQL 表示子程序中不包含 SQL
    语句;READS SQL DATA 表示子程序中包含读数据的语句;MODIFIES SQL DATA 表示子程序中包含写数据的语句。
    默认情况下,系统会指定为 CONTAINS SQL;
    SQL SECURITY { DEFINER | INVOKER };指明谁有权限来执行。
    DEFINER 表示只有定义者自己才能够执行;
    INVOKER 表示调用者可以执行。默认情况下,系统指定的权限是 DEFINER。
    COMMENT ‘string’ :注释信息;

    2,创建存储函数

    CREATE FUNCTION sp_name ( [func_parameter[,…]] )
    RETURNS type
    [ characteristic… ] routine_body
    sp_name 参数是存储函数的名称;func_parameter 表示存储函数的参数列表;RETURNS type 指定返回值的类型;characteristic 参数指定存储过程的特性,该参数的取值与存储过程中的取值是一样的;routine_body 参数是 SQL 代码的内容,可以用 BEGIN…END 来标志 SQL 代码的开始和结束;func_parameter 可以由多个参数组成,其中每个参数由参数名称和参数类型组成,其形式如下:param_name type 其中,param_name 参数是存储函数的参数名称;type 参数指定存储函数的参数类型,该类型可以是 MySQL 数据库的任意数据类型;

    3,变量的使用

    3.1 定义变量
    DECLARE var_name [,…] type [ DEFAULT value ]
    3.2 为变量赋值
    SET var_name = expr [,var_name=expr] …
    SELECT col_name[,…] INTO var_name[,…]
    FROM table_name WHERE condition

    4,游标的使用

    查询语句可能查询出多条记录,在存储过程和函数中使用游标来逐条读取查询结果集中的记录。游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前,并且声明在变量和条件之后。
    4.1 声明游标
    DECLARE cursor_name CURSOR FOR select_statement ;
    4.2 打开游标
    OPEN cursor_name;
    4.3 使用游标
    FETCH cursor_name INTO var_name [,var_name … ];
    4.4 关闭游标
    CLOSE cursor_name;

    5,流程控制的使用

    存储过程和函数中可以使用流程控制来控制语句的执行。MySQL 中可以使用 IF 语句、CASE 语句、LOOP语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和WHILE 语句来进行流程控制。
    5.1 IF语句
    IF search_condition THEN statement_list
    [ ELSEIF search_condition THEN statement_list ]…
    [ ELSE statement_list ]
    END IF

    5.2 CASE语句
    CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]…
    [ELSE statement_list ]
    END CASE

    5.3 LOOP,LEAVE 语句
    LOOP 语句可以使某些特定的语句重复执行,实现一个简单的循环。但是 LOOP 语句本身没有停止循环的语句,必须是遇到 LEAVE 语句等才能停止循环。LOOP 语句的语法的基本形式如下:
    [begin_label:]LOOP
    Statement_list
    END LOOP [ end_label ]
    LEAVE 语句主要用于跳出循环控制。语法形式如下:
    LEAVE labe

    5.4 ITERATE 语句
    ITERATE 语句也是用来跳出循环的语句。但是,ITERATE 语句是跳出本次循环,然后直接进入下一次循环。基本语法:
    ITERATE label ;

    5.5 REPEAT 语句
    REPEAT 语句是有条件控制的循环语句。当满足特定条件时,就会跳出循环语句。REPEAT 语句的基本语法形式如下:
    [ begin_label : ] REPEAT
    Statement_list
    UNTIL search_condition
    END REPEAT [ end_label ]

    5.6 WHILE语句
    [ begin_label : ] WHILE search_condition DO
    Statement_list
    END WHILE [ end_label ]

    展开全文
  • 1 MySQL存储过程和函数 过程和函数,它们被编译后保存在数据库中,称为持久性存储模块(Persistent Stored Module,PSM),可以反复调用,运行速度快。 1.1 存储过程 存储过程是由过程化 SQL 语句书写
     
    

    目录(?)[+]


    与你相遇 好幸运 
    可我已失去为你泪流满面的权利 
    但愿在我看不到的天际 
    你张开了双翼


    1 MySQL存储过程和函数

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

    1.1 存储过程

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

    1.2 函数

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

    MySQL 命令的执行过程: 
    这里写图片描述

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

    2 我的 MySQL 数据表

    数据库名:peng 
    表名: imooc_goddess

    这里写图片描述

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

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

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

    3.1 创建存储过程模板

    这里写图片描述

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

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

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

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

    3.2 调用存储过程

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

    4 创建的存储过程

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

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

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

    示例

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

    这里写图片描述

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

    方式一 CMD命令行方式

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

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

    这里写图片描述

    方式二 Navicat 新建查询

    创建名为 v3 的存储过程

    这里写图片描述

    方式三 Navicat 新建函数

    创建名为 v2 的存储过程

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

    这里写图片描述

    2.选择过程。 
    这里写图片描述

    3.这个存储过程没有参数,不填写,点完成。 
    这里写图片描述

    4.在过程体中输入SELECT VERSION();,点击保存,输入过程名 v2 ,确认。 
    这里写图片描述

    还是GIF图来的快 
    这里写图片描述

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

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

    来调用

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

    在 CMD 中执行下面的语句。 
    这里写图片描述

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

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

    这里写图片描述

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

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

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

    1.要输入的值为 p_id,返回的值为 userNums。 
    这里写图片描述

    2.在过程体中输入

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

    这里写图片描述

    3.点击进入函数,点击运行 
    这里写图片描述

    结果:

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

    1.在 CMD 命令行的写法

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

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

    1. 在 CMD 命令行调用

    这里写图片描述

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

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

    这里写图片描述

    SQL 语句

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

    5 自定义函数

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

    5.1 创建自定义函数模板

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

    关于函数体

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

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

    1 自定义不带参数的函数

    这里写图片描述

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

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

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

    2 自定义两个参数的函数

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

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

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

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

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

    这里写图片描述

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

    在 Navicat 中点开函数栏,选择你要查看的函数,右击选择对象信息。 
    这里写图片描述

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

    这里写图片描述

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

    语句中的

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    展开全文
  • MySQL调用存储过程和函数

    千次阅读 2019-09-01 15:00:27
    存储过程必须使用call语句调用,并且存储过程和数据库相关,如果要执行其他数据库中的存储过程,需要指定数据库名称。例如call dbname.procname。存储函数的调用与MySQL中预定义的函数的调用方式相同。 1.调用存储...
  • 存储过程和函数的区别

    千次阅读 2018-07-11 15:26:07
    函数可以嵌入sql中和存储过程使用,但是存储过程需要让sql的query可以执行,将mysql_real_connect的最后一个参数设置为CLIENT_MULTI_STATEMENTS函数的限制比较多,不能用临时表,只能用表变量,有些函数不能用,...
  • mysql手册05_存储过程和存储函数

    万次阅读 2020-08-14 21:34:41
    mysql手册05_存储过程和存储函数 存储过程和存储函数是存储在数据库中的一段SQL语句集合。 通过调用存储过程或存储函数,可以简化开发,减少数据在数据库服务器间的传输。 存储过程和存储函数的区别是:存储过程...
  • SQL存储过程和函数

    千次阅读 2019-07-05 14:19:07
    SQL存储过程和函数 ...(2)存储过程的参数可以使用 IN、OUT、INOUT 类型,而函数的参数只能是 IN 类型的。 相关操作 语句 定义 CREATE PROCEDURE sp_name ([proc_parameter[,…]]) [ch...
  • 存储过程,存储函数(Oracle)存储过程和存储函数指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数存储过程和存储函数的区别?存储函数:可以通过return 语句返回函数值。存储过程:不能除此之外...
  • 剑指Offer——常用SQL语句、存储过程和函数常用SQL语句1.在MySQL数据库建立多对多的数据表关系2.授权、取消授权grant、revokegrant select, insert, update, delete on testdb.* to common_user@’%’; revoke all ...
  • mysql中查看所有存储过程和函数

    千次阅读 2019-09-22 17:57:51
    直接po图代码 参考网页 #查询数据库中的存储过程 SHOW PROCEDURE STATUS; #查询数据库中的函数 SHOW FUNCTION STATUS;...#查看存储过程函数的创建代码 ...#mysql中查看所有存储过程和函数 #查看...
  • MYSQL中创建存储过程和函数分别使用CREATE PROCEDURECREATE FUNCTION 使用CALL语句来调用存储过程存储过程也可以调用其他存储过程 函数可以从语句外调用,能返回标量值 创建存储过程 语法 CREATE PROCEDURE sp...
  • 开始之前 首先创建如下两张表,并初始化一些数据。 创建存储过程(CREATE PROCEDURE) ...基本语法格式如下: ...CREATE PROCEDURE sp_name (parameters) ...sp_name为存储过程的名称(唯一性,没有所谓的重...
  • MySql存储过程函数

    千次阅读 2015-08-06 08:21:26
    存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中...
  • SQL中的存储过程和函数

    千次阅读 2017-03-04 11:59:35
    存储过程和函数 简单的说,存储过程就是一条或者多条SQL语句的集合。可以视为批文件,但是其作用不仅仅局限于批处理。本文主要介绍如何创建存储过程和存储函数,以及如何调用、查看、修改、删除存储过程...
  • mysql中存储过程和函数区别

    千次阅读 2018-05-23 18:11:25
    概述1)一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。存储过程,功能强大,可以执行包括修改表等一系列数据库操作;用户定义函数不能用于执行一组修改全局数据库状态的操作。2)对于...
  • MySQL存储过程和函数的区别

    千次阅读 2017-07-19 12:32:02
    存储函数和存储过程统称为存储例程(stored routine)。两者的定义语法很相似,但却是不同的内容。 存储函数限制比较多,比如不能用临时表,只能用表变量。还有一些函数都不可用等等。而存储过程的限制相对就比较少...
  • 存储过程和存储函数的区别

    千次阅读 2018-09-09 12:46:33
    存储过程和存储函数的区别 语法区别:关键字不一样, 存储函数存储过程多了两个return。 本质区别:存储函数有返回值,而存储过程没有返回值。 如果存储过程想实现有返回值的业务,我们就必须使用out类型的参数...
  • MyBatis中调用存储过程和函数

    千次阅读 2018-07-06 12:03:53
    创建存储过程:1、2、3、4、5、调用6、6、MyBatis中调用存储过程和函数:https://www.cnblogs.com/xushirong/p/6999568.html
  • C#调用存储过程和函数

    千次阅读 2017-12-23 11:34:44
    C#调用PLSQL函数和存储过程,简单实用的小教程带你体会编码的快感.......
  • 文章目录存储过程和函数变量系统变量全局变量会话变量自定义变量用户变量局部变量存储过程创建语法调用语法删除存储过程查看存储过程的信息案例函数创建语法调用语法查看函数删除函数流程控制结构分支结构if函数case...
  • Mysql存储过程和函数区别介绍

    千次阅读 2019-03-02 15:42:46
    存储存储过程 是一段代码(过程),存储在数据库中的SQL组成。一个存储过程通常用于完成一段业务逻辑,例如报名,...存储过程和函数都是属于某个数据库。 存储 创建无参存储过程: delimiter $$ CREATE PROCEDURE sho...
  • MySQL——查看存储过程和函数

    万次阅读 2018-05-22 21:47:17
    1. 查看存储过程和函数的状态 用 SHOW STATUS 语句可以查看存储过程和函数的状态,其基本的语法结构如下: SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE 'pattern' ] SHOW STATUS 语句是 MySQL 的...
  • MySQL删除存储过程和函数

    万次阅读 2015-06-24 13:39:29
    MySQL中使用DROP PROCEDURE语句来删除存储过程使用DROP FUNCTION语句来删除存储函数。 基本的语法格式如下: DROP {PROCEDURE|FUNCTION} sp_name; 其中,sp_name参数表示存储过程函数的名称。 1. ...
  • 一、为什么要使用存储过程和函数 存储过程和函数,是用来实现一组关于表操作的SQL语句、可重复地执行操作数据库的集合。 存储过程和函数可以简单的理解为一条或多条SQL语句的集合。存储过程和函数就是事先经过编译并...
  • MySQL-调用存储过程和函数

    千次阅读 2013-05-14 11:29:02
    1.要使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式来实现。 存储过程是通过CALL 语句来调用的。而存储函数使用方法与MySQL内部函数使用方法是一样的。 执行存储过程和存储函数需要拥有...
  • Mysql存储过程函数和游标基本使用

    千次阅读 2019-05-24 14:46:19
    掌握数据库PL/SQL编程语言,以及数据库存储过程的设计和使用方法 (2)实验内容要求 存储过程定义、存储过程运行,存储过程更名,存储过程删除,存储过程的参数传递。掌握PL/SQL编程语言编程规范,规范设计...
  • oracle存储过程存储函数总结

    千次阅读 2018-01-07 10:20:02
    PL/SQL语法 存储过程 存储函数 触发器

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,010,834
精华内容 404,333
关键字:

存储过程和函数如何使用