mysql存储过程及调用_MySql存储过程及调用

存储过程：

CREATE PROCEDURE `select_AppraiseShow_Panel3_2`(IN p_id INT,IN year_start VARCHAR(20),IN year_over VARCHAR(20),IN selUnit varchar(50),IN pageNo INTEGER,IN pageSize INTEGER,OUT totalSize INTEGER)

BEGIN

DECLARE idx INT DEFAULT 0;-- 表名标号

DECLARE t_name VARCHAR(50); -- 拼接后的表名

DECLARE t_tabYoN VARCHAR(10);-- 是否有这张表

-- DECLARE error_num INTEGER DEFAULT 0; -- 是否有错误发生

-- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error_num=1;

DECLARE sqlStr varchar(4000) default ''; -- 拼接SQL

DECLARE sqlStr2 VARCHAR(4000) DEFAULT '';

DECLARE $start INTEGER DEFAULT 0;

-- START TRANSACTION;-- 开启事务

IF p_id=1 THEN

-- year_start ==> 2017-02-13

set t_name = CONCAT('t_ace_appraise_',LEFT(year_start,4),SUBSTR(year_start,6,2));

IF EXISTS (SELECT 1 FROM information_schema.`TABLES` where TABLE_NAME=t_name) THEN

SET sqlStr = CONCAT(sqlStr,"select CONCAT('",year_start," ',LPAD(T.stime,2,'0'),'时') as stime,T.description,count(T.pk_id) as counts",

" from","(",

" select o.pk_id,hour(o.stime) as stime,o.u_pk,seu.description",

" from t_ace_appraise_",LEFT(year_start,4),SUBSTR(year_start,6,2)," as o RIGHT JOIN t_setup_unit as seu on o.u_pk=seu.pk_id and seu.id LIKE '%",selUnit,"%'",

" where o.stime<='",year_over," 23:59:59' and o.stime>='",year_start," 00:00:00' and o.ace15Ld4 > 0 and o.remission = 0 ",

" ) as T",

" group by t.u_pk,T.stime ORDER BY t.u_pk,t.stime");

END IF;

END IF;

IF p_id=3 THEN

loop_num:LOOP

set idx=idx+1;

set t_name = CONCAT('t_ace_appraise_',year_start,LPAD(idx,2,'0'));

IF EXISTS (select 1 FROM information_schema.`TABLES` WHERE table_name=t_name) THEN

set sqlStr = CONCAT(sqlStr," select CONCAT('",year_start,"-',LPAD(T.stime,2,'0')) as stime,T.description,count(T.pk_id) as counts",

" from"," (select o.pk_id,month(o.stime) as stime,o.u_pk,tsu.description"," from ",t_name," as o"," RIGHT JOIN t_setup_unit tsu on o.u_pk = tsu.pk_id AND tsu.id like '%",selUnit,"%'",

" where o.stime<='",year_over,"-",LPAD(idx,2,'0'),"-31 23:59:59' and o.stime>='",year_start,"-",LPAD(idx,2,'0'),"-01 00:00:00' and o.ace15Ld4 > 0 and o.remission = 0 ",

" ) as T group by t.u_pk,T.stime");

set sqlStr = CONCAT(sqlStr,' UNION ALL');

END IF;

IF idx>12 THEN

LEAVE loop_num;

END IF;

END LOOP;

set sqlStr = LEFT(sqlStr,LENGTH(sqlStr)-9);

set sqlStr = CONCAT('select Y.stime,Y.description,Y.counts from (',sqlStr,') as Y order by Y.description,Y.stime asc');

END IF;

-- 计算总数

SET sqlStr2 = CONCAT("select count(*) into @totalSize from (",sqlStr,") as tc");

set @sql2 = sqlStr2;

PREPARE stat2 FROM @sql2;

EXECUTE stat2;

DEALLOCATE PREPARE stat2;

-- 设置分页

IF pageNo IS NOT NULL AND pageSize IS NOT NULL AND pageSize!=-1 THEN

IF pageNo < 0 THEN SET pageNo = 0; END IF;

IF pageSize < 0 THEN SET pageSize = 10; END IF;

SET $start = (pageNo-1)*pageSize;

-- SET $start = pageNo;

SET sqlStr = CONCAT(sqlStr," limit ",$start,",",pageSize);

END IF;

-- 获取列表

set @sql1 = sqlStr;

PREPARE stat FROM @sql1;

EXECUTE stat;

DEALLOCATE PREPARE stat;

SET totalSize = @totalSize;

-- 判断中途是否出错

-- IF error=1 THEN

-- ROLLBACK;

-- ELSE

-- COMMIT;

-- END IF;

END调用:

public PageResult>> callProcedureForPagedata1(String procName, Map inParams,

Map outParams) throws SQLException {

Connection con = SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection();

ResultSet rs = null;

CallableStatement call = con.prepareCall("{CALL " + procName + "}");

// 设置输入参数

if (inParams != null && !inParams.isEmpty()) {

for (String one : inParams.keySet()) {

call.setObject(one, inParams.get(one));

}

}

// 注册输出参数

outParams.put("totalSize", Types.INTEGER);

if (outParams != null && !outParams.isEmpty()) {

for (String one : outParams.keySet()) {

call.registerOutParameter(one, outParams.get(one));

}

}

call.execute();

rs = call.getResultSet();// 获取查询结果集

List list = new ArrayList();

// 将结果集封装出List类型的集合

while (rs != null && rs.next()) {

Map rowData = new HashMap();

ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();

int columnCount = md.getColumnCount();

for (int i = 1; i <= columnCount; i++) {

rowData.put(md.getColumnLabel(i), rs.getObject(i));

}

list.add(rowData);

}

long totalSize = call.getLong("totalSize");// 总记录数的输出参数

int pageNo = inParams.get("pageNo") == null ? 0 : (int) inParams.get("pageNo");

int pageSize = (int) (inParams.get("pageSize") == null ? totalSize : (int) inParams.get("pageSize"));

PageResult>> pageResult = new PageResult(pageNo, totalSize, pageSize, list);

// 释放资源

call.close();

con.close();

return pageResult;

}