精华内容
下载资源
问答
  • delimiter // > create procedure all() -> begin -> select* from student; -> end -> //
  • Mysql存储过程编写

    千次阅读 2019-05-20 09:58:22
    Mysql存储过程编写存储过程编写的模板:Create PROCEDURE PROCEDUREName (IN para mint,……)BeginDeclare varname type;语句;End;以上就是存储过程的编写模板。其中,type可以是表的任意类型,比如:varchar,...

    Mysql存储过程编写
    存储过程编写的模板:
    Create PROCEDURE PROCEDUREName (IN para mint,……)
    Begin
    Declare varname type;
    语句;
    End;
    以上就是存储过程的编写模板。
    其中,type可以是表中的任意类型,比如:varchar,bigint,int,decimal,longtext等等类型。
    游标的声明是:
    Declare cursorName cursor from select语句。
    Declare continue handler for not found set varName = 1;
    varName在使用的时候,需要进行声明,这个是表明如果游标没有数据了,varName赋值为1时表示没有值。
    Open cursorName;表示打开游标。
    CLOSE cursorName;表示关闭游标。
    FETCH cursorName into varlist;表示向游标中取出值。
    If条件语句:
    1、种情况
    If 条件 then
    满足条件时执行的语句
    End if;
    2、种情况
    If 条件 then
    满足条件的执行的语句
    Else
    不满足条件的执行的语句
    End if;
    循环语句:
    Out_loop:LOOP

    END LOOP out_loop;
    这个是LOOP循环,其中out_loop表示的是LOOP的循环标签,类似于汇编的标签。
    其中结束LOOP循环的语句是:
    LEAVE out_loop;out_loop表示LOOPd的标签
    例子:
    create PROCEDURE selectExtratUnit()
    BEGIN
    DECLARE id BIGINT;
    DECLARE na LONGTEXT;
    DECLARE linkName LONGTEXT;
    DECLARE notfound INT;
    DECLARE cursor_avgScore CURSOR for select summary_id,text2 from edoc_summary_extend_send_sj where text2 is not null and text2 <> '';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET notfound = 1;
    OPEN cursor_avgScore;
    out_loop:LOOP
    if notfound = 1 THEN
    LEAVE out_loop;
    end if;
    FETCH cursor_avgScore into id,na;
    select group_concat(org_name) into linkName from trans_org_sj where org_id in (
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(na,'|',help_topic_id+1),'|',-1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH(na)-LENGTH(REPLACE(na,"|",''))+1 );
    INSERT into extrat_table(id,orgname) VALUES(id,linkName);
    END LOOP out_loop;
    CLOSE cursor_avgScore;
    end;

    注意:
    在创建存储过程的时候,选用的用户端的不同,会导致存储过程在语法对的情况下,会有一些错误。最好使用Navicat和mysql自带的黑窗口。如果使用mysql自带的黑窗口,需要在编写存储过程的时候要使用DELIMITER //命令,执行完成后,在编写存储过程,编写完成后,使用//来表示命令的结束。

    转载于:https://blog.51cto.com/xiaoshunzi/2397146

    展开全文
  • MySQL存储过程编写总结文档,包括循环结构样式,创建临时表,删除数据,MySQL分页,动态存储过程编写
  • 一、存储过程函数详解 CREATE PROCEDURE : 声明存储过程 delete_matches:方法名用来调用 IN uid INTEGER :传参uid,INTEGER 类型。 IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量) OUT ...

    一、存储过程函数详解

    CREATE  PROCEDURE : 声明存储过程

    delete_matches:方法名用来调用

    IN uid INTEGER :传参uid,INTEGER 类型。

    • IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
    • OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
    • INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

    BEGIN :开始

    语句块{

    }

    END:结束

    二、编写存储过程

    CREATE  PROCEDURE  delete_matches (IN uid INTEGER)
    BEGIN
    
    DELETE FROM da_city_stlstj  WHERE id = uid;
    
    END

    三、调用测试

    CALL delete_matches(1); 

    成功结果如下↓

    展开全文
  • MySQL中存储过程、游标和存储函数

    千次阅读 2018-12-18 23:49:23
    MySQL中存储过程 首先来看两个问题: 1.什么是存储过程存储过程(Stored Procedure)是数据库系统,一组为了完成特定功能的SQL语句集,经编译后存储在数据库,用户通过指定存储过程的名字并给出参数...

    MySQL中的存储过程

    首先来看两个问题:

    1.什么是存储过程?

    存储过程(Stored Procedure)是在数据库系统中,一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程有参数的话)来执行它。

    2.为什么要使用存储过程?

    《MySQL必知必会》这本书中给出了如下几条主要理由:

    1)通过把处理封装在容易使用的单元中,简化复杂的操作;

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

    3)简化对变动的管理。如果表名、列名或业务逻辑(或别的内容) 有变化,只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误(无意识的或别的原因所导致的数据讹误)的机会;

    4)提高性能。存储过程只在创建时进行编译,以后每次执行存储过程都不需要再重新编译,而一般SQL语句每执行一次就编译一次,因此使用存储过程比使用单独的SQL语句要快;

    5)存在一些只能用在单个请求中的MySQL元素和特性,存储过程可以使用它们来编写功能更强更灵活的代码。

    总的来说,使用存储过程有3个主要的好处,即简单、安全、高性能。同时,我们也要知道存储过程的一些缺陷:存储过程的编写比基本SQL语句复杂,编写存储过程需要更高的技能,更丰富的经验;我们可能没有创建存储过程的安全访问权限,许多数据库管理员限制存储过程的创建权限,允许用户使用存储过程,但不允许他们创建存储过程。尽管有这些缺陷,但存储过程还是非常有用的。

    下面进入正题:

    存储过程中变量的声明与分配

    在存储过程中,我们通常用已声明的变量来保存直接/间接结果。 这些变量是存储过程中的的本地变量,只能用于存储过程中,无法在其他代码块中访问。

    声明变量

    要在存储过程中声明一个变量,可以使用declare语句,如下所示:

    declare variable_name datatype(size) default default_value;

    下面来更详细地解释上面的语句:

    首先,在declare关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。

    其次,指定变量的数据类型及其大小。变量可以有任何MySQL数据类型如int,varchar,datetime等。

    最后,当声明一个变量时,它的初始值为null。但是可以使用default关键字为变量分配默认值。

    例如,可以声明一个名为total_sale的变量,数据类型为int,默认值为0,如下所示:

    declare total_sale int default 0;

    MySQL允许使用单个declare语句声明共享相同数据类型的两个或多个变量,如下所示:

    declare x, y int default 0;
    
    我们声明了两个整数变量x和y,并将它们的默认值都设置为0。

    分配变量值

    当声明了一个变量后,就可以开始使用它了。要为变量分配一个值,可以使用set语句,例如:

    declare total_count int default 0; 
    
    set total_count = 10;

    上面语句中,把10分配给变量total_count。

    除了set语句之外,还可以使用select into语句将查询的结果分配给一个变量。 如下所示:

    declare total_products int default 0;
    select count(*) into total_products from products;

    在上面的示例中:

    首先,声明一个名为total_products的变量,并将其值初始化为0;

    然后,使用select into语句将products表中选择的产品数量分配给total_products变量。

    存储过程的三种参数

    (1)IN型参数:它是默认模式。在存储过程中定义IN参数时,调用程序必须将参数传递给存储过程。 另外,IN参数的值被保护。这意味着即使在存储过程中更改了IN参数的值,在存储过程结束后仍保留其原始值。换句话说,存储过程只使用IN参数的副本。

    tips:

    1)如果你使用的是mysql命令行客户端程序,默认的MySQL语句分隔符为;如果命令行实用程序要解释存储过程自身内的;字符,则它们最终不会成为存储过程的成分,这会使 存储过程中的SQL出现句法错误。解决办法是使用delimiter关键字临时更改命令行实用程序的语句分隔符。如delimiter //告诉命令行客户端程序使用//作为新的语句结束分隔符,这样,存储过程体内的;仍然保持不动,并且正确地传递给数据库引擎。最后,为恢复为原来的语句分隔符,可使用delimiter ;。除\符号外,任何字符都可以用作语句分隔符。

    2)在创建一个存储过程前可以使用drop procedure if exists procedure_name语句,防止因为创建的新存储过程的名字已存在而出现错误。

    示例:

    建立存储过程IN_example:

    delimiter //
    drop procedure if exists IN_example//
    create procedure IN_example (IN input_number int)
    begin
    set input_number = 2;
    select input_number;
    end//

    把用户变量@input_number的初始值设为3:

    set @input_number = 3//

    调用存储过程IN_example,并将用户变量传入存储过程IN_example中:

    call IN_example(@input_number)//
    
    # 返回结果为:
    +--------------+
    | input_number |
    +--------------+
    |            2 |
    +--------------+
    
    select @input_number//
    
    # 返回结果为:
    +---------------+
    | @input_number |
    +---------------+
    |             3 |
    +---------------+

    由上述结果可以看到,调用存储过程时,如果把用户变量@input_number作为IN型参数传给存储过程IN_example,因为IN参数在存储过程中被赋予了一个新值2,所以调用存储过程IN_example后的返回结果为2,但是用户变量@Input_number本身的值并没有改变,仍然是3。

    (2)OUT型参数:可以在存储过程中更改OUT参数的值,并将其更改后的新值传递回调用程序。

    示例:

    建立一个求正整数算术平方根的存储过程my_sqrt:

    drop procedure if exists my_sqrt//
    create procedure my_sqrt(IN input_number int,OUT output_number float)
    begin
    set output_number = sqrt(input_number);
    end//

    把用户变量@number的初始值设为1:

    set @number = 1//

    调用存储过程my_sqrt:

    # 把@number作为OUT型参数传递给存储过程my_sqrt
    call my_sqrt(10,@number)//
    
    #返回结果为空
    
    select @number//
    
    # 返回结果为:
    +--------------------+
    | @number            |
    +--------------------+
    | 3.1622776985168457 |
    +--------------------+
    
    
    

    由上述结果可以看到,调用存储过程后,用户变量@number的值由初始值1变成了10的算术平方根3.16227······

    (3)INOUT型参数: INOUT参数是IN和OUT参数的组合。这意味着调用程序可以传递参数,且存储过程可以修改INOUT参数并将更改后的新值传递回调用程序。

    示例:

    建立一个存储过程INOUT_example:

    drop procedure if exists INOUT_example//
    create procedure INOUT_example(INOUT number int)
    begin
    set number = 20;
    select number;
    end//
    

    把用户变量@inout_number的初始值设为1:

    set @inout_number = 1//

    调用存储过程INOUT_example,并把用户变量@inout_number作为INOUT型参数传给存储过程INOUT_example:

    call INOUT_example(@inout_number)//
    
    # 返回结果为:
    +--------+
    | number |
    +--------+
    |     20 |
    +--------+
    
    select @inout_number//
    
    # 返回结果为:
    +---------------+
    | @inout_number |
    +---------------+
    |            20 |
    +---------------+
    

    由上述结果可以看到,用户变量@inout_number作为INOUT型参数传给存储过程INOUT_example,当INOUT型参数在存储过程中被赋予了一个新值20时,用户变量@inout_number也由初始值1变成了20。此处的INOUT型参数的主要作用有两个:一是接受传入参数的值,二是保存传入的参数因在存储过程中经过一系列操作后而变成的新值。

    补充说明:虽然存储过程有三种类型的参数,但是我们在创建存储过程的时候也可以没有参数,例如创建一个从商品表goods中查询所有商品的平均价格的存储过程,如下所示:

    drop procedure if exists avg_price//
    create avg_price()
    begin
    select avg(price) as avg_price from goods;
    end//

    返回多个值的存储过程

    在创建返回多个值得存储过程之前,说明一下用到的数据表goods,它是一个商品信息记录表,详细字段如下所示:

    +----------+-------------------------------+--------+----------+-----------+--------------+------------+-------------+
    | goods_id | goods_name                    | cat_id | brand_id | goods_sn  | goods_number | shop_price | click_count |
    +----------+-------------------------------+--------+----------+-----------+--------------+------------+-------------+
    |        4 | HTCN85原装充电器              |      8 |        1 | ECS000004 |           17 |      58.00 |           0 |
    |        3 | HTC原装5800耳机               |      8 |        1 | ECS000002 |           25 |      68.00 |           3 |
    |        5 | 索爱原装M2卡读卡器            |     11 |        7 | ECS000005 |            8 |      20.00 |           3 |
    |        6 | 胜创KINGMAX内存卡             |     11 |        0 | ECS000006 |           15 |      42.00 |           0 |
    |        7 | HTCN85原装立体声耳机HS-82     |      8 |        1 | ECS000007 |           20 |     100.00 |           0 |
    |        8 | 飞利浦9@9v                    |      3 |        4 | ECS000008 |           17 |     399.00 |           9 |
    |        9 | HTCE66                        |      3 |        1 | ECS000009 |           13 |    2298.00 |          20 |
    |       10 | 索爱C702c                     |      3 |        7 | ECS000010 |            7 |    1328.00 |          11 |
    |       12 | 摩托罗拉A810                  |      3 |        2 | ECS000012 |            8 |     983.00 |          14 |
    |       13 | HTC5320 XpressMusic           |      3 |        1 | ECS000013 |            8 |    1311.00 |          13 |
    |       14 | HTC5800XM                     |      4 |        1 | ECS000014 |            4 |    2625.00 |           6 |
    |       15 | 摩托罗拉A810                  |      3 |        2 | ECS000015 |            3 |     788.00 |           8 |
    |       16 | 恒基伟业G101                  |      2 |       11 | ECS000016 |            0 |     823.33 |           3 |
    |       17 | 夏新N7                        |      3 |        5 | ECS000017 |            1 |    2300.00 |           2 |
    |       18 | 夏新T5                        |      4 |        5 | ECS000018 |            1 |    2878.00 |           0 |
    |       19 | 三星SGH-F258                  |      3 |        6 | ECS000019 |            0 |     858.00 |           7 |
    |       20 | 三星BC01                      |      3 |        6 | ECS000020 |           13 |     280.00 |          14 |
    |       21 | 金立 A30                      |      3 |       10 | ECS000021 |           40 |    2000.00 |           4 |
    |       22 | 多普达Touch HD                |      3 |        3 | ECS000022 |            0 |    5999.00 |          15 |
    |       23 | HTCN96                        |      5 |        1 | ECS000023 |            8 |    3700.00 |          17 |
    |       25 | 小灵通/固话50元充值卡         |     13 |        0 | ECS000025 |            2 |      48.00 |           0 |
    |       26 | 小灵通/固话20元充值卡         |     13 |        0 | ECS000026 |            2 |      19.00 |           0 |
    |       27 | 联通100元充值卡               |     15 |        0 | ECS000027 |            2 |      95.00 |           0 |
    |       28 | 联通50元充值卡                |     15 |        0 | ECS000028 |            0 |      45.00 |           0 |
    |       29 | 移动100元充值卡               |     14 |        0 | ECS000029 |            0 |      90.00 |           0 |
    |       30 | 移动20元充值卡                |     14 |        0 | ECS000030 |            9 |      18.00 |           1 |
    |       31 | 摩托罗拉E8                    |      3 |        2 | ECS000031 |            1 |    1337.00 |           5 |
    |       32 | HTCN85                        |      3 |        1 | ECS000032 |            1 |    3010.00 |           9 |
    +----------+-------------------------------+--------+----------+-----------+--------------+------------+-------------+

    字段说明:goods_id表示商品编号,goods_name表示商品名称,cat_id表示商品所属类别编号,brand_id表示商品所属品牌编号,goods_sn表示商品货号,goods_number表示商品库存量,shop_price表示商品的销售价格,click_count表示商品的点击量。

    创建一个能够返回商品表goods中所有商品的最低价格、最高价格和平均价格的存储过程:

    drop procedure if exists goods_price//
    create procedure goods_price(
    OUT pl decimal(6,2),
    OUT ph decimal(6,2),
    OUT pa decimal(6,2)
    )
    begin
    select min(shop_price) into pl from goods;
    select max(shop_price) into ph from goods;
    select avg(shop_price) into pa from goods;
    select pl;
    select ph;
    select pa;
    end//
    
    # 调用存储过程goods_price:
    
    call goods_price(@low_price,@high_price,@average_price)//
    
    # 返回结果为:
    +-------+
    | pl    |
    +-------+
    | 18.00 |
    +-------+
    1 row in set (0.00 sec)
    
    +---------+
    | ph      |
    +---------+
    | 5999.00 |
    +---------+
    1 row in set (0.02 sec)
    
    +---------+
    | pa      |
    +---------+
    | 1197.15 |
    +---------+
    1 row in set (0.02 sec)
    

    存储过程中的流程控制:

    可以利用if或者case语句控制存储过程中的执行流程。

    利用if语句创建一个根据金额大小判断是否打折的存储过程discounted_price:

    drop procedure if exists discounted_price//
    create procedure discounted_price(
    IN normal_price decimal(8,2),
    OUT discount_price decimal(8,2)
    )
    begin 
        if (normal_price>1000) then
            set discount_price = normal_price*0.8;
        elseif (normal_price>500) then
            set discount_price = normal_price*0.9;
        else
            set discount_price = normal_price;
        end if;
        select discount_price as new_price;
    end//

    调用discounted_price并观察其返回结果:

    call discounted_price(2000,@discount_price)//
    
    # 返回结果为:
    +-----------+
    | new_price |
    +-----------+
    |   1600.00 |
    +-----------+

    利用case语句创建一个根据金额大小判断是否打折的存储过程case_example:

    drop procedure if exists case_example//
    create procedure case_example(IN normal_price decimal(8,2),OUT discount_price decimal(8,2))
    begin
        case 
            when normal_price > 1000 then
                set discount_price = normal_price * 0.8;
            when normal_price > 500 then
                set discount_price = normal_price * 0.9;
            else
                set discount_price = normal_price;
        end case;
        select discount_price as new_price;
    
    end//

    调用存储过程case_example并观察其返回结果:

    call case_example(2000,@discount_price)//
    
    # 返回结果为:
    +-----------+
    | new_price |
    +-----------+
    |   1600.00 |
    +-----------+

    if语句和case语句的比较:

    MySQL提供if和case语句用于流程控制,使我们能够根据某些条件(称为流程控制)执行一个SQL代码块。那么我们应该使用什么语句? 对于大多数开发人员,在if和case之间进行选择只是个人偏好的问题。但是,当决定使用if或case时,应该考虑以下几点:

    1)当将单个表达式与唯一值的范围进行比较时,简单case语句比if语句更易读。另外,简单case语句比if语句更有效率。

    关于存储过程中case语句的基本用法可以参考:https://blog.csdn.net/qq_41080850/article/details/84851263

    2)当根据多个值检查复杂表达式时,if语句更容易理解。

    3)如果选择使用case语句,则必须确保至少有一个case条件匹配。否则,需要定义一个错误处理程序来捕获错误。if语句则不需要处理错误。关于存储过程中的错误处理,可以参考:

    https://www.yiibai.com/mysql/error-handling-in-stored-procedures.html

    https://www.cnblogs.com/shijiaqi1066/p/3435037.html

    4)在某些情况下,if和case混合使用反而使存储过程更加可读和高效。

    存储过程中的循环

    loop循环

    有两个语句可以用于控制loop循环:

    1)leave语句用于立即退出循环,而无需等待检查条件。leave语句的工作原理类似于C/C++,java等语言中的break语句。

    2)iterate语句允许跳过剩下的整个代码并开始新的迭代。iterate语句类似C/C++,Java等语言中的continue语句。

    示例1:

    创建存储过程loop_example1,它可以用于条件计数:

    drop procedure if exists loop_example1//
    create procedure loop_example1()
    begin
        declare counter int default 0;
        my_loop:loop
            if counter < 10 then
                set counter = counter + 1;
            else
                leave my_loop;
            end if;
        end loop my_loop;
        select counter;
    end//

    调用存储过程loop_example1并观察其返回结果:

    call loop_example1()//
    
    # 返回结果为
    +---------+
    | counter |
    +---------+
    |      10 |
    +---------+

    示例2:

    创建存储过程loop_example2,它的功能是找出20以内不为0的偶数:

    drop procedure if exists loop_example2//
    create procedure loop_example2()
    begin
        declare x int default 0;
        declare strings varchar(100) default '';
        my_loop:loop
            if x > 20 then
                leave my_loop;
            end if;
            set x = x + 1;
            if mod(x,2) then
                iterate my_loop;
            else
                set strings = concat(strings,x,',');
                iterate my_loop;
            end if;
        end loop;
        select strings;
    end//

    调用存储过程loop_example2并观察其返回结果:

    call loop_example2()//
    
    # 返回结果为:
    +----------------------------+
    | strings                    |
    +----------------------------+
    | 2,4,6,8,10,12,14,16,18,20, |
    +----------------------------+

    while循环

    while语句的语法如下:

    while expression do
        statements; 
    end while

    while循环在每次迭代开始时检查表达式。 如果expression为True,MySQL将执行while和end while之间的语句,直到expression为false。 while循环称为预先测试条件循环,因为它总是在执行前检查语句的表达式。

    示例:

    创建存储过程while_example,它也可以用于条件计数:

    drop procedure if exists while_example//
    create procedure while_example()
    begin
        declare counter int default 0;
        while counter < 10 do
            set counter = counter + 1;
        end while;
        select counter;
    end//

    调用存储过程while_example并观察其返回结果:

    call while_example()//
    
    # 返回结果为:
    +---------+
    | counter |
    +---------+
    |      10 |
    +---------+
    
    

    repeat循环

    repeat循环语句的语法如下:

    repeat 
        statements;  
    until expression  # 注意until语句后面是没有标点符号的 
    end repeat

    首先,MySQL执行语句statements,然后评估求值表达式expression。如果表达式expression的计算结果为false,则MySQL将重复执行该语句,直到该表达式计算结果为True。因为repeat循环语句在执行语句后检查表达式expression,因此repeat循环语句也称为测试后循环。

    示例:

    创建存储过程repeat_example,它也可以用于条件计数:

    drop procedure if exists repeat_example//
    create procedure repeat_example()
    begin
        declare counter int default 0;
        repeat
            set counter = counter + 1;
        until counter > 10
        end repeat;
        select counter;
    end//

    调用存储过程repeat_example并观察其返回结果:

    call repeat_example()//
    
    # 返回结果为:
    +---------+
    | counter |
    +---------+
    |      11 |
    +---------+
    

    MySQL中的游标

    说明:不像其他DBMS,MySQL中的游标只能用于存储过程和存储函数。

    关于MySQL游标的理解:

    MySQL中的游标可以理解成一个可迭代对象(类比Python中的列表、字典等可迭代对象),它可以用来存储select 语句查询到的结果集,这个结果集可以包含多行数据,从而使我们可以使用迭代的方法从游标中依次取出每行数据。

    MySQL游标的特点:

    1)只读:无法通过光标更新基础表中的数据。

    2)不可滚动:只能按照select语句确定的顺序获取行。不能以相反的顺序获取行。 此外,不能跳过行或跳转到结果集中的特定行。

    3)敏感:有两种游标:敏感游标和不敏感游标。敏感游标指向实际数据,不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快,因为它不需要临时拷贝数据。MySQL游标是敏感的。

    MySQL游标的使用方法:

    首先,必须使用declare语句声明游标:

    declare cursor_name cursor for select_statement;

    游标声明必须在变量声明之后。如果在变量声明之前声明游标,MySQL将会发出一个错误。游标必须始终与select语句相关联。

    接下来,使用open语句打开游标。open语句初始化游标的结果集,因此必须在从结果集中提取行之前调用open语句。

    open cursor_name;
    

    然后,使用fetch语句来检索游标指向的一行数据,并将游标移动到结果集中的下一行。

    fetch cursor_name into variable_name;

    之后,可以检查是否有任何行记录可用,然后再提取它。

    最后,调用close语句来停用游标并释放与之关联的内存,如下所示:

    close cursor_name

    当游标不再使用时,应该关闭它。

    当使用MySQL游标时,还必须声明一个not found处理程序来处理当游标找不到任何行时的情况。 因为每次调用fetch语句时,游标会尝试依次读取结果集中的每一行数据。 当游标到达结果集的末尾时,它将无法获得数据,并且会产生一个条件。 处理程序用于处理这种情况。

    要声明一个not found处理程序,参考以下语法:

    declare continue handler for not found set finished = 1;

    finished是一个变量,指示游标到达结果集的结尾。注意,处理程序声明必须出现在存储过程中的变量和游标声明之后。

    示例:

    # 先创建一个根据商品名称获取商品价格的存储过程get_shop_price
    
    drop procedure if exists get_shop_price//
    
    create procedure get_shop_price(IN name varchar(20),OUT price decimal(6,2))
    begin
    select shop_price into price from goods where goods_name = name;
    end//
    
    # 再创建一个获取商品表goods中所有价格大于指定值的商品名称和价格,并把结果存入一张新建的goodsnames表中
    
    drop procedure if exists build_goodsname_list//
    
    create procedure build_goodsname_list(IN input_price decimal(6,2))
    begin
        declare finished int default 0;
        declare name varchar(20) default '';
        declare price decimal(6,2) default 0;
    
        -- declare the cursor
        declare goods_cursor cursor for select goods_name from goods where shop_price>input_price;
    
        -- declare continue handler
        declare continue handler for not found set finished = 1;
    
        -- drop table goodsnames
        drop table goodsnames;
    
        -- create a table to store the results
        create table goodsnames(
        goods_name varchar(20) not null default '',
        shop_price decimal(6,2) not null default 0
        )engine myisam charset utf8;
    
        -- open the cursor
        open goods_cursor;
    
        -- fetch all rows in the cursor
        repeat
            -- get goods_name
            fetch goods_cursor into name;
        
            if not finished then
    
            -- get the shop_price for this goods_name
            call get_shop_price(name,price);
    
            -- insert name and price into goodsnames
            insert into goodsnames values(name,price);
    
            end if;
    
        -- end of repeat loop
        until finished
        end repeat;
    
        -- close the cursor
        close goods_cursor;
        
        select * from goodsnames;
    end//

    调用存储过程build_goodsname_list,并观察其返回结果:

    call build_goodsname_list(1000)//
    
    # 返回结果为:
    +--------------------------+------------+
    | goods_name               | shop_price |
    +--------------------------+------------+
    | HTCE66                   |    2298.00 |
    | 索爱C702c                |    1328.00 |
    | HTC5320 XpressMusic      |    1311.00 |
    | HTC5800XM                |    2625.00 |
    | 夏新N7                   |    2300.00 |
    | 夏新T5                   |    2878.00 |
    | 金立 A30                 |    2000.00 |
    | 多普达Touch HD           |    5999.00 |
    | HTCN96                   |    3700.00 |
    | 摩托罗拉E8               |    1337.00 |
    | HTCN85                   |    3010.00 |
    +--------------------------+------------+
    
    
    call build_goodsname_list(2000)//
    
    # 返回结果为:
    +-------------------------+------------+
    | goods_name              | shop_price |
    +-------------------------+------------+
    | HTCE66                  |    2298.00 |
    | HTC5800XM               |    2625.00 |
    | 夏新N7                  |    2300.00 |
    | 夏新T5                  |    2878.00 |
    | 多普达Touch HD          |    5999.00 |
    | HTCN96                  |    3700.00 |
    | HTCN85                  |    3010.00 |
    +-------------------------+------------+

    MySQL中的存储函数

    存储的函数是返回单个值的特殊类型的存储程序。我们使用存储的函数来封装在SQL语句或存储的程序中可重用的常用公式或业务规则。

    与存储过程不同,我们可以在SQL语句中使用存储的函数,也可以在表达式中使用。 这有助于提高程序代码的可读性和可维护性。

    存储函数语法

    create function function_name(param1,param2,…)
        returns datatype
       [not] deterministic
     statements;

    上述代码的详细解释:

    首先,在create function语句之后指定存储函数的名称。

    其次,列出括号内存储函数的所有参数。 默认情况下,所有参数均为IN参数。不能为参数指定IN,OUT或INOUT修饰符。

    第三,必须在returns语句中指定返回值的数据类型。它可以是任何有效的MySQL数据类型。

    第四,对于相同的输入参数,如果存储的函数返回相同的结果,这样则被认为是确定性的,否则存储的函数不是确定性的。必须决定一个存储函数是否是确定性的。 如果声明不正确,则存储的函数可能会产生意想不到的结果,或者不能使用可用的优化,从而降低性能。

    第五,将代码写入存储函数的主体中。 它可以是单个语句或复合语句。 在主体部分中,必须至少指定一个return语句。return语句用于返回一个值给调用者。 每当到达return语句时,存储函数的执行将立即终止。

     示例:

    创建一个存储函数goods_level,它的功能是根据给定的商品价格确定商品所属的价格分类:

    drop function if exists goods_level//
    
    create function goods_level(p_shop_price decimal(6,2))
        returns varchar(20)
        deterministic
    begin
        declare g_level varchar(20);
        if p_shop_price > 2000 then
            set g_level = 'high_level';
        elseif (p_shop_price>=1000 and p_shop_price<=2000) then
            set g_level = 'middle_level';
        else
            set g_level = 'low_level';
        end if;
        return (g_level);
    end//

    在select语句中调用goods_level函数:

    select goods_id,goods_name,shop_price,goods_level(shop_price) as price_level from goods//
    
    # 返回结果为:
    +----------+-------------------------------+------------+--------------+
    | goods_id | goods_name                    | shop_price | price_level  |
    +----------+-------------------------------+------------+--------------+
    |        4 | HTCN85原装充电器              |      58.00 | low_level    |
    |        3 | HTC原装5800耳机               |      68.00 | low_level    |
    |        5 | 索爱原装M2卡读卡器            |      20.00 | low_level    |
    |        6 | 胜创KINGMAX内存卡             |      42.00 | low_level    |
    |        7 | HTCN85原装立体声耳机HS-82     |     100.00 | low_level    |
    |        8 | 飞利浦9@9v                    |     399.00 | low_level    |
    |        9 | HTCE66                        |    2298.00 | high_level   |
    |       10 | 索爱C702c                     |    1328.00 | middle_level |
    |       12 | 摩托罗拉A810                  |     983.00 | low_level    |
    |       13 | HTC5320 XpressMusic           |    1311.00 | middle_level |
    |       14 | HTC5800XM                     |    2625.00 | high_level   |
    |       15 | 摩托罗拉A810                  |     788.00 | low_level    |
    |       16 | 恒基伟业G101                  |     823.33 | low_level    |
    |       17 | 夏新N7                        |    2300.00 | high_level   |
    |       18 | 夏新T5                        |    2878.00 | high_level   |
    |       19 | 三星SGH-F258                  |     858.00 | low_level    |
    |       20 | 三星BC01                      |     280.00 | low_level    |
    |       21 | 金立 A30                      |    2000.00 | middle_level |
    |       22 | 多普达Touch HD                |    5999.00 | high_level   |
    |       23 | HTCN96                        |    3700.00 | high_level   |
    |       25 | 小灵通/固话50元充值卡         |      48.00 | low_level    |
    |       26 | 小灵通/固话20元充值卡         |      19.00 | low_level    |
    |       27 | 联通100元充值卡               |      95.00 | low_level    |
    |       28 | 联通50元充值卡                |      45.00 | low_level    |
    |       29 | 移动100元充值卡               |      90.00 | low_level    |
    |       30 | 移动20元充值卡                |      18.00 | low_level    |
    |       31 | 摩托罗拉E8                    |    1337.00 | middle_level |
    |       32 | HTCN85                        |    3010.00 | high_level   |
    +----------+-------------------------------+------------+--------------+
    

    建立存储过程get_price_level,在存储过程中调用存储函数goods_level,使得存储过程get_goods_level能根据给定的goods_id确定对应商品的价格所属的分类:

    drop procedure if exists get_price_level//
    
    create procedure get_price_level(IN g_goods_id int,OUT g_price_level varchar(20))
    begin
        declare g_shop_price decimal(6,2);
        select shop_price into g_shop_price from goods where goods_id = g_goods_id;
        select goods_level(g_shop_price) into g_price_level;
        select g_price_level as price_level;
    end//

    测试存储过程get_price_level:

    call get_price_level(3,@price_level)//
    
    # 返回结果为:
    +-------------+
    | price_level |
    +-------------+
    | low_level   |
    +-------------+
    
    
    call get_price_level(9,@price_level)//
    
    # 返回结果为:
    +-------------+
    | price_level |
    +-------------+
    | high_level  |
    +-------------+
    
    
    call get_price_level(21,@price_level)//
    
    # 返回结果为:
    +--------------+
    | price_level  |
    +--------------+
    | middle_level |
    +--------------+
    
    # 将以上测试结果与前文在select语句中调用goods_level后的返回结果比较可知测试结果是正确的。

    存储过程与存储函数的比较

    可以参考:https://blog.csdn.net/qq_32444825/article/details/79170109

     

    其他参考:

    https://www.cnblogs.com/gavin110-lgy/p/5772577.html

    https://blog.csdn.net/JQ_AK47/article/details/52087484

    https://blog.csdn.net/myweishanli/article/details/41245923

    https://www.yiibai.com/mysql/stored-procedure.html#article-start

    https://blog.csdn.net/Maple1997/article/details/79390797

    《MySQL必知必会》——Ben·Forta

     

     

    展开全文
  • Mysql中存储过程

    千次阅读 2017-07-26 15:01:47
    什么是存储过程 简单的说,就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似...有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能;...

    什么是存储过程

    简单的说,就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法;

    ps:存储过程跟触发器有点类似,都是一组SQL集,但是存储过程是主动调用的,且功能比触发器更加强大,触发器是某件事触发后自动调用;

    有哪些特性

    有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能;

    函数的普遍特性:模块化,封装,代码复用;

    速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤;

    创建一个简单的存储过程

    存储过程proc_adder功能很简单,两个整型输入参数a和b,一个整型输出参数sum,功能就是计算输入参数a和b的结果,赋值给输出参数sum;

    几点说明:

    DELIMITER ;;:之前说过了,把默认的输入的结束符;替换成;;。

    DEFINER:创建者;

    复制代码
    -- ----------------------------
    -- Procedure structure for `proc_adder`
    -- ----------------------------
    DROP PROCEDURE IF EXISTS `proc_adder`;
    DELIMITER ;;
    CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
    BEGIN
        #Routine body goes here...
    
        DECLARE c int;
        if a is null then set a = 0; 
        end if;
      
        if b is null then set b = 0;
        end if;
    
        set sum  = a + b;
    END
    ;;
    DELIMITER ;
    复制代码

    执行以上存储结果,验证是否正确,如下图,结果OK:

    set @b=5;
    call proc_adder(2,@b,@s);
    select @s as sum;

    存储过程中的控制语句

    IF语句:

    复制代码
    -- ----------------------------
    -- Procedure structure for `proc_if`
    -- ----------------------------
    DROP PROCEDURE IF EXISTS `proc_if`;
    DELIMITER ;;
    CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_if`(IN type int)
    BEGIN
        #Routine body goes here...
        DECLARE c varchar(500);
        IF type = 0 THEN
            set c = 'param is 0';
        ELSEIF type = 1 THEN
            set c = 'param is 1';
        ELSE
            set c = 'param is others, not 0 or 1';
        END IF;
        select c;
    END
    ;;
    DELIMITER ;
    复制代码

    CASE语句:

    复制代码
    -- ----------------------------
    -- Procedure structure for `proc_case`
    -- ----------------------------
    DROP PROCEDURE IF EXISTS `proc_case`;
    DELIMITER ;;
    CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_case`(IN type int)
    BEGIN
        #Routine body goes here...
        DECLARE c varchar(500);
        CASE type
        WHEN 0 THEN
            set c = 'param is 0';
        WHEN 1 THEN
            set c = 'param is 1';
        ELSE
            set c = 'param is others, not 0 or 1';
        END CASE;
        select c;
    END
    ;;
    DELIMITER ;
    复制代码

    循环while语句:

    复制代码
    -- ----------------------------
    -- Procedure structure for `proc_while`
    -- ----------------------------
    DROP PROCEDURE IF EXISTS `proc_while`;
    DELIMITER ;;
    CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_while`(IN n int)
    BEGIN
        #Routine body goes here...
        DECLARE i int;
        DECLARE s int;
        SET i = 0;
        SET s = 0;
        WHILE i <= n DO
            set s = s + i;
            set i = i + 1;
        END WHILE;
        SELECT s;
    END
    ;;
    DELIMITER ;
    复制代码

    其它:略~

    存储过程弊端

    不同数据库,语法差别很大,移植困难,换了数据库,需要重新编写;

    不好管理,把过多业务逻辑写在存储过程不好维护,不利于分层管理,容易混乱,一般存储过程适用于个别对性能要求较高的业务,其它的必要性不是很大;

     

    原文 http://www.cnblogs.com/chenpi/p/5136483.html

    展开全文
  • 使用MySQL存储过程编写九九乘法表

    千次阅读 2020-10-19 09:07:45
    进入数据库编写存储过程执行存储过程,并查看效果 进入数据库编写存储过程 mysql> use school; Database changed mysql> create table 乘法表(九九 varchar(666)); Query OK, 0 rows affected (0.01 sec) ...
  • MySQL工作笔记-编写存储过程批量添加数据

    千次阅读 多人点赞 2019-03-18 16:47:11
    存储过程就和编程里面的函数差不多,这里主要是写一个for循环! 演示 如下代码: delimiter $$ create procedure autoInsert() begin declare i int; set i = 0; while i &lt; 60 * 24 do insert into ...
  • 主要介绍了在Mysql存储过程中使用事务实例,需要的朋友可以参考下
  • 中文乱码无论何时都是一个头疼的问题,mysql存储过程参数也同样存在这个问题。 1、直接使用insert into语句没问题,能够正常插入汉字。 2、把insert into语句移到Procedure后,就无法插入汉字了。客户端软件...
  • mysql navicat创建存储过程

    万次阅读 2018-06-13 00:37:04
    1、navicat for mysql安装及连接到数据库navicat安装就不说了,...图12、在MySql Shell下创建存储过程创建存储过程时,可以在mysql shell里头写代码实现,下面是shell创建并调用一个存储过程的简单实例。给定一...
  • 通过使用Oracle作为中间容器编写存储过程,将Sql Server的数据实时同步到MySql,不直接SqlServer进行操作。 公司最近的项目要部署上线了,然后需要将甲方公司的ERP,OA,MES,PLM等系统的数据同步到我们的系统之...
  • mysql编写存储过程hibernate4调用

    千次阅读 2015-11-18 20:36:52
    2.写存储过程 create procedure pro_student(stu_name varchar(20)) BEGIN start TRANSACTION; update student set name=stu_name where id=1; commit; select * from student where id=1; end select * ...
  • mysql存储过程学习笔记

    万次阅读 多人点赞 2019-02-22 17:09:36
    本文主要记录了本人学习mysql存储过程时的笔记,文档中用到的表结构文档结尾处有说明。 一、定义  存储过程(Stored Procedure)是大型数据库系统,一组为了完成特定功能的SQL 语句集,存储在数据库,...
  • MySQL存储过程详解 mysql 存储过程

    千次阅读 2016-09-20 15:13:23
    我们常用的操作数据库语言SQL语句执行的时候需要要先编译,然后执行,而存储过程(StoredProcedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库,用户通过指定存储过程的名字并给定参数(如果该...
  • mysql存储过程

    2011-11-03 11:50:28
    如何编写 mysql存储过程 ,对存储过程的各种写法,语法,例子齐全。是编写mysql存储过程的法宝
  • 学习MySQL存储过程中写到的一些小demo,此记录一种项目MySQL事件结合使用得,以供记录学习过程。 一个简单的update存储过程: CREATE DEFINER=`root`@`%` PROCEDURE `update_bagNUM_stamp`() BEGIN ...
  • Mysql中存储过程简介

    千次阅读 2018-11-11 21:13:19
    1、什么是存储过程 简单的说,就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于...有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功...
  • MySQL数据库存储过程讲解与实例

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

    千次阅读 2014-11-06 16:13:52
    存储过程如同一门程序设计语言,同样包含了数据类型、流程控制、输入和输出和它自己的函数库。 --------------------基本语法-------------------- 一.创建存储过程 create procedure sp_name() begin .......
  • mysql存储引擎

    万次阅读 多人点赞 2019-07-31 19:28:44
    数据库存储引擎 数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行...因为关系数据库数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(Table Type,即存储和操作此表...
  • MySQL 存储过程

    千次阅读 多人点赞 2018-12-10 20:55:26
    存储过程(Stored Procedure)是一种数据库中存储复杂程序,以便外部程序调用的一种数据库对象 存储过程就是具有名字的一段代码,用来完成一个特定的功能。 创建的存储过程保存数据库的数据字典 优点: 存储...
  • MYSQL存储过程编写删除前N天的数据实例编写存储过程sql编写定时事件SQL开启某个事件 编写存储过程sql DELIMITER $$ -- -- 存储过程 -- CREATE PROCEDURE `p_del_count`(IN `date_inter` INT) BEGIN DELETE FROM ...
  • shell脚本编写存储过程(mysql

    千次阅读 2015-06-27 11:10:25
    #!/bin/sh --三个变量 数据库名 生成的作业ID 生成数据量 ...--如果变量输等于3 跳出 ...delete from DC_JOB_REF where job_id like '${id_mod}_%' or ...mysql -uroot -p+password -Ddc #rm -f test.sq
  • 辛星解读mysql中存储过程的优劣

    千次阅读 2014-08-26 14:58:31
    我们常用的SQL语句执行的时候需要首先进行编译,然后执行,而存储过程则是一组为了完成特定功能的SQL语句集,它直接编译好了存储在数据库,用户通过指定存储过程的名字即可调用该存储过程。  存储过程的优点...
  • mysql创建使用存储过程

    千次阅读 2019-10-28 10:37:37
    mysql存储过程详解 1.      存储过程简介   我们常用的操作数据库语言SQL语句执行的时候需要要先编译,然后执行,...
  • 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调用存储过程 ...
  • 在编写MySQL存储过程过程中,我们会时不时地需要对某些存储过程进行优化,其目的是确保代码的可读性、正确性及运行性能。本文以作者实际工作为背景,介绍了对某一个MySQL存储过程优化的整个过程
  • MySQL创建存储过程

    万次阅读 2019-03-27 17:08:44
    Navicat for MySQL创建存储过程及MyBatis调用存储过程 问题描述 一条语句实现向两个表添加数据 前段时间遇到需求,向一个表添加数据,获取新增主键值。再向另一个表添加数据,其中一个字段为前一个表的主键值。...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 138,613
精华内容 55,445
关键字:

怎么在mysql中编写存储过程

mysql 订阅