1. 产妇信息
2. 产检项目表
3. 产检时间表
存储过程:添加一个产妇信息时,为产检时间表添加关联产检项目的数据
和触发器有些类似点
CREATE DEFINER=`root`@`%` PROCEDURE `ms_cfcj`(IN ORGID VARCHAR (64),IN ORGNAME VARCHAR (64),IN CUNID VARCHAR (64),IN CUNNAME VARCHAR (64),IN CFXM VARCHAR (64),IN LXFS VARCHAR (64),IN HYRQ VARCHAR (64), IN SFZ VARCHAR (64),IN YYMC VARCHAR(64))
BEGIN
#产妇信息主键id
DECLARE CFXXID VARCHAR(64);
#产妇信息产检时间表主键id
DECLARE CJSJID VARCHAR(64);
#判断游标数据是否查完
DECLARE flag INT DEFAULT 1;
#孕期结束时间记录
DECLARE HYRQ_END VARCHAR(64);
#产检项目截止日期
DECLARE CJXMSJ_END VARCHAR(64);
DECLARE MS_CJZQ VARCHAR(64);
DECLARE MS_XMID VARCHAR(64);
#DECLARE MS_CJXM VARCHAR(64);
#DECLARE MS_CJFY VARCHAR(64);
#DECLARE MS_CJCS VARCHAR(64);
#创建游标,并存储数据
DECLARE ms_cf CURSOR FOR
#SELECT CJZQ, CJXM, CJFY, CJCS FROM ms_hy_cjxm;
SELECT CJZQ,XMID FROM ms_hy_cjxm;
#游标中的内容执行完后将flag设置为0
DECLARE EXIT HANDLER FOR NOT FOUND SET flag := 0;
#SQL异常处理
#DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
#START TRANSACTION;
SET CFXXID = UUID();
SELECT date_add( HYRQ , interval 40 week) INTO HYRQ_END;
INSERT INTO ms_hy_cfxx (
CFXXID, ORGID, ORGNAME, CUNID, CUNNAME,
CFXM, LXFS, HYRQ, YCRQ, SFZ, YYMC)
VALUES (
CFXXID, ORGID, ORGNAME, CUNID, CUNNAME,
CFXM, LXFS, HYRQ, HYRQ_END, SFZ, YYMC);
#打开游标
OPEN ms_cf;
#执行循环
REPEAT
#取游标中的值
#FETCH ms_cf INTO MS_CJZQ,MS_CJXM,MS_CJFY,MS_CJCS;
FETCH ms_cf INTO MS_CJZQ,MS_XMID;
#主键ID赋值
SET CJSJID = UUID();
SELECT date_add( HYRQ , interval MS_CJZQ week) INTO CJXMSJ_END;
#执行插入操作
INSERT INTO ms_hy_cfxx_cjsj (
CJSJID, CFXXID,XMID, JZRQ, TXZT)
VALUES (
CJSJID, CFXXID, MS_XMID, CJXMSJ_END, 0);
UNTIL flag = 0
END REPEAT;
#释放游标
CLOSE ms_cf;
SELECT 1;
#COMMIT;
END
触发器:当添加一种产检项目时,为产检时间表添加关联产检项目的数据
insert into ms_hy_cfxx_cjsj
(CJSJID,
CFXXID,
XMID,
TXZT,
JZRQ)
select UUID(),
new.XMID,
fxu.CFXXID,
0,
DATE_ADD(fxu.HYRQ,INTERVAL new.CJZQ week)
from ms_hy_cfxx fxu
5. sql分割特殊字符
CREATE DEFINER=`root`@`%` PROCEDURE `f_split`(IN c VARCHAR (2000), IN split VARCHAR (2), IN table_name VARCHAR(50))
BEGIN
-- 参数依次是:字符串,分隔符,临时表名(可缺省)
-- 因为使用了动态表名,因此采用了prepare语句操作SQL
set table_name = trim(table_name);
set @tablename = IF(ISNULL(table_name) or CHAR_LENGTH(table_name)=0,'f_split_temp', table_name);
-- 创建临时表
set @sql = concat('create TEMPORARY TABLE ', @tablename, '(
col varchar(50)
)');
PREPARE stmt from @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
WHILE (instr(c, split) <> 0) DO #循环遍历字符串
set @sql = concat('INSERT into ', @tablename, ' VALUES(', substring(c, 1, instr(c, split)-1), ')');
PREPARE stmt from @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET c = insert(c, 1, instr(c, split), '');
END WHILE;
-- 将结果插入临时表
set @sql = concat('INSERT into ', @tablename, ' values("', c,'")');
PREPARE stmt from @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
使用
package com.zhiruan.base.dao;
import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.annotations.Param;
public interface SysUserAuthDao {
/**
* 新增角色权限(菜单,页面,按钮权限)
* @param map
* {
* roleid:"1024",
* creator:"系统管理员",
* creatorid:"361"
* menulist:[
* menu_id
* ],
* pagelist:[
* page_id
* ],
* btnlist:[
* btn_id
* ]
* }
* @return
*/
/* public int create(HashMap<String, Object> map); */
public void create(HashMap<String, Object> map);
/**
* 菜单,页面,按钮权限结构树
* @param roleid 权限id
* @return
* [
* {
* id:"id"
* pid:"父id",
* name:"名称",
* type:"类型" , //0:菜单1:页面2:按钮
* open:"", //默认true
* checked:"true/false"//是否选中
* },
* {
* ......
* }
* ]
*/
public List<HashMap<String, Object>> tree(@Param("role_id")String role_id,@Param("sysid")String sysid);
/**
* 获取多角色的权限
* @param role_ids 角色id拼接
* @return
* [
* [ //拥有权限的菜单列表
* role_id:"" ,
* menu_id:"" ,
* menu_code:"" ,
* menu_name:"" ,
* menu_url:"" ,
* menu_icon:"" ,
* menu_endflg:"" ,
* menu_level:""
* ],
* [//拥有权限的页面列表
* role_id:"" ,
* page_id:"" ,
* page_name:"" ,
* page_url
* ],
* [//拥有权限的按钮列表
* role_id:"" ,
* btn_id:"" ,
* btn_code:"" ,
* btn_name:"" ,
* btn_type
* ]
* ]
*/
public List<?> auth(@Param("role_ids")String role_ids);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.zhiruan.base.dao.SysUserAuthDao" >
<resultMap id="menu" type="java.util.HashMap" >
<result column="role_id" property="role_id" jdbcType="INTEGER" />
<result column="menu_id" property="menu_id" jdbcType="VARCHAR" />
<result column="menu_code" property="menu_code" jdbcType="VARCHAR" />
<result column="menu_name" property= "menu_name" jdbcType="VARCHAR"/>
<result column="menu_url" property= "menu_url" jdbcType="VARCHAR"/>
<result column="sys_url" property= "sys_url" jdbcType="VARCHAR"/>
<result column="menu_icon" property="menu_icon" jdbcType="DATE" />
<result column="menu_endflg" property= "menu_endflg" jdbcType="VARCHAR"/>
<result column="menu_level" property= "menu_level" jdbcType="VARCHAR"/>
<result column="menu_seq" property= "menu_seq" jdbcType="VARCHAR"/>
<result column="menu_pid" property= "menu_pid" jdbcType="VARCHAR"/>
</resultMap>
<resultMap id="page" type="java.util.HashMap" >
<result column="role_id" property="role_id" jdbcType="INTEGER" />
<result column="page_id" property="page_id" jdbcType="VARCHAR" />
<result column="page_name" property="page_name" jdbcType="VARCHAR" />
<result column="page_url" property= "page_url" jdbcType="VARCHAR"/>
</resultMap>
<resultMap id="btn" type="java.util.HashMap" >
<result column="role_id" property="role_id" jdbcType="INTEGER" />
<result column="btn_id" property="btn_id" jdbcType="VARCHAR" />
<result column="btn_code" property="btn_code" jdbcType="VARCHAR" />
<result column="btn_name" property= "btn_name" jdbcType="VARCHAR"/>
<result column="btn_type" property= "btn_type" jdbcType="VARCHAR"/>
<result column="page_id" property="page_id" jdbcType="VARCHAR" />
<result column="btn_multi_flg" property="btn_multi_flg" jdbcType="VARCHAR" />
</resultMap>
<delete id="deleteAuth">
<!-- 删除按钮权限 -->
delete from fx_btn_auth where role_id = #{role_id};
<!-- 删除页面权限 -->
delete from fx_page_auth where role_id = #{role_id};
<!-- 删除菜单权限 -->
delete from fx_menu_auth where role_id = #{role_id}
</delete>
<insert id="createMenuAndPage">
<if test=' menulist != null and menulist.size > 0 ' >
<!-- 新增菜单权限 -->
insert into fx_menu_auth (menu_id,role_id,creatorid,creator,createtime)
VALUES
<foreach collection="menulist" item="item" separator=",">
(#{item},#{role_id},#{creatorid},#{creator},sysdate())
</foreach>
;
</if>
<if test=' pagelist != null and pagelist.size > 0 ' >
<!-- 新增页面权限 -->
insert into fx_page_auth(page_id,role_id,creatorid,creator,createtime)
VALUES
<foreach collection="pagelist" item="item" separator=",">
(#{item},#{role_id},#{creatorid},#{creator},sysdate())
</foreach>
</if>
</insert>
<insert id="createBtn">
<if test=' btnlist != null and btnlist.size > 0 ' >
<!-- 新增按钮权限 -->
insert into fx_btn_auth(btn_id,role_id,creatorid,creator,createtime)
VALUES
<foreach collection="btnlist" item="item" separator=",">
(#{item},#{role_id},#{creatorid},#{creator},sysdate())
</foreach>
</if>
</insert>
<select id="create" parameterType="java.util.HashMap" resultType="java.lang.Integer">
declare @errorcode int
set @errorcode = 0
begin tran <!-- 开启事务 -->
begin try <!-- 开启捕获 -->
<!-- 删除按钮权限 -->
delete from fx_btn_auth where role_id = #{role_id}
<!-- 删除页面权限 -->
delete from fx_page_auth where role_id = #{role_id}
<!-- 删除菜单权限 -->
delete from fx_menu_auth where role_id = #{role_id}
<if test=' menulist != null and menulist.size > 0 ' >
<!-- 新增菜单权限 -->
insert into fx_menu_auth(menu_id,role_id,creatorid,creator,createtime)
values
<foreach collection="menulist" item="item" separator=",">
(#{item},#{role_id},#{creatorid},#{creator},getdate())
</foreach>
</if>
<if test=' pagelist != null and pagelist.size > 0 ' >
<!-- 新增页面权限 -->
insert into fx_page_auth(page_id,role_id,creatorid,creator,createtime)
values
<foreach collection="pagelist" item="item" separator=",">
(#{item},#{role_id},#{creatorid},#{creator},getdate())
</foreach>
</if>
<if test=' btnlist != null and btnlist.size > 0 ' >
<!-- 新增按钮权限 -->
insert into fx_btn_auth(btn_id,role_id,creatorid,creator,createtime)
values
<foreach collection="btnlist" item="item" separator=",">
(#{item},#{role_id},#{creatorid},#{creator},getdate())
</foreach>
</if>
end try
begin catch <!-- 捕获 -->
set @errorcode = 1
rollback tran <!-- 由于出错,这里回滚到开始,第一条语句也没有插入成功。 -->
end catch
if(@errorcode>0)
begin
select -1
end
else
begin
select 1
end
commit tran <!-- 提交事务 -->
</select>
<!-- 获取btn 权限树结构 -->
<select id="tree" resultType="java.util.HashMap">
select a.menu_id id ,a.menu_pid pId,a.menu_name name,0 type,'true' open,
case when b.menu_auth_id is not null then 'true' else 'false' end checked
from fx_menu a left join fx_menu_auth b on a.menu_id = b.menu_id and b.role_id = #{role_id}
where a.menu_enable = 1 and a.sysid =#{sysid}
union all
select c.page_id id, c.menu_id pId,c.page_name name,1 type ,'true' open,
case when d.page_auth_id is not null then 'true' else 'false' end checked
from fx_page c left join fx_page_auth d on c.page_id = d.page_id and d.role_id = #{role_id}
where c.page_enable = 1
union all
select e.btn_id id ,e.page_id pId,e.btn_name name,2 type ,'true' open,
case when f.btn_auth_id is not null then 'true' else 'false' end checked
from fx_btn e left join fx_btn_auth f on e.btn_id = f.btn_id and f.role_id = #{role_id}
where e.btn_enable = 1
</select>
<!-- 获取用户权限 -->
<select id="auth" resultMap="menu,page,btn">
<!-- 获取菜单权限 -->
CALL f_split(#{role_ids},',','');
select a.role_id ,b.menu_seq,b.menu_id,b.menu_pid,b.menu_code,b.menu_name,b.menu_url,b.menu_icon,b.menu_endflg,b.menu_level
from fx_menu_auth a inner join fx_menu b on a.menu_id = b.menu_id
where b.menu_enable = 1 and a.role_id in(SELECT col FROM f_split_temp)
group by a.role_id,b.menu_seq,b.menu_id,b.menu_pid,b.menu_code,b.menu_name,b.menu_url,b.menu_icon,b.menu_endflg,b.menu_level
order by b.menu_seq asc;
<!-- 获取页面权限 -->
select c.role_id,d.page_id, d.page_name,d.page_url from fx_page_auth c inner join fx_page d on d.page_id = c.page_id
where d.page_enable = 1 and c.role_id in(SELECT col FROM f_split_temp)
group by c.role_id, d.page_id, d.page_name,d.page_url;
<!-- 获取按钮权限 -->
select e.role_id,f.btn_id ,f.btn_code,f.btn_name,f.btn_type,
f.page_id,f.btn_multi_flg
from fx_btn_auth e inner join fx_btn f on e.btn_id = f.btn_id where f.btn_enable = 1
and e.role_id in(SELECT col FROM f_split_temp)
group by e.role_id,f.btn_id ,f.btn_code,f.btn_name,f.btn_type ,f.page_id,f.btn_multi_flg;
drop temporary table f_split_temp;
</select>
</mapper>