动态sql

客官°小女子只卖身不卖艺 2022-04-21 15:50 151阅读 0赞

在做调查模块的答题报表，由于每个调查问卷的题目数是变动的，要用到动态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;  
\}