PL/SQL Code

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

Block Header The block header will contain what type of PL/SQL program the code unit is, be it a function or a procedure.
Declaration Section You can declare one or more variables or constants within the declaration section, you can set default values for them or constraints.
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.
Example
Basic Block Structure

[DECLARE]
   declaration_statements
BEGIN
   execution_statements
[EXCEPTION]
   exception_handling_statements
END;

-- The minimum amount of code could be
BEGIN
   NULL;
END;
/

Functions and Procedures

Oracle supports subroutines that are stored as functions and procedures in the database. You can deploy them as standalone subroutines or as components in packages. You define the function or procedure in the package specification not the package body or object body.

Function

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

Note: functions are stored within the data dictionary

FUNCTION function_name
[ ( parameter1 [IN] [OUT]   [NOCOPY]   sql_data_type | plsql_data_type,
    parameter1 [IN] [OUT]   [NOCOPY]   sql_data_type | plsql_data_type,
    parameter1 [IN] [OUT]   [NOCOPY]   sql_data_type | plsql_data_type ) ]
RETURN [ sql_data_type  |  plsql_data_type ]
  [ AUTHID [ {DEFINER  |  CURRENT_USER}  ]
  [ DETERMINISTIC  |  PARALLEL_ENABLED  ]
  [ PIPELINED  ]
  [ RESULT_CACHE  [RELIES ON table_name  ] ]   IS
    declaration_statements
BEGIN
    execution_statements
[ EXCEPTION ]
    exception_handling_statements
END;  

Procedure

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 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

PROCEDURE PROCEDURE_name
[ ( parameter1 [IN] [OUT]   [NOCOPY]   sql_data_type | plsql_data_type,
    parameter1 [IN] [OUT]   [NOCOPY]   sql_data_type | plsql_data_type,
    parameter1 [IN] [OUT]   [NOCOPY]   sql_data_type | plsql_data_type ) ]

  [ AUTHID [ {DEFINER  |  CURRENT_USER}  ]   IS
    declaration_statements
BEGIN
    execution_statements
[ EXCEPTION ]
    exception_handling_statements
END;  

Anonymous block

block of code that does not contain a header or runs within another piece of code.

Note: anonymous blocks are not stored within the database unless within another piece of code and are generally run ad hoc

Package

is a collection of program units

Note: packages are stored within the data dictionary

Examples
Basic Function create or replace function hello_world (check_date IN date default sysdate)
return varchar2 IS     -- Note the return which is required for a function
BEGIN
  return 'Hello World!';
END;
Basic Procedure create or replace procedure archive_orders ( cust_id IN number, cust_name IN varchar2 ) IS
BEGIN
  dbms_output.put_line('Customer Name: ' || cust_name || 'Customer ID: ' || cust_id);
END;
Function and Procedure Maintenance
Remove Procedure drop procedure archive_orders;
Remove Function drop function hello_world;
Recompile alter procedure archive_orders compile;
Detect invalid objects select object_name from dba_objects where status = 'INVALID';

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 named, or mixed notation see below for more details.

IN This is the default mode, it means that the parameter is read-only. You can assign a default value to the parameter which means that this parameter is optional when passing to the function/procedure but you must not alter it in the subroutine. In this mode the value is defined as pass-by-value.
OUT This mode means that the parameter is write-only, you cannot assign a default value to the parameter thus it is not optional. In this mode the value is defined as pass-by-reference.
IN OUT This mode means that the parameter is read-write, you cannot assign a default value to the parameter thus it is not optional. In this mode the value is defined as pass-by-reference.

You can override the parameter option by using the NOCOPY hint but there are rules

The deterministic clause lets you guarantee that a function always works the same way with any inputs. This type of guarantee requires that a function doesn't read or write data from external sources, like packages or database tables. For an example a calcuation function would a good choice as it performs the same regardless of the inputted data. You use deterministic functions inside materialized views or function-based indexes.

Deterministic create or replace function pv
( future_value   NUMBER,
  periods        NUMBER,
  interest       NUMBER)
return number deterministic is
BEGIN
   return future_value / ((1 + interest) ** periods);   -- the code can never change
END pv;
/

The parallel_enable clause lets you you designate a function to support parallel query capabilities, this guarantee requires that a function doesn't read or write data from an external source.

Parallel_enable create or replace function merge
( last_name       varchar2,
  first_name      varchar2,
  middle_initial  varchar2)
return varchar2 parallel_enable is
BEGIN
   return last_name || ', ' || first_name || ' ' || middle_initial;
END;
/

The pipeline clause lets you treat the return value as a table (nested table or varray). A pipelined function are simply code that you can pretend is a database table, so you can use 'select * from <PLSQL_function>;'. You can only use the pipeline statements in SQL and not in PL/SQL.

pipelined

-- Create the data type
create type numbers as varray(10) of number;

-- The pipelined function
create or replace function pipelined_numbers
return numbers
pipelined is
   list numbers := numbers(0,1,2,3,4,5,6,7,8,9);
BEGIN
   for i in 1..list.last
   loop
      pipe row(list(i));
   end loop;
   return;
END;
/

-- Use the pipelined function
select * from table(pipelined_numbers);

The result_cache is new in Oracle 11g, it indicates that a function result is cached only one in the SGA and available across all sessions, both the actual parameters of prior calls and results are available in the result cache. It uses the parameters to match on previous results within the cache, so it does not have to re-run the function again, however the RELIES_ON clause is critical because it ensures any change to the underlying table invalidate the result cache, which means that the result set is always current (not stale data) but it does come at a performance cost.

result_cache does have some restrictions

result_cache -- In the function use the below
result_cache relies_on(<table_name>) IS

Positional, Named or Mixed Notation (Procedures and Functions)

There are three ways to pass parameters to either a function or procedure

Example

CREATE OR REPLACE FUNCTION add_three_number
   (a NUMBER := 0, b NUMBER := 0, c NUMBER := 0)
RETURN NUMBER IS
BEGIN
   RETURN a + b + c;
END;
/


BEGIN
   -- Positional Notation
   dbms_output.put_line(add_three_numbers(3,4,5));

   -- Named Notation
   dbms_output.put_line(add_three_numbers(a => 3, b => 4, c => 5));

   -- Mixed Notation, Positional must come first
   dbms_output.put_line(add_three_numbers(3, b => 4, c => 5));
END;

Note: when using mixed positional notation must come first, then Named.

Function and Procedure rules

Pass-by-Value Functions
  • All parameters must be write-only using the IN mode
  • Parameters are locally scoped that cannot be changed
  • Any parameters can use any valid SQL or PL/SQL datatype. Only functions with parameter lists that use SQL datatypes work with SQL statements.
  • Any IN parameter can have a default value
  • The formal return values can use any SQL or PL/SQL datatype, but pipelined return tables must use SQL.
  • Any system cursor is not writeable and must be passed as a IN parameter
Pass-by-Reference Functions
  • You must have one parameter as read-only or read-write
  • All formal parameters are locally scoped variables that you can change during operation inside the function
  • Any parameters can use any valid SQL or PL/SQL datatype. Only functions with parameter lists that use SQL datatypes work with SQL statements.
  • Any IN parameter can have a default value
  • The formal return values can use any SQL or PL/SQL datatype, but pipelined return tables must use SQL.
  • Any system cursor is not writeable and must be passed as a IN parameter
Pass-by-Value Procedures
  • All parameters must be write-only using the IN mode
  • Parameters are locally scoped that cannot be changed
  • Any parameters can use any valid SQL or PL/SQL datatype.
  • Any IN parameter can have a default value
  • Any system cursor is not writeable and must be passed as a IN parameter
Pass-by-Reference Procedure
  • At least one parameter must be defined as read-only or read-write.
  • Parameters are locally scoped that can be changed
  • Any parameters can use any valid SQL or PL/SQL datatype.
  • Any IN parameter can have a default value
  • Any system cursor is not writeable and must be passed as a IN parameter

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

Overloading examples

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

Recursive Functions

You can use recursive function for complex problem like advanced parsing. A recursive function calls one or more copies of itself to resolve a problem by coverging on a result. Recursive functions use a base case and a recursive case, a base case is the anticipated result the recursive case applies a formula that includes one or more calls back to the same function. You can have two types of recursive function a linear or a non-linear.

linear recursive function
(only one recursive call)
create or replace function factorial
( n binary_double) return binary_double is
BEGIN
   if n <= 1
   then
      return 1;
   else
      return n * factorial(n - 1);                  -- only one recursive call
   end if;
END factorial;
/
non-linear recursive function
(two or more recursive calls)
create or replace function fibonacci
( n binary_double) return binary_double is
BEGIN
   if n <= 2
   then
      return 1;
   else
      return fibonacci(n - 2) + fibonacci(n - 1);   -- two or more recursive calls
   end if;
END fibonacci;
/

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 also possible to create your own exceptions and to initiate an exception. Exceptions are all handled with the scope of the block of code.

Example

EXCEPTION
  when <exception_name>
  then
    error handling statements
  when others
    default handling statements

Note: when other is the catch all exception handler

User-Defined Exception

DECLARE
   e exception;
BEGIN
   raise e;
   dbms_output.putline('This will not run because of the raise above');
EXCEPTION
  WHEN OTHERS THEN
     if sqlcode = 1
     then
        dbms_output.putline('This is a [' || SQLERRM || ']');
     end if;
END;
/

-- More complex example
DECLARE
   a varchar2(20);
   invalid_userenv_parameter EXCEPTION;
   PRAGMA EXCEPTION_INIT(invalid_userenv_parameter, -2003);

BEGIN
   a := SYS_CONTEXT('userenv','proxy_pusher');
   -- you could raise the error yourself with the below text
   -- raise_application_error(-2003, 'Invalid context error');
EXCEPTION
   WHEN invalid_userenv_parameter
   then
      dbms_output.put_line(SQLERRM);
END;
/

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.

SQLCODE

EXCEPTION
WHEN value_error THEN
   dbms_output.putline('Error Code: ' || SQLERRM )'

Note: the negative number returned maps to the Oracle predefined exceptions.
example of an error would be [ORA-01403: no data found]

SQLERRM

EXCEPTION
WHEN value_error THEN
   dbms_output.putline('Error Code: ' || SQLCODE )'

Note: a negative number is returned

Oracle has many standard error codes, most of them will raise a negative number, if you need the code number then use sqlcode.

To quicky identify an errors loction you use dbms_utility function format_error_backtrace. This function will identify the line number the error came from.

Stack Trace dbms_utility.format_error_backtrace;

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

Symbol
Type
Description
:=
Assignment
You assign the right operand to the left operand
:
Association
This indicates that the variable is a bind variable
&
Association
The substitution indicator lets you pass actual parameters into anonymous-block PL/SQL programs
%
Association
This attribute lets you link a database catalog column, row or cursor attribute
=>
Association
It is used in name notation function and procedure calls
.
Association
It glues references together, for example a schema and table
@
Association
The remote access indicator lets you access a remote database through database links
||
Concatation
Used to glue to Strings together
=
Comparison
The comparsion operator
-
Comparison
Used to change a number from its positive to negative values and vice versa
<>, !=, ^=
Comparison
The not-equals operators
>
Comparison
greater-than operator
<
Comparison
less-than operator
>=
Comparison
greater-than or equal-to operator
<=
Comparison
less-than or equal-to operator
' (single quote)
Delimiter
lets you define a String literal
( )
Delimiter
brackets can be used to override order or precedence, used to pass arguments to functions and procedures, for lists
, (comma)
Delimiter
delimits lists
<<, >>
Delimiter
The opening and closing delimters for LABELS in pl/sql
--
Delimiter
single line comment operator
/*, */
Delimiter
multi-line comment operator
" (double quote)
Delimiter
It lets you access tables created in case-sensitive fashion from the database catalog
+, /, *, -
Math
The standard math operator
**
Math
the exponential operator
;
Statement
The statement terminator

Identifiers

Identifiers are used to name things with PL/SQL as such variables, constants, cursors, sub-programs, packages and exceptions, etc. Every identifier 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 overridden 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.

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 consult the Oracle documentation on the current list.

Literals

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

Character a := 'a';
String a := 'A string of text';
Number

a := 1968;
b := 2.0d;           -- a double (BINARY_DOUBLE)
c := 2.0f;           -- a float (BINARY_FLOAT)

See data types for more information on BINARY_DOUBLE and BINARY_FLOAT

Boolean a := TRUE;
a := FALSE;
a := NULL;           -- Does not revaluate to either TRUE or FALSE
Data/Time

relative_date := '04-JAN-68';
absolute_date := '04-JAN-1968';

relative_date := TO_DATE('04-JAN-68');                  -- Default format mask
absolute_date := TO_DATE('04-JAN-1968','MON-DD-YY');    -- Override default format mask

relative_date := CAST('04-JAN-68' AS DATE);             -- Relative format mask
absolute_date := CAST('04-JAN-1968' AS DATE);           -- Absolute format mask

Variable Types

PL/SQL supports two variable datatypes, scalar and composite. Scalar variables contain only one thing, character, date or a number. Composite variable are arrays, records (structures), reference cursors and objects. PL/SQL also introduces a Boolean datatype ans several subtypes derived from the SQL datatypes. Subtypes inherit the behavior of a datatype but also typically have constrained behaviors, a unconstrained type does not change a base type's behavior. The base type is a supertype because its the model for subtypes. PL/SQL automatically assigns most declared variables a null value.

An explicit datatype

variable_name variable_type;

composite_variable_name record_type;

An anchored datatype

variable_name column_name%TYPE;

composite_variable_name catalog_object%ROWTYPE;

Note: anchoring a variable using %TYPE means that your program automatically adjusts as the column datatype changes providing conversions meet all logical conditions. The %ROWTYPE updates your program to reflect any changes in the row definition of the catalog_object.

See special data types for more information on anchored datatypes

Variable datatypes can be defined in SQL or PL/SQL, you can use SQL datatypes in both SQL and PL/SQL statements. You can only use PL/SQL datatypes inside PL/SQL program units.

Scalar Datatypes
Boolean

var1 Boolean;                                      -- Implicity assign a null value
var2 Boolean NOT NULL := TRUE;                     -- Explicity assign a TRUE value
var3 Boolean NOT NULL := FALSE;                    -- Implicity assign a FALSE value

subtype booked is BOOLEAN;                         -- subtype a BOOLEAN datatype

Note: you can also use the NOT NULL option

Characters and Strings DECLARE
  c CHAR(32767)      := ' ';                       -- will allocate all memory immediately
  v VARCHAR2(32767)  := ' ';                       -- will allocate memory as required
BEGIN
  dbms_output.put_line('c is ' || LENGTH(c)');     -- will display 32767
  dbms_output.put_line('v is ' || LENGTH(v)');     -- will display 1
  v := v || ' ';
  dbms_output.put_line('v is ' || LENGTH(v)');     -- will display 2
END;
/
Char and Character Datatypes

var1 CHAR;                                         -- Implicity size at 1 byte
var2 CHAR(1);                                      -- Explicity size at 1 byte
var3 CHAR(1 BYTE);                                 -- Explicity size at 1 byte
var4 CHAR(1 CHAR);                                 -- Explicity size at 1 character

DECLARE
  subtype code is CHAR(1 CHAR);                    -- create a subtype
  c CHAR(1 CHAR) := 'A';
  d code;                                          -- declare the code subtype
BEGIN
  d := c;                                          -- assign a value to the subtype
END;
/

Note: you can also use the NOT NULL option

Long and Long Raw Datatypes

var1 LONG;                                         -- Implicity size at 0
var2 LONG RAW;                                     -- Implicity size at 0

var1 LONG := 'CAR';
var2 LONG RAW := HEXTORAW('43'||'41'||'52');       -- assign CAR in hexadecimal

Note: you can also use the NOT NULL option

ROWID and UROWID ROWID                                              -- used for backward compatibility use UROWID instead
UROWID
Varchar2

var1 VARCHAR2(100);                                -- Explicity sized at 1OO bytes
var2 VARCHAR(100 BYTE);                            -- Explicity sized at 100 bytes
var3 VARCHAR(100 CHAR);                            -- Explicity sized at 100 characters

DECLARE
  subtype db_string is varchar2(4000 byte);        -- setting a physical limit on the subtype
  c varchar2(1 char) := 'A';
  d db_string;
BEGIN
  d := c;                                          -- assigning a value to the subtype
END;
/

Note: you can also use the NOT NULL option

Date Datatype

var1 DATE;                                         -- Implicity assigns a null value
var2 DATE := SYSDATE;                              -- Explicity assigns current server timestamp
var3 DATE := SYSYDATE + 1;                         -- Explicity assigns tomorrow server timestamp
var4 DATE := '08-OCT-08';                          -- Explicity assigns todays date

Note: you can also use the NOT NULL option, you can also use the interval subtypes INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH

Timestamp subtypes

var1 TIMESTAMP;                                    -- Implicity assigns a null value
var1 TIMESTAMP := SYSTIMESTAMP;                    -- Explicity assigns a value
var1 TIMESTAMP(3);                                 -- Explicity sets precision for null value
var1 TIMESTAMP(3) := SYSTIMESTAMP;                 -- Explicity set precision and value

DECLARE
   d DATE := SYSTIMESTAMP;
   t TIMESTAMP(3) := SYSTIMESTAMP;
BEGIN
   dbms_output.put_line('DATE      ['||d||']');
   dbms_output.put_line('TO_CHAR   ['||TO_CHAR(d,'DD-MON-YYYY HH24:MI:SS')||']');
   dbms_output.put_line('TIMESTAMP ['||t||']');
END;

NCHAR and NVARCHAR2 Datatypes

var1 NCHAR;                                         -- Implicity sized at 1 character
var2 NCHAR(100);                                    -- Explicity sized at 100 characters

var1 NVARCHAR2(100);                                -- Explicity sized at 100 characters

Note: you can also use the NOT NULL option

BINARY_INTEGER Datatype var1 BINARY_INTEGER;
var2 BINARY_INTEGER := 21;

subtype myrange is BINARY_INTEGER range 1..100;
IEEE 745-Format Datatype

var1 BINARY_DOUBLE;
var2 BINARY_DOUBLE := 21d;

var1 BINARY_FLOAT;
var2 BINARY_FLOAT := 21f;

Note: you can also use the following contants as well
BINARY_FLOAT_NAN
BINARY_FLOAT_INFINITY
BINARY_FLOAT_MIN_NORMAL
BINARY_FLOAT_MAX_NORMAL
BINARY_FLOAT_MIN_SUBNORMAL
BINARY_FLOAT_MAX_SUBNORMAL

Number Datatypes

var1 NUMBER;                                        -- A null number with 38 digits
var2 NUMBER(15);                                    -- A null number with 15 digits
var3 NUMBER(15,2);                                  -- A null number with 15 digits and 2 decimals

var4 DOUBLE PRECISION;                              -- A null number with 126 digits
var5 FLOAT;                                         -- A null number with 126 digits
var6 DOUBLE PRECISION(15);                          -- A null number with 15 digits
var7 FLOAT;                                         -- A null number with 15 digits

Note: Number datatypes use Oracle's own libraries

PLS_INTEGER Datatype var1 PLS_INTEGER;                                   -- A null number requires no space
var2 PLS_INTEGER := 11;                             -- An integer requires space for each character

Note: the PLS_INTEGER datatype uses native math libraries
Large Objects (LOBs)
BFILE Datatype

var1 BFILE;                                         -- Declare a null reference to a BFILE

Note: you must use the DBMS_LOB package with this datatype

BLOB var1 BLOB;                                          -- Declare a null reference to a BLOB
var2 BLOB := empty_blob();                          -- Declare an empty BLOB
var2 BLOB := '43'||'41'||'52';                      -- Declare a hexadecimal BLOB for CAR

Note: you must use the DBMS_LOB package to read and write to a BLOB
CLOB

var1 CLOB;                                          -- Declare a null reference to a CLOB
var2 CLOB := empty_clob();                          -- Declare an empty CLOB
var2 CLOB := 'CAR';                                 -- Declare a CLOB for CAR

Note: you must use the DBMS_LOB package to read and write to a CLOB

NCLOB

var1 NCLOB;                                         -- Declare a null reference to a NCLOB
var2 NCLOB := empty_clob();                         -- Declare an empty NCLOB
var2 NCLOB := 'CAR';                                -- Declare a hexadecimal NCLOB for CAR

Note: you must use the DBMS_LOB package to read and write to a NCLOB

Composite Datatypes
Records

-- Simple record
DECLARE
  TYPE demo_record_type IS RECORD
  ( id number default 1,
    value varchar2(10) := 'One'
  );

  demo demo_record_type;
BEGIN
  dbms_output.put_line('[' || demo.id || '][' || demo.value ']');
END;
/

-- A record within a record
DECLARE
  TYPE full_name IS RECORD                          -- this type
  ( first varchar2(10 char) := 'Paul',
    last varchar2(10 char)  := 'Valle'
  };

  TYPE demo_record_type IS RECORD
  ( id number default 1,
    contact full_name                               -- assign the type FULL_NAME inside this record
  );

  demo demo_record_type;
BEGIN
  dbms_output.put_line('[' || demo.id || '][' || demo.value ']');
  dbms_output.put_line('[' || demo.contact.id || '][' || demo.contact.value ']');
END;
/

VARRAY Datatype

-- SQL definition
create or replace type varray_name as VARRAY(maximum_size) of sql_datatype;

-- PL/SQL definition
TYPE varray_name IS VARRAY(maximum_size) of [sql_datatype | pl/sql_data];

var1 varray_name;
var2 varray_name := varray_name();
var3 varray_name := varray_value1, value2, value3);

DECLARE
  TYPE number_varray is varray(10) of number;
  list number_varray := number_varray(1,2,3,4,5,6,7,8,null,null);
BEGIN
  for i in 1..list.limit
  loop
     dbms_output.put('[' || list(i) || ']');
  end loop;
  dbms_output.new_line;
END;
/

Note: see PL/SQL and SQL for more details on varrays

Nested Table Datatype

-- SQL Definition
create or replace type table_name as table of sql_datatype;

-- PL/SQL Definition
type table_name is table of [sql_datatype | pl/sql_datatype];

DECLARE
  TYPE number_table is table of number;
  list number_table := number_varray(1,2,3,4,5,6,7,8,null,null);
BEGIN
  for i in 1..list.limit
  loop
     dbms_output.put('[' || list(i) || ']');
  end loop;
  dbms_output.new_line;
END;
/

Note: see PL/SQL and SQL for more details on Nested Tables

Associate Array Datatype

-- SQL Definition
This is a PL/SQL only datatype

-- PL/SQL definition
type table_name is table of [sql_datatype | plsql_datatype] index by pls_integer;
type table_name is table of [sql_datatype | plsql_datatype] index by varchar2(10);

DECLARE
  TYPE number_table is table of number index by pls_integer;
  list number_table;
BEGIN
  -- Explicity assignment required for associative arrays
  for i in 1..6
  loop
     list(i) := i;                               
  end loop;

  -- Delete the second element
  list.delete(2);

  --
  for i in 1..list.count
  loop
     if list.exists
     then
        dbms_output.put('[' || list(i) || ']');
     end if;
  end loop;
  dbms_output.new_line;
END;
/

Note: see PL/SQL and SQL for more details on Associate Arrays

Reference Cursor

-- Weakly typed cursor
type reference_cursor_name is ref cursor;

-- Strongly typed cursor
type reference_cursor_name is ref cursor return catalog_object_name%ROWTYPE;

-- Example
variable refcur recursor

DECLARE
  type weakly_typed is refcursor
  quick weakly_typed;
BEGIN
  open quick for
     select item_title, count(*) from employees having (count(*) > 2) group by item_title;
  :refcur := quick;
END;
/

select :refcur from dual;

Note: a weakly typed cursor are used when the query returns something other than a catalog object.

Note: see PL/SQL and SQL for more details on Reference Cursors

Bind Variables

There are session level variables in the SQL*Plus environment, these are called bind variables because you can bind the contents from query execution to use in another query, likewise you can bind variables from one PL/SQL execution scope to another or a subsequent SQL statement.

Example

SQL> variable mybindvar varchar2(30)          -- Notice no semi-colon after statement

BEGIN
   :mybindvar := 'Demostration';
END;
/

SQL> select :mybindvar as "Bind Variable" from dual;

Variable Scope

Each programming block establishes its own program scope, each block has access to its own variables within the block. However it is possible to override your scope access.

Scope access DECLARE
   current_block varchar2(10) := 'Outer';
   outer_block varchar2(10)   := 'Outer';
BEGIN
   dbms_output.put_line('current_block][' || current_block || ']');
   DECLARE
      -- This overrides the outer block current_block variable
      current_block varchar2(10) := 'Inner';
   BEGIN
      dbms_output.put_line('current_block][' || current_block || ']');
      dbms_output.put_line('outer_block][' || outer_block || ']');
   END;
   dbms_output.put_line('current_block][' || current_block || ']');
END;
/

Quoting Alternative

In oracle 10g you can replace the familiar single quote with another quoting symbol, this is helpful when you have got a bunch of apostrophes in a string that would individually require back-quoting with another single quote.

Quoting Alternative example

-- The old way
select 'It''s a bird, no plane, no it''s superman!' as phrase from dual;

-- The new way
select q'(it's a bird, no plane, no it's superman!)' as phrase from dual;

Note: in the old way we use two single quotes.

Conditional Logic

There are a number of conditional logic operations

IF .. THEN .. ELSE

if <condition>
then
  code;
end if;

if <condition>
then
  code;
else
  code;
end if;

if <condition>
then
  code;
elsif <condition>
  code;
else
  code;
end if;

Case statement

case <selector>
when <condition>
  then
     code;
when <condition>
  then
     code;
when <condition>
  then
     code;
else
  code;
end case;

Note: the when condition can be a string match or an expression, the selector can be TRUE which forces you into the case statement, or FALSE which would match the first WHEN block.

## EXAMPLE
create or replace procedure case_test (guess_num IN varchar2)
IS
   win varchar2(15) default 'You Win';
   lose varchar2(15) default 'You Lose';
BEGIN
   case guess_num
   when '1'
     then
         dbms_output.put_line(lose);
   when '2'
     then
         dbms_output.put_line(win);
   when '3'
     then
         dbms_output.put_line(lose);
   when '4'
     then
         dbms_output.put_line(win);
   else
         dbms_output.put_line('You did not select a number between 1-4');
   end case;
end case_test;
/

Comparison Operators

You can use comparison operators to create more complex expression that result in either true or false.

Comparison Operators
AND allows you to combine two comparisons into one
BETWEEN check whether a variable value is between two values of the same data type
IN check whether a variable value is in a set of comma delimited values
IS EMPTY check whether a VARRAY or NESTED TABLE collection variable is empty
IS NULL check whether a variable is NULL
IS A SET check whether a variable is a VARRAY or NESTED TABLE collection variable
LIKE check whether a variable value is part of another value
MEMBER OF find out whether an element is a member of a collection
NOT allows you check the opposite of a Boolean state of an expression
OR allows you to combine two comparisons into one
SUBMULTISET check whether a VARRAY or NESTED TABLE collection is a subset of a mirrored datatype
Examples
AND BEGIN
   if 1 =1 AND 2 = 2
   then
      dbms_output.put_line('Statement is TRUE');
   end if;
END;
/
BETWEEN BEGIN
   if 1 BETWEEN 1 AND 3
   then
      dbms_output.put_line('In the range of 1 and 3');
   end if;
END;
/
IN BEGIN
   if 1 IN (1,2,3)
   then
      dbms_output.put_line('In the current set');
   end if;
END;
/
IS EMPTY DECLARE
   type list is table of integer;
   a list: = list();
BEGIN
   if a IS EMPTY
   then
      dbms_output.put_line('A is empty');
   end if;
END;
/
IS NULL DECLARE
   var Boolean;
BEGIN
   if var IS NULL
   then
      dbms_output.put_line('var is null');
   end if;
END;
/
IS A SET DECLARE
   type list is table of integer;
   a list: = list();
BEGIN
   if a IS A SET
   then
      dbms_output.put_line('A is a set');
   end if;
END;
/
LIKE BEGIN
   if 'Str%' LIKE 'String'
   then
      dbms_output.put_line('We have a Match');
   end if;
END;
/
MEMBER OF DECLARE
   type list is table of number;
   n varchar2(10) := 'One';
   a list := list('One', 'Two', 'Three');
BEGIN
   if n MEMBER OF a
   then
      dbms_output.put_line('n is a member');
   end if;
END;
/
NOT BEGIN
   if NOT FALSE
   then
      dbms_output.put_line('It must be TRUE');
   end if;
END;
/
OR BEGIN
   if 1 = 1 OR 1 = 2
   then
      dbms_output.put_line('1 is either 1 or 2');
   end if;
END;
/
SUBMULTISET DECLARE
   type list is table of integer;
   a list := list(1,2,3);
   b list := list(1,2,3,4);
BEGIN
   if a SUBMULTISET b
   then
      dbms_output.put_line('We have a subset match');
   end if;
END;
/

The order of precedence is

Order
Operator
Definition
1
**
Exponentional
2
+, -
Identity and Negation
3
*, /
Multiplication and Division
4
+, -, ||
Addition, Subtraction and Concatenation
5
==,<,>,<=,>=,<>,!=,~=,^=,BETWEEN, IN, IS NULL, LIKE
Conjunction
6
AND
Comparison
7
NOT
Logical Negation
8
OR
Inclusion

Branching

You can redirect the flow of the program by using goto statements, but in todays modern day programming you should 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 (Guard on Entry)

-- SQL version
LOOP
  [counter_management_statement;]
  if not entry_condition
  
then
     exit;
  end if;
  repeating_statements;
END LOOP;

-- PL/SQL version
LOOP
  [counter_management_statement;]
  exit when not entry_condition
  repeating_statements;
END LOOP;

Loop (Guard on Exit)

-- SQL version
LOOP
  [counter_management_statement;]
  repeating_statements;
  if exit _condition
  
then
     exit;
  end if;
END LOOP;

-- PL/SQL version
LOOP
  [counter_management_statement;]
  repeating_statements;
  exit when exit_condition
END LOOP;

Note: the guard on exit loop will at least execute the code once.

For .. Loop

-- Range loop
for counter in [reverse] low .. high
loop
  code;
end loop;

for <row_index> in [ cursor | select statement ]
loop
  code;
end loop;

-- EXAMPLE
create or replace procedure for_loop_test
IS
   counter number,
   counter2 number default 1;
BEGIN
   for counter in 1..10
   loop
      dbms_output.put_line('Counter: ' || counter || ' Counter2: ' || counter2);
      counter2 := counter2 + 1;
   end loop;
END;

Note: See for cursor for more for loops using cursors

Forall Loop see forall for more details
While Loop

while <condition>
loop
  [counter_management_statement]
  code;
end loop;

-- EXAMPLE
create or replace procedure while_loop_test
IS
   counter number default 0;
BEGIN
   while (counter < 10)
   loop
      dbms_output.put_line('Counter: ' || counter);
      counter := counter + 1;
   end loop;
END;

Continue statement
(Oracle 11g)
BEGIN
  FOR i IN 1 .. 5
  LOOP
     IF MOD(i,2) = 0 THEN
       CONTINUE;                       -- return to the start of the loop
     END IF;
     dbms_output.put_line(i);
  END LOOP;
END;

Running Code

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

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 objects select object_name from dba_objects where status = 'INVALID';
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.

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 invokers rights create or replace procedure test_procedure authid current_user is

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

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
  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.