PL/SQL Notes

The PL/SQL engine resides in:
r
• The Oracle database for executing stored subprograms

• The Oracle Forms client when running client/server applications, or in the Oracle

Application Server when using Oracle Forms Services to run Forms on the Web

I
The PL/SQL engine is a virtual machine that resides in memory and processes the PL/SQL
m-code instructions. When the PL/SQL engine encounters a SQL statement, a context switch is
made to pass the SQL statement to the Oracle server processes. The PL/SQL engine waits for the
SQL statement to complete and for the results to be returned before it continues to process
subsequent statements in the PL/SQL block.

The Oracle Forms PL/SQL engine runs in the client for the client/server implementation, and in
the application server for the Forms Services implementation. In either case, SQL statements are
typically sent over a network to an Oracle server for processing.


A procedure is compiled and stored in the database as a schema object. If you are using the
procedures with Oracle Forms and Reports, then they can be compiled within the Oracle Forms
or Oracle Reports executables.

compiled procedure is stored as m-code in the database;
if errors exist then m-code is not stored until all errors are fixed.


The OUT parameter in a Procedure must be actually passed as a variable (actual parameter).
It cannot be assigned a default value. It is an uninitialized variable.
IN OUT parameter also cannot be assigned a default value. I must also be a variable being passed
so that it can take the value back to the caller. It is an initialized variable.

The IN parameter is passed by reference and IN OUT or OUT are passed by value.
passed by value means that a copy is made of the actual parameter to the formal parameter
and the procedure only modifies the local copy until the procedure exits successfully
then the local modified copy is copied to the actual parameter.
If a large collection is passed as IN OUT then it requires double the memory resources
because it copies the collection and makes a local copy in the procedure. To avoid this
declare IN OUT as NOCOPY
eg procedure test (abc IN OUT NOCOPY integer)
source : http://www.dba-oracle.com/t_pl_sql_nocopy_data_structures.htm

EXCEPTION
If not handled DML is rolled back in that block/procedure and if the caller block/procedure also does not handle the exception its DML is also rolled back.


Functions
You cannot reference host or bind variables in the PL/SQL block of a stored function.

Note: Although the OUT and IN OUT parameter modes can be used with functions, it is not good

programming practice to use them with functions. However, if you need to return more than one

value from a function, consider returning the values in a composite data structure such as a

PL/SQL record or a PL/SQL table.
If IN OUT parameters are used in a function then that function cannot be part of an sql statement
it can be executed as
execute :x := fn(:param);
but not select fn(:param) from dual;

Procedures Functions
Execute as a PL/SQL statement Invoke as part of an expression
Do not contain RETURN clause in Must contain a RETURN clause in the
the header header
Can pass values (if any) using Must return a single value
output parameters
Can contain a RETURN statement Must contain at least one RETURN
without a value statement




CURSOR

When you open a cursor, PL/SQL executes the query for that cursor. It also identifies
the active set of data—that is, the rows from all involved tables that meet the criteria
in the WHERE clause and join conditions. The OPEN does not actually retrieve any of
these rows; that action is performed by the FETCH statement.

Regardless of when you perform the first fetch, however, the read consistency model
in the Oracle database guarantees that all fetches will reflect the data as it existed when
the cursor was opened. In other words, from the moment you open your cursor until
the moment that cursor is closed, all data fetched through the cursor will ignore any
inserts, updates, and deletes performed by any active sessions after the cursor was
opened.

TRIGGERS

Trigger types
- Before
- After
- Row level
- Statement level

Triggers can be for
-DML statements
-DDL statements
-Database events
-INSTEAD of
-Suspended Statements


Row level triggers will give mutating table error even if u read from the tigger table, updating is of course not allowed. This is not the case with Statement level trigger. Statement level trigger can both read and modify the triggering table.

Triggers can have commit within them if used as a part of AUTONOMOUS TRANSACTION.


11g has now compound triggers ie mix both row level and statement level triggers in the same trigger. The keyword is COMPOUND TRIGGER
To distinguish between statement level code and row level code
use
BEFORE STATEMENT
BEFORE EACH ROW
AFTER EACH ROW
AFTER STATEMENT

Triggers can have a max size of 32K but can call stored procedures or functions.

If a trigger raises an error then that part of transaction is rolled back.

can combine update/insert/delete in the same trigger like
AFTER INSERT or UPDATE or DELETE or MERGE -- statement level trigger
on ORDERS
[FOR EACH ROW] -- row level trigger


- REFERENCING old as prior

- INSERTING
- UPDATING
- DELETING
eg IF INSERTING THEN

- WHEN clause of a trigger is part of trigger header and controls if the trigger should be executed at all based on the condition specified.
- WHEN Vs IF -- IF is used within the trigger body when the trigger is being executed. IF is used to code logic within the trigger. eg IF INSERTING

- FOLLOWS another_trigger_name -- used to specify the trigger order