PL/SQL Code -1-

Oracle uses a block structure for organising PL/SQL code, the structure contains of the following

• Block header
• Declaration section
• Execution section
• Exception section

Block Header

The block header will contain what type of PL/SQL program the code unit is and there are a number of different
types

always returns a value, the return keyword can be used anywhere in the program, functions can be
called in select, where, order by and group by clauses
Function
Note: functions are stored within the data dictionary
does not require a return value but can pass values between code via parameters, procedures are
invoked as stand-alone statements, they are invoked using the execute keyword. Privileges are
Procedure dropped when you delete a procedure so you need to re-grant any privileges that are required.

Note: procedures are stored within the data dictionary

block of code that does not contain a header or runs within another piece of code.
Anonymous block
Note: anonymous blocks are not stored within the database unless within another piece of code
and are generally run adhoc
is a collection of program units
Package
Note: packages are stored within the data dictionary

All these program types can have parameters, which are used to provide the program with values or to retrieve
values. Each parameter must be associated with a data type (char, number). Parameters can be either in, out or
both (travels both ways), you can specify that the value is not null or set a default value (used if not parameter is
passed). You can pass the parameter either by positional notation or named notation.

create or replace function is_weekend (check_date IN date default sysdate) return varchar2
AS
Function example BEGIN
return 'Hello World!';
END;
create or replace archive_orders ( cust_id IN number, cust_name IN varchar2 )
AS
Procedure example BEGIN
dbms_output.put_line('Customer Name: ' || cust_name || 'Customer ID: ' || cust_id);
END;
Recompile alter procedure archive_orders compile;
PL/SQL Code -2-

Detect invalid objects select object_name from dba_objects where status = 'INVALID';

Overloading

As in other OOP programming languages you can overload procedures and functions, basically having the same
piece of code with the same name but different parameters, Oracle is clever enough to work out which one to
run

function overloading_test (city IN varchar2);

Overloading examples function overloading_test (city IN varchar2, county IN varchar2);
function overloading_test (city IN varchar2, county IN varchar2, country IN varchar2);

Declaration Section

You can declare one or more variables or constants within the declaration section, you can set default values for
them or constraints.

myvariable integer default 1;

Example
myvariable constant int := 1;

Execution Section

This section is were the action takes place, it start with the begin keyword and stops with the end keyword. You
must have one executable line of code or the null keyword.

Exception Section

This is the final section in the code block, this section is optional but comes before the end keyword. This
section handles errors within the code, there are many exception handlers that deal with different errors, using
when statements you setup handles to handle that specific type of error, you can have as many when statements
as you like. It is alos possible to create your own exceptions and to initiate an exception. Exceptions are all
handled with the scope of the block of code.

exception
when <exception_name>
then
error handling statements
Example
when others
default handling statements

Note: when other is the catch all exception handler

Own exception <exception_name> exception;
Initiate exception raise <exception_name>;

There are two functions that you can use that will provide some information regarding any errors, sqlcode
returns the error code and sqlerrm returns the error message.
PL/SQL Code -3-

Character Set

The characters allowed in PL/SQL are

Letters A-Z and a-z

Numbers 0-9
Symbols ~!@#$%^&*()_-+={}[]|;:'"<>,./?
Formatting tabs, spaces, carridge return

PL/SQL keywords are not case-sensitive but may programmers use upper case for keyword.

Special Symbols

The following symbols are used in PL/SQL

Arithmetic
+-*/
operations
Delimiters ' ( ) " /* */ --
Relational
= < > <> != ~= ^= <= >=
operators
Indicators %:@
Component Selector .
Item separator ,
Statement
;
terminator
&&
Input operator
Note: used to obtain information from the user
:= => || ** ..

:= equivalence operator
=> equates a parameter on the left of the symbol with a value on the right (basically assign
Special operators
values to a parameter)
|| the concatenation symbol
** used to identify exponents
.. used to separate a range ( 1 .. 10 - meaning 1 to 10)

Identifers

Identifiers are used to name things with PL/SQL as such variables, constants, cursors, sub-programs, packages
and exceptions, etc. Every indentifier must start with a letter and can include most of the character mentioned
above, however they must not include hyphens, slashes, ampersands or spaces, this rule can be overriden by
using double quotes around the identifier name when creating it. You cannot use a reserved word for an
identifier, see below on reserved words.
PL/SQL Code -4-

Reserved Words

Reserved Words have a special meaning in PL/SQL and are used as part of the PL/SQL syntax. There are many
reserved words with oracle, so its probably best to comsult the Oracle documentation on the current list.

Literals

Literals are representations of specific numeric, character, string, boolean or date/time values.

Conditional Logic

There are a number of conditional logic operations

if <condition>
then
code;
end if;

if <condition>
then
code;
else
code;
IF .. THEN .. ELSE
end if;

if <condition>
then
code;
elsif <condition>
code;
else
code;
end if;
Case statement case <selector>
when <condition>
then
code;
when 'VALLEP'
then
code;
when ename = 'VALLEP'
then
code;
else
code;
end case;
PL/SQL Code -5-

Note: the when condition can be a string match or an expression

Branching

You can redirect the flow of the program by using goto statements, but in todays modern day programming you
never use this statement.

Iterative

You will use iterative or repeated logic at sometime, basically you will go through data one row at a time, there
are number of iterative logic in Oracle

loop
code;
exit;
end loop;

Loop loop
code;
exit when <condition>
end loop;

Note: you must have a exit otherwise the loop with loop forever
for counter [reverse] low .. high
loop
code;
end loop;
For .. Loop
for <row_index> in [ cursor | select statement ]
loop
code;
end loop;
while <condition>
loop
While Loop
code;
end loop;

Complete example

Example create or replace procedure addstudent (roomin in int)

IS
roomname varchar2(20);
studentcount int;
capacity int;
noroom exception;
PL/SQL Code -6-

BEGIN
select scount, cap, rname
into studentcount, capacity, roomname
from scott.rooms
where room_id = roomin;

if studentcount > capacity - 1

then
raise noroom;
else
update scott.rooms set scount = studentcount + 1 where room_id = roomin;
commit;
dbms_output.put_line( 'Student count now ' || studentcount || ' in ' || roomname );
end if;
EXCEPTION
when noroom
then
dbms_output.put_line( 'There is no room in ' || roomname);
when others
then
dbms_output.put_line( 'Error ' || SQLERRM || ' occurred' );
END;

Running Code

Once you have the stored procedure you need to compile it, the compiler performs serveral tasks

• Checks syntax errors

• Checks for any dependencies
• Stores the pcode (this is whats actually runs)
• Set the status of the stored code to valid

If any dependencies are changed that affect the store program then the status will be changed to invalid, what
this means it that it will have to be recompiled on the fly when the next user runs it, this can cause a
performance hit especially if you are having to continually recompile stored programs.

Display Source code select * from all_source where name = 'test_procedure' and owner = 'vallep';
Display Invalid
select object_name from dba_objects where status = 'INVALID';
objects
Recompile alter procedure test_procedure recompile;

Program access rights

A user can have access rights to a table which he/she has not been granted to via a procedure or function,
basically the PL/SQL code has been complied with the same rights as the one who created it (owners rights),
which means when a user runs it the right are already within the code hence you obtain the owners rights.
PL/SQL Code -7-

However Oracle has a feature called invoker rights, which means that the objects are resolved first and does not
use the compiled rights, so if you don't have privileges then access is denied.

implementing
create or replace procedure test_procedure authid current_user is
invokers rights

Native Compilation

The Compilation process generates pcode which is what is run (like interpreted code), however you have the
choice to compile it in native code, to will only see performance improvements if the code is performing a lot of
repeated caculations. There are 4 steps to compile native code

1. edit the makefile spnc_makefile.mk which should be in the PLSQL subdirectory under
the home directory for your oracle database
2. set the initialization parameter plsql_compiler_flags to native
Compile native code 3. Do the normal compile of the PL/SQL program units

4. check the user_stored_settings data dictionary view to make sure that the objects
compiled properly.