Oracle 10g PLSQL
This book belongs to
Name : ______________________________________
Batch : ______________________________________
SQL STAR INTERNATIONAL LTD
.
SQL Star House,
No. 8-2-293/174/A 25,
Road No. 14, Banjara Hills,
Hyderabad - 500 034,
Andhra Pradesh, INDIA
Copyright © 2008
Second EditionSQL STAR INTERNATIONAL LIMITED
SQL Star House,8-2-293/174/A25, Road No.14,
Banjara Hills, Hyderabad - 500 034.
Tel. No. 91- 40-23101600(30 lines)
Fax No. 23101663
Toll Free No: 1800 425 2944
Email:
[email protected]
No part of this publication may be reproduced (incl. photocopying) in any way, without prior agreement and written permission of SQL Star International Ltd., Hyderabad. SQL Star International Ltd., Hyderabad assumes no responsibility for its use, nor for any infringements of patents or other rights of third parties which could result.
Table of Contents
10g PL /SQL
CHAPTER
No.
CHAPTER TITLE
PAGE
NO
1. Introduction to PL/SQL 1-25
2. DML’s in PL/SQL 26-38
3. Control Structure 39-59
4. Composite DataTypes 60-104
5. Cursors and advanced cursors 105-128
6. Handling exception in PL/SQL 129-146
7. Creating subprograms 147-170
8. Managing subPrograms 171-184
9. Creating Packages 185-204
10. Using More Package Concepts 205-223
11. Oracle Supplied Packages 224-250
12. PL/SQL Performance Enhancements 251-277
13. Large Objects 278-295
14. Database Triggers 296-320
Chapter 1
Introduction to PL/SQL
Need for PL/SQL
Benefits of Using PL/SQL
PL/SQL Block Types and Constructs
PL/SQL Block Structure
Operators and SQL Functions in PL/SQL
Variables
Nested Blocks
Objectives
At the end of this chapter, you will be able to:
State the need for a procedural language in Oracle
Create PL/SQL blocks
Write nested blocks
Introducing PL/SQL
In the journey so far, you have learnt about the Structured Query Language (SQL). The Oracle software provides a language, Procedural Language/SQL (PL/SQL), which is an extension of SQL.
PL/SQL implements modularity in the codes you write. It allows you to perform iterations and handle errors. Features like data hiding and error handling make PL/SQL a state-of-the-art language for databases.
PL/SQL also allows data manipulation and SQL query statements to be included within procedural units of code. This makes PL/SQL a powerful transaction processing mechanism.
Need for PL/SQL
The requirement for PL/SQL was mainly due to the need to centralize automated business tasks. For example, if each employee in an organization maintains separate programs to manage their tasks and updates them at their discretion, it could create confusions, such as:
• New employees or existing employees who are handed over someone else’s work, would have a problem understanding the process followed by the employee vis-à-vis the organization
• Any change in business policy or functionality would have to be updated by all the employees individually in all relevant programs.
In a centralized functionality this is avoided because all changes need to be made at one place. Changes will get reflected in the relevant codes and all users can access updated information.
Benefits of Using PL/SQL
The reasons for using PL/SQL are as follows:Integrating with the Oracle server and Oracle development tools Implementing performance enhancements in an application Modularizing the development of programs
Implementing Portability Declaration of variables
Programming with procedural language control structures Handling errors
Integration
PL/SQL plays a key role in:
Oracle server, through stored procedures and functions, database triggers and packages
Oracle development tools, through Oracle Developer component triggers
PL/SQL supports the use of SQL datatypes. With direct access provided by SQL, these shared datatypes integrate PL/SQL with the Oracle server data dictionary. Hence, PL/SQL successfully bridges the gap between access to database technology and the need for procedural language capabilities.
Most applications such as Oracle Forms, Oracle Reports and Oracle Graphics, use shared libraries (that hold code), which can be accessed locally or remotely. To execute these stored codes, the Oracle tools have their own PL/SQL engine (independent of the engine present in the Oracle server). The engine first filters out the SQL statements to send them individually to the SQL statement executor in the Oracle server. It then processes the remaining procedural statements in the procedural statement executor in the PL/SQL engine. The procedural statement executor processes data, which is already inside the client environment and not in the database. This reduces the workload on the Oracle server and also the amount of memory required.
Performance Enhancements of Applications
When SQL statements are sent to the Oracle server one at a time, each statement results in a call to the Oracle server, leading to performance overhead and network traffic. If your application is SQL intensive, then instead of sending SQL statements individually to the server, you can put them into one block using PL/SQL and send the entire block to the server at one time. This reduces network traffic.
PL/SQL also operates with Oracle development tools, thereby adding procedural processing power to these tools and enhancing performance.
Modularized Program Development
PL/SQL programs are made up of one or more blocks. These blocks may be individual ones or may be nested within another. That is, a block may represent a small part of another block, which may in turn be part of a whole unit of code. The units (procedures, functions or anonymous blocks) making up a PL/SQL program are called logical blocks.
A diagrammatic representation of modularization is:
The advantages associated with modularized development of programs are:
Logical groupings of related statements within blocks
Nesting blocks within larger blocks help build powerful programs
Breaking down complex problems into manageable sets of logical, well-defined modules, which can be implemented within blocks
Portability
PL/SQL is portable. That means, PL/SQL programs can be run wherever the Oracle server exists. There is no need to tailor them to suit each new environment. This is achieved because PL/SQL is native to the Oracle server, and therefore can be moved to any environment (operating system or platform) that supports the Oracle server. PL/SQL code can also be moved between the Oracle server and Oracle Developer applications by writing programs and creating libraries in different environments.
Variable Declaration
In PL/SQL, you can declare variables:
To use them in SQL and procedural statements Belonging to different data types
Dynamically based on the structure of tables and columns in the database
Programming with Procedural Language Control Structures
PL/SQL allows the usage of control structures, which enable you to execute: Sequence of statements conditionally
Sequence of statements iteratively in a loop
Individually the rows returned by multiple-row query Handling Errors
PL/SQL implements error handling functionality by:
Processing Oracle server errors with error handling routines
Declaring your own error conditions and process them with error handlers
PL/SQL Block Types and Constructs
There are two block types and different types of constructs in PL/SQL. A block is a PL/SQL code. A construct is the way in which a block is written to implement different functionality.
Block Types
Logical blocks are the basic units of code that make up a PL/SQL program. The two kinds of PL/SQL blocks are:
Anonymous blocks
Named blocks or Subprograms Anonymous Blocks
Anonymous blocks are declared at that point in an application from where they are to be executed and are passed to the PL/SQL engine for execution at runtime. These blocks are unnamed blocks. They can be embedded in the iSQL*Plus environment. An application trigger consists of these blocks.
Subprograms
Unlike anonymous blocks, named blocks or subprograms are given a name. These blocks can be invoked for execution and they can also accept parameters.
Subprograms can be declared as Procedures or Functions. You declare a subprogram as a Procedure to perform an action, and you declare a subprogram as a Function to compute a value. Subprograms can be written and stored either at the server side or at the client side.
Constructs
There are various PL/SQL program constructs available that use the basic PL/SQL block. The availability of program constructs depends on the environment in which they are executed.
PL/SQL Block Structure
To write a PL/SQL block, you need to know the different parts of a PL/SQL block and what each part should hold. The structure of all PL/SQL blocks is the same. The only difference is that, if it is an anonymous block it is not given a name.
A PL/SQL block has three sections . They are :
Declarative section Executable section
Exception handling section
The declarative section is where the variables and constants used in the body of the block are declared. Any cursors or user-defined error handlers that are used in the body are declared here. This section is optional.
The executable section holds the set of statements or the logic of the tasks that are to be performed. You can include SQL statements to make changes to the database and PL/SQL statements to manipulate data in the block. The statements in this block are enclosed within the keywords BEGIN and END. This section is mandatory.
The last section of a block is the exception section. Here a set of statements is written to handle any errors that might occur when the statements in the executable section are being executed. This section is optional .
The diagramatic expression of PL/SQL block structure is given below.
The syntax for writing a PL/SQL block is:
DECLARE
<variable name> datatype(size); BEGIN
SQL statements; PL/SQL statements; EXCEPTION
WHEN <exception name> THEN …
END; /
Some points that will help you write a block:
A line of PL/SQL text which contains group of characters known as lexical units. These units are classified as follows:
Delimiters are simple or compound symbols that have a special meaning to PL/SQL. The following table presents both the simple as well as the compound symbols:
Identifiers are used to name PL/SQL program constructs such as Constants, Variables and Exceptions. The following points need to be kept in mind while using identifiers:
• Identifiers can be up to 30 characters in length, but ensure they start with an alphabet.
• Do not give the same name for the identifiers as the name of columns in a table used in the block. If so, then the Oracle server assumes that the table column is being referred.
• An identifier consists of a letter, followed (optional) by other letters, numerals, underscores, dollar signs and number signs. The following characters are however illegal:
Abc&efg – illegal ampersand Abc-efg – illegal hyphen Abc/efg – illegal slash Abc efg – illegal space
• Identifiers should not be reserved words except when they are used within double quotes, for instance “INSERT”.
Literals are made up of definite values such as character, numeric, string or Boolean values, not represented by identifiers. These are case sensitive.
Literals are of two types:
• Character literals: are all the printable characters such as letters, numerals, spaces, and special symbols. Specify character literals in single quotes.
• Numeric literals: are represented either by a simple value such as –12 or by a scientific notation such as 3E6.
Comments are extra information given by the programmer to improve readability in a code, to enable documenting in each phase and debugging.
PL/SQL code can be commented in two ways:
• Single line comment represented by --
• Multi line comment represented by /* */ Use a semicolon (;) at the end of all SQL and PL/SQL control statements and
the
END keyword.
Do not use semicolons after the keywords DECLARE, BEGIN and EXCEPTION. Increase the readability of the block by writing the keywords in uppercase. Use a slash (/) to terminate a PL/SQL block on a line by itself.
Using Quotes in Literals
Oracle 10g allows you to define your own string delimiters to remove the need to double up any single quotes.
SET SERVEROUTPUT ON BEGIN
--original code
DBMS_OUTPUT.PUT_LINE(‘He is New Jersey Library
‘’s Member!’);
-- using quote symbol
DBMS_OUTPUT.PUT_LINE(q’# He is New Jersey Library‘s Member!#’);
DBMS_OUTPUT.PUT_LINE(q’[ He is New Jersey
Library‘s
END;
/
He is New Jersey Library‘s Member!
He is New Jersey Library‘s Member!
He is New Jersey Library‘s Member !
PL/SQL procedure successfully completed
Operators in PL/SQL
The following operators are supported in PL/SQL: Arithmetic Logical Relational or Comparison Concatenation Exponentiation [Represented by (**)]
SQL Functions in PL/SQL Statements
All the SQL functions can also be used in procedural statements in a block. These include:
Single-row number and character functions Datatype conversion functions
Date functions
Timestamp functions
GREATEST and LEAST functions
The functions that cannot be used in procedural statements are: DECODE
Group functions like AVG, MIN, MAX, COUNT, SUM, STDDEV and VARIANCE. These functions work only on a group of rows in a table and hence, they can be used only with the SQL statements in a PL/SQL block.
Variables
A variable are named memory locations used to store data temporarily. The data stored in variables is used in the blocks and then processed. When the processing is completed, the data held in the variables may be written to the database or simply erased in case of session wide variables.
Why is a Variable used?
A Variable stores data temporarily. It manipulates the stored data and performs calculations with the data without accessing the database. Once declared, variables can be used repeatedly in an application by referencing them in other statements in the block.
When variables are declared using %TYPE and %ROWTYPE (more information is provided later in the chapter), you are infact basing the variable declaration on the column definition of a table. In this case, if the column definition changes, then the variable declarations also changes accordingly. This helps in data independence, reduces maintenance cost and allows the program to adjust to the new business logic.
How to handle variables in PL/SQL? In a PL/SQL block, variables are:
Declared and initialized in the declarative section of the block.
Assigned new values in the executable section. On doing so, the existing value is replaced with the newly assigned value. Care must be taken to see that a variable being referred to is already declared in the declarative section. Forward references cannot be made.
Types of Variables
Variables are of two types. They are: PL/SQL variables
Non-PL/SQL variables PL/SQL Variables
PL/SQL variables have a data type that specifies a storage format, constraints and also valid range of values.
The data types used to declare PL/SQL variables are:
Scalar data types are those that correspond to database column types. These data types hold only a single value. The base scalar data types include:
CHAR VARCHAR2 LONG LONG RAW NUMBER BINARY_INTEGER PLS_INTEGER
BINARY_FLOAT
BINARY_DOUBLE
BOOLEAN DATE TIMESTAMPTIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIMEZONE INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
Binary_Float and Binary_Double are the two new Datatypes introduced in Oracle10g.They represent floating point numbers in IEEE 754 format (Institute of
Electrical and Electronic Engineers) and require 5 byte and 9 bytes to store the values respectively. IEEE format s supported by most of the computer system operating through native processor instructions thereby helping us to carry out complex computations using floating point data.
Composite data types are those that are defined by users. They enable you to manipulate groups of data in PL/SQL blocks
Reference data types are those that hold values pointing to other objects. These are also known as pointers.
LOB (large object) data types: are those that hold values, which specify the location of large objects such as graphic images. These values are known as locators. Large objects are stored in the same database as the table but not within the table. LOB data type allows you to store unstructured data of a maximum of 8-128 terabytes. This data could be a movie clip or a graphic image or a sound wave form. LOBs are further classified into:
CLOB (Character Large Objects) is used to store large blocks of character data of a single byte.
BLOB (Binary Large Object) is used to store large binary objects within the database.
BFILE (Binary File) is used to store large binary objects that are in the operating system files outside the database.
NCLOB (National Language Character Large Objects), are used to store large blocks of NCHAR data that may be single-byte or fixed-width multiple bytes The syntax for declaring PL/SQL variables is:
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
Where,
identifier is the name assigned to the variable declared.
CONSTANT specifies a constraint that the value of the variable cannot change. Constant variables must be initialized. While declaring a variable as a constant, the CONSTANT keyword must precede the datatype specification.
DATATYPE specifies the type of data the variable can hold. It could be scalar, composite, reference or LOB datatype.
NOT NULL specifies a constraint that the variable must contain a value. Therefore, NOT NULL variables must be initialized.
:= is the assignment operator used to assign an expression to a variable. Instead of the assignment operator, the DEFAULT expr (expression) can be used to assign values to the variables.
By default, all variables are initialized to NULL. To prevent null values, variables are initialized using the DEFAULT keyword.
The following code snippet shows how PL/SQL variables are declared in the declarative section of a block:
DECLARE
FirstName CHAR(20);
BranchID CHAR(7) NOT NULL: = ‘09RANNJ’; FeeAmt CONSTANT NUMBER(2): = 15;
CatgName CHAR(15) DEFAULT ‘Fiction’;
% TYPE Attribute
If you need to store a database column value in a variable or write a value from a variable to a database column, then the data type of the variable needs to be the same as that of the database column. You can use the %TYPE attribute to declare a variable to be of the same data type as that of a previously declared variable or database column.
Incorrect variable data types generate PL/SQL errors during execution. When you want to declare a variable using a table attribute instead of the datatype the syntax is
<variable_name> table.columnname%TYPE
For example, in case you want to declare a variable that will store the address of a library member, you will write in the following syntax:
DECLARE
Address VARCHAR2(45);
But, the above declaration will result in an error when an attempt is made to populate the variable with address details of members from the database table. This is because there is a mismatch in type specification of the variable. The datatype width of the vAddress column in table Member is 50, but you have declared it as 45. To overcome this, declare the variable using %TYPE attribute as follows:
DECLARE
Address Member.vAddress%TYPE;
This statement declares a variable whose datatype and width is based on the vAddress column.
If you want to declare a variable of the same type as a previously declared variable then the syntax is
<variable_name> variable_name%TYPE
Using %TYPE attribute to declare a variable based on a previously declared variable, is illustrated in the section dealing with the iSQL*Plus variables within PL/SQL blocks.
Datatype and Variable size is determined when the block is compiled. So even if there is a change in the database column datatype the code manages the changed datatype information.
Data Conversion Functions
In any programming language generally, we have to deal with different datatypes simultaneously or receive data which is not in the default format. In such cases, Oracle server implicitly converts data into valid datatypes wherever feasible. Explicit conversions come into the scenario where automatic conversions are not possible. Oracle Server takes care of implicit conversion between
1. Character and Number 2. Character and Date
But how is it done? Let us take an example.
DECLARE cons_nfine NUMBER(3):=50; cons_extra_fine VARCHAR2(20):=’5'; tot_fine Transaction.nfine%TYPE; BEGIN tot_fine:= cons_nfine+cons_extra_fine;
DBMS_OUTPUT.PUT_LINE(‘The total fine payable is
Rs.‘||tot_fine); END;
/
So, did you notice something?
Variable cons_nfine is of number datatype and cons_extra_fine is of VARCHAR2 datatype. While assigning the result to tot_fine variable, cons_extra_fine is converted to number by PL/SQL executer and then the operation is performed.
To perform explicit conversion, following built in functions can be used. to_char() to_number() to_date() to_Binary_float() to_Binary_double() DECLARE
v_Date date:= to_Date( ‘April 04 2007’,’Month dd YYYY’); BEGIN
DBMS_OUTPUT.PUT_LINE(‘You have entered ‘ ||v_Date ||’ as input’);
END; /
Example to show the usage of new datatypes.
DECLARE l_binary_float BINARY_FLOAT; l_binary_double BINARY_DOUBLE; BEGIN l_binary_float := 2.1f; l_binary_double := 2.00001d; DBMS_OUTPUT.PUT_LINE(l_binary_double); DBMS_OUTPUT.PUT_LINE(l_binary_float); l_binary_float := TO_BINARY_FLOAT(2.1); l_binary_double := TO_BINARY_DOUBLE(2.00001); DBMS_OUTPUT.PUT_LINE(l_binary_double); DBMS_OUTPUT.PUT_LINE(l_binary_float); END; /
Non-PL/SQL Variables
Since PL/SQL has neither input nor output capabilities, it relies on the environment in which it is executed in order to pass values into and out of a PL/SQL block.
The following non-PL/SQL variables can be used within PL/SQL blocks: Substitution variables
Host variables
Substitution variables are those that you can use to pass values to a PL/SQL block at runtime. To reference a substitution variable in a block, prefix it with an ampersand (&). Before the block is executed the values for the variables are substituted with the values passed.
Hence, you cannot input different values for the substitution variables using a loop. The substitution variable can be replaced only by one value.
Here is an example showing the use of substitution variables within PL/SQL blocks. Suppose, the library wants to calculate its quarterly income based on its annual income. To do this, it declares a substitution variable, which would prompt the user to enter the figure of annual income. Based on the value entered, the quarterly income would be calculated.
DECLARE
AnnualIncome NUMBER (7): = &annualinc; QuarterlyInc AnnualIncome%TYPE;
-—declaring variable based on previously declared variable
using --%TYPE attribute.
BEGIN
QuarterlyInc:= AnnualIncome/4;
DBMS_OUTPUT.PUT_LINE (‘The quarterly income of the library is: ’|| QuarterlyInc);
END; /
In the above block, to display the quarterly income calculated, you can specify the DBMS_OUTPUT.PUT_LINE in the PL/SQL block. DBMS_OUTPUT is an Oracle supplied package and PUT_LINE is a procedure within it. To use this you need to specify the information you want printed, in parentheses, following the DBMS_OUTPUT.PUT_LINE command as shown in the above code:
DBMS_OUTPUT.PUT_LINE (‘The quarterly income of the library is: ’|| QuarterlyInc);
For DBMS_OUTPUT.PUT_LINE to work, you need to run the iSQL*Plus command SET SERVEROUTPUT ON.
iSQL*Plus host variables (also known as bind variables) are used to pass runtime values from the PL/SQL block back to the iSQL*Plus environment. These variables can be referenced in a PL/SQL block by placing a colon(:) before the variable.
VARIABLE <variable_name> datatype
The syntax to display the variable value is:
PRINT <variable_name>
Bind variables cannot be referred within the PL/SQL block of a function, procedure or a package.
In a PL/SQL block, to differentiate between host variables and declared PL/SQL variables, prefix the former with a colon(:).
The code wherein you had calculated the quarterly income using substitution variables can be re-written using host variables as follows:
VARIABLE QuarterlyInc NUMBER DECLARE
AnnualIncome NUMBER(7):= &AnnInc; BEGIN
:QuarterlyInc:= AnnualIncome/4; END;
/
old 2: AnnualIncome NUMBER(7):= &AnnInc; new 2: AnnualIncome NUMBER(7):= 80000; PL/SQL procedure successfully completed. PRINT QuarterlyInc
QUARTERLYINC --- 20000
The above example shows the Host variable being assigned a value inside a PL/SQL
block. To assign a value to a host variable outside the Pl/SQL block following can be
done:-SQL > VARIABLE QuarterlyInc NUMBER SQL > Exec :QuarterlyInc := 20000 SQL> PRINT QuarterlyInc QUARTERLYINC --- 20000 Where,
Exec stand for Execute privilege.
Nested Blocks
PL/SQL allows blocks to be nested wherever you can have an executable statement. [This makes the nested block a statement.] Hence, the executable part of a block can be broken down into smaller blocks. Even the exception section can contain nested blocks.
Variable Scope
Issues that concern references to identifiers can be resolved taking into account their scope and visibility.
By scope, we mean that region of a program unit from which an identifier can be referenced. For instance, a variable in the declarative section can be referenced from the exception section of that block.
The diagram shown below illustrates the concept of nested blocks and variable scope.
By visibility, we mean the regions from which an identifier can be referenced without using a qualified name.
The following code snippet shows how to qualify identifiers:
<<outer_blk>> --Block label DECLARE --This is the parent block
JoiningDt DATE; BEGIN
DECLARE --his is the child block JoiningDt DATE;
BEGIN …
outer_blk.JoiningDt :=TO_DATE (’20- JAN-1998’, ‘DD-MON-YY’);
END; …
END; /
In the code snippet, a variable with the same name as the variable declared in the outer block is declared in the inner block. To reference the outer block variable in the inner block, the variable is qualified by prefixing it with the block name.
Identifiers are considered local to the PL/SQL block in which they are declared, and are considered global to all its sub-blocks. Within the sub-block, only local identifiers are visible because to reference the global identifiers you must use a qualified name. If a block cannot find the identifier declared locally, it will look up to declarative section of the enclosing block (parent block). However, the block will never look down to the enclosed blocks (child blocks).
Look at the following code and determine the variable scope:
<<OUTER_BLK>> DECLARE
vSal NUMBER(8,2) := 50000;
vComm NUMBER(8,2) := vSal * 0.10;
vNote VARCHAR2(200) := ‘Eligible for commission’; BEGIN
DECLARE
vSal NUMBER(8,2) := 90000; vComm NUMBER (4) := 0;
vAnnualComp NUMBER(8,2) := vSal + vComm; BEGIN
vNote := ‘Manager not’||vNote;
OUTER_BLK.vComm := vSal * 0.20;
END;
vNote := ‘Salesman’||vNote;
END;
/
Based on the rules of scoping, determine values of: vNote at position 1 vAnnualComp at position 2 vComm at position 1 OUTER_BLK.vComm at position 1 vComm at position 2 vNote at position 2
Guidelines for Writing PL/SQL Code
Programs can be indented using carriage return,tabs and aligning keywords in the same line at different levels.Writing so, the task of debugging is made simpler. While writing programs we can follow a case convention, though PL/SQL is not case sensitive. For instance:-
• All keywords should be written in uppercase. • They must be aligned in the same line.
• Table names and column names are to be in Initcap and lower case respectively.
Indented programs improve the performance in those cases where similar statements are to be issued repeatedly because there is no need to parse the statements again.
Let us write a program following all the rules stated above.
DECLARE
AnnualIncome NUMBER(7):= 60000; QuarterlyInc NUMBER(8,2); BEGIN
QuarterlyInc:= AnnualIncome/4;
DBMS_OUTPUT.PUT_LINE(‘The Quarterly income is
‘||QuarterlyInc); END;
/
Here, the keywords are aligned in the same line but at different levels. This improves readability of the code.
Summary
In this chapter, you have learnt that:
PL/SQL bridges gap between SQL and a procedural language. Two types of PL/SQL blocks are:
1. Anonymous or unnamed blocks which are not stored in database and compiled each time they are executed.
2. Named Blocks which are compiled only once and stored in the database. The three sections of a PL/SQL Block are:
1. Declarative Section, where all the variables used in the program are declared. This is optional. Keyword: DECLARE
2. Executable Section where actual logic of the program lies. Keyword: BEGIN 3. Exception Section where all the errors are handled. This section is optional.
Keyword: EXCEPTION 4. END to end the PL/SQL program.
PL/SQL variables are declared and are accessible only within that PL/SQL block. Non- PL/SQL variables are declared outside the PL/SQL block and are available throughout the session.
Two new datatypes FLOAT and DOUBLE introduced in this release help users in computing complex calculations.
Nesting of blocks is allowed to have good control on the scope of the variables. Nested Blocks can also have exception sections.
Lab Exercises
1. Identify whether the following declarations are correct or incorrect: a) DECLARE
empID NUMBER(5); b) DECLARE
str1, str2, str3 VARCHAR2(20); c) DECLARE
hiredate DATE NOT NULL; d) DECLARE
on BOOLEAN := 5;
2. Create an anonymous block to display the phrase “Welcome to the world of PL/SQL”.
3. Create a block that declares two variables, one of VARCHAR2 type and the other of NUMBER type. Assign the following values to the variables and display the output on the screen.
4. Write a PL/SQL code to display the ID, Last Name, job ID and salary of an employee?
5. Among the following which datatypes are enhancements of Oracle10g? a) BINARY_INTEGER b) BINARY_FLOAT c) BINARY_DOUBLE
6. Display the following text using quote operator q:
Chapter 2
DMLs in PL/SQL
SELECT Statements in PL/SQL
DML Statements in PL/SQL
Objectives
At the end of this chapter, you will be able to:
Use SELECT statements within PL/SQL blocks
Perform Data Manipulations within PL/SQL blocks
SELECT Statements in PL/SQL Blocks
The use of a PL/SQL block would be incomplete without its ability to interact with the database. To interact with the database you must use SQL. PL/SQL therefore,
supports the use of Data Manipulation Language, Transaction Control commands, SQL functions for extracting data and also applying changes to the database. Embedding SELECT Statements
SELECT statements within PL/SQL blocks help retrieve data from the database. The syntax is the same as when issued from iSQL*Plus, with a slight difference.
SELECT <select_list>
INTO (variable_name[, variable_name] | record_name) FROM <table_name>
WHERE condition;
In the syntax,
select_list specifies a list of columns whose values are to be retrieved. They could also include SQL expressions, row functions or group functions.
variable_name is the variable which has been declared to hold the values that are retrieved. They are also known output variables.
record_name is the PL/SQL record which has been declared to hold the retrieved values. [PL/SQL records will be dealt with in a later session]
table_name is the database table from which values are retrieved.
condition comprises of various operators used to compare column names, constants, expressions and PL/SQL input variables.
The main factor that differentiates this syntax from the usual SELECT syntax written in iSQL*Plus, is the INTO clause. This clause is mandatory and is placed between the SELECT clause and the FROM clause. It specifies the variables into which the values returned by the SELECT clause are to be stored. For each item selected in the
SELECT clause there must be corresponding output variables in the INTO clause. The order of the output variables too must correspond to the columns selected.
Embedded SELECT statements are within the scope of ANSI (American National Standard Institute) Classification of Embedded SQL, which states that ‘Queries must return one and only one row’. Therefore, if queries return more than one row the Oracle server generates an error.
The following example will illustrate the use of SELECT statements within PL/SQL
blocks:
SET SERVEROUT ON DECLARE vBkname Book.cBookName%TYPE; vAuthor Book.cAuthorName%TYPE; BEGINSELECT cBookName, cAuthorName
INTO vBkname, vAuthor
FROM Book
WHERE cBookID = ‘HUM020000323’;
DBMS_OUTPUT.PUT_LINE(vBkname||’ written by: ‘||vAuthor);
END; /
On executing the code, the following result is generated:
Rapunzel meets Santa Claus in Hell written by: Tom Boyle
PL/SQL procedure successfully completed.
In the above program, the block retrieves the book name and author name for the specified book.
In the following code SQL functions are used in the SELECT statement within the block:
DECLARE
vTotcopies NUMBER(4); — Output variable
vBrID Book.cBranchID%TYPE:=’02CHSNJ’;
BEGIN
SELECT SUM(nNoOfCopies)
INTO vTotcopies
FROM Book
WHERE cBranchID = vBrID;
Output
DBMS_OUTPUT.PUT_LINE(‘The total stock of books stored at the
Chester NJ Library is: ‘||vTotcopies);
END; /
On executing the code, the following result is generated:
The total stock of books stored at the Chester NJ Library is: 176 PL/SQL procedure successfully completed.
The block retrieves the total number of copies of books stored in the specified branch.
There is a possibility of ambiguity in the WHERE clause if the variable name and the column name are identical. This is because the Oracle server on encountering a variable on the right side of the comparison operator in the WHERE clause, checks whether it is a column in the database. If it is not, then it assumes it to be a PL/SQL variable. However, if the variable name is identical to the column name, the server considers it to be a column and therefore generates an erroneous result.
Why don’t you try this out to check what erroneous result the server gives?
There is another interesting aspect of the WHERE clause. As you know, it is used in the SELECT statement to restrict the rows that are to be selected. However, if no rows are selected, the server generates an error. For example, if the branch ID specified does not exist in the Branch table and you use it in the WHERE clause, the query would be unable to retrieve any details pertaining to the branch ID specified. The following code would clarify this:
DECLARE vBrName Branch.cBranchName%TYPE; BEGIN SELECT cBranchName INTO vBrName FROM Branch WHERE cBranchID = ’00SCHNJ’; END; /
On executing the code, the following error is generated:
DECLARE *
ERROR at line 1:
ORA-01403: no data found
While embedding SELECT statements within PL/SQL blocks you must remember the following points:
• Each SQL statement must be terminated with a semicolon (;).
• Do not forget the INTO clause in the SELECT statement.
• The WHERE clause specifies a condition using constants, literals or PL/SQL input variables (for example, vBrID). This clause is optional.
• The number of output variables in the INTO clause must be the same as the number of columns selected in the SELECT clause.
• Make sure that the output variables in the INTO clause are mapped correctly to the selected columns, and that their datatypes are also compatible.
• To ensure that the datatypes of variables match that of the columns selected in the SELECT clause, use the %TYPE attribute.
• You can use group functions in the SELECT statement but not in the PL/SQL statements.
• Do not give names to variables that are identical to column names. This could create ambiguity in the WHERE clause.
DML Statements in PL/SQL
You can manipulate data stored in the database by embedding DML statements within
PL/SQL blocks. This has its benefits. When you issue DML statements in iSQL*Plus, they are sent to the Oracle server for execution one at a time. This results in high performance overhead especially in a networked environment. Therefore, to improve performance, club all the DML statements into one PL/SQL block, because the entire block gets sent to the server for execution in one go.
The transaction control options COMMIT, SAVEPOINT and ROLLBACK, that define the beginning, breakpoint and end of a logical unit of activity in SQL statement
processing, are also available in PL/SQL. The option that provides locking mechanism, which enables only one user at a time to change a record in the database, is also supported in PL/SQL.
However, an important difference between issuing SQL statements within PL/SQL blocks and in iSQL*Plus is that the beginning and end of a PL/SQL block does not necessarily imply the beginning or end of a transaction. The execution of the first DML statement in the
PL/SQL block begins a transaction. To save or discard the changes made, the PL/SQL code should explicitly contain COMMIT or ROLLBACK statements.
Embedding INSERT, UPDATE, DELETE and MERGE Statements
The INSERT statement within a PL/SQL block is embedded just as you would enter them in iSQL*Plus.
For example, insert details of a new member ‘Ann Judd’ into the Member table.
BEGIN
INSERT INTO Member
VALUES(‘CAJ040501’,’ANN’,’JUDD’,’26A,Due Drops Apts,Blue Mountain Villa’,‘Elizabeth’,’78099',
NULL,22,SYSDATE,’C’,’N’,’04RANNJ’); END;
/
You can confirm the insertion of a new row by issuing a SELECT statement:
SELECT * FROM Member
This block does not have a declarative section, as you have not declared any
variables that are to be used in the INSERT statement. The VALUES clause requires:
• The actual values ‘Ann’ and ‘Judd’ to be entered into the cFirstName and cLastName columns respectively
• The SYSDATE function to enter the current date into the dMembershipDt column.
You can also insert the above details in the following manner:
DECLARE
cFirstName Member.cFirstName%TYPE DEFAULT ‘Ann’;
BEGIN INSERT INTO Member(cMemberID,cFirstName,cLastName,vAddress, cArea,cZipcode,cPhone,nAge,dMembershipDt, cGrade,cMaritalStatus,cBranchID) VALUES (‘CAJ040501’,cFirstName,’Judd’, ’26A,Due Drops Apts,Blue Mountain Villa’,
‘Elizabeth’,’78099',NULL,22,SYSDATE,’C’,’N’,’04RANNJ’); END;
/
The block declares an input variable cFirstName of Member.cFirstName%TYPE and initializes it with a value ‘Ann’ using the DEFAULT keyword instead of the assignment operator. This variable is then used in the VALUES clause to populate cFirstName column with the value ‘Ann’.
Therefore, in the INSERT statement you can:
• Use functions such as SYSDATE
• Use database sequences to generate sequential numbers
You can make changes to data or remove data stored in the database by embedding UPDATE and DELETE statements within PL/SQL blocks.
For example, the librarian has to:
• Update the number of copies of the book ‘On The Street Where You Live’ in the Book table
• Remove details pertaining to one Mr. Derian Bates from the Member table
DECLARE
vCopiesIncrease Book.nNoOfCopies%TYPE:=3; vMemID Member.cMemberID%TYPE: = ‘CDB028504’; BEGIN
UPDATE Book
SET nNoOfCopies = nNoOfCopies + vCopiesIncrease WHERE cBookName = ‘On The Street Where You Live’; DELETE FROM Member
WHERE cMemberID = vMemID; COMMIT;
END; /
It is important to remember that there could be ambiguity in the SET clause if the declared variable has the same name as that of the column whose value is to be updated. This is because the name on the left of the assignment operator in the SET clause always corresponds to a database column, but on the right side, it could correspond either to a database column or a PL/SQL variable.
For instance, in the above code,
SET nNoOfCopies = nNoOfCopies + vCopiesIncrease;
nNoOfCopies to the left of the operator corresponds to the nNoOfCopies column of the Book table, where as nNoOfCopies to the right also corresponds to the same column. But vCopiesIncrease to the right of the operator corresponds to the input variable. Therefore, there is no ambiguity in this SET clause.
Why don’t you see the outcome of naming a variable as that of a column name?
Use the MERGE statement to update or insert rows in a table using data from
another table. Rows are inserted or updated based on a specified equijoin condition. For example,
DECLARE
vMemID Member.cMemberID%TYPE:= ‘CDB028504’;
BEGIN
MERGE INTO MemberTabCopy mc
USING Member m
ON (m.cMemberID=vMemID)
WHEN MATCHED THEN
UPDATE SET mc.cFirstName = m.cFirstName, mc.cLastName = m.cLastName, mc.vAddress = m.vAddress, mc.cArea = m.cArea, mc.cZipcode = m.cZipcode, mc.cPhone = m.cPhone,
mc.nAge = m.nAge,
mc.dMembershipDt = m.dMembershipDt,
mc.cGrade = m.cGrade
WHEN NOT MATCHED THEN
INSERT VALUES (m.cMemberID, m.cFirstName, m.cLastName, m.vAddress, m.cArea, m.cZipcode, m.cPhone,
m.nAge, m.dMembershipDt, m.cGrade); END;
/
[For the purpose of this code, we have created a table MemberTabCopy, which is similar in structure to the Member table]
The code matches the member ID in the MemberTabCopy table to the member ID in the Member table. If they match, the row is updated to match the row in the Member table or else the row is inserted into the MemberTabCopy table. But, where do the SQL statements issued within the blocks get parsed (that is, checked whether the tables the statements are accessing are the ones on which accessing privileges have been granted) and executed? This brings in the concept of cursors which will be discussed in a later chapter.
Summary
In this chapter, you have learnt that:
Embedded SELECT statement has a INTO clause in it. One or more variable follows after the INTO clause.
Variable names should not collide with the names of the database columns. Data fetched from the database are stored in these variables.
WHERE clause restricts the number of rows to be fetched into these scalar variables.
DML Statements INSERT, UPDATE, DELETE and MERGE can be used to
manipulate the data in the database by embedding these statements inside the PL/SQL block.
By doing so, one can reduce the network traffic, resulting in the performance improvement.
Lab Exercises
1.
Create a PL/SQL block that retrieves the sum of salary of department 80 and stores it in an iSQL*Plus variable. Print the value to the screen.2. Create a PL/SQL block that will:
Retrieve the maximum employee ID in the Employees table.
Insert data regarding a new employee into the Employees table. For the employee ID of the new employee, add 1 to the maximum employee ID retrieved. Leave the phone number, commission and manager ID as null. Display the new row created
.
Chapter 3
Control Structures
Conditional Constructs
Case Constructs
Loop Constructs
GOTO Statement
Objectives
At the end of this chapter, you will be able to:
Use Branching structures
Use Case Control structures
Control Structures
Oracle PL/SQL provides a range of constructs that allow you to control the flow of process and there by produce well-structured programs. The selection structure tests a condition and then executes one sequence of statements instead of another, based on whether the condition is true or false. A condition is any variable or expression that returns a Boolean value (TRUE or FALSE). The iteration structure executes a sequence of statements repeatedly as long as a condition holds true. The sequence structure simply executes a sequence of statements in the order in which they occur. Hence, Control structures are the backbones of a program, where logic of the
transactions are specified.
Control Structures are broadly divided into
• Conditional constructs
• IF Statements
• CASE Statement and CASE Expression
•Loop constructs
• Basic Loop • WHILE Loop • FOR Loop
CONDITIONAL CONSTRUCTS USING IF Statements
When you need to perform actions based on conditions, you use the IF statements. The structure of the IF statement is the same as those used in other procedural programming languages. It has three variations. They are:
IF - THEN - END IF IF - THEN - ELSE - END IF IF - THEN - ELSIF - END IF Their syntaxes are shown below.
IF condition THEN IF condition THEN IF condition THEN statements; statements; statements; END IF; [ELSE [ELSIF condition THEN
statements;] statements;]
END IF; [ELSE
statements;]
Where,
condition is an expression, which when executed will return a value of TRUE or FALSE or NULL. If the result of the expression is TRUE then the set of statements, following the THEN clause is performed else the statements following the ELSE or ELSIF clauses are performed.
THEN is the clause that connects the value returned by the condition to the statements that are to be executed
statements include the SQL or PL/SQL statements that would perform the required actions. They may include further IF statements.
The ELSIF keyword is associated with another set of SQL or PL/SQL statements that should be executed in case the condition does not return the desired value.
The ELSE keyword is also associated with a set of SQL or PL/SQL statements that will be executed, if none of the statements higher up in the construct has been executed, as the condition associated with each was not satisfied.
Let us now look into a few examples of the various IF statements, to have a better understanding of its functioning.
Example of a simple IF statement:
The library does not permit a person to be its member if his/her age happens to be below five. This could be checked using the following IF-THEN-END IF statement:
DECLARE
Age Member.nAge%TYPE:= &age; BEGIN
IF Age<5 THEN
DBMS_OUTPUT.PUT_LINE (‘Membership denied – Invalid age’);
END IF; END;
/
If you execute the above code entering the value for age as 4, you get the following output:
Membership denied – Invalid age
PL/SQL procedure successfully completed.
In the code, PL/SQL displays the message only if the condition is TRUE. If the condition is FALSE or NULL, PL/SQL just ignores them. But if you want some action to be performed in case of FALSE or NULL result, use the ELSE clause.
Example of an IF – THEN – ELSE – END IF Statement
The library charges a fine amount of Rs.1.5 per day after the elapse of the due date. To perform this calculation, the following IF-THEN-ELSE statement can be used:
IF ActualRetDt > ReturnDt THEN
Fine := (ActualRetDt – ReturnDt)*1.5; ELSE
Fine: = 0; END IF;
In the statement, if the actual return date is later than the due date, fine amount is calculated. If not, the fine amount is set to 0.
Example of an IF-THEN-ELSE-ENDIF Statement using Logical Operators
IF LibraryName=’Jersey’ AND ActualRetDt>ReturnDt THEN
Fine:=(ActualRetDt-ReturnDt)*1.5; ELSE
Fine:=0; END IF;
In the statement, the fine is calculated only if the Library Name is Jersey and actual return date is later than the due date. If not, the fine amount is set to 0.
Nested IF Statements
Either set of actions of the first IF statement can include further IF statements. Such a condition is known as a nested IF statements. Its syntax is as follows:
IF condition1 THEN Statement1; ELSE IF condition2 THEN Statement2; END IF; END IF;
The nested IF statement is terminated with its corresponding END IF. Example of nested IF statements
The library charges fee from members based on their ages. To illustrate this execute the following code:
DECLARE
Fee NUMBER; BEGIN
IF Age < 5 THEN
DBMS_OUTPUT.PUT_LINE (‘Membership denied: Age below permissible membership age’);
ELSE
IF Age BETWEEN 5 AND 13 THEN Fee := 10;
ELSE
IF Age BETWEEN 14 AND 20 THEN Fee := 15;
ELSE
IF Age BETWEEN 21 AND 50 THEN Fee := 20; ELSE IF Age > 50 THEN Fee := 5; END IF; END IF; END IF; END IF; END IF;
DBMS_OUTPUT.PUT_LINE (‘Fee for Member is:’|| Fee); END;
/
If you execute the above code, you can see the following output:
Enter value for age: 44
old 2: Age NUMBER : = &age ; new 2: Age NUMBER : = 44 ; Fee for Member is:20
PL/SQL procedure successfully completed.
The above code does not permit a person to be the library’s member if his age is below five. However, if his age is above five, then membership fee is charged based
on a set of nested IF conditions. If the age is between 5 and 13, then the fee amount is 10. If the age is between 14
and 20, then the fee amount is 15. If the age is between 21 and 50, then the fee amount is 20. If the age is above 50, then the fee amount is 5.
Example of IF – THEN – ELSIF – END IF Statements
The above code was quite cumbersome due to large number of nested IF statements. Instead you can use the ELSIF clause to make the code easier to read.
DECLARE
Age NUMBER := &age ; Fee Number;
BEGIN
IF Age < 5 THEN
DBMS_OUTPUT.PUT_LINE (‘Membership denied: Age below
permissible membership age’);
ELSIF Age BETWEEN 5 AND 13 THEN Fee := 10;
ELSIF Age BETWEEN 14 AND 20 THEN Fee := 15;
ELSIF Age BETWEEN 21 AND 50 THEN Fee := 20;
ELSIF Age > 50 THEN Fee := 5; END IF;
DBMS_OUTPUT.PUT_LINE (‘Fee for Member is:’|| Fee); END;
/
On executing the above code, you get the following result:
Enter value for age: 55
old 2: Age NUMBER : = &age ; new 2: Age NUMBER : = 55 ; Fee for Member is:5
PL/SQL procedure successfully completed.
After having used all the variations of the IF constructs, there are certain guidelines to remember while writing them in a program. They are:
Every IF is followed by a THEN after stating the condition. Every IF must have a matching END IF.
An IF construct may have any number of ELSIF statements. END IF is not one word but two words and ELSIF is one word. Every IF construct can have only one ELSE statement.
The ELSIF keyword does not have a matching END IF.
Do not use a semicolon (;) on the lines with the IF…THEN, ELSIF, and ELSE keywords. END IF must be followed by a semicolon (;).
You could indent the SQL and PL/SQL statements to be executed to increase readability of the code.
CASE Expressions and CASE Statements
In PL/SQL, two types of CASE constructs are supported:CASE expressions: The expressions select a result and return it. CASE expressions can be assigned to variables, can be part of SQL statements and can be used in logical expressions. The syntax begins with CASE and ends with END.
CASE statements: They are independent statements just like other PL/SQL statements such as IF…THEN…ELSE statements. The syntax begins with CASE and ends with END CASE.
CASE Expressions
CASE expressions select a result and return it. To understand better, look at the following syntax of a CASE expression:
CASE selector
WHEN expr1 THEN result1 WHEN expr2 THEN result2 …
WHEN exprn THEN resultn [ELSE resultn+1;]
END;
From the syntax, it becomes clear that, to select the result, the CASE expression uses a selector, which is an expression whose value is used to select one of the several alternatives provided. The selector is followed by one or more WHEN clauses. The value of the selector determines which clause is executed. This is the syntax of a simple CASE expression.
Execute the following code of a simple CASE expression:
DECLARE
vGrade CHAR(1):= ‘&grade’; vGradeWiseAge VARCHAR2(25); BEGIN
VGradeWiseAge := CASE vGrade
WHEN ‘A’ THEN ‘Age between 5 and 13’ WHEN ‘B’ THEN ‘Age between 14 and 20’ WHEN ‘C’ THEN ‘Age between 21 and 50’
WHEN ‘D’ THEN ‘Age above 50’ END;
DBMS_OUTPUT.PUT_LINE (‘Grade : ’||vGrade||‘ Stands for: ’||vGradeWiseAge);
END; /
In the code, the CASE expression is being assigned to a variable vGradeWiseAge. The CASE expression uses the value in the vGrade variable (The value is accepted
using a substitution variable) as the expression. Depending on the value entered by the user, the CASE expression assigns the value to variable vGradeWiseAge.
PL/SQL also supports a searched CASE expression. It has the following syntax:
CASE
WHEN searchCondition1 THEN result1 WHEN searchCondition2 THEN result2 …
WHEN searchConditionn THEN resultn [ELSE resultn+1]
END;
Searched CASE expressions have no selectors. The WHEN clauses contain search conditions, which return a Boolean value, and not expressions that can return values of any type. Each WHEN clause can have different conditional expressions, which can take the form of searchCondition = <variableExpr> operator <variableExpr>. The operator can be any comparison operator.
Execute following code of a searched CASE expression:
DECLARE
vAge NUMBER(3):= ‘&age’; vAgeWiseGrade CHAR(25); BEGIN
vAgeWiseGrade := CASE
WHEN vAge<5 THEN ‘Membership not permitted’
WHEN vAge BETWEEN 5 AND 13 THEN ‘A’ WHEN vAge BETWEEN 14 AND 20 THEN ‘B’ WHEN vAge BETWEEN 21 AND 50 THEN ‘C’ WHEN vAge BETWEEN 50 AND 100 THEN ‘D’
END;
DBMS_OUTPUT.PUT_LINE (‘For Age : ’||vAge||‘ The
corresponding grade is: ’||vAgeWiseGrade); END;
/
In the code, the searched CASE expression is assigned to a variable vAgeWiseGrade. The block accepts the age and returns the grade based on the CASE expression.
CASE Statements
PL/SQL supports simple as well as searched CASE statements. Their differences are as follows:
Simple CASE statements:
Evaluate a single variable or expression for multiple values Do not support comparison operators in the WHEN clause Searched CASE statements:
Evaluate multiple variables or expressions Each WHEN clause can:
• Evaluate different expressions • Have comparison operators
The following examples will help us in understanding the different ways in which the two different CASE statements function.
A simple CASE statement example:
DECLARE
vGrade CHAR(1):= ‘&grade’; vGradeWiseAge VARCHAR2(25); BEGIN
CASE vGrade
WHEN ‘A’ THEN vGradeWiseAge:=‘Age between 5 and 13’; WHEN ‘B’ THEN vGradeWiseAge:=‘Age between 14 and 20’; WHEN ‘C’ THEN vGradeWiseAge:=‘Age between 21 and 50’; WHEN ‘D’ THEN vGradeWiseAge:=‘Age above 50’;
END CASE;
DBMS_OUTPUT.PUT_LINE (‘Grade : ’||vGrade||‘ Stands for: ’||vGradeWiseAge);
END; /
In the code, you will notice that the CASE statement ends with an END CASE statement, and that every executable statement following the WHEN clause is terminated with a
semicolon. The above code accepts the value of vGrade and based on it assigns different values to vGradeWiseAge variable.
A searched CASE statement example:
DECLARE
vAge NUMBER(3):= ‘&age’; vAgeWiseGrade CHAR(2); BEGIN
CASE
WHEN vAge<5 THEN vAgeWiseGrade:=‘Membership not permitted’; WHEN vAge BETWEEN 5 AND 13 THEN vAgeWiseGrade:=‘A’;
WHEN vAge BETWEEN 14 AND 20 THEN vAgeWiseGrade:=‘B’; WHEN vAge BETWEEN 21 AND 50 THEN vAgeWiseGrade:=‘C’; WHEN vAge BETWEEN 50 AND 100 THEN vAgeWiseGrade:=‘D’; END CASE;
DBMS_OUTPUT.PUT_LINE (‘For Age : ’||vAge||‘ The
END; /
In the code, you will observe that the searched CASE statement is similar to the searched CASE expression except that the former cannot be assigned to variables.
NULLIF and COALESCE Expressions
NULLIF and COALESCE expressions are a form of shorthand for PL/SQL CASE expressions.
The NULLIF expression behaves like an inverse of the NVL function. The semantics can be written as follows:
CASE
WHEN expr1=expr2 THEN NULL; ELSE expr1;
END;
Execute the following PL/SQL code:
DECLARE
vDate DATE:= ‘&Date’; vReturnDt DATE;
BEGIN
IF vDate >= SYSDATE THEN
vReturnDt:= NULLIF (vDate, SYSDATE);
DBMS_OUTPUT.PUT_LINE (‘The member has to pay a fine amount of: ’|| (vReturnDt – SYSDATE) * 1.5); ELSE
DBMS_OUTPUT.PUT_LINE (‘No fines due’); END IF;
END; /
In the code, if the date entered by the user is before the SYSDATE, the ELSE condition is executed. However, in case the date entered is after the SYSDATE or equal to the SYSDATE, the NULLIF expression is evaluated. If the vDate variable
value is after SYSDATE, then the vDate value is returned to vReturnDt variable. If vDate variable value is same as SYSDATE, then a NULL value is returned.
The COALESCE expression behaves like the NVL function, but it can take a list of values. Its semantics can be written as follows:
Expression with two arguments:
CASE
WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2
END;
Expression with three or more arguments:
CASE
WHEN expr1 IS NOT NULL THEN expr1; ELSE COALESCE (expr2, expr3,…,exprn); END CASE;
LOOP Constructs
You might require performing a set of steps continuously till the transaction or task to be performed is complete.
Using loops will enable you to perform such kind of execution. The execution of the statements in a loop also depends on the result of a condition being true or false. As long as the condition is true, the statements in the loop will be executed. The condition to be satisfied is the starting point of the loop. Only when the condition is satisfied, the control of the block will enter the loop and will stay in the loop till the condition is reversed. An important point to remember is that the loop should provide for a condition to be checked for exiting from the loop.
The different looping constructs include: BASIC loop
FOR loop WHILE loop BASIC LOOP
This is the simplest form of a loop. The statements are enclosed between the keywords LOOP and END LOOP.
When the flow of execution encounters the END LOOP keyword, the control goes back to the corresponding LOOP statement above it. Hence, the statements in a basic loop will be executed at least once.
The loop should contain an EXIT statement. This statement helps to end the loop. If it is not specified, then the loop will be endless. An EXIT statement can also be specified as an action within an IF statement. The EXIT statement must be placed within the loop.
The syntax for a basic loop is:
LOOP
statements; . . .
EXIT [WHEN condition]; END LOOP;
Where,
LOOP is the beginning of the loop statements
statements are the SQL and/or PL/SQL statements to be executed. EXIT is the keyword to end the loop.
WHEN condition is the condition to be true to exit the loop.
END LOOP marks the end of the loop and takes the loop back to the beginning. An example of a basic loop to display the first ten even numbers is as follows:
DECLARE Counter NUMBER(2):= 0; BEGIN LOOP DBMS_OUTPUT.PUT_LINE(Counter); Counter:= Counter+2;
EXIT WHEN Counter= 20; END LOOP;
END; /
The output generated by the above code is:
Result: ———
0 2 4 6 8 10 12 14 16 18
PL/SQL procedure successfully completed.
The EXIT-WHEN statement replaces a simple IF statement. For example, compare the following statements.
IF counter=20 THEN | EXIT WHEN counter=20; EXIT;
END IF;
These statements are logically equivalent, but the EXIT-WHEN statement is easier to read and understand.
If you are checking the condition first and then executing the statements, EXIT statement should be placed immediately after LOOP statement in the above program
FOR LOOP
In a FOR LOOP a condition is placed in the beginning of the loop as a counter. The condition controls the number of times the statements is to be executed. The syntax is:
FOR counter in [REVERSE ]
lower_bound .. upper_bound LOOP statement1;
statement2; . . . END LOOP;
Where,
counter is an integer, which is implicitly declared and whose value increases (or decreases when used with the REVERSE keyword) by 1 until its value reaches either of the limits specified.
REVERSE enables the counter to decrement in intervals of 1. lower_bound is the lower limit for the range of the counter value. upper_bound is the upper limit for the range of the counter value.
The values for these limits can be strings, variables or expressions or numeric values. When the lower value is increased to a values higher or greater than the specified upper limit then the loop will not be executed.
Certain points to remember when using a FOR loop is that: The counter can be referenced only inside the loop.
When referring to the counter use an expression to refer to its existing value. Cannot assign a value to the counter.
Use a FOR loop instead of the basic loop to display the first ten even numbers.
DECLARE EvenNo NUMBER(2): = 0; BEGIN FOR I IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE(EvenNo); EvenNo: = EvenNo +2; END LOOP; END; /
This code generates the following output:
Result: ——— 0 2 4 6 8 ... ...
PL/SQL procedure successfully completed.
WHILE LOOP
In this loop construct, the statements are executed based on a condition at the beginning of the loop. The condition controls the end of the loop as well. In other words, the statements in the loop will be executed as long as the condition specified is true.
The condition is checked at the beginning of the LOOP and if the condition is FALSE, the loop is terminated and none of the statements are executed. The syntax is:
WHILE condition LOOP statement1; statement2; . . . END LOOP; Where,
condition is the expression that returns a boolean value of TRUE, FALSE or NULL statement is the set of SQL and PL/SQL statements to be executed.
In case the condition returns a NULL value then the loop is skipped and none of the statements are executed.
The same code regarding the display of even numbers can be done using the WHILE loop as follows: DECLARE EvenNo NUMBER(2):= 0; BEGIN WHILE EvenNo < 20 LOOP DBMS_OUTPUT.PUT_LINE(EvenNo); EvenNo := EvenNo+2; END LOOP; END; /
The result when you execute the code is similar to that of the previous one.
GOTO Statement
The GOTO statement branches to a label unconditionally. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block. In the following example, you move to an executable statement further down in a sequence of statements: BEGIN ... GOTO insert_row; ... <<insert_row>>
INSERT INTO emp VALUES ... END;
/
In the next example, you move to a PL/SQL block further up in a sequence of statements: DECLARE x NUMBER := 0; BEGIN <<increment_x>> BEGIN x := x + 1;