1、SQL-存储过程
1、简单的存储过程
定义:
drop procedure if exists f_mis;
create PROCEDURE f_mis(IN p_masterId int,IN p_serverId int,IN p_attributeId int,OUT p_mapKey varchar(256),OUT p_attributeValue varchar(256))
BEGIN
SELECT CONCAT(masterId,serverId,attributeId), attributeValue into `p_mapKey`, `p_attributeValue`
FROM table_xxx
where masterId=`p_masterId` and serverId=`p_serverId` and attributeId=`p_attributeId` and instantId='1800';
END
注意:varchar(256)类型必须指定长度,否则报错
调用:
call f_mis(65,64,72,@p_mapkey,@p_value);
select @p_mapkey,@p_value;
注意:入参65,64,72为int型,不能带单引号
2、合并列值存储过程
drop procedure if exists f_append_value;
create PROCEDURE f_append_value()
BEGIN
-- 用来接收游标循环时的字段值
DECLARE i_instant_id int;
DECLARE a_attribute_value VARCHAR(1000);
-- 声明游标
DECLARE append_value CURSOR for SELECT t.instantId, t.attributeValue FROM table_xxx t;
-- 声明退出循环的条件:当找不到下一条记录时,退出游标
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE append_value;
-- 返回值,或者存储过程运行的结果
SET @result = '';
-- open 光标
OPEN append_value;
-- 循环拼接字符串
REPEAT
FETCH append_value INTO i_instant_id, a_attribute_value;
IF i_instant_id = 1800 THEN
SET @result = CONCAT(a_attribute_value,'---',@result);
END IF;
UNTIL 0 END REPEAT;
-- close 光标
CLOSE append_value;
END
调用:
call f_append_value();
select @result;
存储过程:
https://www.cnblogs.com/huaxingtianxia/p/5628828.html
报表
存储过程游标
https://blog.csdn.net/datouniao1/article/details/81903738
https://blog.csdn.net/m0_37897502/article/details/80911727