判断当前过程是否存在
DROP PROCEDURE IF EXISTS `proc_test`;
创建PROCEDURE
CREATE PROCEDURE `proc_test` (
IN report_year VARCHAR (50),
IN date_type VARCHAR (50),
IN report_date VARCHAR (50),
IN finance_status VARCHAR (50),
IN subject_type VARCHAR (50),
IN user_id VARCHAR (200),
OUT o_err_code VARCHAR (50),
OUT o_err_msg VARCHAR (4000)
)
声明游标、变量
DECLARE cur_index CURSOR FOR SELECT t.xx_name FROM xx_table t;
DECLARE vc_name VARCHAR (500);
DECLARE is_down INT DEFAULT FALSE;
声明变量、给变量赋值
SET @l_id = null;
SET o_err_code = '0';
SET o_err_msg = '运行成功';
异常捕获
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done = TRUE;
declare CONTINUE handler for sqlwarning
begin
GET DIAGNOSTICS condition 1 o_err_code = RETURNED_SQLSTATE, o_err_msg= MESSAGE_TEXT;
select o_err_code,o_err_msg;
end;
declare CONTINUE handler for sqlexception
begin
GET DIAGNOSTICS condition 1 o_err_code = RETURNED_SQLSTATE, o_err_msg= MESSAGE_TEXT;
select o_err_code,o_err_msg;
end;
打开、遍历、关闭游标
OPEN cur_index;
read_loop :LOOP
FETCH cur_index INTO vc_name;
IF is_done THEN
LEAVE read_loop;
END IF;
IF is_flag THEN
SET is_flag = FALSE;
ELSE
SELECT concat('vc_name ==',vc_name);
END IF;
END LOOP;
CLOSE cur_index;
打开事务管理
START TRANSACTION;
IF o_err_code = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
获取插入主键(特殊情况)
SELECT auto_increment INTO @l_id
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA= DATABASE()
AND TABLE_NAME='xx_table';