存储过程,一组为了完成特定功能的sql语句集,将复杂操作进行封装,经编译后存储在数据库中,用户只需调用过程名,避免输入冗余sql命令
1、创建存储过程
create procedure 存储过程名([参数列表])
begin
具体的procedure(处理)
end
2、调用(执行)存储过程
call存储过程名(参数1,…);
3、删除存储过程
drop procedure 过程名
1、创建游标 (cur_name 游标名字)
declare cur_name cursor for select 语句;
2、打开游标
open cur_name;
3、提取游标数据
fetch cur_name [into 变量1,变量2,…];
4、关闭(释放)游标
close/deallocate cur_name;
--指定分割符
DELIMITER $
--创建存储过程
CREATE PROCEDURE 过程名字([参数列表])
BEGIN
--定义变量(变量名 数据类型)
DECLARE 变量;
--定义游标,游标内容为表中选择项的结果集
DECLARE 游标名 CURSOR FOR SELECT 列 FROM 表 WHERE 条件;
--定义条件,满足条件就处理(结束标志绑定到游标)
DECLARE CONTINUE HANDLER FOR NOT FOUND SET 变量 = 1;
--打开游标,遍历
OPEN 游标名;
read_loop : LOOP
--FETCH遍历游标,将数据保存到变量中(提取单个行)
FETCH NEXT FROM 游标名 INTO 变量;
IF 变量 THEN
LEAVE read_loop ;
--条件判断,跳出
--执行操作,需要的处理(将变量的值插入表中对应字段)
--结束循环,关游标
END IF ;
需要执行的操作;
END LOOP;
CLOSE 游标名;
END$
DELIMITER $
CREATE PROCEDURE pro_uploadMaterials_info()
BEGIN
-- 定义变量
DECLARE done BOOLEAN;
DECLARE cattachment_id decimal(40,0);
DECLARE cwork_id decimal(40,0);
DECLARE cwork_id_temp decimal(40,0);
DECLARE cmaterial_id decimal(40,0);
DECLARE cmaterial_name varchar(600);
DECLARE cseq decimal(5,0);
DECLARE ccollect_type_code varchar(20);
DECLARE cis_collect varchar(5);
DECLARE ccollect_date DATETIME;
DECLARE cfile_name varchar(100);
DECLARE cpath varchar(1000);
DECLARE ccreate_date DATETIME;
DECLARE cupdate_date DATETIME;
DECLARE ccomments varchar(255);
-- 游标
DECLARE cur_materials_info CURSOR FOR SELECT
projid,
attrid,
attrname,
sortid,
taketype,
istake,
taketime,
filename,
entityurl_h,
create_time,
syncupdatetime,
remark
FROM
materials_info;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ; -- 将结束标志绑定到游标
OPEN cur_materials_info; -- 打开游标
read_loop : -- 遍历;
LOOP
FETCH NEXT FROM cur_materials_info INTO cwork_id_temp,cmaterial_id,cmaterial_name,cseq,ccollect_type_code,cis_collect,ccollect_date,cfile_name,cpath,ccreate_date,cupdate_date,ccomments; -- 取值 取多个字段
IF done THEN
LEAVE read_loop ;
END IF ;
SET cattachment_id = newSnowFlakeID();
SET cwork_id = (SELECT id FROM sg_sp_work WHERE WORK_NO = cwork_id_temp);
INSERT INTO sg_sp_work_materials (id,work_id,material_id,material_name,collect_type_code,is_collect,collect_date,create_date,update_date,comments,state,attach_id)
VALUES
(newSnowFlakeID(),cwork_id,cmaterial_id,cmaterial_name,ccollect_type_code,cis_collect,ccollect_date,ccreate_date,cupdate_date,ccomments,'A',cattachment_id);
INSERT INTO bfm_attachment (id,file_name,path,state,create_date,update_date)
VALUES
(cattachment_id,cfile_name,cpath,'A',ccreate_date,cupdate_date);
INSERT INTO sg_sp_work_materials_file (id,material_id,attach_id,seq,state)
VALUES
(newSnowFlakeID(),cmaterial_id,cattachment_id,cseq,'A');
END LOOP;
CLOSE cur_materials_info ; -- 关闭游标
END$