存储过程

介绍

存储过程是一种sql的语句集，其优点如下：

• 灵活，比sql更加灵活；
• 减少流量，调用存储过程会减少应用与数据库服务器之间通讯流量及时间 ；
• 相对批量执行来说，有更高的执行效率；

使用

语法

DROP PROCEDURE IF EXISTS `过程名`;
CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) 过程体

• 如下BEGIN和END之间是过程体
• fun 过程名
• 带一个int类型的输出参数s

DELIMITER ;;
  CREATE PROCEDURE fun(OUT s int)
    BEGIN
      // sql 
    END
DELIMITER ;

简单存储过程

DROP PROCEDURE IF EXISTS `funa`;

DELIMITER ;;
CREATE  PROCEDURE `funa`()
BEGIN
  SELECT 1;
END;;
DELIMITER ;

CALL funa() ;

• 如上定义了一个存储过程funa，并且调用了该存储过程；
• DELIMITER 定义语句的结束符号；

带输入参数

# define
DROP PROCEDURE IF EXISTS `funa`;

DELIMITER ;;
CREATE PROCEDURE funa(IN p int)
  BEGIN
    SELECT p;
  END;
;;
DELIMITER ;

# call
SET @p=1;
CALL funa(@p);

参数输出

# define 
DROP PROCEDURE IF EXISTS `funa`;
DELIMITER ;;
CREATE PROCEDURE funa(OUT p_out int)
  BEGIN
    SELECT p_out;
    SET p_out=2;
    SELECT p_out;
  END;
;;
DELIMITER ;
# call
SET @p_out=1;
CALL funa(@p_out);
SELECT @p_out;

• 如上，执行可以发现第一个到第三个select 分别返回null,2,2，可见参数可以返回输出到类型外；

输入输出

# define 
DROP PROCEDURE IF EXISTS `funa`;
DELIMITER ;;
CREATE PROCEDURE funa(INOUT p_out int)
  BEGIN
    SELECT p_out;
    SET p_out=2;
    SELECT p_out;
  END;
;;
DELIMITER ;
# call
SET @p_out=1;
CALL funa(@p_out);
SELECT @p_out;

• 如上，执行可以发现第一个到第三个select 分别返回1,2,2，可见外部参数可输入到函数内，并且参数可以返回输出到类型外；

变量

• 变量声明，语法：DECLARE 变量名1[,变量名2...] 数据类型 [默认值];
• 变量类型支持mysql的所有变量；
• 变量赋值，语法：SET 变量名 = 变量值 [,变量名= 变量值 ...]
• 用户变量： 用户变量一般以@开头，因为用户变量为全局的变量，所以建议少使用用户变量

# define 
DROP PROCEDURE IF EXISTS `funa`;
DELIMITER ;;
CREATE PROCEDURE funa()
  BEGIN
      DECLARE a1 int default 12;
        SELECT  a1;
        SET a1=13;
        SELECT  a1;
  END;
;;
DELIMITER ;
# call
CALL funa();

程序逻辑

条件(IF-THEN-ELSE语句)

#条件语句IF-THEN-ELSE
DROP PROCEDURE IF EXISTS proc;
DELIMITER ;;
CREATE PROCEDURE proc(IN parameter int)
  BEGIN
    DECLARE var int;
    SET var=parameter+1;
    IF var=0 THEN
       SELECT 0;
    END IF ;
    IF parameter=0 THEN
      select "=0";
    ELSE
      select ">0";
    END IF ;
  END ;
  ;;
DELIMITER ;

call proc(-1);

CASE-WHEN-THEN-ELSE语句

DROP PROCEDURE IF EXISTS proc;
DELIMITER ;;
  CREATE PROCEDURE proc (IN param INT)
    BEGIN
      CASE param
        WHEN 0 THEN
          SELECT 0;
        WHEN 1 THEN
           SELECT 1;
        ELSE
           SELECT 1;
      END CASE ;
    END ;
  ;;
DELIMITER ;
CALL proc(1)

循环语句

# WHILE-DO…END-WHILE
DROP PROCEDURE IF EXISTS proc;
DELIMITER ;;
  CREATE PROCEDURE proc()
    BEGIN
      DECLARE var INT;
      SET var=0;
      WHILE var<6 DO
        SELECT var;
        SET var=var+1;
      END WHILE ;
    END;
  ;;
DELIMITER ;
call proc();

# REPEAT...END REPEAT
# 此语句的特点是执行操作后检查结果
DROP PROCEDURE IF EXISTS proc;
DELIMITER ;;
  CREATE PROCEDURE proc ()
    BEGIN
      DECLARE v INT;
      SET v=0;
      REPEAT
        SELECT v;
        SET v=v+1;
        UNTIL v>=5
      END REPEAT;
    END;
  ;;
DELIMITER ;
call proc();

# LOOP...END LOOP
DROP PROCEDURE IF EXISTS proc;
DELIMITER ;;
  CREATE PROCEDURE proc ()
    BEGIN
      DECLARE v INT;
      SET v=0;
      LOOP_LABLE:LOOP
        SELECT v;
        SET v=v+1;
        IF v >=5 THEN
          LEAVE LOOP_LABLE;
        END IF;
      END LOOP;
    END;
  ;;
DELIMITER ;
call proc();

应用

游标遍历select结果

DROP TABLE IF EXISTS `tbl_job`;
CREATE TABLE `tbl_job` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `JOB_NAME` varchar(32) NOT NULL COMMENT '职位名称',
  `OCCUPANT_ID` int(11) NOT NULL COMMENT '任职者',
  `AGE` int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`ID`)
);
-- ----------------------------
-- Records of tbl_job
-- ----------------------------
INSERT INTO `tbl_job` VALUES ('1', '经理', '1', '21'),
('2', '董事长', '2', '21'),
('3', '项目组长', '3', '22'),
('4', 'SE', '4', '24'),
('5', 'MDE', '5', '24');

DROP PROCEDURE IF EXISTS proc_tbl_job;
DELIMITER ;;
CREATE PROCEDURE proc_tbl_job(IN inId INT)
BEGIN
    DECLARE id INT;
    DECLARE jobName VARCHAR(32);
    DECLARE occupantId INT;
    DECLARE age INT;
    
    DECLARE done INT DEFAULT FALSE;
    DECLARE curJob CURSOR FOR ( -- 定义
        SELECT ID,JOB_NAME,OCCUPANT_ID,AGE FROM tbl_job -- WHERE ID = inId
    );
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 监听器
    
    OPEN curJob; -- 打开游标
    label:LOOP
        FETCH curJob INTO id,jobName,occupantId,age;
        IF done THEN LEAVE label;END IF;
        SELECT id,jobName,occupantId,age;
    END LOOP label;
    
    CLOSE curJob; -- 关闭游标
END ;;
delimiter;
 
CALL proc_tbl_job(2)

复杂嵌套

DROP PROCEDURE IF EXISTS proc_syn_single_blacklist;
DELIMITER ;;
CREATE PROCEDURE proc_syn_single_blacklist(IN var_license VARCHAR(10))
BEGIN
    DECLARE var_calc_amount_owed INT DEFAULT 0;  
    DECLARE var_calc_paid_in_money INT DEFAULT 0;
    DECLARE var_calc_arrears_count INT DEFAULT 0;
    
    SELECT License ,IFNULL(SUM(ReceivablesMoney),0),IFNULL(SUM(PaidInMoney),0),IFNULL(COUNT(*),0)
        into var_license, var_calc_amount_owed,var_calc_paid_in_money,var_calc_arrears_count
            FROM  urpcs_evasion_arrears WHERE  license=var_license;
    SELECT var_license,var_calc_amount_owed,var_calc_paid_in_money,var_calc_arrears_count;
END ;;
DELIMITER;

DROP PROCEDURE IF EXISTS proc_syn_blacklist;
DELIMITER ;;
CREATE PROCEDURE proc_syn_blacklist()
BEGIN

    DECLARE var_license VARCHAR(10);
    DECLARE done INT DEFAULT FALSE;
    DECLARE curJob CURSOR FOR ( -- 定义
        SELECT DISTINCT license  FROM  urpcs_evasion_arrears ORDER BY EvasionID LIMIT 2 
    );
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 监听器
    
    OPEN curJob; -- 打开游标
    label:LOOP
        FETCH curJob INTO var_license;
        IF done THEN LEAVE label;END IF;
                call proc_syn_single_blacklist(var_license);
    END LOOP label;
    
    CLOSE curJob; -- 关闭游标
END ;;
DELIMITER;

 
CALL proc_syn_blacklist();