Oracle PL/SQL Programming Tutorial

1- Introduction

PL/SQL (Procedural Language/Structured Query Language) is a procedural programming language used for Oracle SQL. It is an extension of Oracle SQL.


PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.
 
Note: In the following illustrative images, I use the version 8.x of  PL/SQL Developer. However, there is no difference if you use the version 10.x of PL/SQL Developer or another version.

2- Starting with PL/SQL need what?

To quickly access to  PL/SQL you need a programming tool. In my experience, you can use  PL/SQL  Developer that is a visual tool working with Oracle and being used to programme  PL/SQL.
You can view the installation guide and configure PL/SQL Developer at:
In this document, I use LearningSQL database( A small database is used to instruct how to learn SQL in  o7planning.org website). You can create this database as the following instruction:

3- Overview of PL/SQL

There are some definitions you need to master when programming with  PL/SQL:
  1. Each SQL statement ends with a semicolon (;)
  2. Data Definition Language (DDL) statement is not used in PL/SQL
  3. That SELECT..INTO statement returns many lines may cause exception (> 1 line).
  4. That SELECT..INTO statement does not return any line may cause exception
  5. DML statement may have effect on many lines.
  6. Use operator := to grant value for a variable.
-- assign value to variable
x  := 1;

-- Insert:
Insert into Department (Dept_Id, Dept_No, Dept_Name, Location)
 values (1, 'D1', 'HR', 'Chicago');

-- Catch exception:
Begin
  Select Dept.Dept_Id
  into v_Dept_Id
  from Department Dep;
Exception when too_many_rows then
  Dbms_Output.put_line('Error:'||Sqlerrm);
End;

......
PL/SQL is organized by blocks of statements. A block of statements can contain sub-block of statements in it.
Declare
-- The declaration block - (Optional)
-- Declare the variables used in the body
v_Location Varchar2(100);
Begin
-- Body of block
-- Statements ...
v_Location := 'Chicago';
-- ....
Exception
-- Catch exception block - Optional
-- Catching the various exceptions.
When No_Data_Found Then
   -- Exception when SELECT ..INTO statement return nothing
   -- (Optional)
   -- Do something here.
   -- Or null if do nothing
   Null;
When Too_Many_Rows Then
   -- Exception when the SELECT .. INTO statement returns multiple records ( > 1)
   -- (Optional)
   Null;
When Others Then
   -- Other exceptions
   Null;
End;

4- The simple PL/SQL statements

Here I introduce the overview of basic statements of  PL/SQL. You will understand it through examples in the next sections.

4.1- If-elsif-else

Syntax:
IF <condition 1> THEN
    Job 1;
[ELSIF <condition 2> THEN
     Job 2;
]
[ELSE
     Job n + 1;
]
END IF;
Example:
If v_Option = 1 Then
   v_Action := 'Run';
Elsif v_Option = 2 Then
   v_Action := 'Backup';
Elsif v_Option = 3 Then
   v_Action := 'Stop';
Else
   v_Action := 'Invalid';
End If;

4.2- LOOP

Syntax:
LOOP
 -- Do something here
EXIT WHEN <Condition>;
END LOOP;
Example:
x := 0;
Loop
 x := x + 1;
 y := y - x;
Exit When x > y;
End Loop;

4.3- FOR LOOP

Syntax:
FOR v_Index IN <Min value> .. <Max value>
LOOP
 -- Do something here
END LOOP;
Example:
x := 0;
For v_Idx In 1 .. 100 Loop
 x := x + 1;
End Loop;

4.4- WHILE LOOP

Syntax:
WHILE <Condition> LOOP
 -- Do something here
END LOOP;
Example:
v_Text Varchar2(100);
...

While Length(v_Text) < 50 Loop
   v_Text := v_Text || '00';
End Loop

5- Starting with PL/SQL using the PL/SQL Developer

First, you need to open  PL/SQL Developer, and log in user  learningsql:
In PL/SQL Developer, open new SQL window:
Write a simple code to sum two numbers.
Declare
  v_Result Number;
  -- Declare a variable of type Number with value of 50
  v_a Number := 50;
  -- Declare a variable of type Number with value 100
  v_b Number := 100;
Begin
  -- Print out Console Screen
  Dbms_Output.Put_Line('v_a= ' || v_a);
  -- Print out Console Screen
  Dbms_Output.Put_Line('v_b= ' || v_b);
  -- Sum
  v_Result := v_a + v_b;
  -- Print out Console Screen
  Dbms_Output.Put_Line('v_Result= ' || v_Result);
End;
Click the icon  or F8 to execute the code.
Results of running the example:

6- The common data types and declarations

6.1- Numeric data type

The numeric data types in PL/SQL
Data Type Description
PLS_INTEGER Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
BINARY_INTEGER Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
BINARY_FLOAT Single-precision IEEE 754-format floating-point number
BINARY_DOUBLE Double-precision IEEE 754-format floating-point number
NUMBER(prec, scale) Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0.
DEC(prec, scale) ANSI specific fixed-point type with maximum precision of 38 decimal digits.
DECIMAL(prec, scale) IBM specific fixed-point type with maximum precision of 38 decimal digits.
NUMERIC(pre, secale) Floating type with maximum precision of 38 decimal digits.
DOUBLE PRECISION ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
FLOAT ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
INT ANSI specific integer type with maximum precision of 38 decimal digits
INTEGER ANSI and IBM specific integer type with maximum precision of 38 decimal digits
SMALLINT ANSI and IBM specific integer type with maximum precision of 38 decimal digits
REAL Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)
The most common type:
Data type Declaration
Number v_Amount Number(15,2)
v_Salary Number;
Integer v_Age Integer;
Float v_Amount Float;
Double  
Real  

6.2- Text data types

The most common type
Data type Declaration
Varchar2 v_First_Name Varchar2(32);
   

6.3- Date/time data types

Data type Declaration
Date v_Birthday Date;
   

6.4- %type data type

This is the structure of the EMPLOYEE table:
First_Name column in Employee table contains the type of Varchar2 data which has 20 characters in length. To declare a variable that may contain the value of this column, you can declare by using the following way:
-- Declaring a varchar2 variable, length 20.
-- This variable can store value for column FIRST_NAME (of EMPLOYEE table)
v_First_Name Varchar2(20);

-- Assign values to v_First_Name, taken from the query.
-- Exception may occur when length of variable < length of string value
Select Emp.First_Name
into v_First_Name
From Employee Emp
Where Emp.Emp_Id = 1;

-- Declaring a varchar2 variable, length 30.
-- This variable can also store value for the column FIRST_NAME
v_First_Name2 Varchar2(30);

-- Safe:
v_First_Name Employee.First_Name%Type;


 
Example:
Declare
  v_Emp_Id     Employee.Emp_Id%Type := 1;
  v_First_Name Employee.First_Name%Type;
Begin
  Select Emp.First_Name
  Into   v_First_Name
  From   Employee Emp
  Where  Emp.Emp_Id = v_Emp_Id;
  ----
  Dbms_Output.Put_Line('v_First_Name= ' || v_First_Name);
Exception
  When No_Data_Found Then
     -- When SELECT .. INTO returns nothing.
     Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;

6.5- %Rowtype data type

Syntax:
-- Declare a variable.
-- Is row type

v_Variable_name Table_Name%Rowtype;

-- Example
v_Emp Employee%Rowtype;
Example:
Declare
  v_Emp_Id Employee.Emp_Id%Type := 1;
  -- Declare a variable
  -- Is row type of Employee table.
  v_Emp Employee%Rowtype;
Begin
  Select * Into v_Emp From Employee Emp Where Emp.Emp_Id = v_Emp_Id;
  ----
  Dbms_Output.Put_Line(' First_Name= ' || v_Emp.First_Name);
  Dbms_Output.Put_Line(' Last_Name= ' || v_Emp.Last_Name);
Exception
  When No_Data_Found Then
     -- When SELECT .. INTO statement returns nothing.
     Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;
The results:

6.6- Record data type

You can define Record data type that contains some columns. Syntax:
-- Declare your data type.

TYPE Type_Record_Name IS
    RECORD  (
            Col1  Datatype1  [NOT NULL{:=|DEFAULT} expression],
            Col2  Datatype2  [NOT NULL{:=|DEFAULT} expression],
             ...
     );


-- Variable declaration using data type:
variable_name  Type_Record_Name;
Example:
Declare
 v_Emp_Id Employee.Emp_Id%Type := 1;
 -- Define Record data type has 3 column.
 Type Emp_Name_Type Is Record(
     Emp_First_Name Employee.First_Name%Type
    ,Emp_Last_Name  Employee.Last_Name%Type
    ,Emp_Full_Name  Varchar2(50));
 ----
 -- Define a variable of type Emp_Name_Type
 v_Emp Emp_Name_Type;
Begin
 Select Emp.First_Name
       ,Emp.Last_Name
       ,Emp.First_Name || ' ' || Emp.Last_Name
 Into   v_Emp
 From   Employee Emp
 Where  Emp.Emp_Id = v_Emp_Id;
 ----
 Dbms_Output.Put_Line(' First_Name= ' || v_Emp.Emp_First_Name);
 Dbms_Output.Put_Line(' Last_Name= ' || v_Emp.Emp_Last_Name);
 Dbms_Output.Put_Line(' Full_Name= ' || v_Emp.Emp_Full_Name);
Exception
 When No_Data_Found Then
    -- When SELECT .. INTO statement return nothing.
    Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;
The results run the example:

6.7- Table data type

You can define a new data type that can store many elements. It is TABLE type.

Features of TABLE type:
  • Data type of TABLE is alike to an array, but it has innumerable elements.
  • Indexes of TABLE type are unnecessarily consecutive. For example, TABLE has three elements with indexes of 1, 3, 5.
Syntax:
-- Syntax:

TYPE  <Table_Name>
IS TABLE OF  <Data_Type>   [NOT NULL]
INDEX BY BINARY_INTEGER;

-- Example
-- Define TABLE data type, contains elements of type VARCHAR2 (50)
TYPE My_Tbl
IS TABLE OF Varchar2(50)
INDEX BY BINARY_INTEGER;
Example:
Declare
  -- Define TABLE data type:
  Type My_Tbl Is Table Of Varchar2(50) Index By Binary_Integer;
  -- Define varable of type My_Tbl.
  v_Emps My_Tbl;
Begin
  v_Emps(1) := 'One';
  v_Emps(2) := 'Two';
  v_Emps(3) := 'Three';
  ----
  Dbms_Output.Put_Line('Element Count = ' || v_Emps.Count);
  ---
  For i In v_Emps.First .. v_Emps.Last Loop
     Dbms_Output.Put_Line('Element at ' || i || ' = ' || v_Emps(i));
  End Loop;
End;
The results run the example:
The functions of TABLE data type:
Tên hàm/Thuộc tính Ý nghĩa Ví dụ sử dụng
• DELETE Deletes rows in a table. v_tbl.delete(3);
• EXISTS Return TRUE if the specified entry exists in the table. v_e:=v_tbl.exists;
• COUNT Returns the number of rows in the table. v_count:= v_tbl.count;
• FIRST Returns the index of the first row in the table. v_first_idx:=v_tbl.first;
• LAST Returns the index of the last row in the table. v_last_idx:=v_tbl.last;
• NEXT Returns the index of the next row in the table after the specified row. v_idx:=v_tbl.next(2);
• PRIOR Returns the index of the previous row in the table be-fore v_idx:=v_tbl.prior(2);
Example:
Declare
  -- Define TABLE data type.
  Type t_City_Type Is Table Of Varchar2(30) Index By Binary_Integer;
  -- Declare a variable of type T_City_Type.
  t_City       t_City_Type;
  v_City_Index Binary_Integer;
  v_e          Boolean;
Begin
  t_City(100) := 'Chicago';
  t_City(101) := 'Chicago';
  t_City(200) := 'Hanoi';
  t_City(301) := 'Tokyo';
  Begin
     -- Check exists element at index 500.
     v_e := t_City.Exists(500);
     If v_e Then
        Dbms_Output.Put_Line('Exists element At 500');
     Else
        Dbms_Output.Put_Line('Not Exists element At 500');
     End If;
     --
     -- Delete element at index 101
     t_City.Delete(101);
     --
     -- First index
     v_City_Index := t_City.First;
     Dbms_Output.Put_Line('First= ' || v_City_Index);
     --
     Loop
        Dbms_Output.Put_Line('Element at ' || v_City_Index || ' = ' ||
                             t_City(v_City_Index));
        Exit When v_City_Index = t_City.Last;
        -- Returns the index of the next row in the table after the specified row.
        v_City_Index := t_City.Next(v_City_Index);
     End Loop;
     -- Raise No_Data_Found exception
     Raise No_Data_Found;
  Exception
     When No_Data_Found Then
        Dbms_Output.Put_Line('the Last City Has Been Reached.');
  End;
End;
The results run the example:

6.8- Array data type

Syntax:
-- Define Array data type
TYPE <varray_type_name>
IS VARRAY(n)
OF <element_type>;

-- Example, declare an array, declare an array of 5 elements, and elements of type VARCHAR2(10);
TYPE cityarray
IS VARRAY(5)
OF Varchar2(10);
 
An array has n elements. Its elements are numbered consecutively from 1 to n.
Example:
Declare
 -- Define Array data type.
 -- containing data type of VARCHAR2 (50)
 Type Emp_Array Is Varray(5) Of Varchar2(50);
 -- Define Array data type, containing data type of Integer
 Type Salary_Array Is Varray(5) Of Integer;
 ---
 v_Names    Emp_Array;
 v_Salaries Salary_Array;
 v_Count    Integer;
Begin
 -- Initialize the value of array elements.
 v_Names := Emp_Array('KING'
                     ,'JONES'
                     ,'FORD'
                     ,'SMITH'
                     ,'BLAKE');
 -- Initialize the value of array elements.
 v_Salaries := Salary_Array(5000
                           ,2975
                           ,3000
                           ,800
                           ,2850);
 -- Element count.                            
 v_Count := v_Names.Count;
 Dbms_Output.Put_Line('Count = ' || v_Count);
 ---
 For i In 1 .. v_Count Loop
    Dbms_Output.Put_Line('Employee = ' || v_Names(i) || ', Salary = ' ||
                         v_Salaries(i));
 End Loop;
End;
The results run the example:

7- Cursor

7.1- What is Cursor?

Cursor is a type of structured variable that allows us to process data with various lines. The number of lines is dependent on the data querying statement after line. During the processing, we manipulate cursor through each data line. This data line is specified by a cursor. By moving the cursor, we can extract the whole data of a current line.
Cursor declaration syntax:
-- Cursor declaration has no parameters:
CURSOR <Cursor_Name>
IS
<Select_Statement>


-- Cursor declaration has parameters:
CURSOR <Cursor_Name>(<Parameter_List>)
IS
<Select_Statement>


 
Example:
-- Cursor declaration has no parameters:
Cursor Emp_Cur Is
 Select Emp.Emp_Id
       ,Emp.First_Name
       ,Emp.Last_Name
 From   Employee Emp;

-- Cursor declaration has parameters:
Cursor Emp_Cur(p_Dept_Id   Number
        ,p_Branch_Id Number)
Is
Select Emp.Emp_Id
   ,Emp.First_Name
   ,Emp.Last_Name
   ,Emp.Assigned_Branch_Id
   ,Emp.Dept_Id
From   Employee Emp
Where  (Emp.Dept_Id = p_Dept_Id Or p_Dept_Id Is Null)
And    (Emp.Assigned_Branch_Id = p_Branch_Id Or p_Branch_Id Is Null);
There are two types of cursor:
  1. Explicit cursor
  2. Implicit cursor.
The concept of explicit means when using it you need to write the statement of opening and closing cursor distinctly. To implicit cursor, you needn't write opening and closing statements.
The properties of the Cursor:
Thuộc tính Ý nghĩa
%isopen  Return true if cursor is openning
%notfound  Return true if there is no next line
%found  Return true if there is next line
%rowcount return the row count was fetched.

7.2- Explicit Cursor

Steps of declaring and using explicit cursor:
Example:
Declare
    -- Declaring a Cursor with 2 parameters.
    Cursor Emp_Cur
    (
        p_Dept_Id   Number
      ,p_Branch_Id Number
    ) Is
        Select Emp.Emp_Id
                ,Emp.First_Name
                ,Emp.Last_Name
                ,Emp.Assigned_Branch_Id
                ,Emp.Dept_Id
        From   Employee Emp
        Where  (Emp.Dept_Id = p_Dept_Id Or p_Dept_Id Is Null)
        And    (Emp.Assigned_Branch_Id = p_Branch_Id Or p_Branch_Id Is Null);
    ---
    -- Declare a variable type of ROWTYPE, based on the newly created Cursor.
    v_Emp Emp_Cur%Rowtype;
    ---
    v_Dept_Id   Number := 1;
    v_Branch_Id Number;
    ---
    v_Row  Integer := 0;
    v_Open Boolean;
Begin
    -- Check cursor is openning?
    If Emp_Cur%Isopen Then
        Dbms_Output.Put_Line('Cursor opened');
    Else
        Dbms_Output.Put_Line('Cursor not open');
    End If;
    --
    Dbms_Output.Put_Line('Opening cursor...');
    -- Open Cursor (Pass input parameters).
    Open Emp_Cur(v_Dept_Id, v_Branch_Id);
    -- Using loop
    Loop
        -- Get row of data from Cursor
        -- Each time Fetch cursor moves one line
        -- (Downward).
        Fetch Emp_Cur
            Into v_Emp;
        -- Conditions to exit the loop
        Exit When Emp_Cur%Notfound;
        -- Process the data
        v_Row := v_Row + 1;
        Dbms_Output.Put_Line(v_Row || ' - First_Name: ' || v_Emp.First_Name || ' - Last_Name: ' || v_Emp.Last_Name);
    End Loop;
    --
    Dbms_Output.Put_Line('Closing cursor...');
    -- Close Cursor.
    Close Emp_Cur;
End;
The results run the example:

7.3- Implicit Cursor

To implicit cursor, you don't need to write open/close statement clearly.
Use the 'for loop' to fetch the cursor, with the syntax:
-- Use the 'for loop' to fetch the Implicit cursor:

FOR <v_Record> in <cursor_name>(<Parameter_values>) LOOP

   -- Statements..

END LOOP;
Example:
Declare
  -- Declaring a Cursor with 2 parameters.
  Cursor Emp_Cur(p_Dept_Id   Number
                ,p_Branch_Id Number) Is
     Select Emp.Emp_Id
           ,Emp.First_Name
           ,Emp.Last_Name
           ,Emp.Assigned_Branch_Id
           ,Emp.Dept_Id
     From   Employee Emp
     Where  (Emp.Dept_Id = p_Dept_Id Or p_Dept_Id Is Null)
     And    (Emp.Assigned_Branch_Id = p_Branch_Id Or p_Branch_Id Is Null);
  ---
  ---
  v_Dept_Id   Number := 1;
  v_Branch_Id Number;
  ---
  v_Row Integer := 0;
Begin
  -- Check cursor is open
  If Emp_Cur%Isopen Then
     Dbms_Output.Put_Line('Cursor opened');
  Else
     Dbms_Output.Put_Line('Cursor not open');
  End If;
  --
  -- Using loops to fetch cursor
  -- No need: open/close/fetch.
  --
  For v_Emp In Emp_Cur(v_Dept_Id
                      ,v_Branch_Id) Loop
     --
     v_Row := v_Row + 1;
     Dbms_Output.Put_Line(v_Row || ' - First_Name: ' || v_Emp.First_Name ||
                          ' - Last_Name: ' || v_Emp.Last_Name);
  End Loop;
End;

8- Procedure

A group of statements performing some function can be gathered in a procedure for the purpose of increasing the capability of handling, Reuse, security, data safety, and utility in development.
Procedure can be saved on database as a object of database and ready for reuse. Procedure then is called  Store procedure. To  Store procedure,after  Store procedure is saved, it is translated into  p-code so its performing capability is raised. 
Procedure does not directly return value like function.
The syntax to create a procedure:
-- procedure_name:  Name of procedure
-- argument:  
-- mode:  IN or OUT or IN OUT, default is IN
-- datatype:  Data type

CREATE [OR REPLACE] PROCEDURE <procedure_name>
          [
           (argument1  [mode1]  datatype1,
            argument2  [mode2]  datatype2,
           ...)
          ]
     IS | AS
BEGIN
   -- PL/SQL Block;
END;
Example:
-- For example a procedure with no parameters.
CREATE OR REPLACE Procedure Do_Something AS
   -- Declare variables here
Begin
  -- Do something here.
End;

-- For example a procedure with parameters
-- input parameters, and output parameters.
CREATE OR REPLACE Procedure Do_Something(p_Param1 Varchar2,
                                                                 v_Param Out Varchar2)
AS
   -- Declare variables here
Begin
  -- Do something here.
End;
Drop procedure
-- Drop procedure

DROP PROCEDURE <Procedure_Name>
The steps for a procedure:

Procedure Example:

This is an example of creating the first procedure on  PL/SQL Developer. This example will be a model helping you both programme and check error if there is any problem.
  1. Create a Procedure
  2. Compile the procedure
  3. Run the procedure
  4. Debug the procedure by using PL/SQL Developer to check how the program runs.
If you are a beginner of  PL/SQL, you should watch this section thoroughly. This helps you easily understand the next issues mentioned in this document.
Create a procedure
Enter the name of procedure, and parameters will be written later:
  • Get_Employee_Infos
A procedure is created by  PL/SQL Developer. However, you need to correct the list of parameters and write code to this procedure.
Modify your procedure:
-- Input parameter: p_Emp_Id
-- Output parameters: v_First_Name, v_Last_Name, v_Dept_Id.
Create Or Replace Procedure Get_Employee_Infos(p_Emp_Id     Number
                                             ,v_First_Name Out Varchar2
                                             ,v_Last_Name  Out Varchar2
                                             ,v_Dept_Id    Out Number) Is
Begin
  -- Print out console.
  -- Log - use for programmers
  Dbms_Output.Put_Line('Parameter p_Emp_Id = ' || p_Emp_Id);
  --
  -- If SELECT .. INTO statement return nothing
  -- it will throw an Exception - NO_DATA_FOUND:
  --
  --
  Select Emp.First_Name
        ,Emp.Last_Name
        ,Emp.Dept_Id
  Into   v_First_Name
        ,v_Last_Name
        ,v_Dept_Id
  From   Employee Emp
  Where  Emp.Emp_Id = p_Emp_Id;
  --
  -- Print out Console.
  --
  Dbms_Output.Put_Line('Found Record!');
  Dbms_Output.Put_Line(' v_First_Name= ' || v_First_Name);
  Dbms_Output.Put_Line(' v_Last_Name= ' || v_Last_Name);
  Dbms_Output.Put_Line(' v_Dept_Id= ' || v_Dept_Id);
Exception
  When No_Data_Found Then
     -- Print out Console.
     Dbms_Output.Put_Line('No Record found with p_Emp_Id = ' || p_Emp_Id);
End Get_Employee_Infos;
Click Execute or F8 to compile the procedure. In case you are write wrong code, PL/SQL Developer will inform you.

8.1- Test procedures in PL/SQL Developer

Right-click Get_Employee_Infos procedure, and select Test:
Enter input parameters, for example:
  • p_Emp_Id = 1
Results execute procedure:
View on Screen Console:
Test different case with:
  • p_Emp_Id = 9999
View on the console:

8.2- Debug procedures in PL/SQL Developer

Debug on  PL/SQL Developer allows you to see a procedure, how the function runs, by each statement. This makes it easy find out positions of arising errors. You can watch the instruction at:
  • TODO

9- Function

Like procedure, function is also a group of  PL/SQL statements that play some role. Unlike procedure, a function will return a value right on its call.
Functions are also saved on database in form of Store procedure.
Syntax create function.
-- function_name:  Name of function
-- argument:  
-- mode: IN or OUT or IN OUT, default is IN
-- datatype:  Data type

CREATE [OR REPLACE] FUNCTION <function_name>
                [
                 (argument1  [mode1]  datatype1,
                  argument2  [mode2]  datatype2,
                  ...)
               ]
              RETURN  datatype
     IS | AS
BEGIN
   -- PL/SQL Block;
END;
Example:
-- Function has 1 parameter:
CREATE OR REPLACE FUNCTION Sum(a Integer, b Integer)
RETURN Integer
AS
Begin
  return a + b;
End;

-- A function with no parameters:
CREATE OR REPLACE FUNCTION Get_Current_Datetime
RETURN Date
AS
Begin
  return sysdate;
End;
Drop Function:
-- Drop Function

DROP FUNCTION <function_name>;
Call function:
-- When calling the function, must declare a variable

-- Declare variable c.
c Integer;
....

-- Call function
c := Sum(10, 100);

Function Example:

-- Function with input p_Emp_ID
-- And return First_Name of Employee.

Create Or Replace Function Get_Emp_First_Name(p_Emp_Id Number)
  Return Varchar2 As
  -- Declare variable v_Emp_First_Name
  v_Emp_First_Name Employee.First_Name%Type;
Begin
  Begin
     Select Emp.First_Name
     Into   v_Emp_First_Name
     From   Employee Emp
     Where  Emp.Emp_Id = p_Emp_Id;
  Exception
     When No_Data_Found Then
        -- Assigning null in case not found Employee
        v_Emp_First_Name := Null;
  End;
  --
  Return v_Emp_First_Name;
End;
The function without OUT parameters, can participate in the SQL statement, for example:
Select Emp.Emp_Id
     ,Get_Emp_First_Name(Emp.Emp_Id) Emp_First_Name
From   Employee Emp;
Results:

10- Package

PL/SQL packages are schema objects that groups logically related PL/SQL types, variables and subprograms.

Package structure:

A package is structured with two parts. Specification part defines potential interactions between the package with outsides. The Body part has the codes for various methods declared in the package specification and other private declarations, which are hidden from code outside the package.
The structure of package includes 05 components:
  1. Public variable: is a variable that outside applications can make reference to.
  2. Public procedure: include functions and procedures of package that can be called by outside applications.
  3. Private procedure: is functions and procedures available in package and they only can be called by other functions or procedures in the package.
  4. Global variable: is a variable that is declared and used in the whole package; outside applications can make reference to this variable.
  5. Private variable: is a variable that is declared in a function or procedure of the package. Only these functions or procedures can make reference to it.
Declaring Package:
-- Spec Package declaration:

CREATE [OR REPLACE] PACKAGE <package_name>
IS| AS
       -- public type and item declarations subprogram specifications
END <package_name>;

-- Body Package declaration:
CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS | AS


      -- private type and item declarations 
      -- subprogram bodies

END <package_name>;

10.1- Create a package with PL/SQL Developer

PL/SQL Developer help you quickly create spec & package body package.
Package has been created by PL/SQL Developer and hints of writing package are automatically generated. You can delete all of them.
You can delete all codes that are automatically generated by PL/SQL Developer itself in order to have an empty package:
  • PKG_EMP (Package Spec)
--
-- This is Package Spec of Package PKG_EMP
-- It declared two functions (While Package Body has 2 functions and 1 procedure).
-- The function or procedure is not declared on the Spec Package, meant only for use within the package.
--
Create Or Replace Package Pkg_Emp Is

 -- Function returns First_Name
 Function Get_First_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Employee.First_Name%Type;

 -- Function returns department name of employee
 Function Get_Dept_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Department.Name%Type;

End Pkg_Emp;
  • PKG_EMP (Package Body)
--
-- This is Package Body of Package PKG_EMP
--
Create Or Replace Package Body Pkg_Emp Is

 -- =====================================================
 -- The procedure returns the employee information
 -- Includes 2 output parameters v_First_Name, v_Last_Name
 -- =====================================================
 Procedure Get_Emp_Infos(p_Emp_Id     Employee.Emp_Id%Type
                        ,v_First_Name Out Employee.Emp_Id%Type
                        ,v_Last_Name  Out Employee.Last_Name%Type) As
 Begin
    Begin
       Select Emp.First_Name
             ,Emp.Last_Name
       Into   v_First_Name
             ,v_Last_Name
       From   Employee Emp
       Where  Emp.Emp_Id = p_Emp_Id;
    Exception
       -- Not found employee with p_Emp_Id
       When No_Data_Found Then
          v_First_Name := Null;
          v_Last_Name  := Null;
    End;
 End;

 -- =====================================================
 -- Function returns First_Name for Emp_ID
 -- =====================================================
 Function Get_First_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Employee.First_Name%Type As
    -- Declare a variable.
    v_First_Name Employee.First_Name%Type;
    v_Last_Name  Employee.Last_Name%Type;
 Begin
    -- Call procedure Get_Emp_Infos
    Get_Emp_Infos(p_Emp_Id
                 ,v_First_Name -- Out
                 ,v_Last_Name -- Out
                  );
    --
    Return v_First_Name;
 End;

 -- =====================================================
 -- Function returns Dept_Name for Emp_ID.
 -- (Department name of Employee)
 -- =====================================================
 Function Get_Dept_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Department.Name%Type As
    -- Declare a variable.
    v_Dept_Name Department.Name%Type;
 Begin
    Begin
       Select Dept.Name
       Into   v_Dept_Name
       From   Employee   Emp
             ,Department Dept
       Where  Emp.Dept_Id = Dept.Dept_Id
       And    Emp.Emp_Id = p_Emp_Id;
    Exception
       When No_Data_Found Then
          v_Dept_Name := Null;
    End;
    --
    Return v_Dept_Name;
 End;

End Pkg_Emp;

10.2- Test Package

Like procedure and function, you can test procedure/function of package. This helps you detect errors during programming process.
Test results:

11- What is Oracle Application Express?

Oracle Application Express (Oracle APEX), formerly called HTML DB, is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle application express combines the qualities of a personal database, productivity, ease of use, and flexibility with the qualities of an enterprise database, security, integrity, scalability, availability and built for the web. Application Express is a tool to build web-based applications and the application development environment is also conveniently web-based
Oracle APEX only requires skills about Oracle PL/SQL. You can see what is Oracle Application Express at: