CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlcursor
sayhaha
sayhaha

Reputation: 789

Stored Procedure: Cursor is bad?

I read somewhere that 99% of time you don't need to use a cursor.

But I can't think of any other way beside using a cursor in this following situation.

Select t.flag
From Dual t;

Let's say this return 4 rows of either 'Y' or 'N'. I want the procedure to trigger something if it finds 'Y'. I usually declare a cursor and loop until %NOTFOUND. Please tell me if there is a better way.

Also, if you have any idea, when is the best time to use a cursor?

EDIT: Instead of inserting the flags, what if I want to do "If 'Y' then trigger something"?

Upvotes: 3

Views: 766

Answers (4)

akzhere
akzhere

Reputation: 353

Cursors are best to use when an column value form one table will be used repeatedly in multiple queries on different tables.

Suppose the values of id_test column are fetched from MY_TEST_TBL using a cursor CUR_TEST. Now this id_test column is a foreign key in MY_TEST_TBL. If we want to use id_test to insert or update any rows in table A_TBL,B_TBL and C_TBL, then in this case it's best to use cursors instead of using complex queries.

Hope this might help to understand the purpose of cursors

Upvotes: 0

Guffa
Guffa

Reputation: 700342

Your case definitely falls into the 99%.

You can easily do the conditional insert using insert into ... select.... It's just a matter or making a select that returns the result that you want to insert.

If you want to insert one record for each 'Y' then use a query with where flag = 'Y'. If you only want to insert a single record depending on whether there are at least one 'Y', then you can add distinct to the query.

A cursor is useful when you make something more complicated. I for example use a cursor when need to insert or update records in one table, and also for each record insert or update one or more records into several other tables.

Upvotes: 2

Abe Miessler
Abe Miessler

Reputation: 85056

Something like this:

INSERT INTO TBL_FLAG (col)
SELECT ID FROM Dual where flag = 'Y'

You will usually see a performance gain when using set based instead of procedural operations because most modern DBMS are setup to perform set based operations. You can read more here.

Upvotes: 2

Randy
Randy

Reputation: 16677

well the example doesnt quite make sense..

but you can always write an insert as select statement instead of what i think you are describing

Upvotes: 1

Related Questions