3. SQL编程之MySQL 存储过程

SQL编程之MySQL 存储过程

上一篇已经介绍了function与procedure的区别,function主要用来实现只读的功能,当需要涉及修改的需求时,procedure就派上用场.同样的先来个简单的例子,目睹为快:

USE db_dba;

DELIMITER $$
CREATE PROCEDURE `P_INIT_TB_T1`()
BEGIN

  DROP TABLE IF EXISTS t1;

  CREATE TABLE t1(
    c1 int not null,
    c2 int not null,
    c3 int not null,
    c4 int not null,
    PRIMARY KEY(c1),
    KEY c2(c2)
  ) ENGINE=INNODB;

  INSERT INTO t1 VALUES
    (0,0,0,0),
    (1,1,1,0),
    (3,3,3,0),
    (4,2,2,0),
    (6,2,5,0),
    (8,6,6,0),
    (10,4,4,0);

END$$

DELIMITER ;

纳尼,怎么这么眼熟! 这个我想不用做过的解释了,这个procedure的功能就是初始化一个和叶老师讲InnoDB锁章节用的测试环境.是不是感觉很方便呢? 接下来再次体验一下:

# 创建PROCEDURE
mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS `P_INIT_TB_T1` $$
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE PROCEDURE `P_INIT_TB_T1`()
    -> BEGIN
    ->
    ->   DROP TABLE IF EXISTS t1;
    ->
    ->   CREATE TABLE t1(
    ->     c1 int not null,
    ->     c2 int not null,
    ->     c3 int not null,
    ->     c4 int not null,
    ->     PRIMARY KEY(c1),
    ->     KEY c2(c2)
    ->   ) ENGINE=INNODB;
    ->
    ->   INSERT INTO t1 VALUES
    ->     (0,0,0,0),
    ->     (1,1,1,0),
    ->     (3,3,3,0),
    ->     (4,2,2,0),
    ->     (6,2,5,0),
    ->     (8,6,6,0),
    ->     (10,4,4,0);
    ->
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

procedure已经创建好了,它的调用方式和function不同,需要用到call关键字.

mysql> call p_init_tb_t1();
Query OK, 7 rows affected (0.52 sec)

当你在测试锁时, 不小心改错数据,想要还原t1表的数据, 仅需再call p_init_tb_t1()一下就OK , SO EASY!

之前也介绍过SQL编程中一些语法,如变量定义,赋值,条件判断,循环,异常处理在function,procedure及trigger中都是通用的,下面再用procedrue作为例子,进一步了解SQL编程的具体语法:

DELIMITER $$
DROP PROCEDURE IF EXISTS `P_INIT_TB_T2` $$

-- 请注意,这次多出了两个参数,用IN和OUT修饰, 分别代表输入参数和输出参数
-- 参数的命名也分别用"I_"和"O_"进行区分,使其更加清晰
CREATE PROCEDURE `P_INIT_TB_T2`(IN I_TOTAL INT,OUT O_RESULT TINYINT)
-- 申明带label的语句块
main_label:BEGIN
  -- 功能: 创建表T2,并随机生成I_TOTAL条数据,成功返回O_RESULT等于0, 失败返回O_RESULT等于1

  -- 定义几个变量,同样都用前缀"C_"来标示,代表CACHE的意思
  DECLARE C_N1 INT DEFAULT 0;
  DECLARE C_NUM INT DEFAULT 0;
  DECLARE C_CHAR VARCHAR(10);

  -- 条件判断:超过10000时就退出
  IF I_TOTAL >= 10000 THEN
    SET O_RESULT=1;
    -- 只能使用LEAVE + label的形式来退出指定语名块.本例中是退出整个prcedure
    LEAVE main_label;
  END IF;
  
  -- 标准SQL,删除表
  DROP TABLE IF EXISTS t2;

   -- 重新创建
  CREATE TABLE t2(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `nid` int(11) DEFAULT NULL,
    `cid` varchar(10) DEFAULT NULL,
    `n1` int(11) DEFAULT NULL,
    `c1` varchar(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_nid` (`nid`),
    KEY `idx_cid` (`cid`)
  ) ENGINE=INNODB;

  -- 循环
  WHILE C_N1 < I_TOTAL DO
    -- 赋值, 不能写成 C_N1++
    SET C_N1 = C_N1 + 1;

    -- 随机生成一个数字
    SELECT ROUND(RAND()*1000)
    INTO   C_NUM;
    
    -- 随机生成一串字符
    SELECT REPEAT(CHAR(65 + RAND()*52),10)
    INTO   C_CHAR;

    -- 标准的INSERT语句,使用随机生成的值写入T2表
    INSERT INTO t2
    SELECT NULL,C_NUM,C_CHAR,C_NUM,C_CHAR;

  END WHILE;
    
    -- 处理返回值,注意: 这里没有return,在procedure中可以实现多个返回值
  SET O_RESULT = 0;
END$$

DELIMITER ;

为了节约篇幅,我就不再贴创建的过程了.
由于procedure是用call调用,在获取返回值时需要用到MySQL中的session变量,先普及一下:

# 创建一个session变量,固定以@开头即可,生命周期是整个session
mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

# 直接查看值
mysql> select @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

# 使用session变量进行运算,当然你也可以拿来做where条件
mysql> select @a + 1;
+--------+
| @a + 1 |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

现在来使用session变量来call一下procedure:

# 调用procedure
mysql> call p_init_tb_t2(1000000,@result);
Query OK, 0 rows affected (0.00 sec)

# 查看返回值
mysql> select @result;
+---------+
| @result |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)


# 调用procedure
mysql> call p_init_tb_t2(10,@result);
Query OK, 1 row affected (0.34 sec)

# 查看返回值
mysql> select @result;
+---------+
| @result |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

# 查看t2中的数据
mysql> select * from t2;
+----+------+------------+------+------------+
| id | nid  | cid        | n1   | c1         |
+----+------+------------+------+------------+
|  1 |  522 | RRRRRRRRRR |  522 | RRRRRRRRRR |
|  2 |   69 | TTTTTTTTTT |   69 | TTTTTTTTTT |
|  3 |  609 | ssssssssss |  609 | ssssssssss |
|  4 |  949 | oooooooooo |  949 | oooooooooo |
|  5 |  559 | IIIIIIIIII |  559 | IIIIIIIIII |
|  6 |   71 | pppppppppp |   71 | pppppppppp |
|  7 |  325 | pppppppppp |  325 | pppppppppp |
|  8 |  533 | ssssssssss |  533 | ssssssssss |
|  9 |  196 | FFFFFFFFFF |  196 | FFFFFFFFFF |
| 10 |  939 | UUUUUUUUUU |  939 | UUUUUUUUUU |
+----+------+------------+------+------------+
10 rows in set (0.00 sec)

可以看出存储过程的SQL编程并不像其它高级语言那么方便, 没有高级的数据结构, 甚至连基础的数组都没有,所以这也是SQL Server和Oracle的同学特别喜欢用临时表的重要原因. 这个例子在处理退出时也用到了类似GOTO的用法,所以要写好SQL程序还是需要多多适应它才行, 实际应用时需要扬长避短.

procedure的删除,修改,查看和function是一样的,这里不再复述.

最后总结一下
看完这篇文章,你至少了解了如下内容:

进一步了解function和procedure的区别
session变量的使用
使用条件判断语法
使用循环语法
使用LEAVE退出procedure
学会编写一个利于自己工作procedure

还有一个异常处理的语法, 将在触发器章节中介绍.

返回目录