mysql存储过程的实例（数据库存储过程实例）

发布时间：2022-11-14

本文目录一览：

  1. mysql存储过程 in 怎么用
  2. 如何使用mysql 存储过程 实例
  3. mysql 存储过程总结（一）
  4. mysql存储过程实例
  5. mysql创建存储过程示例
  6. 求一个MYSQL存储过程的例子

mysql存储过程 in 怎么用

out 表示输出的参数，存储过程调用代码需要获得此参数值。 in 表示输入参数，默认为 in。

例1、一个简单存储过程游标实例

DELIMITER $$
DROP PROCEDURE IF EXISTS getUserInfo $$
CREATE PROCEDURE getUserInfo(in date_day datetime)
-- 
-- 实例
-- 存储过程名为：getUserInfo
-- 参数为：date_day日期格式:2008-03-08
-- 
BEGIN
    declare _userName varchar(12); -- 用户名
    declare _chinese int ; -- 语文
    declare _math int ; -- 数学
    declare done int;
    -- 定义游标
    DECLARE rs_cursor CURSOR FOR SELECT username,chinese,math from userInfo where datediff(createDate, date_day)=0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    -- 获取昨天的日期
    if date_day is null then
        set date_day = date_add(now(),interval -1 day);
    end if;
    open rs_cursor;
    cursor_loop:loop
        FETCH rs_cursor into _userName, _chinese, _math; -- 取数据
        if done=1 then
            leave cursor_loop;
        end if;
        -- 更新表
        update infoSum set total=_chinese+_math where UserName=_userName;
    end loop cursor_loop;
    close rs_cursor;
END$$
DELIMITER ;

例2、存储过程游标循环跳出现

在 MySQL 的存储过程中，游标操作时，需要执行一个 continue 的操作。众所周知，MySQL 中的游标循环操作常用的有三种：LOOP、REPEAT、WHILE。三种循环，方式大同小异。

1. REPEAT

REPEAT
    Statements;
UNTIL expression
END REPEAT

示例：

DECLARE num INT;
DECLARE my_string VARCHAR(255);
REPEAT
    SET my_string = CONCAT(my_string, num, ',');
    SET num = num + 1;
UNTIL num > 5
END REPEAT;

2. WHILE

WHILE expression DO
    Statements;
END WHILE

示例：

DECLARE num INT;
DECLARE my_string VARCHAR(255);
SET num = 1;
SET my_string = '';
WHILE num <= 10 DO
    SET my_string = CONCAT(my_string, num, ',');
    SET num = num + 1;
END WHILE;

3. LOOP（包含 ITERATE 和 LEAVE）

DECLARE num INT;
DECLARE my_string VARCHAR(255);
SET num = 1;
SET my_string = '';
loop_label: LOOP
    IF num > 10 THEN
        LEAVE loop_label;
    END IF;
    SET num = num + 1;
    IF (num % 3) THEN
        ITERATE loop_label;
    ELSE
        SET my_string = CONCAT(my_string, num, ',');
    END IF;
END LOOP;

可以这样理解：ITERATE 就是我们程序中常用的 continue，而 LEAVE 就是 break。当然在 MySQL 存储过程中，需要循环结构有个名称，其他都是一样的。

例3、MySQL 存储过程中使用多游标

先创建一张表，插入一些测试数据：

DROP TABLE IF EXISTS netingcn_proc_test;
CREATE TABLE `netingcn_proc_test` (
    `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20),
    `password` VARCHAR(20),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO netingcn_proc_test(name, password) VALUES
('procedure1', 'pass1'),
('procedure2', 'pass2'),
('procedure3', 'pass3'),
('procedure4', 'pass4');

示例存储过程：

drop procedure IF EXISTS test_proc;
delimiter //
create procedure test_proc()
begin
    -- 声明一个标志done，用来判断游标是否遍历完成
    DECLARE done INT DEFAULT 0;
    -- 声明一个变量，用来存放从游标中提取的数据
    -- 特别注意这里的名字不能与由游标中使用的列明相同，否则得到的数据都是NULL
    DECLARE tname varchar(50) DEFAULT NULL;
    DECLARE tpass varchar(50) DEFAULT NULL;
    -- 声明游标对应的 SQL 语句
    DECLARE cur CURSOR FOR
        select name, password from netingcn_proc_test;
    -- 在游标循环到最后会将 done 设置为 1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    -- 执行查询
    open cur;
    -- 遍历游标每一行
    REPEAT
        -- 把一行的信息存放在对应的变量中
        FETCH cur INTO tname, tpass;
        if not done then
            -- 这里就可以使用 tname， tpass 对应的信息了
            select tname, tpass;
        end if;
    UNTIL done END REPEAT;
    CLOSE cur;
end
//
delimiter ;
-- 执行存储过程
call test_proc();

使用多个游标：

drop procedure IF EXISTS test_proc_1;
delimiter //
create procedure test_proc_1()
begin
    DECLARE done INT DEFAULT 0;
    DECLARE tid int(11) DEFAULT 0;
    DECLARE tname varchar(50) DEFAULT NULL;
    DECLARE tpass varchar(50) DEFAULT NULL;
    DECLARE cur_1 CURSOR FOR
        select name, password from netingcn_proc_test;
    DECLARE cur_2 CURSOR FOR
        select id, name from netingcn_proc_test;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    open cur_1;
    REPEAT
        FETCH cur_1 INTO tname, tpass;
        if not done then
            select tname, tpass;
        end if;
    UNTIL done END REPEAT;
    CLOSE cur_1;
    -- 注意这里，一定要重置 done 的值为 0
    set done = 0;
    open cur_2;
    REPEAT
        FETCH cur_2 INTO tid, tname;
        if not done then
            select tid, tname;
        end if;
    UNTIL done END REPEAT;
    CLOSE cur_2;
end
//
delimiter ;
call test_proc_1();

使用 BEGIN 嵌套处理多个游标：

drop procedure IF EXISTS test_proc_2;
delimiter //
create procedure test_proc_2()
begin
    DECLARE done INT DEFAULT 0;
    DECLARE tname varchar(50) DEFAULT NULL;
    DECLARE tpass varchar(50) DEFAULT NULL;
    DECLARE cur_1 CURSOR FOR
        select name, password from netingcn_proc_test;
    DECLARE cur_2 CURSOR FOR
        select id, name from netingcn_proc_test;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    open cur_1;
    REPEAT
        FETCH cur_1 INTO tname, tpass;
        if not done then
            select tname, tpass;
        end if;
    UNTIL done END REPEAT;
    CLOSE cur_1;
    begin
        DECLARE done INT DEFAULT 0;
        DECLARE tid int(11) DEFAULT 0;
        DECLARE tname varchar(50) DEFAULT NULL;
        DECLARE cur_2 CURSOR FOR
            select id, name from netingcn_proc_test;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        open cur_2;
        REPEAT
            FETCH cur_2 INTO tid, tname;
            if not done then
                select tid, tname;
            end if;
        UNTIL done END REPEAT;
        CLOSE cur_2;
    end;
end
//
delimiter ;
call test_proc_2();

如何使用mysql 存储过程 实例

DECLARE var_name[,...] type [DEFAULT value] 这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个 DEFAULT 子句。值可以被指定为一个表达式，不需要为一个常数。如果没有 DEFAULT 子句，初始值为 NULL。局部变量的作用范围在它被声明的 BEGIN ... END 块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。

mysql 存储过程总结（一）

1. 存储过程定义：

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

2. 特点：

  • 封装，复用：可以把某一业务 SQL 封装在存储过程中，需要用到的时候直接调用即可。
  • 可以接收参数，也可以返回数据：在存储过程中，可以传递参数，也可以接收返回值。
  • 减少网络交互，效率提升：如果涉及到多条 SQL，每执行一次都是一次网络传输。而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

3. 基本语法

（1）创建：

CREATE PROCEDURE procedure_name (parameters)
BEGIN
    -- SQL statements
END;

（2）调用：

CALL procedure_name(parameters);

（3）查看：

SHOW CREATE PROCEDURE procedure_name;

（4）删除：

DROP PROCEDURE IF EXISTS procedure_name;

注意：在命令行中，执行创建存储过程的 SQL 时，需要通过关键字 delimiter 指定 SQL 语句的结束符。

mysql存储过程实例

你是指 '01' 想动态的可以用别的值？可以在创建存储过程时用 in，也就是调用存储过程时传入参数。 大概像这个样子：

CREATE PROCEDURE sp_test(IN a CHAR(50))

然后将 '01' 换为 a。

mysql创建存储过程示例

  1. 用 MySQL 客户端登入
  2. 选择数据库
USE test;
  1. 查询当前数据库有哪些存储过程
SHOW PROCEDURE STATUS WHERE Db = 'test';
  1. 创建一个简单的存储过程
CREATE PROCEDURE hi()
SELECT 'hello';
  1. 存储过程创建完毕，看怎么调用它
CALL hi();

显示结果：

+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
  1. 一个简单的存储过程就成功了。

求一个MYSQL存储过程的例子

DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_test`$$
CREATE PROCEDURE `carpo_xianjin`.`proc_test`(IN user_name VARCHAR(50), IN amount VARCHAR(20))
BEGIN
    DECLARE v_balance VARCHAR(20);
    SELECT balance INTO v_balance FROM userbalance WHERE USER = user_name;
    IF v_balance > amount THEN
        UPDATE userbalance SET balance = balance - amount WHERE USER = user_name;
        INSERT INTO LOG(TYPE) VALUES('修改余额');
    END IF;
END$$
DELIMITER ;

看下吧，你没给脚本，我也没办法测试。

推荐文章