Advanced Database PL SQL

College of Computing and Informatics
Department of Software Engineering
Advanced Database
Lab Session
Compiled by Miliyon A
Compiled by Miliyon A 1
Basics of SQL *Plus
Basics of SQL * Plus
 The SQL database language allows you to
store and retrieve data in Oracle.
 PL/SQL allows you to link several SQL
commands through procedural logic.
 SQL*Plus enables you to execute SQL
commands and PL/SQL blocks, and to
perform many additional tasks as well.
Basics of SQL * Plus
Through SQL*Plus, you can
® enter, edit, store, retrieve, and run SQL commands and
PL/SQL blocks format,
® perform calculations on, store, print and create web
output of query results
® list column definitions for any table access and
® copy data between SQL databases
® send messages to and accept responses from an end user
® perform database administration
PL/SQL
PL/SQL is a procedural language designed
specifically to embrace SQL statements within its
syntax.
® PL/SQL program units are compiled by the
Oracle Database server and stored in the
database.
® SQL is used to write queries DDL and DMLs.
® SQL/PL: is used to write program blocks,
functions procedures triggers and packages.
SQL, PL/SQL and SQL Plus
As described earlier
® SQL is used to write queries DDL and DMLs.
® SQL/PL: is used to write program blocks,
functions procedures triggers and packages.
Whereas SQP *Plus is
An Oracle product is used to run SQL and
PL/SQL statements.
Basic Concepts
The following definitions explain concepts central to SQL*Plus:
®Command: An instruction you give SQL*Plus or Oracle.
®Block: A group of SQL and PL/SQL commands related
to one another through procedural logic.
®table : The basic unit of storage in Oracle.
®query : A SQL command (specifically, a SQL SELECT
command) that retrieves information from one or more
tables.
®query results: The data retrieved by a query.
Who Can Use SQL*Plus
® The SQL*Plus, SQL, and PL/SQL command languages are powerful
enough to serve the needs of users with some database experience,
yet straightforward enough for new users who are just learning to
work with Oracle.
® The design of the SQL*Plus command language makes it easy to
use. For example, to give a column labeled ENAME in the database
the clearer heading "Employee", you might enter the following
command:
COLUMN ENAME HEADING EMPLOYEE
Similarly, to list the column definitions for a table called EMP, you
might enter this command:
DESCRIBE EMP
Conventions for Command Syntax
What You Need to Run SQL*Plus
 To run SQL*Plus, you need hardware, software, operating

system specific information, a username and password, and
access to one or more tables.
 Before you can begin using SQL*Plus, both Oracle and

SQL*Plus must be installed on your computer.
 You also need authorized access to the database (username

and password required)
What is SQL * Plus?
 SQL * Plus is an interactive and batch query tool that is

installed with every oracle installation.
 you can get it in StartProgramsOracle-
HomeApplication DevelopmentSQL Plus
 Or alternatively, You can also download it from Oracle
Source like OTN (Oracle Technology Network) and install
it
 It has a command line user interface, Windows GUI, and
web-based user interface.
Connecting to database
 To connect to an already existing Oracle Database using

SQL * Plus
A. You need to have Oracle installed on your Machine
B. Have authorized username and Password
C. Knowledge of the database sever host machine
Connecting to database
 An alternative way of connecting to database can be done using
commandline
 Open CMD as Administrator
 Type SQLplus
 On the prompt type the user name and password press enter
 after successful login you will seethe screen
Writing Sample program using SQL * plus
With PL/SQL, you can use SQL statements to

manipulate ORACLE data and flow-of-control
statements to process the data.
Moreover, you can declare constants and variables,
define subprograms (procedures and functions),
and trap runtime errors.
Thus, PL/SQL combines the data manipulating
power of SQL with the data processing power of
procedural languages.
PL SQL Overview
At this point it is important to introduce PL SQL.

 The PL/SQL programming language was developed by Oracle Corporation
in the late 1980s as procedural extension language for SQL and the Oracle
relational database.
Some of its facts are:
 PL/SQL is a completely portable, high-performance transaction-processing
language.
 PL/SQL provides a built-in, interpreted and OS independent programming
 environment.
 PL/SQL can also directly be called from the command-line SQL*Plus
interface.
 Direct call can also be made from external programming language calls to
database.
 PL/SQL's general syntax is based on that of ADA and Pascal programming
language.
Features of PL SQL
The following are features of PL SQL

 PL/SQL is tightly integrated with SQL.
 It offers extensive error checking.
 It offers numerous data types.
 It offers a variety of programming structures.
 It supports structured programming through
functions and procedures.
 It supports object-oriented programming.
 It supports the development of web applications and
server pages.
Features of PL SQL
 PL SQL is not a stand-alone programming

language like C++ is.
 it is a tool within the Oracle programming
environment.
 SQL* Plus is an interactive tool that allows you to
type SQL and PL/SQL statements at the command
prompt.
 In the next section we will see how to write PL
SQL program using SQL * Plus
Writing Sample program using SQL * plus
 PL/SQL is a block-structured language. That is, the basic

units (procedures, functions, and anonymous blocks)
that make up a PL/SQL program are logical blocks,
which can contain any number of nested sub-blocks.
 Typically, each logical block corresponds to a problem
or sub-problem to be solved.
 A block (or sub-block) lets you group logically related
declarations and statements.
 That way you can place declarations close to where they
are used. The declarations are local to the block and
cease to exist when the block completes.
Blocks in PL SQL
 We said that PL/SQL is a block-structured language.

 Each block consists of three sub-parts:
Blocks in PL SQL
 Every PL/SQL statement ends with a semicolon (;).

 PL/SQL blocks can be nested within other PL/SQL blocks
using BEGIN and END.
Following is the basic structure of a PL/SQL block:
The 'Hello World' Example
1. DECLARE // is a keyword
2. message varchar2(20):= 'Hello, World!'; // declaration
3. BEGIN //beginning the executable command
4. dbms_output.put_line(message); // the statement
5. END; // end of the program
Alternatively the above code can be written without
declaration as follows
6. BEGIN
7. dbms_output.put_line(‘Hello World’);
8. END;
Advantages of PL SQL
PL/SQL in Client/Server Architecture
 Many Oracle applications are built as client/server

architecture.
 The Oracle database resides on the server.
 The program that makes requests against this database
resides on the client machine.
 This program can be written in C, Java, or PL/SQL.
 PL/SQL is a part of the Oracle RDBMS, and it can reside in
two environments, the client and the server.
 As a result, it is very easy to move PL/SQL modules
between server-side and client-side applications.
PL/SQL engine
 In both environments, any PL/SQL block or subroutine is processed by the

PL/SQL engine, which is a special component of many Oracle products.
 The PL/SQL engine processes and executes any PL/SQL statements and
sends any SQL statements to the SQL statement processor.
 The SQL statement processor is always located on the Oracle server.
 When the PL/SQL engine is located on the server, the whole PL/SQL
block is passed to
the PL/SQL engine on the Oracle server. The PL/SQL engine processes
the block according to Figure
PL/SQL engine
 PL/SQL engine and the Architecture
PL/SQL engine in the client side
 When the PL/SQL engine is located on the client the PL/SQL

processing is done on the client side.
 All SQL statements that are embedded within the PL/SQL
block are sent to the Oracle server for further processing.
 When the PL/SQL block contains no SQL statements, the entire
block is executed on the client side.
PL/SQL in SQL*Plus
 SQL*Plus is an interactive tool that allows you to type SQL or PL/SQL
statements at the command prompt.
 These statements are then sent to the database.
 The command sent from SQL *Plus is sent to the SQL processor and
PL/SQL engine.
 After they are processed, the results are sent back from the database
and are displayed on the screen.
 However, there are some differences between entering SQL and PL/SQL
statements.
PL/SQL in SQL*Plus ….
 Consider the following example of a SQL statement:
SELECT first_name, last_name FROM student;
 As soon as you type the semicolon & press Enter, result is displayed.
1. BEGIN
2. DBMS_OUTPUT.PUT_LINE(“Hello There!”);
3. End;
4. .
5. /
 Two additional lines at the end of the block contain . and /.
 The . marks the end of the PL/SQL

block and is optional. The / executes the PL/SQL block and is required.
SQL*Plus treats SQL command and PL/SQL blocks differently
 When SQL*Plus reads a SQL statement, it knows that the semicolon

marks the end of the statement.
 Therefore, the statement is complete and can be sent to the database.
 When SQL*Plus reads a PL/SQL block, a semicolon marks the end of the
individual statement within the block.
 In other words, it is not a block terminator.
 Therefore, SQL*Plus needs to know when the block has ended.
 As you can see here, this can be done with a period and a slash.
Another PL SQL block code sample
Look at the following code
1. DECLARE
2. v_student_id NUMBER := &sv_student_id;
3. v_first_name VARCHAR2(35);
4. v_last_name VARCHAR2(35);
5. BEGIN
6. SELECT first_name, last_name INTO v_first_name, v_last_name FROM student
WHERE student_id = v_student_id;
7. DBMS_OUTPUT.PUT_LINE ('Student name: '||v_first_name||' '|| v_last_name);

8.EXCEPTION
9. WHEN NO_DATA_FOUND THEN
10.DBMS_OUTPUT.PUT_LINE ('There is no such student');
11.
END; by Miliyon A
Compiled 30
SUBSTITUTION VARIABLES
 PL/SQL is Programming tool within the Oracle not a stand-alone programming language.
 As a result, it does not really have capabilities to accept input from a user.
 However, SQL*Plus allows a PL/SQL block to receive input information with the help of
substitution variables.
 Substitution variables cannot be used to output values, because no memory is allocated for them.
 SQL*Plus substitutes a variable before the PL/SQL block is sent to the database.
 Substitution variables usually are prefixed by the ampersand (&) or double ampersand (&&)
characters.
 When this example is executed, the user is asked to provide a value for the student ID.
 The student’s name is then retrieved from the STUDENT table if there is a record with the given
student ID.
 If there is no record with the given student ID, the message from the exception-handling section is
displayed on the screen.
DBMS_OUTPUT.PUT_LINE
 DBMS_OUTPUT.PUT_LINE is a call to the procedure PUT_LINE.
 This procedure is a part of the DBMS_OUTPUT package.
 DBMS_OUTPUT.PUT_LINE writes information to the buffer for storage.
 When a program is completed, information in the buffer is displayed on the screen.
 The size of the buffer can be set between 2,000 and 1,000,000 bytes.
 before the PL/SQL block write : SET SERVEROUTPUT ON; or SET

SERVEROUTPUT ON SIZE 5000;
 The first SET statement enables the DBMS_OUTPUT.PUT_LINE statement;
 the default value for the buffer size is used.
 The second SET statement not only enables the DBMS_OUTPUT. PUT_LINE
statement, but also changes the buffer size from its default value to 5,000 bytes.
 To stop writing to the screen type the code: SET SERVEROUTPUT OFF;
PL/SQL Programming Fundamentals
This section introduce you with how to:
 Make use of PL/SQL language components

 Make use of PL/SQL variables
 Handle PL/SQL reserved words
 Make use of identifiers in PL/SQL
 Make use of anchored datatypes
 Declare and initialize variables
 Understand the scope of a block, nested blocks, and labels
using PL/SQL language components
 CHARACTER TYPES and LEXICAL UNITS
The PL/SQL engine accepts four types of characters: letters,

digits, symbols (*, +, –, =, and so
on), and white space.
 When elements from one or more of these character types

are joined, they create a lexical unit (these lexical units can
be a combination of character types).
 The lexical units are the words of the PL/SQL language.
Using PL/SQL language components
PL/SQL lexical units fall within one of the following five groups:
1) Identifiers must begin with a letter and may be up to 30 characters

long.
2) Reserved words are words that PL/SQL saves for its own use (such as
BEGIN, END, and SELECT).
3) Delimiters are characters that have special meaning to PL/SQL, such

as arithmetic operators and quotation marks.
4) Literals are values (character, numeric, or Boolean [true/false]) that

are not identifiers.
5)Compiled
Comments can be either single-line comments (--) or multiline
by Miliyon A 35
comments (/* */).

Using PL/SQL language components
 Variables may be used to hold a temporary value. The syntax is as

follows:
Syntax : variable-name data type [optional default assignment]
Variables may also be called identifiers.
 You need to be familiar with some restrictions when naming variables:
 Variables must begin with a letter and may be up to 30 characters long.
 Consider the following example, which contains a list of valid

identifiers: v_student_id, v_last_name, V_LAST_NAME, apt_#
Note that the identifiers v_last_name and V_LAST_NAME are considered

identical because PL/SQL is not case-sensitive.
illegal variable demonstration
Consider the code below:
1. SET SERVEROUTPUT ON;
2. DECLARE
3. first&last_names VARCHAR2(30);
4. BEGIN
5. first&last_names := 'TEST NAME';
6. DBMS_OUTPUT.PUT_LINE(first&last_names);
7. END;
Consider the output produced.
 Because an ampersand (&) is present in the name of the variable

first&last_names, part of the variable is treated as a substitution variable
 In other words, the PL/SQL compiler treats the portion of the variable
name after the ampersand (last_names) as a substitution variable.
 As a result, you are prompted to enter the value for the last_names
variable every time the compiler encounters it.
illegal variable demonstration
Consider the code below and its output
SET SERVEROUTPUT ON
DECLARE
v_name VARCHAR2(30);
v_dob DATE;
v_us_citizen BOOLEAN;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_name||'born on'||v_dob);
END;
Output
born on
SET SERVEROUTPUT ON;

DECLARE
exception VARCHAR2(15);
BEGIN
exception := 'This is a test';
DBMS_OUTPUT.PUT_LINE(exception);
END;Compiled by Miliyon A 38
Make Use of Identifiers in PL/SQL
Consider the code below and its output
1. SET SERVEROUTPUT ON;
2. DECLARE
3. v_var1 VARCHAR2(20);
4. v_var2 VARCHAR2(6);
5. v_var3 NUMBER(5,3);
6. BEGIN
7. v_var1 := 'string literal';
8. v_var2 := '12.345';
9. v_var3 := 12.345;
10.DBMS_OUTPUT.PUT_LINE('v_var1: '||v_var1);
13.END;
output
v_var1: string literal
v_var2: 12.345
v_var3: 12.345
Make Use of Anchored Data-types
 We have seen basic data types like number, varchar, boolean etcin PL
SQL we can declare variables with.
 Sometimes we need variables to be similar in type with database table

column (attribute) so that when the data type in the database for that
column is changed the type of the variable too changes automatically.
 Consider the column esal for employee salary in the database and a
variable v_esal in the PL SQL program that calculates Net salary using
esal.
 SYNTAX
 Variable name tablename.column-name%type;

 Example : v_esal employee.esal%type;
 SET SERVEROUTPUT ON
 DECLARE
 v_id employee.eid%type:='1234';
 v_sal employee.esal%type;
 BEGIN
 SELECT esal INTO v_sal FROM employee WHERE eid=v_id;
 IF v_sal>=7500 then
 DBMS_OUTPUT.PUT_LINE('I fetched this from Database: '||v_sal);
 ELSE
 DBMS_OUTPUT.PUT_LINE('Balance low');
 END IF;
 END;
 SET SERVEROUTPUT ON
 DECLARE
 v_eid varchar(20):=&sv_eid;
 v_ename Varchar(20); v_etitle Varchar(20);
 v_gs employee.esal%type; v_ns employee.esal%type;
 BEGIN
 SELECT eid, ename, etitle, esal INTO v_eid, v_ename, v_etitle, v_gs FROM employee WHERE eid=v_eid;
 DBMS_OUTPUT.PUT_LINE('Employee Detail'); DBMS_OUTPUT.PUT_LINE('====================');
 DBMS_OUTPUT.PUT_LINE('Employee id: '||v_eid); DBMS_OUTPUT.PUT_LINE('Employee Name: '||v_ename);
 DBMS_OUTPUT.PUT_LINE('Employee Title: '||v_etitle); DBMS_OUTPUT.PUT_LINE('Employee Salary: '||v_gs);
 v_ns:=0.65*v_gs; DBMS_OUTPUT.PUT_LINE('Employee Net Salary: '||v_ns);
 Update employee set esal=v_ns where eid=v_eid;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE ('There is no such student');

 END;
Program Structure
 Program structure: controls the flow of execution of statements in the

program
 It specifies which statements(s) is executed first, which next and then which
till end.
There are mainly three types of program control structures:
 Sequential: programs that contains statements that are executed one after the
other line by line. Every code we wrote so far is sequential in type.
 Conditionals and: programs that contains statements based on conditions.
 Iterative (Loops): statements that are executed repeatedly until a condition

comes false.
 Recursion is type
Compiled by Miliyon A of iteration. 43
Conditionals: conditions
 Conditionals and: programs that contains statements based on conditions.
 Conditions are statements compared to be true or false
If statement
 An IF statement has two forms: IF-THEN and IF-THEN-ELSE.
 An IF-THEN statement allows you to specify only one group of actions to take.
 In other words, this group of actions is taken only when a condition evaluates to TRUE.
 An IF-THEN statement is the most basic kind of a conditional control; it has the following
structure:
IF CONDITION THEN
STATEMENT 1;
... STATEMENT N;
END IF;
 The reserved word IF marks the beginning of the IF statement.
 Statements 1 through N are a sequence of executable statements that consist of one or

more standard programming structures.
 The word CONDITION between the keywords IF and THEN determines whether these
statements are executed.
 END IF is a reserved phrase that indicates the end of the IF-THEN construct.
1. SET SERVEROUTPUT ON
2. DECLARE
3. v_id employee.eid%type:='1234';
4. v_sal employee.esal%type;
5. BEGIN
6. SELECT esal INTO v_sal FROM employee WHERE eid=v_id;
7. IF v_sal>=7500 then
8. DBMS_OUTPUT.PUT_LINE('I fetched this from Database: '||v_sal);
9. END IF;
10.DBMS_OUTPUT.PUT_LINE('Balance low');
11.Compiled
END; by Miliyon A 45
IF-THEN-ELSE STATEMENT
 An IF-THEN-ELSE statement enables you to specify two groups of statements.
 One group of statements is executed when the condition evaluates to TRUE.
 Another group of statements is executed when the condition evaluates to

FALSE.
 You should use the IF-THEN-ELSE construct when trying to choose between two
mutually exclusive actions.
 This is indicated as follows:

IF CONDITION THEN
STATEMENT 1;
ELSE
STATEMENT 2;
END IF;
STATEMENT 3;
IF-THEN-ELSE STATEMENT example: test if the number provided by the user is even
DECLARE
v_num NUMBER := &sv_user_num;
BEGIN
IF MOD(v_num,2) = 0 THEN
DBMS_OUTPUT.PUT_LINE (v_num||' is even number');
ELSE
DBMS_OUTPUT.PUT_LINE (v_num||' is odd number');
END IF;
DBMS_OUTPUT.PUT_LINE ('Done');
END;
Solution
 NULL CONDITION : conditions are known to be TRUE or false.

In some cases, a condition in an IF can be evaluated to NULL than TRUE or FALSE.
 For the IF-THEN construct, the statements are not executed if an associated
condition evaluates to NULL. Next, control is passed to the first executable
statement after END IF.
 For the IF-THEN-ELSE construct, the statements specified after the keyword ELSE
are executed if an associated condition evaluates to NULL.
DECLARE
v_num1 NUMBER := 0;
v_num2 NUMBER;
BEGIN
IF v_num1 = v_num2 THEN DBMS_OUTPUT.PUT_LINE ('v_num1 = v_num2');
ELSE DBMS_OUTPUT.PUT_LINE ('v_num1 != v_num2');
END IF;
END;
 ELSIF Statements
ELSIF (or IF---ELSEIF…ELSE) statement is used when several mutually
exclusive statements are in the program.
An ELSIF statement has the following structure:

If condition 1 then
Statement 1;
Elsif condition 2 then
Statement 2;
Elsif condition 3 then
Statement 3;
...
Else
Statement n;
End if;
 ELSIF Statements example: Computing grade from mark
Write a PL/SQL program that accepts students mark and convert it to grade
letters as per the grade scale given below and displays wrong mark entry for
mark out of the given ranges:
A=[85, 100]
B =[70, 85)
C=[50,70)
D=[40,50)
F=[0,40)
Here Is the
Compiled Solution
by Miliyon A 50
Write a PL/SQL program that accepts students mark and convert it to grade
mark out of the given ranges:
A=[85, 100]
B =[70, 85)
C=[50,70)
D=[40,50)
F=[0,40)
Here Is the
Compiled Solution
by Miliyon A 51
Write a PL/SQL program that retrieve students gpa from students table and
rank students as per the Ranking scale given below:
Gpa>=3.75, Very Great Distniction
Gpa>=3.5, Great Distniction
Gpa>=3.25, Distniction
Gpa>=3.0, Very Good
Gpa>2, Pass
Gpa<2, Poor
Here Is the
Compiled Solution
by Miliyon A 52
Conditional Control
CASE Statements
Conditional Control ： CASE STATEMENT
 A CASE statement has two forms: CASE and

searched CASE.
 A CASE statement allows you to specify a selector

that determines which group of actions to take.
 A searched CASE statement does not have a

selector; it has search conditions that are evaluated
in order to determine which group of actions to
take.
Conditional Control ： CASE STATEMENT
CASE STATEMENTS
A CASE statement has the following structure:
Case selector
when expression 1 then statement 1;
when expression 2 then statement 2;
...
when expression n then statement n;
else statement n+1;
end case;
Conditional Control ： CASE
STATEMENTS
CASE STATEMENTS
 The reserved word CASE marks the start of the CASE statement.
 A selector is a value that determines which WHEN clause should

be executed.
 Each WHEN clause contains an EXPRESSION and one or more

executable statements associated with it.
 The ELSE clause is optional and It works much like the ELSE clause
used in the IF-THEN-ELSE statement.
 END CASE is a reserved phrase that indicates the end of the CASE
statement.
STATEMENTS
CASE STATEMENTS
 Note that the selector is evaluated only once, and the WHEN
clauses are evaluated sequentially.
 The value of an expression is compared to the value of the

selector.
 If they are equal, the statement associated with a particular

WHEN clause is executed, and subsequent WHEN clauses are not
evaluated.
 If no expression matches the value of the selector, the ELSE

clause is executed.
STATEMENTS
 The execution sequence of CASE statement is shown below
STATEMENTS
Example: Even Odd checker using CASE statement
1. DECLARE
2. v_num NUMBER := &sv_user_num;
3. v_num_flag NUMBER;
4. BEGIN
5. v_num_flag := MOD(v_num,2);
6. CASE v_num_flag
7. WHEN 0 THEN
8. DBMS_OUTPUT.PUT_LINE (v_num||’ is even number’);
9. ELSE
10.DBMS_OUTPUT.PUT_LINE (v_num||’ is odd number’);
11.END CASE;
12.DBMS_OUTPUT.PUT_LINE ('Done’);
13. END;
Conditional Control ： SEARCHED CASE STATEMENTS
 A searched CASE statement has search conditions that yield Boolean

values: TRUE, FALSE, or NULL.
 When a particular search condition evaluates to TRUE, the group of

statements associated with this condition is executed.
This is indicated as follows:

CASE
WHEN SEARCH CONDITION 1 THEN STATEMENT 1;
WHEN SEARCH CONDITION 2 THEN STATEMENT 2;
...
WHEN SEARCH CONDITION N THEN STATEMENT N;
ELSE STATEMENT
Compiled by Miliyon A N+1; 60
 When a search condition evaluates to TRUE, control is passed to the

statement associated with it.
 If no search condition yields TRUE, statements associated with the ELSE

clause are executed.
 Note that the ELSE clause is optional.
Searched case example:
DECLARE
BEGIN
-- test if the number provided by the user is even
CASE
WHEN MOD(v_num,2) = 0 THEN
DBMS_OUTPUT.PUT_LINE (v_num||' is even number');
ELSE
DBMS_OUTPUT.PUT_LINE (v_num||' is odd number');
END byCASE;
Compiled Miliyon A 61
write a PL/SQL code (using searched) that accepts grade & credit hour and
calculates and Prints the GP for the course.
GP calculation Rules:
 grade = A/a, GP=4* Credit hours

grade = B/b, GP=3* Credit hours
grade = C/c, GP=2* Credit hours
grade = D/d, GP=1* Credit hours
grade = F/f, GP=0* Credit hours
Otherwise: msg wrong Grade value
Solution
Write a PL/SQL program to accept students mark and convert it to grade

data out of the given ranges:
A=[80, 100]
B =[70, 80)
C=[50,70)
D=[40,50)
F=[0,40)
Solution
Write a PL/SQL program to retrieve students mark and convert it to grade

data out of the given ranges.
Update the course grade column for the student by the calculated grade
A=[80, 100]
B =[70, 80)
C=[50,70)
D=[40,50)
F=[0,40)
Solution
CASE VS SEARCHED CASE STATEMENTS
 DIFFERENCES BETWEEN CASE AND SEARCHED CASE STATEMENTS

It is important to note the differences between CASE and searched CASE
statements.
 Unlike CASE statement searched CASE statement does not have a

selector.
 In addition, its WHEN clauses contain search conditions that yield a

Boolean value similar to the IF statement, not expressions that can yield
a value of any type except a PL/SQL record, an index-by-table, a nested
table, avararray, BLOB, BFILE, or an object type.
Case expression
 An expression is statement that process & assigns some value to a variable.
 The result of an expression yields a single value that is assigned to a
variable.
 In a similar manner, a CASE expression evaluates to a single value that

then may be assigned to a variable.
A CASE expression has a structure almost identical to a CASE statement.
Thus, it also has two forms: CASE and searched CASE.
V_letter_grade :=
case
when v_final_grade >= 90 then 'a'
when v_final_grade >= 80 then 'b'
when v_final_grade >= 70 then 'c'
when v_final_grade >= 60 then 'd'
else 'f'
Case expression
DECLARE
v_num_flag NUMBER;
v_result VARCHAR2(30);
BEGIN
v_num_flag := MOD(v_num,2);
v_result :=
CASE v_num_flag
WHEN 0 THEN v_num||' is even number'
ELSE v_num||' is odd number'
END;
DBMS_OUTPUT.PUT_LINE (v_result);
END;
Searched Case expression
V_letter_grade :=
case
when v_final_grade >= 90 then 'a'
when v_final_grade >= 80 then 'b'
when v_final_grade >= 70 then 'c'
when v_final_grade >= 60 then 'd'
else 'f'
end;
NULLIF and COALESCE Functions
THE NULLIF FUNCTION
 The NULLIF function compares two expressions. If they are equal, the
function returns NULL; otherwise, it returns the value of the first
expression.
 NULLIF has the following structure: NULLIF (expression1, expression2)
 If expression1 is equal to expression2, NULLIF returns NULL.

 If expression1 is not equal expression2, NULLIF returns expression1 ;
 v Number:=5;
 x Number=5;
 DBMS_output.PUT_LINE(NULLIF(x,v));
 What is the output?
THE NULLIF FUNCTION
 The NULLIF function is equivalent to the following CASE expression:

CASE
WHEN expression1 = expression2 THEN NULL
ELSE expression1
END
 example NULLIF demo:

SET SERVEROUTPUT ON
DECLARE
v_remainder NUMBER;
BEGIN
v_remainder := NULLIF(MOD(v_num,2),0);
DBMS_OUTPUT.PUT_LINE ('v_remainder: '||v_remainder);
END;
THE COALESCE FUNCTION
 The COALESCE function compares each expression to NULL from the

list of expressions & returns the value of the first non-null expression.
 The COALESCE function has the following structure:

COALESCE (expression1, expression2, ..., expressionN)
 If expr1 evaluates to NULL, expression2 is evaluated.
 If expr2 does not evaluate to NULL, the function returns expression2.
 If expr2 also evaluates to NULL, the next expression is evaluated.
 If all expressions evaluate to NULL, the function returns NULL.
 The COALESCE function can also be used as an alternative to a CASE

expression. For example, COALESCE (expression1, expression2)
 The COALESCE function can also be used as an alternative to a CASE

expression.
 For example,
COALESCE (expression1, expression2)
is equivalent to
CASE
WHEN expression1 IS NOT NULL THEN expression1
ELSE expression2
END
 If more than two expressions need to be evaluated,

COALESCE (expression1, expression2, ..., expressionN)
is equivalent to:
CASE
ELSE COALESCE (expression2, ..., expressionN)
END
which in turn is equivalent to:
CASE
WHEN expression2 IS NOT NULL
THEN expression2
...
 Look at the following usage of COALESCE FUNCTION
SELECT studresult.sid, studresult.courseid,
studresult.courseinstructorid,
studresult.enrollment,
COALESCE(studresult.courseinstructorid,
studresult.enrollment) Additional
FROM studresult WHERE sid = 'HR001';
 this code selects non-null values from studresult table for

courseinstructiorid and enrollmenet as additional
Iterative Control: Part I
 Iterative controls (Loops) are program control statements used to
execute a particular statement(s) repeatedly until exit condition
 Using a loop, you can write the desired number of records to a table.
 In other words, loops are programming facilities that allow a set of

instructions to be executed repeatedly
PL/SQL has four types of loops:
1. simple loops,
2. WHILE loops,
3. numeric FOR loops, and
4. cursor FOR loops

Iterative Control: Simple Loops
 A simple loop, as you can tell from its name, is the most basic kind of
loop.
 It has the following structure:
LOOP
STATEMENT 1; //loop body
STATEMENT 2;
...
STATEMENT N;
END LOOP;
 The reserved word LOOP marks the beginning of the simple loop.
 Statements 1 through N are statements that is executed repeatedly.
 END LOOP is a reserved phrase that indicates the end of the loop
 The basic structure of simple loop as you seen earlier doesn’t specify
when the loop terminates.
 The sequence of statements is executed an infinite number of times,

because no statement specifies when the loop must terminate.
 Hence, a simple loop is called an infinite loop because there is no

means to exit the loop.
 A properly constructed loop needs an exit condition that determines

when the loop is complete
 EXIT STATEMENT is added to control the loop termination condition

via IF statement or WHEN clause
 The structure of simple loop with exit (via IF) looks like:
LOOP
STATEMENT 1;
STATEMENT 2;
IF CONDITION THEN
EXIT;
END IF;
END LOOP;
STATEMENT 3;
 The Structure of simple loop with exit (via WHEN) looks like:
LOOP
STATEMENT 1;
STATEMENT 2;
EXIT WHEN CONDITION;
END LOOP;
STATEMENT 3;
Notice that the statement IF CONDITION THEN
EXIT; END IF; is equivalent 78to EXIT WHEN CONDITION;
 A simple loop example
2. DECLARE
3. v Number:=&Numberoftimes;
4. d Number:=1;
5. BEGIN
6. LOOP
7. DBMS_OUTPUT.PUT_LINE ('statement executed'||d||'times
');
8. d:=d+1;
9. IF v=d then Exit;
10.END IF; END LOOP; END;
 This program accepts the number of times from user and displays
“statement executed x times ” until
the specified number of times.
 A simple loop example
2. DECLARE
3. v Number:=&Numberoftimes;
4. d Number:=1;
5. BEGIN
6. LOOP
7. DBMS_OUTPUT.PUT_LINE ('statement executed'||d||'times
');
8. d:=d+1;
9. IF v=d then Exit;
10.END IF; END LOOP; END;
 This program accepts the number of times from user and displays
“statement executed x times ” until
the specified number of times.
 A simple loop examples
1. concatenateIDUsingLoop
2. CalculateGRD and UPDATE RECORD PLSQL.txt
Iterative Control: While Loops
 A WHILE loop has the following structure:
while condition loop
statement 1;
statement 2;
... statement n;
end loop;
 The reserved word WHILE marks the beginning of a loop construct.
 The CONDITION is the loop condition that evaluates to TRUE or FALSE.
 The result of this evaluation determines whether the loop is executed.
 Statements 1 through N are a sequence of statements that is executed
repeatedly.
 The END LOOP is a phrase that indicates the end of the loop construct.
Iterative Control:While Loops
example
SET SERVEROUTPUT ON
DECLARE
a Number:=0;
BEGIN
while a<=10 LOOP
DBMS_OUTPUT.PUT_LINE (
'Iteration'||a||'of the WHILE LOOP');
a:=a+1;
END LOOP;
END;
Iterative Control: While Loops
 A boolean valued variable can be used to control loop condition
DECLARE
v_test BOOLEAN := TRUE;
BEGIN
WHILE v_test LOOP
STATEMENTS;
IF TEST_CONDITION THEN
v_test := FALSE;
END IF;
END LOOP;
END;
 This loop executes STATEMENTS until a certain condition happens and

v_test is set to false.
Iterative Control: Numeric FOR Loops
 A numeric FOR loop is called numeric because it requires an integer as
its terminating value. Its structure is as follows:
For Loop_counter in[REVERSE] lower_limit..Upper_limit
LOOP
statement 1;
statement 2;
... statement n;
End loop;
 The reserved word FOR marks the beginning of a FOR loop construct.
 The variable loop counter is an implicitly defined index variable.
 You don’t need to declare the loop counter in the declaration section.
 This variable is defined by the loop construct.

For Loop_counter IN[REVERSE] lower_limit..Upper_limit LOOP
statement 1;
statement 2;
... statement n;
End loop;
 lower_limit & upper_limit are to integer evaluated expressions or

numbers , and the double dot (..) serves as the range operator.
 They define the number of iterations for the loop, and their values are
evaluated once, for the first iteration of the loop.
 At this point, it is determined how many times the loop will iterate.
 Statements 1 through N are the while loop body elements.
 END LOOP is a reserved phrase that marks the end of the loop construct.
86

The key word IN or IN REVERSE shows which way the loop counter begin
A for loop example:
 For v_counter 1-5 (both limit values are included) the statement
v_counter = value is displayed.
A for loop example: Adding 1-5 to variable sum
1. set serveroutput on
2. declare
3. v_sum number:=0;// declared a variable for the sum
4. begin
5. for a in 1..5 loop // a between 1 and 5 IN
6. v_sum:=a + v_sum; //add a values to sum
7. if a=5 then //generate output only if a=5
8. dbms_output.put_line('the sum of the first '||a||'
integers is: '||v_sum);
9. end if; end loop; end;
 It is important to remember that the loop counter is implicitly

defined and incremented when a numeric FOR loop is used.
 As a result, it can’t be referenced

Compiled by Miliyon A 88 outside the body of the FOR
USING THE REVERSE OPTION IN THE LOOP
the same program in previous slide can be rewritten using IN REVERSE
option as follows:
1. set serveroutput on
2. declare v_sum NUMBER:=0;
3. begin
4. for a IN REVERSE 1..5 loop // a between 1 and 5 IN
5. v_sum:=a + v_sum; //add a values to sum
6. if a=1 then //generate output only if a=1
7. dbms_output.put_line('the sum is: '||v_sum);
8. end if; end loop; end;
The Output will be:
The sum is 15.
Iterative Control: the CONTINUE statement
 The CONTINUE statement causes a loop to terminate its current

iteration and pass control to the next iteration of the loop when the
CONTINUE condition evaluates to TRUE.
 The CONTINUE condition is evaluated using an IF statement.
 When the CONTINUE condition evaluates to TRUE, control is passed to

the first executable statement in the body of the loop.
 This is indicated by the following:
LOOP
STATEMENT 1;
STATEMENT 2;
IF CONTINUE_CONDITION THEN
CONTINUE;
END IF;
EXIT WHEN EXIT_CONDITION;
END LOOP;
STATEMENT 3;
Iterative Control: the CONTINUE statement
 Problem: Write a PL SQL program (using LOOP and the

CONTINUE Statement) that adds even numbers between x
and Y(x and y are set by users).
 Solution: if MOD(num,2,1) CONTINUE else Sum=sum+num

DECLARE
V_initial NUMBER:=&VS_initial;
V_FINAL NUMEBR:=&FINAL;
Sum NUMBER:=0;
BEGIN
For num in V_initial.. V_FINAL LOOP
Sum:= sum + num;
If MOD(num,2)=1 then
CONTINUE;
END IF;
Iterative Control: the CONTINUE when statement
 The CONTINUE WHEN statement causes a loop to terminate its current

iteration and pass control to the next iteration of the loop only if the
CONTINUE WHEN condition evaluates to TRUE.
 Control is then passed to the first executable statement inside the

body of the loop.
 The structure of a loop using a CONTINUE WHEN clause is as follows:

Loop
statement 1;
statement 2;
continue when continue_condition;
exit when exit_condition;
End Loop;
statement 3;
 The continue and continue when statements are alternatives and can
be used interchangeably.
 IF CONDITION THEN
CONTINUE;
END IF; is equivalent to CONTINUE WHEN CONDITION;
 The CONTINUE and CONTINUE WHEN statements are valid only when
placed inside a loop.
 When placed outside a loop, they cause a syntax error. To avoid this
error, use the RETURN statement .
That means IF condition THEN return; or RETURN; WHEN condition is

syntactically correct outside loops.
 It is very important to understand the difference between the EXIT

and CONTINUE conditions.
 The EXIT condition terminates the whole loop when a certain

condition holds TRUE, whereas the CONTINUE condition terminates
the current iteration of the loop not the whole remaining iterations
loop.
1. For a IN 1..10 LOOP
1. For a IN 1..10 LOOP
2. DBMS_OUTPUT.PUT_LINE(a); 2. DBMS_OUTPUT.PUT_LINE(a);
3. If a=5 3. If a=5
4. Exit; 4. CONTINUE;
5. END IF; 5. END IF;
6. END LOOP
6. END LOOP
94
2. DECLARE This Program inserts for subject
3. v_idsegm VARCHAR(20):='HR00'; mark detail for 8 students in the
4. v_idfdigit int:=1;
5. v_id VARCHAR(20):='NULL'; database table STUDRESULT
6. BEGIN
WHEN EXECUTED the output is:
7. Loop
8. v_idfdigit:=v_idfdigit+1;
9. if v_idfdigit>9 THEN
10. EXIT;
11. END IF;
12. v_id:=v_idsegm||v_idfdigit;
13. INSERT INTO STUDRESULT VALUES (v_id,'SENG341',57,'N','Null','REGULAR');
17. END LOOP;
18. DBMS_OUTPUT.PUT_LINE(v_idfdigit||' Amount of data inserted');
19. END; by Miliyon A
Compiled 95
Iterative Control: Nested Loops
 Any type of LOOP can reside inside any other loop body.
 When a loop resides inside another loop we call such construct a

nested loop
 In such cases we use terms outer loop to refer the hosting loop and
inner loop to refer the embraced loop.
 For each iteration of the outer loop the whole iterations of the inner
loop are executed until its condition comes FALSE.
 When the iterations of the inner loop are finished (when the inner
loop’s condition comes FALSE) the next iteration of the outerloop
begins and inner loop is executed wholly.
Compiled
Thebynested
Miliyon A LOOP terminates when
96 the outer loops condition comes
 Suppose i is outer loop variable and j is inner loop variable.
 The statement yields the output below
2. BEGIN
3. for i IN 1..3 LOOP
4. for j in 1..3 LOOP
5. DBMS_OUTPUT.PUT_LINE(i||', '||j||‘ only j grow');
6. END LOOP;
7. DBMS_OUTPUT.PUT_LINE('First Value Incremented Now');
8. END LOOP;
9. END;
 LOOP LABELS
When we work with Nested Loops sometimes the beginning and end of
loops might be confusing as the Nesting increases.
In such case we need to label(refer them by some name) loops.

1. <<label_name>>
2. FOR LOOP_COUNTER IN LOWER_LIMIT..UPPER_LIMIT LOOP
3. STATEMENT 1;
4. ...
5. STATEMENT N;
6. END LOOP label_name;
 The label must appear right before the beginning of the loop.
LOOP LABELS
 Now look at our previous NESTED loop example, how much easier
to understand is it now?
2. BEGIN
3. <<Outer_Loop>>
4. for i IN 1..3 LOOP
5. <<Inner_Loop>>
6. for j in 1..3 LOOP
7. DBMS_OUTPUT.PUT_LINE(i||', '||j||‘ only j grow');
8. END LOOP Inner_Loop;
9. DBMS_OUTPUT.PUT_LINE('First Value Incremented Now');
10. END LOOP Outer_Loop;
11.END;
LOOP LABELS: look at this code

1. DECLARE
2. v_test NUMBER := 0;
3. BEGIN
4. <<outer_loop>> FOR i IN 1..3 LOOP
5. DBMS_OUTPUT.PUT_LINE('Outer Loop');
6. DBMS_OUTPUT.PUT_LINE('i = '||i);
7. DBMS_OUTPUT.PUT_LINE('v_test = '||v_test);
8. v_test := v_test + 1;
9. <<inner_loop>> FOR j IN 1..2 LOOP
10. DBMS_OUTPUT.PUT_LINE('Inner Loop');
11. DBMS_OUTPUT.PUT_LINE('j = '||j);
12. DBMS_OUTPUT.PUT_LINE('i = '||i);
13. DBMS_OUTPUT.PUT_LINE('v_test = '||v_test);
14. END LOOP inner_loop;
15. END LOOP outer_loop;
16. END;
Error Handling and Exceptions
 PL SQL programs may generate two types of errors : compilation

errors and runtime errors.
 PL/SQL block that handles runtime errors is called the

exception-handling section, and in it, runtime errors are called
exceptions.
 The exception-handling section allows programmers to specify what

actions should be taken when a specific exception occurs .
 Exception occurs not because of wrong syntax in program but in

logical problems.
 PL/SQL has two types of exceptions: built-in and user-defined.

 Look at the code below:

2. DECLARE
3. x integer:=&x; y integer:=&y;z integer;
4. begin
5. Z:=x/y;
6. dbms_output.put_line(z);
7. end;
 When you run this code you get syntax error not runtime error

 Look at the code below:
2. DECLARE
3. x integer:=&x; y integer:=&y;z integer;
4. begin
5. Z:=x/y;
6. dbms_output.put_line(z); end;
 When you run this code you get no error enter x = 3 and y = 0 to
generate runtime error; such an error is what we call Exception.

 To handle runtime error in the program, exception handler is used.
 The exception-handling section is placed after the executable section

of the block. The exception handling section has the following
structure:
Exception
WHEN exception_name THEN
Error-processing statements;
 Example: DIVISION-BY-ZERO EXCEPTION
DECLARE
v_num1 INTEGER := &sv_num1;
v_num2 INTEGER := &sv_num2;
v_result NUMBER;
BEGIN
v_result := v_num1 / v_num2;
DBMS_OUTPUT.PUT_LINE ('v_result: '||v_result);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('A number104can’t be divided by zero');
END;
 An example of exception handling for square root function
 If input is negative number or text then the square root is not
defined.
1. DECLARE
2. v_num1 INTEGER := &sv_num1;
3. v_result NUMBER;
4. BEGIN
5. v_result := SQRT(v_num);
6. DBMS_OUTPUT.PUT_LINE ('v_result: '||v_result);
7. EXCEPTION
8. WHEN VALUE_ERROR THEN
9. DBMS_OUTPUT.PUT_LINE(‘Square Root of ‘||v_num1||’is
Undefined’);
10.END;
We can rewrite this code as follows:
IF num1>=0 THEN
v_result := SQRT(v_num);
DBMS_OUTPUT.PUT_LINE ('v_result: '||v_result);
SCOPE OF EXCEPTIONS
 the scope of an exception is the portion of the block that is covered

by this exception.
 If you define an exception in a block, it is local to that block.

However, it is global to any blocks enclosed by that block.
 In other words, in the case of nested blocks, any exception defined in

the outer block becomes global to its inner blocks.

User Defined Exceptions
 There are set of defined exceptions that PL/SQL understand and
categorizes in group. Those are called Built-in Exceptions.
 Built-in Exceptions cant fulfill every of exceptions your program need

to encounter
 You define and use your own exceptions. For example what if an
integer variable you defined for student mark is 198? Built in
exceptions doesn’t know this.
 In such cases you need to use your own program specific exceptions.
 This type of exception is called a user-defined exception because the

programmer defines it.
 As a result, before the exception can be used, it must be declared.

User Defined Exceptions
 A user-defined exception is declared in the declaration section of a
PL/SQL block:
DECLARE
exception_name EXCEPTION;
 After an exception has been declared, the executable statements

associated with this exception are specified in the exception-handling
section of the block. The format of the exception handling section
is :
BEGIN ...
IF CONDITION THEN
RAISE exception_name;
ELSE
...
END IF;
EXCEPTION
WHEN exception_name THEN
ERROR-PROCESSING STATEMENTS;
User Defined Exceptions: Example
 This Program calculates grade based on student mark. If student
mark is < 0 or >100 it generate mark is unknown Message.
1. DECLARE
2. e_a EXCEPTION;
3. mark INTEGER:=&STUDENTMARK;
4. grd VARCHAR(2):='NG';
5. BEGIN
6. if mark > 80 AND mark <=100 THEN grd:='A';
7. ELSIF mark>=60 AND mark <80 THEN grd:='B';
8. ELSIF mark>=50 AND mark <60 THEN grd:='C';
9. ELSIF mark>=40 AND mark <50 THEN grd:='D';
10.ELSIF mark>0 AND mark <40 THEN grd:='F';
11.ELSE RAISE e_a;
12.END IF;
13.DBMS_OUTPUT.PUT_LINE('The Grade for mark '||mark||' is: '||grd);
14.EXCEPTION WHEN e_a THEN DBMS_OUTPUT.PUT_LINE(mark||‘ Unknown value');
15. END;

Advanced Database PL SQL

Uploaded by

Copyright:

Available Formats

Advanced Database PL SQL

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Advanced Database PL SQL

Uploaded by

Copyright:

Available Formats

College of Computing and Informatics

Department of Software Engineering

 To run SQL*Plus, you need hardware, software, operating

 Before you can begin using SQL*Plus, both Oracle and

 You also need authorized access to the database (username

 SQL * Plus is an interactive and batch query tool that is

 To connect to an already existing Oracle Database using

With PL/SQL, you can use SQL statements to

At this point it is important to introduce PL SQL.

The following are features of PL SQL

 PL SQL is not a stand-alone programming

 PL/SQL is a block-structured language. That is, the basic

 We said that PL/SQL is a block-structured language.

 Every PL/SQL statement ends with a semicolon (;).

 Many Oracle applications are built as client/server

 In both environments, any PL/SQL block or subroutine is processed by the

 The SQL statement processor is always located on the Oracle server.

 PL/SQL engine and the Architecture

 When the PL/SQL engine is located on the client the PL/SQL

 These statements are then sent to the database.

 The . marks the end of the PL/SQL

 When SQL*Plus reads a SQL statement, it knows that the semicolon

 Therefore, the statement is complete and can be sent to the database.

 In other words, it is not a block terminator.

 Therefore, SQL*Plus needs to know when the block has ended.

7. DBMS_OUTPUT.PUT_LINE ('Student name: '||v_first_name||' '|| v_last_name);

 This procedure is a part of the DBMS_OUTPUT package.

 DBMS_OUTPUT.PUT_LINE writes information to the buffer for storage.

 When a program is completed, information in the buffer is displayed on the screen.

 before the PL/SQL block write : SET SERVEROUTPUT ON; or SET

 The first SET statement enables the DBMS_OUTPUT.PUT_LINE statement;

 the default value for the buffer size is used.

This section introduce you with how to:

 Make use of PL/SQL language components

 CHARACTER TYPES and LEXICAL UNITS

The PL/SQL engine accepts four types of characters: letters,

 When elements from one or more of these character types

 The lexical units are the words of the PL/SQL language.

1) Identifiers must begin with a letter and may be up to 30 characters

3) Delimiters are characters that have special meaning to PL/SQL, such

4) Literals are values (character, numeric, or Boolean [true/false]) that

comments (/* */).

 Variables may be used to hold a temporary value. The syntax is as

Variables may also be called identifiers.

 You need to be familiar with some restrictions when naming variables:

 Variables must begin with a letter and may be up to 30 characters long.

 Consider the following example, which contains a list of valid

Note that the identifiers v_last_name and V_LAST_NAME are considered

Consider the output produced.

 Because an ampersand (&) is present in the name of the variable

SET SERVEROUTPUT ON;

 Sometimes we need variables to be similar in type with database table

 Variable name tablename.column-name%type;

 SELECT esal INTO v_sal FROM employee WHERE eid=v_id;

 DBMS_OUTPUT.PUT_LINE('I fetched this from Database: '||v_sal);

 v_ename Varchar(20); v_etitle Varchar(20);

 v_gs employee.esal%type; v_ns employee.esal%type;

 DBMS_OUTPUT.PUT_LINE('Employee Detail'); DBMS_OUTPUT.PUT_LINE('====================');

 DBMS_OUTPUT.PUT_LINE('Employee id: '||v_eid); DBMS_OUTPUT.PUT_LINE('Employee Name: '||v_ename);

 DBMS_OUTPUT.PUT_LINE('Employee Title: '||v_etitle); DBMS_OUTPUT.PUT_LINE('Employee Salary: '||v_gs);