as I know cursor loop is faster the normal cursor and for all also faster instant of for loop
Your statement does not make sense.
All SQLs are parsed and executed as SQL cursors on the server.
All SQL cursors are equal. Some are not created via special magic to be faster than another, for the same SQL source code.
SQL cursors provide a fetch interface - allowing the client (the language using the cursor, such as Java or PL/SQL) different means to consume the cursor's output. Bulk fetching is usually preferred.
However, using a cursor output (e.g. SELECT cursor) as input to another cursor (e.g. INSERT cursor), is almost always a fundamentally flawed idea. Irrespective of bulk processing (such as using a FORALL loop).
Hi Billy ,
What about delete,update & inserts ? Is the concept still true for them ? How do we fetch a insert or delete statement ?If it sounds silly then please brief with the cursor handling in case of dmls. Please throw some light .:)
Which concept exactly?
Not all interface call are relevant. So for a DML call, the fetch is not relevant. Likewise, the bind call is optional for all types of calls, and not applicable at all to cursors executing DDLs.
A PL/SQL anonymous block for example that is used to return data (such as a ref cursor), will make use of the bind, execute and define variable call.
Inside the PL/SQL language, the DBMS_SQL is the language's "manual" wrapper to the cursor interface interface. This allows one to use cursors in PL/SQL the same way as external languages like C/C++ and Java and so on use cursors (though in Java you would seldom use the cursor interface directly as abstraction layers in the form of classes are provided).
PL/SQL's real strength though is dealing with the cursor interface itself, so that you do not have to. You code SQL and mix PL/SQL variables and so on into it. The PL/SQL engine recognises that, and creates all the cursor interface calls needed for binding, executing, and so on. So do not confuse the way PL/SQL treats cursors as the SQL cursor interface. Just like Java and C# classes provides an abstraction layer and internally use the SQL cursor interface, so too PL/SQL provides an abstraction layer (implicit cursors, explicit cursors, ref cursors, execute immediate cursors) - which internally uses the SQL cursor interface.
can I use cursor for loop in forall if we can the what is the way .
No - a 'forall' is a single executable statement.