精华内容
下载资源
问答
  • mysql存存储储过过程程原原理理与与用用法法详详解解 这篇文章主要介绍了mysql存储过程,结合图文与实例形式较为详细的分析了mysql存储过程基本概念原理用法 及相关操作 意事项,需要的朋友可以参考下 本文实例讲述了...
  • 存储过程再简单点来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批件,虽然它们的作用不仅限于批处理。在我看来, 存储过程就是有业务逻辑和流程的集合, 可以在存储过程中创建表...

    存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合,存储和和函数的区别在于函数必须有返回值,而存储过程没有,存储过程的参数可以使用IN、OUT、INOUT类型,而函数的参数只能是IN类型。存储过程再简单点来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批件,虽然它们的作用不仅限于批处理。在我看来, 存储过程就是有业务逻辑和流程的集合, 可以在存储过程中创建表,更新数据, 删除等等。本次博客就来讲一下存储过程

    存储过程的操作

    语法如下:

    创建:CREATE PROCEDURE sp_name([proc_parameter[,...]])[characteristic...]routine_body

    proc_parameter:[IN|OUT|INOUT] param_name type #type: Anyvalid MySQL data type

    characteristic:

    LANGUAGE SQL|[NOT] DETERMINISTIC|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY {DEFINAER|INVOKER}|COMMENT 'string'routine_body:

    Valid SQLprocedure statement orstatements

    修改:ALTER PROCEDURE sp_name [characteristic...]characteristic:

    {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY {DEFINAER|INVOKER}|COMMENT 'string'调用:

    CALL sp_name([parameter[,...]])

    删除:DROP PROCEDUREsp_name

    查看:

    showPROCEDURE STATUS [like 'pattern']SHOWCREATE PROCEDURE sp_name

    MySQL的存储过程和函数中允许包含DDL语句,也允许在存储过程中执行提交或者回滚,但是存储过程和函数不允许执行LOAD DATA INFILE语句,存储过程和函数可以调用其他的过程或者函数。

    插入小知识点@:

    1.用户变量:以"@"开始,形式为"@变量名"

    用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效。2.全局变量:定义方式 set GLOBAL 变量名 或者 set @@global.变量名

    对所有客户端生效,只有具有super权限才可以设置全局变量。

    存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批件,虽然它们的作用不仅限于批处理。

    在我看来, 存储过程就是有业务逻辑和流程的集合, 可以在存储过程中创建表,更新数据, 删除等等。

    为什么要使用存储过程

    通过把处理封装在容易使用的单元中,简化复杂的操作(正如前面例子所述)。

    由于不要求反复建立一系列处理步骤,这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程,则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多,出错的可能性就越大。防止错误保证了数据的一致性。

    简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化,只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

    users表如下:

    82981a59059ef3eab71fe79bf5828a41.png

    创建存储过程,传入性别(男或女),显示对应性别的用户id,返回对应性别的人数(我的是在mysql front中操作):

    #DELIMITER $$CREATE PROCEDURE user_procedure(IN sex VARCHAR(2) character set utf8,OUT num INT)BEGIN

    SELECT id FROM users WHERE gender=sex;SELECT FOUND_ROWS() INTOnum;END#$$

    #DELIMITER ;

    如果大家用的navicat版本,应该改成是:

    DELIMITER $$CREATE PROCEDURE user_procedure(IN sex VARCHAR(2) character set utf8,OUT num INT)BEGIN

    SELECT id FROM users WHERE gender=sex;SELECT FOUND_ROWS() INTOnum;END$$

    DELIMITER ;

    上面记得中文字符字段,一定要设置编码:character set utf8,这里自己被坑了好久才觉悟过来...

    调用

    CALL user_procedure('女',@num);select @num;

    f9f468d0b16a7fd597dad9e0afb9fff9.png

    定义条件和处理

    条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤。

    语法如下:

    条件定义:DECLARE condition_name CONDITION FORcondition_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]sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code

    继续用users表举个例子吧!

    现在有数据如下:

    931f5a24cb5e742c3dd7aa6a8faff024.png

    (1)当没有进行条件处理的时候:

    #delimiter $$create procedureuser_insert()begin

    set @x=1;insert into users(id,gender,name) values(1,'男','常贵');set @x=2;insert into users(gender,name) values('女','大脚');set @x=3;END #$$

    2b0ab273cadc8d1e63b175e477d9e009.png

    上面的例子可以看出,当插入id=1,主键重复了,直接退出了,并没有执行余下的语句,所以@x的值为1。

    d7742a3f28c4d8f6397a7c094afc6d44.png

    (2)可以对主键重复进行处理:

    #delimiter $$create procedureuser_insert2()begin

    declare continue handler for sqlstate '23000' set @x2=1;set @x=1;insert into users(id,gender,name) values(3,'男','jack');set @x=2;insert into users(id,gender,name) values(1,'男','mary');set @x=3;end#$$

    #delimiter ;

    调用call user_insert2();

    a039eb4078e750ebbc1cd364dce6f9c7.png

    这次在调用存储过程的时候,并没有报错,而是在遇到主键重复的时候,会安装定义的continue去执行,所以继续向下执行。

    condition_value的值可以是通过declare定义的condition_name,可以是SQLSTATE的值或者mysql_error_code的值会在是SQLWARNING、NOT FOUND、SQLEXCEPTION,这个3个值是3种定义好的错误类别,分别代表不同的含义:

    SQLWARNING:是对所有以01开头的SQLSTATE代码的速记

    NOT FOUND是对所有以02开头的SQLSTATE代码的速记

    SQLEXCEPTION是对所有没有被SQLWARNING或者NOT FOUND捕获的SQLSTATE代码的速记。

    以上的declare continue handler for sqlstate '23000' set @x2=1;也可以用以下几种方式来写:

    #捕获mysql-error-code

    declarecontinue handler for 1062 set @x2=1;

    #事先定义condition_name

    declare duplicatekey conditionfor sqlstate '23000';

    declarecontinue handler for duplicatekey set @x2=1;

    #捕获sqlexception

    declarecontinue handler for sqlexception set @x2=1;

    流程控制

    mysql支持的流程控制有:IF、CASE、LOOP、LEAVE、ITERATE、REPEAT和WHILE语句。

    1.IF

    语法如下:

    IF search_condition THEN statement_list

    [ELSEIF search_condition THEN statement_list]...

    [ELSE statement_list]

    END IF

    举例:求两个数的最大值

    #DELIMITER $$CREATE PROCEDURE compare(IN n1 INT,IN n2 INT)BEGIN

    SET @res=0;IF n1 > n2 THEN

    SET @res=n1;

    ELSEIF n1= n2 THEN

    SET @res=n1;ELSE

    SET @res=n2;END IF;END#$$

    #DELIMITER ;

    调用后查询结果如下:

    2cb6ab5700743074161a794edcfb074f.png

    2.CASE语句

    语法如下:

    CASE case_value

    WHEN when_value THEN statement_list

    [WHEN when_value THEN statement_list]...

    [ELSE statement_list]

    END CASE

    或者:

    CASE

    WHEN when_value THEN statement_list

    [WHEN when_value THEN statement_list]...

    [ELSE statement_list]

    END CASE

    我们将以上例子使用case来实现:

    #DELIMITER $$CREATE PROCEDURE compare2(IN n1 INT,IN n2 INT)BEGIN

    SET @res=0;CASE

    WHEN n1>n2 THEN

    SET @res=n1;WHEN n1=n2 THEN

    SET @res=n1;ELSE

    SET @res=n2;END CASE;END#$$

    #DELIMITER ;

    测试:

    fc1d5fcd23e61b4be5d1e3f863118cd7.png

    3.LOOP和LEAVE语句

    LOOP可以实现简单的循环,通常和LEAVE一起使用,LOOP语法如下:

    [begin_label:]LOOP

    statement_list

    END LOOP[end_label]

    我们还是以users表为例,当前users表按照 id desc 数据如下:

    1af6d2249474858e7f5f55b61597bb66.png

    使用循环向里面插入100行数据:

    #DELIMITER $$CREATE PROCEDUREuserinset()BEGIN

    SET @x=0;

    ins: LOOP #标签为insSET @x=@x+1;IF @x=100 THENLEAVE ins; #当@x=100的时候,则退出循环END IF;INSERT INTO users(name,gender) values('周伯通', '男');ENDLOOP ins;END#$$

    #DELIMITER ;

    测试:

    call userinset();select count(1) from users;

    aa731aa2e40f2289702f4aedd0df16f7.png

    4.ITERATE语句

    必须在循环中使用,作用是跳过当前循环的剩下的语句,直接进入下一轮循环,相当于一些高级语言中的continue。

    只向表中插入奇数行:(仍以users为例):

    #delimiter $$CREATE PROCEDUREinserinfo()BEGIN

    set @x=1000103;

    ins: LOOPSET @x=@x+1;IF @x=1000113 THENLEAVE ins;

    ELSEIF mod(@x,2)=0 THENITERATE ins;END IF;INSERT INTO users(id,name) VALUES(@x,'乔峰');ENDLOOP ins;END#$$

    #delimiter ;

    测试:call inserinfo();

    e1e43a4e0aa661107520c3dd281a2dcd.png

    5.REPEAT语句

    有条件的循环控制语句,当满足条件的时候退出循环,语法如下:

    [begin_label:]REPEAT

    statement_list

    UNTIL search_condition

    END REPEAT [end_label]

    举例:再在上面例子中插入10行:

    #delimiter $$CREATE PROCEDUREinserinfo2()BEGIN

    DECLARE x INT DEFAULT 9;

    ins: REPEATSET x=x+1;INSERT INTO users(name,gender) VALUES(x,'保密');

    UNTIL x>18 ENDREPEAT;END#$$

    #delimiter ;

    调用:call inserinfo2();查看数据如下

    fb26b2b6dbda569b9953904d6391e02c.png

    6.WHILE语句

    WHILE是满足条件才执行。

    语法如下:

    [begin_lable:]WHILE search_condition DO

    statement_list

    END WHILE [end_label]

    以上的例子如果用while来实现如下:

    #delimiter $$CREATE PROCEDUREinserinfo3()BEGIN

    DECLARE x INT DEFAULT 9;

    ins:WHILE x<=18DOSET x=x+1;INSERT INTO users(name,gender) VALUES(x,'女');END WHILE;END#$$

    #delimiter ;

    测试:call inserinfo3();

    c269474686b131bb1e001a170c8eb710.png

    光标的使用

    在存储过程和函数中,可以使用光标对结果进行循环的处理,语法如下:

    声明光标:

    DECLARE cursor_name CURSOR FOR select_statement

    OPEN光标:

    OPEN cursor_name

    FETCH光标:

    FETCH cursor_name INTO var_name[,var_name]...

    CLOSE光标:

    CLOSE cursor_name

    举例:

    还是以users表为例,好吧。夜深人静。。不早了,未完待续....

    展开全文
  • 主要介绍了mysql数据存储过程参数实例详解,小编觉得挺不错的,这里分享给大家,供需要的朋友参考。
  • 本文实例讲述了mysql存储过程原理与使用方法。分享给大家供大家参考,具体如下: 存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql 存储过程的优点 #1. 用于...
  • 主要介绍了mysql存储过程之错误处理,结合实例形式详细分析了mysql存储过程错误处理相关原理、操作技巧与注意事项,需要的朋友可以参考下
  • mysql存储过程详解实例

    万次阅读 多人点赞 2016-12-23 10:23:17
    mysql存储过程详解实例

    mysql存储过程详解

    1.      存储过程简介

     

    我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

    一个存储过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

    存储过程通常有以下优点:

    (1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

    (2).存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。

    (3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。

    (4).存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。

    (5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

     

    2.      关于MySQL的存储过程

    存储过程是数据库存储的一个重要的功能,但是MySQL5.0以前并不支持存储过程,这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程,这样即可以大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。

    3.      MySQL存储过程的创建

     

    (1). 格式

    MySQL存储过程创建的格式:CREATE PROCEDURE 过程名 ([过程参数[,...]])
    [
    特性 ...] 过程体

    这里先举个例子:
       

    1. mysql> DELIMITER //  
    2. mysql> CREATE PROCEDURE proc1(OUT int)  
    3.     -> BEGIN 
    4.     -> SELECT COUNT(*) INTO FROM user;  
    5.     -> END 
    6.     -> //  
    7. mysql> DELIMITER 
     
    注:

    1)这里需要注意的是DELIMITER //DELIMITER ;两句,DELIMITER是分割符的意思,因为MySQL默认以";"为分隔符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事先用DELIMITER关键字申明当前段分隔符,这样MySQL才会将";"当做存储过程中的代码,不会执行这些代码,用完了之后要把分隔符还原。

    2)存储过程根据需要可能会有输入、输出、输入输出参数,这里有一个输出参数s,类型是int型,如果有多个参数用","分割开。

    3)过程体的开始与结束使用BEGINEND进行标识。

    这样,我们的一个MySQL存储过程就完成了,是不是很容易呢?看不懂也没关系,接下来,我们详细的讲解。

     

    (2). 声明分割符

     

    其实,关于声明分割符,上面的注解已经写得很清楚,不需要多说,只是稍微要注意一点的是:如果是用MySQLAdministrator管理工具时,可以直接创建,不再需要声明。

     

    (3). 参数

    MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:

    CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])

    IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值

    OUT 输出参数:该值可在存储过程内部被改变,并可返回

    INOUT 输入输出参数:调用时指定,并且可被改变和返回

    . IN参数例子

    创建:

    1. mysql DELIMITER //  
    2. mysql CREATE PROCEDURE demo_in_parameter(IN p_in int)  
    3. -> BEGIN   
    4. -> SELECT p_in;   
    5. -> SET p_in=2;   
    6. -> SELECT p_in;   
    7. -> END  
    8. -> //  
    9. mysql DELIMITER 


    执行结果
    :

    1. mysql SET @p_in=1;  
    2. mysql CALL demo_in_parameter(@p_in);  
    3. +------+  
    4. p_in |  
    5. +------+  
    6.      
    7. +------+  
    8.  
    9. +------+  
    10. p_in |  
    11. +------+  
    12.      
    13. +------+  
    14.  
    15. mysql> SELECT @p_in;  
    16. +-------+  
    17. @p_in |  
    18. +-------+  
    19.     |  
    20. +-------+  


    以上可以看出,
    p_in虽然在存储过程中被修改,但并不影响@p_id的值

     

    .OUT参数例子

    创建:

    1. mysql DELIMITER //  
    2. mysql CREATE PROCEDURE demo_out_parameter(OUT p_out int)  
    3. -> BEGIN 
    4. -> SELECT p_out;  
    5. -> SET p_out=2;  
    6. -> SELECT p_out;  
    7. -> END;  
    8. -> //  
    9. mysql DELIMITER 


    执行结果
    :

    1. mysql SET @p_out=1;  
    2. mysql CALL sp_demo_out_parameter(@p_out);  
    3. +-------+  
    4. p_out   
    5. +-------+  
    6. NULL    
    7. +-------+  
    8.  
    9. +-------+  
    10. p_out |  
    11. +-------+  
    12.       
    13. +-------+  
    14.  
    15. mysql> SELECT @p_out;  
    16. +-------+  
    17. p_out |  
    18. +-------+  
    19.     |  
    20. +-------+  


    . INOUT参数例子

    创建:

    1. mysql DELIMITER //   
    2. mysql CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)   
    3. -> BEGIN 
    4. -> SELECT p_inout;  
    5. -> SET p_inout=2;  
    6. -> SELECT p_inout;   
    7. -> END;  
    8. -> //   
    9. mysql DELIMITER 

     

     

    执行结果:
    1. mysql > SET @p_inout=1;  
    2. mysql > CALL demo_inout_parameter(@p_inout) ;  
    3. +---------+  
    4. p_inout |  
    5. +---------+  
    6.       |  
    7. +---------+  
    8.  
    9. +---------+  
    10. p_inout   
    11. +---------+  
    12.       |  
    13. +---------+  
    14.  
    15. mysql > SELECT @p_inout;  
    16. +----------+  
    17. @p_inout   
    18. +----------+  
    19.        |  
    20. +----------+ 

    (4). 变量

    . 变量定义

    DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

    其中,datatypeMySQL的数据类型,如:int, float, date, varchar(length)

    例如:

    1. DECLARE l_int int unsigned default 4000000;  
    2. DECLARE l_numeric number(8,2) DEFAULT 9.95;  
    3. DECLARE l_date date DEFAULT '1999-12-31';  
    4. DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
    5. DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded'  

     

     

    . 变量赋值

     SET 变量名 = 表达式值 [,variable_name = expression ...]

     

    . 用户变量

     

    . MySQL客户端使用用户变量

    1. mysql SELECT 'Hello World' into @x;  
    2. mysql SELECT @x;  
    3. +-------------+  
    4.   @x        |  
    5. +-------------+  
    6. Hello World |  
    7. +-------------+  
    8. mysql SET @y='Goodbye Cruel World';  
    9. mysql SELECT @y;  
    10. +---------------------+  
    11.     @y              |  
    12. +---------------------+  
    13. Goodbye Cruel World |  
    14. +---------------------+  
    15.  
    16. mysql SET @z=1+2+3;  
    17. mysql SELECT @z;  
    18. +------+  
    19. @z   |  
    20. +------+  
    21.    |  
    22. +------+  

    ⅱ. 在存储过程中使用用户变量

    1. mysql CREATE PROCEDURE GreetWorld( SELECT CONCAT(@greeting,World');  
    2. mysql SET @greeting='Hello';  
    3. mysql CALL GreetWorld( );  
    4. +----------------------------+  
    5. CONCAT(@greeting,World'|  
    6. +----------------------------+  
    7.  Hello World               |  
    8. +----------------------------+  

     

    . 在存储过程间传递全局范围的用户变量
    1. mysql> CREATE PROCEDURE p1()   SET @last_procedure='p1';  
    2. mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);  
    3. mysql> CALL p1( );  
    4. mysql> CALL p2( );  
    5. +-----------------------------------------------+  
    6. CONCAT('Last procedure was ',@last_proc  |  
    7. +-----------------------------------------------+  
    8. Last procedure was p1                         |  
    9. +-----------------------------------------------+  

     

     

    注意:

    用户变量名一般以@开头

    滥用用户变量会导致程序难以理解及管理

     

    (5). 注释

     

    MySQL存储过程可使用两种风格的注释

    双模杠:--

    该风格一般用于单行注释

    c风格: 一般用于多行注释

    例如:

     

    1. mysql DELIMITER //  
    2. mysql CREATE PROCEDURE proc1 --name存储过程名  
    3. -> (IN parameter1 INTEGER  
    4. -> BEGIN   
    5. -> DECLARE variable1 CHAR(10);   
    6. -> IF parameter1 17 THEN   
    7. -> SET variable1 'birds'  
    8. -> ELSE 
    9. -> SET variable1 'beasts'  
    10. -> END IF;   
    11. -> INSERT INTO table1 VALUES (variable1);  
    12. -> END   
    13. -> //  
    14. mysql DELIMITER ;  

     

    4.      MySQL存储过程的调用

    call和你过程名以及一个括号,括号里面根据需要,加入参数,参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。

    5.      MySQL存储过程的查询

    我们像知道一个数据库下面有那些表,我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程,是否也可以采用呢?答案是,我们可以查看某个数据库下面的存储过程,但是是令一钟方式。

    我们可以用

    select name from mysql.proc where db=’数据库名’;

    或者

    select routine_name from information_schema.routines where routine_schema='数据库名';

    或者

    show procedure status where db='数据库';

    进行查询。

    如果我们想知道,某个存储过程的详细,那我们又该怎么做呢?是不是也可以像操作表一样用describe 表名进行查看呢?

    答案是:我们可以查看存储过程的详细,但是需要用另一种方法:

    SHOW CREATE PROCEDURE 数据库.存储过程名;

    就可以查看当前存储过程的详细。

     

    6.      MySQL存储过程的修改

    ALTER PROCEDURE

    更改用CREATE PROCEDURE 建立的预先指定的存储过程,其不会影响相关存储过程或存储功能。

     

    7.      MySQL存储过程的删除

    删除一个存储过程比较简单,和删除表一样:

    DROP PROCEDURE

    MySQL的表格中删除一个或多个存储过程。

     

    8.      MySQL存储过程的控制语句

    (1). 变量作用域

    内部的变量在其作用域范围内享有更高的优先权,当执行到end。变量时,内部变量消失,此时已经在其作用域外,变量不再可见了,应为在存储
    过程外再也不能找到这个申明的变量,但是你可以通过out参数或者将其值指派
    给会话变量来保存其值。

     

     

    1. mysql DELIMITER //  
    2. mysql CREATE PROCEDURE proc3()  
    3.      -> begin 
    4.      -> declare x1 varchar(5) default 'outer';  
    5.      -> begin 
    6.      -> declare x1 varchar(5) default 'inner';  
    7.      -> select x1;  
    8.      -> end;  
    9.      -> select x1;  
    10.      -> end;  
    11.      -> //  
    12. mysql DELIMITER ;  

     

     (2). 条件语句

    . if-then -else语句

     

     

     

    1. mysql DELIMITER //  
    2. mysql CREATE PROCEDURE proc2(IN parameter int)  
    3.      -> begin 
    4.      -> declare var int;  
    5.      -> set var=parameter+1;  
    6.      -> if var=0 then 
    7.      -> insert into values(17);  
    8.      -> end if;  
    9.      -> if parameter=0 then 
    10.      -> update set s1=s1+1;  
    11.      -> else 
    12.      -> update set s1=s1+2;  
    13.      -> end if;  
    14.      -> end;  
    15.      -> //  
    16. mysql DELIMITER ;  


    . case语句: 

    1. mysql DELIMITER //  
    2. mysql CREATE PROCEDURE proc3 (in parameter int)  
    3.      -> begin 
    4.      -> declare var int;  
    5.      -> set var=parameter+1;  
    6.      -> case var  
    7.      -> when then   
    8.      -> insert into values(17);  
    9.      -> when then   
    10.      -> insert into values(18);  
    11.      -> else   
    12.      -> insert into values(19);  
    13.      -> end case;  
    14.      -> end;  
    15.      -> //  
    16. mysql DELIMITER 

     

    (3). 循环语句

    . while ···· end while

    1. mysql DELIMITER //  
    2. mysql CREATE PROCEDURE proc4()  
    3.      -> begin 
    4.      -> declare var int;  
    5.      -> set var=0;  
    6.      -> while var<6 do  
    7.      -> insert into values(var);  
    8.      -> set var=var+1;  
    9.      -> end while;  
    10.      -> end;  
    11.      -> //  
    12. mysql DELIMITER 

     

     

    . repeat···· end repeat

    它在执行操作后检查结果,而while则是执行前进行检查。

    1. mysql DELIMITER //  
    2. mysql CREATE PROCEDURE proc5 ()  
    3.      -> begin   
    4.      -> declare int;  
    5.      -> set v=0;  
    6.      -> repeat  
    7.      -> insert into values(v);  
    8.      -> set v=v+1;  
    9.      -> until v>=5  
    10.      -> end repeat;  
    11.      -> end;  
    12.      -> //  
    13. mysql DELIMITER ;  

     


    . loop ·····end loop:

    loop循环不需要初始条件,这点和while 循环相似,同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

    1. mysql DELIMITER //  
    2. mysql CREATE PROCEDURE proc6 ()  
    3.      -> begin 
    4.      -> declare int;  
    5.      -> set v=0;  
    6.      -> LOOP_LABLE:loop  
    7.      -> insert into values(v);  
    8.      -> set v=v+1;  
    9.      -> if >=5 then 
    10.      -> leave LOOP_LABLE;  
    11.      -> end if;  
    12.      -> end loop;  
    13.      -> end;  
    14.      -> //  
    15. mysql DELIMITER ;  

     

     

    . LABLES 标号:

    标号可以用在begin repeat while 或者loop 语句前,语句标号只能在合法的语句前面使用。可以跳出循环,使运行指令达到复合语句的最后一步。

     

    (4). ITERATE迭代

    . ITERATE:

    通过引用复合语句的标号,来从新开始复合语句

    1. mysql DELIMITER //  
    2. mysql CREATE PROCEDURE proc10 ()  
    3.      -> begin 
    4.      -> declare int;  
    5.      -> set v=0;  
    6.      -> LOOP_LABLE:loop  
    7.      -> if v=3 then   
    8.      -> set v=v+1;  
    9.      -> ITERATE LOOP_LABLE;  
    10.      -> end if;  
    11.      -> insert into values(v);  
    12.      -> set v=v+1;  
    13.      -> if v>=5 then 
    14.      -> leave LOOP_LABLE;  
    15.      -> end if;  
    16.      -> end loop;  
    17.      -> end;  
    18.      -> //  
    19. mysql DELIMITER 

     

     

    9.      MySQL存储过程的基本函数

     

    (1).字符串类

    CHARSET(str) //返回字串字符集
    CONCAT (string2 [,... ]) //
    连接字串
    INSTR (string ,substring ) //
    返回substring首次在string中出现的位置,不存在返回0
    LCASE (string2 ) //
    转换成小写

    LEFT (string2 ,length ) //
    string2中的左边起取length个字符
    LENGTH (string ) //string
    长度
    LOAD_FILE (file_name ) //
    从文件读取内容
    LOCATE (substring , string [,start_position ] )
     INSTR,但可指定开始位置
    LPAD (string2 ,length ,pad ) //
    重复用pad加在string开头,直到字串长度为length
    LTRIM (string2 ) //
    去除前端空格

    REPEAT (string2 ,count ) //
    重复count
    REPLACE (str ,search_str ,replace_str ) //
    str中用replace_str替换search_str
    RPAD (string2 ,length ,pad) //
    str后用pad补充,直到长度为
    length
    RTRIM (string2 ) //
    去除后端空格

    STRCMP (string1 ,string2 ) //
    逐字符比较两字串大小,
    SUBSTRING (str , position [,length ]) //
    strposition开始,length个字符
    ,
    注:mysql中处理字符串时,默认第一个字符下标为1,即参数position必须大于等于1
     

     

    1. mysql> select substring('abcd',0,2);  
    2. +-----------------------+  
    3. substring('abcd',0,2) |  
    4. +-----------------------+  
    5.                       |  
    6. +-----------------------+  
    7. row in set (0.00 sec)  
    8.  
    9. mysql> select substring('abcd',1,2);  
    10. +-----------------------+  
    11. substring('abcd',1,2) |  
    12. +-----------------------+  
    13.     ab                |  
    14. +-----------------------+  
    15. row in set (0.02 sec)  

    TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
    UCASE (string2 ) //
    转换成大写
    RIGHT(string2,length) //
    string2最后length个字符
    SPACE(count) //
    生成count个空格

    (2).数学类

    ABS (number2 ) //绝对值
    BIN (decimal_number ) //
    十进制转二进制
    CEILING (number2 ) //
    向上取整
    CONV(number2,from_base,to_base) //
    进制转换
    FLOOR (number2 ) //
    向下取整
    FORMAT (number,decimal_places ) //
    保留小数位数
    HEX (DecimalNumber ) //
    转十六进制
    注:HEX()中可传入字符串,则返回其ASC-11,如HEX('DEF')返回4142143
    也可以传入十进制整数,返回其十六进制编码,如HEX(25)返回
    19
    LEAST (number , number2 [,..]) //
    求最小值

    MOD (numerator ,denominator ) //
    求余
    POWER (number ,power ) //
    求指数
    RAND([seed]) //
    随机数
    ROUND (number [,decimals ]) //
    四舍五入,decimals为小数位数]

    注:返回类型并非均为整数,如:
    (1)
    默认变为整形值

    1. mysql> select round(1.23);  
    2. +-------------+  
    3. round(1.23) |  
    4. +-------------+  
    5.           |  
    6. +-------------+  
    7. row in set (0.00 sec)  
    8.  
    9. mysql> select round(1.56);  
    10. +-------------+  
    11. round(1.56) |  
    12. +-------------+  
    13.           |  
    14. +-------------+  
    15. row in set (0.00 sec) 



    (2)
    可以设定小数位数,返回浮点型数据

    1. mysql> select round(1.567,2);  
    2. +----------------+  
    3. round(1.567,2) |  
    4. +----------------+  
    5.           1.57 |  
    6. +----------------+  
    7. row in set (0.00 sec) 

    SIGN (number2 ) //

     

    (3).日期时间类

    ADDTIME (date2 ,time_interval ) //time_interval加到date2
    CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //
    转换时区
    CURRENT_DATE ( ) //
    当前日期
    CURRENT_TIME ( ) //
    当前时间
    CURRENT_TIMESTAMP ( ) //
    当前时间戳
    DATE (datetime ) //
    返回datetime的日期部分
    DATE_ADD (date2 , INTERVAL d_value d_type ) //
    date2中加上日期或时间
    DATE_FORMAT (datetime ,FormatCodes ) //
    使用formatcodes格式显示datetime
    DATE_SUB (date2 , INTERVAL d_value d_type ) //
    date2上减去一个时间
    DATEDIFF (date1 ,date2 ) //
    两个日期差
    DAY (date ) //
    返回日期的天
    DAYNAME (date ) //
    英文星期
    DAYOFWEEK (date ) //
    星期(1-7) ,1为星期天
    DAYOFYEAR (date ) //
    一年中的第几天
    EXTRACT (interval_name FROM date ) //
    date中提取日期的指定部分
    MAKEDATE (year ,day ) //
    给出年及年中的第几天,生成日期串
    MAKETIME (hour ,minute ,second ) //
    生成时间串
    MONTHNAME (date ) //
    英文月份名
    NOW ( ) //
    当前时间
    SEC_TO_TIME (seconds ) //
    秒数转成时间
    STR_TO_DATE (string ,format ) //
    字串转成时间,format格式显示
    TIMEDIFF (datetime1 ,datetime2 ) //
    两个时间差
    TIME_TO_SEC (time ) //
    时间转秒数]
    WEEK (date_time [,start_of_week ]) //
    第几周
    YEAR (datetime ) //
    年份
    DAYOFMONTH(datetime) //
    月的第几天
    HOUR(datetime) //
    小时
    LAST_DAY(date) //date
    的月的最后日期
    MICROSECOND(datetime) //
    微秒
    MONTH(datetime) //

    MINUTE(datetime) //
    返回符号,正负或0
    SQRT(number2) //
    开平方
    展开全文
  • 主要介绍了mysql存储过程之if语句用法,结合实例形式详细分析了mysql存储过程中if语句相关原理、使用技巧与操作注意事项,需要的朋友可以参考下
  • MySQL——存储过程详解实例分析

    千次阅读 2019-05-11 16:27:49
    目录 一、储存过程简介 1、什么是存储过程 2、存储过程优缺点 3、存储过程入门程序 4、在idea中如何调用储存过程?...三、存储过程实例讲解 1、案例1:取消订单 2、案例2:根据商品分类获...

     

    目录

    一、储存过程简介

    1、什么是存储过程

    2、存储过程优缺点

    3、存储过程入门程序

    4、在idea中如何调用储存过程?

     二、存储过程编程

    1、存储过程的变量

     2、存储过程中的参数

     3、选择结构if

    4、分支结构case

    5、3个循环结构

    6、存储过程的异常处理

     7、MySQL游标

    三、存储过程实例讲解

    1、案例1:取消订单

    2、案例2:根据商品分类获取商品详情


    一、储存过程简介

    1、什么是存储过程

    存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
              存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。

    2、存储过程优缺点

    • 优点

    1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而 一般SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度

    2.存储过程可以重复使用,可减少数据库开发人员的工作量(可以忽略) 

    3.安全性高,可设定只有某此用户才具有对指定存储过程的使用权

    4. 减少网络通信量、 Java与mysql的IO交互。调用一个行数不多的存储过程与直接调用SQL 语句的网络通信量可能不会有很大的差别,可是如果存储过程包含上百行SQL 语句,那么其性能绝对比一条一条的调用SQL 语句要高得多。 

    • 缺点

    1、不可移植性,每种数据库的内部编程语法都不太相同,当你的系统需要兼容多种数据库时最好不要用存储过程。
    2、学习成本高,没有java代码debug方便
    3、业务逻辑多处存在,采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理,这种架构会增加一些系统维护和调试成本。业务逻辑集中管理会更易于维护与调试,你很难做到业务逻辑都放在存储过程里,比如关于一些客户输入数当据的简单校验,会话数据的校验,应用服务器缓存数据的校验。
    4、 运行速度:大多数高级的数据库系统都有statement   cache的,所以编译sql的花费没什么影响。但是执行存储过程要比直接执行sql花费更多(检查权限等),所以对于很简单的sql,存储过程没有什么优势。
    5、存储占物理内存,如果使用大量存储过程,那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外,如果您在存储过程中过度使用大量逻辑操作,则CPU使用率也会增加,因为数据库服务器的设计不当于逻辑运算。

    3、存储过程入门程序

    DELIMITER //
     CREATE PROCEDURE GetAllProducts()
       BEGIN
       SELECT *  FROM products;
       END //
    DELIMITER 
    

    对上述存储过程的详解:

    • 第一个命令是DELIMITER //,它与存储过程语法无关。 DELIMITER语句将标准分隔符 - 分号(;)更改为:// 在这种情况下,分隔符从分号(;)更改为双斜杠//为什么我们必须更改分隔符? 因为我们想将存储过程作为整体传递给服务器,而不是让mysql工具一次解释每个语句。 在END关键字之后,使用分隔符//来指示存储过程的结束。 最后一个命令(DELIMITER;)将分隔符更改回分号(;)。
    • 使用CREATE PROCEDURE语句创建一个新的存储过程。在CREATE PROCEDURE语句之后指定存储过程的名称。在这个示例中,存储过程的名称为:GetAllProducts,并把括号放在存储过程的名字之后。
    • BEGINEND之间的部分称为存储过程的主体。将声明性SQL语句放在主体中以处理业务逻辑。 在这个存储过程中,我们使用一个简单的SELECT语句来查询products表中的数据。

    要调用存储过程,可以使用以下SQL命令:

     CALL  GetAllProducts();

    4、在idea中如何调用储存过程?

    <select id="getProductByCid" resultType="com.xmcc.redis01.vo.ProductVo">
      <!--该存储过程请阅读本文最后的案例分析2-->
            call  find_category_id( #{cid,mode=IN,jdbcType=INTEGER}  )
        </select>

    <select id="getProductByCid" resultType="com.xmcc.redis01.vo.ProductVo">

            call  find_category_id( #{cid,mode=IN,jdbcType=INTEGER} ,

    #{id,mode=OUT,jdbcType=INTEGER} )
        </select>

    其他命令: 

    查看已有的procedure
    show procedure status \G

    删除存储过程
    drop procedure 名字

     二、存储过程编程

    1、存储过程的变量

    声明变量:要在存储过程中声明一个变量,可以使用DECLARE语句

    DECLARE variable_name datatype(size)  DEFAULT default_value;

    • 首先,在DECLARE关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。
    • 其次,指定变量的数据类型及其大小。变量可以有任何MySQL数据类型,如INTVARCHARDATETIME等。
    • 第三,当声明一个变量时,它的初始值为NULL。但是可以使用DEFAULT关键字为变量分配默认值。
    • 如:DECLARE test INT;

    分配变量值: 要为变量分配一个值,可以使用SET语句,或者使用SELECT INTO语句将查询的结果分配给一个变量

    DECLARE total_count INT DEFAULT 0;

    SET total_count := 10;

    或者:

    SELECT COUNT(*) INTO total_count  FROM products

     变量范围(作用域)

    一个变量有自己的范围(作用域),它用来定义它的生命周期。 如果在存储过程中声明一个变量,那么当达到存储过程的END语句时,它将超出范围,因此在其它代码块中无法访问。

    如果您在BEGIN END块内声明一个变量,那么如果达到END,它将超出范围。 可以在不同的作用域中声明具有相同名称的两个或多个变量,因为变量仅在自己的作用域中有效。 但是,在不同范围内声明具有相同名称的变量不是很好的编程习惯。

    @符号开头的变量是会话变量。直到会话结束前它可用和可访问。

     :=与=的区别:

    1.= 
    只有在set和update时才是和:=一样,赋值的作用,其它都是等于的作用。鉴于此,用变量实现行号时,必须用:=
    2.:= 
    不只在set和update时时赋值的作用,在select也是赋值的作用。

    如果明白了=和:=的区别,那么也就理解了下边的现象。 
    @num:=@num+1,:=是赋值的作用,所以,先执行@num+1,然后再赋值给@num,所以能正确实现行号的作用。 
    @num=@num+1,此时=是等于的作用,@num不等于@num+1,所以始终返回0,如果改为@num=@num,始终返回1了。mysql数据库中,用1表示真,0表示假。

     2、存储过程中的参数

    在MySQL中,参数有三种模式:INOUTINOUT

    • IN - 是默认模式。(可写可不写) 在存储过程中定义IN参数时,调用程序必须将参数传递给存储过程。 另外,IN参数的值被保护。这意味着即使在存储过程中更改了IN参数的值,在存储过程结束后仍保留其原始值。换句话说,存储过程只使用IN参数的副本。
    • OUT - 可以在存储过程中更改OUT参数的值,并将其更改后新值传递回调用程序。请注意,存储过程在启动时无法访问OUT参数的初始值。
    • INOUT - INOUT参数是INOUT参数的组合。这意味着调用程序可以传递参数,并且存储过程可以修改INOUT参数并将新值传递回调用程序。  

    #需要注意类型是写在后面的

    CREATE PROCEDURE my_test5(IN i INT,  OUT message VARCHAR(20))

     3、选择结构if

    因为很类似于Java,此处直接放实例了,详解介绍可以参考:MySQL教程——存储过程的if语句

    delimiter $$
    create procedure p4()
    begin
    declare age int default 18;
    if age >=18 then
    select concat('已经成年');
    else
    select concat('未成年');
    end if;#(只是结束if 在存储过程中的if、循环语句等基本都需要结束)
    
    end $$
    
    
    参数的传递
    #求矩形的面积
    delimiter $$
    create procedure p5(width int,height int)
    begin
    	select concat('你的面积是',width*height) as area;
    	if width>height then
    	select '胖';
    	elseif width < height then
    	select '瘦';
        else
        select '方';
       end if;
    end $$

    4、分支结构case

    case……when ~then ~
    
    delimiter $$
    create procedure p9()
    begin
    declare pos int default 0;
    case pos  #相当于switch()
    	when 1 then select 1;#相当于case
    	when 2 then select 2;
    	when 3 then select 3;
    	else select 'ojbk'; #相当于default
    end case;
    end $$
    
    带参数
    delimiter $$
    create procedure p10(num int)
    begin
    case num
    when 1 then select 1;
    when 2 then select 2;
    when 3 then select 3;
    else select 'ojbk';
    end case;
    end $$
    注意 参考java中的switch\case语句

    5、3个循环结构

    • while ~~~do
    ----》实例1:
    #求1-100之和
    delimiter $
    create procedure p6()
    begin
    	declare total int default 0;
    	declare num int default 0;
    while num<=100 do
    		set total := total +num;
    		set num = num+1;
    end while;
    select total;
    end$
    
    ----》实例2:
    增强: 求1~N的何 N由用户传入
    create  procedure p7(in n int)
    begin
    	declare total int default 0;
    	declare num  int default 0;
    while num<=n do	
    	set total = total +num;
    set num = num+1;
    end while;
    
    select total;
    end$
    
    
    ----》实例3:
    增强:out
    create  procedure p8(in n int,out total int)
    begin
    	#设置初始值 如果不设置初始值 结果为NULL
    	declare num  int default 0;
    	set total = 0;
    while num<=n do	
    	set total = total +num;
    	set num = num+1;
    end while;
    end$
    
    #参数一:直接输入 参数二 定义一个变量 接受total的结果
    call p8(100,@sum);
    
    #查询接受参数
    select @sum$
    

    repeat

    语法结构
    repeat
    		sql1
    		sql2
    until(直到) 条件 
    
    repeat和until之间就是循环体
    
    create procedure p11()
    begin
    declare i int default 0;
    declare total int default 0;
    repeat #开始循环
    	set i = i+1;
    	set total = total+i;
    until i>=100#当i大于等于100就执行下面的 结束循环
    end repeat;
    select total;
    end$
    

     loop

    #loop 用法  
    DELIMITER //
    CREATE PROCEDURE my_test4()
    BEGIN
         DECLARE i INT DEFAULT 100;
         loop_label: LOOP  #相当于java的标记 用于多重循环,loop表示开始循环 也可以不用标记 这儿一起举例了
            INSERT INTO xmcc_product VALUES(CONCAT('1000',i),CONCAT('手机',i),i*10,i*100);
           SET i=i+1;
            if i>=105 THEN  #当i大于105的时候 执行下面的
                leave loop_label; #离开标记处的循环
            END IF; #结束if 再次提示 注意
        END LOOP; #离开循环 语法
        END ;// #简写 delimiter
    
     #调用 
     CALL my_test4();
    

    6、存储过程的异常处理

    语法:

    01)当sql出现异常的时候跳出存储过程并设置值为xxx
        Declare exit handler for sqlException set …..

    02)当sql出现异常的时候继续存储过程并设置值为xxx
        Declare continue handler for sqlException set …..

    -- continue继续  exit 退出  HANDLER执行处理器    SQLEXCEPTION:sql异常

    编程实例:(结合事务使用)

    #存储过程异常处理
    DELIMITER//
    CREATE PROCEDURE my_test7()
    BEGIN
    DECLARE i INT DEFAULT 0;
    #发生SQL异常时,程序继续,并设置i=-1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET i:=-1;
    START TRANSACTION;#开启事务
    UPDATE xmcc_product SET p_name='苹果' WHERE p_id=10000;
    INSERT INTO xmcc_product VALUES(10000,'主键冲突',10,100); 
    IF i=-1 THEN
    SELECT '出现异常,事务回滚';
    ROLLBACK; #回滚
    ELSE
    COMMIT; #提交
    END IF;
    END//
    DELIMITER ;
    #调用 
    CALL my_test7();
    #查看结果
    SELECT * FROM xmcc_product

     7、MySQL游标

    游标介绍:(处理存储过程中的结果集)

    1)有数据缓冲的思想:游标的设计是一种数据缓冲区的思想,用来存放SQL语句执行的结果。 
    2)先有数据基础:游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。
    3)类似于指针:游标类似于指向数据结构堆栈中的指针,用来pop出所指向的数据,并且只能每次取一个

    使用语法:

    1)declare 声明;(游标声明必须在变量声明之后。如果在变量声明之前声明游标,MySQL将会发出一个错误。游标必须始终与SELECT语句相关联。)
        declare 游标名 cursor for  查询语句

    2)open 打开;
        open 游标名

    3)fetch 取值;
            fetch 游标名 into val1,val2,val3……

    4)close 关闭;
            close 游标名

    实用实例:(该实例非常重要,请重点理解)

    DELIMITER //
    CREATE PROCEDURE my_test8()
    BEGIN
    DECLARE result VARCHAR(100) DEFAULT '';-- 定义一个result 来存储商品名称的拼接
    DECLARE product_name VARCHAR(10);-- 定义一个变量来接收每次游标的商品名称
    DECLARE done INT DEFAULT FALSE; -- 定义done变量 默认值为false
    -- 查询所有的商品名称放入游标中 CURSOR 表示该变量为游标类型
    DECLARE curl CURSOR FOR SELECT p_name FROM xmcc_product;
    -- 游标遍历结束 会出现not found 设置done的值为true
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
    OPEN curl;-- 打开游标
    WHILE(NOT done) DO
    FETCH curl INTO product_name; -- 弹出游标的一条数据赋值给product_name
    SET result:=CONCAT(result,',',product_name);-- 拼接字符串
    END WHILE;
    CLOSE curl;-- 关闭游标 就像java中需要关闭resultset一样
    SELECT result;-- 打印出结果
    END;//
    #调用
    CALL my_test8();

    三、存储过程实例讲解

    1、案例1:取消订单

    案例:当取消订单的时候
        1.修改订单状态为取消(1)
        2.对订单项里面的商品增加库存

     存储过程编写及分析(分析请看注解)

    #编写取消订单的存储过程
    DELIMITER $$
    #输入参数为订单order_id 输出参数为result 1 代表存储过程成功 -1代表存储过程出现异常
    CREATE PROCEDURE cancel_order(IN order_id VARCHAR(30),OUT result INT)
    BEGIN 
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result:=-1;#如果出现异常设置返回值为-1
    START TRANSACTION;#开启事务
    SET result:=1;#设置初始值 这里设置为1
    UPDATE xmcc_order SET o_statu=1 WHERE o_id=order_id; #修改订单状态
         BEGIN 
    	  DECLARE done INT DEFAULT TRUE;#定义变量done用来判断
    	  DECLARE product_id VARCHAR(30); #定义变量 来接收游标的商品id
    	  DECLARE quantity_1 INT; #定义变量来接收游标的商品数量
    	  #定义游标 存储根据订单id查询到订单项中的商品id与数量
    	  DECLARE cur CURSOR FOR SELECT p_id,quantity FROM xmcc_orderdetail WHERE o_id=order_id;
    	  #当游标循环结束 设置done的值为false
    	  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done:=FALSE;
    	  OPEN cur;#打开游标
    	  WHILE done DO #循环
    	  FETCH cur INTO product_id,quantity_1;#将游标的值设置到变量中
    	  IF done THEN #判断 不然会多修改一次
    	  UPDATE xmcc_product SET p_stock=p_stock+quantity_1 WHERE p_id=product_id;
    	  END IF;
    	  END WHILE;
    	  CLOSE cur;
         END ;
     IF result=1 THEN #没有出现异常就提交
     COMMIT;
     ELSE 
     ROLLBACK;
     END IF;
     END ; $$
     #测试
    CALL cancel_order('20001',@result);
    #查看结果
    SELECT @result

    2、案例2:根据商品分类获取商品详情

    项目资料背景:

    数据库资料:
        1、产品分类表:xmcc_category。数据内容结构(父—子结构):A(a1,a2,a3)B(b1,b2,b3)
        2、产品详情表:xmcc_product 有对应的category_id

    项目需求:
        根据category_id获取到产品详情的集合。

    逻辑分析:
        如:查询的category_id=A    
        1)根据category_id=A  从表xmcc_category获取到所有的分类id集合list1(A,a1,a2,a3)
        2)根据list1从表xmcc_product 获取对应的产品详情list2<xmcc_product >

    存储过程编程: 

    DELIMITER //
    #传入参数category_id 分类id
    CREATE PROCEDURE find_category_id(IN category_id INT)
    BEGIN
    DROP TABLE IF EXISTS tmp_id;
    #创建临时表 用来存储所有的id 临时表在链接结束会自动删除
    CREATE TEMPORARY TABLE  tmp_id(id INT);
    #清空该表
    TRUNCATE TABLE tmp_id;
    #临时表 用来存储所有的商品信息;
    #根据分类查询商品的时候,商品展示列表只需要这几个字段就可以了,详情的时候才是所有字段 
    DROP TABLE IF EXISTS tmp_product;
    CREATE TEMPORARY TABLE  tmp_product(SELECT id,NAME,subtitle,main_image,price FROM xmcc_product WHERE 1=2);
    TRUNCATE TABLE tmp_id;
    #调用另外的存储过程
    CALL recursive_find_id(category_id);
      BEGIN
      #设置循环条件
       DECLARE done INT DEFAULT '0';
       #设置cid来接收游标中弹出的id
       DECLARE cid INT DEFAULT '0';
       #递归过程将所有的id放在临时表中 现在查出放在游标ids中
       DECLARE ids CURSOR FOR SELECT id FROM tmp_id;
       #当游标遍历结束 done=1
       DECLARE CONTINUE HANDLER  FOR NOT FOUND SET done=1;
       #打开游标
       OPEN ids;
       #弹出游标一个值赋给cid
       FETCH ids INTO cid;
       WHILE (done=0) DO
       #根据分类查找到商品 ,插入数据到商品临时表中
       INSERT INTO tmp_product  SELECT id,NAME,subtitle,main_image,price FROM xmcc_product xp WHERE xp.category_id=cid and xp.status=1;
       FETCH ids INTO cid;
       END WHILE;
      END;
      #返回查询到的商品结果集
    SELECT * FROM tmp_product;
    END ;//
    
    
    DELIMITER //
    CREATE PROCEDURE recursive_find_id(IN category_id INT)
    BEGIN 
    #定义id接收每次查询到的分类id
    DECLARE cid INT;
    #定义done为0TRUE继续循环
    DECLARE done INT DEFAULT TRUE;
    #定义ids游标来获得parent_id为传入的id的集合
    DECLARE ids CURSOR FOR SELECT id FROM xmcc_category WHERE parent_id=category_id;
    #游标循环结束 出现not found  done=1循环结束
    DECLARE CONTINUE HANDLER  FOR NOT FOUND SET done=FALSE;
    #mysql 递归的时候 需要设置深度
    SET @@max_sp_recursion_depth = 10;
    #将id插入临时表中
    INSERT INTO tmp_id VALUES(category_id);
    OPEN ids;#打开游标
    #将查询到的游标结果 弹出赋值给id 每次弹出一个
    FETCH ids INTO cid;
    WHILE done DO
    #递归调用当前存储过程
    CALL recursive_find_id(cid);
    FETCH ids INTO cid;
    END WHILE;
    CLOSE ids;
    END;//

     

    展开全文
  • 本文实例讲述了mysql存储过程之case语句用法。分享给大家供大家参考,具体如下: 除了if语句,mysql提供了一个替代的条件语句CASE。 mysql的 CASE语句使代码更加可读和高效。废话不多说,我们先来看下简单case语句的...
  • 例1、一个简单存储过程游标实例 代码如下: DELIMITER $$ DROP PROCEDURE IF EXISTS getUserInfo $$CREATE PROCEDURE getUserInfo(in date_day datetime)— — 实例存储过程名为:getUserInfo— 参数为:date_day...
  • 详细实例全⾯解析SQL存储过程存储过程(Stored Procedure),是⼀组为了完成特定功能的SQL 语句,类似⼀门程序设计语⾔,也包括了数据类 、流程控制、输⼊和输出和它⾃⼰的函数库。存储过程可以说是⼀个记录集,它是由...

    详细实例全⾯解析SQL存储过程

    存储过程(Stored Procedure),是⼀组为了完成特定功能的SQL 语句,类似⼀门程序设计

    语⾔,也包括了数据类 、流程控制、输⼊和输出和它⾃⼰的函数库。存储过程可以

    说是⼀个记录集,它是由⼀些T-SQL语句组成的代码块,这些T-SQL语句代码像⼀个

    ⽅法⼀样实现⼀些功能 (对单表或多表的增删改查),然后再给这个代码块取⼀个名

    字,在⽤到这个功能的时候调⽤他就⾏了。不过SQL存储过程对于⼀些初学者来说还

    是⽐较抽象难理解的,因此本⽂将由浅⾄深地剖析SQL存储过程,帮助你学习它。

    存储过程的优点

    1.存储过程只在创造时进⾏编译,以后每次执⾏存储过程都不需再重新编译,⽽⼀般

    SQL语句每执⾏⼀次就编译⼀次,所以使⽤存储过程可提⾼数据库执⾏速度,效率要⽐

    T-SQL语句⾼。

    2.当对数据库进⾏复杂操作时,可将此复杂操作⽤存储过程封装起来与数据库提供的

    事务处理结合⼀起使⽤。

    3、⼀个存储过程在程序在⽹络中交互时可以替代⼤堆的T-SQL语句,所以也能降低⽹

    络的通信量,提⾼通信速率。

    4 .存储过程可以重复使⽤,可减少数据库开发⼈员的⼯作量。

    5.安全性⾼,可设定只有某些⽤户才具有对指定存储过程的使⽤权

    存储过程基本语法

    创建存储过程

    CREATE PROC [ EDURE ] procedure_name [ ; number ]

    [ { @parameter data_type }

    [ ARYING ] [ = default ] [ OUTPUT ]

    ] [ ,...n ]

    [ WITH

    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

    [ FOR REPLICATION ]

    AS sql_statement [ ...n ]

    调⽤存储过程

    EXECUTE Procedure_name '' --存储过程如果有参数,后⾯加参数格式为:@参数名

    删除存储过程

    drop procedure procedure_name --在存储过程中 调⽤另外⼀个存储过程,

    创建存储过程的参数

    1.procedure_name :存储过程的名称,在前⾯加#为局部临时存储过程,加##为全局

    临时存储过程。

    2.; number :是可选的整数,⽤来对同名的过程分组,以便⽤⼀条 DROP

    PROCEDURE 语句即可将同组的过程⼀起除去。例如,名为 orders 的应⽤程序使⽤的

    过程可以命名为 order roc;1、order roc;2 等。DROP PROCEDURE order roc 语句将除

    去整个组。如果名称中包含定界标识符,则数字不应包含在标识符中,只应在

    rocedure_name 前后使⽤适当的定界符。

    3.@parameter :存储过程的参数。可以有⼀个或多个。⽤户必须在执⾏过程时提供每

    个所声明参数的值 (除⾮定义了该参数的默认值)。存储过程最多可以有 2.100 个参

    数。

    使⽤ @ 符号作为第⼀个字符来指定参数名称。参数名称必须符合标识符的规则。每

    个过程的参数仅⽤于该过程本⾝;相同的参数名称可以⽤在其它过程中。默认情况

    下,参数只能代替常量,⽽不能⽤于代替表名、列名或其它数据库对象的名称。有关

    更多信息,请参见 EXECUTE 。

    4.data_type :参数的数据类 。所有数据类 (包括 text 、ntext 和 image )均可以⽤

    作存储过程的参数。不过,cursor 数据类 只能⽤于 OUTPUT 参数。如果指定的数据

    类 为 cursor ,也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提

    供的数据类 及其语法的更多信息,请参见数据类 。

    说明 对于可以是 cursor 数据类 的输出参数,没有最⼤数⽬的限制。

    5.VARYI G :指定作为输出参数⽀持的结果集 (由存储过程动态构造,内容可以变

    化)。仅适⽤于游标参数。

    6.default : 参数的默认值。如果定义了默认值,不必指定该参数的值即可执⾏过程。

    默认值必须是常量或 NULL 。如果过程将对该参数使⽤ LIKE 关键字,那么默认值中

    可以包含通配符 (%、_ 、[] 和 [^] )。

    7.OUTPUT :表明参数是返回参数。该选项的值可以返回给 EXEC[UTE] 。使⽤

    OUTPUT 参数可将信息返回给调⽤过程。Text

    展开全文
  • MySQL数据库存储过程讲解与实例

    万次阅读 多人点赞 2018-06-03 00:48:17
    SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。...
  • mysql存储过程讲解

    2018-08-06 14:27:15
    mysql存储过程结束讲解的ppt,个人觉得讲的十分清晰,值得大家一看
  • 1 用mysql客户端登入 2 选择数据库 mysql>use test 3 查询当前数据库有哪些存储过程 mysql>show procedure status where Db=’test’ 4 创建一个简单的存储过程 mysql>create procedure hi() select ‘hello’;...
  • 存储程序是被存储在服务器中的组合SQL语句,经编译创建并保存在数据库中,用户可通过...基本语法格式(免费学习视频教程分享:mysql视频教程)实例一:计算消费折扣实例二:While..Do写数据提供一张数据表存储程序根...
  • 用navicat创建mysql存储过程的示例,这个存储过程比较简单,利用了游标,循环,插入数据,更新数据等行为。代码如下:BEGINDECLARE FNAME1 VARCHAR(50);DECLARE FPATH1 VARCHAR(200) DEFAULT '0';DECLARE FSIZE1 INT...
  • 本文实例讲述了mysql存储过程之游标(DECLARE)原理与用法。分享给大家供大家参考,具体如下: 我们在处理存储过程中的结果集时,可以使用游标,因为游标允许我们迭代查询返回的一组行,并相应地处理每行。mysql的...
  • mysql存储过程、游标 、事务实例详解 下面是自己曾经编写过的mysql数据库存储过程,留作存档,以后用到的时候拿来参考。 其中,涉及到了存储过程、游标(双层循环)、事务。 【说明】:代码中的注释只针对当时业务...
  • 主要介绍了Mysql存储java对象实例详解的相关资料,需要的朋友可以参考下
  • 基于Php mysql存储过程详解实例一:无参的存储过程$conn = mysql_connect('localhost','root','root') or die ("数据连接错误!!!");mysql_select_db('test',$conn);$sql = "create procedure myproce()beginINSERT...
  • 无详细内容 MySQL mysqlmysql DELIMITER //mysql CREATE FUNCTION myProc (cost DECIMAL(10,2)) - RETURNS DECIMAL(10,2) - - SQL SECURITY DEFINER - - tax: BEGIN - DECLARE order_tax DECIMAL(10,2); - SET order...
  • 本文实例讲述了mysql存储过程原理与用法。分享给大家供大家参考,具体如下: 本文内容: 什么是存储过程 存储过程的创建 存储过程的使用 查看存储过程 修改存储过程 删除存储过程 首发日期:2018-04-17 什么...
  • MySQL存储过程使用详解

    千次阅读 2017-05-16 20:06:13
    【1】存储过程简介存储过程中的控制语句 【http://blog.csdn.net/J080624/article/details/72353508】不同类型参数存储过程示例 【http://blog.csdn.net/j080624/article/details/55096307】MyBatis调用存储过程 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 23,029
精华内容 9,211
关键字:

mysql存储过程实例详解

mysql 订阅