10,606
社区成员
发帖
与我相关
我的任务
分享
delimiter $$
use `familyaccountmanagement`$$
drop procedure if exists `deleteRole`$$
create definer=`root`@`localhost` procedure `deleteRole`(uid varchar(1000))
_return:begin
declare rids varchar(8); -- 角色ID
declare roleids varchar(8); -- 角色编号
declare rolnams varchar(32);-- 角色名称
declare fanames varchar(60);-- 家庭名称
declare ruids varchar(8); -- 该角色在s_roleusers这个表中有多少条记录
declare total int default 0; -- 这个删除角色的有多少个
declare i int default 0;
set @total = func_split_TotalLength(uid, ',');
while i < @total do
set i = i + 1;
set @roleids = '0';
set @rids = func_split(uid, ',', i);
select roleid, rolnam, faname into @roleids, @rolnams, @fanames from `s_role` where rid = @rids;
select count(*) into @ruids from `s_roleusers` where ru_rid = @roleids;
select @ruids;
if @ruids = 0 then
delete from `s_role` where rid = @rids;
else
set @rolnams = concat(@fanames, '下面的(', @rolnams, ')角色还有用户,请先删掉用户!');
select 'false' success, @rolnams errorMsg;
leave _return;
end if;
end while;
select 'true' success, '角色删除成功' errorMsg;
end$$
delimiter ;
public Object executeHql(String hql, Object[] params) {
Session s = this.getCurrentSession();
s.beginTransaction();
//hql == {call deleteRole(?)}
SQLQuery q = s.createSQLQuery(hql);
for (int i = 0; i < params.length; i++) {
if (params[i] instanceof String) {
q.setString(i, (String) params[i]);
} else if(params[i] instanceof Integer) {
q.setInteger(i, (Integer) params[i]);
} else if(params[i] instanceof Long){
q.setLong(i, (Long) params[i]);
} else if(params[i] instanceof Double){
q.setDouble(i, (Double) params[i]);
} else if(params[i] instanceof Date){
q.setDate(i, (Date) params[i]);
}
}
ScrollableResults sr = q.scroll();
while (sr.next()) {
Object str1 = sr.get(0);
System.out.println(str1.toString());
//这里输出来的结果为整数
}
s.getTransaction().commit();
return sr;
}