I have the following procedure that is used for returning paged resultsets using different methods (AND can be used with LARGE tables of data).
It is extremely simple concept:
* PageNumber=1, PageSize=10, SearchTerm='hello' - would return the first page of 10 records that contain the word 'hello'.
This concept is used ALOT with web applications (browsing pages with <<prev || next >> buttons).
OK, so now I simply want the following procedure rewritten with the following in mind:
* Remove and duplication or errors
* Is there a way to remove the requirement of temporary table use with the ROWCOUNT|SUBQUERY method?
* Optimise
--------------------------
----------
----------
----------
------
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE tmpNot CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
CREATE GLOBAL TEMPORARY TABLE tmpNot
ON COMMIT PRESERVE ROWS AS
SELECT PICKDETAILKEY
FROM PICKDETAIL
WHERE 1 = 2
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE tmpIn CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
CREATE GLOBAL TEMPORARY TABLE tmpIn
ON COMMIT PRESERVE ROWS AS
SELECT PICKDETAILKEY
FROM PICKDETAIL
WHERE 1 = 2
/
CREATE GLOBAL TEMPORARY TABLE PKTABLE (PK1 VARCHAR2(1) NOT NULL PRIMARY KEY)
ON COMMIT PRESERVE ROWS
/
CREATE OR REPLACE PROCEDURE spGetPagedPickDetails
(
PageNumber IN OUT INT DEFAULT 1,
PageSize IN OUT INT DEFAULT 10,
SearchText IN VARCHAR2 DEFAULT NULL,
Cur_Out IN OUT RCT1
)
AS
-- create likeText used for searching
likeText VARCHAR2(1);
-- determine paging variables
intStartRow INT;
PrimaryKey VARCHAR2(1);
CURSOR PagingCursor
IS
SELECT
PICKDETAILKEY
FROM
PICKDETAIL
ORDER BY
PICKDETAILKEY;
CURSOR PagingCursor
IS
SELECT
PICKDETAILKEY
FROM
PICKDETAIL
WHERE
ORDERKEY LIKE likeText
OR STORERKEY LIKE likeText
OR SKU LIKE likeText
ORDER BY
PICKDETAILKEY;
BEGIN
-- default page if bad input
IF PageNumber < 1 THEN
PageNumber := 1;
NULL;
END IF;
-- get the like text if search term provided
IF SearchText IS NOT NULL THEN
likeText := '%' || SearchText || '%';
NULL;
END IF;
-- use "SubQuery" method for close pages
IF( PageNumber >= 1 AND PageNumber < 20) THEN
BEGIN
-- works faster to just get TOP rows
IF( PageNumber = 1) THEN
BEGIN
IF( SearchText IS NULL) OR LENGTH(RTRIM(SearchText)) = 0 THEN
BEGIN
OPEN RCT1 FOR
SELECT
PICKDETAILKEY,
STORERKEY,
SKU
FROM
PICKDETAIL
ORDER BY
PICKDETAILKEY;
END;
ELSE
BEGIN
OPEN RCT1 FOR
SELECT
PICKDETAILKEY,
STORERKEY,
SKU
FROM
PICKDETAIL
WHERE
ORDERKEY LIKE likeText
OR STORERKEY LIKE likeText
OR SKU LIKE likeText
ORDER BY
PICKDETAILKEY;
END;
END IF;
END;--END TOP METHOD
ELSE
BEGIN
--SELECT * FROM PICKDETAIL WHERE PICKDETAILKEY IN
-- (SELECT TOP 10 PICKDETAILKEY FROM PICKDETAIL WHERE PICKDETAILKEY NOT IN
-- (SELECT TOP 10 PICKDETAILKEY FROM PICKDETAIL ORDER BY GENNUMBER ) ORDER BY GENNUMBER ) ORDER BY GENNUMBER
-- no search subquery
IF( SearchText IS NULL) THEN
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpNot';
INSERT INTO
tmpNot
SELECT
PICKDETAILKEY
FROM
PICKDETAIL
WHERE
ROWNUM <= PageSize
ORDER BY
PICKDETAILKEY;
EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpIn';
INSERT INTO
tmpIn
SELECT
PICKDETAILKEY
FROM
PICKDETAIL
WHERE
PICKDETAILKEY NOT IN ( SELECT * FROM tmpNot )
AND ROWNUM <= PageSize
--AND ROWNUM <= PageSize
ORDER BY
PICKDETAILKEY;
OPEN RCT1 FOR
SELECT
PICKDETAILKEY,
STORERKEY,
SKU
FROM
PICKDETAIL
WHERE
PICKDETAILKEY IN ( SELECT * FROM tmpIn )
ORDER BY
PICKDETAILKEY;
/* drop temporary tables (shouldnt need to but check anyways)*/
EXECUTE IMMEDIATE ' DROP TABLE tmpIn, tmpNot ';
END;
ELSE
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpNot';
INSERT INTO
tmpNot
SELECT
PICKDETAILKEY
FROM
PICKDETAIL
WHERE
(
ORDERKEY LIKE likeText
OR STORERKEY LIKE likeText
OR SKU LIKE likeText
)
AND ROWNUM <= PageSize
ORDER BY
PICKDETAILKEY;
EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpIn';
INSERT INTO
tmpIn
SELECT
PICKDETAILKEY
FROM
PICKDETAIL
WHERE
(
ORDERKEY LIKE likeText
OR STORERKEY LIKE likeText
OR SKU LIKE likeText
)
AND PICKDETAILKEY NOT IN ( SELECT * FROM tmpNot )
AND ROWNUM <= PageSize
--AND ROWNUM <= PageSize
ORDER BY
PICKDETAILKEY;
OPEN RCT1 FOR
SELECT
PICKDETAILKEY,
STORERKEY,
SKU
FROM
PICKDETAIL
WHERE
PICKDETAILKEY IN ( SELECT * FROM tmpIn )
ORDER BY
PICKDETAILKEY;
/* drop temporary tables (shouldnt need to but check anyways)*/
EXECUTE IMMEDIATE ' DROP TABLE tmpIn,tmpNot ' ;
END;
END IF;
END;
END IF; -- END SUBQUERY METHOD
END; -- END TOP AND SUBQUERY METHODS
ELSE
BEGIN
intStartRow := ( ( PageNumber - 1 ) * PageSize + 1 );
-- no searching required!
IF( SearchText IS NULL) THEN
OPEN PagingCursor;
FETCH PagingCursor INTO PrimaryKey;
WHILE PageSize > 0 AND PagingCursor%FOUND
LOOP
BEGIN
INSERT INTO
PKTABLE ( PK1 )
VALUES
( PrimaryKey );
FETCH PagingCursor INTO PrimaryKey;
PageSize := PageSize - 1;
END;
END LOOP;
-- close and dereference cursors
CLOSE PagingCursor;
/* return the paged set*/
OPEN RCT1 FOR
SELECT
PICKDETAILKEY,
STORERKEY,
SKU
FROM
PICKDETAIL t,
PKTABLE tt
WHERE
t.PICKDETAILKEY = tt.PK1
ORDER BY
PICKDETAILKEY;
END;
END IF;
END;
/
