A short PL/SQL Course

Application Structure

The basic structure of a PL/SQL program consist of three parts:

Declare
...
Begin
...
End;

Using PL/SQL, we can develop several different kinds of codes:

1. PL/SQL script. Codes that execute using external files.
2. Database stored codes - once executed the code is stored in the database. This code consist several types:

• Stored function (returns one value)
• Stored procedure (does not return value - except through the parameter).
• Triggers (linked to a specific action to a table.

Stored procedures and functions can be grouped together into a package.

For the code below, we will create a sample table called EmployeeTab as follows:

CREATE TABLE EMPLOYEETAB
(
  ENO VARCHAR2(20),
  ENAME VARCHAR2(40),
  EDEPT VARCHAR2(20),
  SUPNO VARCHAR2(20),
  PHONE VARCHAR2(20),
  JTITLE VARCHAR2(20)
);

CREATE TABLE EMPLOYEETABHIST
( ENO VARCHAR2(20 BYTE),
COLNAME VARCHAR2(20 BYTE),
OLDVAL VARCHAR2(40 BYTE),
NEWVAL VARCHAR2(40 BYTE),
CHANGEDATE DATE
);

Stored Procedure/Function

To create a stored procedure, the syntax is as follows:

Create or replace procedure storeData
/*
Sample stored procedure to store Employee Data
Author  Strovek
Date    Jul 17, 2009
*/
(pEno IN Varchar2,
pEName In Varchar2)
IS
Begin -- start of code
-- Insertion code here
Insert into EmployeeTab (eNo, eName) Values (pEno, pEName);

End storeData;

Note the code above:

1. The code above takes in two parameters and both are input parameters.
2. PL/SQL codes are not case sensitive so pEno and peno are the same.
3. Comments can be added any where either surrounded by /* ... */ or -- (codes after -- is comment until the end of the line. while everything between the * are all comments irrespective of the number of lines.

Here is an example of a function which sums two parameters entered to it.

Create or Replace
Function sumFunc
  (pVar1 IN Number, pVar2 IN Number)
   Return Number IS
   tmpNum   Number;
Begin
  tmpNum := pVar1 + pVar2;
  return tmpNum;
End sumFunc;

Note the code above:

1. Both parameter above are input parameters and in this example both are of type Number.
2. This function will return a value of type number.
3. After the keyword IS, we can declare our variables (this represent the Declare portion of the PL/SQL block.
4. I used a parameter to return the value just to show how the local variables can be used. The code could be changed as follows:

Create or Replace
Function sumFunc
(pVar1 IN Number, pVar2 IN Number)
Return Number IS
Begin
 return pvar1 + pvar2;
End sumFunc;

One nice thing about functions, is you can call the function as another column in your SQL statement. For example:

Select eNo, eName, sumFunc(5, 3) Age from EmployeeTab;


Package

Package allows the developer to group codes that are related together. So assuming that the above codes are common than we can group them together in a package. Package consist of two parts:

1. Declaration
2. Body

The declaration is as follows:

create or replace PACKAGE TestPack
AS
procedure storeData
/*
Sample stored procedure to store Employee Data
Author  Strovek
Date    Jul 17, 2009
*/
(pEno IN Varchar2,
pEName In Varchar2);
-------------
Function sumFunc
  (pVar1 IN Number, pVar2 IN Number)
   Return Number;
-------------
End TestPack;

The body contains the actual codes and the header needs to match the declaration as follows:

create or replace
PACKAGE BODY TestPack
AS
procedure storeData
/*
Sample stored procedure to store Employee Data
Author  Strovek
Date    Jul 17, 2009
*/
(pEno IN Varchar2,
pEName In Varchar2)
IS
Begin -- start of code
  -- Insertion code here
  Insert into EmployeeTab (eNo, eName) Values (pEno, pEName);

End storeData;
------------------
Function sumFunc
  (pVar1 IN Number, pVar2 IN Number)
   Return Number IS
   tmpNum   Number; 
Begin
  tmpNum := pVar1 + pVar2;
  return tmpNum;
End sumFunc;

End TestPack;

To use the codes in the package, we need to prefix the procedure or function with the package name, for example:

Select eNo, eName, TestPack.sumFunc(5, 3) Age from EmployeeTab;

Triggers

Triggers are special type of stored procedures that will be executed based on an event. For example, in the case of the employeeTab, I would like to keep track of changes to the columns in the table. For this example, I make an assumption that eNo is the primary key and will not change. All changes will be stored in the EmployeeTabHist.

create or replace
Trigger trEmp_AttribUpd
Before Update Of eName, eDept, SupNo, Phone, jTitle on EmployeeTab
For Each Row
Declare
/*
 Create a history of the changes in the employee attributes
 Author Strovek
 Date Jul 18, 2009
 Revised
*/
 Procedure ins_empattribhist(pParam IN Varchar2, pOldVal IN Varchar2, pNewVal IN Varchar2)
  IS
 Begin
  insert into employeetabhist
   (eNo, colName, oldVal, newVal, changedate) values
   (:new.eNo, pParam, pOldVal, pnewVal, sysdate);

 End ins_empattribhist;

Begin
       If :old.eName <> :new.eName Then
          ins_empattribhist('Name', :old.eName, :new.eName);
       End If;
       If :old.eDept <> :new.eDept Then
          ins_empattribhist('Dept', :old.eDept, :new.eDept);
       End If;
       If :old.SupNo <> :new.SupNo Then
          ins_empattribhist('SuperVisor', :old.SupNo, :new.SupNo);
       End If;
       If :old.Phone <> :new.Phone Then
          ins_empattribhist('Phone', :old.Phone, :new.Phone);
       End If;
       If :old.jTitle <> :new.jTitle Then
          ins_empattribhist('Name', :old.jTitle, :new.jTitle);
       End If;

End;

Note the code above:

1. Since some of the codes are common, we can reduce the amount of codes written by create a procedure within the trigger. This procedure is only accessible by the trigger since it is within the declaration section of the code.
2. Some common columns can be obtained directly and does not need to passed to the code via parameters (e.g. eNo, which is the same irrespective of the attribute changed and the changedate which is the sysdate).

Error Handling

Similar to Java and Vb.Net, you can also catch and throw exceptions in PL/SQL. Before we go into that note that it is possible to create your own error code, Oracle has reserved the numbers -20000 to -20999 for this purpose.

Going back to the first section, we have the code block:

Declare
 ...
Begin
 ...
End;

There is an optional section between Begin and End called Exception. So the code can as follows:

Declare
...
Begin
...
Exception
...
End;

Similar to the try catch, you can specify multiple whens to handle different error codes. Some examples found here.

Using an earlier example of store data, we can further enhance it to prevent insertion of existing ENo as follows:

Create or replace procedure storeData2
/*
Sample stored procedure to store Employee Data
Author  Strovek
Date    Jul 17, 2009
*/
(pEno IN Varchar2,
pEName In Varchar2)
IS
Cursor chkRec IS
Select eName from employeeTab where eNo = pENo;
vChkRec   chkRec%ROWTYPE;
vRecExist Boolean;

Begin -- start of code
vRecExist := False;
Open chkRec;
Fetch chkRec INTO vChkRec;
If chkRec%NOTFOUND Then
Insert into EmployeeTab (eNo, eName) Values (pEno, pEName);
Else
vRecExist := True;
End If;
Close chkRec;

If vRecExist Then
Raise_application_error(-20101, 'Eno ' || pENo || ' already exist, cannot insert record');
End If;

End storeData2;

Cursors

In order to retrieve records from a database via the sql statement, a cursor is used. The cursor can either be used implicitly or explicitly.

Implicit means that Oracle will open, fetch the records and close the cursor for you automatically.

An example of implicit cursor is as follows:

Set serveroutput on size 100000
Declare
  cursor cGetEmp IS
    Select eName, Phone from EmployeeTab;

Begin
  For vRec in cGetEmp Loop
    dbms_output.put_line(vRec.eName || ' phone number is ' || vRec.Phone);
  End Loop;
End;

Note on the code above:

1. I did not have to declare vRec. It is implied.
2. I did not have to open the cursor. The records are fetched into vRec and once all the records are retrieve, the cursor is closed.

By comparison of an explicit cursor is as follows:

Set serveroutput on size 100000
Declare
  cursor cGetEmp IS
    Select eName, Phone from EmployeeTab;
  vGetEmp   cGetEmp%ROWTYPE;

Begin
  Open cGetEmp;
  Fetch cGetEmp INTO vGetEmp;
  While cGetEmp%FOUND
  Loop
    dbms_output.put_line(vGetEmp.eName || ' phone number is ' || vGetEmp.Phone);
    Fetch cGetEmp INTO vGetEmp;
  End Loop;
  Close cGetEmp;
End;

Note on the code above:

1. vGetEmp had to be declared and to ease things, we use a special type called %ROWTYPE so the record type is changed depending on the cursor declaration.
2. The cursor has to be opened and closed using the Open and Close statement.
3. Each record has be fetched and we have to check for the end of the records using %FOUND condition.
   

Closing

This short cause is meant to only introduce you to PL/SQL programming and not meant to be comprehensive. Note that when writing to code to avoid using reserved words for variables. You can learn more about the Loops structure, logic, predefined function from the Oracle Manual.

Here is the one for Oracle 9i from Oracle.

When writing code, you can use Oracle SQL Developer (a freeware from Oracle) to write the codes. That way during compilation (especially for stored procedure/function/packages), you can easily find the cause why your code has error.

Any comments are welcome.