How to write SQL statement with dynamic where clause?

Do we need Cursors in PL/SQL?

• In SQL Server, we can use some alternative ways to avoid cursors in order to improve performance. Can we do it in Oracle PL/SQL ? In PL/SQL, we always have an INTO clause for a SELECT statement, so we can not return multiple rows without cursors and composite date type ?

• Answer:

  Cursors are actually not a bad choice ... as long as you don't forget to close them, and performance-wise cursors are your best choice if you need to manipulate a larger dataset. If your issue is with the process of using cursors where you have to define a cursor and manipulate it you could try using a for loop over a select statement: FOR v_row IN (SELECT sth FROM mytable) LOOP ...code... END LOOP; This uses an implicit cursor and is actually the same as if you would define a cursor, open it and loop over it until it returns %NOTFOUND and finally close it. The selected fields of the current row are accessible as member fields of v_row (i.e.: v_row.sth). v_row's datatype is established in runtime and doesn't have to be defined prior to it's use. You can also use variables of type TABLE OF mytable%ROWTYPE (so no need for defining composite types yourself) and BULK COLLECT INTO clause in you SELECT statement. Then you can use that in a normal LOOP or if you wish to perform a bulk insert/update/delete over that dataset use FORALL statement which is the fastest way of doing bulk operations using data from an external dataset. .. so generally speaking if you have performance issues cursors are your friends as long as you remember to close them. If you need an example of FORALL use and a performance comparison to FOR loop, have a look at this link at PSOUG (best reference for Oracle IMHO) http://psoug.org/reference/array_processing.html