如果报表逻辑非常复杂的话, 可以把报表逻辑放到存储过程里,加工一个全局临时表。
前端查询的时候只查询临时表即可。只是第一次查询需要忍受加工的时间。
--创建存储过程,返回SYS_REFCURSOR
CREATE OR REPLACE PROCEDURE P_GET_AGENT(V_AGENT_CATE IN VARCHAR2,
V_PAGE IN INTEGER,
O_CURSOR OUT SYS_REFCURSOR) IS
BEGIN
INSERT INTO TMP_TAB_T_AGENT(AGENT_ID)
SELECT AGENT_ID FROM T_AGENT TA WHERE TA.AGENT_CATE = V_AGENT_CATE;
OPEN O_CURSOR FOR
SELECT AGENT_ID
FROM (SELECT ROWNUM AS RN, AGENT_ID
FROM (SELECT AGENT_ID FROM TMP_TAB_T_AGENT ORDER BY AGENT_ID)
WHERE ROWNUM < V_PAGE * 10)
WHERE RN >= (V_PAGE - 1) * 10;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('wrong');
END;
--调用存储过程,
DECLARE
V_CURSOR SYS_REFCURSOR;
V_AGENT_ID T_AGENT.AGENT_ID%TYPE;
BEGIN
-- Call the procedure
P_GET_AGENT(V_AGENT_CATE => '5', V_PAGE => 4, O_CURSOR => V_CURSOR);
LOOP
FETCH V_CURSOR
INTO V_AGENT_ID;
EXIT WHEN V_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_AGENT_ID);
END LOOP;
CLOSE V_CURSOR;
COMMIT;
END;