| REF cursor type [message #371606] |
Mon, 13 November 2000 22:51  |
Kiki
Messages: 13
Registered: November 2000
|
Junior Member |
|
|
we can declare any cursor and also any variable with the same type of that cursor. for example, v_cursor c_cursor%rowtype.
Does anyone know how to declare variable for ref cursor, which we define inside the function?
Thx
|
|
|
|
| Re: REF cursor type - example [message #371627 is a reply to message #371606] |
Wed, 15 November 2000 15:12  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Try this working example...
/* Formatted by PL/Formatter v2.2.5.0 on 2000/08/18 18:01 */
-- 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;
/
|
|
|
|
| Re: REF cursor type [message #374614 is a reply to message #371606] |
Thu, 21 June 2001 08:00 |
Andrew
Messages: 144
Registered: March 1999
|
Senior Member |
|
|
hello andrew,
here is my requirement is
i declared two cursors say cur_a, cur_b
cur_c of type cursor.
if(ture)
cur_c := cur_a
else
cur_c := cur_b
end if;
is it possible in oracle
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
