return clause in cursor

What does a cursor returns?
will it returns something just like procedures?

then, for what the return clause is used in declare cursor statement?

[QUOTE=Barbie;5620]What does a cursor returns?
will it returns something just like procedures?

then, for what the return clause is used in declare cursor statement?[/QUOTE]

Cursor will return fetched record(s) for the statment which has been declared.
Barbie, can u give an example for ur doubt.
I wanted to understand ur problem clearly.

*** Innila ***

CURSOR company_cur (id_in IN NUMBER)
RETURN company%ROWTYPE IS
SELECT * FROM company;

What a declare cursor statement like this one will do?
For what that return clause?
what does it returns? and how?

Hi, the cursor return clause one of the most significant new features in pl/sql version 2 is the full support for packages and the resulting modularization of code that is now possible with that construct. Packages introduce an enhancement to the way you can declare a cursor: the return clause.when you group programs together into a package, you can make only the specification, or header information, of those programs available to developers. Although a developer can tell from the specification what the module is called and how to call it, he or she does not need to see any of the underlying code. As a result, you can create true black boxes behind which you can hide complex implementational details. With version 2 of pl/sql you can accomplish the same objective with cursors by using the cursor return clause. The return clause allows you to create a specification for a cursor which is separate from its body (the select statement). You may then place cursors in packages and hide the implementation details from developers. Consider the following cursor declaration with return clause: cursor caller_cur (id_in in number) return caller%rowtype is select * from caller where caller_id = id_in; the specification of the caller_cur cursor is: cursor caller_cur (id_in in number) return caller%rowtype while the body of the caller_cur cursor is: select * from caller where caller_id = id_in; everything up to but not including the is keyword is the specification, while everything following the is keyword is the body. You can include a return clause for any cursor you write in pl/sql version 2, but it is required only for cursors which are contained in a package specification. The return clause may be made up of any of the following datatype structures: · a record defined from a database table, using the %rowtype attribute · a record defined from a programmer-defined record here is an example of a cursor defined in a package. First, the package specification provides the name of the cursor and the return datatype (an entire row from the company table): package company is cursor company_cur (id_in number) return company%rowtype; end company; then the following package body repeats the cursor specification and adds the sql statement: package body company is cursor company_cur (id_in number) return company%rowtype is select * from company where company_id = id_in; end company; the number of expressions in the cursor's select list must match the number of columns in the record identified by table_name%rowtype or plsql_record%rowtype. The datatypes of the elements must also be compatible. If the second element in the select list is type number, then the second column in the return record cannot be type varchar2 or boolean. Why place cursors in a package? for the same reasons you would place a procedure or a function in a package: a package is a collection of logically related objects. By grouping the code into a package you make it easier for a developer to identify. Packaged cursors are essentially black boxes. This is advantageous to developers because they never have to code or even see the select statement. They only need to know what records the cursor returns, in what order it returns them, and which columns are in the column list. When cursor information is limited on this kind of "need to know" basis, it protects developers and the overall application from change. Suppose that a year from now the where clause of a query has to change. If a packaged cursor is not used, then each program that has a hardcoded or local cursor will have to be modified to meet the new specification. If, on the other hand, all developers simply access the same cursor, then changes will only need to be made to that packaged declaration of the cursor. The programs can then be recompiled to automatically support this change. I hope above information explain ur query and solve ur mind puzzle . Regards nikhil rattan

Hi Barbie,

A cursor with a RETURN clause will have all columns of the underlying table (same structure as the underlying table) as the result set.

For Example,
CURSOR emp_cur RETURN employee%ROWTYPE
IS
SELECT * FROM employee
WHERE department_id = 10;

The RETURN clause can have 2 types of datatype structures,
1) A record defined from a database table, using the %ROWTYPE attribute &
2) A record defined from a programmer-defined record

*** Innila ***

Can u tell how to find the values returned by the cusror using return clause
in the form of query.

I mean how to query that cursor to find out what values that cursor as returned.