动态sql

在做调查模块的答题报表，由于每个调查问卷的题目数是变动的，要用到动态sql，拼接sql，以及动态游标（引用游标）：故开发了存储过程来处理。
create or replace package PAK_SURVEY is

-- Author : XLSHENG
-- Created : 2014/2/21 10:45:40
-- Purpose :

-- Public type declarations
TYPE retcursor IS REF CURSOR;
FUNCTION pro_count_survey_select_report(surveyId IN varchar2) RETURN number;
PROCEDURE pro_survey_select_report(surveyId IN varchar2,
outcurse OUT retcursor);
PROCEDURE pro_page_select_report(surveyId IN varchar2,page in number,pageSize in number,sortName varchar2,sord varchar2,
outcurse OUT retcursor);

end PAK_SURVEY;
/


create or replace package body PAK_SURVEY is

FUNCTION pro_count_survey_select_report(surveyId IN varchar2) RETURN number
IS
--surveyId varchar(40) := 110;
----str_query_select_question_sql 统计每个人的选择题答题情况
recordsCount number;
str_query_select_question_sql varchar2(4000) := '';
str_sql_parts_join varchar2(4000) := '';
str_sql_part1 varchar2(100) := ' wmsys.wm_concat(decode(t1.question_id,';
str_sql_part2 varchar2(80) := ',answer))';
counts number := 1;
cursor cur_query_question_subject(surveyId varchar2) is
select * from (
--查出问卷所有的选择题题目
select q.SUBJECT, q.question_id
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_SELECT_OPTION o,
TK_SURVEY_SELECT_ANSWER sa
where q.SURVEY_ID = s.SURVEY_ID
and o.QUESTION_ID = q.QUESTION_ID
and q.TYPE = 0
and s.SURVEY_ID = surveyId
and sa.select_question_option_id = o.SELECT_QUESTION_OPTION_ID
union all
--查出问卷所有的问答题题目
select q.SUBJECT, q.question_id
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_DIALOGICAL_ANSWER da
where q.SURVEY_ID = s.SURVEY_ID
and q.TYPE = 1
and da.question_id = q.question_id
and s.SURVEY_ID = surveyId)
group by question_id, SUBJECT order by question_id;
begin
for result in cur_query_question_subject(surveyId) loop
--dbms_output.put_line(result.subject);
str_sql_parts_join := str_sql_parts_join || str_sql_part1 || chr(39) ||
result.question_id || chr(39) || str_sql_part2 ||
'answer' || counts || ',';
counts := counts + 1;
end loop;
--dbms_output.put_line('str_sql_parts_join=' || str_sql_parts_join);
str_query_select_question_sql := 'select ' || str_sql_parts_join ||
't1.employee_id' ||
' from
(select *
from (
--选择题答案
select s.TITLE,
q.SUBJECT,
o.LABEL answer,
sa.employee_id,
q.question_id,
q.rank
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_SELECT_OPTION o,
TK_SURVEY_SELECT_ANSWER sa
where q.SURVEY_ID = s.SURVEY_ID
and o.QUESTION_ID = q.QUESTION_ID
and q.TYPE = 0
and s.SURVEY_ID = ' || surveyId ||'
and sa.select_question_option_id = o.SELECT_QUESTION_OPTION_ID
union all
--问答题答案
select s.TITLE,
q.SUBJECT,
da.dialogical_answer answer,
da.employee_id,
q.question_id,
q.rank
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_DIALOGICAL_ANSWER da
where q.SURVEY_ID = s.SURVEY_ID
and da.question_id = q.question_id
and q.TYPE = 1
and s.SURVEY_ID = ' || surveyId ||')
order by rank
) t1 group by employee_id';
dbms_output.put_line('str_query_select_question_sql= ' ||
str_query_select_question_sql);
str_query_select_question_sql := 'select count(*) from (' || str_query_select_question_sql || ')';
execute immediate str_query_select_question_sql into recordsCount;
return recordsCount;
end;

PROCEDURE pro_survey_select_report(surveyId IN varchar2,
outcurse OUT retcursor) IS
--surveyId varchar(40) := 110;
----str_query_select_question_sql 统计每个人的选择题答题情况

str_query_select_question_sql varchar2(4000) := '';
str_sql_parts_join varchar2(4000) := '';
str_sql_part1 varchar2(100) := ' wmsys.wm_concat(decode(t1.question_id,';
str_sql_part2 varchar2(80) := ',answer||chr(10)))';
counts number := 1;
cursor cur_query_question_subject(surveyId varchar2) is
select * from (
--查出问卷所有的选择题题目
select q.SUBJECT, q.question_id
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_SELECT_OPTION o,
TK_SURVEY_SELECT_ANSWER sa
where q.SURVEY_ID = s.SURVEY_ID
and o.QUESTION_ID = q.QUESTION_ID
and q.TYPE = 0
and s.SURVEY_ID = surveyId
and sa.select_question_option_id = o.SELECT_QUESTION_OPTION_ID
union all
--查出问卷所有的问答题题目
select q.SUBJECT, q.question_id
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_DIALOGICAL_ANSWER da
where q.SURVEY_ID = s.SURVEY_ID
and q.TYPE = 1
and da.question_id = q.question_id
and s.SURVEY_ID = surveyId)
group by question_id, SUBJECT order by question_id;
begin
for result in cur_query_question_subject(surveyId) loop
--dbms_output.put_line(result.subject);
str_sql_parts_join := str_sql_parts_join || str_sql_part1 || chr(39) ||
result.question_id || chr(39) || str_sql_part2 ||
'answer' || counts || ',';
counts := counts + 1;
end loop;
--dbms_output.put_line('str_sql_parts_join=' || str_sql_parts_join);
str_query_select_question_sql := 'select ' || str_sql_parts_join ||
't1.employee_id' ||
' from
(select *
from (
--选择题答案
select s.TITLE,
q.SUBJECT,
o.LABEL answer,
sa.employee_id,
q.question_id,
q.rank
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_SELECT_OPTION o,
TK_SURVEY_SELECT_ANSWER sa
where q.SURVEY_ID = s.SURVEY_ID
and o.QUESTION_ID = q.QUESTION_ID
and q.TYPE = 0
and s.SURVEY_ID = ' || surveyId ||'
and sa.select_question_option_id = o.SELECT_QUESTION_OPTION_ID
union all
--问答题答案
select s.TITLE,
q.SUBJECT,
da.dialogical_answer answer,
da.employee_id,
q.question_id,
q.rank
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_DIALOGICAL_ANSWER da
where q.SURVEY_ID = s.SURVEY_ID
and da.question_id = q.question_id
and q.TYPE = 1
and s.SURVEY_ID = ' || surveyId ||')
order by rank
) t1 group by employee_id';
dbms_output.put_line('str_query_select_question_sql= ' ||
str_query_select_question_sql);

OPEN outcurse FOR str_query_select_question_sql;
return;
end;


PROCEDURE pro_page_select_report(surveyId IN varchar2,page in number,pageSize in number,sortName varchar2,
sord varchar2,outcurse OUT retcursor)
IS
--surveyId varchar(40) := 110;
----str_query_select_question_sql 统计每个人的选择题答题情况
str_page_select_question_sql varchar2(4000) := '';
str_query_select_question_sql varchar2(4000) := '';
str_sql_parts_join varchar2(4000) := '';
str_sql_part1 varchar2(100) := ' wmsys.wm_concat(decode(t1.question_id,';
str_sql_part2 varchar2(80) := ',answer||chr(10)))';
counts number := 1;
cursor cur_query_question_subject(surveyId varchar2) is
select * from (
--查出问卷所有的选择题题目
select q.SUBJECT, q.question_id
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_SELECT_OPTION o,
TK_SURVEY_SELECT_ANSWER sa
where q.SURVEY_ID = s.SURVEY_ID
and o.QUESTION_ID = q.QUESTION_ID
and q.TYPE = 0
and s.SURVEY_ID = surveyId
and sa.select_question_option_id = o.SELECT_QUESTION_OPTION_ID
union all
--查出问卷所有的问答题题目
select q.SUBJECT, q.question_id
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_DIALOGICAL_ANSWER da
where q.SURVEY_ID = s.SURVEY_ID
and q.TYPE = 1
and da.question_id = q.question_id
and s.SURVEY_ID = surveyId)
group by question_id, SUBJECT order by question_id;
begin
for result in cur_query_question_subject(surveyId) loop
--dbms_output.put_line(result.subject);
str_sql_parts_join := str_sql_parts_join || str_sql_part1 || chr(39) ||
result.question_id || chr(39) || str_sql_part2 ||
'answer' || counts || ',';
counts := counts + 1;
end loop;
--dbms_output.put_line('str_sql_parts_join=' || str_sql_parts_join);
str_query_select_question_sql := 'select ' || str_sql_parts_join ||
't1.employee_id' || ',max(t1.title) title,
max(t1.is_anonymous) isAnonymous' ||
' from
(select *
from (
--选择题答案
select s.TITLE,
s.is_anonymous,
q.SUBJECT,
o.LABEL answer,
sa.employee_id,
q.question_id,
q.rank
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_SELECT_OPTION o,
TK_SURVEY_SELECT_ANSWER sa
where q.SURVEY_ID = s.SURVEY_ID
and o.QUESTION_ID = q.QUESTION_ID
and q.TYPE = 0
and s.SURVEY_ID = ' || surveyId ||'
and sa.select_question_option_id = o.SELECT_QUESTION_OPTION_ID
union all
--问答题答案
select s.TITLE,
s.is_anonymous,
q.SUBJECT,
da.dialogical_answer answer,
da.employee_id,
q.question_id,
q.rank
from TK_SURVEY s,
TK_SURVEY_QUESTION q,
TK_SURVEY_DIALOGICAL_ANSWER da
where q.SURVEY_ID = s.SURVEY_ID
and da.question_id = q.question_id
and q.TYPE = 1
and s.SURVEY_ID = ' || surveyId ||')
order by rank
) t1 group by employee_id';


str_page_select_question_sql := 'select t3.*,u.name
from ( select t2.*,rownum r from (' || str_query_select_question_sql || ' )' || ' t2 where rownum <=' || page* pageSize || ') t3' || ',sec_user u' || ' where t3.r > ' || (page-1)* pageSize || ' and t3.employee_id=u.staff_id' || ' order by ' || sortName ||' ' || sord;
dbms_output.put_line('str_page_select_question_sql= ' ||
str_page_select_question_sql);
OPEN outcurse FOR str_page_select_question_sql;
return;
end;
end PAK_SURVEY;

mybaits对应的配置：
<!-- 查询问卷的答题详情 -->
<select id="querySelectAnswerReport" parameterType="java.util.Map" statementType="CALLABLE">
{CALL PAK_SURVEY.PRO_SURVEY_SELECT_REPORT(#{surveyId}, #{resultList, mode=OUT, jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=selectAnswerMap} )}
</select>

<!-- 分页查询问卷的答题详情 -->
<select id="pageSelectAnswerReport" parameterType="java.util.Map" statementType="CALLABLE">
{CALL PAK_SURVEY.PRO_PAGE_SELECT_REPORT(#{surveyId},#{page},#{pageSize},#{sidx},#{sord}, #{resultList, mode=OUT, jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=selectAnswerMap} )}
</select>


mybatis spring Mapper dao：
public void querySelectAnswerReport(Map map);

public void pageSelectAnswerReport(Map map);


service 调用 dao：
/**
* 查询问卷的答卷报表-分页的
*/
@Transactional(readOnly=true,isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
public List<SurveyAnswerDetailsDTO> pageSelectAnswerReport(Integer surveyID, Integer page, Integer pageSize,String sidx,String sord) {
Map params = new HashMap();
params.put("surveyId", surveyID);
params.put("page", page);
params.put("pageSize", pageSize);
params.put("sidx", sidx);
params.put("sord", sord);

surveyReporterMybatisDao.pageSelectAnswerReport(params);
List<Map<String,String>> userAnswers = (List<Map<String,String>>) params.get("resultList");

List<SurveyAnswerDetailsDTO> result = new ArrayList<SurveyAnswerDetailsDTO>();
for (Map<String,String> map : userAnswers) {
Object userID = map.get("EMPLOYEE_ID");
String isAnonymous = map.get("ISANONYMOUS");
String name = map.get("NAME");
map.remove("EMPLOYEE_ID");
map.remove("ISANONYMOUS");
map.remove("NAME");
map.remove("R");
//String title = map.get("TITLE");
map.remove("TITLE");
TreeMap treeMap = new TreeMap();
treeMap.putAll(map);
SurveyAnswerDetailsDTO answerDetails = new SurveyAnswerDetailsDTO();

answerDetails.setIsAnonymous(isAnonymous);
if ("Y".equals(isAnonymous)) {
answerDetails.setUserID(null);
answerDetails.setName("--");
} else {
answerDetails.setUserID(userID);
answerDetails.setName(name);
}

answerDetails.setAnswers(treeMap.values());
//System.out.println(answerDetails);
result.add(answerDetails);
}
return result;
}