KAMAL SQL Material

  EXCEPTION Handling à An exception in PL/SQL block is raised during exception of a block. It terminates the main body of action means a block always terminates when pl/SQL raises an exception. à We can specify an exception handler to perform final action. à If an exception raised in the executable block with there is no exception handler, then the PL/SQL block terminates with the failure. If exception is handled then the Pl/SQL block terminates successfully. à Only one exception section is allowed with in single block and if more than one exception is required, we must use nested blocks. à The exception handling consists of handlers of all the exceptions. à Each exception handler consists of a when clause which specifies an exception that has to be handled. à Syntax Exception When <exception 1…> then    Sql statement1;    Sql statement2; When others then    Sql statement1;    Sql statement2; End; ...

  CURSOR à In order to process SQL statements oracle will allocate an area of memory known as context area. PL/SQL uses it to store and execute SQL statements. The information retrieved from database which is available in context area is known as active set. A CURSOR is pointer which works on active set. CURSOR points one row at a time in context area. A CURSOR is used to process multiple rows using PL/SQL. à It is a private memory or buffer. It is used to hold the data from tables or views in memory location for manipulation purpose. It is valid in PL/SQL block only. à CURSORs are variables that you can define in the declaration section. à Types of CURSORs 1.Implicit Cursor 2.Explicit Cursor à 1.Implicit Cursor It is cursor that is automatically declared by oracle every time SQL statement is executed. They are identified by “SQL % <CURSOR ATTRIBUTES>”. When the executable part of the block is issued SQL statement, PL/SQL creates an implicit cursor. ...