Stored Procedure: Cursor is bad?

Question

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"?

Answer 1

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.

Answer 2

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.