functional dependency in database (1).pptx

What is Functional Dependency?
Functional dependency (FD) is a set of constraints between two attributes in a relation. Functional dependency says that
if two tuples have same values for attributes A1, A2,..., An, then those two tuples must have to have same values for
attributes B1, B2, ..., Bn.
Functional Dependency (FD) is a constraint that determines the relation of one attribute to another attribute in a
Database Management System (DBMS).
Functional Dependency helps to maintain the quality of data in the database.
Functional dependency is represented by an arrow sign (→) that is, X→Y, where X functionally determines Y. The left-
hand side attributes determine the values of attributes on the right-hand side.
α β
t1[α] t1[β]
t2[α] t2[β]
……. …….
If and are the two sets of attributes in a relational table R where-
α β
• R
α ⊆
• R
β ⊆
Then, for a functional dependency to exist from to ,
α β
If t1[ ] = t2[ ], then t1[ ] = t2[ ]
α α β β
fd : α (determinant)→ β(dependent)

In this example, if we know the value of Employee number, we can obtain
Employee Name, city, salary, etc. By this, we can say that the city, Employee
Name, and salary are functionally depended on Employee number.

1. Trivial Functional Dependencies-
•A functional dependency X → Y is said to be trivial if and only if Y X.
⊆
•Thus, if RHS of a functional dependency is a subset of LHS, then it is called as a trivial functional dependency.
Examples-
The examples of trivial functional dependencies are-
•AB → A
•AB → B
•AB → AB
2. Non-Trivial Functional Dependencies-
A functional dependency X → Y is said to be non-trivial if and only if Y X.
⊄
•Thus, if there exists at least one attribute in the RHS of a functional dependency that is not a part of LHS, then it is called as
a non-trivial functional dependency.
Examples-
The examples of non-trivial functional dependencies are-
•AB → BC
•AB → CD

1. Trivial Functional Dependency
In Trivial Functional Dependency, a dependent is always a subset of
the determinant.
i.e. If X(determinant) → Y(dependent) and Y is the subset of X,
then it is called trivial functional dependency
Here, {roll_no, name} → name is a trivial functional
dependency, since the dependent name is a subset of
determinant set{roll_no,name}
Similarly, roll_no → roll_no is also an example of
trivial functional dependency.

2. Non-trivial Functional Dependency
In Non-trivial functional dependency, the dependent is strictly not a
subset of the determinant.
i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial
functional dependency.
Here, roll_no → name is a non-trivial functional
dependency, since the dependent name is not a subset
of determinant roll_no
Similarly, {roll_no, name} → age is also a non-trivial
functional dependency, since age is not a subset of
{roll_no, name}

Armstrong’s axioms/properties of functional dependencies:
1.Reflexivity: If Y is a subset of X, then X→Y holds by reflexivity rule
For example, {roll_no, name} → name is valid.
2.Augmentation: If X → Y is a valid dependency, then XZ → YZ is also valid by the
augmentation rule.
For example, If {roll_no, name} → dept_building is valid, hence {roll_no, name,
dept_name} → {dept_building, dept_name} is also valid.→
3. Transitivity: If X → Y and Y → Z are both valid dependencies, then X→Z is also
valid by the Transitivity rule. For example, roll_no → dept_name & dept_name →
dept_building, then roll_no → dept_building is also valid.

Dependencies in DBMS is a relation between two or more attributes. It has the following types in DBMS −
•Functional Dependency
•Fully-Functional Dependency
•Transitive Dependency
•Multivalued Dependency
•Partial Dependency
EmpID EmpName EmpAge
E01 Amit 28
E02 Rohit 31
EmpID -> EmpName
Functional Dependency
If the information stored in a table can uniquely determine another information in the same table, then it is called Functional
Dependency. Consider it as an association between two attributes of the same relation.
If P functionally determines Q, then P->Q
Let us see an example −
EmpName is functionally dependent on EmpID because EmpName can take only one value for the given value of EmpID:

Fully-functionally Dependency
An attribute is fully functional dependent on another attribute, if it is Functionally Dependent on that attribute and not on
any of its proper subset.
For example, an attribute Q is fully functional dependent on another attribute P, if it is Functionally Dependent on P and not
on any of the proper subset of P.
ProjectID ProjectCost
001 1000
002 5000
EmpID ProjectID Days (spent on the
project)
E099 001 320
E056 002 190
EmpID, ProjectID, ProjectCost -> Days
However, it is not fully functional dependent.
Whereas the subset {EmpID, ProjectID} can easily determine the {Days} spent on the project by the employee.
This summarizes and gives our fully functional dependency −
{EmpID, ProjectID} -> (Days)

Transitive Dependency
When an indirect relationship causes functional dependency it is called Transitive Dependency.
If P -> Q and Q -> R is true, then P-> R is a transitive dependency.
Multivalued Dependency
When existence of one or more rows in a table implies one or more other rows in the same table, then the Multi-valued
dependencies occur.
If a table has attributes P, Q and R, then Q and R are multi-valued facts of P.
It is represented by double arrow ->->
Example: Suppose there is a bike manufacturer company which produces two colors(white and black) of each model every
year.
Here columns COLOR and MANUF_YEAR are dependent on BIKE_MODEL and independent of each other.
In this case, these two columns can be called as multivalued dependent on BIKE_MODEL. The representation of these
dependencies is shown below:
BIKE_MODEL → → MANUF_YEAR
BIKE_MODEL → → COLOR
This can be read as "BIKE_MODEL multidetermined
MANUF_YEAR" and "BIKE_MODEL multidetermined COLOR".
BIKE_MODEL MANUF_YEAR COLOR
M2011 2008 White
M2001 2008 Black
M3001 2013 White
M3001 2013 Black
M4006 2017 White
M4006 2017 Black

Normalization
A large database defined as a single relation may result in data duplication. This repetition
of data may result in:
• Making relations very large.
• It isn't easy to maintain and update data as it would involve searching many records in
relation.
• Wastage and poor utilization of disk space and resources.
• The likelihood of errors and inconsistencies increases.
So to handle these problems, we should analyze and decompose the relations with
redundant data into smaller, simpler, and well-structured relations that are satisfy desirable
properties. Normalization is a process of decomposing the relations into relations with fewer
attributes.

What is Normalization?
• Normalization is the process of organizing the data in the database.
• Normalization is used to minimize the redundancy from a relation
or set of relations. It is also used to eliminate undesirable
characteristics like Insertion, Update, and Deletion Anomalies.
• Normalization divides the larger table into smaller and links them
using relationships.
• The normal form is used to reduce redundancy from the database
table.

First Normal Form-
A given relation is called in First Normal Form (1NF) if each cell of the table contains only an atomic value.
OR
A given relation is called in First Normal Form (1NF) if the attribute of every tuple is either single valued or a null value.
The following relation is not in 1NF-
Student_id Name Subjects
100 Akshay Computer Networks, Designing
101 Aman Database Management System
102 Anjali Automata, Compiler Design
However,
This relation can be brought into 1NF.
This can be done by rewriting the relation such that each cell of the table contains only one value.
Student_id Name Subjects
100 Akshay Computer Networks
100 Akshay Designing
101 Aman Database Management System
102 Anjali Automata
102 Anjali Compiler Design

1. First Normal Form –
If a relation contain composite or multi-valued attribute, it violates first normal form or a relation is in first normal form if it does not contain any composite or multi-
valued attribute. A relation is in first normal form if every attribute in that relation is singled valued attribute.
Example 1 – Relation STUDENT in table 1 is not in 1NF because of multi-valued attribute STUD_PHONE. Its decomposition into 1NF is?

Decomposed table:
Example 2 –
Convert into 1NF

2. Second Normal Form –
To be in second normal form, a relation must be in first normal form and relation must not contain any partial dependency. A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of
any candidate key) is dependent on any proper subset of any candidate key of the table.
Partial Dependency –
If the proper subset of candidate key determines non-prime attribute, it is called partial dependency.

In case there is a partial dependency, we will remove that attribute from the relation that is partially dependent.
In this table,
you can note that many subjects come with the same subject fee.
Three things are happening here:
The SUBJECT_FEE won’t be able to determine the values of CAND_NO or SUBJECT_NO alone;
The SUBJECT_FEE along with CAND_NO won’t be able to determine the values of SUBJECT_NO;
The SUBJECT_FEE along with SUBJECT_NO won’t be able to determine the values of CAND_NO;
Thus,
We can conclude that the attribute SUBJECT_FEE is a non-prime one since it doesn’t belong to the candidate key here
{SUBJECT_NO, CAND_ID} ;
But, on the other hand, the SUBJECT_NO – > SUBJECT_FEE, meaning the SUBJECT_FEE depends directly on the
SUBJECT_NO, and it forms the candidate key’s proper subset. Here, the SUBJECT_FEE is a non-prime attribute, and it
depends directly on the candidate key’s proper subset. Thus, it forms a partial dependency.
Conclusion: The relation mentioned here does not exist in 2NF.
CAND_ID SUBJECT_N
O
SUBJECT_FE
E
111 S1 1000
222 S2 1500
111 S4 2000
444 S3 1000
444 S1 1000
222 S5 2000

Table 1
CAND_NO SUBJECT_NO
111 S1
222 S2
111 S4
444 S3
444 S1
222 S5
Table 2
SUBJECT_NO SUBJECT_FEE
S1 1000
S2 1500
S3 1000
S4 2000
S5 2000
Now, the tables are in their Second Normal Form.
Note: The Second Normal Form tries to reduce any redundant data from getting stored in the system’s
memory. For instance, if we take an example of about 100 candidates taking the S1 subject, then we don’t
have to store their fees as 1000 as a record for all the 100 candidates. Rather, we can store them all at once
in the second table as the subject fee for S1 is 1000

Example- Consider following functional dependencies in relation
R (A, B , C, D )
In the above relation, AB is the only candidate key and there is no
partial dependency, i.e., any proper subset of AB doesn’t determine
any non-prime attribute.

3. Third Normal Form –
A given relation is said to be in its third normal form when it’s in 2NF but has no transitive partial dependency. Meaning, when no transitive dependency exists for the attributes that are non-prime, then the relation can be said to be in 3NF.
In a relation that is in 1NF or 2NF, when none of the non-primary key attributes transitively depend on their primary keys, then we can say that the relation is in the third normal form of 3NF.
Rules Followed in 3rd Normal Form in DBMS
We can say that a relation is in the third normal form when it holds any of these given conditions in case of a functional dependency P -> Q that is non-trivial:
P acts as a super key.
Q acts as a non-prime attribute. Meaning, every element of Q forms a part of a candidate key.

To normalize a 2NF to 3NF, we have to determine if we have a transitive dependency in the table. In case a transitive
dependency exists, then we remove those attributes that are transitively dependent from the relations.
Note: If P -> Q and Q -> R are two functional dependencies, then P -> R is known as a transitive dependency. When
normalizing a 2NF relation to 3NF, we remove these transitive dependencies.
CAND_NO CAND_NAME CAND_STATE CAND_COUNT
RY
CAND_AGE
1 TINA MAHARASHTR
A
INDIA 18
2 ANJALI RAJASTHAN INDIA 17
3 RAHUL RAJASTHAN INDIA 19
In the relation CANDIDATE given above:
Functional dependency Set:
{CAND_NO -> CAND_NAME, CAND_NO ->CAND_STATE, CAND_STATE -> CAND_CUNTRY, CAND_NO -> CAND_AGE}
So, Candidate key here would be: {CAND_NO}
For the relation given here in the table, CAND_NO -> CAND_STATE and CAND_STATE -> CAND_COUNTRY are actually true.
Thus, CAND_COUNTRY depends transitively on CAND_NO. This transitive relation violates the rules of being in the 3NF. So, if
we want to convert it into the third normal form, then we have to decompose the relation CANDIDATE (CAND_NO,
CAND_NAME, CAND_STATE, CAND_COUNTRY, CAND_AGE) as:
CANDIDATE (CAND_NO, CAND_NAME, CAND_STATE, CAND_AGE)
STATE_COUNTRY (STATE, COUNTRY)

•A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.
•3NF is used to reduce the data duplication. It is also used to achieve the data integrity.
•If there is no transitive dependency for non-prime attributes, then the relation must be in third normal form.
A relation is in third normal form if it holds at least one of the following conditions for every non-trivial function
dependency X → Y.
1.X is a super key.
2.Y is a prime attribute, i.e., each element of Y is part of some candidate key.
EMP_ID EMP_NA
ME
EMP_ZIP EMP_STA
TE
EMP_CIT
Y
222 Harry 201010 UP Noida
333 Stephan 02228 US Boston
444 Lan 60007 US Chicago
555 Katharine 06389 UK Norwich
666 John 462007 MP Bhopal

Super key in the table above:
1.{EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}....so on
Candidate key: {EMP_ID}
Non-prime attributes: In the given table, all attributes except EMP_ID are non-prime.
Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent on EMP_ID. The non-prime attributes
(EMP_STATE, EMP_CITY) transitively dependent on super key(EMP_ID). It violates the rule of third normal form.
That's why we need to move the EMP_CITY and EMP_STATE to the new <EMPLOYEE_ZIP> table, with EMP_ZIP as a
Primary key.
EMP_ID EMP_NAME EMP_ZIP
222 Harry 201010
333 Stephan 02228
444 Lan 60007
555 Katharine 06389
666 John 462007

EMP_ZIP EMP_STATE EMP_CITY
201010 UP Noida
02228 US Boston
60007 US Chicago
06389 UK Norwich
462007 MP Bhopal
EMPLOYEE_ZIP table:
Boyce Codd normal form (BCNF)
•BCNF is the advance version of 3NF. It is stricter than 3NF.
•A table is in BCNF if every functional dependency X Y, X is the super key of the table.
→
•For BCNF, the table should be in 3NF, and for every FD, LHS is super key.

Example: Let's assume there is a company where employees work in more than one department.
EMPLOYEE table:
EMP_ID EMP_COU
NTRY
EMP_DEPT DEPT_TYP
E
EMP_DEPT
_NO
264 India Designing D394 283
264 India Testing D394 300
364 UK Stores D283 232
364 UK Developing D283 549
In the above table Functional dependencies are as follows:
1.EMP_ID → EMP_COUNTRY
2.EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate key: {EMP-ID, EMP-DEPT}le to pay record $391.5 million privacy settlement!
The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.
To convert the given table into BCNF, we decompose it into three tables:

EMP_ID EMP_COUNTRY
264 India
264 India
EMP_COUNTRY table:
EMP_DEP
T
DEPT_TY
PE
EMP_DEP
T_NO
Designing D394 283
Testing D394 300
Stores D283 232
Developin
g
D283 549
EMP_DEPT table:
EMP_ID EMP_DEPT
D394 283
D394 300
D283 232
D283 549
EMP_DEPT_MAPPING table:
Functional dependencies:
1.EMP_ID EMP_COUNTRY
→
2.EMP_DEPT {DEPT_TYPE, EMP_DEPT_NO}
→
Candidate keys:
For the first table: EMP_ID
For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}

Example—
Take a look at these functional dependencies in the relation
A (P, Q, R, S, T)
Here,
P-> QR,
RS-> T,
Q-> S,
T-> P
In the relation given above, all the possible candidate keys would be
{P, T, RS, QR}. In this case, the attributes that exist on the right sides of
all the functional dependencies are prime.

4. Boyce-Codd Normal Form (BCNF) –
A relation R is in BCNF if R is in Third Normal Form and for every FD, LHS is super key. A relation is in BCNF iff in every non-trivial functional dependency X –> Y, X is a super key.
Example 1 – Find the highest normal form of a relation R(A,B,C,D,E) with FD set as {BC->D, AC->BE, B->E}

1.Find all possible candidate keys of the relation.
2.Divide all attributes into two categories: prime attributes and non-prime attributes.
3.Check for 1st
normal form then 2nd
and so on. If it fails to satisfy the nth
normal form condition,
the highest normal form will be n-1.
Example 1. Find the highest normal form of a relation R(A,B,C,D,E) with FD set {A->D, B->A, BC-
>D, AC->BE}
Step 1. As we can see, (AC)+ ={A, C, B, E, D} but none of its subsets can determine all attributes
of relation, So AC will be the candidate key. A can be derived from B, so we can replace A in AC
with B. So BC will also be a candidate key. So there will be two candidate keys {AC, BC}.
Step 2. The prime attribute is those attribute which is part of candidate key {A, B, C} in this
example and others will be non-prime {D, E} in this example.
Step 3. The relation R is in 1st normal form as a relational DBMS does not allow multi-valued or
composite attributes.
The relation is not in the 2nd Normal form because A->D is partial dependency (A which is a
subset of candidate key AC is determining non-prime attribute D) and the 2nd normal form does
not allow partial dependency.
So the highest normal form will be the 1st Normal Form.

Example 2. Find the highest normal form of a relation R(A,B,C,D,E) with FD set as {BC->D, AC->BE, B->E}
Step 1. As we can see, (AC)+ ={A,C,B,E,D} but none of its subsets can determine all attributes of relation, So AC will
be the candidate key. A or C can’t be derived from any other attribute of the relation, so there will be only 1
candidate key {AC}.
Step 2. The prime attribute is those attribute which is part of candidate key {A,C} in this example and others will be
non-prime {B,D,E} in this example.
Step 3. The relation R is in 1st normal form as a relational DBMS does not allow multi-valued or composite
attributes.
The relation is in 2nd normal form because BC->D is in 2nd normal form (BC is not a proper subset of candidate key
AC) and AC->BE is in 2nd normal form (AC is candidate key) and B->E is in 2nd normal form (B is not a proper
subset of candidate key AC).
The relation is not in 3rd normal form because in BC->D (neither BC is a super key nor D is a prime attribute) and
in B->E (neither B is a super key nor E is a prime attribute) but to satisfy 3rd normal for, either LHS of an FD should
be super key or RHS should be a prime attribute.
So the highest normal form of relation will be the 2nd Normal form.

Example 3. Find the highest normal form of a relation R(A,B,C,D,E) with FD set {B->A, A-
>C, BC->D, AC->BE}
Step 1. As we can see, (B)+
={B,A,C,D,E}, so B will be candidate key. B can be derived from AC
using AC->B (Decomposing AC->BE to AC->B and AC->E). So AC will be super key but (C)
+
={C} and (A)+
={A,C,B,E,D}. So A (subset of AC) will be candidate key. So there will be two
candidate keys {A,B}.
Step 2. The prime attribute is those attribute which is part of candidate key {A,B} in this
example and others will be non-prime {C,D,E} in this example.
Step 3. The relation R is in 1st
normal form as a relational DBMS does not allow multi-valued
or composite attributes.
The relation is in 2nd
normal form because B->A is in 2nd
normal form (B is a superkey) and A-
>C is in 2nd
normal form (A is super key) and BC->D is in 2nd
normal form (BC is a super key)
and AC->BE is in 2nd
normal form (AC is a super key).
The relation is in 3rd
normal form because the LHS of all FD’s is super keys. The relation is in
BCNF as all LHS of all FD’s are super keys. So the highest normal form is BCNF.

Fourth Normal Form (4NF) :
Any relation is said to be in the fourth normal form when it satisfies the following conditions:
•It must be in Boyce Codd Normal Form (BCNF).
•It should have no multi-valued dependency.
A multi-valued dependency is said to occur when there are two attributes in a table which depend on a third attribute
but are independent of each other.
For a functional dependency X->Y there will be a multi-valued dependency if there exists multiple values of Y for a
single value of X.
Thus if a relation is in BCNF and also it does not have any kind of multi-valued dependency then that relation will be in
4NF.
In order to denote a multi-valued dependency, “->->” this sign is used.
In this relation Student-ID 1 has thus opted for two courses and has two hobbies. Similarly Student-ID 2 has
opted for two courses and has two hobbies. Thus it contains multi-valued dependencies. It is not in 4NF and
in order to convert it into 4NF it can be decomposed into two relations:

Student-ID Course Hobby
100 Science Dancing
100 Maths Singing
101 C# Dancing
101 PHP Singing
Student-ID Hobby
100 Dancing
100 Singing
101 Dancing
101 Singing
Student-ID Course
100 Science
100 Maths
101 C#
101 PHP

Fifth Normal Form (5NF) :
Any relation in order to be in the fifth normal form must satisfy the following conditions:
It must be in Fourth Normal Form (4NF).
It should have no join dependency and also the joining must be lossless.
In the fifth normal form the relation must be decomposed in as many sub-relations as possible so as to avoid any kind of
redundancy and there must be no extra tuples generated when the sub-relations are combined together by using natural
join.
A relation in 5NF cannot be decomposed further without any kind of modification in the meaning or facts. 5NF is also
known as Project Join Normal Form (PJNF).
Student-ID Mobile Number Hobby
123 9999900000 Dancing
124 9999900000 Singing
124 9999900000 Dancing
123 8975622122 Singing
123 9999900000 Singing
Student-ID Mobile Number
123 9999900000
123 8975622122
124 9999900000
Student-ID Hobby
123 Dancing
123 Singing
124 Singing
124 Dancing
Mobile Number Hobby
9999900000 Dancing
9999900000 Singing
8975622122 Singing
Thus if natural join is performed on all
the three relations then there will be no
extra tuples. Hence R1, R2 and R3 are in
fifth normal form (5NF).

Oracle PL/SQL is an extension of SQL language that combines the data manipulation power of SQL with the
processing power of procedural language to create super powerful SQL queries.
PL/SQL ensures seamless processing of SQL statements by enhancing the security, portability, and robustness
of the Database.
Similar to other database languages, it gives more control to the programmers by the use of loops, conditions
and object-oriented concepts.
The PL/SQL Full form is “Procedural Language extensions to SQL”.
n PL/SQL, a block without any name is called Anonymous Block. PL/ SQL block consists of various functions,
library, procedures, trigger, packages etc.
The following points should be remembered while writing a PL/SQL program –
• In PL/SQL the semicolon (;) is placed at the end of an SQL statement or PL/SQL control statement.
• Section keyword DECLARE, BEGIN and EXECUTION are not followed by semicolons.
• END keyword and all other PL/SQL statements require a semicolon to terminate the statements.

The PL/SQL architecture mainly consists of following
three components:
• PL/SQL Block
• PL/SQL Engine
• Database Server
PL/SQL block:
This is the component which has the actual PL/SQL
code.
This consists of different sections to divide the code
logically (declarative section for declaring purpose,
execution section for processing statements, exception
handling section for handling errors)
It also contains the SQL instruction that used to interact
with the database server.
All the PL/SQL units are treated as PL/SQL blocks, and
this is the starting stage of the architecture which serves
as the primary input.

Following are the different type of PL/SQL units.
Anonymous Block
Function
Package Specification
Trigger
Type
Type Body
PL/SQL Engine
PL/SQL engine is the component where the actual processing of the
codes takes place.
PL/SQL engine separates PL/SQL units and SQL part in the input
The separated PL/SQL units will be handled by the PL/SQL engine
itself.
The SQL part will be sent to database server where the actual
interaction with database takes place.
It can be installed in both database server and in the application
server.
Database Server:
This is the most important component of Pl/SQL unit which stores
the data.
The PL/SQL engine uses the SQL from PL/SQL units to interact with
the database server. It consists of SQL executor which parses the
input SQL statements and execute the same.
SQL PL/SQL
•SQL is a single query that
is used to perform DML
and DDL operations.
•PL/SQL is a block of codes
that used to write the
entire program blocks/
procedure/ function, etc.
•It is declarative, that
defines what need to be
done, rather than how
things need to be done.
•PL/SQL is procedural that
defines how the things
needs to be done.
•Execute as a single
statement.
•Execute as a whole block.
•Mainly used to
manipulate data.
•Mainly used to create an
application.
•Interaction with a
Database server.
•No interaction with the
database server.
•Cannot contain PL/SQL
code in it.
•It is an extension of SQL,
so that it can contain SQL
inside it.

What is PL/SQL block?
In PL/SQL, the code is not executed in single line format, but it is always executed by grouping the code into a single element
called Blocks.
Block Structure
PL/SQL blocks have a pre-defined structure in which the code is to be grouped. Below are different sections of PL/SQL
blocks.
• Declaration section
• Execution section
• Exception-Handling section

Declaration Section
This is the first section of the PL/SQL blocks.
This section is an optional part.
This is the section in which the declaration of variables, cursors, exceptions, subprograms,
pragma instructions and collections that are needed in the block will be declared.
This section starts with the keyword ‘DECLARE’ for triggers and anonymous block.
For other subprograms, this keyword will not be present. Instead, the part after the
subprogram name definition marks the declaration section.
This section should always be followed by execution section.

Execution Section
Execution part is the main and mandatory part which actually executes the code that is
written inside it.
Since the PL/SQL expects the executable statements from this block this cannot be an empty
block, i.e., it should have at least one valid executable code line in it. Below are few more
characteristics of this part.
This can contain both PL/SQL code and SQL code.
This can contain one or many blocks inside it as a nested block.
This section starts with the keyword ‘BEGIN’.
This section should be followed either by ‘END’ or Exception-Handling section (if present)
It supports all DML commands, DDL commands and SQL*PLUS built-in functions as well.

Exception-Handling Section:
The exception is unavoidable in the program which occurs at run-time
and to handle this Oracle has provided an Exception-handling section in
blocks.
This section can also contain PL/SQL statements. This is an optional
section of the PL/SQL blocks.
This is the section where the exception raised in the execution block is
handled.
This section is the last part of the PL/SQL block.
Control from this section can never return to the execution block.
This section starts with the keyword ‘EXCEPTION’.
This section should always be followed by the keyword ‘END’.

DECLARE --optional
<declarations>
BEGIN --mandatory
<executable statements. At least one executable
statement is mandatory>
EXCEPTION --optional
<exception handles>
END; --mandatory
/
Note: A block should always be followed by ‘/’
which sends the information to the compiler
about the end of the block.

PL/SQL identifiers
There are several PL/SQL identifiers such as variables, constants, procedures, cursors, triggers etc.
Variables:
Like several other programming languages, variables in PL/SQL must be declared prior to its use. They should have a valid
name and data type as well.
Syntax for declaration of variables:
variable_name datatype [NOT NULL := value ];
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
var1 INTEGER;
var2 REAL;
var3 varchar2(20) ;
BEGIN
null;
END;
/
Output:
PL/SQL procedure successfully completed.

•SET SERVEROUTPUT ON: It is used to display the buffer used by the dbms_output.
•var1 INTEGER : It is the declaration of variable, named var1 which is of integer type. There are many other data types
that can be used like float, int, real, smallint, long etc. It also supports variables used in SQL as well like NUMBER(prec,
scale), varchar, varchar2 etc.
•PL/SQL procedure successfully completed.: It is displayed when the code is compiled and executed successfully.
•Slash (/) after END;: The slash (/) tells the SQL*Plus to execute the block.
1.1) INITIALISING VARIABLES:
The variables can also be initialized just like in other programming languages.
SQL> DECLARE
var1 INTEGER := 2 ;
var3 varchar2(20) := ‘Hello' ;
BEGIN
null;
END;
/
Output:
Explanation:
Assignment operator (:=) : It is used to assign a value to a variable.

Displaying Output:
The outputs are displayed by using DBMS_OUTPUT which is a built-in package that enables
the user to display output, debugging information, and send messages from PL/SQL blocks,
subprograms, packages, and triggers.
SQL> DECLARE
var varchar2(40) := ‘Hello' ;
BEGIN
dbms_output.put_line(var);
END;
/
Output:
Hello
Explanation:
dbms_output.put_line : This command is used to direct the PL/SQL output to a screen.

Using Comments:
Like in many other programming languages, in PL/SQL also, comments can be put within the code which has
no effect in the code. There are two syntaxes to create comments in PL/SQL :
Single Line Comment: To create a single line comment , the symbol – – is used.
Multi Line Comment: To create comments that span over several lines, the symbol /* and */ is used.
Example to show how to create comments in PL/SQL :
SQL> DECLARE
-- I am a comment, so i will be ignored.
var varchar2(40) := ‘Hello' ;
BEGIN
dbms_output.put_line(var);
END;
/
Output:
Hello

Taking input from user:
SQL> DECLARE
-- taking input for variable a
a number := &a;
-- taking input for variable b
b varchar2(30) := &b;
BEGIN
null;
END;
/
Output:
Enter value for a: 24
old 2: a number := &a;
new 2: a number := 24;
Enter value for b: ‘HelloWorld'
old 3: b varchar2(30) := &b;
new 3: b varchar2(30) := ‘HelloWorld';

--PL/SQL code to print sum of two numbers taken from the user.
SQL> DECLARE
-- taking input for variable a
a integer := &a ;
-- taking input for variable b
b integer := &b ;
c integer ;
BEGIN
c := a + b ;
dbms_output.put_line('Sum of '||a||' and '||b||' is = '||c);
END;
/
Enter value for a: 2
Enter value for b: 3
Sum of 2 and 3 is = 5

Example of Local and Global variables
DECLARE
-- Global variables
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
-- Local variables
num1 number := 195;
num2 number := 185;
BEGIN
dbms_output.put_line('Inner Variable num1: ' || num1);
dbms_output.put_line('Inner Variable num2: ' || num2);
END;
END;
/
After the execution, this will produce the following result:
Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185

Initializing Variables in PL/SQL
Evertime you declare a variable, PL/SQL defines a default value NULL to it. If you want to initialize a
variable with other value than NULL value, you can do so during the declaration, by using any one of the
following methods.
• The DEFAULT keyword
• The assignment operator
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Hello Hello';
•Local Variable: Local variables are the inner block variables which are not accessible to outer blocks.
•Global Variable: Global variables are declared in outermost block.
DECLARE
a integer := 30;
b integer := 40;
c integer;
f real;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 100.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;

Variable Attributes:
A % sign servers as the attribute indicator.
%TYPE:
The %TYPE attribute is used to declare variables according to the already declared variable or database column.
It is used when you are declaring an individual variable, not a record.
The data type and precision of the variable declared using %TYPE attribute is the same as that of the column that is
referred from a given table.
The syntax for declaring a variable with %TYPE is:
<var_name> <tab_name>.<column_name>%TYPE;
DECLARE
SALARY EMP.SAL % TYPE;
ECODE EMP.empno % TYPE;
BEGIN
Ecode :=&Ecode;
Select SAL into SALARY from EMP where EMPNO = ECODE;
dbms_output.put_line('Salary of ' || ECODE || 'is = || salary');
END;
After the execution, this will produce the following result:
Enter value for ecode: 7499
Salary of 7499 is = 1600

%ROWTYPE:
The %ROWTYPE attribute is used to declare a record type that represents a row in a table. The record can store an entire
row or some specific data selected from the table. A column in a row and corresponding fields in a record have the same
name and data types.
The syntax for declaring a variable with %ROWTYPE is:
<var_name> <tab_name>.ROW%TYPE;
Where <variable_name> is the variable defined in the <tab_name>.
EMPLOYEE EMP. % ROW TYPE;
This declaration will declare a record named EMPLOYEE having fields with the same name and data types as that of
columns in the EMP table.
DECLARE
EMPLOYEE EMP. % ROW TYPE;
BEGIN
EMPLOYEE.EMPNO := 2092;
5 EMPLOYEE.ENAME := 'Sanju';
Insert into EMP where (EMPNO, ENAME) Values (employee.empno, employee.ename);
dbms_output.put_line('Row Inserted');
END;
If the data type of the variable you are referencing changes the %TYPE or %ROWTYPE variable changes at run time without
having to rewrite variable declarations. For example: if the ENAME column of an EMP table is changed from a
VARCHAR2(10) to VRACHAR2(15) then you don’t need to modify the PL/SQL code.

PL/SQL If
PL/SQL supports the programming language features like conditional statements and iterative statements. Its
programming constructs are similar to how you use in programming languages like Java and C++.
Syntax for IF Statement:
There are different syntaxes for the IF-THEN-ELSE statement.
Syntax: (IF-THEN statement):
IF condition
THEN
Statement: {It is executed when condition is true}
END IF;
This syntax is used when you want to execute statements only when condition is TRUE.
Syntax: (IF-THEN-ELSE statement):
IF condition
THEN
{...statements to execute when condition is TRUE...}
ELSE
{...statements to execute when condition is FALSE...}
END IF;
This syntax is used when you want to execute one set of statements when condition is TRUE or a different set of
statements when condition is FALSE.

Syntax: (IF-THEN-ELSIF statement):
1.IF condition1
2.THEN
3. {...statements to execute when condition1 is TRUE...}
4.ELSIF condition2
5.THEN
7.END IF;
This syntax is used when you want to execute one set of statements when condition1 is TRUE or a different set of statements when
condition2 is TRUE.
Syntax: (IF-THEN-ELSIF-ELSE statement):
8.IF condition1
9.THEN
11.ELSIF condition2
12.THEN
14.ELSE
15. {...statements to execute when both condition1 and condition2 are FALSE...}
16.END IF;
It is the most advance syntax and used if you want to execute one set of statements when condition1 is TRUE, a different set of statement
when condition2 is TRUE or a different set of statements when both the condition1 and condition2 are FALSE.
When a condition is found to be TRUE, the IF-THEN-ELSE statement will execute the corresponding code and not check the conditions
any further.
If there no condition is met, the ELSE portion of the IF-THEN-ELSE statement will be executed.
ELSIF and ELSE portions are optional.

1.DECLARE
2. a number(3) := 500;
3.BEGIN
4. --
check the boolean condition using if statement
5. IF( a < 20 ) THEN
6. -- if condition is true then print the followin
g
7. dbms_output.put_line('a is less than 20 ' );
8. ELSE
9. dbms_output.put_line('a is not less than 20 '
);
10. END IF;
11. dbms_output.put_line('value of a is : ' || a);
12.END;
a is not less than 20
value of a is : 500
DECLARE
c_id customers.id%type := 1;
c_sal customers.salary%type;
BEGIN
SELECT salary
INTO c_sal
FROM customers
WHERE id = c_id;
IF (c_sal <= 2000) THEN
UPDATE customers
SET salary = salary + 1000
WHERE id = c_id;
dbms_output.put_line ('Salary updated');
END IF;
END;
/
When the above code is executed at the SQL prompt, it produces
the following result −
Salary updated

DECLARE
a number(3) := 100;
BEGIN
-- check the boolean condition using if statement
IF( a < 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 20 ' );
ELSE
dbms_output.put_line('a is not less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
/
When the above code is executed at the SQL prompt,
it produces the following result −
a is not less than 20
value of a is : 100
DECLARE
a number(3) := 100;
BEGIN
IF ( a = 10 ) THEN
dbms_output.put_line('Value of a is 10' );
ELSIF ( a = 20 ) THEN
ELSIF ( a = 30 ) THEN
ELSE
dbms_output.put_line('None of the values is matching');
END IF;
dbms_output.put_line('Exact value of a is: '|| a );
END;
/
When the above code is executed at the SQL prompt, it
produces the following result −
None of the values is matching
Exact value of a is: 100

The syntax for the case statement in PL/SQL is
−
CASE selector
WHEN 'value1' THEN S1;
...
ELSE Sn; -- default case
END CASE;
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('You passed');
when 'F' then dbms_output.put_line('Better try
again');
else dbms_output.put_line('No such grade');
END CASE;
END;
/
Excellent

the searched CASE statement has no selector and
the WHEN clauses of the statement contain
search conditions that give Boolean values.
Syntax
The syntax for the searched case statement in
PL/SQL is −
CASE
WHEN selector = 'value1' THEN S1;
...
ELSE Sn; -- default case
END CASE;
DECLARE
grade char(1) := 'B';
BEGIN
case
when grade = 'A' then
dbms_output.put_line('Excellent');
when grade = 'B' then dbms_output.put_line('Very
good');
when grade = 'C' then dbms_output.put_line('Well
done');
when grade = 'D' then dbms_output.put_line('You
passed');
when grade = 'F' then dbms_output.put_line('Better
try again');
else dbms_output.put_line('No such grade');
end case;
END;
/
Very good

Nested IF-THEN-ELSE Statements
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;
DECLARE
a number(3) := 100;
b number(3) := 200;
BEGIN
-- check the boolean condition
IF( a = 100 ) THEN
-- if condition is true then check the following
IF( b = 200 ) THEN
-- if condition is true then print the following
dbms_output.put_line('Value of a is 100 and b is 200' );
END IF;
END IF;
dbms_output.put_line('Exact value of a is : ' || a );
dbms_output.put_line('Exact value of b is : ' || b );
END;
/
Value of a is 100 and b is 200
Exact value of a is : 100
Exact value of b is : 200

PL/SQL Loop
The PL/SQL loops are used to repeat the execution of one or more statements for specified number of times. These are also
known as iterative control statements.
Syntax for a basic loop:
LOOP
Sequence of statements;
END LOOP;
Types of PL/SQL Loops
• Basic Loop / Exit Loop
• While Loop
• For Loop
• Cursor For Loop
PL/SQL Exit Loop (Basic Loop)
PL/SQL exit loop is used when a set of statements is to be executed at least once before the termination of the loop. There
must be an EXIT condition specified in the loop, otherwise the loop will get into an infinite number of iterations. After the
occurrence of EXIT condition, the process exits the loop.
Syntax of basic loop:
LOOP
Sequence of statements;
END LOOP;

Syntax of exit loop:
LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;
DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i>10;
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;
O/P
1 2 3 4 5 6 7 8 9 10
Note: You must follow these steps while using PL/SQL Exit
Loop.
•Initialize a variable before the loop body
•Increment the variable in the loop.
•You should use EXIT WHEN statement to exit from the Loop.
Otherwise the EXIT statement without WHEN condition, the
statements in the Loop is executed only once.

DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
IF x > 50 THEN
exit;
END IF;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
END;
/
10
20
30
40
50
After Exit x is: 60
You can use the EXIT WHEN statement instead of the EXIT
statement −
DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
exit WHEN x > 50;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
END;
/
10
20
30
40
50
After Exit x is: 60

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;
/
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 FOR Loop
PL/SQL for loop is used when when you want
to execute a set of statements for a
predetermined number of times. The loop is
iterated between the start and end integer
values. The counter is always incremented by 1
and once the counter reaches the value of end
integer, the loop ends.
Syntax of for loop:
FOR counter IN initial_value .. final_value
LOOP
LOOP statements;
END LOOP;
initial_value : Start integer value
final_value : End integer value
BEGIN
FOR k IN 1..10 LOOP
-- note that k was not declared
DBMS_OUTPUT.PUT_LINE(k);
END LOOP;
END;
After the execution of the above code, you will get the following result:
1
2
3
4
5
6
7
8
9
10
No need to declare the counter variable explicitly because it is declared
implicitly in the declaration section.
The counter variable is incremented by 1 and does not need to be
incremented explicitly.
You can use EXIT WHEN statements and EXIT statements in FOR Loops
but it is not done often

DECLARE
VAR1 NUMBER;
BEGIN
VAR1:=10;
FOR VAR2 IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);
END LOOP;
END;
Output:
10
20
30
40
50
60
70
80
90
100
PL/SQL For Loop REVERSE
DECLARE
VAR1 NUMBER;
BEGIN
VAR1:=10;
FOR VAR2 IN REVERSE 1..10
LOOP
DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);
END LOOP;
END;
Output:
100
90
80
70
60
50
40
30
20
10

PL/SQL Continue Statement
The continue statement is used to exit the loop from the reminder if its body either conditionally or unconditionally and
forces the next iteration of the loop to take place, skipping any codes in between.
.
DECLARE
a number(2) := 10;
BEGIN
-- while loop execution
WHILE a < 20 LOOP
dbms_output.put_line ('value of a: ' || a);
a := a + 1;
IF a = 15 THEN
-- skip the loop using the CONTINUE statement
a := a + 1;
CONTINUE;
END IF;
END LOOP;
END;
/
After the execution of above code, you will get
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: 16
value of a: 17
value of a: 18
value of a: 19

The continue statement is not a keyword in Oracle 10g. It is a new feature incorporated in oracle 11g.
For example: If a continue statement exits a cursor FOR LOOP prematurely then it exits an inner loop and transfer control to the next iteration
of an outer loop, the cursor closes (in this context, CONTINUE works like GOTO).
After the execution of above code, you will get the
following result:
Inside loop: x = 0
Inside loop: x = 1
Inside loop: x = 2
Inside loop, after CONTINUE: x = 3
Inside loop: x = 3
Inside loop: x = 4
After loop: x = 5
DECLARE
x NUMBER := 0;
BEGIN
LOOP -- After CONTINUE statement, control resumes here
DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' ||
TO_CHAR(x));
x := x + 1;
IF x < 3 THEN
CONTINUE;
END IF;
DBMS_OUTPUT.PUT_LINE
('Inside loop, after CONTINUE: x = ' || TO_CHAR(x));
EXIT WHEN x = 5;
END LOOP;
DBMS_OUTPUT.PUT_LINE (' After loop: x = ' ||
TO_CHAR(x));
END;
/

PL/SQL GOTO Statement
In PL/SQL, GOTO statement makes you able to get an unconditional jump from the GOTO to a specific executable statement
label in the same subprogram of the PL/SQL block.
Here the label declaration which contains the label_name encapsulated within the << >> symbol and must be followed by at
least one statement to execute.
Syntax:
GOTO label_name;
DECLARE
a number(2) := 30;
BEGIN
<<loopstart>>
-- while loop execution
WHILE a < 50 LOOP
dbms_output.put_line ('value of a: ' || a);
a := a + 1;
IF a = 35 THEN
a := a + 1;
GOTO loopstart;
END IF;
END LOOP;
END;
/
After the execution of above code, you will get the following result:
value of a: 30
value of a: 31
value of a: 32
value of a: 33
value of a: 34
value of a: 36
value of a: 37
value of a: 38
value of a: 39
value of a: 40
value of a: 41
value of a: 42
value of a: 43
value of a: 44
value of a: 45
value of a: 46
value of a: 47
value of a: 48
value of a: 49

Restriction on GOTO statement
Following is a list of some restrictions imposed on GOTO statement.
1. Cannot transfer control into an IF statement, CASE statement, LOOP statement or sub-block.
2. Cannot transfer control from one IF statement clause to another or from one CASE statement WHEN clause to another.
3. Cannot transfer control from an outer block into a sub-block.
4. Cannot transfer control out of a subprogram.
5. Cannot transfer control into an exception handler.

Subprograms
• Subprograms are procedures or sub-routines (also called modules) in Oracle
• A subprogram is a program unit/module that performs a particular task.
• These subprograms are combined to form larger programs. This is basically called the 'Modular design'. A
subprogram can be invoked by another subprogram or program which is called the calling program.
• 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.
PL/SQL Procedure
The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more specific tasks. It is
just like procedures in other programming languages.
The procedure contains a header and a body.
Header: The header contains the name of the procedure and the parameters or variables passed to the
procedure.
Body: The body contains a declaration section, execution section and exception section similar to a general
PL/SQL block.

Types of procedures or functions
• Local procedures/functions (local subprograms),
• Stored procedures/functions (stored subprograms)
Stored procedure is a procedure stored in a database (e.g., If a procedure is stored in a database, it
becomes like a library function).
Local procedure is not stored in a database but used within a program (e.g., a typical procedure that can
be defined within a program like in C/C++).
If you omit CREATE OR REPLACE, the PROCEDURE becomes a local procedure.
• Procedure_body is a PL/SQL block (BEGIN…END section)
• Creating a procedure is a DDL operation -- so implicit COMMIT is done
• Either IS or AS can be used
• Parameter mode (Ada style of call-by-value or reference): IN (read-only), OUT (write-only value is
ignored and NOT allowed at RHS value), IN OUT (read-write), default is IN mode.
• Through parameters, procedure can return values
• Note that there is no DECLARE keyword used.

Syntax for creating Local procedure:
It is aprocedure which is defined in declaration section of
PL/SQL block. This is called as local as it is defined in parent
block and cannot be called by any other PL/SQL block defined
outside that enclosing block.
DECLARE
--declaration of global variables
PROCEDURE <procedure_name>
[(parameter_name [IN | OUT | IN OUT] datatype [, ...])]
{IS |AS}
[local declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
BEGIN
--Executable code and call to procedure
[EXCEPTION
exception_section]
END
Syntax for creating Stored procedure:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS |
AS}
BEGIN
< procedure_body >
END procedure_name;
Call stored Procedure
procedure_name(parameters);
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.

How to pass parameters in procedure:
When you want to create a procedure or function, you have to define parameters .There is three ways to pass parameters
in procedure:
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 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.
• Parameters are passed by reference.
OUT
• An OUT parameter returns a value to the calling program.
• Inside the subprogram, an 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 the value can be read.
• 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.

PL/SQL procedure body
Similar to an anonymous block, the procedure body has three parts. The executable part is mandatory whereas the
declarative and exception-handling parts are optional. The executable part must contain at least one executable
statement.
1) Declarative part
In this part, you can declare variables, constants, cursors, etc. Unlike an anonymous block, a declaration part of a
procedure does not start with the DECLARE keyword.
2) Executable part
This part contains one or more statements that implement specific business logic. It might contain only a NULL
statement.
3) Exception-handling part
This part contains the code that handles exceptions.

Example of Local Procedure
DECLARE
i number;
j number;
k number;
PROCEDURE findAdd(num1 IN number, num2 IN number, sum OUT number) IS
BEGIN
sum := num1 + num2;
END;
BEGIN
i:= 5;
j:= 5;
findAdd(i, j, k);
dbms_output.put_line(' The sum is : ' || k);
END;
/

CREATE OR REPLACE PROCEDURE calculate_salary(EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE) AS
increase FLOAT := 1.05; base_salary NUMBER := 10000; TENURE NUMBER; SALARY NUMBER;
EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE;
LAST_NAME EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
SELECT EMPLOYEE_ID, ROUND((SYSDATE - HIRE_DATE)/365,0), FIRST_NAME, LAST_NAME INTO EMP_ID,TENURE, FIRST_NAME,
LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = EMP_ID;
FOR i IN 0..TENURE LOOP
SALARY := base_salary * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('First Name: '||FIRST_NAME);
DBMS_OUTPUT.PUT_LINE ('Last Name: '||LAST_NAME);
DBMS_OUTPUT.PUT_LINE ('Salary: '||TO_CHAR(SALARY,'$99,999.99'));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('No Data Found!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error!');
END;
/
BEGIN
calculate_salary(1);
END;
/

Local procedure to return multiple values i.e. addition, subtraction, multiplication and
division of two numbers
Declare
p number(4);
q number(4);
r number(4);
s number(4);
procedure p1(a number, b number, c out number, d out number, e out number, f out number)
IS
begin
c:=a+b;
d:=a-b;
e:=a*b;
f:=a/b;
end;
begin p1(2, 3, p, q, r, s);
dbms_output.put_line(p||' '||q||' '||r||' '||s);
end;

IN-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:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
Minimum of (23, 45) : 23

Create Stored procedure example
In this example, we are going to insert record in user table. So you need to create user table first.
Table creation:
create table user(id number(10) primary key,name varchar2(100));
Now write the procedure code to insert record in user table.
Procedure Code:
create or replace procedure insertuser
(id IN NUMBER,
name IN VARCHAR2)
IS
begin
insert into user values(id,name);
end;
/
Output:
Procedure created.

PL/SQL program to call procedure
BEGIN
insertuser(101,'Rahul');
dbms_output.put_line('record inserted successfully');
END;
/
Now, see the "USER" table, you will see one record is inserted.
ID Name
101 Rahul
Syntax for drop procedure
DROP PROCEDURE procedure_name;
Example of drop procedure
DROP PROCEDURE pro1;

CREATE OR REPLACE PROCEDURE print_contact(
in_customer_id NUMBER
)
IS
r_contact contacts%ROWTYPE;
BEGIN
-- get contact based on customer id
SELECT *
INTO r_contact
FROM contacts
WHERE customer_id = p_customer_id;
-- print out contact's information
dbms_output.put_line( r_contact.first_name || ' ' ||
r_contact.last_name || '<' || r_contact.email ||'>' );
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( SQLERRM );
END;
If the procedure is compiled successfully, you will find the new
procedure under the Procedures node as shown below:
PL/SQL procedure example
Executing a PL/SQL procedure
EXECUTE procedure_name( arguments);
Code language: SQL (Structured Query Language) (sql)
Or
EXEC procedure_name( arguments);
Code language: SQL (Structured Query Language) (sql)
For example, to execute the print_contact procedure that prints
the contact information of customer id 100, you use the
following statement:
EXEC print_contact(100);

Functions
A function is same as a procedure except that it returns a value.
CREATE [OR REPLACE] FUNCTION function_name [parameters]
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
Function_name: specifies the name of the function.
[OR REPLACE] option allows modifying an existing function.
The optional parameter list contains name, mode and types of the parameters.
IN represents that value will be passed from outside and OUT represents that this
parameter will be used to return a value outside of the procedure.

The function must contain a return statement.
RETURN clause specifies that data type you are going to return from the function.
Function_body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a standalone function.

--Example to create function
create or replace function adder(n1 in number, n2 in number)
return number
is
n3 number(8);
begin
n3 :=n1+n2;
return n3;
end;
/
Call a function
DECLARE
n3 number(2);
BEGIN
n3 := adder(11,22);
dbms_output.put_line('Addition is: ' || n3);
END;
/
Output
Addition is: 33 Statement processed.

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(' Maximum of (23,45): ' || c);
END; /
Maximum of (23,45): 45 PL/SQL procedure successfully
completed.

functional dependency in database (1).pptx

More Related Content

Similar to functional dependency in database (1).pptx

Recently uploaded

functional dependency in database (1).pptx