0% found this document useful (0 votes)
5 views55 pages

DBMS Lab Manual

The document outlines the objectives and requirements for a Database Management Systems lab, focusing on database design, query, and PL/SQL. It provides a comprehensive list of programming tasks, including table creation, data manipulation, and advanced PL/SQL programming techniques. Additionally, it explains various SQL commands and concepts such as DDL, DML, DCL, and TCL, along with examples of queries and operations.
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
5 views55 pages

DBMS Lab Manual

The document outlines the objectives and requirements for a Database Management Systems lab, focusing on database design, query, and PL/SQL. It provides a comprehensive list of programming tasks, including table creation, data manipulation, and advanced PL/SQL programming techniques. Additionally, it explains various SQL commands and concepts such as DDL, DML, DCL, and TCL, along with examples of queries and operations.
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 55

Database Management Systems Lab

Objectives: · To teach the student database design and query and PL/SQL.
System/Software Requirements:
· Intel based desktop PC
· Mysql /Oracle latest version Recommended
PROGRAMS LIST

1) Creation, altering and droping of tables and inserting rows into a table (use constraints while
creating tables) examples using SELECT command.
2) Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, NOTEXISTS, UNION,
INTERSET, Constraints.
Example:- Select the roll number and name of the student who secured fourth rank in the class.
3) Queries using Aggregate functions (COUNT, SUM, AVG, MAX and
MIN), GROUP BY, HAVING and Creation and dropping of Views.
4) Queries using Conversion functions (to_char, to_number and to_date), string functions
(Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr), date
functions (Sysdate, next_day, add_months, last_day, months_between, least, greatest, trunc,
round,
to_char, to_date)
5) i)Creation of simple PL/SQL program which includes declaration section, executable section
and
exception –Handling section (Ex. Student marks can be selected from the table and printed for
those who secured first class and an exception can be raised if no records were found)
ii)Insert data into student table and use COMMIT, ROLLBACK and SAVEPOINT in PL/SQL
block.
6) Develop a program that includes the features NESTED IF, CASE and CASE expression. The
program can be extended using the NULLIF and COALESCE functions.
7) Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops using
ERROR
Handling, BUILT –IN Exceptions, USE defined Exceptions, RAISE- APPLICATION ERROR.
8) Programs development using creation of procedures, passing parameters IN and OUT of
PROCEDURES.
9) Program development using creation of stored functions, invoke functions in SQL Statements
and
write complex functions.
10) Program development using creation of package specification, package bodies, private
objects,
package variables and cursors and calling stored packages.
11) Develop programs using features parameters in a CURSOR, FOR UPDATE CURSOR,
WHERE
CURRENT of clause and CURSOR variables.
12) Develop Programs using BEFORE and AFTER Triggers, Row and Statement Triggers and
INSTEAD OF Triggers

1
1) Creation, altering and droping of tables and inserting rows into a table (use
constraints while creating tables) examples using SELECT command.

DDL
Data Definition Language (DDL) statements are used to define the database structure or
schema. Some examples:
o CREATE - to create objects in the database
o ALTER - alters the structure of the database
o DROP - delete objects from the database
o TRUNCATE - remove all records from a table, including all spaces allocated for the
records are removed
o COMMENT - add comments to the data dictionary
o RENAME - rename an object
DML
Data Manipulation Language (DML) statements are used for managing data within
schema objects. Some examples:
o SELECT - retrieve data from the a database
o INSERT - insert data into a table
o UPDATE - updates existing data within a table
o DELETE - deletes all records from a table, the space for the records remain
o MERGE - UPSERT operation (insert or update)
o CALL - call a PL/SQL or Java subprogram
o EXPLAIN PLAN - explain access path to data
o LOCK TABLE - control concurrency
DCL
Data Control Language (DCL) statements. Some examples:
o GRANT - gives user's access privileges to database
o REVOKE - withdraw access privileges given with the GRANT command
TCL
Transaction Control (TCL) statements are used to manage the changes made by DML
statements. It allows statements to be grouped together into logical transactions.
o COMMIT - save work done
o SAVEPOINT - identify a point in a transaction to which you can later roll back
o ROLLBACK - restore database to original since the last COMMIT
o SET TRANSACTION - Change transaction options like isolation level and what
rollback segment to use
DML Commands - Select - Insert - Update - Delete
INSERTION

DELETION

UPDATION

1. CREATE:
(a)CREATE TABLE: This is used to create a new relation and the corresponding

2
Syntax: CREATE TABLE relation_name
(field_1 data_type(Size),field_2 data_type(Size), .. . );

Example:
SQL>CREATE TABLE Student (sno NUMBER(3),sname CHAR(10),class CHAR(5));
Example:
SQL> CREATE TABLE Emp1 ( EmpNo Number(10)PRIMARY KEY,
EName VarChar2(15), Job Char(10),deptname varchar2(10),deptno number(9),Hiredate
Date, salary number(8),exp number(5)));

RESULT: Table created.

(b)CREATE TABLE..AS SELECT....: This is used to create the structure of a new relation
from the structure of an existing relation.
Syntax: CREATE TABLE (relation_name_1, field_1,field_2,.....field_n) AS SELECT
field_1,field_2,...........field_n FROM relation_name_2;
Example: SQL>CREATE TABLE std(rno,sname) AS SELECT sno,sname FROM student;

** DESC: It is used to describe a schema as well as to retrieve rows from table in


descending order.
SYNTAX: DESC
EX: SQL> DESC EMP1;
NAME NULL? TYPE
----------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(10)
ENAME VARCHAR2(15)
JOB CHAR(10)
DEPTNAME VARCHAR2(10)
DEPTNO NUMBER(9)
HIREDATE DATE
SALARY NUMBER(8)
EXP NUMBER(5)
2. ALTER:
(a)ALTER TABLE ...ADD...: This is used to add some extra fields into existing relation.
Syntax: ALTER TABLE relation_name ADD(new field_1 data_type(size), new field_2
data_type(size),..);
Example : SQL>ALTER TABLE emp1 ADD(Address CHAR(10));
TABLE ALTERED.
/**TO CHECK WHETHER COLUMN ADD OR NOT CHECK USING DESC COMMAND**/
SQL> DESC EMP1;
NAME NULL? TYPE
----------------------------------------- -------- ---------------
EMPNO NOT NULL NUMBER (10)
ENAME VARCHAR2 (15)
JOB CHAR (10)
3
DEPTNAME VARCHAR2 (10)
DEPTNO NUMBER (9)
HIREDATE DATE
SALARY NUMBER (8)
EXP NUMBER (5)
ADDRESS CHAR (10)
(b)ALTER TABLE...MODIFY...: This is used to change the width as well as data type of
fields of existing relations.
Syntax: ALTER TABLE relation_name MODIFY (field_1 newdata_type(Size), field_2
newdata_type(Size),....field_newdata_type(Size));
Example:SQL>ALTER TABLE emp1 MODIFY(ename VARCHAR2(20),salary NUMBER(5));
TABLE ALTERED.

SQL> DESC EMP1;


NAME NULL? TYPE
----------------------------------------- -------- -----------------
EMPNO NOT NULL NUMBER(10)
ENAME VARCHAR2(20)
JOB CHAR(10)
DEPTNAME VARCHAR2(10)
DEPTNO NUMBER(9)
HIREDATE DATE
SALARY NUMBER(5)
EXP NUMBER(5)
ADDRESS CHAR(10)
3. DROP TABLE: This is used to delete the structure of a relation. It permanently deletes
the records in the table.
Syntax: DROP TABLE relation_name;
Example: SQL>DROP TABLE EMP1;
Table dropped;
SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
EMPLOYEE1 TABLE
SALGRADE TABLE
5 ROWS SELECTED.
4. RENAME: It is used to modify the name of the existing database object.
Syntax: RENAME TABLE old_relation_name TO new_relation_name;
Example: SQL>RENAME TABLE EMP1 TO EMP2;
Table renamed.
5. TRUNCATE: This command will remove the data permanently. But structure will not be
removed.
4
Syntax: TRUNCATE TABLE <Table name>
Example TRUNCATE TABLE EMP1;
Difference between Truncate & Delete:-
 By using truncate command data will be removed permanently & will not get back
where as by using delete command data will be removed temporally & get back by
using roll back command.
 By using delete command data will be removed based on the condition where as by
using truncate command there is no condition.
 Truncate is a DDL command & delete is a DML command.

Viewing Data in the Table (Select Command)

Once data has been inserted into a table, the next most logical operation would be to view
what has been inserted. The SELECT SQL verb is used to achieve this.

All Rows and All Columns

Syntax: SELECT * FROM Table_name;

eg: Select * from Student; It will show all the table records.

SELECT First_name, DOB FROM STUDENT WHERE Reg_no = 'S101'; Cover it by single
inverted comma if its datatype is varchar or char.

This Command will show one row. because you have given condition for only one row and
particular records. If condition which has given in WHERE Clause is true then records will
be fetched otherwise it will show no records selected.

Eliminating Duplicates:

A table could hold duplicate rows. In such a case, you can eliminate duplicates.

Syntax: SELECT DISTINCT col, col, .., FROM table_name;

eg : SELECT DISTINCT * FROM Student;

or : SELECT DISTINCT first_name, city, pincode FROM Student;

It scans through entire rows, and eliminates rows that have exactly the same contents in
each column.

Sorting DATA:

The Rows retrieved from the table will be sorted in either Ascending or Descending order
depending on the condition specified in select statement, the Keyword has used ORDER

5
BY.

SELECT * FROM Student


ORDER BY First_Name;

it will in show records as alphabetical order from A to Z ascending order.If you want
Descending order means Z to A then used DESC Keyword at last.

eg : SELECT first_name, city,pincode FROM Student


ORDER BY First_name DESC;
Insert Command
More than one are there to Insert data into a table
eg: INSERT INTO student (reg_no, first_name, last_name, dob,address, pincode)
VALUES('A101', 'Mohd', 'Imran', '01-MAR-89','Allahabad', 211001);
eg : INSERT INTO student VALUES('A101', 'Mohd', 'Imran', '01-MAR-
89','Allahabad', 211001);
Note : Character expression placed within the insert into statement must be enclosed in
single quotes (').
Inserting data into a table from another table:
In addition to inserting data one row at a time into a table, it is quite possible to populate a
table with data that already exist in another table. You can store same record in a table
that already stored in another table.
Eg : suppose you want to insert data from course table to university table then use this
example:
INSERT INTO university SELECT course_id, course_name FROM course;
Data will be inserted into university table automatically, what will be in course table.
you can give condition also in WHERE clause.
Delete Command
DELETE Operation:

The DELETE command can remove all the rows from the table or a set of rows from the
table.

eg: DELETE FROM student; It will DELETE all the rows from student table.

eg: DELETE FROM student WHERE reg_no='A101'; If condition will be satisfied then it
will delete a row from the table Register number A101 will be deleted from the table

If you want to delete table column then use ALTER TABLE command.

eg : ALTER TABLE student DROP COLUMN dob; The DOB column will be deleted from
the student table.

Update Command

6
UPDATE Operation:

The UPDATE command is used to change or modify data values in a table and UPDATE
command can Update all the rows from the table or a set of rows from the table.

eg : UPDATE Student SET course='MCA';

Course is a column name, suppose ant time you want to update something like that in the
student table course should be MCA for all students then you can use this type of query. It
will update all the rows in the table all rows will have MCA course.

Now, if you want update particular row then see below.

UPDATE Student SET course='MCA' where reg_no='A101'; it will update only one row
that will have Register no. A101.

you can use different-different types of condition in WHERE clause, eg salary updation, if
salary has increased someone's then simply multiply, addition you can do in salary column.

I will show example. Suppose someone's has increased salary by 10% then what should to
do. See example:

UPDATE employee SET salary= salary+salary*0.1 WHERE employee_id='E101';


Salary will be automatically increased by 10% of salary.

7
2) Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, NOTEXISTS, UNION,
INTERSET, Constraints.
Example:- Select the roll number and name of the student who secured fourth rank in the
class.
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- ---------------------
4000 Jackson Joe www.techonthenet.com
5000 Smith Jane www.digminecraft.com
6000 Ferguson Samantha www.bigactivities.com
7000 Reynolds Allen www.checkyourmath.com
8000 Anderson Paige
9000 Johnson Derek www.techonthenet.com
And a table called orders with the following data:
ORDER_ID CUSTOMER_ID
-------- -----------
1 5000
2 6000
3 7000
4 9000
Now let's find all of the records from the customers table where there is at least one record in the
orders table with the same customer_id. Enter the following SELECT statement:
SELECT *
FROM customers
WHERE EXISTS (SELECT *
FROM orders
WHERE customers.customer_id = orders.customer_id);
These are the results that you should see:
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- ---------------------
5000 Smith Jane www.digminecraft.com
6000 Ferguson Samantha www.bigactivities.com
7000 Reynolds Allen www.checkyourmath.com
9000 Johnson Derek www.techonthenet.com

SELECT *
FROM customers
WHERE NOT EXISTS (SELECT *
FROM orders
WHERE customers.customer_id = orders.customer_id);
These are the results that you should see:
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- ---------------------
4000 Jackson Joe www.techonthenet.com
8000 Anderson Paige

The general ANY syntax is:


1. SELECT column-names
2. FROM table-name
3. WHERE column-name operator ANY
4. (SELECT column-name
5. FROM table-name
6. WHERE condition)

The general ALL syntax is:

8
1. SELECT column-names
2. FROM table-name
3. WHERE column-name operator ALL
4. (SELECT column-name
5. FROM table-name
6. WHERE condition)
SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns.
The columns must also have similar data types. Also, the columns in each SELECT statement
must be in the same order.
SQL UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Note: The UNION operator selects only distinct values by default. To allow duplicate values,
use the ALL keyword with UNION.
SQL UNION ALL Syntax
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
PS: The column names in the result-set of a UNION are usually equal to the column names in
the first SELECT statement in the UNION.

Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
Ana Trujillo
Avda. de la México
2 Emparedados y Ana Trujillo 05021 Mexico
Constitución 2222 D.F.
helados
Antonio Moreno Antonio México
3 Mataderos 2312 05023 Mexico
Taquería Moreno D.F.

SQL UNION Example


The following SQL statement selects all the different cities (only distinct values) from the
"Customers" and the "Suppliers" tables:
Example
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

9
3) Queries using Aggregate functions (COUNT, SUM, AVG, MAX and
MIN), GROUP BY, HAVING and Creation and dropping of Views.

Aggregative operators: In addition to simply retrieving data, we often want to perform


some computation or summarization. SQL allows the use of arithmetic expressions. We
now consider a powerful class of constructs for computing aggregate values such as MIN
and SUM.
1. Count: COUNT following by a column name returns the count of tuple in that column. If
DISTINCT keyword is used then it will return only the count of unique tuple in the column.
Otherwise, it will return count of all the tuples (including duplicates) count (*) indicates all
the tuples of the column.
Syntax: COUNT (Column name)
Example: SELECT COUNT (Sal) FROM emp1;
2. SUM: SUM followed by a column name returns the sum of all the values in that column.
Syntax: SUM (Column name)
Example: SELECT SUM (Sal) From emp1;
3. AVG: AVG followed by a column name returns the average value of that column values.
Syntax: AVG (n1,n2..)
Example: Select AVG(10, 15, 30) FROM DUAL;
4. MAX: MAX followed by a column name returns the maximum value of that column.
Syntax: MAX (Column name)
Example: SELECT MAX (Sal) FROM emp;
SQL> select deptno,max(sal) from emp group by deptno;
DEPTNO MAX(SAL)
------ --------
10 5000
20 3000
30 2850

SQL> select deptno,max(sal) from emp group by deptno having max(sal)<3000;


DEPTNO MAX(SAL)
----- --------
30 2850
5. MIN: MIN followed by column name returns the minimum value of that column.
Syntax: MIN (Column name)
Example: SELECT MIN (Sal) FROM emp;
SQL>select deptno,min(sal) from emp group by deptno having min(sal)>1000;
DEPTNO MIN(SAL)
----- --------
10 1300

VIEW:
The SQL VIEW is, in essence, a virtual table that does not physically exist. Rather, it is
created by a SQL statement that joins one or more tables.
Views, which are kind of virtual tables, allow users to do the following:
 Structure data in a way that users or classes of users find natural or intuitive.

10
 Restrict access to the data such that a user can see and (sometimes) modify exactly
what they need and no more.
 Summarize data from various tables which can be used to generate reports.
Creating Views:
Database views are created using the CREATE VIEW statement. Views can be created from
a single table, multiple tables, or another view.
To create a view, a user must have the appropriate system privilege according to the
specific implementation.
The basic CREATE VIEW syntax is as follows:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
You can include multiple tables in your SELECT statement in very similar way as you use
them in normal SQL SELECT query.
SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM CUSTOMERS;
Now, you can query CUSTOMERS_VIEW in similar way as you query an actual table.
Following is the example:
SQL > SELECT * FROM CUSTOMERS_VIEW;
This would produce the following result:

+----------+-----+
| name | age |
+----------+-----+
| Ramesh | 32 |
| Khilan | 25 |
| kaushik | 23 |
| Chaitali | 25 |
| Hardik | 27 |
| Komal | 22 |
| Muffy | 24 |
+----------+-----+
The WITH CHECK OPTION:
The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH
CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the
view definition.
If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.
The following is an example of creating same view CUSTOMERS_VIEW with the WITH
CHECK OPTION:
CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;
11
The WITH CHECK OPTION in this case should deny the entry of any NULL values in the
view's AGE column, because the view is defined by data that does not have a NULL value in
the AGE column.
Updating a View:
A view can be updated under certain conditions:
 The SELECT clause may not contain the keyword DISTINCT.
 The SELECT clause may not contain summary functions.
 The SELECT clause may not contain set functions.
 The SELECT clause may not contain set operators.
 The SELECT clause may not contain an ORDER BY clause.
 The FROM clause may not contain multiple tables.
 The WHERE clause may not contain subqueries.
 The query may not contain GROUP BY or HAVING.
 Calculated columns may not be updated.
 All NOT NULL columns from the base table must be included in the view in order for
the INSERT query to function.
So if a view satisfies all the above-mentioned rules then you can update a view. Following is
an example to update the age of Ramesh:
SQL > UPDATE CUSTOMERS_VIEW
SET AGE = 35
WHERE name='Ramesh';
This would ultimately update the base table CUSTOMERS and same would reflect in the
view itself. Now, try to query base table, and SELECT statement would produce the
following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Inserting Rows into a View:
Rows of data can be inserted into a view. The same rules that apply to the UPDATE
command also apply to the INSERT command.
Here we can not insert rows in CUSTOMERS_VIEW because we have not included all the
NOT NULL columns in this view, otherwise you can insert rows in a view in similar way as
you insert them in a table.
Deleting Rows into a View:
Rows of data can be deleted from a view. The same rules that apply to the UPDATE and
INSERT commands apply to the DELETE command.
Following is an example to delete a record having AGE= 22.
SQL > DELETE FROM CUSTOMERS_VIEW
12
WHERE age = 22;
This would ultimately delete a row from the base table CUSTOMERS and same would
reflect in the view itself. Now, try to query base table, and SELECT statement would
produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Dropping Views:
Obviously, where you have a view, you need a way to drop the view if it is no longer
needed. The syntax is very simple as given below:
DROP VIEW view_name;
Following is an example to drop CUSTOMERS_VIEW from CUSTOMERS table:
DROP VIEW CUSTOMERS_VIEW;
SQL> select * from stud;
ID NAME PHONENO CITY SAL
---------- ----- ---------- ----- ----------
1 adam 123456 vja 2000
2 aman 27356 hyd 2000
3 eve 56789 gunt 3000
4 henry 987654 vja 5000
5 singh 787654 hyd 7000
6 sunny 6000
7 gunt 8000
9 navy hyd 9000
8 rows selected.
SQL> create view info 1as select id,name,city,sal from stud where name is not null;
View created.
SQL> select * from info1;
ID NAME CITY SAL
---------- ----- ----- ----------
1 adam vja 2000
2 aman hyd 2000
3 eve gunt 3000
4 henry vja 5000
5 singh hyd 7000
6 sunny 6000
9 navy hyd 9000
7 rows selected.
SQL> select * from stud;
13
ID NAME PHONENO CITY SAL
---------- ----- ---------- ----- ----------
1 adam 123456 vja 2000
2 aman 27356 hyd 2000
3 eve 56789 gunt 3000
4 henry 987654 vja 5000
5 singh 787654 hyd 7000
6 sunny 6000
7 gunt 8000
9 navy hyd 9000
8 rows selected.
SQL> select distinct name from info;
NAME
-----
eve
navy
singh
sunny
aman
henry
adam
7 rows selected.
SQL> create view info1 as select distinct city from stud;
View created.
SQL> select * from info1;
CITY
----
hyd
gunt
vja
SQL> insert into info1 values('gunt');
insert into info1 values('gunt')
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
SQL> create view info1 as select min(sal) "sal" from stud;
View created.
SQL> select * from info1;
Sal
----------
2000
SQL> insert into info1 values(3000);
insert into info1 values(3000)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
14
4) Queries using Conversion functions (to_char, to_number and to_date), string
functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length,
substr and instr), date functions (Sysdate, next_day, add_months, last_day,
months_between, least, greatest, trunc, round, to_char, to_date)
1. Conversion functions:
To_char: TO_CHAR (number) converts n to a value of VARCHAR2 data type, using the
optional number format fmt. The value n can be of type NUMBER, BINARY_FLOAT, or
BINARY_DOUBLE.
SQL>select to_char(65,'RN')from dual;
LXV
To_number : TO_NUMBER converts expr to a value of NUMBER data type.
SQL> Select to_number('1234.64') from Dual;
1234.64
To_date: TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type
to a value of DATE data type.
SQL>SELECT TO_DATE('January 15, 1989, 11:00 A.M.')FROM DUAL;
TO_DATE('
---------
15-JAN-89
2. String functions:
Concat: CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any
of the datatypes
SQL>SELECT CONCAT(‘ORACLE’,’CORPORATION’)FROM DUAL;
ORACLECORPORATION
Lpad: LPAD returns expr1, left-padded to length n characters with the sequence of
characters in expr2.
SQL>SELECT LPAD(‘ORACLE’,15,’*’)FROM DUAL;
*********ORACLE
Rpad: RPAD returns expr1, right-padded to length n characters with expr2, replicated as
many times as necessary.
SQL>SELECT RPAD (‘ORACLE’,15,’*’)FROM DUAL;
ORACLE*********
Ltrim: Returns a character expression after removing leading blanks.
SQL>SELECT LTRIM(‘SSMITHSS’,’S’)FROM DUAL;
MITHSS
Rtrim: Returns a character string after truncating all trailing blanks
SQL>SELECT RTRIM(‘SSMITHSS’,’S’)FROM DUAL;
SSMITH
Lower: Returns a character expression after converting uppercase character data to
lowercase.
SQL>SELECT LOWER(‘DBMS’)FROM DUAL;
dbms
Upper: Returns a character expression with lowercase character data converted to
uppercase
SQL>SELECT UPPER(‘dbms’)FROM DUAL;
15
DBMS
Length: Returns the number of characters, rather than the number of bytes, of the given
string expression, excluding trailing blanks.
SQL>SELECT LENGTH(‘DATABASE’)FROM DUAL; 8
Substr: Returns part of a character, binary, text, or image expression.
SQL>SELECT SUBSTR(‘ABCDEFGHIJ’3,4)FROM DUAL;
CDEF
Instr: The INSTR functions search string for substring. The function returns an integer
indicating the position of the character in string that is the first character of this
occurrence.
SQL>SELECT INSTR('CORPORATE FLOOR','OR',3,2)FROM DUAL;
14
3. Date functions:
Sysdate:
SQL>SELECT SYSDATE FROM DUAL;
29-DEC-08
next_day:
SQL>SELECT NEXT_DAY(SYSDATE,’WED’)FROM DUAL;
05-JAN-09
add_months:
SQL>SELECT ADD_MONTHS(SYSDATE,2)FROM DUAL;
28-FEB-09
last_day:
SQL>SELECT LAST_DAY(SYSDATE)FROM DUAL;
31-DEC-08
months_between:
SQL>SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE)FROM EMP;
4
Least:
SQL>SELECT LEAST('10-JAN-07','12-OCT-07')FROM DUAL;
10-JAN-07
Greatest:
SQL>SELECT GREATEST('10-JAN-07','12-OCT-07')FROM DUAL;
10-JAN-07
Trunc:
SQL>SELECT TRUNC(SYSDATE,'DAY')FROM DUAL;
28-DEC-08
Round:
SQL>SELECT ROUND(SYSDATE,'DAY')FROM DUAL;
28-DEC-08
to_char:
SQL> select to_char(sysdate, "dd\mm\yy") from dual;
24-mar-05.
to_date:
SQL> select to_date(sysdate, "dd\mm\yy") from dual;
24-mar-o5.
16
5) i)Creation of simple PL/SQL program which includes declaration section,
executable section and exception –Handling section (Ex. Student marks can be
selected from the table and printed for those who secured first class and an
exception can be raised if no records were found)
ii)Insert data into student table and use COMMIT, ROLLBACK and SAVEPOINT in
PL/SQL block.
(i)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.
Following are notable facts about PL/SQL:
 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.
 Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM
DB2.
PL/SQL Scalar Data Types and Subtypes
PL/SQL Scalar Data Types and Subtypes come under the following categories:

Description
Date Type

Numeric Numeric values on which arithmetic operations are performed.

Character Alphanumeric values that represent single characters or strings of


characters.

Boolean Logical values on which logical operations are performed.

Datetime Dates and times.


PL/SQL - Basic Syntax
PL/SQL is a block-structured language, meaning that PL/SQL programs are divided and
written in logical blocks of code. Each block consists of three sub-parts:
S.N. Sections & Description
Declarations
1 This section starts with the keyword DECLARE. It is an optional section and defines all
variables, cursors, subprograms, and other elements to be used in the program.
Executable Commands
This section is enclosed between the keywords BEGIN and END and it is a mandatory
2 section. It consists of the executable PL/SQL statements of the program. It should have
at least one executable line of code, which may be just a NULL command to indicate
that nothing should be executed.

17
Exception Handling
3 This section starts with the keyword EXCEPTION. This section is again optional and
contains exception(s) that handle errors in the program.
Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested within
other PL/SQL blocks using BEGIN and END. Here is the basic structure of a PL/SQL block:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
The end; line signals the end of the PL/SQL block. To run the code from SQL command line,
you may need to type / at the beginning of the first blank line after the last line of the code.
When the above code is executed at SQL prompt, it produces following result:
Program 1:

DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
Output: Hello,world
syntax for Exception Handling
The General Syntax for exception handling is as follows. Here, you can list down as many as
exceptions you want to handle. The default exception will be handled using WHEN others
THEN:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........
WHEN others THEN
exception3-handling-statements
END;
Exception Name Reason Error

18
Number
CURSOR_ALREADY_OPEN When you open a cursor that is already open. ORA-
06511
INVALID_CURSOR When you perform an invalid operation on a ORA-
cursor like closing a cursor, fetch data from a 01001
cursor that is not opened.
NO_DATA_FOUND When a SELECT...INTO clause does not return any ORA-
row from a table. 01403
TOO_MANY_ROWS When you SELECT or fetch more than one row ORA-
into a record or variable. 01422
ZERO_DIVIDE When you attempt to divide a number by zero. ORA-
01476

Program 2 : Retrieving a value from a table and printing the value.


DECLARE
a NUMBER;
BEGIN
SELECT sal INTO a FROM stud where id=1;
dbms_output.put_line(a);
END;
/
Output: 2000
Pl/sql procedure successfully completed.
Program 3:Addition of two numbers
DECLARE
a integer := 10;
b integer := 20;
c integer;
f real;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;
/
Output:
When the above code is executed, it produces the following result:
Value of c: 30
Value of f: 23.333333333333333333
PL/SQL procedure successfully completed.
Program 4:
DECLARE
a number;
b number;

19
begin
SELECT sal into a FROM stud WHERE id=9;
dbms_output.put_line(a);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such student!');
END;
/
PL/SQL
Transaction Control:
There are following commands used to control transactions:
 COMMIT: to save the changes.
 ROLLBACK: to rollback the changes.
 SAVEPOINT: creates points within groups of transactions in which to ROLLBACK
 SET TRANSACTION: Places a name on a transaction.
Transactional control commands are only used with the DML commands INSERT, UPDATE
and DELETE only. They can not be used while creating tables or dropping them because
these operations are automatically commited in the database.
The COMMIT Command:
The COMMIT command is the transactional command used to save changes invoked by a
transaction to the database.
The COMMIT command saves all transactions to the database since the last COMMIT or
ROLLBACK command.
The syntax for COMMIT command is as follows:
COMMIT;
Example:
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is the example which would delete records from the table having age = 25 and
then COMMIT the changes in the database.
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> COMMIT;
As a result, two rows from the table would be deleted and SELECT statement would
produce the following result:
+----+----------+-----+-----------+----------+
20
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
The ROLLBACK Command:
The ROLLBACK command is the transactional command used to undo transactions that
have not already been saved to the database.
The ROLLBACK command can only be used to undo transactions since the last COMMIT or
ROLLBACK command was issued.
The syntax for ROLLBACK command is as follows:
ROLLBACK;
Example:
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is the example, which would delete records from the table having age = 25 and
then ROLLBACK the changes in the database.
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> ROLLBACK;
As a result, delete operation would not impact the table and SELECT statement would
produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
21
The SAVEPOINT Command:
A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain
point without rolling back the entire transaction.
The syntax for SAVEPOINT command is as follows:
SAVEPOINT SAVEPOINT_NAME;
This command serves only in the creation of a SAVEPOINT among transactional statements.
The ROLLBACK command is used to undo a group of transactions.
The syntax for rolling back to a SAVEPOINT is as follows:
ROLLBACK TO SAVEPOINT_NAME;
Following is an example where you plan to delete the three different records from the
CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can
ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original
state:
Example:
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Now, here is the series of operations:
SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.
Now that the three deletions have taken place, say you have changed your mind and
decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was
created after the first deletion, the last two deletions are undone:
SQL> ROLLBACK TO SP2;
Rollback complete.
Notice that only the first deletion took place since you rolled back to SP2:
SQL> SELECT * FROM CUSTOMERS;
22
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.

23
6) Develop a program that includes the features NESTED IF, CASE and CASE
expression. The program can be extended using the NULLIF and COALESCE functions.
IF-THEN-ELSE Statement
A sequence of IF-THEN statements can be followed by an optional sequence of ELSE
statements, which execute when the condition is FALSE.
Syntax:
Syntax for the IF-THEN-ELSE statement is:
IF condition THEN
S1;
ELSE
S2;
END IF;

Example:
declare
a number:=10;
b number:=20;
begin
if a>b then
dbms_output.put_line('a is greater');
else
dbms_output.put_line('b is greater');
end if;
end;
/
IF-THEN-ELSIF Statement
The IF-THEN-ELSIF statement allows you to choose between several alternatives. An IF-
THEN statement can be followed by an optional ELSIF...ELSE statement. The ELSIF clause
lets you add additional conditions.
When using IF-THEN-ELSIF statements there are few points to keep in mind.
 It's ELSIF, not ELSEIF
 An IF-THEN statement can have zero or one ELSE's and it must come after any
ELSIF's.
 An IF-THEN statement can have zero to many ELSIF's and they must come before
the ELSE.
 Once an ELSIF succeeds, none of the remaining ELSIF's or ELSE's will be tested.
Syntax:
The syntax of an IF-THEN-ELSIF Statement in PL/SQL programming language is:
IF(boolean_expression 1)THEN
S1; -- Executes when the boolean expression 1 is true
ELSIF( boolean_expression 2) THEN
S2; -- Executes when the boolean expression 2 is true
ELSIF( boolean_expression 3) THEN
S3; -- Executes when the boolean expression 3 is true
ELSE
S4; -- executes when the none of the above condition is true
24
END IF;

Example:

declare
a number:=10;
b number:=20;
begin
if a>b then
dbms_output.put_line('a is greater than b');
elsif a<b then
dbms_output.put_line('b is greater than a');
else
dbms_output.put_line('a is equal to b');
end if;
end;
/
Nested IF-THEN-ELSE Statements
It is always legal in PL/SQL programming to nest IF-ELSE statements, which means you can
use one IF or ELSE IF statement inside another IF or ELSE IF statement(s).
Syntax:
IF( boolean_expression 1)THEN
-- executes when the boolean expression 1 is true
IF(boolean_expression 2) THEN
-- executes when the boolean expression 2 is true
sequence-of-statements;
END IF;
ELSE
-- executes when the boolean expression 1 is not true
else-statements;
END IF;
Example:

declare
a number:=60;
b number:=50;
c number:=70;
begin
if a>b then
if a>c then
dbms_output.put_line('a is greater');
else
dbms_output.put_line('c is greater');
end if;
else
if b>c then
25
dbms_output.put_line('b is greater');
else
dbms_output.put_line('c is greater');
end if;
end if;
end;
/
CASE Statement
Like the IF statement, the CASE statement selects one sequence of statements to execute.
However, to select the sequence, the CASE statement uses a selector rather than multiple
Boolean expressions. A selector is an expression, whose value is used to select one of
several alternatives.
Syntax:
The syntax for case statement in PL/SQL is:
CASE selector
WHEN 'value1' THEN S1;
WHEN 'value2' THEN S2;
WHEN 'value3' THEN S3;
...
ELSE Sn; -- default case
END CASE;

Example:
DECLARE
grade char(1) := 'A';
BEGIN
CASE grade
when 'A' then dbms_output.put_line('Excellent');
when 'B' then dbms_output.put_line('Very good');
when 'C' then dbms_output.put_line('Well done');
when 'D' then dbms_output.put_line('Passed');
when 'F' then dbms_output.put_line('Failed');
else dbms_output.put_line('No such Grade');
END CASE;
END;
/
COALESCE Function
The SQL Server (Transact-SQL) COALESCE function returns the first non-null expression in
the list. If all expressions evaluate to null, then the COALESCE function will return null.
Syntax
COALESCE( expression1, expression2, ... expression_n )
Example:
SELECT COALESCE(NULL, NULL, 'hello', NULL, 'welcome');
Result: 'hello'

SELECT COALESCE(NULL, 'TechOnTheNet.com', 'CheckYourMath.com');


26
Result: 'welcome'

SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4);


Result: 1
For examples, say we have the following table,
Table Contact_Info
Name Business_Phone Cell_Phone Home_Phone
Jeff 531-2531 622-7813 565-9901
Laura NULL 772-5588 312-4088
Peter NULL NULL 594-7477
1. If a person has a business phone, use the business phone number.
2. If a person does not have a business phone and has a cell phone, use the cell phone
number.
3. If a person does not have a business phone, does not have a cell phone, and has a home
phone, use the home phone number.
We can use the COALESCE function to achieve our goal:
SELECT Name, COALESCE (Business_Phone, Cell_Phone, Home_Phone) Contact_Phone
FROM Contact_Info;
Result:
Name Contact_Phone
Jeff 531-2531
Laura 772-5588
Peter 594-7477
NULLIF FUNCTION
The SQL Server (Transact-SQL) NULLIF function compares expression1 and expression2. If
expression1 and expression2 are equal, the NULLIF function returns NULL. Otherwise, it
returns the first expression which is expression1.
SYNTAX
NULLIF( expression1, expression2 )
EXAMPLE
1.SELECT NULLIF('welcome', 'welcome') from dual;
Result: NULL (returns NULL because values are the same)

2.SELECT NULLIF('hello', 'welcome') from dual;


Result: 'hello' (returns first value because values are different)

3.SELECT NULLIF(12, 12) from dual;


Result: NULL (returns NULL because values are the same)
4.SELECT NULLIF(12, 45) from dual;
Result: 12 (returns first value because values are different)
For example, let's say we have a table that tracks actual sales and sales goal as below:
Table Sales_Data
Store_Name Actual Goal

27
Store A 50 50
Store B 40 50
Store C 25 30
We want to show NULL if actual sales is equal to sales goal, and show actual sales if the two
are different. To do this, we issue the following SQL statement:
SELECT Store_Name, NULLIF (Actual, Goal) FROM Sales_Data;
Result:
Store_Name NULLIF (Actual, Goal)
Store A NULL
Store B 40
Store C 25

28
7) Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops
using ERROR Handling, BUILT –IN Exceptions, USE defined Exceptions, RAISE-
APPLICATION ERROR.
WHILE LOOP:
A WHILE LOOP statement in PL/SQL programming language repeatedly executes a target
statement as long as a given condition is true.
Syntax:
WHILE condition LOOP
sequence_of_statements
END LOOP;
Example:
DECLARE
a number(2) := 10;
BEGIN
WHILE a < 20 LOOP
dbms_output.put_line('value of a: ' || a);
a := a + 1;
END LOOP;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
value of a: 10
value of a: 11
value of a: 12
value of a: 13
value of a: 14
value of a: 15
value of a: 16
value of a: 17
value of a: 18
value of a: 19

PL/SQL procedure successfully completed.

DECLARE
i number(3):=1;
sum1 number(4):=0;
BEGIN
while i<11 loop
sum1:=sum1+i;
i:=i+1;
END loop;
dbms_output.put_line('sum is' ||sum1);
END;
/
FOR LOOP:
29
A FOR LOOP is a repetition control structure that allows you to efficiently write a loop that
needs to execute a specific number of times.
Syntax:
FOR counter IN initial_value .. final_value LOOP
sequence_of_statements;
END LOOP;
Here is the flow of control in a for loop:
 The initial step is executed first, and only once. This step allows you to declare and
initialize any loop control variables.
 Next, the condition, i.e., initial_value .. final_value is evaluated. If it is TRUE, the body
of the loop is executed. If it is FALSE, the body of the loop does not execute and flow
of control jumps to the next statement just after the for loop.
 After the body of the for loop executes, the value of the counter variable is increased
or decreased.
 The condition is now evaluated again. If it is TRUE, the loop executes and the
process repeats itself (body of loop, then increment step, and then again condition).
After the condition becomes FALSE, the FOR-LOOP terminates.
Following are some special characteristics of PL/SQL for loop:
 The initial_value and final_value of the loop variable or counter can be literals,
variables, or expressions but must evaluate to numbers. Otherwise, PL/SQL raises
the predefined exception VALUE_ERROR.
 The initial_value need not to be 1; however, the loop counter increment (or
decrement) must be 1.
 PL/SQL allows determine the loop range dynamically at run time.
Example:
DECLARE
a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
value of a: 10
value of a: 11
value of a: 12
value of a: 13
value of a: 14
value of a: 15
value of a: 16
value of a: 17
value of a: 18
value of a: 19
value of a: 20

30
PL/SQL procedure successfully completed.
Example:sum of 10 numbers
DECLARE
i number(3);
j number(3);
sum1 number(4):=0;
BEGIN
FOR i IN 1..10 LOOP
sum1:=sum1+i;
END loop;
dbms_output.put_line('sum is' ||sum1);
END;
/
Reverse FOR LOOP Statement
By default, iteration proceeds from the initial value to the final value, generally upward
from the lower bound to the higher bound. You can reverse this order by using the
REVERSE keyword. In such case, iteration proceeds the other way. After each iteration, the
loop counter is decremented.
However, you must write the range bounds in ascending (not descending) order. The
following program illustrates this:
DECLARE
a number(2) ;
BEGIN
FOR a IN REVERSE 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
value of a: 20
value of a: 19
value of a: 18
value of a: 17
value of a: 16
value of a: 15
value of a: 14
value of a: 13
value of a: 12
value of a: 11
value of a: 10

PL/SQL procedure successfully completed.


Labeling a PL/SQL Loop
PL/SQL loops can be labeled. The label should be enclosed by double angle brackets (<<
and >>) and appear at the beginning of the LOOP statement. The label name can also

31
appear at the end of the LOOP statement. You may use the label in the EXIT statement to
exit from the loop.
The following program illustrates the concept:
DECLARE
i number(1);
j number(1);
BEGIN
<< outer_loop >>
FOR i IN 1..3 LOOP
<< inner_loop >>
FOR j IN 1..3 LOOP
dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
END loop inner_loop;
END loop outer_loop;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
i is: 1 and j is: 1
i is: 1 and j is: 2
i is: 1 and j is: 3
i is: 2 and j is: 1
i is: 2 and j is: 2
i is: 2 and j is: 3
i is: 3 and j is: 1
i is: 3 and j is: 2
i is: 3 and j is: 3

PL/SQL procedure successfully completed.

Basic loop:
DECLARE n NUMBER := 0;
BEGIN
LOOP
n:= n + 1;
DBMS_OUTPUT.PUT_LINE(n);
IF n = 5 THEN
EXIT;
END IF;
END LOOP;
END;
/
a) Named System Exceptions
System exceptions are automatically raised by Oracle, when a program violates a RDBMS
rule. There are some system exceptions which are raised frequently, so they are pre-
defined and given a name in Oracle which are known as Named System Exceptions.
For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.
32
Named system exceptions are:
1) Not Declared explicitly,
2) Raised implicitly when a predefined Oracle error occurs,
3) caught by referencing the standard name within an exception-handling routine.
Exception Name Reason Error
Number
CURSOR_ALREADY_OPEN When you open a cursor that is already open. ORA-
06511
INVALID_CURSOR When you perform an invalid operation on a ORA-
cursor like closing a cursor, fetch data from a 01001
cursor that is not opened.
NO_DATA_FOUND When a SELECT...INTO clause does not return any ORA-
row from a table. 01403
TOO_MANY_ROWS When you SELECT or fetch more than one row ORA-
into a record or variable. 01422
ZERO_DIVIDE When you attempt to divide a number by zero. ORA-
01476
For Example: Suppose a NO_DATA_FOUND exception is raised in a proc, we can write a
code to handle the exception as given below.
BEGIN
Execution section
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('A SELECT...INTO did not return any row.');
END;

User-defined Exceptions
Apart from sytem exceptions we can explicity define exceptions based on business rules.
These are known as user-defined exceptions.
Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception
section.
Example:
DECLARE
notfound EXCEPTION;
a number;
BEGIN
SELECT sal into a from stud where id=2;
IF a < 10000 THEN
RAISE notfound;
else
Dbms_output.put_line (a);
END IF;

33
EXCEPTION
WHEN notfound THEN
Dbms_output.put_line ('less amount');
END;
/

RAISE_APPLICATION_ERROR ( )
RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the
user-defined error messages along with the error number whose range is in between -
20000 and -20999.
Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous
transactions which are not committed within the PL/SQL Block are rolled back
automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).
RAISE_APPLICATION_ERROR raises an exception but does not handle it.
RAISE_APPLICATION_ERROR is used for the following reasons,
a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.
The General Syntax to use this procedure is:
RAISE_APPLICATION_ERROR (error_number, error_message);

• The Error number must be between -20000 and -20999


• The Error_message is the message you want to display when the error occurs.
Steps to be folowed to use RAISE_APPLICATION_ERROR procedure:
1. Declare a user-defined exception in the declaration section.
2. Raise the user-defined exception based on a specific business rule in the execution
section.
3. Finally, catch the exception and link the exception to a user-defined error number in
RAISE_APPLICATION_ERROR.
Example:
DECLARE
notfound EXCEPTION;
a number;
BEGIN
SELECT sal into a from stud where id=2;
IF a < 10000 THEN
RAISE notfound;
else
Dbms_output.put_line (a);
END IF;
EXCEPTION
WHEN notfound THEN
raise_application_error(-20009, 'The salary amount is less');
END;
/
Output:
DECLARE
34
*
ERROR at line 1:
ORA-20009: The salary amount is less
ORA-06512: at line 13

35
8) Programs development using creation of procedures, passing parameters IN and
OUT of PROCEDURES.
PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of
parameters. PL/SQL provides two kinds of subprograms:
 Functions: these subprograms return a single value, mainly used to compute and
return a value.
 Procedures: these subprograms do not return a value directly, mainly used to
perform an action.
 Parts of a PL/SQL Subprogram
 Each PL/SQL subprogram has a name, and may have a parameter list. Like
anonymous PL/SQL blocks and, the named blocks a subprograms will also have
following three parts:
S.N. Parts & Description
Declarative Part
It is an optional part. However, the declarative part for a subprogram does not start
1 with the DECLARE keyword. It contains declarations of types, cursors, constants,
variables, exceptions, and nested subprograms. These items are local to the
subprogram and cease to exist when the subprogram completes execution.
Executable Part
2
This is a mandatory part and contains statements that perform the designated action.
Exception-handling
3
This is again an optional part. It contains the code that handles run-time errors.
Creating a Procedure
A procedure is created with the CREATE PROCEDURE statement. The simplified syntax
for the CREATE PROCEDURE statement is as follows:
CREATE PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;

Example:
The following example creates a simple procedure that displays the string 'Hello World!' on
the screen when executed.
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/
When above code is executed using SQL prompt, it will produce the following result:
Procedure created.

Example 2:creating procedure for addition of two numbers


36
create procedure abc as
a number;
b number;
c number;
begin
a:=&a;
b:=&b;
c:=a+b;
dbms_output.put_line(c);
end abc;
/
Executing a Standalone Procedure
A standalone procedure can be called in two ways:
 Using the EXECUTE keyword
 Calling the name of the procedure from a PL/SQL block
The above procedure named 'greetings' can be called with the EXECUTE keyword as:
EXECUTE greetings;
The above call would display:
Hello World

PL/SQL procedure successfully completed.


The procedure can also be called from another PL/SQL block:
BEGIN
greetings;
END;
/
The above call would display:
Hello World

PL/SQL procedure successfully completed.


Deleting a Standalone Procedure
A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax for
deleting a procedure is:
DROP PROCEDURE procedure-name;
So you can drop greetings procedure by using the following statement:
DROP PROCEDURE greetings;
Parameter Modes in PL/SQL Subprograms
S.N. Parameter Mode & Description
IN
An IN parameter lets you pass a value to the subprogram. It is a read-only
parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be
1
assigned a value. You can pass a constant, literal, initialized variable, or expression as
an IN parameter. You can also initialize it to a default value; however, in that case, it is
omitted from the subprogram call. It is the default mode of parameter passing.

37
Parameters are passed by reference.

OUT
An OUT parameter returns a value to the calling program. Inside the subprogram, an
2 OUT parameter acts like a variable. You can change its value and reference the value
after assigning it. The actual parameter must be variable and it is passed by value.

IN OUT
An IN OUT parameter passes an initial value to a subprogram and returns an updated
value to the caller. It can be assigned a value and its value can be read.
2
The actual parameter corresponding to an IN OUT formal parameter must be a
variable, not a constant or an expression. Formal parameter must be assigned a value.
Actual parameter is passed by value.
IN & OUT Mode Example 1
This program finds the minimum of two values, here procedure takes two numbers using
IN mode and returns their minimum using OUT parameters.
DECLARE
a number;
b number;
c number;

PROCEDURE findMin(x IN number, y IN number, z OUT number) IS


BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;

BEGIN
a:= 20;
b:= 40;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (20, 40) : ' || c);
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Minimum of (20, 40) : 20

PL/SQL procedure successfully completed.


IN & OUT Mode Example 2
This procedure computes the square of value of a passed value. This example shows how
we can use same parameter to accept a value and then return another result.
DECLARE

38
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 25;
squareNum(a);
dbms_output.put_line(' Square of (25): ' || a);
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Square of (25): 625

PL/SQL procedure successfully completed.

39
9) Program development using creation of stored functions, invoke functions in SQL
Statements and write complex functions.
Functions:
A PL/SQL function is same as a procedure except that it returns a value.
Creating a Function
A standalone function is created using the CREATE FUNCTION statement. The simplified
syntax for the CREATE OR REPLACE PROCEDURE statement is as follows:
CREATE FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
AS}
BEGIN
< function_body >
END [function_name];
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;

RETURN total;
END;
/
When above code is executed using SQL prompt, it will produce the following result:
Function created.
Calling a Function
While creating a function, you give a definition of what the function has to do. To use a
function, you will have to call that function to perform the defined task. When a program
calls a function, program control is transferred to the called function.
A called function performs defined task and when its return statement is executed or when
it last end statement is reached, it returns program control back to the main program.
To call a function you simply need to pass the required parameters along with function
name and if function returns a value then you can store returned value. Following program
calls the function totalCustomers from an anonymous block:
DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Total no. of Customers: 6
PL/SQL procedure successfully completed.
Example :
40
CREATE FUNCTION fun1(no in number)
RETURN varchar2
IS
a varchar2(20);
BEGIN
select name into a from stud where id = 2;
return a;
END;
/
This program call the above define function with pass employee number and get that
employee name.
DECLARE
no number :=&no;
name varchar2(20);
BEGIN
name := fun1(no);
dbms_output.put_line('Name:'||' '||name);
end;
Example:
The following is one more example which demonstrates Declaring, Defining, and Invoking a
Simple PL/SQL Function that computes and returns the maximum of two values.
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(c);
END;

PL/SQL Recursive Functions

41
We have seen that a program or subprogram may call another subprogram. When a
subprogram calls itself, it is referred to as a recursive call and the process is known as
recursion.
To illustrate the concept, let us calculate the factorial of a number. Factorial of a number n
is defined as:
n! = n*(n-1)!
= n*(n-1)*(n-2)!
...
= n*(n-1)*(n-2)*(n-3)... 1
The following program calculates the factorial of a given number by calling itself
recursively:
DECLARE
num number;
factorial number;

FUNCTION fact(x number)


RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;

BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Factorial 6 is 720

PL/SQL procedure successfully completed.

42
10) Program development using creation of package specification, package bodies,
private objects,package variables and cursors and calling stored packages.
PL/SQL packages are schema objects that groups logically related PL/SQL types, variables
and subprograms.
A package will have two mandatory parts:
 Package specification
 Package body or definition
Package Specification
The specification is the interface to the package. It just DECLARES the types, variables,
constants, exceptions, cursors, and subprograms that can be referenced from outside the
package. In other words, it contains all information about the content of the package, but
excludes the code for the subprograms.
All objects placed in the specification are called public objects. Any subprogram not in the
package specification but coded in the package body is called a private object.
The following code snippet shows a package specification having a single procedure. You
can have many global variables defined and multiple procedures or functions inside a
package.
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/
When the above code is executed at SQL prompt, it produces the following result:
Package created.
Package Body
The package body has the codes for various methods declared in the package specification
and other private declarations, which are hidden from code outside the package.
The CREATE PACKAGE BODY Statement is used for creating the package body. The
following code snippet shows the package body declaration for the cust_sal package created
above. I assumed that we already have CUSTOMERS table created in our database as
mentioned in PL/SQL - Variables chapter.
CREATE OR REPLACE PACKAGE BODY cust_sal AS
PROCEDURE find_sal(c_id customers.id%TYPE) IS
c_sal customers.salary%TYPE;
BEGIN
SELECT salary INTO c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line('Salary: '|| c_sal);
END find_sal;
END cust_sal;
/
When the above code is executed at SQL prompt, it produces the following result:
Package body created.
Using the Package Elements
The package elements (variables, procedures or functions) are accessed with the following
syntax:
43
package_name.element_name;
Consider, we already have created above package in our database schema, the following
program uses the find_sal method of the cust_sal package:
DECLARE
code customers.id%type := &cc_id;
BEGIN
cust_sal.find_sal(code);
END;
/
When the above code is executed at SQL prompt, it prompt to enter customer ID and when
you enter an ID, it displays corresponding salary as follows:
Enter value for cc_id: 1
Salary: 3000

PL/SQL procedure successfully completed.


Example:
The following program provides a more complete package. We will use the CUSTOMERS
table stored in our database with the following records:
Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 3000.00 |
| 2 | Khilan | 25 | Delhi | 3000.00 |
| 3 | kaushik | 23 | Kota | 3000.00 |
| 4 | Chaitali | 25 | Mumbai | 7500.00 |
| 5 | Hardik | 27 | Bhopal | 9500.00 |
| 6 | Komal | 22 | MP | 5500.00 |
+----+----------+-----+-----------+----------+
THE PACKAGE SPECIFICATION:
CREATE OR REPLACE PACKAGE c_package AS
-- Adds a customer
PROCEDURE addCustomer(c_id customers.id%type,
c_name customers.name%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type);

-- Removes a customer
PROCEDURE delCustomer(c_id customers.id%TYPE);
--Lists all customers
PROCEDURE listCustomer;

END c_package;
/
44
When the above code is executed at SQL prompt, it creates the above package and displays
the following result:
Package created.
CREATING THE PACKAGE BODY:
CREATE OR REPLACE PACKAGE BODY c_package AS
PROCEDURE addCustomer(c_id customers.id%type,
c_name customers.name%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type)
IS
BEGIN
INSERT INTO customers (id,name,age,address,salary)
VALUES(c_id, c_name, c_age, c_addr, c_sal);
END addCustomer;

PROCEDURE delCustomer(c_id customers.id%type) IS


BEGIN
DELETE FROM customers
WHERE id = c_id;
END delCustomer;

PROCEDURE listCustomer IS
CURSOR c_customers is
SELECT name FROM customers;
TYPE c_list is TABLE OF customers.name%type;
name_list c_list := c_list();
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter +1;
name_list.extend;
name_list(counter) := n.name;
dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter));
END LOOP;
END listCustomer;
END c_package;
/
Above example makes use of nested table which we will discuss in the next chapter. When
the above code is executed at SQL prompt, it produces the following result:
Package body created.
USING THE PACKAGE:
The following program uses the methods declared and defined in the package c_package.
DECLARE
code customers.id%type:= 8;
BEGIN
45
c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500);
c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500);
c_package.listcustomer;
c_package.delcustomer(code);
c_package.listcustomer;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
Customer(8): Subham
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish

PL/SQL procedure successfully completed

46
11) Develop programs using features parameters in a CURSOR, FOR UPDATE
CURSOR, WHERE CURRENT of clause and CURSOR variables.

CURSORS:
Oracle creates a memory area, known as context area, for processing an SQL statement,
which contains all information needed for processing the statement, for example, number
of rows processed, etc.
A cursor is a pointer to this context area. PL/SQL controls the context area through a
cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows
the cursor holds is referred to as the active set.
You can name a cursor so that it could be referred to in a program to fetch and process the
rows returned by the SQL statement, one at a time. There are two types of cursors:
 Implicit cursors
 Explicit cursors
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is
executed, when there is no explicit cursor for the statement. Programmers cannot control
the implicit cursors and the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is
associated with this statement. For INSERT operations, the cursor holds the data that needs
to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that
would be affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which
always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The
SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS,
designed for use with the FORALL statement. The following table provides the description
of the most used attributes:
Attribute Description
Returns TRUE if an INSERT, UPDATE, or DELETE statement affected
%FOUND one or more rows or a SELECT INTO statement returned one or
more rows. Otherwise, it returns FALSE.
The logical opposite of %FOUND. It returns TRUE if an INSERT,
%NOTFOUND UPDATE, or DELETE statement affected no rows, or a SELECT INTO
statement returned no rows. Otherwise, it returns FALSE.
Always returns FALSE for implicit cursors, because Oracle closes
%ISOPEN the SQL cursor automatically after executing its associated SQL
statement.
Returns the number of rows affected by an INSERT, UPDATE, or
%ROWCOUNT
DELETE statement, or returned by a SELECT INTO statement.
Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the
example.
Example:
We will be using the CUSTOMERS table we had created and used in the previous chapters.
Select * from customers;

47
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000 |
| 2 | Khilan | 25 | Delhi | 1500 |
| 3 | kaushik | 23 | Kota | 2000 |
| 4 | Chaitali | 25 | Mumbai | 6500 |
| 5 | Hardik | 27 | Bhopal | 8500 |
| 6 | Komal | 22 | MP | 4500 |
+----+----------+-----+-----------+----------+
The following program would update the table and increase salary of each customer by 500
and use the SQL%ROWCOUNT attribute to determine the number of rows affected:
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
6 customers selected

PL/SQL procedure successfully completed.


If you check the records in customers table, you will find that the rows have been updated:
Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2500 |
| 2 | Khilan | 25 | Delhi | 2000 |
| 3 | kaushik | 23 | Kota | 2500 |
| 4 | Chaitali | 25 | Mumbai | 7000 |
| 5 | Hardik | 27 | Bhopal | 9000 |
| 6 | Komal | 22 | MP | 5000 |
Explicit Cursors
Explicit cursors are programmer defined cursors for gaining more control over the context
area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It
is created on a SELECT Statement which returns more than one row.
48
The syntax for creating an explicit cursor is :
CURSOR cursor_name IS select_statement;
Working with an explicit cursor involves four steps:
 Declaring the cursor for initializing in the memory
 Opening the cursor for allocating memory
 Fetching the cursor for retrieving data
 Closing the cursor to release allocated memory
Declaring the Cursor
Declaring the cursor defines the cursor with a name and the associated SELECT statement.
For example:
CURSOR c_customers IS
SELECT id, name, address FROM customers;
Opening the Cursor
Opening the cursor allocates memory for the cursor and makes it ready for fetching the
rows returned by the SQL statement into it. For example, we will open above-defined
cursor as follows:
OPEN c_customers;
Fetching the Cursor
Fetching the cursor involves accessing one row at a time. For example we will fetch rows
from the above-opened cursor as follows:
FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor


Closing the cursor means releasing the allocated memory. For example, we will close
above-opened cursor as follows:
CLOSE c_customers;
Example:
Following is a complete example to illustrate the concepts of explicit cursors:
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
1 Ramesh Ahmedabad
49
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.

Program 1: Write a PL/SQL program to read employee salary as input and adding Rs
500/- to salary and verify no of records are found or not found based on n value
using implicit cursors.
declare
n employ.sal%type;
begin
n:=&n;
update employ set sal=sal+500 where sal>n;
if sql%notfound then
dbms_output.put_line('Records not modified');
else
dbms_output.put_line(sql%rowcount ||'Records modified');
end if;
end;
SQL> /
Enter value for n: 5000
old 4: n:=&n;
new 4: n:=5000;
Records not modified
PL/SQL procedure successfully completed.
SQL> /
Enter value for n: 1000
old 4: n:=&n;
new 4: n:=1000;
2Records modified
PL/SQL procedure successfully completed.
SQL> select * from employ;
ID NAME SAL
---------- ---------- ----------
1 a 1000
2 b 3000
3 c 4000
Program 2:Write an explicit cursor program to count no of employees in each
department.
declare
id employ.id%type;
dno employ.deptno%type;
cursor c1 is select count(id),deptno from employ group by deptno;
50
begin
open c1;
dbms_output.put_line('eno'||' '||'deptno');
loop
fetch c1 into id,dno;
dbms_output.put_line(id || ' ' ||dno);
exit when c1%notfound;
end loop;
close c1;
end;
/
Output:
SQL> select * from employ;

ID NAME SAL DEPTNO


---------- ---------- ---------- ----------
1a 1000 1
2b 3000 2
3c 4000 1
4d 2000 2
5e 2000 3
6e 7000 4
6 rows selected.
SQL> /
eno deptno
2 1
2 2
1 4
1 3
Program 3:Write a cursor program to display first 5 records in emp table
declare
a employ%rowtype;
dno employ.deptno%type;
cursor c2 is select * from employ;
begin
open c2;
loop
fetch c2 into a;
dbms_output.put_line(a.id ||' '||a.name||' '||a.sal||' ' ||a.deptno);
exit when c2%rowcount=4;
end loop;
close c2;
end;
/
Output:
51
SQL> /
1 a 1000 1
2 b 3000 2
3 c 4000 1
4 d 2 000 2
PL/SQL procedure successfully completed.

12) Develop Programs using BEFORE and AFTER Triggers, Row and Statement
Triggers and INSTEAD OF Triggers
TRIGGERS:
Triggers are stored programs, which are automatically executed or fired when some events
occur. Triggers are, in fact, written to be executed in response to any of the following
events:
 A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
 A database definition (DDL) statement (CREATE, ALTER, or DROP).
 A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers could be defined on the table, view, schema, or database with which the event is
associated.
Benefits of Triggers
Triggers can be written for the following purposes:
 Generating some derived column values automatically
 Enforcing referential integrity
 Event logging and storing information on table access
 Auditing
 Synchronous replication of tables
 Imposing security authorizations
 Preventing invalid transactions
Creating Triggers
The syntax for creating a trigger is:
CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Where,

52
 CREATE TRIGGER trigger_name: Creates or replaces an existing trigger with the
trigger_name.
 {BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be
executed. The INSTEAD OF clause is used for creating trigger on a view.
 {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.
 [OF col_name]: This specifies the column name that would be updated.
 [ON table_name]: This specifies the name of the table associated with the trigger.
 [REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values
for various DML statements, like INSERT, UPDATE, and DELETE.
 [FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would be
executed for each row being affected. Otherwise the trigger will execute just once
when the SQL statement is executed, which is called a table level trigger.
 WHEN (condition): This provides a condition for rows for which the trigger would
fire. This clause is valid only for row level triggers.
 AFTER Triggers
AFTER triggers run the trigger action after the triggering statement is run.
 BEFORE statement trigger

Before executing the triggering statement, the trigger action is run.


Example:
To start with, we will be using the CUSTOMERS table we had created and used in the
previous chapters:
Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
The following program creates a row level trigger for the customers table that would fire
for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This
trigger will display the salary difference between the old values and new values:
Program 1: Multiple operations on a row
CREATE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
53
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Trigger created.
Here following two points are important and should be noted carefully:
 OLD and NEW references are not available for table level triggers, rather you can
use them for record level triggers.
 If you want to query the table in the same trigger, then you should use the AFTER
keyword, because triggers can query the table or change it again only after the
initial changes are applied and the table is back in a consistent state.
 Above trigger has been written in such a way that it will fire before any DELETE or
INSERT or UPDATE operation on the table, but you can write your trigger on a
single or multiple operations, for example BEFORE DELETE, which will fire
whenever a record will be deleted using DELETE operation on the table.
Triggering a Trigger
Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT
statement, which will create a new record in the table:
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );
When a record is created in CUSTOMERS table, above create trigger
display_salary_changes will be fired and it will display the following result:
Old salary:
New salary: 7500
Salary difference:
Because this is a new record so old salary is not available and above result is coming as
null. Now, let us perform one more DML operation on the CUSTOMERS table. Here is one
UPDATE statement, which will update an existing record in the table:
UPDATE customers
SET salary = salary + 500
WHERE id = 2;
When a record is updated in CUSTOMERS table, above create trigger
display_salary_changes will be fired and it will display the following result:
Old salary: 1500
New salary: 2000
Salary difference: 500

Program 2: Single operation on a column

CREATE trg2
BEFORE
INSERT ON stud
FOR EACH ROW
BEGIN
54
:new.name := upper(:new.name);
END;
/

Program 3: Creation of a Trigger using ‘Instead of’

Info table:

Id name salary
1 aaa 1000
2 bbb 2000
2 ccc 3000
3 ddd 4000
1 eee 5000

View:
Create view info_view as select distinct id from info;
View created;
Select * from info_view;

Id
1
2
3
create trigger trg1
INSTEAD OF UPDATE
on info_view
begin
update info
set id = :new.id
where id= :old.id;

end;
/

55

You might also like