MySQL存储过程和函数

存储过程和函数概述

事先经过编译并存储在数据库中的一段SQL语句集合，可以简化开发人员的工作，减少数据在数据库和应用服务之间的传输

函数：有返回值的过程；

过程：没有返回值的过程；

创建存储过程

CREATE PROCEDURE pre_test()
BEGIN
	SELECT * from city;
end

调用

call `过程名`

查询存储过程

SELECT name from mysql.proc where db="数据库名"

删除存储过程

DROP PROCEDURE `过程名`

变量

-- 声明变量
CREATE PROCEDURE pro_test()
    BEGIN
    DECLARE `变量名` `变量类型` DEFAULT `默认类型`;
    SELECT CONCAT('num的值:',num);
end

-- 为变量进行赋值 set
CREATE PROCEDURE pro_test1()
BEGIN
    DECLARE num int DEFAULT 0;
    set num = num+11;
    select num;
end
-- 为变量进行赋值 into
CREATE PROCEDURE pro_test2()
BEGIN
    DECLARE num int;
    SELECT COUNT(*) into num from city;
    SELECT num;
end

if条件判断

CREATE PROCEDURE pro_test3()
BEGIN
    DECLARE height int DEFAULT 175;
    DECLARE description VARCHAR(50) DEFAULT '';
    if height >=180 then set description = '身材高挑';
    ELSEIF height <180 AND height >=170 then set description = '身材标准';
    ELSE SET description = '身材一般';
    END IF;
    SELECT CONCAT("身高:",height,"-身材类型:",description);
end

传递参数

-- in表示传入参数  inout既可以传入也可以输出
CREATE PROCEDURE pro_test3(in height int)
BEGIN
    DECLARE description VARCHAR(50) DEFAULT '';
    if height >=180 then set description = '身材高挑';
    ELSEIF height <180 AND height >=170 then set description = '身材标准';
    ELSE SET description = '身材一般';
    END IF;
    SELECT CONCAT("身高:",height,"-身材类型:",description);
end

-- out表示输出参数   @表示用户方接受的参数：用户会话变量  @@系统变量
CREATE PROCEDURE pro_test3(in height int,out description VARCHAR(50))
BEGIN
    if height >=180 then set description = '身材高挑';
    ELSEIF height <180 AND height >=170 then set description = '身材标准';
    ELSE SET description = '身材一般';
    END IF;
end

call pro_test3(190,@description);
SELECT @description

--

CASE语句

CREATE PROCEDURE pro_test4(in mouth int)
BEGIN
    DECLARE result varchar(10);
    CASE
        WHEN mouth >=1 and mouth<=3 THEN set result = '第一季度';
        WHEN mouth >=4 and mouth<=6 THEN set result = '第二季度';
        WHEN mouth >=7 and mouth<=9 THEN set result = '第三季度';
        ELSE set result = '第四季度';
        END CASE;
        SELECT CONCAT("当前月份:",mouth,"-当前季度:",result) 'result';
END

WHILE循环

-- 1到n求和
CREATE PROCEDURE pro_test5(in n INT)
BEGIN
	DECLARE total int DEFAULT 0;
	DECLARE num int DEFAULT 1;

	WHILE num<=n DO
		set total = total+num;
		set num = num+1;
	END WHILE;
	SELECT total;
	
END

REPEAT循环

-- 满足条件退出循环，和while相反
-- 注意：util后面不加；不然报错
CREATE PROCEDURE pro_test6(in n INT)
BEGIN
	DECLARE total int DEFAULT 0;
		
	REPEAT
		set total = total + n;
		set n = n-1;
		UNTIL n=0
	END REPEAT;
	SELECT total;
END

LOOP循环

-- 需要借助leave
CREATE PROCEDURE pro_test7(in n INT)
BEGIN
	DECLARE total int DEFAULT 0;
		
	c:LOOP
		set total = total + n;
		set n = n-1;
		IF n<=0 THEN LEAVE c;
		END IF;
	END LOOP c;

	SELECT total;
END

游标

CREATE PROCEDURE pro_test8()
BEGIN
	DECLARE c_id INT;
	DECLARE c_name VARCHAR(10);
	DECLARE c_country_id INT;
	DECLARE c_country VARCHAR(10);
	
	DECLARE has_data INT DEFAULT 1;

	--新建一个游标
	DECLARE city_result CURSOR for SELECT c.*,t.country_name FROM city c,country t where c.country_id = t.country_id;
	--如果fetch取不到数据则会触发这句话
	DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;

	OPEN city_result;
		
	REPEAT
	-- 每fetch一次，游标向下移动一次
		FETCH city_result into c_id,c_name,c_country_id,c_country;
		SELECT CONCAT("id=",c_id,",name=",c_name,",cid=",c_country_id,",country=",c_country) "result";
		
		UNTIL has_data = 0
	END REPEAT;

	CLOSE city_result;
END

函数

-- 主要是多了返回值
CREATE FUNCTION fun(countryId INT)
RETURNS INT
BEGIN
	DECLARE num INT;
	SELECT COUNT(*) into num FROM city where country_id = countryId;	
	RETURN num;
END

-- 通过select调用
select fun(1)

触发器

-- 这里的new表示插入的数据 如果是old则表示修改前的数据
CREATE TRIGGER `名字`
AFTER INSERT --表示插入之后执行 可以更改 BEFORE UPDATE DELETE 
on `表名`
for EACH ROW --表示行级

BEGIN 
	
	INSERT into city_logs VALUES(null,'insert',now(),new.city_id,CONCAT('插入id=',new.city_id,',插入name=',new.city_name));

end