Home » RDBMS Server » Server Administration » Re: Result Set from Procdeure (?)
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;
/
Previous Topic: using nvl to conquer is null vs. = value in where clause?
Next Topic: Re: MTS
Goto Forum:
  


Current Time: Fri Oct 30 03:35:10 CDT 2020