Oracle PL/SQL Programming Tutorial with Examples

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

......

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;

IF <condition 1> THEN
    Job 1;
[ELSIF <condition 2> THEN
     Job 2;
]
[ELSE
     Job n + 1;
]
END IF;

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;

LOOP
 -- Do something here
EXIT WHEN <Condition>;
END LOOP;

x := 0;
Loop
 x := x + 1;
 y := y - x;
Exit When x > y;
End Loop;

FOR v_Index IN <Min value> .. <Max value>
LOOP
 -- Do something here
END LOOP;

x := 0;
For v_Idx In 1 .. 100 Loop
 x := x + 1;
End Loop;

WHILE <Condition> LOOP
 -- Do something here
END LOOP;

v_Text Varchar2(100);
...

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

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;

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


 

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;

-- Declare a variable.
-- Is row type

v_Variable_name Table_Name%Rowtype;

-- Example
v_Emp Employee%Rowtype;

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;

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

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;

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

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;

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;

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

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;

-- Cursor declaration has no parameters:
CURSOR <Cursor_Name>
IS
<Select_Statement>


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


 

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

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;

-- Use the 'for loop' to fetch the Implicit cursor:

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

   -- Statements..

END LOOP;

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;

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

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

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

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

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

-- When calling the function, must declare a variable

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

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

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

Select Emp.Emp_Id
     ,Get_Emp_First_Name(Emp.Emp_Id) Emp_First_Name
From   Employee Emp;

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

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

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