2 CURSORS - access 1 cursor for processing??? [message #243258] |
Wed, 06 June 2007 11:13  |
sarans
Messages: 30 Registered: November 2006 Location: UK
|
Member |
|
|
I have 2 cursors with different where clause, and depends on any one given scenario, I need to open up one of the cursors and process it, the processing are same for both of the cursors, but opening and using the cursor depends on the business requirement, now all I want to know is how ot dynamically open up the cursor and process it.
If the above is not clear, please ask me and I will explain.
|
|
|
Re: 2 CURSORS - access 1 cursor for processing??? [message #243305 is a reply to message #243258] |
Wed, 06 June 2007 16:47   |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
one example using ref cursor
CREATE TABLE MY_TAB (
MY_TAB_PK NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(64) NOT NULL,
CREATE_DATE DATE NOT NULL);
-- Some rows !
INSERT INTO MY_TAB
(my_tab_pk, description, create_date)
VALUES (991, 'Description 1', SYSDATE);
INSERT INTO MY_TAB
(my_tab_pk, description, create_date)
VALUES (992, 'Description 2', SYSDATE - 1);
INSERT INTO MY_TAB
(my_tab_pk, description, create_date)
VALUES (993, 'Description 3', SYSDATE - 2);
INSERT INTO MY_TAB
(my_tab_pk, description, create_date)
VALUES (994, 'Description 4', SYSDATE - 3);
COMMIT;
CREATE OR REPLACE PACKAGE my_pkg
AS
TYPE t_test_rec IS RECORD(
my_tab_pk MY_TAB.my_tab_pk%TYPE,
description MY_TAB.description%TYPE,
create_date MY_TAB.create_date%TYPE
);
--c1rec MY_TAB%rowTYPE;
TYPE t_test_cur IS REF CURSOR
RETURN t_test_rec;
FUNCTION my_query (v_test_cv IN OUT t_test_cur)
RETURN NUMBER;
END my_pkg;
/
CREATE OR REPLACE PACKAGE BODY my_pkg
AS
/*Select all Records from the Table*/
FUNCTION my_query (v_test_cv IN OUT t_test_cur)
RETURN NUMBER
AS
BEGIN -- just a silly if condition to demonstrate!
IF TO_NUMBER (TO_CHAR (SYSDATE, 'ss')) < 30
THEN -- Return query 1 !
DBMS_OUTPUT.put_line ('------- ss < 30, query 1 results ---------');
OPEN v_test_cv FOR
SELECT my_tab_pk, description, create_date
FROM MY_TAB
WHERE 10 < 100 -- whatever
ORDER BY 1 ASC; -- Sort Ascending!
ELSE -- Return query 2 !
DBMS_OUTPUT.put_line ('------- ss >= 30, query 2 results --------');
OPEN v_test_cv FOR
SELECT my_tab_pk, description, create_date
FROM MY_TAB
WHERE 5 > 3 -- whatever
ORDER BY 1 DESC; -- Sort Descending!
END IF;
RETURN 0;
END my_query;
END my_pkg;
/
set serveroutput on size 10000 format wrapped
DECLARE
retval NUMBER;
-- TYPE t_test_rec IS RECORD( -- New Record type defined!
-- my_tab_pk MY_TAB.my_tab_pk%TYPE,
-- description MY_TAB.description%TYPE,
-- create_date MY_TAB.create_date%TYPE
-- );
-- c1rec t_test_rec; -- Cursor of New Record type!
c1rec MY_TAB%ROWTYPE; -- Cursor of New Record type!
v_test_cv my_pkg.t_test_cur; -- Cursor Variable passed out of Function!
-- Defined as per ref cursor in Function!
BEGIN
retval := my_pkg.my_query (v_test_cv);
LOOP
FETCH v_test_cv INTO c1rec;
EXIT WHEN v_test_cv%NOTFOUND;
DBMS_OUTPUT.put_line (
c1rec.my_tab_pk ||
', ' ||
c1rec.description ||
', ' ||
c1rec.create_date
);
END LOOP;
END;
/
|
|
|
|
|
|