SIS2203:Database Systems Implementation

Lecture Notes

JS

Department of Computer Science

Kyambogo University.

March 4, 2020
Contents
1 Database Programming with PL/SQL 3
1.1 Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.1.1 Introduction to PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.1.2 Benefits of PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.1.3 Creating PL/SQL Blocks . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.2 Variables in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.2.1 Handling Variables in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . 5
1.3 Identifiers and Delimiters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.3.1 PL/SQL Identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.3.2 PL/SQL Delimiters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
1.3.3 PL/SQL Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
1.3.4 PL/SQL Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1.3.5 PL/SQL Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.4 Using SQL in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.4.1 Manipulating Data in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . 9
1.4.2 Manipulating Data in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . 10
1.5 Program Structures to Control Execution Flow . . . . . . . . . . . . . . . . . . 11
1.5.1 IF Statements Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.5.2 Case Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.5.3 Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.6 Cursor Management in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.6.1 Implicit Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.6.2 Explicit Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
1.6.3 Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
1.7 Exception Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
1.7.1 Predefined exception . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
1.7.2 Raising Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
1.7.3 User-defined Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
1.8 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
1.8.1 Triggering a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
1.9 Procedures and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
1.9.1 Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
1.9.2 Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
1.10 PL/SQL Practice Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

List of Figures
1 Structure of PL/SQL Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2 PL/SQL Delimiters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3 Syntax of Exception handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
4 Example of Exception handling . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
5 Syntax of Raised Exception . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
6 Example of User-defined Exception . . . . . . . . . . . . . . . . . . . . . . . . . 20
7 Syntax for creating a triger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

2
1 Database Programming with PL/SQL
1.1 Fundamentals
1.1.1 Introduction to PL/SQL
Procedural Language extension to SQL:

• Allows basic program logic and control flow to be combined with SQL statements.

• Is an Oracle proprietary programming language. It can be used only with an Oracle

database or tool.

• Is a procedural language. It produces a result when a series of instructions are followed.

• Is a 3GL (third-generation programming language).

• It is a “high-level” programming language.

PL/SQL is used to write the procedural code, and embed SQL data-accessing statements within
the PL/SQL code. The PL/SQL code uses variables, cursors, and conditional logic.
Characteristics of PL/SQL are:

• Is a highly structured, readable, and accessible language.

• Is a standard and portable language for Oracle development.

• Is an embedded language and it works with SQL.

• Is a high-performance, highly integrated database language.

• Is based on the ADA language and has many similarities in syntax.

1.1.2 Benefits of PL/SQL

Benefits of PL/SQL:

Integration of Procedural Constructs With SQL SQL is a nonprocedural language. When

you issue a SQL command, your command tells the database server what to do. How-
ever, you cannot specify how to do it or how often to do it. PL/SQL integrates control
statements and conditional statements with SQL. This gives you better control of your
SQL statements and their execution.

Modularized Program Development The basic unit in a PL/SQL program is a block. All
PL/SQL programs consist of blocks. You can think of these blocks as modules and you
can “modularize” these blocks in a sequence, or nest them in other blocks. Modularized
program development has the following advantages:

1. You can group logically related statements within blocks.

2. You can nest blocks inside other blocks to build powerful programs.
3. You can share blocks with other programmers to speed up development time.
4. You can break your application into smaller modules. If you are designing a com-
plex application, PL/SQL allows you to break down the application into smaller,
manageable, and logically related modules.
5. You can easily read, maintain, and debug the programming statements.

3
Improved Performance PL/SQL allows you to logically combine multiple SQL statements
as one unit or block. The application can send the entire block to the database instead
of sending the SQL statements one at a time. This significantly reduces the number of
database calls (consider a database with several million records).

Integration With Oracle Tools PL/SQL is integrated in Oracle tools, such as Oracle Forms
Developer, Oracle Report Builder, and Application Express.

Portability PL/SQL is integrated in Oracle tools, such as Oracle PL/SQL programs can run
anywhere an Oracle server runs, regardless of the operating system and the platform.
PL/SQL programs do not need to be tailored for different operating systems and plat-
forms.
You can write portable program packages and create libraries that can be reused on
Oracle databases in different environments. You can even anticipate those differences
and establish instructions to run a specific way given a specific environment.

Exception Handling An exception is an error that occurs in the database or in a user’s

program during runtime. Examples of errors include: hardware or network failures, ap-
plication logic errors, data integrity errors, and so on. You can prepare for errors by
writing exception handling code. Exception handling code tells your program what to do
in the event of an exception.
PL/SQL allows you to handle database and program exceptions efficiently. You can define
separate blocks for dealing with exceptions.

1.1.3 Creating PL/SQL Blocks

PL/SQL is a block-structured language, meaning that PL/SQL programs are divided and
written in logical blocks of code. Each block consists of three sub-parts as shown in Figure 1.

Figure 1: Structure of PL/SQL Program

Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested within
other PL/SQL blocks using BEGIN and END. Here is the basic structure of a PL/SQL block:

[DECLARE]

4
–declarations
BEGIN
–statements
[EXCEPTION]
–exceptions
END;

Examples:
No declaration or exception sections, execution only
BEGIN
DBMS_OUTPUT.PUT_LINE(’PL/SQL is easy!’);
END;
/

Declaration and execution sections, but no exception section

DECLARE
v_date DATE := SYSDATE;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_date);
END;

The end; line signals the end of the PL/SQL block. To run the code from SQL command
line, you may need to type / at the beginning of the first blank line after the last line of the
code.

1.2 Variables in PL/SQL

You use variables to store and manipulate data. In this lesson, you learn how to declare and
initialize variables in the declarative section of a PL/SQL block. With PL/SQL, you can declare
variables and then use them in SQL and procedural statements. Variables can be thought of
as storage containers that hold something until it is needed.
Use variables for:

• Temporary storage of data

• Manipulation of stored values

• Reusability

1.2.1 Handling Variables in PL/SQL

Variables are:

• Declared and initialized in the declarative section

• Used and assigned new values in the executable section

Variables can be:

• Passed as parameters to PL/SQL subprograms

• Assigned to hold the output of a PL/SQL subprogram

5
 All PL/SQL variables must be declared in the declaration section before referencing them
in the PL/SQL block. The purpose of a declaration is to allocate storage space for a value,
specify its data type, and name the storage location so that you can reference it. You can
declare variables in the declarative part of any PL/SQL block, subprogram, or package.
Syntax for declaring variables:

identifier [CONSTANT] datatype [NOT NULL]

[:= expr | DEFAULT expr];

Variables are assigned a memory location inside the DECLARE section. Variables can be
assigned a value at the same time. This process is called initializing.
DECLARE
v_counter INTEGER := 0;
BEGIN
v_counter := v_counter + 1;
DBMS_OUTPUT.PUT_LINE(v_counter);
END;

Declaring and Initializing Variables Example

DECLARE
fam_birthdate DATE;
fam_size NUMBER(2) NOT NULL := 10;
fam_location VARCHAR2(13) := ’Florida’;
fam_bank CONSTANT NUMBER := 50000;
fam_population INTEGER;
fam_name VARCHAR2(20) DEFAULT ’Roberts’;

After a variable is declared, you can use it in the executable section of a PL/SQL block. For
example, in the following block, the variable v myname is declared in the declarative section of
the block. You can access this variable in the executable section of the same block.

DECLARE
v_myname VARCHAR2(20);
BEGIN
v_myname := ’John’;
DBMS_OUTPUT.PUT_LINE(’My name is: ’|| v_myname);
END;

1.3 Identifiers and Delimiters

1.3.1 PL/SQL Identifiers
PL/SQL identifiers are constants, variables, exceptions, procedures, cursors, and reserved
words. The identifiers consist of a letter optionally followed by more letters, numerals, dol-
lar signs, underscores, and number signs and should not exceed 30 characters. By default,
identifiers are not case-sensitive. So you can use integer or INTEGER to represent a numeric
value. You cannot use a reserved keyword as an identifier.

6
1.3.2 PL/SQL Delimiters
A delimiter is a symbol with a special meaning. The table in Figure 2, shows a list of delimiters
in PL/SQL:

Figure 2: PL/SQL Delimiters

1.3.3 PL/SQL Comments

Program comments are explanatory statements that you can include in the PL/SQL code that
you write and helps anyone reading its source code. All programming languages allow for some
form of comments.
The PL/SQL supports single-line and multi-line comments. All characters available inside
any comment are ignored by PL/SQL compiler. The PL/SQL single-line comments start with
the delimiter –(double hyphen) and multi-line comments are enclosed by /* and */.

7
DECLARE
...
v_annual_sal NUMBER (9,2);
BEGIN -- Begin the executable section
/* Compute the annual salary based on
the monthly salary input from the user */
v_annual_sal := v_monthly_sal * 12;
END; -- This is the end of the block

1.3.4 PL/SQL Data Types

The default data types that we can declare in PL/SQL are number (for storing numeric data),
char (for storing character data), date (for storing date and time data) boolean (for storing
TRUE, FALSE or NULL). number, char and date data types can have NULL values.
The SELECT statement has a special form in PL/SQL in which a single tuple is placed
in variables. The information from the database is transferred into variables which is used in
PL/SQL programs. Every variable has a specific type associated with it. That type can be :
• A generic type used in PL/SQL
• A type same as used by SQL for database columns.
The most commonly used generic type is NUMBER. Variables of type NUMBER can hold either
an integer or a real number. The most commonly used character string type is VARCHAR2(n),
where n is the maximum length of the string in bytes.
The variable can contain any data type that is valid for SQL and Oracle (such as char,
number, long, varchar2, & date) in addition to these types PL/SQL allows following types :
• Binary integer : Range is -2,147,483,647 to 2,147,483,647
• Positive : Range is 1 to 2,147,483,647.
• Natural : Range is 0 to 2,147,483,647.
• Boolean : Assigned values either True, False or NULL.
• %type : Assign the same type to variable as that of the relation column declared in
database. If there is any type mismatch, variable assignments and comparisons may not
work the way you expect, so instead of hard coding the type of a variable, you should use
the %TYPE operator.
For example :
DECLARE
My_name [Link]%TYPE;

gives PL/SQL variable my name whatever type was declared for the ename column in
emp table.
• %rowtype - A variable can be declared with %rowtype that is equivalent to a row of a
table i.e. record with several fields. The result is a record type in which the fields have
the same names and types as the attributes of the relation.
For example :
DECLARE
Emp_rec emp1%ROWTYPE;

8
 This makes variable emp rec be a record with fields name and salary, assuming that the
relation has the schema emp1(name, salary). The initial value of any variable, regardless
of its type, is NULL.

Declaration of a constant is similar to declaring a variable except that the keyword constant
must be added to the variable name and a value assigned immediately. Thereafter, no further
assignments to the constant are possible, while the constant is within the scope of the PL/SQL
block.

1.3.5 PL/SQL Expressions

Expressions are a composite of operators and operands . In the case of a mathematical ex-
pression ,the operand is the number and operator is the symbol such as + or - that acts on
the operand. The expression value is the evaluated total of the operands using the operators.
Operators are divided into categories that describe the way that act upon operands.

• Comparison operators are binary, meaning they work with two operands. Examples of
comparison operators are the greater than (>) ,less than(<) and equal(=) signs ,among
others.

• Logical operators include AND,OR and NOT

• Arithmetic operators include addition/positive(+),subtraction/negative( ), multiplica-

tion(*),and division(/).

• The assignment operator is specific to PL/SQL and is written as colon-equal (:=)

• The lone character operator is a double pipe(||) that joins two strings together, concate-
nating the operands.

• Other basic SQL operators include IS NULL, IN and BETWEEN.

1.4 Using SQL in PL/SQL

1.4.1 Manipulating Data in PL/SQL
Retrieve data from the database with a SELECT statement.

SELECT select list

INTO {variable name[, variable name]...
| record name}
FROM table
[WHERE condition];

The INTO clause is mandatory and occurs between the SELECT and FROM clauses. It is
used to specify the names of PL/SQL variables that hold the values that SQL returns from the
SELECT clause. You must specify one variable for each item selected, and the order of the
variables must correspond with the items selected.
DECLARE
v_country_name wf_countries.country_name%TYPE;
BEGIN
SELECT country_name INTO v_country_name
FROM wf_countries WHERE country_id= 359;
DBMS_OUTPUT.PUT_LINE(’ The country name is :

9
 ’||v_country_name);
END;

Retrieve hire date and salary for the specified employee.

DECLARE
v_emp_hiredate employees.hire_date%TYPE;
v_emp_salary [Link]%TYPE;
BEGIN
SELECT hire_date, salary
INTO v_emp_hiredate, v_emp_salary
FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE(’Hiredate is: ’ || v_emp_hiredate
|| ’ and Salary is: ’ || v_emp_salary);
END;

Return the sum of the salaries for all the employees in the specified department.
DECLARE
v_sum_sal NUMBER(10,2);
v_deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary) -- group function
INTO v_sum_sal FROM employees
WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE (’The sum of salary is ’
|| v_sum_sal);
END;

1.4.2 Manipulating Data in PL/SQL

You manipulate data in the database by using the DML commands. You can issue the DML
commands: INSERT, UPDATE, DELETE, and MERGE, without restriction in PL/SQL.
Examples:
Increase the salary of all employees who are stock clerks.
DECLARE
v_sal_increase [Link]%TYPE := 800;
BEGIN
UPDATE copy_emp SET salary = salary + v_sal_increase WHERE job_id = ’ST_CLERK’;
END;

Delete rows that belong to department 10 from the COPY EMP table.
DECLARE
v_deptno employees.department_id%TYPE := 10;
BEGIN
DELETE FROM copy_emp WHERE department_id = v_deptno;
END;

Consider the following two tables:

employee(employee id, first name, last name, email)
copy emp(employee id, first name, last name, email)

Insert or update rows in the copy emp table to match the employees table.

10
BEGIN
MERGE INTO copy_emp c
USING employees e
ON (e.employee_id = c.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
[Link] = [Link],
. . .
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name, [Link]);
END;

1.5 Program Structures to Control Execution Flow

1.5.1 IF Statements Structure
The structure of the PL/SQL IF statement is similar to the structure of IF statements in other
procedural languages. It enables PL/SQL to perform actions selectively based on conditions.
IF condition THEN
statements;
ELSIF condition THEN
statements;
ELSE
statements;
END IF;

Example 1
DECLARE
v_myage NUMBER:=31;
BEGIN
IF v_myage < 11 THEN
DBMS_OUTPUT.PUT_LINE(’ I am a child ’);
ELSE
DBMS_OUTPUT.PUT_LINE(’ I am not a child ’);
END IF;
END;

Example 2 : Consider a table employee(Employee id, Employee name, sales, salary)

Determine the bonus that needs to be added to the salary of a given employee.
DECLARE
v_sales [Link]%TYPE;
v_comission NUMBER(2) := 20;
v_bonus NUMBER(6,2);
v_emp_id NUMBER(6) := 250;
BEGIN
SELECT sales from employee into v_sales where Employee_id=v_emp_id;
IF v_sales > 20000 THEN
v_bonus := v_sales * 2.0;
ELSE

11
 v_bonus := 100;
END IF;
UPDATE employee SET salary = salary + v_bonus WHERE employee_id = v_emp_id;
END;
/

1.5.2 Case Statements

CASE statements are similar to IF statements, but are often easier to write and easier to read.
CASE expressions are functions that return one of a number of values into a variable.
Syntax:
variable_name :=
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE resultN+1
END;

Example
DECLARE
v_grade CHAR(1) := ’A’;
v_appraisal VARCHAR2(20);
BEGIN
v_appraisal :=
CASE v_grade
WHEN ’A’ THEN ’Excellent’
WHEN ’B’ THEN ’Very Good’
WHEN ’C’ THEN ’Good’
ELSE ’No such grade’
END;
DBMS_OUTPUT.PUT_LINE (’Grade: ’|| v_grade ||
’ Appraisal ’ || v_appraisal);
END;

1.5.3 Loops
Loops are mainly used to execute statements repeatedly until an EXIT condition is reached.
PL/SQL provides three ways to structure loops to repeat a statement or a sequence of state-
ments multiple times. These are: basic loops, FOR loops, and WHILE loops.

Basic Loop:
A basic loop allows the execution of its statements at least once, even if the EXIT condition is
already met upon entering the loop. Without the EXIT statement, the loop would be infinite.
Syntax:
LOOP
statement1;
. . .
EXIT WHEN condition;

12
END LOOP;

Example
DECLARE
sum INTEGER := 0;
BEGIN
LOOP
sum := sum + 1;
IF sum > 10 THEN
EXIT;
END IF;
END LOOP;
END;

You can also use EXIT WHEN condition as shown below:

DECLARE
sum INTEGER := 0;
BEGIN
LOOP
sum := sum + 1;
EXIT WHEN sum > 10;
END LOOP;
END;

WHILE Loops:
You can use the WHILE loop to repeat a sequence of statements until the controlling condition is
no longer TRUE. The condition is evaluated at the start of each iteration. The loop terminates
when the condition is FALSE or NULL. If the condition is FALSE or NULL at the start of the
loop, then no further iterations are performed.
WHILE condition
LOOP
statement1;
statement2;
. . .
END LOOP;

Example
DECLARE
n_counter NUMBER :=0;
BEGIN
WHILE n_counter < 10
LOOP
DBMS_OUTPUT.PUT_LINE( ’Counter : ’ || n_counter );
n_counter := n_counter + 1;
END LOOP;
END;

FOR Loops FOR loops have the same general structure as the basic loop. In addition,
they have a control statement before the LOOP keyword to set the number of iterations that
PL/SQL performs.
FOR counter IN [REVERSE]

13
 lower_bound..upper_bound
LOOP
statement1;
statement2;
. . .
END LOOP;

Examples
DECLARE
Lcntr NUMBER(2);
LCalc NUMBER;
BEGIN
FOR Lcntr IN 1..20
LOOP
LCalc := Lcntr * 31;
END LOOP;
END;

DECLARE
Lcntr NUMBER(2);
LCalc NUMBER;
BEGIN
FOR Lcntr IN REVERSE 1..15
LOOP
LCalc := Lcntr * 31;
END LOOP;
END;

1.6 Cursor Management in PL/SQL

Every time an SQL statement is about to be executed, the Oracle server allocates a private
memory area to store the SQL statement and the data that it uses. This memory area is called
a cursor. There are two types of cursors:

• Implicit cursors: Defined automatically by Oracle for all SQL data manipulation state-
ments, and for queries that return only one row. An implicit cursor is always automatically
named “SQL.”

• Explicit cursors: Defined by the PL/SQL programmer for queries that return more than
one row.

1.6.1 Implicit Cursors

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed,
when there is no explicit cursor for the statement. Programmers cannot control the implicit cur-
sors and the information in it. Whenever a DML statement (INSERT, UPDATE and DELETE)
is issued, an implicit cursor is associated with this statement. For INSERT operations, the cur-
sor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor
identifies the rows that would be affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which
always has some attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.
The following is the description of these most used attributes:

14
 • %FOUND - Returns TRUE if an INSERT, UPDATE, or DELETE statement affected
one or more rows or a SELECT INTO statement returned one or more rows. Otherwise,
it returns FALSE.

• %NOTFOUND - The logical opposite of %FOUND. It returns TRUE if an INSERT, UP-

DATE, or DELETE statement affected no rows, or a SELECT INTO statement returned
no rows. Otherwise, it returns FALSE.

• %ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL
cursor automatically after executing its associated SQL statement.

• %ROWCOUNT Returns the number of rows affected by an INSERT, UPDATE, or

DELETE statement, or returned by a SELECT INTO statement.

Any SQL cursor attribute will be accessed as sql%attribute name as shown below in the exam-
ple.
Delete rows that have the specified department id from the copy emp table. Print the number
of rows deleted.
DECLARE
v_deptno copy_emp.department_id%TYPE := 50;
BEGIN
DELETE FROM copy_emp WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ’ rows deleted.’);
END;

Update several rows in the COPY EMP table. Print the number of rows updated.
DECLARE
v_sal_increase [Link]%TYPE := 800;
BEGIN
UPDATE copy_emp SET salary = salary + v_sal_increase
WHERE job_id = ’ST_CLERK’;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ||’ rows updated.’);
END;

1.6.2 Explicit Cursors

With an explicit cursor, you can retrieve multiple rows from a database table, have a pointer to
each row that is retrieved, and work on the rows one at a time. The following are some reasons
to use an explicit cursor:

• It is the only way in PL/SQL to retrieve more than one row from a table.

• Each row is fetched by a separate program statement, giving the programmer more control
over the processing of the rows.

Oracle uses four commands to handle Cursors. They are :

1. DECLARE : Defines the name and structure of the cursor together with the SELECT
statement.

2. OPEN : Executes the query and the number of rows to be returned is determined.

3. FETCH : Loads the row addressed by the cursor pointer into variables and moves the
cursor pointer on to the next row ready for the next fetch.

15
 4. CLOSE : Releases the data within the cursor and closes it.
The following example uses an explicit cursor to obtain the country name and national
holiday for countries in East Africa.
DECLARE
CURSOR wf_holiday_cursor IS
SELECT country_name, national_holiday_date
FROM wf_countries where region_id IN(30,34,35,36,37);
v_country_name wf_countries.country_name%TYPE;
v_holiday wf_countries.national_holiday_date%TYPE;
BEGIN
OPEN wf_holiday_cursor;
LOOP
FETCH wf_holiday_cursor INTO v_country_name, v_holiday;
EXIT WHEN wf_holiday_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_country_name||’ ’||v_holiday);
END LOOP;
CLOSE wf_holiday_cursor;
END;

Retrieve the employee id and last name columns of the employees working in the department
with a department id of 50.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id =50;
v_empno employees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_empno, v_lname;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( v_empno ||’ ’||v_lname);
END LOOP;
END;

%ROWTYPE is convenient for processing the rows of the active set because you can simply
fetch into the record.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees WHERE department_id = 30;
v_emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id|| ’ - ’
||v_emp_record.last_name);
END LOOP;
CLOSE emp_cursor;
END;

16
Cursors and %ROWTYPE: Another Example
DECLARE
CURSOR emp_dept_cursor IS
SELECT first_name, last_name, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
v_emp_dept_record emp_dept_cursor%ROWTYPE;
BEGIN
OPEN emp_dept_cursor;
LOOP
FETCH emp_dept_cursor INTO v_emp_dept_record;
EXIT WHEN emp_dept_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_dept_record.first_name ||’ -
’||v_emp_dept_record.last_name
||’ - ’||v_emp_dept_record.department_name);
END LOOP;
CLOSE emp_dept_cursor;
END;

DECLARE
CURSOR empcur IS
SELECT employee_id, salary
FROM my_employees
WHERE salary <= 20000 FOR UPDATE NOWAIT;
v_emp_rec empcur%ROWTYPE;
BEGIN
OPEN empcur;
LOOP
FETCH empcur INTO v_emp_rec;
EXIT WHEN empcur%NOTFOUND;
UPDATE my_employees SET salary = v_emp_rec.salary*1.1
WHERE CURRENT OF empcur;
END LOOP;
CLOSE empcur;
COMMIT;
END;

The optional NOWAIT keyword tells the Oracle server not to wait if any of the requested rows
have already been locked by another user. Control is immediately returned to your program
so that it can do other work before trying again to acquire the lock. If you omit the NOWAIT
keyword, then the Oracle server waits indefinitely until the rows are available.
The WHERE CURRENT OF clause is used in conjunction with the FOR UPDATE clause
to refer to the current row (the most recently FETCHed row) in an explicit cursor. The
WHERE CURRENT OF clause is used in the UPDATE or DELETE statement, whereas the
FOR UPDATE clause is specified in the cursor declaration.

1.6.3 Question
Consider the following table:
Customer(CustId, CustName, TotalPurchases, Discount)
Create a cursor as follows:

• The cursor fetches customers from the table, calculates customers discount according to

17
 the following formula:
if total purchases are equal or above 100,000 then discount is 40%.
if below 100,000 then discount is 10%

• Update the discount of each customer

• Display the custid, custname, total purchases and discount of each customer.

1.7 Exception Handling

The Exception section in PL/SQL block is used to handle an error that occurs during the
execution of PL/SQL program. If an error occurs within a block PL/SQL passes control to the
EXCEPTION section of the block. If no EXCEPTION section exists within the block or the
EXCEPTION section does not handle the error that’s occurred then the error is passed out to
the host environment. There are two classes of exceptions, these are :

1. Predefined exception : Oracle predefined errors which are associated with specific error
codes.

2. User-defined exception : Declared by the user and raised when specifically requested
within a block. You can associate a user-defined exception with an error code if you wish.

Syntax for Exception Handling The General Syntax for exception handling is as shown in
Figure 3. Here, you can list down as many as exceptions you want to handle. The default
exception will be handled using WHEN others THEN:

Figure 3: Syntax of Exception handling

1.7.1 Predefined exception

The two most common errors originating from a SELECT statement occur when it returns no
rows (WHEN NO DATA FOUND) or more than one row (remember that this is not allowed

18
in PL/SQL select command). If no rows are selected from SELECT statement then WHEN
NO DATA FOUND exception is used and for more than one row WHEN TOO MANY ROWS
exception is used.
Example Let us write some simple code (see Figure 4) to illustrate the concept. The program
displays the name and address of a customer whose ID is given. If there is no customer with ID
value 8 in the database, the program raises the run-time exception NO DATA FOUND, which
is captured in EXCEPTION block.

Figure 4: Example of Exception handling

1.7.2 Raising Exceptions

Exceptions are raised by the database server automatically whenever there is any internal
database error, but exceptions can be raised explicitly by the programmer by using the com-
mand RAISE. In Figure 5 is the simple syntax of raising an exception. You can use above
syntax in raising Oracle standard exception or any user-defined exception. Next section will
give you an example on raising user-defined exception, similar way you can raise Oracle standard
exceptions as well.

1.7.3 User-defined Exceptions

PL/SQL allows you to define your own exceptions according to the need of your program. A
user-defined exception must be declared and then raised explicitly, using either a RAISE state-
ment or the procedure DBMS [Link] APPLICATION ERROR. The example in
Figure 6 illustrates the concept. This program asks for a customer ID, when the user enters an
invalid ID, the exception invalid id is raised.

1.8 Triggers
Triggers are stored programs, which are automatically executed or fired when some events
occur. Triggers are, in fact, written to be executed in response to any of the following events:

19
 Figure 5: Syntax of Raised Exception

Figure 6: Example of User-defined Exception

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

• A database definition (DDL) statement (CREATE, ALTER, or DROP).

• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUT-

20
 DOWN).

Triggers could be defined on the table, view, schema, or database with which the event is
associated. You can use triggers to:

• Create consistency and access restrictions to the database.

• Create logging records automatically

• Prevent tables from being accidentally dropped

• Prevent invalid DML transactions from occurring

• Generate derived column values automatically

• Gather statistics on table access

• Modify table data when DML statements are issued against views

The syntax for creating a trigger is as shown in Figure 7.

Where:

• CREATE [OR REPLACE] TRIGGER trigger name: Creates or replaces an existing trig-
ger with the trigger name.

• {BEFORE | AFTER | INSTEAD OF}: This specifies when the trigger would be executed.
The INSTEAD OF clause is used for creating trigger on a view.

• {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.

• [OF col name]: This specifies the column name that would be updated.

• [ON table name]: This specifies the name of the table associated with the trigger.

• [REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values
for various DML statements, like INSERT, UPDATE, and DELETE.

• [FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would be executed
for each row being affected. Otherwise the trigger will execute just once when the SQL
statement is executed, which is called a table level trigger.

• WHEN (condition): This provides a condition for rows for which the trigger would fire.
This clause is valid only for row level triggers.

Examples of triggers:
Trigger inserts a row of user details into the logging table whenever an SQL statement updates
a salary of an employee
employees(eno, ename, salary)
log table(user id, logon date)
CREATE OR REPLACE TRIGGER log_sal_change_trigg
AFTER UPDATE OF salary ON employees
BEGIN
INSERT INTO log_table (user_id, logon_date)
VALUES (USER, SYSDATE);
END;

21
 Figure 7: Syntax for creating a triger

This statement trigger automatically inserts a row into a logging table every time a DML
operation is successfully executed on the DEPARTMENTS table.
log dept table (which user, when done)
CREATE OR REPLACE TRIGGER log_dept_changes
AFTER INSERT OR UPDATE OR DELETE ON DEPARTMENTS
BEGIN
INSERT INTO log_dept_table (which_user, when_done) VALUES (USER, SYSDATE);
END;

This trigger allows INSERTs into the EMPLOYEES table during normal working days (Monday
through Friday), but prevent INSERTs on the weekend (Saturday and Sunday).
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
IF TO_CHAR(SYSDATE,’DY’) IN (’SAT’,’SUN’) THEN RAISE_APPLICATION_ERROR(-20500,
’You may insert into EMPLOYEES’ ||’ table only during business hours’);
END IF;
END;

Using conditions
CREATE OR REPLACE TRIGGER secure\_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF TO_CHAR(SYSDATE,’DY’) IN (’SAT’,’SUN’)
THEN IF DELETING THEN RAISE_APPLICATION_ERROR (-2050
1,’You may delete from EMPLOYEES’ ||’ table only during business hours’);
ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (-20502,’You may insert into
EMPLOYEES
’ ||’ table only during business hours’);
ELSIF UPDATING THEN RAISE_APPLICATION_ERROR (-20503,’You may update EMPLOYEES
’ ||’ table only during business hours’);
END IF;
END IF;
END;

Trigger for each row

22
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_emp(user_name, time_stamp, id,
old_last_name, new_last_name, old_title,
new_title, old_salary, new_salary)
VALUES (USER, SYSDATE, :OLD.employee_id,
:OLD.last_name, :NEW.last_name, :OLD.job_id,
:NEW.job_id, :[Link], :[Link]);
END;

Trigger prevents employees who are not a President or Vice-President from having a salary of
more than $15000.
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF NOT (:NEW.job_id IN (’AD_PRES’, ’AD_VP’))
AND :[Link] > 15000 THEN
RAISE_APPLICATION_ERROR (-20202,
’Employee cannot earn more than $15,000.’);
END IF;
END;

1.8.1 Triggering a Trigger

Testing SECURE EMP in example (3).

A user tries to INSERT a row on the weekend:

INSERT INTO employees (employee id, last name, first name, email, hire date, job id, salary,
department id)
VALUES (300, ’Smith’, ’Rob’, ’RSMITH’, SYSDATE,’IT PROG’, 4500, 60);

Output: ORA-20500: You may insert into EMPLOYEES table only during busi-
ness hours.
ORA-06512: at “USVA TEST SQL01 [Link] EMP”, line 4
ORA 04088: error during execution of trigger ‘USVA TEST SQL01 [Link] EMP’
2. VALUES (300, ’Smith’, ’Rob’, ’RSMITH’, SYSDATE, ’IT PROG’, 4500, 60);

1.9 Procedures and Functions

Procedures are simply a named PL/SQL block, that executes certain task. A procedure is
completely portable among platforms in which Oracle is executed. A function is similar to a
procedure. The main difference between the function and procedure is that a function returns
a value where procedure does not. Procedures and functions have many benefits due to the
modularizing of the code:

• Easy maintenance: Modifications need only be done once to improve multiple applications
and minimize testing.

23
 • Code reuse: Subprograms are located in one place. When compiled and validated, they
can be used and reused in any number of applications.

• Improved data security: Indirect access to database objects is permitted by the granting
of security privileges on the subprograms. By default, subprograms run with the privileges
of the subprogram owner, not the privileges of the user.

• Data integrity: Related actions can be grouped into a block and are performed together
(“Statement Processed”) or not at all.

• Improved performance: You can reuse compiled PL/SQL code that is stored in the shared
SQL area cache of the server. Subsequent calls to the subprogram avoid compiling the
code again. Also, many users can share a single copy of the subprogram code in memory.

• Improved code clarity: By using appropriate names and conventions to describe the action
of the routines, you can reduce the need for comments, and enhance the clarity of the
code.

1.9.1 Procedures
Syntax for creating a procedure:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [mode] datatype1,
parameter2 [mode] datatype2, ...)]
IS|AS
[local_variable_declarations; ]
BEGIN -- actions;
END [procedure_name];

In the following example, the add dept procedure inserts a new department with the de-
partment id 280 and department name ST-Curriculum. The procedure declares two variables,
v dept id and v dept name, in the declarative section.
CREATE OR REPLACE PROCEDURE add_dept IS
v_dept_id dept.department_id%TYPE;
v_dept_name dept.department_name%TYPE;
BEGIN
v_dept_id :=280;
v_dept_name :=’ST-Curriculum’;
INSERT INTO dept(department_id,department_name)
VALUES(v_dept_id,v_dept_name);
DBMS_OUTPUT.PUT_LINE(’Inserted ’||SQL%ROWCOUNT ||’row’);
END;

A procedure can be called in two ways:

• Another procedure

• Anonymous block

• A calling application

The example shows a procedure with two parameters. Running this first statement creates the
raise salary procedure in the database. The second example executes the procedure, passing
the arguments 176 and 10 to the two parameters.

24
CREATE OR REPLACE PROCEDURE raise_salary (
emp_id NUMBER, -- formal parameter
amount NUMBER -- formal parameter
) IS
BEGIN
UPDATE employees SET salary = salary + amount
WHERE employee_id = emp_id;
END raise_salary;

Calling a procedure from Anonymous block

BEGIN
raise_salary(176,10);
END;

To invoke a procedure from another procedure

CREATE OR REPLACE PROCEDURE process_employees
IS
CURSOR emp_cursor IS
SELECT employee_id FROM my_employees;
v_emp_rec emp_cursor%ROWTYPE;
BEGIN
FOR v_emp_rec IN emp_cursor
LOOP
raise_salary(v_emp_rec.employee_id, 10);
END LOOP;
COMMIT;
END process_employees;

Parameter-passing modes:define the action of formal parameters. The three parameter

modes are IN (the default), OUT, and IN OUT.

• An IN parameter (the default) provides values for a subprogram to process.

• An OUT parameter returns a value to the caller.

• An IN OUT parameter supplies an input value, which can be returned (output) as a

modified value.

CREATE OR REPLACE PROCEDURE query_emp

(p_id IN employees.employee_id%TYPE,
p_name OUT employees.last_name%TYPE,
p_salary OUT [Link]%TYPE) IS
BEGIN
SELECT last_name, salary INTO p_name, p_salary
FROM employees
WHERE employee_id = p_id;
END query_emp;

Viewing OUT Parameters

a_emp_name employees.last_name%TYPE;
a_emp_sal [Link]%TYPE;
BEGIN

25
 query_emp(178, a_emp_name, a_emp_sal);
DBMS_OUTPUT.PUT_LINE(’Name: ’ || a_emp_name);
DBMS_OUTPUT.PUT_LINE(’Salary: ’ || a_emp_sal);
END;

Create a procedure with OUT parameters to retrieve information about an employee. The
procedure accepts the value 178 for employee ID and retrieves the name and salary of the
employee with ID 178 into the two OUT parameters. The query emp procedure has three formal
parameters. Two of them are OUT parameters that return values to the calling environment.
The procedure accepts an employee ID value through the pid parameter. The a emp name
and a emp sal variables are populated with the information retrieved from the query into their
two corresponding OUT parameters. Make sure that the data type for the actual parameter
variables used to retrieve values from OUT parameters has a size large enough to hold the data
values being returned.

1.9.2 Functions
A function is a named PL/SQL block (a subprogram) that can accept optional IN parameters
and must return a single output value. Functions are stored in the database as schema objects
for repeated execution. Syntax:
CREATE [OR REPLACE] FUNCTION function_name
[(parameter1 [mode1] datatype1, ...)]
RETURN datatype IS|AS
[local_variable_declarations;]
BEGIN
-- actions;
RETURN expression;
END [function_name];

Example:
CREATE OR REPLACE FUNCTION get_sal
(p_id employees.employee_id%TYPE)
RETURN NUMBER IS
v_sal [Link]%TYPE := 0;
BEGIN
SELECT salary
INTO v_sal
FROM employees
WHERE employee_id = p_id;
RETURN v_sal;
END get_sal

Invoke the function as an expression or as a parameter value:

DECLARE
v_sal [Link]%type;
BEGIN
v_sal := get_sal(100);
...
END;

26
1.10 PL/SQL Practice Questions
1. Write a PL/SQL blocks for the following:

(a) Using case statement, the block accepts a number 1 to 4 and outputs it in words
(i.e 1 - one), else it should output invalid number.
(b) Display the first 5 even numbers making use of basic loop, for loop and while
loop.
(c) Creates a table temp(number, Description). The program inserts the numbers 1
to 10, along with a description of whether the number is even or odd. Check if the
table exists with its data.
NB: The program should check whether a number is even or odd.

2. Consider a table student (Regno, Name, Department, Address, fees)

Create the following explicit cursors:

(a) Retrieve Regno, Name of students belonging to Computer Science department and
display the list on screen.
(b) Retrieve Regno, name, fees of students belonging to Physics department. Thereafter
update the fees by 10% of the current fees.

3. Consider a table student (Regno, Name, Department, Address, fees)

Create the following triggers:

(a) create a trigger which ensures that before a row is inserted into the student table,
the fees amount must be above 200,000. Otherwise displays an error message.
(b) Create a trigger which ensures that when an update or delete is done on the student
table, it inserts a row in a student log table, the user name of the person who has
updated/deleted and date of update/delete.

4. consider the following table: student(regno, name, totalfees, feespaid)

(a) Create a parameterized procedure which takes regno as input, computes pending fees
for the student. Call the procedure in an anonymous block and display the student
regno, name and pending fees.
(b) Create a parameterized function which takes regno as input, computes and returns
pending fees for the student. Call the function in an anonymous block and display
the student regno and pending fees.

5. Using the insurance database, which you already created, answer the questions which fol-
low. Driver(Driver id:String, Name:String, City:String, Phone no:String, Country:String,
Gender:String)
Car(Regno:String, Make:String, Year:String)
Accident(Report no:int, Accident date:Date, Location:String)
Owns(Driver id:String, Regno:String)
Participated(Driver id:String, Regno:String, Report no:int, Damage amount:int)

(a) Write a PL/SQL block to display the name and city of a driver whose id is ’D101’.
(b) Write a PL/SQL block to check how many accidents have occurred in the location
of “Jinja”. If 5 or more accidents have occurred then display a message ”there is
need for more awareness on road safety”. Otherwise, ”Drivers are conscious of road
safety”.

27
(c) Write a cursor which will display accident details and damage amounts for [Link].
Assume [Link] has been involved in more than one accident.
(d) Write a procedure to increase the damage amount of a given driven driver’s id. Test
the procedure by using another PL/SQL block.
(e) Write a function to return the total number of drivers who got accidents in a given
location. Test the function by using another PL/SQL block.
(f) Define a trigger before update of damage amount for each row of Participated table,
to insert a record of the row in Participated table, the new value of damage amount
and date of update into a table “audit Participated”. Test the trigger. Hint: Re-
member to create the table “audit Participated” with the required columns.

28