1/1/25, 4:19 PM PLSQL Introduction – Day – 01 – NiC IT Academy

PLSQL Introduction – Day – 01

 Published On: 9 September 2024
 Last Updated: 16 September 2024 󰡡 No Responses

 Go Back

https://nicitacademy.com/plsql-introduction-day-1/ 1/6
1/1/25, 4:19 PM PLSQL Introduction – Day – 01 – NiC IT Academy


Welcome to Oracle PLSQL Sessions [Please watch the YouTube session
to understand the below notes]

Oracle PLSQL – Introduction

What is PLSQL?

In Oracle database management, PL/SQL is a procedural language extension to Structured Query Language (SQL). The
purpose of PL/SQL is to combine database language and procedural programming language. The basic unit in PL/SQL
is called a block and is made up of three parts: a declarative part, an executable part and an exception-building part.

Two blocks in PLSQL

1. Anonymous Block
2. Named Block — Stored as an object in DB
PROCEDURE
FUNCTION
PACKAGE
TRIGGER
1. Anonymous Block

===================
DECLARE
— Variable declaration — Optional
Begin
— Executable statements — Mandatory
Exception
— Exception handling code — Optional
end;
/
=========================================================
:= assignment operator
— Anonymous Block – very basic block
Begin
DBMS_OUTPUT.PUT_LINE(‘Welcome To Oracle PLSQL’);
END;
— PLSQL Anonymous Block to Add two numbers
declare
N1 number:=5;
N2 number:=10;
RESULT number;
begin
N1:=20;
https://nicitacademy.com/plsql-introduction-day-1/ 2/6
1/1/25, 4:19 PM PLSQL Introduction – Day – 01 – NiC IT Academy

N2:=15;
RESULT:=N1+N2;
DBMS_OUTPUT.PUT_LINE(RESULT); 
END;
— — PLSQL Anonymous Block to Add two numbers with decimal values
declare
number1 number(10,2):=20.5;
number2 number(10,2):=10.5;
result number(10,2);
begin
result := (number1+number2);
dbms_output.put_line(‘The addition of ‘||number1||’ and ‘||number2||’ are: ‘||result);
end;
— how to get user input in anonymous block
declare
number1 number(10,2):=&number1;
number2 number(10,2):=&number2;
result number(10,2);
begin
result := (number1+number2);
dbms_output.put_line(‘The addition of ‘||number1||’ and ‘||number2||’ are: ‘||result);
end;
— How to re-assin a different value for a variable
declare
number1 number(10,2):=50;
number2 number(10,2):=100;
result number(10,2);
begin
number1:=20;
number2:=30;
result := (number1+number2);
dbms_output.put_line(‘The addition of ‘||number1||’ and ‘||number2||’ are: ‘||result);
end;
— Constant variable declaration
declare
number1 constant number(10,2):=10.5;
number2 number(10,2):=20.5;
result number(10,2);
begin
number1:=20;
number2:=30;
result := (number1+number2);
dbms_output.put_line(‘The addition of ‘||number1||’ and ‘||number2||’ are: ‘||result);
end;
— Not null variable declaration
declare
number1 constant number(10,2):=0;
https://nicitacademy.com/plsql-introduction-day-1/ 3/6
1/1/25, 4:19 PM PLSQL Introduction – Day – 01 – NiC IT Academy

number2 number(10,2) not null:=0;

result number(10,2);
begin 
–number1:=20;
number2:=null;
result := (number1+number2);
dbms_output.put_line(‘The addition of ‘||number1||’ and ‘||number2||’ are: ‘||result);
end;
==================================================================================
— select operation in PLSQL
declare
v_ph_no varchar2(100);
v_job_id varchar2(100);
begin
select phone_number,job_id into v_ph_no,v_job_id from employee where employee_id=102;
dbms_output.put_line(‘The phone number of the employee is ‘||v_ph_no|| ‘ and job id is ‘||v_job_id);
end;
declare
v_salary number;
v_phone_number varchar2(30);
begin
select salary,phone_number into v_salary,v_phone_number from employees where employee_id=&emp_id;
dbms_output.put_line(‘The salary of the employee is: ‘||v_salary||’ Phone ‘||v_phone_number);
end;
==================================================================================
Write a PLSQL anonymous block that shows the usage of the WHILE loop to calculate the average of user entered
numbers and entry of more numbers are stopped by entering number 0?
DECLARE
n NUMBER;
avg NUMBER :=0 ;
sum NUMBER :=0 ;
count NUMBER :=0 ;
BEGIN
n := &enter_a_number;
WHILE(n<>0)
LOOP
count := count+1;
sum := sum+n;
n := &enter_a_number;
END LOOP;
avg := sum/count;
DBMS_OUTPUT.PUT_LINE(‘the average is’||avg);
END;
296 Total Visitors

https://nicitacademy.com/plsql-introduction-day-1/ 4/6
1/1/25, 4:19 PM PLSQL Introduction – Day – 01 – NiC IT Academy

Mr. Chandra 
15+ Yrs of IT Industry Experience.

PREVIOUS POST NEXT POST

 SQL Quiz – 01 PLSQL Introduction – Day – 02


Leave a Reply

Your email address will not be published. Required fields are marked *
Comment

Name* Email*

Website

Save my name and email in this browser for the next time I comment.

Submit

We’re an online job-based training institute. Our cutting-edge courses, crafted by industry experts, provide hands-on experience and practical skills
essential for today’s competitive job market.

NiC IT Academy
https://nicitacademy.com/plsql-introduction-day-1/ 5/6
1/1/25, 4:19 PM PLSQL Introduction – Day – 01 – NiC IT Academy
For enrolment support contact us at:
+91 99529 74013 
Call & WhatsApp (Both)

WhatsApp Support (Only)

+91 70100 80468
NiC IT Academy
An Online Software Training Institute

G1, Venkatesan St, Chrompet, Chennai - Tamil Nadu 600044 (IN)

Support Email
support@nicitacademy.com

Useful Links
Terms & Conditions
Refund Policy
Privacy Policy
Contact Us

©2024 NiC IT Academy - All Right Reserved.

Designed By - Innovtechsol

https://nicitacademy.com/plsql-introduction-day-1/ 6/6
1/1/25, 4:20 PM PLSQL Introduction – Day – 02 – NiC IT Academy

PLSQL Introduction – Day – 02

 Published On: 9 September 2024
 Last Updated: 16 September 2024 󰡡 No Responses

 Go Back

https://nicitacademy.com/plsql-introduction-day-2/ 1/6
1/1/25, 4:20 PM PLSQL Introduction – Day – 02 – NiC IT Academy

Constant in variable declaration: 

========================
Declare
DOB date:=’22-Dec-2015′;
Begin
DOB:=’23-Dec-2015′;
dbms_output.put_line(DOB);
end;
Declare
DOB constant date:=’22-Dec-2015′;
Begin
–DOB:=’23-Dec-2015′;
dbms_output.put_line(DOB);
end;
————————————————————-
Not Null in variable declaration

Declare
DOB date not null:=’22-Dec-2015′;
Begin
–DOB:=’23-Dec-2015′;
–DOB:=null;
dbms_output.put_line(DOB);
end;
————————————————————–
SELECT statement in PLSQL block

select column into variable from table where condition;

————————————————————–
DML

Insert update delete commit rollback;

—————————————————————
IF Statement

If condition then
statement;
end if;
—————————————————————-

https://nicitacademy.com/plsql-introduction-day-2/ 2/6
1/1/25, 4:20 PM PLSQL Introduction – Day – 02 – NiC IT Academy

IF condition1 THEN
[statements to execute when condition1 is TRUE…]

ELSE
[statements to execute when condition1 is FALSE]
END IF;
———————————————————————
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;
—————————————————-
declare
a boolean:=False;
begin
if a then
dbms_output.put_line(‘The condition is true’);
else
dbms_output.put_line(‘The condition is false’);
end if;
end;
————————————————————————-
DECLARE
a boolean := true;
b boolean := false;
BEGIN
IF (a AND b) THEN
dbms_output.put_line(‘Line 1 – Condition is true’);
else
dbms_output.put_line(‘Line 2 – Condition is true’);
END IF;
END;
————————————————————————-
DECLARE
a boolean := true;
b boolean := false;
BEGIN
https://nicitacademy.com/plsql-introduction-day-2/ 3/6
1/1/25, 4:20 PM PLSQL Introduction – Day – 02 – NiC IT Academy

IF (a AND b) THEN
dbms_output.put_line(‘Line 1’);
END IF; 
IF (a OR b) THEN
dbms_output.put_line(‘Line 2’);
END IF;
IF (NOT a) THEN
dbms_output.put_line(‘Line 3’);
ELSE
dbms_output.put_line(‘Line 4’);
END IF;
IF (NOT b) THEN
dbms_output.put_line(‘Line 5’);
ELSE
dbms_output.put_line(‘Line 6’);
END IF;
END;
—————————————————————————-
Declare
C number:=0;
begin
loop
dbms_output.put_line(‘Oracle PLSQL’);
C:=C+1;
exit when C=5;
end loop;
end;
—————————————————————————–
178 Total Visitors

Mr.Chandra
15+ Yrs of IT Industry Experience.

PREVIOUS POST NEXT POST

 PLSQL Introduction – Day – 01 PLSQL Introduction – Day – 03


Leave a Reply

https://nicitacademy.com/plsql-introduction-day-2/ 4/6
1/1/25, 4:20 PM PLSQL Introduction – Day – 02 – NiC IT Academy

Your email address will not be published. Required fields are marked * 
Comment

Name* Email*

Website

Save my name and email in this browser for the next time I comment.

Submit

We’re an online job-based training institute. Our cutting-edge courses, crafted by industry experts, provide hands-on experience and practical skills
essential for today’s competitive job market.

NiC IT Academy
For enrolment support contact us at:
+91 99529 74013
Call & WhatsApp (Both)

WhatsApp Support (Only)

+91 70100 80468
NiC IT Academy
An Online Software Training Institute

G1, Venkatesan St, Chrompet, Chennai - Tamil Nadu 600044 (IN)

Support Email
support@nicitacademy.com

https://nicitacademy.com/plsql-introduction-day-2/ 5/6
 Useful Links
1/1/25, 4:20 PM PLSQL Introduction – Day – 02 – NiC IT Academy

Terms & Conditions 

Refund Policy
Privacy Policy
Contact Us

©2024 NiC IT Academy - All Right Reserved.

Designed By - Innovtechsol

https://nicitacademy.com/plsql-introduction-day-2/ 6/6
1/1/25, 4:20 PM PLSQL Introduction – Day – 03 – NiC IT Academy

PLSQL Introduction – Day – 03

 Published On: 9 September 2024
 Last Updated: 16 September 2024 󰡡 No Responses

 Go Back

https://nicitacademy.com/plsql-introduction-day-3/ 1/7
1/1/25, 4:20 PM PLSQL Introduction – Day – 03 – NiC IT Academy

1) DML Transactions in PL/SQL: 

===========================
DML stands for Data Manipulation Language. These statements are mainly used to perform the manipulation activity. It
deals with the below operations.
Data Insertion
Data Update
Data Deletion
Data Selection
In PL/SQL, we can do the data manipulation only by using the SQL commands.

INSERT Statement:
================
BEGIN
INSERT INTO <table_name>(<column1 >,<column2>,…<column_n>)
VALUES(<valuel><value2>,…:<value_n>);
END;
Example:
========
begin
insert into customer values( 100,’NIC IT Academy’,9090909090,32,20);
COMMIT;
end;

INSERT INTO SELECT:

==================
BEGIN
INSERT INTO <table_name>(<columnl>,<column2>,…,<column_n>)
SELECT <columnl>,<column2>,.. <column_n> FROM <table_name2>;
END;
Example:
========
begin
insert into customer select * from customer1;
COMMIT;
end;
Update Statement:
=================
https://nicitacademy.com/plsql-introduction-day-3/ 2/7
1/1/25, 4:20 PM PLSQL Introduction – Day – 03 – NiC IT Academy

BEGIN
UPDATE <table_name>
SET <columnl>=<VALUE1>,<column2>=<value2>,<column_n>=<value_n> 
WHERE <condition that uniquely identifies the record that needs to be update>;
END;
Example:
========
begin
update customer set cust_name=’Welcome’; –where cust_id=100003;
COMMIT;
end;

DELETE Statement:
=================
BEGIN
DELETE
FROM
<table_name>
WHERE <condition that uniquely identifies the record that needs to be update>;
END;
SELECT Statement:
=================
BEGIN
SELECT <columnl>,..<column_n> INTO <vanable 1 >,. .<variable_n>
FROM <table_name>
WHERE <condition to fetch the required records>;
END;

Example code:
=============
DECLARE
v_cust_id NUMBER;
v_cust_name VARCHAR2(250);
v_mobile NUMBER;
v_city VARCHAR2(250);
BEGIN
insert into customer values( 100,’NIC IT Academy’,9090909090,32,20);
COMMIT;

https://nicitacademy.com/plsql-introduction-day-3/ 3/7
1/1/25, 4:20 PM PLSQL Introduction – Day – 03 – NiC IT Academy

Dbms_output.put_line(‘Values Inserted’);
update customer set cust_name=’Welcome’; –where cust_id=100003; 
COMMIT;
Dbms_output.put_line(‘Values Updated’);
DELETE from customer WHERE cust_id=100000;
COMMIT;
Dbms_output.put_line(‘Values Deleted’);
SELECT cust_id,cust_name INTO v_cust_id,v_cust_name FROM customer WHERE cust_id=100;
Dbms_output.put_line(‘Cust Details ‘||v_cust_id||’ name ‘||v_cust_name);
END;
/
2) % TYPE Attribute
syntax:
variable_name tablename.column_name%TYPE;

3) %ROWTYPE
Syntax:
variable_name table_name%rowtype;
—————————————————————-
create table customer
(
cust_id number(8),
cust_name varchar2(40),
dob date,
mobile_no number(10),
city varchar2(40)
);
insert into customer values(1000,’Arun’,to_date(’12/09/1985′,’mm/dd/yyyy’),9090909090,’Chennai’);
insert into customer values(1001,’John’,to_date(’01/27/1982′,’mm/dd/yyyy’),9090909093,’Pune’);
insert into customer values(1002,’Babu’,to_date(’06/23/1995′,’mm/dd/yyyy’),9090909089,’Hyd’);
commit;
select * from customer;
https://nicitacademy.com/plsql-introduction-day-3/ 4/7
1/1/25, 4:20 PM PLSQL Introduction – Day – 03 – NiC IT Academy

‘+91-8909909090’
declare 
v_mobile_no number(10);
begin
select mobile_no into v_mobile_no from customer where cust_id=1002;
dbms_output.put_line(‘The mobile number is ‘||v_mobile_no);
end;
alter table customer modify mobile_no varchar2(40);
rename customer to customer_bkp;
create table customer
(
cust_id number(8),
cust_name varchar2(40),
dob date,
mobile_no varchar2(20),
city varchar2(40)
);
insert into customer (select cust_id,cust_name,dob,’+91-‘||mobile_no,city from customer_bkp);
commit;
truncate table customer;
select * from customer;
declare
v_mobile_no hr.customer.mobile_no%type;
begin
select mobile_no into v_mobile_no from customer where cust_id=1002;
dbms_output.put_line(‘The mobile number is ‘||v_mobile_no);
end;
declare
v_customer hr.customer%rowtype;
begin
select * into v_customer from customer where cust_id=1002;
dbms_output.put_line(‘The customer name is ‘||v_customer.cust_name);
dbms_output.put_line(‘The customer dob is ‘||v_customer.dob);
end;

https://nicitacademy.com/plsql-introduction-day-3/ 5/7
1/1/25, 4:20 PM PLSQL Introduction – Day – 03 – NiC IT Academy

134 Total Visitors



Mr.Chandra
15+ Yrs of IT Industry Experience.

PREVIOUS POST NEXT POST

 PLSQL Introduction – Day – 02 PLSQL Introduction – Day – 04


Leave a Reply

Your email address will not be published. Required fields are marked *
Comment

Name* Email*

Website

Save my name and email in this browser for the next time I comment.

Submit

We’re an online job-based training institute. Our cutting-edge courses, crafted by industry experts, provide hands-on experience and practical skills
essential for today’s competitive job market.

https://nicitacademy.com/plsql-introduction-day-3/ 6/7
1/1/25, 4:20 PM PLSQL Introduction – Day – 03 – NiC IT Academy

NiC IT Academy
For enrolment support contact us at: 
+91 99529 74013
Call & WhatsApp (Both)

WhatsApp Support (Only)

+91 70100 80468
NiC IT Academy
An Online Software Training Institute

G1, Venkatesan St, Chrompet, Chennai - Tamil Nadu 600044 (IN)

Support Email
support@nicitacademy.com

Useful Links
Terms & Conditions
Refund Policy
Privacy Policy
Contact Us

©2024 NiC IT Academy - All Right Reserved.

Designed By - Innovtechsol

https://nicitacademy.com/plsql-introduction-day-3/ 7/7
1/1/25, 4:25 PM PLSQL Introduction – Day – 04 – NiC IT Academy

PLSQL Introduction – Day – 04

 Published On: 9 September 2024
 Last Updated: 16 September 2024 󰡡 No Responses

 Go Back

https://nicitacademy.com/plsql-introduction-day-4/ 1/9
1/1/25, 4:25 PM PLSQL Introduction – Day – 04 – NiC IT Academy

1) Conditional statement in PLSQL: 

===============================
— IF Statement
— Case Statement
IF Statement:
=============
IF condition then
statement;
end IF;
—————————————————————-
IF condition1 THEN
[statements to execute when condition1 is TRUE…]
ELSE
[statements to execute when condition1 is FALSE]
END IF;
———————————————————————
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;
—————————————————-
—Example 1:
declare
a boolean:=False;
begin
if a then
dbms_output.put_line(‘The condition is true’);
else
dbms_output.put_line(‘The condition is false’);
end if;
end;

https://nicitacademy.com/plsql-introduction-day-4/ 2/9
1/1/25, 4:25 PM PLSQL Introduction – Day – 04 – NiC IT Academy

declare
a number:=10;
b number:=5; 
begin
if a>b then
dbms_output.put_line(a ||’ is greater than ‘||b);
else
dbms_output.put_line(b ||’ is greater than ‘||a);
end if;
end;
————————————————————————-
—Example 2:
DECLARE
a boolean := true;
b boolean := false;
BEGIN
IF (a AND b) THEN
dbms_output.put_line(‘Line 1 – Condition is true’);
else
dbms_output.put_line(‘Line 2 – Condition is true’);
END IF;
END;
————————————————————————-
— Example 3:
DECLARE
a boolean := true;
b boolean := false;
BEGIN
IF (a AND b) THEN
dbms_output.put_line(‘Line 1’);
END IF;
IF (a OR b) THEN
dbms_output.put_line(‘Line 2’);
END IF;
IF (NOT a) THEN
dbms_output.put_line(‘Line 3’);
ELSE
dbms_output.put_line(‘Line 4’);
END IF;
IF (NOT b) THEN
dbms_output.put_line(‘Line 5’);
ELSE
dbms_output.put_line(‘Line 6’);
https://nicitacademy.com/plsql-introduction-day-4/ 3/9
1/1/25, 4:25 PM PLSQL Introduction – Day – 04 – NiC IT Academy

END IF;
END;

— Example 4:
declare
v_max_salary number(10);
begin
select max(salary) into v_max_salary
from employees;
dbms_output.put_line(‘The maximum salary’||v_max_salary);
IF v_max_salary>100000 then
delete from employees where salary=v_max_salary;
end if;
end;
===============================================================================
————————————————————-
CASE Statement
CASE selector
WHEN selector_value_1 THEN
statements_1
WHEN selector_value_1 THEN
statement_2
…
ELSE
else_statements
END CASE;
declare
vsalary number(10);
vlocal number(10):=&vemp_id;
begin
select salary into vsalary from employees where employee_id=vlocal;
case vsalary
when vsalary > 15000 then
dbms_output.put_line(‘Fair salary’);
when vsalary > 10000 and vsalary < 15000 then
dbms_output.put_line(‘Avg salary’);
else
dbms_output.put_line(vsalary||’ Low salary’);
end case;
end;

————————————————————-
https://nicitacademy.com/plsql-introduction-day-4/ 4/9
1/1/25, 4:25 PM PLSQL Introduction – Day – 04 – NiC IT Academy

–searched case:
 value is TRUE .
The searched CASE statement evaluates multiple Boolean expressions and chooses the first one whose
Expression whose value is evaluated once and used to select one of several alternatives. selector can have any PL/SQL
data type except BLOB , BFILE , or a user-defined type.
CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
…
WHEN condition_n THEN statements_n
[ ELSE
else_statements ]
END CASE;]
Example:

declare
vsalary number(10);
begin
select salary into vsalary from employees where employee_id=101;
case
when vsalary > 15000 and vsalary < 20000 then
dbms_output.put_line(‘Fair salary’);
when vsalary > 5000 and vsalary < 10000 then
dbms_output.put_line(‘low salary’);
else
dbms_output.put_line(vsalary ||’high salary’);
end case;
end;
===================================================================
— case statement in SQL
select cust_id,cust_name,mobile_no,dob,city,country,
case
when initcap(country)=’India’ then ‘+91-‘||mobile_no
when country=’USA’ then ‘+1-‘||mobile_no
when country=’Singapore’ then ‘+65-‘||mobile_no
else mobile_no
end new_phone_no from customer;
====================================================================
Simple CASE or searched CASE statement:

As a rule of thumb, use a searched CASE statement when you want to execute a sequence of statements based on the
results of multiple Boolean expressions and use a simple CASE statement when you want to execute a sequence of
statements based on the result of a single expression.
https://nicitacademy.com/plsql-introduction-day-4/ 5/9
1/1/25, 4:25 PM PLSQL Introduction – Day – 04 – NiC IT Academy

2) LOOPING Statements in PLSQL

–1. Simple Loop: 

Loop … end loop
syntax:
Loop
increment
exit when condition
end loop
–2. While Loop
while (condition) loop …… end loop;
–3. For Loop
For …..Loop… end loop;
——————————————————————
declare
c number:=1;
begin
loop
dbms_output.put_line(‘Welcome to PLSQL ‘||c);
c:=c+1;
exit when c>5;
end loop;
end;
— to print even numbers 0 to 10
declare
c number:=0;
begin
loop
c:=c+2;
dbms_output.put_line(c);
exit when c>=10;
end loop;
end;
declare
c number:=1;
begin
loop
dbms_output.put_line(‘Page ‘||c||’ of 50′);
c:=c+1;
exit when c>50;
end loop;
end;
—————————————————————-
WHILE Loop
While (condition)
loop
executable statements;
https://nicitacademy.com/plsql-introduction-day-4/ 6/9
1/1/25, 4:25 PM PLSQL Introduction – Day – 04 – NiC IT Academy

increment
end loop;
declare 
c number:=0;
begin
while(c<=5)
loop
dbms_output.put_line(‘Hello’);
c:=c+1;
end loop;
end;
—————————————————————————————-
FOR LOOP
FOR variable in [reverse] low value .. high value
loop
executable statements;
end loop;
declare
c1 number;
begin
for c1 in 1..5
loop
dbms_output.put_line(‘Hello’);
end loop;
end;
————————————————————————————
Example 1:
In this example, we are going to print number starting from 1 using Basic loop statement. Each number will be printed as
many times as its value. The upper limit of the series is fixed at the program declaration part. Let us learn how we can
use the label concept to achieve this. For that, we will execute the following code
DECLARE
a NUMBER:=0;
b NUMBER:=0;
upper_limit NUMBER :=4;
BEGIN
dbms_output.put_line(‘Program started.’ );
–outerloop
LOOP
a:=a+1;
b:=1;
–inner loop
LOOP
EXIT WHEN a > upper_limit;
dbms_output.put_line(a);
b:=b+1;
EXIT WHEN b>a;
END LOOP;
https://nicitacademy.com/plsql-introduction-day-4/ 7/9
1/1/25, 4:25 PM PLSQL Introduction – Day – 04 – NiC IT Academy

END LOOP;
dbms_output.put_line(‘Program completed.’);
END; 
/
124 Total Visitors

Mr.Chandra
15+ Yrs of IT Industry Experience.

PREVIOUS POST NEXT POST

 PLSQL Introduction – Day – 03 PLSQL Introduction – Day – 05


Leave a Reply

Your email address will not be published. Required fields are marked *
Comment

Name* Email*

Website

Save my name and email in this browser for the next time I comment.

Submit

https://nicitacademy.com/plsql-introduction-day-4/ 8/9
1/1/25, 4:25 PM PLSQL Introduction – Day – 04 – NiC IT Academy

We’re an online job-based training institute. Our cutting-edge courses, crafted by industry experts, provide hands-on experience and practical skills
essential for today’s competitive job market.

NiC IT Academy
For enrolment support contact us at:
+91 99529 74013
Call & WhatsApp (Both)

WhatsApp Support (Only)

+91 70100 80468
NiC IT Academy
An Online Software Training Institute

G1, Venkatesan St, Chrompet, Chennai - Tamil Nadu 600044 (IN)

Support Email
support@nicitacademy.com

Useful Links
Terms & Conditions
Refund Policy
Privacy Policy
Contact Us

©2024 NiC IT Academy - All Right Reserved.

Designed By - Innovtechsol

https://nicitacademy.com/plsql-introduction-day-4/ 9/9
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy

PLSQL Introduction – Day – 05

 Published On: 9 September 2024
 Last Updated: 16 September 2024 󰡡 No Responses

 Go Back

https://nicitacademy.com/plsql-introduction-day-5/ 1/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy

–What is cursor: 
cursor is defined as a private work area where the SQL statement(SELECT & DML) is executed.
—————————————————————————————
— select statement in PLSQL block
set serveroutput on;
clear screen;
declare
v_emp_salary hr.employees.salary%type;
begin
select salary into v_emp_salary from employees;– where employee_id=220;
dbms_output.put_line(‘The salary of the employee is ‘||v_emp_salary);
exception
when no_data_found then
dbms_output.put_line(‘No data found for this employee’);
when too_many_rows then
dbms_output.put_line(‘Many rows are returned from base table’);
end;
set SERVEROUTPUT ON
declare
v_salary employees.salary%type; — scalar variable
begin
select salary into v_salary from employees where employee_id =100;
dbms_output.put_line(‘The salary of the employee: ‘||v_salary);
end;
instead of using scalar variable,
————————————————————–
To get all values from base table we can go for 2 methods:
1. Composite variable
2. Cursor
–1. Composite variable:
========================
set SERVEROUTPUT ON
declare
type nt_salary_type is table of number(10);
nt_salary nt_salary_type := nt_salary_type();
begin
select salary bulk collect into nt_salary from employees;
for i in nt_salary.first..nt_salary.last
loop
dbms_output.put_line(nt_salary(i));
end loop;
end;
2. Cursor
=========
cursor is defined as a work area where the SQL statement is executed.
Two types of cursor:
https://nicitacademy.com/plsql-introduction-day-5/ 2/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy

================
1.Implicit cursor
2.explicit cursor 
1. Implicit cursor
These are created by default when DML statements like Insert, Update and Delete statements are executed.
Implicit cursor is a session cursor that is created and managed by Oracle. Oracle Opens an implicit cursor everytime you
run a DML or SELECT statement.
As a user we do not have a control on implicit cursor, but we can get information from its attributes.
Cursor Attributes:
================
cursorname%isopen
cursor_name%found
cursor_name%notfound
cursor_name%rowcount
–%FOUND Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open
cursor, cursor_name%FOUND returns NULL. Afterward, it returns TRUE if the last fetch returned a row, or FALSE if the
last fetch failed to return a row.
–%ISOPEN Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. If a cursor is open,
cursor_name%ISOPEN returns TRUE; otherwise, it returns FALSE.
–%NOTFOUND Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open
cursor, cursor_name%NOTFOUND returns NULL. Thereafter, it returns FALSE if the last fetch returned a row, or TRUE if
the last fetch failed to return a row.
–%ROWCOUNT Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened,
%ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT returns 0. Thereafter, it returns the number
of rows fetched so far. The number is incremented if the latest fetch returned a row.
set SERVEROUTPUT ON;
clear screen;
begin
update customer set mobile_no=’+91-‘||mobile_no where cust_id=&cust_id;
if sql%notfound then
dbms_output.put_line(‘No – rows are updated’);
else
dbms_output.put_line(SQL%ROWCOUNT||’- rows updated’);
end if;
end;
/
–Explicit cursor is a user defined cursor
1) Declare the cursor
2) Open the cursor
3) Fetch the cursor
4) Close the cursor
–How to declare a cursor:
–========================
https://nicitacademy.com/plsql-introduction-day-5/ 3/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy

CURSOR cursor_name
IS
SELECT_statement; 
1) cursor C1 — c1 is the cursor variable is select emp_name,salary from employees;
2) Open C1
3) Fetch cursor_name into variable1,variable2
4) Close c1

Example 1:
==========
declare
vemp_salary EMPLOYEES.salary%TYPE;
cursor c1 is select salary from employees; –Cursor declaration
begin
open c1; — Open cursor
fetch c1 into vemp_salary; — Fetch value from cursor pointer
dbms_output.put_line(vemp_salary);
fetch c1 into vemp_salary;
dbms_output.put_line(vemp_salary);
fetch c1 into vemp_salary;
dbms_output.put_line(vemp_salary);
close c1; — Close cursor
end;
Example 2:
==========
declare
vemp_salary EMPLOYEES.salary%TYPE;
cursor c1 is select salary from employees; –Cursor declaration
begin
open c1; — Open cursor
loop
fetch c1 into vemp_salary; — Fetch value from cursor pointer
exit when c1%notfound;
dbms_output.put_line(vemp_salary);
end loop;
https://nicitacademy.com/plsql-introduction-day-5/ 4/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy

dbms_output.put_line(‘—–***********———–‘);
dbms_output.put_line(‘Total no of recored fetched from base table-‘||c1%rowcount);
close c1; — Close cursor 
end;
Example 3:
==========
declare
vemp_name EMPLOYEES.FIRST_NAME%TYPE;
vemp_salary EMPLOYEES.salary%TYPE;
cursor c1 is select first_name,salary from employees;
begin
open c1;
loop
fetch c1 into vemp_name,vemp_salary;
exit when c1%notfound;
dbms_output.put_line(vemp_name||vemp_salary);
end loop;
close c1;
end;
–The following PL/SQL block uses %ROWCOUNT to fetch the names and salaries of the five highest-paid employees:
DECLARE
CURSOR c1 is
SELECT last_name, employee_id, salary FROM employees
ORDER BY salary DESC; — start with highest-paid employee
my_name employees.last_name%TYPE;
my_empno employees.employee_id%TYPE;
my_sal employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_name, my_empno, my_sal;
EXIT WHEN (c1%ROWCOUNT > 5) OR (c1%NOTFOUND);
dbms_output.put_line(‘Employee ‘ || my_name || ‘ (‘ || my_empno || ‘) makes ‘ || my_sal);
END LOOP;
CLOSE c1;
END;
/
The GV$OPEN_CURSOR (or the V$OPEN_CURSOR) view shows all the cursors that each user session has currently
opened and parsed, or cached.
You can issue the following query to identify the sessions with a high number of opened and parsed or cached cursors.
— Execute in ADMIN schema
select * from v$open_cursor where user_name=’HR’;
SQL> select saddr, sid, user_name, address,hash_value,sql_id, sql_text
from gv$open_cursor where sid in (select sid from v$open_cursor
group by sid having count(*) > &threshold);
— how many cursor we can open in a session?
select * from v$parameter where name=’open_cursors’;
https://nicitacademy.com/plsql-introduction-day-5/ 5/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy

——————————————-
CURSOR WITH RETURN CLAUSE
CURSOR cursor_name 
RETURN field%ROWTYPE
IS
SELECT_statement;
——————————————–
cursor FOR loop:
===============
Syntax:
FOR record_index in cursor_name
LOOP
{…statements…}
END LOOP;
————–Eg——————–
Declare
cursor c1 is select first_name,salary from employees;
begin
for record in c1 –record is a composite data type here
loop
dbms_output.put_line(record.first_name||record.salary);
end loop;
end;
ex1:
select emp_name, job_id,salry for particular dept using cursor for loop;
********************************************************************************
— Cursor for LOOP
DECLARE
cursor c1(no number) is select * from employees
where department_id = no;
tmp employees%rowtype;
BEGIN
FOR tmp IN c1(30) LOOP
dbms_output.put_line(‘EMP_No: ‘||tmp.employee_id);
dbms_output.put_line(‘EMP_Name: ‘||tmp.first_name);
dbms_output.put_line(‘EMP_Dept: ‘||tmp.department_id);
dbms_output.put_line(‘EMP_Salary:’||tmp.salary);
dbms_output.put_line(‘ ‘);
END Loop;
–CLOSE c1;
END;
——————————————
CURSOR WITH PARAMETERS:
CURSOR cursor_name (parameter_list)
IS
SELECT_statement;
declare
https://nicitacademy.com/plsql-introduction-day-5/ 6/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy

vemp_name EMPLOYEES.FIRST_NAME%TYPE;
vemp_salary EMPLOYEES.salary%TYPE;
cursor c_dept30 is select first_name,salary from employees where department_id=30; 
cursor c_dept60 is select first_name,salary from employees where department_id=60;
begin
open c_dept30;
loop
fetch c_dept30 into vemp_name,vemp_salary;
exit when c_dept30%notfound;
dbms_output.put_line(vemp_name||vemp_salary);
end loop;
close c_dept30;
open c_dept60;
loop
fetch c_dept60 into vemp_name,vemp_salary;
exit when c_dept60%notfound;
dbms_output.put_line(vemp_name||vemp_salary);
end loop;
close c_dept60;
end;
Declare
cursor c1(prm_dept_no number) is select salary from employees where department_id=prm_dept_no;
v_salary number(10);
begin
open c1(30);
dbms_output.put_line(‘—-This is the data for department_id 30—-‘);
loop
fetch c1 into v_salary;
exit when c1%notfound;
dbms_output.put_line(v_salary);
end loop;
close c1;
open c1(60);
dbms_output.put_line(‘—-This is the data for department_id 60—-‘);
loop
fetch c1 into v_salary;
exit when c1%notfound;
dbms_output.put_line(v_salary);
end loop;
close c1;
end;
Declare
cursor c1(cdept_no number) is select first_name,salary from employees where department_id=cdept_no;
begin
for record in c1(30)
loop
dbms_output.put_line(record.first_name||record.salary);
https://nicitacademy.com/plsql-introduction-day-5/ 7/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy

end loop;
end;
Declare 
cursor c1_emp is select salary from employees;
cursor c2_dept is select department_name from departments;
v_salary number(10);
v_department_name varchar2(100);
begin
open c1_emp;
dbms_output.put_line(‘—-This is the data for Employees table—-‘);
loop
fetch c1_emp into v_salary;
exit when c1_emp%notfound;
dbms_output.put_line(v_salary);
end loop;
close c1_emp;
open c2_dept;
dbms_output.put_line(‘—-This is the data for departments table—-‘);
loop
fetch c2_dept into v_department_name;
exit when c2_dept%notfound;
dbms_output.put_line(v_department_name);
end loop;
close c2_dept;
end;
— REF Cursor
A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value.
A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time.
One can use a Ref Cursor as target of an assignment, and it can be passed as parameter to other program units.
–Ref Cursors are opened with an ‘OPEN FOR’ statement. In most other ways they behave similar to normal cursors.
A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In
essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSOR s are represented
through the OracleRefCursor ODP.NET class.
A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result. The advantage that a
ref cursor has over a plain cursor is that is can be passed as a variable to a procedure or a function. The REF CURSOR
can be assigned to other REF CURSOR variables
This is a powerful capability in that the cursor can be opened, then passed to another block for processing, then
returned to the original block to be closed. The cursor variable can also be returned by a function and assigned to
another variable. The REF CURSOR variable is not a cursor, but a variable that points to a cursor. Before assigning a
cursor variable, a cursor type must be defined.
To declare a cursor variable, we can use the REF CURSOR is the data type.
–What is the syntax for Declaring Ref Cursor?
DECLARE
TYPE [cursor_variable_name] IS REF CURSOR [RETURN (return_type)];
PL/SQL has two forms of REF CURSOR types:
1. Strong typed REF CURSOR — Any Ref Cursor which has a fixed return type is called a Strong Ref Cursor.
2. Weak typed REF CURSOR — weak ref cursors are those which do not have any return type.
https://nicitacademy.com/plsql-introduction-day-5/ 8/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy

The following shows an example of a strong REF CURSOR.

DECLARE
TYPE customer_t IS REF CURSOR RETURN customer%ROWTYPE; 
c_customer customer_t;
This form of cursor variable called strong typed REF CURSOR because the cursor variable is always associated with a
specific record structure, or type
And here is an example of a weak typed REF CURSOR declaration that is not associated with any specific structure
DECLARE
TYPE customer_t IS REF CURSOR;
c_customer customer_t;
Since weak Ref Cursors do not have any fixed return type thus they are open to all SELECT statements. And this makes
them one of the most used Ref Cursors in PL/SQL.
— Weak typed REF CURSOR
declare
type ref_cursor IS REF CURSOR ;
rc_employees_list ref_cursor;
v_first_name varchar2(100);
v_dept_name varchar2(100);
begin
dbms_output.put_line(‘– This is Employees details–‘);
open rc_employees_list for select first_name from employees;
loop
fetch rc_employees_list into v_first_name;
exit when rc_employees_list%notfound;
dbms_output.put_line(v_first_name);
end loop;
dbms_output.put_line(‘The total employees are: ‘||rc_employees_list%rowcount);
close rc_employees_list;
dbms_output.put_line(‘– This is department details–‘);
open rc_employees_list for select department_name from departments;
loop
fetch rc_employees_list into v_dept_name;
exit when rc_employees_list%notfound;
dbms_output.put_line(v_dept_name);
end loop;
dbms_output.put_line(‘The total departments are: ‘||rc_employees_list%rowcount);
close rc_employees_list;
end;
— Strong typed REF CURSOR
declare
type ref_cursor is REF cursor return employees%rowtype;
rc_employees_list ref_cursor;
v_emp_details hr.employees%rowtype;
begin
dbms_output.put_line(‘These are employees details’);
open rc_employees_list for select * from employees;
loop
https://nicitacademy.com/plsql-introduction-day-5/ 9/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy

fetch rc_employees_list into v_emp_details;

exit when rc_employees_list%notfound;
dbms_output.put_line(‘The employee name: ‘||v_emp_details.first_name); 
dbms_output.put_line(‘The employee phone_number: ‘||v_emp_details.phone_number);
end loop;
dbms_output.put_line(‘The total employees are: ‘||rc_employees_list%rowcount);
close rc_employees_list;
end;
142 Total Visitors

Mr.Chandra
15+ Yrs of IT Industry Experience.

PREVIOUS POST NEXT POST

 PLSQL Introduction – Day – 04 PLSQL Introduction – Day – 06


https://nicitacademy.com/plsql-introduction-day-5/ 10/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy


Leave a Reply

Your email address will not be published. Required fields are marked *
Comment

Name* Email*

Website

Save my name and email in this browser for the next time I comment.

Submit

We’re an online job-based training institute. Our cutting-edge courses, crafted by industry experts, provide hands-on experience and practical skills
essential for today’s competitive job market.

NiC IT Academy
For enrolment support contact us at:
+91 99529 74013
Call & WhatsApp (Both)

WhatsApp Support (Only)

+91 70100 80468

https://nicitacademy.com/plsql-introduction-day-5/ 11/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 05 – NiC IT Academy

NiC IT Academy
An Online Software Training Institute

G1, Venkatesan St, Chrompet, Chennai - Tamil Nadu 600044 (IN)


Support Email
support@nicitacademy.com

Useful Links
Terms & Conditions
Refund Policy
Privacy Policy
Contact Us

©2024 NiC IT Academy - All Right Reserved.

Designed By - Innovtechsol

https://nicitacademy.com/plsql-introduction-day-5/ 12/12
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

PLSQL Introduction – Day – 06

 Published On: 10 September 2024
 Last Updated: 16 September 2024 󰡡 No Responses

 Go Back

https://nicitacademy.com/plsql-introduction-day-6/ 1/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

Stored procedure: 
==================
–Stored as a precompiled object
–Compile once and execute multiple times
–Provides reusability
–Procedure are explicitly executed by user
PROCEDURE
Function
Package
Trigger
2 Types of procedure
====================
1) Static procedure

–will not contain any arguments

–always display same output
2) Dynamic procedure

with argument variable

Procedure contains 2 parts:

1. Procedure specification
2. procedure body specification
create or replace procedure <name> (arg_variable IN datatype, arg variable2 datatype)
Is
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
AS|IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
IS – marks the beginning of the body of the procedure
—————————————————————-
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
https://nicitacademy.com/plsql-introduction-day-6/ 2/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

dbms_output.put_line(‘Welcome to PLSQL Session!’);

END;

exec greetings;
—————————————————————-
Drop procedure procedure_name;
—————————————————————-
How to execute procedure:
exec greetings;
execute greeetings;
begin
greetings;
end;
/
——————————————————————
CREATE OR REPLACE PROCEDURE greetings1(p_name IN varchar2)
AS
BEGIN
dbms_output.put_line(‘Hello’||p_name);
END;
exec greetings1(‘Welcome to PLSQL’);
—————————————————————————
CREATE OR REPLACE PROCEDURE total1(N1 IN number,N2 IN number,total out number)
AS
BEGIN
total:=N1+N2;
END;
declare
x number;
begin
total1(7,8,x);
dbms_output.put_line(‘Total: ‘||x);
end;
CREATE OR REPLACE PROCEDURE total1(N1 IN number,N2 IN number)
AS
total1 number;
BEGIN
total1:=N1+N2;
https://nicitacademy.com/plsql-introduction-day-6/ 3/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

dbms_output.put_line(‘Total: ‘||total1);
END;

exec total1(41,57);
—————————————————————————————-

create procedure INOUT_Multiplication(x IN OUT number)

As
begin
x:=x*5;
end;
declare
x number;
begin
x:=6;
INOUT_Multiplication(x);
dbms_output.put_line(‘multiplication: ‘||x);
end;
create or replace procedure total_salary(in_emp_id in number)
is
v_salary number(10);
begin
select salary+(salary*nvl(commission_pct,0)) into v_salary from
employees where employee_id=in_emp_id;
dbms_output.put_line(‘The Total salary of employee ‘|| in_emp_id ||’ is : ‘||v_salary);
end;
execute total_salary(165);
—————————————————————————————
create or replace procedure pro1
(emp_no in number,temp out employees%rowtype)
is
begin
select * into temp from employees where employee_id=emp_no;
end;
declare
emp_no number;
temp employees%rowtype;
begin
emp_no:=105;
pro1(emp_no,temp);
dbms_output.put_line(‘The employee details: ‘||temp.employee_id);
https://nicitacademy.com/plsql-introduction-day-6/ 4/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

dbms_output.put_line(‘The employee details: ‘||temp.first_name);

dbms_output.put_line(‘The employee details: ‘||temp.salary);
end; 
Data dictionary table:
User_procedures;
User_objects;
user_source;
select * from all_procedures where owner=’HR’;
select text from all_source where owner=’HR’ and type=’PROCEDURE’ and name=’ADD_JOB_HISTORY’;
————————————————————
Procedure with Cursor:
=====================
create or replace PROCEDURE get_employees
AS
emp_first_name employees.first_name%type;
emp_salary employees.salary%TYPE;
CURSOR c1 IS
SELECT first_name,salary FROM employees where rownum <=3;
BEGIN
open c1;
LOOP
FETCH c1 INTO emp_first_name, emp_salary;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_first_name);
end loop;
close c1;
end;
create or replace procedure get_employees(in_dept_id IN employees.department_id%type)
is
v_first_name employees.first_name%type;
v_salary employees.salary%type;
cursor c1 is select first_name,salary from employees where department_id=in_dept_id;
begin
open c1;
loop
fetch c1 into v_first_name, v_salary;
exit when c1%notfound;
DBMS_OUTPUT.PUT_LINE(v_first_name);
DBMS_OUTPUT.PUT_LINE(v_salary);
end loop;

https://nicitacademy.com/plsql-introduction-day-6/ 5/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

close c1;
end;

exec get_employees(60);
———————————————————————–
Write a procedure (oracle plsql) to do any one of the following:

(a) update the table course and set the fee of the input course name equal to fee of java course.
(b) insert a new row for given input course and set the fee lowest of all courses available in the table. Condition is: do (a)
if the input course name is already present in the table otherwise do (b) if the input course name is not in the table.
create table course(cid number primary key, cname varchar2(100), duration number, fee number);
insert into course (CID, CNAME, DURATION, FEE)
values (101, ‘java’, 30, 13000);
insert into course (CID, CNAME, DURATION, FEE)
values (102, ‘c’, 20, 5000);
insert into course (CID, CNAME, DURATION, FEE)
values (104, ‘oracle’, 20, 20000);
insert into course (CID, CNAME, DURATION, FEE)
values (105, ‘python’, 20, 30000);
insert into course (CID, CNAME, DURATION, FEE)
values (106, ‘sql’, 20, 1000);
Commit;
create or replace procedure proc_CourseFeeUpdateTry(coursename in course.cname%type,
java_fee out number) is
n_fee number;
j_fee number;
l_course_name course.cname%type;
begin
begin
select c.cname
into l_course_name
from course c
where c.cname = coursename;
exception
when no_data_found then
null;
end ;
if l_course_name = coursename then

https://nicitacademy.com/plsql-introduction-day-6/ 6/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

select t.fee into j_fee from course t where t.cname = ‘java’;

java_fee := j_fee;
update course t set t.fee = java_fee where t.cname = coursename; 
dbms_output.put_line(‘course fee updated’); — course was not added just updted
else
dbms_output.put_line(sqlerrm || ‘-‘ || sqlcode);
select min(t.fee) into n_fee from course t;
java_fee := n_fee;
insert into course values (103, coursename, 40, java_fee); — leave message
dbms_output.put_line(‘new course added’);
end if;
commit;
end;
————————————————————————————————–
— insert statement in procedure
select * from customer;
drop table customer;
create table customer
(
cust_id number,
cust_name varchar2(30),
mobile number(10),
age number,
city_id number
);
create or replace procedure insert_cust
(in_cust_id number, in_cust_name varchar2,in_mobile_no number,in_age number,in_city_id number)
as
begin
insert into customer values( in_cust_id,in_cust_name,in_mobile_no,in_age,in_city_id);
COMMIT;
end;
select * from customer;
truncate table customer;
exec insert_cust(100,’NIC IT Academy’,7010080468,30,23);
exec insert_cust(101,’NIC IT Academy’,7010080468,30,23);
====================================================================================
=====

https://nicitacademy.com/plsql-introduction-day-6/ 7/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

Name Null? Type

——— —– ————
CUST_ID NUMBER(8) 
CUST_NAME VARCHAR2(40)
DOB DATE
MOBILE_NO VARCHAR2(20)
CITY VARCHAR2(4
select * From customer;
create or replace procedure proc_insert_cust
(in_cust_id number, in_cust_name varchar2,in_mobile_no varchar2,in_dob varchar2,in_city_id varchar2)
as
begin
insert into customer values( in_cust_id,in_cust_name,in_mobile_no,in_dob,in_city_id);
COMMIT;
end;
exec proc_insert_cust(100,’NIC IT Academy’,to_date(’12-12-1990′,’mm-dd-yyyy’),’+91-9090909090′,’Chennai’);
exec insert_cust(101,’NIC IT Academy’,7010080468,30,23);

–Function in PLSQL
A function is a named PL/SQL Block which is similar to a procedure. The major difference between PL/SQL function or
procedure, function return always value where as procedure may or may not return value.
A function is the subprogram that returns a value when called. It is stored in database or declared within a block.
Functions can return more than one value via OUT parameter. Fuctions can accept default values
CREATE [OR REPLACE] FUNCTION function_name [(
parameter_1 [IN] [OUT] data_type,
parameter_2 [IN] [OUT] data_type,
parameter_N [IN] [OUT] data_type]
RETURN return_data_type
IS
–the declaration statements
BEGIN
— the executable statements
return return_value;
EXCEPTION
— the exception-handling statements
END;
—————————————————————————–

https://nicitacademy.com/plsql-introduction-day-6/ 8/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

An IN parameter is a read-only parameter. If the function tries to change the value of the IN parameters, the compiler
will issue an error message. You can pass a constant, literal, initialized variable, or expression to the function as the IN
parameter. 
An OUT parameter is a write-only parameter. The OUT parameters are used to return values back to the calling
program. An OUT parameter is initialized to default value of its type when the function begins regardless of its original
value before being passed to the function.
An IN OUT parameter is read and write parameter. It means the function reads value from an IN OUT parameter, change
its value and return it back to the calling program.
The function must have at least one RETURN statement in the execution section. The RETURN clause in the function
header specifies the data type of returned value.
————————————————————————————–
create or replace function f_count
return number
is
v_count number;
begin
select count(*) into v_count from employees;
return v_count;
end;
select f_count() from dual;
—————————————————————————————-
create or replace function f_count(v_department_id out number,v_count out number)
return number
is
v_count number;
v_department_id number;
begin
select department_id,count(*) into v_count from employees
where department_id is not null group by department_id;
return v_count;
end;
—————————————————————
create or replace function hello_function(p_name IN varchar2)
return varchar2
as
v_result varchar2(100);
begin
v_result:=’Hello ‘||p_name;
return v_result;
end hello_function;

https://nicitacademy.com/plsql-introduction-day-6/ 9/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

select HELLO_FUNCTION(‘chandra’) from dual;

———————————————————————————– 
create or replace function salary_hike(p_emp_id IN number)
return number
as
v_job employees.job_id%type;
v_sal employees.salary%type;
v_raise number(3,2);
v_new_sal number(6);
begin
select job_id,salary into v_job,v_sal from employees where employee_id=p_emp_id;
case
when v_job=’AD_VP’ then
IF v_sal < 20000 then v_raise:=0.50;
else v_raise:=0;
end IF;
when v_job=’SA_REP’ then
IF v_sal < 1500 then v_raise:=0.20;
else v_raise:=0;
end IF;
else
v_raise:=0;
end case;
if(v_raise >0) then
v_new_sal:=v_sal+v_sal*v_raise;
else
v_new_sal:=v_sal;
end if;
return v_new_sal;
end salary_hike;
select employee_id,first_name,salary,salary_hike(employee_id) from employees;
select salary_hike(103) from dual;
————————————————————————————–
employee_id IN
salary, >10000 10%
salary <10000 20%
create or replace function salary_incr(in_employee_id in number)
return number
is
v_salary number(10);
v_incr_salary number(10);
https://nicitacademy.com/plsql-introduction-day-6/ 10/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

begin
select salary into v_salary from employees where employee_id=in_employee_id;
if v_salary >10000 then 
v_incr_salary:=v_salary+(v_salary*0.1);
else
v_incr_salary:=v_salary+(v_salary*0.2);
end if;
return v_incr_salary;
end salary_incr;
select employee_id,salary,emp_fun(employee_id) from employees;
— alternative way
create or replace function salary_incr(in_employee_id in number)
return number
is
v_salary number(10);
begin
select case when salary >10000 then salary+(salary*0.1)
else salary+(salary*0.2) end into v_salary from employees where employee_id=in_employee_id;
return v_salary;
end salary_incr;
——————————————-
— write a PLSQL Function to to find given year is leap year or non leap year
create or replace function IS_LEAP_YEAR (nYr in number) return varchar2 is
v_day varchar2(2);
begin
select to_char(last_day(to_date( ’01-FEB-‘|| to_char(nYr), ‘DD-MON-YYYY’)), ‘DD’)
into v_day from dual;
if v_day = ’29’ then — if v_day = 29 then it must be a leap year, return TRUE
return ‘LEAP YEAR’;
else
return ‘NON LEAP YEAR’; — otherwise year is not a leap year, return false
end if;
end;
— =============================================
— write a PLSQL Function to select city of the given employee_id
CREATE OR REPLACE FUNCTION get_city(in_emp_id number)
return varchar2
as
result varchar2(50);
BEGIN
select l.city into result
https://nicitacademy.com/plsql-introduction-day-6/ 11/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

from employees e, departments d, locations l

where e.department_id=d.department_id and d.location_id=l.location_id
and e.employee_id = in_emp_id; 
return result;
END;
/
select employee_id,first_name,get_city(employee_id) from employees;
— Write a function to get employees details based on department_id
CREATE OR REPLACE FUNCTION fn_emp_per_dept
(f_dept_id IN NUMBER,f_emp_id OUT NUMBER,f_first_name OUT VARCHAR,f_last_name OUT VARCHAR,f_job_title OUT
VARCHAR)
RETURN BOOLEAN
AS
BEGIN
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_TITLE
INTO f_emp_id,f_first_name,f_last_name,f_job_title
FROM EMPLOYEES INNER JOIN JOBS ON JOBS.JOB_ID = EMPLOYEES.JOB_ID
INNER JOIN DEPARTMENTS ON DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
WHERE EMPLOYEES.DEPARTMENT_ID = f_dept_id;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE(‘Employee not found’);
RETURN FALSE;
END fn_emp_per_dept;
DECLARE
f_dept_id NUMBER;
f_emp_id NUMBER;
f_first_name VARCHAR(30) ;
f_last_name VARCHAR(30) ;
f_job_title VARCHAR(30);
f_return BOOLEAN;
BEGIN
f_dept_id := 10;
f_return := fn_emp_per_dept(f_dept_id,f_emp_id,f_first_name,f_last_name,f_job_title);
DBMS_OUTPUT.PUT_LINE(‘Employee_ID: ‘ || f_emp_id);
DBMS_OUTPUT.PUT_LINE(‘First Name: ‘ || f_first_name);
DBMS_OUTPUT.PUT_LINE(‘Last Name: ‘ || f_last_name);
DBMS_OUTPUT.PUT_LINE(‘Job: ‘ || f_job_title);
END;
/
———————————————————————————————————–

https://nicitacademy.com/plsql-introduction-day-6/ 12/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

Function overloading:
=====================

create or replace function ADD_NUM(A NUMBER, B NUMBER) return number
IS
c number;
BEGIN
c:=A+B;
return c;
END;
create or replace function ADD_NUM(A NUMBER, B NUMBER, C NUMBER) return number
IS
d number;
BEGIN
d:=A+B+C;
return d;
END;
select add_num(5+10) from dual;
— How to over come this?
We have to create a package.
create or replace package pkg_test as
function area (a in number) return number;
function area (a in number, b in number) return number;
end;
/
create or replace package body pkg_test as
function area(a in number)
return number is
begin
return a*a;
end;
function area(a in number, b in number)
return number is
begin
return a*b;
end;
end;
/
begin
dbms_output.put_line(pkg_test.area(3, 4));
dbms_output.put_line(pkg_test.area(5));

https://nicitacademy.com/plsql-introduction-day-6/ 13/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

end;
/

138 Total Visitors

Mr.Chandra
15+ Yrs of IT Industry Experience.

PREVIOUS POST NEXT POST

 PLSQL Introduction – Day – 05 PLSQL Introduction – Day – 07


Leave a Reply

Your email address will not be published. Required fields are marked *
Comment

Name* Email*

Website

Save my name and email in this browser for the next time I comment.

Submit

https://nicitacademy.com/plsql-introduction-day-6/ 14/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 06 – NiC IT Academy

We’re an online job-based training institute. Our cutting-edge courses, crafted by industry experts, provide hands-on experience and practical skills
essential for today’s competitive job market.

NiC IT Academy
For enrolment support contact us at:
+91 99529 74013
Call & WhatsApp (Both)

WhatsApp Support (Only)

+91 70100 80468
NiC IT Academy
An Online Software Training Institute

G1, Venkatesan St, Chrompet, Chennai - Tamil Nadu 600044 (IN)

Support Email
support@nicitacademy.com

Useful Links
Terms & Conditions
Refund Policy
Privacy Policy
Contact Us

©2024 NiC IT Academy - All Right Reserved.

Designed By - Innovtechsol

https://nicitacademy.com/plsql-introduction-day-6/ 15/15
1/1/25, 4:21 PM PLSQL Introduction – Day – 07 – NiC IT Academy

PLSQL Introduction – Day – 07

 Published On: 10 September 2024
 Last Updated: 16 September 2024 󰡡 No Responses

 Go Back

https://nicitacademy.com/plsql-introduction-day-7/ 1/11
1/1/25, 4:21 PM PLSQL Introduction – Day – 07 – NiC IT Academy

create or replace package first_package

as
procedure greetings1(p_name IN varchar2);
function hello_function(p_name IN varchar2)return varchar2;
end;
create or replace package body first_package as
PROCEDURE greetings1(p_name IN varchar2)
AS
BEGIN
dbms_output.put_line(‘Hello’||p_name);
END;
function hello_function(p_name IN varchar2)
return varchar2
as
v_result varchar2(100);
begin
v_result:=’Hello ‘||p_name;
return v_result;
end hello_function;
end;
execute FIRST_PACKAGE.GREETINGS1(‘chandrasekar’);
select hr.first_package.hello_function(‘Chandra’) from dual;
———————————————————————-
create or replace package first_package
as
procedure greetings1(p_name IN varchar2);
https://nicitacademy.com/plsql-introduction-day-7/ 2/11
1/1/25, 4:21 PM PLSQL Introduction – Day – 07 – NiC IT Academy

function emp_fun(v_emp_id in number)return number;

end;

create or replace package body first_package as
PROCEDURE greetings1(p_name IN varchar2)
AS
BEGIN
dbms_output.put_line(‘Hello’||p_name);
END;
function emp_fun(v_emp_id in number)
return number
as
v_salary number(8);
v_new_sal number(10);
begin
select salary into v_salary from employees where employee_id=v_emp_id;
if(v_salary >10000) then
v_new_sal:= v_salary +(v_salary*0.1);
else
v_new_sal:= v_salary +(v_salary*0.2);
end if;
return v_new_sal;
end emp_fun;
end;
execute FIRST_PACKAGE.GREETINGS1(‘chandrasekar’);
select employee_id,salary,FIRST_PACKAGE.emp_fun(employee_id) from employees;

———————————————————————
CREATE OR REPLACE PACKAGE PKG_OVERLOAD_ADD_NUMBERS
IS
PROCEDURE ADD_NUM(A NUMBER, B NUMBER);
PROCEDURE ADD_NUM(A NUMBER, B NUMBER, C NUMBER);
END;
CREATE OR REPLACE PACKAGE BODY PKG_OVERLOAD_ADD_NUMBERS
IS
PROCEDURE ADD_NUM(A NUMBER, B NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Sum of two numbers are: ‘||to_char(A+B));
END;
PROCEDURE ADD_NUM(A NUMBER, B NUMBER, C NUMBER)
IS
https://nicitacademy.com/plsql-introduction-day-7/ 3/11
1/1/25, 4:21 PM PLSQL Introduction – Day – 07 – NiC IT Academy

BEGIN
DBMS_OUTPUT.PUT_LINE(‘Sum of three numbers are: ‘||to_char(A+B+C));
END; 
END;

exec PKG_OVERLOAD_ADD_NUMBERS.add_num(5,6,7);
— Forward reference
DECLARE
procedure proc1 is
begin
dbms_output.put_line(‘This is procedure 1’);
end;
procedure proc2 is
begin
dbms_output.put_line(‘This is procedure 2’);
end;
begin
proc1;
end;
———————————————————
DECLARE
procedure proc1 is
begin
dbms_output.put_line(‘This is procedure 1’);
end;
procedure proc2 is
begin
proc1;
dbms_output.put_line(‘This is procedure 2’);
end;
begin
proc1;
end;
———————————————
DECLARE
procedure proc1 is
begin
https://nicitacademy.com/plsql-introduction-day-7/ 4/11
1/1/25, 4:21 PM PLSQL Introduction – Day – 07 – NiC IT Academy

dbms_output.put_line(‘This is procedure 1’);

end;

procedure proc2 is
begin
proc1;
dbms_output.put_line(‘This is procedure 2’);
end;
begin
proc2;
end;
———————————————-
DECLARE
procedure proc1 is
begin
proc2;
dbms_output.put_line(‘This is procedure 1’);
end;
procedure proc2 is
begin
proc1;
dbms_output.put_line(‘This is procedure 2’);
end;
begin
proc1;
end;
— error Procedure2 is not declared in this scope
———————————————-
set serveroutput on;
DECLARE
–procedure proc2;
procedure proc1 is
begin
dbms_output.put_line(‘This is procedure 1’);
–proc2;
end;
procedure proc2 is
begin
dbms_output.put_line(‘This is procedure 2’);
end;
https://nicitacademy.com/plsql-introduction-day-7/ 5/11
1/1/25, 4:21 PM PLSQL Introduction – Day – 07 – NiC IT Academy

begin
proc1;
end; 
example 2:
Create or replace package body test_package_fdec as
procedure int_proc; — forward declaration
procedure int_proc2
is
begin
dbms_output.put_line(‘this is int_proc2’);
int_proc;
end int_proc2;
procedure int_proc
is
begin
dbms_output.put_line(‘this is int_proc2′);
int_proc2;
end int_proc;
end test_package_fdec;
/
=====================================================================
create or replace package ABC as
x number:=10;
function1
procedure1
procedure2
end package;
global package variable
public fuctions
—————————————————————-
create package body ABC as
Y number; — Private variable
function function1()
.
.
end;

https://nicitacademy.com/plsql-introduction-day-7/ 6/11
1/1/25, 4:21 PM PLSQL Introduction – Day – 07 – NiC IT Academy

Procedure procedure1
a number; — local variable
begin 
.
.
end;
procedure P3 — Private procedure
.
.
end p3;
Procedure P2
p3;
end p2
end ABC;
==========================================================================
===========================================
— PRAGMA Autonomous transaction in Oracle:
===========================================
-This feature is available from Oracle 8i version
-It is an independent transaction and initiated by another transaction
-The main trasaction is temporarily suspended and Autonomous transaction -Invoked as child transaction
– Autonomous transaction must be committed or rollbacked
– It can be nested
The child transaction can run independently of its parent
The child transaction can commit/Rollback and parent txn resumes
The parent transaction can continue without affecting child transaction
create table customer (cust_id number(8), cust_name varchar2(30));
insert into customer values (1000,’Arun’);
insert into customer values (1001,’Sandeep’);
insert into customer values (1002,’John’);
insert into customer values (1003,’Rakesh’);
Commit;
select * from customer;

https://nicitacademy.com/plsql-introduction-day-7/ 7/11
1/1/25, 4:21 PM PLSQL Introduction – Day – 07 – NiC IT Academy

============================================================================
create table customer (cust_id number(8), cust_name varchar2(30)); 
insert into customer values (1000,’Arun’);
savepoint a;
insert into customer values (1001,’Sandeep’);
savepoint b;
insert into customer values (1002,’John’);
savepoint c;
insert into customer values (1003,’Rakesh’);
rollback to b;
select * from customer;
=====================================================
create table customer (cust_id number);
—Example 1:
insert into customer values(1000);
insert into customer values(1001);
select * from customer;
begin
for i in 1003..1010 loop
insert into customer values(i);
end loop;
rollback;
end;
—Example 2:
insert into customer values(1000);
insert into customer values(1001);
select * from customer;
declare
pragma autonomous_transaction;
begin
for i in 1003..1010 loop
insert into customer values(i);
end loop;

https://nicitacademy.com/plsql-introduction-day-7/ 8/11
1/1/25, 4:21 PM PLSQL Introduction – Day – 07 – NiC IT Academy

commit;
end;

rollback;
select * from customer;

====================================================================================
==

create table customer (cust_id number);

create or replace procedure proc_auto
is
PRAGMA autonomous_transaction;
begin
insert into customer values(100000);
commit;
end;
exec proc_auto;
select * from customer;
———————————————–
create table customer (cust_id number);
create or replace procedure proc_auto
is
PRAGMA autonomous_transaction;
begin
insert into customer values(100000);
commit;
end;
delete from customer;
exec proc_auto;
select * from customer;
declare
begin
insert into customer values(100001);
proc_auto();
https://nicitacademy.com/plsql-introduction-day-7/ 9/11
1/1/25, 4:21 PM PLSQL Introduction – Day – 07 – NiC IT Academy

commit;
end;

==========================================================

create table test (no1 number(3), no2 number(3));

insert into test values (1,2);
declare
pragma autonomous_transaction;
begin
insert into test values (3,4);
commit;
end;
insert into test values (5,6);
rollback;
======================================================
104 Total Visitors

Mr.Chandra
15+ Yrs of IT Industry Experience.

PREVIOUS POST NEXT POST

 PLSQL Introduction – Day – 06 PLSQL Introduction – Day – 08


Leave a Reply

Your email address will not be published. Required fields are marked *
Comment

https://nicitacademy.com/plsql-introduction-day-7/ 10/11
1/1/25, 4:21 PM PLSQL Introduction – Day – 07 – NiC IT Academy

Name* Email*


Website

Save my name and email in this browser for the next time I comment.

Submit

We’re an online job-based training institute. Our cutting-edge courses, crafted by industry experts, provide hands-on experience and practical skills
essential for today’s competitive job market.

NiC IT Academy
For enrolment support contact us at:
+91 99529 74013
Call & WhatsApp (Both)

WhatsApp Support (Only)

+91 70100 80468
NiC IT Academy
An Online Software Training Institute

G1, Venkatesan St, Chrompet, Chennai - Tamil Nadu 600044 (IN)

Support Email
support@nicitacademy.com

Useful Links
Terms & Conditions
Refund Policy
Privacy Policy
Contact Us

©2024 NiC IT Academy - All Right Reserved.

Designed By - Innovtechsol

https://nicitacademy.com/plsql-introduction-day-7/ 11/11
1/1/25, 4:22 PM PLSQL Introduction – Day – 08 – NiC IT Academy

PLSQL Introduction – Day – 08

 Published On: 10 September 2024
 Last Updated: 16 September 2024 󰡡 No Responses

 Go Back

https://nicitacademy.com/plsql-introduction-day-8/ 1/11
1/1/25, 4:22 PM PLSQL Introduction – Day – 08 – NiC IT Academy

==================== 
—Trigger in Oracle:
====================

— Trigger is a PLSQL named Bolck which is automatically fired when an event occured on the DB
— The event can be DDL, DML, system event
— Triggers code will be invoked automatically when an event occur
— Can’t manually trigger the event.
————————————————————————————————-
Purpose of Triggers:
— Auditing
— Enforcing complex integrity check
— Logging
— Enforcing security for transactions
— Prevent the invalid data on the txn
————————————————————————————————-
A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events
are INSERT, UPDATE, or DELETE statements on a table or view.
Oracle allows you to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is
issued against the associated table. These procedures are called database triggers.
Types of DML Triggers:

https://nicitacademy.com/plsql-introduction-day-8/ 2/11
1/1/25, 4:22 PM PLSQL Introduction – Day – 08 – NiC IT Academy

======================
Row level and Statement level trigger
==================== 
Firing Point: Before
====================
BEFORE INSERT TRIGGER
BEFORE UPDATE TRIGGER
BEFORE DELETE TRIGGER
====================
Firing Point: After
===================
AFTER INSERT TRIGGER
AFTER UPDATE TRIGGER
AFTER DELETE TRIGGER
—————————————————————-
BEFORE INSERT
It indicates that the trigger will fire before the INSERT operation is executed.
CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
— variable declarations
BEGIN
— trigger code
EXCEPTION
WHEN …
— exception handling
END trigger_name;
================================================================================
The syntax to a drop a trigger in Oracle in Oracle/PLSQL is:
DROP TRIGGER trigger_name;
—————————————————————-
The syntax for a disabling a Trigger in Oracle/PLSQL is:
ALTER TRIGGER trigger_name DISABLE;
——————————————————————
The syntax for a enabling a Trigger in Oracle/PLSQL is:
ALTER TRIGGER trigger_name ENABLE;
——————————————————————
The syntax for a disabling all Triggers on a table in Oracle/PLSQL is:
ALTER TABLE table_name DISABLE ALL TRIGGERS;
———————————————————————
The syntax to enable all triggers on a table in Oracle/PLSQL is:
ALTER TABLE table_name ENABLE ALL TRIGGERS;
———————————————————————
create table after_delete
(
https://nicitacademy.com/plsql-introduction-day-8/ 3/11
1/1/25, 4:22 PM PLSQL Introduction – Day – 08 – NiC IT Academy

user_name varchar2(20),
date_time date,
count number(10)); 
drop table after_delete;
create or replace trigger after_delete_trig
after delete on
declare
v_count number:=0;
begin
select count(*) into v_count from emp_join;
insert into after_delete values(user,sysdate,v_count);
end;
select * from after_delete;
select * from emp_join;
delete from emp_join where emp_id=10000;
——————————————————————————————————————
========================
Old and New Qualifiers:
=======================
old new
insert NA Yes
Update Yes Yes
Delete Yes NA
CREATE TABLE HR.customer_details
(
CUSTOMER_ID NUMBER(10,0),
FIRST_NAME VARCHAR2(30 BYTE),
MOBILE VARCHAR2(30 BYTE),
ADDRESS VARCHAR2(30 BYTE),
ZIPCODE NUMBER(6,0),
COUNTRY VARCHAR2(30 BYTE),
PRIMARY KEY (CUSTOMER_ID)
);
Insert into HR.customer_details (CUSTOMER_ID,FIRST_NAME,MOBILE,ADDRESS,ZIPCODE,COUNTRY) values
(100000,’Sravanthi’,’1234678906′,’rtretr retre’,400027,’India’);
Insert into HR.customer_details (CUSTOMER_ID,FIRST_NAME,MOBILE,ADDRESS,ZIPCODE,COUNTRY) values
(100001,’NIC IT Academy’,’7010080468′,’No.2 VGP nagar’,453254,’India’);
Insert into HR.customer_details (CUSTOMER_ID,FIRST_NAME,MOBILE,ADDRESS,ZIPCODE,COUNTRY) values
(100002,’Swathi’,’3545454544′,’Framingham’,1921,’USA’);
Insert into HR.customer_details (CUSTOMER_ID,FIRST_NAME,MOBILE,ADDRESS,ZIPCODE,COUNTRY) values
(100003,’Suresh’,’5678453233′,’G1, Balaji Nagar’,7745,’Australia’);
Insert into HR.customer_details (CUSTOMER_ID,FIRST_NAME,MOBILE,ADDRESS,ZIPCODE,COUNTRY) values
(100004,’Sridhar’,’7650099124′,’Pond Street’,77723,’CA’);
commit;
select * from customer_details;
delete from customer_details where first_name=’Swathi’;
commit;
https://nicitacademy.com/plsql-introduction-day-8/ 4/11
1/1/25, 4:22 PM PLSQL Introduction – Day – 08 – NiC IT Academy

create table customer_bkp_trig as select * from customer_details where 1=2;

alter table customer_bkp_trig add date_of_deletion date;
alter table customer_bkp_trig add who_deleted varchar2(30); 
select * from customer_bkp_trig;
create or replace trigger customer_trigger
before delete on customer_details
for each row
begin
insert into customer_bkp_trig values
(:old.customer_id,:old.first_name,:old.mobile,:old.address,
:old.zipcode,:old.country,sysdate,user);
end;
delete from customer_details where customer_id=100003;
rollback;
—————————————————————
DDL Trigger:
============
create table DDL_TRIGGER_TABLE_LOG
(
oracle_obj_name varchar2(50),
oracle_user varchar2(50),
ddl_execution_date date,
ora_system_event varchar2(50),
oracle_obj_type varchar2(50),
oracle_obj_owner varchar2(50)
);
—– DDL Trigger Syntax———————-
create or replace trigger trigger_name
after/before ddl/logon/logoff on database/schema
begin
executatble statement;
end;
———————————————-
create or replace trigger ddl_trigger
after ddl on schema
begin
insert into DDL_TRIGGER_TABLE_LOG
values(ora_dict_obj_name,ora_login_user,sysdate, ora_sysevent,ora_dict_obj_type,ora_dict_obj_owner);
end;
drop table customer_bkp;
select * from DDL_TRIGGER_TABLE_LOG;
— First drop the trigger then drop associated table
drop trigger ddl_trigger;
drop table DDL_TRIGGER_TABLE_LOG;
====================================================================================
=====
logon/logoff Trigger:
https://nicitacademy.com/plsql-introduction-day-8/ 5/11
1/1/25, 4:22 PM PLSQL Introduction – Day – 08 – NiC IT Academy

create table user_event_log

(
ora_login_user varchar2(30), 
ora_sysevent varchar2(30),
creation_date date);
create or replace trigger logon_ddl_trigger
after logon on schema
begin
insert into hr.user_event_log
values(ora_login_user,sysdate, ora_sysevent);
end;
select * from user_event_log;
====================================================================================
=====
Order of Trigger execution:
===================

create table student

(
sno number,
sname varchar2(50),
sdoj date,
sresult varchar2(30)
);
create sequence log_seq;
create or replace trigger st_bf_ins
before insert on student
begin
DBMS_OUTPUT.PUT_LINE(‘Statement level- before Insert :’||log_seq.nextval);
end;
create or replace trigger row_bf_ins
before insert on student
for each row
begin
https://nicitacademy.com/plsql-introduction-day-8/ 6/11
1/1/25, 4:22 PM PLSQL Introduction – Day – 08 – NiC IT Academy

DBMS_OUTPUT.PUT_LINE(‘Row level- before Insert :’||log_seq.nextval);

end;
create or replace trigger row_af_ins 
after insert on student
for each row
begin
DBMS_OUTPUT.PUT_LINE(‘Row level- After Insert :’||log_seq.nextval);
end;
create or replace trigger st_af_ins
after insert on student
begin
DBMS_OUTPUT.PUT_LINE(‘Statement level- after Insert :’||log_seq.nextval);
end;
set serveroutput on
begin
insert into student (sno,sname,sdoj,sresult) values(1,’Arun’,sysdate,’Pass’);
end;
select * from student;
———————————————————–
Instead of triggers:
———————

select * from employees;

desc employees;
select * from departments;
create or replace view emp_v
as select EMPLOYEE_ID, FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,
JOB_ID from employees where department_id=30;
select * from emp_v;
insert into emp_v values (300,’Arun’,’Kumar’,’Arun@gmail.com’,sysdate,’PU_CLERK’);
create or replace view emp_dept_v
as select e.EMPLOYEE_ID, e.FIRST_NAME,e.LAST_NAME,e.EMAIL,e.HIRE_DATE,e.JOB_ID,e.SALARY,e.DEPARTMENT_ID,
d.DEPARTMENT_NAME from employees e , departments d
where e.department_id=d.department_id;
select * from emp_dept_v;
insert into emp_dept_v values (301,’Arun’,’Kumar’,’Arun@gmail.com’,sysdate,’SA_REP’,50000,20,’Purchasing’);
https://nicitacademy.com/plsql-introduction-day-8/ 7/11
1/1/25, 4:22 PM PLSQL Introduction – Day – 08 – NiC IT Academy

create or replace trigger tr_emp_dept_vw_instead

instead of insert on emp_dept_v
declare 
check_exist number;
begin
select count(*) into check_exist from departments where department_id=:new.department_id;
if check_exist=0 then
insert into departments (department_id,department_name)
values(:new.department_id,:new.department_name);
end if;
select count(*) into check_exist from employees where employee_id=:new.employee_id;
if check_exist=0 then
insert into employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
values(:new.EMPLOYEE_ID,:new.FIRST_NAME,:new.LAST_NAME,:new.EMAIL,:new.HIRE_DATE,:new.JOB_ID,:new.SALAR
Y,:new.DEPARTMENT_ID);
end if;
end;
insert into emp_dept_v values (301,’Arun’,’Kumar’,’Arun1@gmail.com’,sysdate,’SA_REP’,50000,280,’Purchasing’);
select * from employees;
select * from departments;
——————————————————————
— Mutating table
The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the
“ORA-04091: table name is mutating, trigger/function may not see it.” message.
A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter
the ORA-04091 error if you have a row trigger that reads or modifies the mutating table. For example, if your trigger
contains a select statement or an update statement referencing the table it is triggering off of you will receive the error.

create table customer1

(
cust_id number,
cust_name varchar2(100)
);
create table customer2
(
cust_id number,
cust_name varchar2(100)
);
insert into customer1 values (200,’NIC IT Academy’);
Commit;

https://nicitacademy.com/plsql-introduction-day-8/ 8/11
1/1/25, 4:22 PM PLSQL Introduction – Day – 08 – NiC IT Academy

create or replace trigger mutate_trigger

after insert on customer2
for each row 
begin
update customer1 set cust_id =(select max(cust_id) from customer2);
end;
insert into customer2 values (100,’NIC’);
==========================
How to fix mutating error:
==========================
— Trigger will get fired, trying to update data in customer1
— 2nd transaction is depends on customer2 table
— but customer2 itself is not commited
— 2nd table is not yet committed, 1st table transaction depends on 2nd table
— we have to make these two transactions independently using automnomous transactions
create or replace trigger mutate_trigger
after insert on customer2
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update customer1 set cust_id =(select max(cust_id) from customer2);
commit;
end;
select * from customer1;
insert into customer2 values (100,’NIC’);
109 Total Visitors

Mr.Chandra
15+ Yrs of IT Industry Experience.

PREVIOUS POST NEXT POST

 PLSQL Introduction – Day – 07 PLSQL Introduction – Day – 09


Leave a Reply

https://nicitacademy.com/plsql-introduction-day-8/ 9/11
1/1/25, 4:22 PM PLSQL Introduction – Day – 08 – NiC IT Academy

Your email address will not be published. Required fields are marked * 
Comment

Name* Email*

Website

Save my name and email in this browser for the next time I comment.

Submit

We’re an online job-based training institute. Our cutting-edge courses, crafted by industry experts, provide hands-on experience and practical skills
essential for today’s competitive job market.

NiC IT Academy
For enrolment support contact us at:
+91 99529 74013
Call & WhatsApp (Both)

WhatsApp Support (Only)

+91 70100 80468
NiC IT Academy
An Online Software Training Institute

G1, Venkatesan St, Chrompet, Chennai - Tamil Nadu 600044 (IN)

Support Email
support@nicitacademy.com

https://nicitacademy.com/plsql-introduction-day-8/ 10/11
 Useful Links
1/1/25, 4:22 PM PLSQL Introduction – Day – 08 – NiC IT Academy

Terms & Conditions 

Refund Policy
Privacy Policy
Contact Us

©2024 NiC IT Academy - All Right Reserved.

Designed By - Innovtechsol

https://nicitacademy.com/plsql-introduction-day-8/ 11/11
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

PLSQL Introduction – Day – 09

 Published On: 10 September 2024
 Last Updated: 16 September 2024 󰡡 No Responses

 Go Back

https://nicitacademy.com/plsql-introduction-day-9/ 1/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

— scalar Data types: 

declare
v_first_name employees.first_name%type;
v_salary employees.salary%type;
begin
select first_name,salary into v_first_name,v_salary
from employees where employee_id=120;
dbms_output.put_line(‘The first name is: ‘||v_first_name);
dbms_output.put_line(‘The salary is: ‘||v_salary);
end;
PL/SQL lets you define two kinds of composite data types: collection and record.
A composite data type stores values that have internal components. You can pass entire composite variables to
subprograms as parameters, and you can access internal components of composite variables individually. Internal
components can be either scalar or composite. You can use scalar components wherever you can use scalar variables.
You can use composite components wherever you can use composite variables of the same type.
In a collection, the internal components always have the same data type, and are called elements. You can access each
element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection
variable, you either define a collection type and then create a variable of that type or use %TYPE.
In a record, the internal components can have different data types, and are called fields. You can access each field of a
record variable by its name, with this syntax: variable_name.field_name. To create a record variable, you either define a
RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.
Record Type:
============
What is Record Type?

A Record type is a complex data type which allows the programmer to create a new data type with the desired column
structure.
It is similar structures in C
It groups one or more column to form a new data type
These columns will have its own name and data type
A Record type can accept the data
Record type simply means a new data type. Once the record type is created, it will be stored as a new data type in the
database and the same shall be used to declare a variable in programs.
It will use the keyword ‘TYPE’ to instruct the compiler that it is creating the new data type.
It can be created at “database level” which can be stored as database objects, used all-over the database or it can be
created at the “subprogram levels”, which is visible only inside the subprograms.
The database level record type can also be declared for the table columns so that single column can hold the complex
data.
https://nicitacademy.com/plsql-introduction-day-9/ 2/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

The data in these data type can be accessed by referring to their variable_name followed by period operator (.)
followed by column_name i.e. ‘<record_type_variable_name>.<column_name>’

You can not create a RECORD type at schema level, To define a RECORD type specify its name and define its field.

Syntax:
=======
CREATE TYPE <type_name_db> IS RECORD
(
<column 1> <datatype>,
);
Syntax for declaration at subprogram level:
==========================================
DECLARE
TYPE <type_name> IS RECORD
(
<columnl> <datatype>,
);
BEGIN
<execution_section>;
END;
%RowType:
========
declare
v_emp_rec employees%rowtype;
begin
select * into v_emp_rec from employees where employee_id=120;
dbms_output.put_line(‘The first name is: ‘||v_emp_rec.first_name);
dbms_output.put_line(‘The phone_numebr is: ‘||v_emp_rec.phone_number);
end;
Record: TYPE

declare
type empl_record_type is record (first_name varchar2(30), salary number(8)); — definition
emp_rec empl_record_type; — declare
begin
emp_rec.first_name:=’John’;
emp_rec.salary:=’20000′;
dbms_output.put_line(emp_rec.first_name ||’ ,’||emp_rec.salary);
end;

https://nicitacademy.com/plsql-introduction-day-9/ 3/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

********************************************************************************
Record: TYPE with constructor:

declare
type emp_rec_type is record (first_name varchar2(30), salary number); — defintion
emp_rec emp_rec_type := emp_rec_type(‘John’,2000); — declare
begin
dbms_output.put_line(’emp_rec.first_name:=’||emp_rec.first_name);
end;
********************************************************************************
declare
type emp_rec_type is record (first_name varchar2(20),salary number(8));
emp_rec emp_rec_type;
begin
select first_name, salary into emp_rec from employees where employee_id=120;
dbms_output.put_line(’emp_rec.first_name is :=’||emp_rec.first_name);
dbms_output.put_line(’emp_rec.salary is :=’||emp_rec.salary);
end;
declare
type emp_rec_type is record (first_name varchar2(20),salary number(8));
emp_rec emp_rec_type;
begin
select first_name, salary into emp_rec from employees where employee_id=120;
dbms_output.put_line(’emp_rec.first_name is :=’||emp_rec.first_name||’ emp_rec.salary is :=’||emp_rec.salary);
end;
declare
type emp_rec_type is record (first_name varchar2(20),salary number(8));
emp_rec emp_rec_type;
begin
select first_name, salary into emp_rec from employees where employee_id=120;
dbms_output.put_line(’emp_rec.first_name is :=’||emp_rec.first_name||’ emp_rec.salary is :=’||emp_rec.salary);
dbms_output.put_line(’emp_rec.phone_number is :=’||emp_rec.phone_numer);
end;
— Not null constraint in record
set serveroutput on
declare
type emp_rec_type is record (
first_name varchar2(1000) not null:=’John’,
salary number(10));
emp_rec emp_rec_type;
begin
–emp_rec.first_name:=”;
emp_rec.salary:=100;
dbms_output.put_line(emp_rec.first_name ||’ ,’||emp_rec.salary);
https://nicitacademy.com/plsql-introduction-day-9/ 4/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

end;
/

———————————————————————————
Collections:
============
Collections are used in some of the most important performance optimization features of PL/SQL, such as
BULK COLLECT. SELECT statements that retrieve multiple rows with a single fetch, increasing the speed of data
retrieval.
FORALL. Inserts, updates, and deletes that use collections to change multiple rows of data very quickly.
Table functions. PL/SQL functions that return collections and can be called in the FROM clause of a SELECT statement.
A Collection is an ordered group of logically related elements.

Main Purpose of using collection is to improve performance.

Using collection we can load all records once from database into local memory and then we can perform operations on
it and save it back to database. It reduces calls to database.
Oracle provided 3 types of collection
======================================
VARRAY – variable array
Nested Table
Associative Array
Collections are used along with
===============================
Bulk collect
Forall
Table functions to improve performance.

You can also use collections to work with lists of data in your program that are not stored in database tables.
Let’s start by defining a common collections vocabulary:

Index value. The location of the data in a collection. Index values are usually integers but one type of collection can also
be strings.
Element. The data stored at a specific index value in a collection. Elements in a collection are always of the same type
(all of them are strings, dates, or records). PL/SQL collections are homogeneous.

https://nicitacademy.com/plsql-introduction-day-9/ 5/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

Sparse. A collection is sparse if there is at least one index value between the lowest and highest defined index values
that is not defined. For example, a sparse collection has an element assigned to index value 1 and another to index
value 10 but nothing in between. The opposite of a sparse collection is a dense one. 
Method. A collection method is a procedure or function that either provides information about the collection or changes
the contents of the collection. Methods are attached to the collection variable with dot notation (object-oriented
syntax), as in my_collection.FIRST.
Three types of collections
==========================
Collections have been enhanced in several ways through the years and across Oracle Database versions. There are
now three types of collections to choose from, each with its own set of characteristics and each best suited to a
different circumstance:
Associative array. The first type of collection available in PL/SQL, this was originally called a “PL/SQL table” and it can be
used only in PL/SQL blocks. Associative arrays can be sparse or dense and can be indexed by integer or string.
Nested table. The nested table can be used in PL/SQL blocks, in SQL statements, and as the data type of columns in
tables. Nested tables can be sparse but are almost always dense. They can be indexed only by integer. You can use the
MULTISET operator to perform set operations and to perform equality comparisons on nested tables.
Varray. The varray (variable-size array) can be used in PL/SQL blocks, in SQL statements, and as the data type of
columns in tables. Varrays are always dense and indexed by integer. When a varray type is defined, you must specify
the maximum number of elements allowed in a collection declared with that type.
The associative array is the most commonly used collection type, but nested tables have some powerful, unique
features (such as MULTISET operators) that can simplify the code needed to use your collection.
VARRAY – variable array
Nested Table
Associative Array
1. VARRAYS: variable size array

arrays
pre-defined size
index starts with 1
cannot delete elements by index.
2. Nested Tables:

— List
— Variable Size
— Index starts with 1
— can delete the element with in the array
— will not define maximum limit
3. Associative Arrays – Index by tables:

https://nicitacademy.com/plsql-introduction-day-9/ 6/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

— Indexing can be done with strings

— Map
— Key:value 
Steps in Collections:
=====================
1. Define
2. Declare
3. Intialize
4. Assign
5. Access
——————————————————————————–
VARRAY:
——-
declare
type v_array_type is varray(7) of varchar2(30);
address v_array_type:=v_array_type(null,null,null,null,null,null,null);
begin
address(1):=’G2′;
address(2):=’ABC flat’;
address(3):=’kalyan’;
address(4):=’Mumbai new’;
address(5):=’Mumbai’;
address(6):=’India’;
address(7):=’546354′;
dbms_output.put_line(‘The city f customer is: ‘||address(4));
end;

Declare
Type v_array_type is varray(8) of varchar2(40);
v_color v_array_type:=v_array_type(null,null,null,null,null,null,null,null);
begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’;
v_color(3) := ‘Blue’;
v_color(4) := ‘Green’;
v_color(5) := ‘Yellow’;
v_color(6) := ‘Gray’;
v_color(7) := ‘White’;
v_color(8) := ‘Orange’;
dbms_output.put_line(‘v_color(4) ‘||v_color(4));
end;
https://nicitacademy.com/plsql-introduction-day-9/ 7/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

Collection Methods:
===================

limit – maximum number of value of varray
count – how many elements stored in the varray(actually initialzed)
first – First index value (immutable) – could not delete any element
last – last index value
trim – last elements will be deleted
delete – delete all the elements in varray, can not delete specific element
extend – extend the number of elements
prior(n) – index of prior element
next(n) – index of next element
——————————————————————————–
Declare
Type v_array_type is varray(8) of varchar2(40);
v_color v_array_type:=v_array_type(null,null,null);
begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’;
v_color(3) := ‘Blue’;
dbms_output.put_line(‘v_color(1) ‘||v_color(1));
dbms_output.put_line(‘v_color.limit ‘||v_color.limit);
dbms_output.put_line(‘v_color.count ‘||v_color.count);
dbms_output.put_line(‘v_color.first ‘||v_color.first);
dbms_output.put_line(‘v_color.last ‘||v_color.last);
dbms_output.put_line(‘v_color.prior ‘||v_color.prior(3));
dbms_output.put_line(‘v_color.next ‘||v_color.next(3));
v_color.extend();
–v_color.extend(3);
dbms_output.put_line(‘v_color.count ‘||v_color.count);
dbms_output.put_line(‘v_color.next ‘||v_color.next(3));
v_color.trim();
–v_color.trim(2);
dbms_output.put_line(‘v_color.count ‘||v_color.count);
v_color.delete();
dbms_output.put_line(‘v_color.count ‘||v_color.count);
end;
——————————————————————————————-
Nested Tables:
https://nicitacademy.com/plsql-introduction-day-9/ 8/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

Declare 
Type v_nested_table_type is table of varchar2(40);
v_color v_nested_table_type:=v_nested_table_type(null,null,null,null,null,null,null,null);
begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’;
v_color(3) := ‘Blue’;
v_color(4) := ‘Green’;
v_color(5) := ‘Yellow’;
v_color(6) := ‘Gray’;
v_color(7) := ‘White’;
v_color(8) := ‘Orange’;
dbms_output.put_line(‘v_color(4) ‘||v_color(4));
end;
—————————————————
Nested table will not have limit:

Declare
Type v_nested_table_type is table of varchar2(40);
v_color v_nested_table_type:=v_nested_table_type(null,null,null);
begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’;
v_color(3) := ‘Blue’;
dbms_output.put_line(‘v_limit ‘||v_color.limit);
dbms_output.put_line(‘v_count ‘||v_color.count);
v_color.extend(4);
v_color(4) := ‘Green’;
dbms_output.put_line(‘v_color(4) ‘||v_color(4));
dbms_output.put_line(‘v_limit ‘||v_color.limit);
dbms_output.put_line(‘v_count ‘||v_color.count);
dbms_output.put_line(‘v_first_index ‘||v_color.first); — index of first element
dbms_output.put_line(‘v_last_index ‘||v_color.last);
end;
**************************************************************
Declare
Type v_nested_table_type is table of varchar2(40);
v_color v_nested_table_type:=v_nested_table_type(null,null,null);

https://nicitacademy.com/plsql-introduction-day-9/ 9/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’; 
v_color(3) := ‘Blue’;
dbms_output.put_line(‘v_count ‘||v_color.count);
v_color.extend(4);
v_color(4) := ‘Green’;
dbms_output.put_line(‘v_color(4) ‘||v_color(4));
dbms_output.put_line(‘v_limit ‘||v_color.limit); — will give null since it doesn’t have upper limit
dbms_output.put_line(‘v_count ‘||v_color.count);
dbms_output.put_line(‘v_first_index ‘||v_color.first); — index of first element
dbms_output.put_line(‘v_last_index ‘||v_color.last);
dbms_output.put_line(‘v_last_prior ‘||v_color.prior(3));
–v_color.delete(2);
v_color.delete;
dbms_output.put_line(‘v_count ‘||v_color.count);
end;
———————————————————————-
Declare
Type v_nested_table_type is table of varchar2(40);
v_color v_nested_table_type:=v_nested_table_type(null,null,null);
begin
v_color(1) := ‘Red’;
v_color(2) := ‘Black’;
v_color(3) := ‘Blue’;
v_color.extend(4);
v_color(4) := ‘Green’;
v_color.trim(2);
v_color.delete(2);
dbms_output.put_line(‘v_color(4) ‘||v_color(4));
dbms_output.put_line(‘v_limit ‘||v_color.limit);
dbms_output.put_line(‘v_count ‘||v_color.count);
dbms_output.put_line(‘v_first_index ‘||v_color.first); — index of first element
dbms_output.put_line(‘v_last_index ‘||v_color.last);
if v_color.exists(2) then
dbms_output.put_line(‘v_color(2) ‘||v_color(2));
else
dbms_output.put_line(‘Elements is not available’);
end if;
end;
———————————————————————————-
Associative array: Index by table

https://nicitacademy.com/plsql-introduction-day-9/ 10/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

‘plsql’: ‘Procedural language for SQL’

‘sql’ : ‘Structured Query language’

— In both VARRAY and Nested tables, the index is predefined and maintained by Oracle
— In Associative array the index and the elements will be defined by user
— Associative array is set of key value pair. Each key is unique and it is used to locate the elements
variable_name(index)
Declare
Type v_array_type is table of varchar2(40) index by varchar2(10);
v_color v_array_type;
begin
v_color(‘color1’) := ‘Red’;
v_color(‘color2’) := ‘Black’;
v_color(‘color3’) := ‘Blue’;
v_color(‘color4’) := ‘Green’;
dbms_output.put_line(‘v_color(3) ‘||v_color(‘color3’));
end;
******************************************************************
Declare
Type v_array_type is table of varchar2(40) index by varchar2(10);
v_color v_array_type;
begin
v_color(‘color1’) := ‘Red’;
v_color(‘color2’) := ‘Black’;
v_color(‘color3’) := ‘Blue’;
v_color(‘color4’) := ‘Green’;
dbms_output.put_line(‘v_color(3) ‘||v_color(‘color3’));
dbms_output.put_line(‘v_color(3).first ‘||v_color.first);
dbms_output.put_line(‘v_color(3).first ‘||v_color.last);
dbms_output.put_line(‘v_color(3).count ‘||v_color.count);
–v_color.delete;
–v_color.delete(‘color3’);
dbms_output.put_line(‘v_color(3).count ‘||v_color.count);
end;

Methods will not be available:

https://nicitacademy.com/plsql-introduction-day-9/ 11/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

Limit
Trim
Extend 
——————————————————————————————–
Bulk Collect and Bulk bind:

— If we process or select huge volume of data PLSQL block, performance issue

— Bulk collect — selecting the data table
— Bulk bind — processing the data
====================================================================================
=====
— select statement in PLSQL block
set serveroutput on;
clear screen;
declare
v_first_name hr.employees.first_name%type;
v_emp_salary hr.employees.salary%type;
begin
select first_name,salary into v_first_name,v_emp_salary from employees where employee_id=120;
dbms_output.put_line(‘The first_name of the employee is ‘||v_first_name);
dbms_output.put_line(‘The salary of the employee is ‘||v_emp_salary);
exception
when no_data_found then
dbms_output.put_line(‘No data found for this employee’);
when too_many_rows then
dbms_output.put_line(‘Many rows are returned from base table’);
end;
–– Using cursor
declare
vemp_name EMPLOYEES.FIRST_NAME%TYPE;
vemp_salary EMPLOYEES.salary%TYPE;
cursor c1 is select first_name,salary from employees;
begin
open c1;
loop
fetch c1 into vemp_name,vemp_salary;
exit when c1%notfound;
dbms_output.put_line(vemp_name||vemp_salary);
end loop;
close c1;
end;
https://nicitacademy.com/plsql-introduction-day-9/ 12/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

— Bulk collect and Composite variable: 

=======================================
set SERVEROUTPUT ON
declare
type nt_salary_type is table of number(10);
nt_salary nt_salary_type := nt_salary_type();
begin
select salary bulk collect into nt_salary from employees;
for i in nt_salary.first..nt_salary.last
loop
dbms_output.put_line(nt_salary(i));
end loop;
end;

105 Total Visitors

Mr.Chandra
15+ Yrs of IT Industry Experience.

PREVIOUS POST NEXT POST

 PLSQL Introduction – Day – 08 SQL Quiz – 02


https://nicitacademy.com/plsql-introduction-day-9/ 13/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy


Leave a Reply

Your email address will not be published. Required fields are marked *
Comment

Name* Email*

Website

Save my name and email in this browser for the next time I comment.

Submit

We’re an online job-based training institute. Our cutting-edge courses, crafted by industry experts, provide hands-on experience and practical skills
essential for today’s competitive job market.

NiC IT Academy
For enrolment support contact us at:
+91 99529 74013
Call & WhatsApp (Both)

WhatsApp Support (Only)

+91 70100 80468

https://nicitacademy.com/plsql-introduction-day-9/ 14/15
1/1/25, 4:18 PM PLSQL Introduction – Day – 09 – NiC IT Academy

NiC IT Academy
An Online Software Training Institute

G1, Venkatesan St, Chrompet, Chennai - Tamil Nadu 600044 (IN)


Support Email
support@nicitacademy.com

Useful Links
Terms & Conditions
Refund Policy
Privacy Policy
Contact Us

©2024 NiC IT Academy - All Right Reserved.

Designed By - Innovtechsol

https://nicitacademy.com/plsql-introduction-day-9/ 15/15