项目刚开始时,系统数据量不是很大,润乾报表中的数据显示都是直接使用SQL查询得出数据进而显示,随着后面数据量的变多,数据显示越来越慢,应公司领导要求,把润乾报表中的SQL实现改成由存储过程实现。
那么润乾报表调用Oracle数据存储过程实现数据显示呢?关于这个问题网上很少有说明,在此记下自已亲自实现的笔记。
首先,先把原来在润乾报表中使用的SQL语句全部放到Oracle中改成存储过程实现,最后把SQL查询得出的数据通过返回游标在润乾报表中显示。
以下是原来润乾报表中SQL实现:
SELECT T.ID_KEY,
T.STAFF_ID,
P.SYS_USER_CD USER_CD,
P.SYS_CD_NM USER_NM,
T.WHETHER_WORK,
TO_CHAR(T.KQ_DATE,'YYYY-MM-DD') KQ_DATE,
T.CARD_NUM,
T.TIME_TYPE,
TO_CHAR(T.WORK_TIME,'HH24:MI') WORK_TIME,
TO_CHAR(T.START_TIME,'HH24:MI') START_TIME,
TO_CHAR(T.END_TIME,'HH24:MI') END_TIME,
TO_CHAR(T.WORK_TIME_OFF,'HH24:MI') WORK_TIME_OFF,
TO_CHAR(T.START_TIME_OFF,'HH24:MI') START_TIME_OFF,
TO_CHAR(T.END_TIME_OFF,'HH24:MI') END_TIME_OFF,
TO_CHAR(T.WORK_TIME,'YYYY-MM-DD HH24:MI') WORK_DT,
TO_CHAR(T.WORK_TIME_OFF,'YYYY-MM-DD HH24:MI') WORK_DT_OFF,
T.SYS_COMM,T.FINAL_RESULT,(select A.ITEM_MARK from KQ.KQ_ITEM_SET A WHERE A.ITEM_NUM=S.ITEM_ID) FINAL_RESULT_ID
FROM KQ.KQ_RECORD T,KQ.KQ_PERSON P,KQ_ITEM_SET S
WHERE T.STAFF_ID=P.SYS_ID_KEY AND T.FINAL_RESULT_ID=S.ITEM_NUM AND T.KQ_DATE BETWEEN ?
AND ?
${dept_sql} ${where_sql}
ORDER BY T.KQ_DATE
改为存储过程实现:
create or replace procedure p_recordQuery_ds_main(start_date in varchar2, end_date in varchar2, dept_id in number,where_sql in varchar2,
ds_main OUT SYS_REFCURSOR --声明游标 游标作为输出参数供润乾报表中使用
) is
--声明变量
dept_sql VARCHAR2(2000);
ds_main_sql VARCHAR2(4000);
ds_main_dept_sql VARCHAR2(4000);
ds_main_where_sql VARCHAR2(4000);
ds_main_ordersql VARCHAR2(4000);
ds_main_datesql VARCHAR2(4000);
ds_main_sqls VARCHAR2(4000);
begin
--判断部门id
if dept_id is not null and dept_id !=-1 then
dept_sql:='AND EXISTS (SELECT 1
FROM KQ.KQ_DEPT T,
(SELECT K.SYS_LEV_CD || ''%''SYS_LEV_CD
FROM KQ.KQ_DEPT K
WHERE K.SYS_ID_KEY = '|| dept_id ||') T1
WHERE T.SYS_LEV_CD LIKE T1.SYS_LEV_CD
AND P.DEPT_ID = T.SYS_ID_KEY)';
end if;
--组合动态拼接sql
ds_main_dept_sql :=dept_sql;
ds_main_where_sql :=where_sql;
ds_main_sql :='SELECT T.ID_KEY,
T.STAFF_ID,
P.SYS_USER_CD USER_CD,
P.SYS_CD_NM USER_NM,
T.WHETHER_WORK,
TO_CHAR(T.KQ_DATE,''YYYY-MM-DD'') KQ_DATE,
T.CARD_NUM,
T.TIME_TYPE,
TO_CHAR(T.WORK_TIME,''HH24:MI'') WORK_TIME,
TO_CHAR(T.START_TIME,''HH24:MI'') START_TIME,
TO_CHAR(T.END_TIME,''HH24:MI'') END_TIME,
TO_CHAR(T.WORK_TIME_OFF,''HH24:MI'') WORK_TIME_OFF,
TO_CHAR(T.START_TIME_OFF,''HH24:MI'') START_TIME_OFF,
TO_CHAR(T.END_TIME_OFF,''HH24:MI'') END_TIME_OFF,
TO_CHAR(T.WORK_TIME,''YYYY-MM-DD HH24:MI'') WORK_DT,
TO_CHAR(T.WORK_TIME_OFF,''YYYY-MM-DD HH24:MI'') WORK_DT_OFF,
T.SYS_COMM,
T.FINAL_RESULT,
(select A.ITEM_MARK from KQ.KQ_ITEM_SET A WHERE A.ITEM_NUM=S.ITEM_ID) FINAL_RESULT_ID
FROM KQ.KQ_RECORD T,KQ.KQ_PERSON P,KQ_ITEM_SET S
WHERE T.STAFF_ID=P.SYS_ID_KEY AND T.FINAL_RESULT_ID=S.ITEM_NUM ';
ds_main_ordersql :=' ORDER BY T.KQ_DATE ';
ds_main_datesql :=' AND T.KQ_DATE BETWEEN TO_DATE('''||start_date||''',''yyyy-MM-DD'') AND TO_DATE('''||end_date||''',''yyyy-MM-DD'') ';
--ds_main_dept_sql :=' AND T.KQ_DATE BETWEEN '||start_dates||' AND '||end_dates||' ';
ds_main_sqls :=ds_main_sql || ds_main_datesql || ds_main_dept_sql || ds_main_where_sql || ds_main_ordersql;
dbms_output.put_line(start_date);
dbms_output.put_line(end_date);
-- ds_main
dbms_output.put_line(ds_main_sqls);
OPEN ds_main FOR ds_main_sqls;--使用游标 拼接sql查询出来的值全部放到ds_main游标中
end p_recordQuery_ds_main;
使用润乾报表设计器:
填定义的存储过程,传入参数与传出参数使?占位
填写存储过程执行需要的参数,最后一个为输出参数,其类型为游标
在润乾报表要显示的数据都存在了游标里,在润乾报表中使用:
比如: ds_main_p.STAFF_ID,其中ds_main_p是润乾报表输出参数中的游标类型。
最后显示: