CODEWITHSUNDEEP
oracle-databaseplsql
Sebi
Sebi

Reputation: 3979

Oracle: Using Procedure or Cursor to store a Select-Statement?

I have a PL/SQL package where i want to declare a select-statment which are used by different other Packages. So i see to ways. First way i define a cursor which can be called from other packages and store the select. Second way would be a procedure which stored the select.

Can someone tell me the advantages and disadvantages of each way? My Prof. say Cursor are old and statefull and noone use this today. My Chef tell me Cursor is faster to iterate and you can make Types of it.

Can someone tell me what's the best practice here?

For example:

CURSOR crs_active_customer IS
 SELECT * FROM customer where status = 'active'

OR

PROCEDURE prc_getActiveCustomer IS
BEGIN
 SELECT * FROM customer where status = 'active';
END prc_getActiveCustomer;

What is better way to store select-statements.

Upvotes: 0

Views: 709

Answers (2)

Carlo Sirna
Carlo Sirna

Reputation: 1251

I would write a function that returns a new cursor instance every time you call it. A cursor variable in a package is actually a GLOBAL variable: you can have only one procedure at a time using it. This is probably the problem your professor is referring to.

Having a global cursor means that you will run into "cursor already open" errors if you write a a procedure that, while scanning the results of such cursor calls another function that internally needs to use the same cursor.

Upvotes: 1

user272735
user272735

Reputation: 10648

PL/SQL 101 to the rescue ! From Working with Cursors:

The central purpose of the Oracle PL/SQL language is to make it as easy and efficient as possible to query and change the contents of tables in a database. You must, of course, use the SQL language to access tables, and each time you do so, you use a cursor to get the job done.

So every time you have SQL in PL/SQL there will be a cursor. The next question is what kinds of cursors there is and when to use them. The above mentioned article touches also this topic.

You can also read the fine manual: Cursors

A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.

And then carry on reading about implicit and explicit cursors.

Next find a better professor.

Upvotes: 0

Related Questions