Mysql存储过程

1、什么是存储过程？

　　　我们操作的SQL语句在每次执行的时候需要先编译，再执行；而存储过程（Stroed Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中（有点类似于Java的static变量）。

　　　在我们需要使用的时候通过指定存储过程的名字并指定参数（如果该存储过程带有参数）来调用执行它。

　　　一个存储过程是一个可编程的函数，它在数据库中创建被保存，由SQL语句和一些特殊的控制结构语句组成。

2、存储过程的优点有哪些？

　　（1）增强SQL语言的功能和灵活性

　　　　存储过程可以使用流控制语句（IF...ELSE...）,有很强的灵活性，可以完成复杂的判断和运算。

　　（2）提升执行速度

　　　　如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度要快很多。因为存储过程是预编译的，在首次运行一个存储过程时，优化器对其进行分析优化，最终存储下来，不必每次都进行编译。

　　（3）减少网络流量

　　　　针对同一个数据库对象的操作（如查询、修改），Transaction-SQL语句可能比调用存储过程语句要复杂，所以使用存储过程降低了网络负载和网络流量。
　　（4）保证安全

　　　　系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

3、MySQL的存储过程

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

4、准备数据

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `ID` int(11) NOT NULL,
  `USERNAME` varchar(16) NOT NULL,
  `CITY` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `PHONE` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `name_city_phone` (`USERNAME`,`CITY`,`PHONE`),
  KEY `U_INDEX` (`USERNAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('4', 'Jack', '深圳', '185');
INSERT INTO `t_user` VALUES ('3', 'Jerry', '北京', '181');
INSERT INTO `t_user` VALUES ('1', 'parry', '广州', '180');
INSERT INTO `t_user` VALUES ('2', 'Tom', '上海', '189');

5、Mysql存储过程

　　（1）创建存储过程

CREATE PROCEDURE P_NAME()
BEGIN
    ......
END

　　（2）调用存储过程

CALL P_NAME() // 存储过程名称（P_NAME）后面必须添加括号，即使没有参数

　　（3）删除存储过程

DROP PROCEDURE P_NAME; //不能在一个存储过程中删除另一个存储古城，只能调用另一个存储过程

　　（4）查看数据库里全部的存储过程

SHOW PROCEDURE STATUS

　　（5）查看单个存储过程的详细信息

SHOW CREATE PROCEDURE P_NAME

6、创建存储过程详解

　　（1）简单的无参存储过程（查询所有的结果集）

DROP PROCEDURE IF EXISTS P_user;
CREATE PROCEDURE P_user()
BEGIN
    SELECT * FROM t_user;
END;

　　调用：CALL P_user();

　　

　　（2）带参存储过程

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

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

　　　　a.IN参数

/*创建存储过程*/
DROP PROCEDURE IF EXISTS P_user;
CREATE PROCEDURE P_user(IN t_id VARCHAR(10))
BEGIN
        SELECT * FROM t_user WHERE ID = t_id;
END;
/*调用存储过程*/
CALL P_user(1);

View Code

　　　　　　

　　　　b.OUT参数

/*创建存储过程*/
DROP PROCEDURE IF EXISTS P_user;
CREATE PROCEDURE P_user(IN t_id VARCHAR(10),OUT uCount INT)
BEGIN
        SELECT COUNT(*) FROM t_user WHERE ID = t_id;
END;
/*调用存储过程*/
CALL P_user(4,@p_num);
SLEEP(@p_num)

View Code

　　　　

　　　　c.INOUT参数（参数是入参也是出参）

/*创建存储过程*/
DROP PROCEDURE IF EXISTS P_user;
CREATE PROCEDURE P_user(INOUT t_id VARCHAR(10))
BEGIN
        SELECT COUNT(*) FROM t_user WHERE ID = t_id;
END;
/*调用存储过程*/
SET @p_num = 4;
CALL P_user(@p_num);
SELECT @p_num;

View Code

　　　　

　　（3）流程控制语句（如果ID被2整除，查询1,3；否则查询2,4）

　　　　IF...THEN

　　　　ELSEIF ...THEN

　　　　ELSE...

　　　　END IF

/*创建存储过程*/
DROP PROCEDURE IF EXISTS P_user;
CREATE PROCEDURE P_user(IN t_id VARCHAR(10))
BEGIN
    IF t_id%2 = 0 THEN
        SELECT * FROM t_user WHERE ID IN(2,4);
    ELSE
        SELECT * FROM t_user WHERE ID IN(1,3);
    END IF;
END;
/*调用存储过程*/
SET @p_num=1;
CALL P_user(@p_num);

View Code

　　　　　　

　　（4）while语句

　　　　while ... do

　　　　　　......

　　　　end while;

/*创建存储过程*/
DROP PROCEDURE IF EXISTS P_user;
CREATE PROCEDURE P_user()
BEGIN
    DECLARE temp INT;
    SET temp = 1;
    WHILE temp<5 DO
        SELECT * FROM t_user WHERE ID = temp;
        SET temp = temp + 1;
    END WHILE;
END;
/*调用存储过程*/
CALL P_user();

View Code

　　　　

　　　（5）repeat语句

　　　　　　repeat...

　　　　　　until ...

　　　　　　end repeat;

DROP PROCEDURE IF EXISTS P_user;
CREATE PROCEDURE P_user()
BEGIN
    DECLARE temp INT;
    SET temp = 1;
    REPEAT
        SELECT * FROM t_user WHERE ID = temp;
        SET temp = temp + 1;
    UNTIL temp>=5
    END REPEAT;
END;
/*调用存储过程*/
CALL P_user();

View Code