| Re: Result Set from Procdeure (?) [message #371059] |
Fri, 18 August 2000 20:35 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
See this working example of a Ref-Cursor:
-- Create a test table!
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;
/*
|| Package Spec!
*/
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
);
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;
/
/*
|| Package Body!
*/
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
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
ORDER BY 1 DESC; -- Sort Descending!
END IF;
RETURN 0;
END my_query;
END my_pkg;
/
/*
|| Test the Package and Function to return multiple rows!
*/
set serveroutput on
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!
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;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
