SQL is a database language designed for the retrieval and management of data in a

relational database.

SQL is the standard language for database management. All the RDBMS systems like
MySQL, MS Access, Oracle, Sybase, Postgres, and SQL Server use SQL as their
standard database language.

Here are five types of widely used SQL queries.

● Data Definition Language (DDL)

● Data Manipulation Language (DML)
● Data Control Language(DCL)
● Transaction Control Language(TCL)
● Data Query Language (DQL)
 o SQL commands are instructions. It is used to communicate with the database. It
is also used to perform specific tasks, functions, and queries of data.
o SQL can perform various tasks like create a table, add data to tables, drop the
table, modify the table, set permission for users.

1. Data Definition Language (DDL)

o DDL changes the structure of the table like creating a table, deleting a table,
altering a table, etc.
o All the commands of DDL are auto-committed that means it permanently save all
the changes in the database.

Here are some commands that come under DDL:

o CREATE
o ALTER
o DROP
o TRUNCATE

a. CREATE It is used to create a new table in the database.

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Example:

CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);

b. DROP: It is used to delete both the structure and record stored in the table.

Syntax

DROP TABLE ;
Example

DROP TABLE EMPLOYEE;

c. ALTER: It is used to alter the structure of the database. This change could be either
to modify the characteristics of an existing attribute or probably to add a new attribute.

Syntax:

To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION....);

EXAMPLE

1. ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));

2. ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));
d. TRUNCATE: It is used to delete all the rows from the table and free the space
containing the table.

Syntax:
TRUNCATE TABLE table_name;
Example:

TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language
o DML commands are used to modify the database. It is responsible for all form of
changes in the database.
o The command of DML is not auto-committed that means it can't permanently
save all the changes in the database. They can be rollback.

Here are some commands that come under DML:

o INSERT
o UPDATE
o DELETE

a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row
of a table.

Syntax:

INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, value3, .
... valueN);
Or

INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);

For example:

INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");

b. UPDATE: This command is used to update or modify the value of a column in the
table.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE

CONDITION]
For example:

UPDATE students SET User_Name = 'Sonoo' WHERE Student_Id = '3'

c. DELETE: It is used to remove one or more row from a table.

Syntax:

DELETE FROM table_name [WHERE condition];

For example:

DELETE FROM javatpoint WHERE Author="Sonoo";

3. Data Control Language
DCL commands are used to grant and take back authority from any database user.

Here are some commands that come under DCL:

o Grant
o Revoke
a. Grant: It is used to give user access privileges to a database.

Example

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

b. Revoke: It is used to take back permissions from the user.

Example

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE
only.

These operations are automatically committed in the database that's why they cannot be
used while creating tables or dropping them.

Here are some commands that come under TCL:

o COMMIT
o ROLLBACK
o SAVEPOINT

a. Commit: Commit command is used to save all the transactions to the database.

Syntax:

COMMIT;
Example:

DELETE FROM CUSTOMERS WHERE AGE = 25;

COMMIT;
b. Rollback: Rollback command is used to undo transactions that have not already been
saved to the database.

Syntax:

ROLLBACK;
Example:

DELETE FROM CUSTOMERS WHERE AGE = 25; ROLLBACK;

c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling
back the entire transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

DQL is used to fetch the data from the database.

It uses only one command:

o SELECT

a. SELECT: This is the same as the projection operation of relational algebra. It is used
to select the attribute based on the condition described by WHERE clause.

Syntax:
SELECT expressions FROM TABLES WHERE conditions;
For example:

SELECT emp_name FROM employee WHERE age > 20;

SQL SELECT Statement

How do I get data from a database?

 The SELECT statement retrieves data from a database.

 The data is returned in a table-like structure called a result-set.

 SELECT is the most frequently used action on a database.

 SELECT QUERY is used to fetch the data from the MySQL database. Databases store data for

later retrieval.

 The purpose of MySQL Select is to return from the database tables, one or more rows that

match a given criteria.

 Select query can be used in scripting language like PHP, Ruby, or you can execute it via the

command prompt.

SQL SELECT statement syntax

It is the most frequently used SQL command and has the following general syntax

SELECT [DISTINCT|ALL ] { * | [fieldExpression [AS newName]} FROM tableName

[alias] [WHERE condition][GROUP BY fieldName(s)] [HAVING condition] ORDER BY
fieldName(s)

HERE

 SELECT is the SQL keyword that lets the database know that you want to retrieve data.
 [DISTINCT | ALL] are optional keywords that can be used to fine tune the results
returned from the SQL SELECT statement. If nothing is specified then ALL is assumed as
the default.
 {*| [fieldExpression [AS newName]} at least one part must be specified, "*" selected all
the fields from the specified table name, fieldExpression performs some computations
on the specified fields such as adding numbers or putting together two string fields into
one.
 FROM tableName is mandatory and must contain at least one table, multiple tables must
be separated using commas or joined using the JOIN keyword.
 WHERE condition is optional, it can be used to specify criteria in the result set returned
from the query.
 GROUP BY is used to put together records that have the same field values.
 HAVING condition is used to specify criteria when working using the GROUP BY
keyword.
 ORDER BY is used to specify the sort order of the result set.
  *

The Star symbol is used to select all the columns in table. An example of a simple
SELECT statement looks like the one shown below.

SELECT * FROM `members`;

The above statement selects all the fields from the members table. The semi-colon is a
statement terminate. It's not mandatory but is considered a good practice to end your
statements like that.

The Examples are performed on the following two tables

Getting members listing

Let's suppose that we want to get a list of all the registered library members from our
database, we would use the script shown below to do that.

SELECT * FROM `members`;

Executing the above script in MySQL produces the following results.

Our above query has returned all the rows and columns from the members table.

Let's say we are only interested in getting only the full_names, gender, physical_address
and email fields only. The following script would help us to achieve this.

SELECT `full_names`,`gender`,`physical_address`, `email` FROM `members`;

Executing the above script in MySQL produces the following results.

Getting movies listing

Remember in our above discussion that we mention expressions been used in SELECT
statements. Let's say we want to get a list of movie from our database. We want to have
the movie title and the name of the movie director in one field. The name of the movie
director should be in brackets. We also want to get the year that the movie was
released. The following script helps us do that.

SELECT Concat(`title`, ' (', `director`, ')') , `year_released` FROM `movies`;

HERE

 The Concat () MySQL function is used join the columns values together.
 The line "Concat (`title`, ' (', `director`, ')') gets the title, adds an opening bracket
followed by the name of the director then adds the closing bracket.

String portions are separated using commas in the Concat () function.

Executing the above script in MySQL workbench produces the following result set.

Alias field names

The above example returned the Concatenation code as the field name for our results.
Suppose we want to use a more descriptive field name in our result set. We would use
the column alias name to achieve that. The following is the basic syntax for the column
alias name

SELECT `column_name|value|expression` [AS] `alias_name`;

HERE

 "SELECT ` column_name|value|expression `" is the regular SELECT statement which

can be a column name, value or expression.
 "[AS]" is the optional keyword before the alias name that denotes the expression, value
or field name will be returned as.
 "`alias_name`" is the alias name that we want to return in our result set as the field
name.

The above query with a more meaningful column name

SELECT Concat(`title`, ' (', `director`, ')') AS 'Concat', `year_released` FROM

`movies`;

We get the following result

Getting members listing showing the year of birth

Suppose we want to get a list of all the members showing the membership number, full
names and year of birth, we can use the LEFT string function to extract the year of birth
from the date of birth field. The script shown below helps us to do that.

SELECT `membership_number`,`full_names`,LEFT(`date_of_birth`,4) AS
`year_of_birth` FROM members;
HERE

 "LEFT(`date_of_birth`,4)" the LEFT string function accepts the date of birth as the
parameter and only returns 4 characters from the left.
 "AS `year_of_birth`" is the column alias name that will be returned in our results. Note
the AS keyword is optional, you can leave it out and the query will still work.

Executing the above query in MySQL gives us the results shown below.

Introducing PL/SQL block structure

PL/SQL program units organize the code into blocks. A block without a name is known as an
anonymous block. The anonymous block is the simplest unit in PL/SQL. It is called anonymous block
because it is not saved in the Oracle database.

An anonymous block is an only one-time use and useful in certain situations such as creating test
units. The following illustrates anonymous block syntax:

[DECLARE]

Declaration statements;

BEGIN

Execution statements;

[EXCEPTION]

Exception handling statements;

END;

/
Let’s examine the PL/SQL block structure in greater detail.

PL/SQL Block Structure

The anonymous block has three basic sections that are the declaration, execution, and exception
handling. Only the execution section is mandatory and the others are optional.

 The declaration section allows you to define data types, structures, and variables. You often
declare variables in the declaration section by giving them names, data types, and initial
values.
 The execution section is required in a block structure and it must have at least one statement.
The execution section is the place where you put the execution code or business logic code.
You can use both procedural and SQL statements inside the execution section.
 The exception handling section is starting with the EXCEPTION keyword. The exception
section is the place that you put the code to handle exceptions. You can either catch or
handle exceptions in the exception section.

Notice that the single forward slash (/) is a signal to instruct SQL*Plus to execute the PL/SQL block.

PL/SQL block structure example

Let’s take a look at the simplest PL/SQL block that does nothing.

BEGIN

NULL;

END;

If you execute the above anonymous block in SQL*Plus you will see that it issues a message saying:

PL/SQL procedure successfully completed.

Because the NULL statement does nothing.

To display database’s output on the screen, you need to:

  First, use the SET SERVEROUTPUT ON command to instruct SQL*Plus to echo database’s
output after executing the PL/SQL block. The SET SERVEROUTPUT ON is SQL*Plus
command, which is not related to PL/SQL.
 Second, use the DBMS_OUTPUT.PUT_LINE procedure to output a string on the screen.

The following example displays a message Hello PL/SQL on a screen using SQL*Plus:

SET SERVEROUTPUT ON SIZE 1000000

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');

END;

In this example, you just use the execution part to execute code.

This particular block begins with the keyword PROCEDURE, and, like all blocks, ends with the
keyword END.


 Experiment-2.1

PL/SQL Control Structures are used to control flow of execution. PL/SQL provides different kinds of
statements to provide such type of procedural capabilities. These statements are almost same as that of
provided by other languages.

The flow of control statements can be classified into the following categories:

 Conditional Control
 Iterative Control
 Sequential Control

Conditional Control :

PL/SQL allows the use of an IF statement to control the execution of a block of code.

In PL/SQL, the IF -THEN - ELSIF - ELSE - END IF construct in code blocks allow specifying
certain conditions under which a specific block of code should be executed.

PL/SQL IF Statements
1. IF-THEN Statement
Syntax

IF condition
THEN
Statement;
END IF;

This syntax is used when user needs to execute statements when condition is true.

2. IF-THEN-ELSE Statement
Syntax

IF condition
THEN
[Statements to execute when condition is TRUE]
ELSE
[Statements to execute when condition is FALSE]
END IF;

This syntax is used to execute one set of statements when condition is TRUE or different set of
statements when condition is FALSE.

3. IF-THEN-ELSIF statement
Syntax

IF Condition1
THEN
Statements to execute when condition1 is TRUE
ELSIF condition2

THEN
Statements to execute when condition2 is TRUE

END IF;

This syntax is used to execute one set of statements when condition1 is TRUE or a different set of
statements when condition is FALSE.

4. IF-THEN-ELS-IF-ELSE Statement
Syntax

IF condition1
THEN
Statements to execute when condition1 is TRUE
ELSIF condition2
THEN
Statements to execute when condition2 is TRUE
ELSE
Statements to execute when both condition1 and condition2 are FALSE
END IF;

This syntax is used to execute one set of statements if condition1 is TRUE, a different set of
statements when condition2 is TRUE or a third set of statements when both condition1 and condition2
are false.

Lets take an example to understand the IF-THEN statement.

Example
DECLARE
a number(3) := 200;
BEGIN
-- check the boolean condition using if statement
IF( a < 10 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 10 ' );
ELSE
dbms_output.put_line('a is not less than 10 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;

Output:
a is not less than 10
value of a is : 200

PL/SQL Case statement

The PL/SQL CASE Statement provides facility to execute a sequence of statements based on a
selector. A selector may be variable, function or an expression.

Syntax
CASE [expression]
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…....................
WHEN condition_n THEN result_n
ELSE result
END;

Example
DECLARE
grade char(1) := 'C';
BEGIN
CASE grade
when 'A' then dbms_output.put_line('Distinction');
when 'B' then dbms_output.put_line('First class');
when 'C' then dbms_output.put_line('Second class');
when 'D' then dbms_output.put_line('Pass class');
else dbms_output.put_line('Failed');
END CASE;
END;
/

Output:
Second class

Reading Material Experiment 2.2

Iterative Control: LOOPING Statements
LOOP statements let you execute a sequence of statements multiple times. There are three forms
of LOOP statements: LOOP, WHILE-LOOP, and FOR-LOOP.

LOOP
The simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements
between the keywords LOOP and END LOOP, as follows:
LOOP
sequence_of_statements
END LOOP;
With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the
loop. If further processing is undesirable or impossible, you can use an EXIT statement to complete the loop.
You can place one or more EXIT statements anywhere inside a loop, but nowhere outside a loop. There are two
forms of EXIT statements: EXIT and EXIT-WHEN.

EXIT
The EXIT statement forces a loop to complete unconditionally. When an EXIT statement is encountered, the
loop completes immediately and control passes to the next statement. An example follows:
LOOP
...
IF credit_rating < 3 THEN
...
EXIT; -- exit loop immediately
END IF;
END LOOP;
-- control resumes here

The next example shows that you cannot use the EXIT statement to complete a PL/SQL block:
BEGIN
...
IF credit_rating < 3 THEN
...
EXIT; -- not allowed
END IF;
END;
Remember, the EXIT statement must be placed inside a loop. To complete a PL/SQL block before its normal
end is reached, you can use the RETURN statement.

EXIT-WHEN
The EXIT-WHEN statement lets a loop complete conditionally. When the EXIT statement is encountered, the
condition in the WHEN clause is evaluated. If the condition is true, the loop completes and control passes to the
next statement after the loop. An example follows:
LOOP
FETCH c1 INTO ...
EXIT WHEN c1%NOTFOUND; -- exit loop if condition is true
...
END LOOP;
CLOSE c1;
Until the condition is true, the loop cannot complete. So, a statement inside the loop must change the value of
the condition. In the last example, if the FETCH statement returns a row, the condition is false. When
the FETCH statement fails to return a row, the condition is true, the loop completes, and control passes to
the CLOSE statement.
The EXIT-WHEN statement replaces a simple IF statement. For example, compare the following statements:
IF count > 100 THEN | EXIT WHEN count > 100;
EXIT;
END IF;

These statements are logically equivalent, but the EXIT-WHEN statement is easier to read and understand.

Loop Labels
Like PL/SQL blocks, loops can be labeled. The label, an undeclared identifier enclosed by double angle
brackets, must appear at the beginning of the LOOP statement, as follows:
<<label_name>>
LOOP
sequence_of_statements
END LOOP;

Optionally, the label name can also appear at the end of the LOOP statement, as the following example shows:
<<my_loop>>
LOOP
...
END LOOP my_loop;
When you nest labeled loops, use ending label names to improve readability.
With either form of EXIT statement, you can complete not only the current loop, but any enclosing loop. Simply
label the enclosing loop that you want to complete. Then, use the label in an EXIT statement, as follows:
<<outer>>
LOOP
...
LOOP
...
EXIT outer WHEN ... -- exit both loops
END LOOP;
...
END LOOP outer;
Every enclosing loop up to and including the labeled loop is exited.

WHILE-LOOP
The WHILE-LOOP statement associates a condition with a sequence of statements enclosed by the
keywords LOOP and END LOOP, as follows:
WHILE condition LOOP
sequence_of_statements
END LOOP;
Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements
is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and
control passes to the next statement. An example follows:
WHILE total <= 25000 LOOP
...
SELECT sal INTO salary FROM emp WHERE ...
total := total + salary;
END LOOP;

The number of iterations depends on the condition and is unknown until the loop completes. The condition is
tested at the top of the loop, so the sequence might execute zero times. In the last example, if the initial value
of total is larger than 25000, the condition is false and the loop is bypassed.
Some languages have a LOOP UNTIL or REPEAT UNTIL structure, which tests the condition at the bottom of
the loop instead of at the top. Therefore, the sequence of statements is executed at least once. PL/SQL has no
such structure, but you can easily build one, as follows:
LOOP
sequence_of_statements
EXIT WHEN boolean_expression;
END LOOP;

To ensure that a WHILE loop executes at least once, use an initialized Boolean variable in the condition, as
follows:
done := FALSE;
WHILE NOT done LOOP
sequence_of_statements
done := boolean_expression;
END LOOP;

A statement inside the loop must assign a new value to the Boolean variable. Otherwise, you have an infinite
loop. For example, the following LOOP statements are logically equivalent:
WHILE TRUE LOOP | LOOP
... | ...
END LOOP; | END LOOP;
WHILE loop
The while loop executes commands in its body as long as the condtion remains true
Syntax :

WHILE < condition >

LOOP
< Action >
END LOOP

Example :
find reverse of given number using while loop
DECLARE
num Number(3) :=123;
ans Number(3) :=0;
i Number(3) :=0;
BEGIN
WHILE num != 0
LOOP
i:=mod(num,10);
ans:=(ans * 10 ) + i;
num:=floor(num/10);
END LOOP;
dbms_output.put_line('reverse of given number is: ' || ans);
END;
/
Output :
Run SQL Command Line
SQL>set serveroutput on

SQL>start d://rev.sql

reverse of given number is: 321

PL/SQL successfully completed.

FOR-LOOP
Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of
iterations through a FOR loop is known before the loop is entered. FOR loops iterate over a specified range of
integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP. A double
dot (..) serves as the range operator. The syntax follows:
FOR counter IN [REVERSE] lower_bound..higher_bound LOOP
sequence_of_statements
END LOOP;
The range is evaluated when the FOR loop is first entered and is never re-evaluated.
As the next example shows, the sequence of statements is executed once for each integer in the range. After
each iteration, the loop counter is incremented.
FOR i IN 1..3 LOOP -- assign the values 1,2,3 to i
sequence_of_statements -- executes three times
END LOOP;
The following example shows that if the lower bound equals the higher bound, the sequence of statements is
executed once:
FOR i IN 3..3 LOOP -- assign the value 3 to i
sequence_of_statements -- executes one time
END LOOP;
By default, iteration proceeds upward from the lower bound to the higher bound. However, as the example
below shows, if you use the keyword REVERSE, iteration proceeds downward from the higher bound to the
lower bound. After each iteration, the loop counter is decremented. Nevertheless, you write the range bounds in
ascending (not descending) order.
FOR i IN REVERSE 1..3 LOOP -- assign the values 3,2,1 to i
sequence_of_statements -- executes three times
END LOOP;
Inside a FOR loop, the loop counter can be referenced like a constant but cannot be assigned values, as the
following example shows:
FOR ctr IN 1..10 LOOP
IF NOT finished THEN
INSERT INTO ... VALUES (ctr, ...); -- legal
factor := ctr * 2; -- legal
ELSE
ctr := 10; -- not allowed
END IF;
END LOOP;
Using the FOR-LOOP Statement
Simple FOR loops iterate over a specified range of integers. The number of iterations is known before the loop
is entered. A double dot (..) serves as the range operator. The range is evaluated when the FOR loop is first
entered and is never re-evaluated. If the lower bound equals the higher bound, the loop body is executed once.
Example: Using a Simple FOR LOOP Statement
DECLARE
p NUMBER := 0;
BEGIN
FOR k IN 1..500 LOOP -- calculate pi with 500 terms
p := p + ( ( (-1) ** (k + 1) ) / ((2 * k) - 1) );
END LOOP;
p := 4 * p;
DBMS_OUTPUT.PUT_LINE( 'pi is approximately : ' || p ); -- print result
END;
/
 Experiment- 2.3

Introduction to PL/SQL Cursor

When you work with Oracle database, you work with a complete set of rows returned from an SELECT statement. However
the application in some cases cannot work effectively with the entire result set, therefore, the database server needs to
provide a mechanism for the application to work with one row or a subset of the result set at a time. As the result, Oracle
created PL/SQL cursor to provide these extensions.

A PL/SQL cursor is a pointer that points to the result set of an SQL query against database tables.

Working with PL/SQL Cursor

The following picture describes steps that you need to follow when you work with a PL/SQL cursor:

Fig 1: PL/SQL Cursor

Oracle has dedicated memory locations for executing SQL statements and then it holds that processed information, for
example, the total number of rows updated.

A cursor in PL/SQL gives a name and acts as a pointer to the area of work called a context area and then uses its
information. It keeps the number of rows processed by the SQL statement. These rows are called as an active set. The size of
the active set is equal to the count of the rows that meet the condition.
There are two types of cursors which are listed below:
1. Implicit Cursor
2. Explicit cursor

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 attributes such
as %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 −

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

2. %NOTFOUND
The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a
SELECT INTO statement returned no rows. Otherwise, it returns FALSE.

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

4. %ROWCOUNT
Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO
statement.

Any SQL cursor attribute will be accessed as sql%attribute_name

Example

We will be using the CUSTOMERS table

The following program will update the table and increase the 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 the 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 −
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.

The syntax for creating an explicit cursor is –

CURSOR cursor_name IS select_statement;

Working with an explicit cursor includes the following steps −
 Declaring the cursor for initializing the memory
 Opening the cursor for allocating the memory
 Fetching the cursor for retrieving the data
 Closing the cursor to release the 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 the memory for the cursor and makes it ready for fetching the rows returned by the SQL
statement into it. For example, we will open the 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 the above-opened cursor as follows −
CLOSE c_customers;

Example

Following is a complete example to illustrate the concepts of explicit cursors &minua;

DECLARE
c_id customers.id%type;
c_name customer.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 the SQL prompt, it produces the following result −
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.

Experiment-2.4

SQL View

An SQL View is a specific representation of data from one or more tables. The tables referred in the views are
known as Base tables. Creating a view does not take any storage space as only the query is stored in the data
dictionary and the actual data is not stored anywhere.
The maximum number of columns that can be defined in a SQL View are 1000 as in tables.

Benefits / Advantages of SQL Views

The reasons for using views (benefits / advantages of views) in applications can be many like;
 Reducing complexity.
 Improving security.
 Renaming the table columns.

Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the
database. We can create a view by selecting fields from one or more tables present in the database. A View
can either have all the rows of a table or specific rows based on certain condition.
In this article we will learn about creating , deleting and updating Views.
Sample Tables:
StudentDetails

StudentMarks
 CREATING VIEWS
We can create View using CREATE VIEW statement. A View can be created from a single table or multiple
tables.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

view_name: Name for the View

table_name: Name of the table
condition: Condition to select rows
Examples:

Creating View from a single table:

In this example we will create a View named DetailsView from the table StudentDetails.
Query:

CREATE VIEW DetailsView AS

SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;

To see the data in the View, we can query the view in the same manner as we query a table.

SELECT * FROM DetailsView;

Output:

In this example, we will create a view named StudentNames from the table StudentDetails.
Query:

CREATE VIEW StudentNames AS

SELECT S_ID, NAME
FROM StudentDetails
ORDER BY NAME;

If we now query the view as,

SELECT * FROM StudentNames;

Output:

Creating View from multiple tables: In this example we will create a View named MarksView from two
tables StudentDetails and StudentMarks. To create a View from multiple tables we can simply include
multiple tables in the SELECT statement.
Query:

CREATE VIEW MarksView AS

SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

To display data of View MarksView:

SELECT * FROM MarksView;

Output:

DELETING VIEWS
We have learned about creating a View, but what if a created View is not needed any more? Obviously we
will want to delete it. SQL allows us to delete an existing View. We can delete or drop a View using the
DROP statement.
Syntax:
DROP VIEW view_name;

view_name: Name of the View which we want to delete.

For example, if we want to delete the View MarksView, we can do this as:

DROP VIEW MarksView;

UPDATING VIEWS
There are certain conditions needed to be satisfied to update a view. If any one of these conditions is not met,
then we will not be allowed to update the view.
1. The SELECT statement which is used to create the view should not include GROUP BY clause
or ORDER BY clause.
2. The SELECT statement should not have the DISTINCT keyword.
3. The View should have all NOT NULL values.
4. The view should not be created using nested queries or complex queries.
5. The view should be created from a single table. If the view is created using multiple tables then
we will not be allowed to update the view.
We can use the CREATE OR REPLACE VIEW statement to add or remove fields from a view.
Syntax:

CREATE OR REPLACE VIEW view_name AS

SELECT column1,coulmn2,..
FROM table_name
WHERE condition;

For example, if we want to update the view MarksView and add the field AGE to this View
from StudentMarks Table, we can do this as:

CREATE OR REPLACE VIEW MarksView AS

SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS,
StudentMarks.AGE
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

If we fetch all the data from MarksView now as:

SELECT * FROM MarksView;

Output:

Inserting a row in a view:

We can insert a row in a View in a same way as we do in a table. We can use the INSERT INTO statement of
SQL to insert a row in a View.

Syntax:
INSERT INTO view_name(column1, column2 , column3,..)
VALUES(value1, value2, value3..);

view_name: Name of the View

Example:

In the below example we will insert a new row in the View DetailsView which we have created above in the
example of “creating views from a single table”.

INSERT INTO DetailsView(NAME, ADDRESS)

VALUES("Suresh","Gurgaon");

If we fetch all the data from DetailsView now as,

SELECT * FROM DetailsView;

 Output:

Deleting a row from a View:

Deleting rows from a view is also as simple as deleting rows from a table. We can use the DELETE statement
of SQL to delete rows from a view. Also deleting a row from a view first delete the row from the actual table
and the change is then reflected in the view.Syntax:

DELETE FROM view_name

WHERE condition;

view_name:Name of view from where we want to delete rows

condition: Condition to select rows
Example:
In this example we will delete the last row from the view DetailsView which we just added in the above
example of inserting rows.

DELETE FROM DetailsView

WHERE NAME="Suresh";

If we fetch all the data from DetailsView now as,

SELECT * FROM DetailsView;

Output:

WITH CHECK OPTION

The WITH CHECK OPTION clause in SQL is a very useful clause for views. It is applicable to a updatable
view. If the view is not updatable, then there is no meaning of including this clause in the CREATE VIEW
statement.
 The WITH CHECK OPTION clause is used to prevent the insertion of rows in the view where
the condition in the WHERE clause in CREATE VIEW statement is not satisfied.
 If we have used the WITH CHECK OPTION clause in the CREATE VIEW statement, and if the
UPDATE or INSERT clause does not satisfy the conditions then they will return an error.
Example:

In the below example we are creating a View SampleView from StudentDetails Table with WITH CHECK
OPTION clause.
CREATE VIEW SampleView AS
SELECT S_ID, NAME
FROM StudentDetails
WHERE NAME IS NOT NULL
WITH CHECK OPTION;

In this View if we now try to insert a new row with null value in the NAME column then it will give an error
because the view is created with the condition for NAME column as NOT NULL.
For example,though the View is updatable but then also the below query for this View is not valid:

INSERT INTO SampleView(S_ID)

VALUES(6);

NOTE: The default value of NAME column is null.

Uses of a View :
A good database should contain views due to the given reasons:
1. Restricting data access –
Views provide an additional level of table security by restricting access to a predetermined set of
rows and columns of a table.
2. Hiding data complexity –
A view can hide the complexity that exists in a multiple table join.
3. Simplify commands for the user –
Views allows the user to select information from multiple tables without requiring the users to
actually know how to perform a join.
4. Store complex queries –
Views can be used to store complex queries.
5. Rename Columns –
Views can also be used to rename the columns without affecting the base tables provided the
number of columns in view must match the number of columns specified in select statement.
Thus, renaming helps to to hide the names of the columns of the base tables.
6. Multiple view facility –
Different views can be created on the same table for different users.

References
 SQL | Views - GeeksforGeeks
 Oracle SQL & PL/SQL: SQL Views (sql-plsql.blogspot.com)
 PL/SQL Create View

Video References
 SQL tutorial 60: SQL View in Oracle Database By Manish Sharma RebellionRider - YouTube
 What is the view in oracle? simple view? complex view? - YouTube
Procedures:

Procedures and Functions are the subprograms which can be created and saved in the database as
database objects. They can be called or referred inside the other blocks also.

PL/SQL is a block-structured language that enables developers to combine the power of SQL with
procedural statements.

Terminologies:

Before we learn about PL/SQL subprograms, we will discuss the various terminologies that are the
part of these subprograms. Below are the terminologies that we are going to discuss.

Parameter:

The parameter is variable or placeholder of any valid PL/SQL datatype through which the PL/SQL
subprogram exchange the values with the main code. This parameter allows to give input to the
subprograms and to extract from these subprograms.

 These parameters should be defined along with the subprograms at the time of creation.
 These parameters are included n the calling statement of these subprograms to interact the
values with the subprograms.
 The datatype of the parameter in the subprogram and the calling statement should be same.
 The size of the datatype should not mention at the time of parameter declaration, as the size
is dynamic for this type.

Based on their purpose parameters are classified as

1. IN Parameter
2. OUT Parameter
3. IN OUT Parameter

IN Parameter:
 This parameter is used for giving input to the subprograms.
 It is a read-only variable inside the subprograms. Their values cannot be changed inside the
subprogram.
 In the calling statement, these parameters can be a variable or a literal value or an
expression, for example, it could be the arithmetic expression like '5*8' or 'a/b' where 'a' and
'b' are variables.
 By default, the parameters are of IN type.
OUT Parameter:
 This parameter is used for getting output from the subprograms.
 It is a read-write variable inside the subprograms. Their values can be changed inside the
subprograms.
 In the calling statement, these parameters should always be a variable to hold the value from
the current subprograms.
IN OUT Parameter:
 This parameter is used for both giving input and for getting output from the subprograms.
 It is a read-write variable inside the subprograms. Their values can be changed inside the
subprograms.
 In the calling statement, these parameters should always be a variable to hold the value from
the subprograms.

These parameter type should be mentioned at the time of creating the subprograms.
RETURN

RETURN is the keyword that instructs the compiler to switch the control from the subprogram to the
calling statement. In subprogram RETURN simply means that the control needs to exit from the
subprogram. Once the controller finds RETURN keyword in the subprogram, the code after this will be
skipped.

Normally, parent or main block will call the subprograms, and then the control will shift from those
parent block to the called subprograms. RETURN in the subprogram will return the control back to
their parent block. In the case of functions RETURN statement also returns the value. The datatype of
this value is always mentioned at the time of function declaration. The datatype can be of any valid
PL/SQL data type.

Creating a Procedure

A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The simplified
syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]

{IS | AS}

BEGIN

< procedure_body >

END procedure_name;

Where,

 procedure-name specifies the name of the procedure.

 [OR REPLACE] option allows the modification of an existing procedure.
 The optional parameter list contains name, mode and types of the parameters. IN represents
the value that will be passed from outside and OUT represents the parameter that will be
used to return a value outside of the procedure.
 procedure-body contains the executable part.
 The AS keyword is used instead of the IS keyword for creating a standalone procedure.
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 the above code is executed using the SQL prompt, it will produce the following result −

Procedure created.

Executing a 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 will 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 will display −

Hello World

PL/SQL procedure successfully completed.

Deleting a Procedure

A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax for deleting a
procedure is −

DROP PROCEDURE procedure-name;

You can drop the greetings procedure by using the following statement −

DROP PROCEDURE greetings;

Example:

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

a number;

PROCEDURE squareNum(x IN OUT number) IS

BEGIN

x := x * x;

END;

BEGIN

a:= 23;

squareNum(a);

dbms_output.put_line(' Square of (23): ' || a);

END;

When the above code is executed at the SQL prompt, it produces the following result −

Square of (23): 529

PL/SQL procedure successfully completed.

A stored procedure in PL/SQL is nothing but a series of declarative SQL statements which can be
stored in the database catalogue. A procedure can be thought of as a function or a method. They can
be invoked through triggers, other procedures, or applications on Java, PHP etc.

All the statements of a block are passed to Oracle engine all at once which increases processing
speed and decreases the traffic.

Advantages:

 They result in performance improvement of the application. If a procedure is being called

frequently in an application in a single connection, then the compiled version of the procedure
is delivered.
 They reduce the traffic between the database and the application, since the lengthy
statements are already fed into the database and need not be sent again and again via the
application.
 They add to code reusability, similar to how functions and methods work in other languages
such as C/C++ and Java.

Disadvantages:

 Stored procedures can cause a lot of memory usage. The database administrator should
decide an upper bound as to how many stored procedures are feasible for a particular
application.
  MySQL does not provide the functionality of debugging the stored procedures.

Packages

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 the 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 the 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.

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 the 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 −

package_name.element_name;

Consider, we already have created the 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 the SQL prompt, it prompts to enter the customer
ID and when you enter an ID, it displays the 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.No.ame%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;

When the above code is executed at the 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.No.ame%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;

The above example makes use of the nested table. We will discuss the concept of nested
table in the next chapter.

When the above code is executed at the 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

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 the 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

Advantages of the package are listed below:

 It gives modularity to our code.
 It helps to design the application easily.
 PLSQL Package helps to hide information with the help of public and private
items, data types, and subprograms.
 package allows maintaining information over all the transactions without
requiring to store it in the database.
 Packages help improve performance of execution.

Dependency in Packages

Since the package is the logical grouping of related things, it has some dependencies.
Following are the dependency that is to be taken care.

 A Specification is a standalone object.

 A Package body is dependent on specification.
 Package body can be compiled separately. Whenever specification is compiled,
the body needs to be recompiled as it will become invalid.
 The subprogram in package body that is dependent on a private element should
be defined only after the private element declaration.
  The database objects that are referred in the specification and body needs to be
in valid status at the time of package compilation.

Guidelines for creating a Package in PL SQL:

 A package should be written in generic terms so they can be reused in the future.
 Avoid duplicate characteristics in a package.
 Package specification contains the key to a properly designed application.
Complete package specification before creating the package body.
 A package specification should only contain items, subprograms, and types that
must be accessible to the package users. It should not have unnecessary
implementation information.
 The package specification should contain a limited number of items. It shall save
time for recompiling the code as a modification to the package specification
requires PL/SQL to recompile all the subprograms that refer the package.

The list of the data definition tables are as follows:

 USER_PROCEDURES: This table contains subprogram information like the
overloaded items, object_id, and so on for the current user.
 ALL_PROCEDURES: This table contains subprogram information like the
overloaded items, object_id, and so on for all the users.
 USER_SOURCE: This table contains the information on the object source for the
current user.
 ALL_SOURCE: This table contains the information on the object source for all the
users.
 ALL_OBJECT: This table contains the information on the package like the
creation_date, object_id, and other object detail for all the users.

DBMS Output In PL/SQL

DBMS_OUTPUT package allows the display of the PL/SQL output produced from
subprograms and blocks of code. This helps us to debug, test our code, and to send
messages.

The put_line procedure produces the output data to a buffer. The information is
displayed with the help of the get_line procedure or by configuring SERVEROUTPUT ON
in the SQL*Plus.

DBMS_OUTPUT package contains the following subprograms:

1. DBMS_OUTPUT.DISABLE : Confines the message output.

 2. DBMS_OUTPUT.ENABLE (buffer IN INTEGER DEFAULT 20000) : Allows the
message output. If the buffer is set to NULL, it represents an unlimited size of the
buffer.
3. DBMS_OUTPUT.GET_LINE (line OUT VARCHAR, status OUT NUMBER):
Fetches a buffered information within a single line.
4. DBMS_OUTPUT.NEW_LINE : Terminates an end of line marker.
5. DBMS_OUTPUT.PUT (item IN VARCHAR) : Puts an incomplete line in the
buffer.
6. DBMS_OUTPUT.PUT_LINE (item IN VARCHAR) :Puts a complete line in the
buffer.
7.

Code Implementation:

DECLARE

BEGIN

DBMS_OUTPUT.PUT_LINE ('Software Testing Help!');

END;

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 can 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 [OR REPLACE ] 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,

 CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger

with the trigger_name.
 {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will 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 will 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, such as INSERT, UPDATE, and DELETE.
 [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will 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.

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 −

CREATE OR REPLACE 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;

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 the SQL prompt, it produces the following result −

Trigger created.
The following points need to be considered here −

 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.
 The 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 the 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 the CUSTOMERS table, the 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, old salary is not available and the above result comes as null. Let us
now perform one more DML operation on the CUSTOMERS table. The UPDATE statement will
update an existing record in the table −

UPDATE customers

SET salary = salary + 500

WHERE id = 2;

When a record is updated in the CUSTOMERS table, the 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

Advantages of Triggers
These are the following advantages of Triggers:

 Trigger generates some derived column values automatically

 Enforces referential integrity
 Event logging and storing information on table access
 Auditing
 Synchronous replication of tables
 Imposing security authorizations
 Preventing invalid transactions
 enerating 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

Types of Triggers in Oracle

Triggers can be classified based on the following parameters.

 Classification based on the timing

 BEFORE Trigger: It fires before the specified event has occurred.
 AFTER Trigger: It fires after the specified event has occurred.
 INSTEAD OF Trigger: A special type. You will learn more about the further topics. (only for
DML )
 Classification based on the level
 STATEMENT level Trigger: It fires one time for the specified event statement.
 ROW level Trigger: It fires for each record that got affected in the specified event. (only for
DML)
 Classification based on the Event
 DML Trigger: It fires when the DML event is specified (INSERT/UPDATE/DELETE)
 DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER)
 DATABASE Trigger: It fires when the database event is specified
(LOGON/LOGOFF/STARTUP/SHUTDOWN)

References

PL/SQL - Triggers - Tutorialspoint

PL/SQL Triggers (oracle.com)

PL/SQL Trigger - javatpoint

Oracle PL/SQL Trigger Tutorial: Instead of, Compound [Example] (guru99.com)

Video References
(1) triggers in pl sql with examples - YouTube