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] -- The minimum amount of code could be |
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 |
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 |
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 -- The pipelined function |
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 -- Named Notation -- Mixed Notation, Positional must come first Note: when using mixed positional notation must come first, then Named. |
Function and Procedure rules
Pass-by-Value Functions |
|
Pass-by-Reference Functions |
|
Pass-by-Value Procedures |
|
Pass-by-Reference Procedure |
|
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); |
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 Note: when other is the catch all exception handler |
User-Defined Exception | DECLARE -- More complex example |
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 Note: the negative number returned maps to the Oracle predefined exceptions. |
SQLERRM | EXCEPTION 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; 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'; relative_date := TO_DATE('04-JAN-68'); -- Default format mask relative_date := CAST('04-JAN-68' AS DATE); -- Relative 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 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 DECLARE Note: you can also use the NOT NULL option |
Long and Long Raw Datatypes | var1 LONG; -- Implicity size at 0 var1 LONG := 'CAR'; 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 DECLARE Note: you can also use the NOT NULL option |
Date Datatype | var1 DATE; -- Implicity assigns a null value 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 DECLARE |
NCHAR and NVARCHAR2 Datatypes | var1 NCHAR; -- Implicity sized at 1 character 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; Note: you can also use the following contants as well |
Number Datatypes | var1 NUMBER; -- A null number with 38 digits var4 DOUBLE PRECISION; -- A null number with 126 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 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 Note: you must use the DBMS_LOB package to read and write to a NCLOB |
Composite Datatypes |
|
Records | -- Simple record -- A record within a record |
VARRAY Datatype | -- SQL definition -- PL/SQL definition var1 varray_name; DECLARE Note: see PL/SQL and SQL for more details on varrays |
Nested Table Datatype | -- SQL Definition DECLARE Note: see PL/SQL and SQL for more details on Nested Tables |
Associate Array Datatype | -- SQL Definition -- PL/SQL definition DECLARE Note: see PL/SQL and SQL for more details on Associate Arrays |
Reference Cursor | -- Weakly typed cursor -- Example 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 |
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 -- The new way 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> if <condition> if <condition> |
Case statement | case <selector> 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 |
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 -- PL/SQL version |
Loop (Guard on Exit) | -- SQL version -- PL/SQL version Note: the guard on exit loop will at least execute the code once. |
For .. Loop | -- Range loop for <row_index> in [ cursor | select statement ] -- EXAMPLE Note: See for cursor for more for loops using cursors |
Forall Loop | see forall for more details |
While Loop | while <condition> -- EXAMPLE |
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 |
|