存储过程
2018-12-28 本文已影响0人
Nick_4438
介绍
存储过程是一种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();
