MYSQL存储过程与函数
存储过程
http://www.shouce.ren/api/view/a/11694
https://blog.csdn.net/yanluandai1985/article/details/83656374
delimiter
mysql默认以分号为结束符，可以通过delimiter关键字来自定义结束符。
模板
$$ 符号只是标记可以换其他
DROP PROCEDURE IF EXISTS procname;
DELIMITER $$
CREATE PROCEDURE procname(in inparam,out outparam)
BEGIN
--SQL
END $$
DELIMITER ;
查看存储过程
show create procedure procname;
有输入输出的过程
注意参数的大小写，mysql是不分大小写的
数据表
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (
id INT NOT NULL PRIMARY KEY COMMENT '编号',
age SMALLINT UNSIGNED NOT NULL COMMENT '年龄',
name VARCHAR(16) NOT NULL COMMENT '姓名'
) COMMENT '用户表';
in参数
-------------- 带in参数--------------
/*设置结束符为$*/
DELIMITER $
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc2 $
/*创建存储过程proc2*/
CREATE PROCEDURE proc2(id int,age int,in name varchar(16))
BEGIN
INSERT INTO t_user VALUES (id,age,name);
END $
/*将结束符置为;*/
DELIMITER ;
/*创建了3个自定义变量*/
-- SELECT @id:=2,@age:=1,@name:='张学友'; --不建议
SET @id=1,@age=1,@name='张学友';
/*调用存储过程*/
CALL proc2(@id,@age,@name);
SELECT * FROM t_user;
out参数
truncate TABLE t_user;
INSERT INTO t_user VALUES (1,44,'刘德华 '),(2,44,'郭富城 '),(5,48,'成龙 ');
select * from t_user;
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc3;
/*设置结束符为$*/
DELIMITER $
/*创建存储过程proc3*/
CREATE PROCEDURE proc3(out user_count int,out max_id INT)
BEGIN
SELECT COUNT(*),max(id) into user_count,max_id from t_user;
END $
/*将结束符置为;*/
DELIMITER ;
CALL proc3(@user_count,@max_id);
SELECT @user_count,@max_id
inout参数
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc4;
/*设置结束符为$*/
DELIMITER $
/*创建存储过程proc4*/
CREATE PROCEDURE proc4(INOUT a int,INOUT b int)
BEGIN
SET a = a*2;
select b*2 into b;
END $
/*将结束符置为;*/
DELIMITER ;
/*创建了2个自定义变量*/
set @a=10,@b=20;
/*调用存储过程*/
CALL proc4(@a,@b);
SELECT @a,@b
参考：
--存储过程名和参数，参数中in表示传入参数，out标示传出参数，inout表示传入传出参数
DROP PROCEDURE IF EXISTS `p_procedurecode`;
create procedure p_procedurecode(in sumdate varchar(10))
begin
declare v_sql varchar(500); --需要执行的SQL语句
declare sym varchar(6);
declare var1 varchar(20);
declare var2 varchar(70);
declare var3 integer;
--定义游标遍历时，作为判断是否遍历完全部记录的标记
declare no_more_departments integer DEFAULT 0;
--定义游标名字为C_RESULT
DECLARE C_RESULT CURSOR FOR
SELECT barcode,barname,barnum FROM tmp_table;
--声明当游标遍历完全部记录后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_departments=1;
set sym=substring(sumdate,1,6); --截取字符串，并将其赋值给一个遍历
--连接字符串构成完整SQL语句，动态SQL执行后的结果记录集，在MySQL中无法获取，因此需要转变思路将其放置到一个临时表中（注意代码中的写法）。一般写法如下：
-- 'Create TEMPORARY Table 表名（Select的查询语句）;
set v_sql= concat('Create TEMPORARY Table tmp_table(select aa as aacode,bb as aaname,count(cc) as ccnum from h',sym,' where substring(dd,1,8)=''',sumdate,''' group by aa,bb)');
set @v_sql=v_sql; --注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
prepare stmt from @v_sql; --预处理需要执行的动态SQL，其中stmt是一个变量
EXECUTE stmt; --执行SQL语句
deallocate prepare stmt; --释放掉预处理段
OPEN C_RESULT; --打开之前定义的游标
REPEAT --循环语句的关键词
FETCH C_RESULT INTO VAR1, VAR2, VAR3; --取出每条记录并赋值给相关变量，注意顺序
--执行查询语句，并将获得的值付给一个变量 @oldaacode（注意如果以@开头的变量可以不用通过declare语句事先声明）
select @oldaacode:=vcaaCode from T_sum where vcaaCode=var1 and dtDate=sumdate;
if @oldaacode=var1 then --判断
update T_sum set iNum=var3 where vcaaCode=var1 and dtDate=sumdate;
else
insert into T_sum(vcaaCode,vcaaName,iNum,dtDate) values(var1,var2,var3,sumdate);
end if;
UNTIL no_more_departments END REPEAT; --循环语句结束
CLOSE C_RESULT; --关闭游标
DROP TEMPORARY TABLE tmp_table; --删除临时表
end;
使用事务
https://www.runoob.com/mysql/mysql-transaction.html
CREATE TABLE testproc(id INT(4) PRIMARY KEY,NAME VARCHAR(100));
-- 没有事务，第一个插入成功
DROP PROCEDURE IF EXISTS test_proc;
DELIMITER &&
CREATE PROCEDURE test_proc(IN i_id INT,IN i_name VARCHAR(100))
BEGIN
INSERT INTO testproc VALUES (i_id, i_name); -- 语句1 插入成功了
INSERT INTO testproc VALUES (i_id, i_name); -- 语句2（因为id为PK，此语句将出错）。
END
&&
DELIMITER ;
CALL test_proc(2,'1')
SELECT * FROM testproc
--事务中出错
DROP PROCEDURE IF EXISTS test_proc2;
DELIMITER &&
CREATE PROCEDURE test_proc2(IN i_id INT,IN i_name VARCHAR(100))
BEGIN
START TRANSACTION;
INSERT INTO testproc VALUES (i_id, i_name); -- 语句1
INSERT INTO testproc VALUES (i_id+1, i_name); -- 语句2（因为id为PK，此语句将出错）。
COMMIT;
END
&&
DELIMITER ;
--
CALL test_proc2(3,'1');
SELECT * FROM testproc;
--
DROP PROCEDURE IF EXISTS test_proc3;
DELIMITER &&
CREATE PROCEDURE test_proc3(IN i_id INT,IN i_name VARCHAR(100))
BEGIN
DECLARE v_commit INT DEFAULT 2;
DECLARE msg MEDIUMTEXT;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
get diagnostics condition 1 msg = message_text;
set v_commit = 0;
END;
START TRANSACTION;
INSERT INTO testproc VALUES (i_id, i_name); -- 语句1
INSERT INTO testproc VALUES (i_id, i_name); -- 语句2（因为id为PK，此语句将出错）。
IF v_commit = 0 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
SELECT msg;
END
&&
DELIMITER ;
/*
CALL test_proc3(7,'1');
SELECT * FROM testproc;
*/
事务中获取执行的错误
DROP PROCEDURE IF EXISTS proc;
DELIMITER &&
CREATE PROCEDURE proc()
BEGIN
--声明变量
DECLARE v_commit INT DEFAULT 2;
DECLARE msg MEDIUMTEXT;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
get diagnostics condition 1 msg = message_text;
set v_commit = 0;
END;
START TRANSACTION;
/*
sql语句
*/
IF v_commit = 0 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
--错误的原因
SELECT msg;
END
&&
DELIMITER ;
存储过程里的变量
-- 有输入输出参数
DELIMITER //
DROP PROCEDURE IF EXISTS `CountFruit` //
CREATE PROCEDURE CountFruit(in FruitId int,out FruitPrice DECIMAL(10,2) )
BEGIN
#声明局部变量
DECLARE var1 ,var4 ,var5 VARCHAR(20);
#赋值
SET var1='qing';
#返回qing
SELECT var1;
#用户变量var2不声明直接赋值
SET @var2 = var1;
#qing
SELECT @var2;
#使用IF,返回2
SET @var3 = IF(FruitId<10,FruitId,FruitId+2);
SELECT @var3;
#流程控制
IF FruitId<10 THEN
SET var4='var4';
SET var5='var5';
ELSE
SET var4='var44';
SET var5='var55';
END IF;
SELECT var4,var5;
#WHILE 语句
WHILE FruitId>0 DO
SET @FruitIdTest = FruitId;
SET FruitId = FruitId-1;
SELECT @FruitIdTest;
END WHILE;
/*
将结果返回到out
变量中
*/
SELECT price INTO FruitPrice FROM fruit where id=2;
--动态执行sql
SET @strSQL ='SELECT * FROM fruit where id=2';
PREPARE stmt1 FROM @strSQL; -- 预处理动态sql语句
EXECUTE stmt1; -- 执行sql语句
DEALLOCATE PREPARE stmt1;-- 释放prepare
END
//
DELIMITER ;
/* 调用并获取out变量值
CALL CountFruit(2,@FruitPrice);
SELECT @FruitPrice AS FruitPrice;
*/
自定义函数
http://c.biancheng.net/view/2590.html
创建函数
create function 函数名(参数名称 参数类型)
returns 返回值类型
begin
函数体
end
参数是可选的。
返回值是必须的。
调用函数
select 函数名(实参列表);
删除函数
drop function [if exists] 函数名;
查看函数详细
show create function 函数名;
模版
/*删除fun1*/
DROP FUNCTION IF EXISTS fun1;
/*设置结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION fun1()
returns INT
BEGIN
DECLARE max_id int DEFAULT 0;
SELECT max(id) INTO max_id FROM t_user;
return max_id;
END $
/*设置结束符为;*/
DELIMITER ;
无参函数
/*删除fun1*/
DROP FUNCTION IF EXISTS fun1;
/*设置结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION fun1()
returns INT
BEGIN
DECLARE max_id int DEFAULT 0;
SELECT max(id) INTO max_id FROM t_user;
return max_id;
END $
/*设置结束符为;*/
DELIMITER ;
SELECT fun1();
有参函数
/*删除函数*/
DROP FUNCTION IF EXISTS get_user_id;
/*设置结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION get_user_id(v_name VARCHAR(16))
returns INT
BEGIN
DECLARE r_id int;
SELECT id INTO r_id FROM t_user WHERE name = v_name;
return r_id;
END $
/*设置结束符为;*/
DELIMITER ;
SELECT get_user_id(name) from t_user;
浙公网安备 33010602011771号