Simple life

As the following figure shows, during the execution of a PL/SQL block, the PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine, which executes the SQL statements and, in some cases, returns data to the PL/SQL engine. 

So when a PL/SQL block including a loop, which causes frequently transfer between PL/SQL engine and SQL engine, the time cost is quite high and the efficiency is really low.

For example:

DECLARE
   TYPE NumList IS VARRAY(20) OF NUMBER;
   depts NumList := NumList(10, 30, 70);  -- department numbers
BEGIN
   ...
   FOR i IN depts.FIRST..depts.LAST LOOP
      DELETE FROM emp WHERE deptno = depts(i);
   END LOOP;
END;

The above DELETE statement is sent to the SQL engine with each iteration of the FOR loop. Too many context switches between the PL/SQL and SQL engines and it can harm performance. So, what should we do to improve the performance? Use Bulk Binds!

1. Difference between implicit cursor and explicit cursor.

An explicit cursor is a named query that is defined as a cursor in PL/SQL by use of  the keyword CURSOR . UPDATE, DELETE and INSERT statements always use an implicit cursor.

The difference between explicit and implicit cursors in Oracle PLSQL is that explicit cursors give you complete control over opening, closing and fetching from an explicit cursor, including the number of rows fetched. Also, fetching from an explicit cursor will never raise a NO_DATA_FOUND or a TOO_MANY_ROWS exception.

When using implicit cursors in Oracle PL/SQL, we access the cursor attributes by prefixing the attribute name with "SQL%". To access the cursor attributes of explicit cursors in Oracle PL/SQL the SQL keyword is replaced by the cursor name.

Another advantage of using explicit named cursors in PL/SQL is that they can be parameterised just like any procedure or function

What is a named system exception?

Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.

Oracle has a standard set of exceptions already named as follows: