mysql存储过程

1. mysql存储过程实例

循环嵌套的时候，需要加上 BEGIN,END

	BEGIN
	#Routine body goes here...
		## 第一次循环，查询出需要的东西，
		DECLARE uuid,flowActiveInstanceId,modelId,flowActiveInstanceUserId VARCHAR(32);
		DECLARE done,insideDone INT;
		DECLARE listIdMap CURSOR FOR
			(SELECT a.id AS flowActiveInstanceId,b.model_id AS modelId FROM tabelA a LEFT JOIN tabelB b on a.flow_instance_id = b.id WHERE ***);
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =1;
		OPEN listIdMap ;
			cursor_loop:LOOP
				FETCH listIdMap INTO flowActiveInstanceId,modelId;
			IF done=1 THEN
				leave cursor_loop;
			END IF;
				## 更新本地库信息
				UPDATE tbaleC SET `status`='100' WHERE id=flowActiveInstanceId;
				SET insideDone =0;   ## 重置标识
				BEGIN
					## 内循环遍历出节点的通知人
					DECLARE insideId CURSOR FOR
						(SELECT id AS flowActiveInstanceUserId FROM flow_activity_instance_user WHERE flow_activity_instance_id = flowActiveInstanceId);
						DECLARE CONTINUE HANDLER FOR NOT FOUND SET insideDone =1;		
					OPEN insideId;
						inside_loop:LOOP
							FETCH insideId	INTO flowActiveInstanceUserId;
						IF insideDone =1 THEN
							LEAVE inside_loop;
						END IF;
								## 内层循环体
								UPDATE flow_activity_instance_user SET `status`='100',message='message',create_datetime=NOW() WHERE id =flowActiveInstanceUserId;
								SET uuid = REPLACE(UUID(), "-", "");
								INSERT INTO flow_activity_instance_message(id,flow_activity_instance_user_id,message,status,create_datetime) VALUES(uuid,flowActiveInstanceUserId,'message','1',NOW());
						END LOOP inside_loop;
					CLOSE insideId;
				END;
				
		END LOOP cursor_loop;
CLOSE listIdMap; 
		
END

2. Hibrenate调用存储过程

		Session session = baseManager.getSession();
        SQLQuery query=session.createSQLQuery("{Call Test(?)}");
        query.setString(0,"1623937219085347");
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map> resultList = query.list();
        if(resultList!=null&&resultList.size()>0){
            for (int i=0;i<resultList.size();i++){
                System.out.println(resultList.get(i).toString());
            }
        }