MySQL进阶（一）存储过程

存储过程是数据库对象之一，存储过程可以理解成数据库的子程序，在客户端和服务器端可以直接调用它

在一般的关系型数据库中，可以在数据库中定义子程序，这种程序块称为存储过程（procedure）。它存放在数据字典中，可以在不同用户和应用程序直接共享，并可实现程序的优化和重用。使用存储过程的有点如下：

• 过程在服务器端运行，执行速度快
• 过程执行一次后代码就驻留在高速缓冲存储器，在以后的操作中，在以后的操作中只需从高速缓冲存储器中调用已编译代码执行，提搞了系统性能。
• 确保数据库安全。可以不授权用户直接访问应用程序中的一些表，而是授权用户执行访问这些表的过程。非表的授权用户除非通过过程，否则就不能访问这些表。
• 自动完成需要预先执行的任务。过程可以在系统自动启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。

存储过程的创建和执行

语法格式：

CREATE 
    [DEFINER = { user | CURRENT_USER }] /* s授权用户*/
PROCEDURE procedure_name ([proc_parameter[,...]])  /* 定义过程名字和参数*/
    BEGIN
			sql_statement   /*定义过程体*/
		END

创建用户表：

DROP TABLE IF EXISTS `myuser`;
CREATE TABLE `myuser` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

创建用过id查找用户姓名过程：

CREATE PROCEDURE by_id_select_username(IN user_id INTEGER)
BEGIN
SELECT username FROM myuser
WHERE id=user_id;
END

查看创建的存储过程：

SHOW PROCEDURE STATUS;

执行创建过程：

CALL by_id_select_username(1);

存储过程的参数

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

PROCEDURE procedure_name ([[IN |OUT |INOUT ] param_name param_type...])

IN参数

DELIMITER $$
CREATE PROCEDURE cou_total(IN num INT)
BEGIN
  DECLARE n INT DEFAULT 0;
  DECLARE total INT DEFAULT 0;
  WHILE n<=num DO
    SET total := n + total;
    SET n := n+1;
  END WHILE;
  SELECT total, n;
END$$

CALL cou_total(10);

调用结果如下：

out参数

统计用户数量然后将结果传出：

DELIMITER $$

CREATE PROCEDURE user_count(out cou INT)
BEGIN
	SELECT COUNT(*) INTO cou
	FROM myuser;
END

DELIMITER ;

set @cou=1; /*定义变量和初始化*/

SELECT @cou; /*此处输出1*/

call user_count(@cou);  /*调用存储过程*/

select @cou; /*此处输出用户数量*/

inout参数

DELIMITER $$
CREATE PROCEDURE user_count_(INOUT num INT)
BEGIN
  DECLARE total INT(11);
  IF num = 10 THEN
     SET total := num+1;
  ELSE 
     SET total := num+2;
  END IF;
  SET num = total;  /*返回结果*/
END $$

DELIMITER;

SET @num=10;

SELECT @num;

CALL user_count_(@num);

SELECT @num;

删除存储过程：

DROP PROCEDURE procedure_name;

参考链接：
存储过程参数的具体详解