Practical 1:BASICS OF PL/SQL

Aim: Basic code of PL/SQL

THEORY:-
PL/SQL stands for Procedural Language extension of SQL. PL/SQL is a
combination of SQL along with the procedural features of programming languages.
PL/SQL Block consists of three sections:
– The Declaration section (optional).
– The Execution section (mandatory).
– The Exception (or Error) Handling section (optional).

SYNTAX:-
DECLARE
Declaration statements;
BEGIN
Executable statements;
END;
/

Questions:

1.Write A Pl/Sql Block To Print "Hello World!".

Code:
DECLARE
v_message VARCHAR2(20) := 'Hello World';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_message);
END;
/
Output:

2.write pl/sql block to declare a variable and assign it a value of 10 . then , print the
value .
Code:

DECLARE
v_number NUMBER := 10;

BEGIN
DBMS_OUTPUT.PUT_LINE('The value is: ' || v_number);

1 | A D VA N C E D ATA B A S E M A N A G E M E N T S Y S T E M
END;
/
Output:

3.write pl/sql block to find the maximum of the two numbers.

Code:
DECLARE
v_num1 NUMBER := 15;
v_num2 NUMBER := 25;
v_max NUMBER;
BEGIN
IF v_num1 > v_num2 THEN
v_max := v_num1; -- Assign v_num1 to v_max if it is greater ELSE
v_max := v_num2; -- Assign v_num2 to v_max otherwise
END IF;
DBMS_OUTPUT.PUT_LINE('The maximum is: ' || v_max);
END;
/
Output:

4.write pl/sql block to calculate the factorial of a number.

Code:
DECLARE
v_number NUMBER := 5;
v_factorial NUMBER := 1;
BEGIN
FOR i IN 1..v_number LOOP
v_factorial := v_factorial * i;
END LOOP;

DBMS_OUTPUT.PUT_LINE('The factorial of ' || v_number || ' is: ' ||

v_factorial);
END;
/
Output:

2 | A D VA N C E D ATA B A S E M A N A G E M E N T S Y S T E M
 5.write pl/sql block to check if a number is prime.
code:
DECLARE
v_number NUMBER := 29;
v_is_prime BOOLEAN := TRUE;
BEGIN
IF v_number < 2 THEN
v_is_prime := FALSE;
ELSE
FOR i IN 2..TRUNC(SQRT(v_number)) LOOP IF
MOD(v_number, i) = 0 THEN
v_is_prime := FALSE;
EXIT;
END IF;
END LOOP;
END IF;
IF v_is_prime THEN
DBMS_OUTPUT.PUT_LINE(v_number || ' is a prime number.');
ELSE
DBMS_OUTPUT.PUT_LINE(v_number || ' is not a prime number.');
END IF;
END;
/

Output:

6. Write a pl/sql block find nth fibonacci number.

code:
DECLARE

3 | A D VA N C E D ATA B A S E M A N A G E M E N T S Y S T E M
 a NUMBER := 0;
b NUMBER := 1;
fibonacci NUMBER;
n NUMBER :=5;
I NUMBER;
BEGIN
DBMS_OUTPUT.put_line('Fibonacci series is:');
DBMS_OUTPUT.put_line(a);
DBMS_OUTPUT.put_line(b);
FOR i IN 2…n LOOP
Fibonacci :=a+b;
a:=b;
b:=Fibonacci;
DBMS_OUTPUT.put_line(fibonacci);
END LOOP;
END;
/

Output:

7.write a pl/sql block to reverse a string.

code:
DECLARE
v_input_stringVARCHAR2(100):='HelloWorld';
v_reversed_string
VARCHAR2(100) := '';
v_length NUMBER;
BEGIN
v_length := LENGTH(v_input_string);
FOR i IN REVERSE 1..v_length LOOP
v_reversed_string := v_reversed_string ||
SUBSTR(v_input_string, i, 1);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Reversed string: ' ||
v_reversed_string);
END;
/
Output:

4 | A D VA N C E D ATA B A S E M A N A G E M E N T S Y S T E M
 8.write a pl/sql block to check if a number is even or odd .
code:
DECLARE
v_number NUMBER := 7;
BEGIN
IF MOD(v_number, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE(v_number || ' is an even number.');
ELSE
DBMS_OUTPUT.PUT_LINE(v_number || ' is an odd number.');
END IF;
END;
/
Output:

9.write a pl/sql block to find the sum of all number from 1 to n.

code:
DECLARE
v_n NUMBER := 10;
v_sum NUMBER := 0;
BEGIN
FOR i IN 1..v_n LOOP
v_sum := v_sum + i;
END LOOP;

DBMS_OUTPUT.PUT_LINE('The sum of all numbers

from 1 to ' || v_n || ' is: ' || v_sum);
END;
/
Output:

10. wirte a pl/sql block to find the length of a string.

code:
DECLARE
v_input_string VARCHAR2(100):= 'Hello, World!';
v_length NUMBER;

5 | A D VA N C E D ATA B A S E M A N A G E M E N T S Y S T E M
 BEGIN
v_length := LENGTH(v_input_string);
DBMS_OUTPUT.PUT_LINE('The length of the string is: ' || v_length);
END;
/
Output:

Practical 2:Control Structure

6 | A D VA N C E D ATA B A S E M A N A G E M E N T S Y S T E M
 Aim:To under the concept of Control structures with PL/SQL
Theory:Control structures are essential components of programming languages,
allowing developers to direct the flow of execution based on conditions and
repetitions.
* There are three types of control structures:
1. Conditional control
2. Iterative control
3. Sequential control

1. CONDITIONAL CONTROL

Syntax:
IF THEN ELSE STATEMENT
1)IF condition
THEN statement 1;
ELSE statement 2;
END IF;

2) IF condition 1
THEN statement 1;
statement 2;
ELSIF condtion2
THEN statement 3;
ELSE statement 4;
END IF;

3) IF condition1 THEN ELSE IF

condition2
THEN statement1;
END IF;
ELSIF condition3
THEN statement2;
END IF;
Case Statements (or switch statements) provide a streamlined way to execute
different blocks of code based on the value of a variable, making them ideal for
handling multiple potential cases.
Syntax:

7 | A D VA N C E D ATA B A S E M A N A G E M E N T S Y S T E M
 CASE < selector variable >
WHEN < expression 1 > THEN
< commands >
WHEN < expression 2 > THEN
< commands >
.
.
.
[ELSE
< commands
>] END
CASE;

2. ITERATIVE CONTROL
Iterative control statements are meant for a sequence of statements that has to be
repeated. In PL/SQL there are three ways for iterative statements:
1. LOOP and EXIT Syntax:
LOOP
< statements >
[EXIT WHEN < condition
>;] END LOOP;
2. WHILE LOOP
Syntax:
WHILE < condition >
LOOP
<
commands
> END
LOOP;
3. FOR LOOP
Syntax:
FOR variable_name IN

8 | A D VA N C E D ATA B A S E M A N A G E M E N T S Y S T E M
 [REVERSE] start..end
LOOP
< commands >
END LOOP;

3.SEQUENTIAL CONTROL
1. GOTO Statement
In PL/SQL, the GOTO statement is used to branch to a labeled statement within a
PL/SQL block. It is generally considered good practice to avoid using GOTO due
to its impact on code readability and maintainability.
Syntax:
<<label_name>>
DECLARE
/* Variable declarations*/
BEGIN
GOTO label_name; -- Jumps to the label
/* More statements */
<<label_name>>
END;
2. CONTINUE statement
The CONTINUE statement causes the loop to skip the remainder of its body and
immediately retest its condition prior to reiterating.
Syntax:
CONTINUE;
Questions:
1.Write PL/SQL code for an IF statement that prints a message if a user-provided
integer variable is greater than or equal to 10.
Code:
DECLARE
N1 INTEGER;
BEGIN
N1:=&N1;
IF N1 >= 10 THEN
dbms_output.put_line('The Value is ' || N1);

9 | A D VA N C E D ATA B A S E M A N A G E M E N T S Y S T E M
 END IF;
END;
/
Output:

2.Write PL/SQL code that prompts the user for two integer inputs, N1 and N2. If N1 is
greater than N2, print "N1 is greater than N2" along with their product. Otherwise,
print "N1 is not greater than N2" along with their sum.
CODE:
DECLARE
N1 INTEGER;
N2 INTEGER;
BEGIN
N1 := &N1;
N2 := &N2;
IF N1 > N2 THEN
dbms_output.put_line('N1 is greater than N2');
dbms_output.put_line('The product is ' || N1 * N2);
ELSE
dbms_output.put_line('N1 is not greater than N2');
dbms_output.put_line('The sum is ' || (N1 + N2));
END IF;
END;
/
Output:

10 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 3.Write PL/SQL code that prompts the user for two integer inputs, num1 and num2.
Compare the two numbers and print one of the following messages based on their
comparison:"NUM1 SMALLER THAN NUM2" if num1 is less than
num2"EQUAL" if num1 is equal to num2"NUM2 SMALLER THAN NUM1" if
num1 is greater than num2.
CODE:
DECLARE
num1 INTEGER;
num2 INTEGER;
BEGIN
num1 :=&num1;
num2 :=&num2;
IF num1 > num2 THEN
dbms_output.put_line('NUM1 SMALLER THAN NUM2');
ELSIF
num1 = num2 THEN
dbms_output.put_line('EQUAL');
ELSE
dbms_output.put_line('NUM2 SMALLER THAN NUM1');
end if;
END;
/
OUTPUT:

4.Write PL/SQL code that assigns an age to a variable and uses a CASE statement to
classify the age into one of the following categories:
• "Child" if the age is less than 13,
• "Teenager" if the age is between 13 and 19,
• "Young Adult" if the age is between 20 and 35,
• "Middle-Aged Adult" if the age is between 36 and 55,
• "Senior Adult" if the age is above 55,

11 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 • "Unknown Age Group" for any other cases.
CODE:
DECLARE
age NUMBER := 25;
BEGIN
CASE
WHEN age < 13 THEN
dbms_output.put_line('Child');
WHEN age BETWEEN 13 AND 19 THEN
dbms_output.put_line('Teenager');
WHEN age BETWEEN 20 AND 35 THEN
dbms_output.put_line('Young Adult');
WHEN age BETWEEN 36 AND 55 THEN
dbms_output.put_line('Middle-Aged Adult');
WHEN age > 55 THEN
dbms_output.put_line('Senior Adult');
ELSE
dbms_output.put_line('Unknown Age Group');
END CASE;
END;
/
Output:

12 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
5.Write a PL/SQL code to calculate total and percentage of marks of the students
in four subjects.
CODE:
declare
rno number(10);
s1 number(10);
s2 number(10);
s3 number(10);
s4 number(10);
tot number(10);
per number(4);
begin
rno:=&rno;
s1:=&s1;
s2:=&s2;
s3:=&s3;
s4:=&s4;
tot:=s1+s2+s3+s4;
per:=tot*0.25;
dbms_output.put_line('Regno s1 s2 s3 s4 total per');
dbms_output.put_line(rno||' '||s1||' '||s2||' '||s3||' '||s4||''||tot||' '||per);
end;
/
OUTPUT:

13 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 6.Write a PL/SQL Code Using the GOTO Statement
CODE:
DECLARE
a NUMBER := 1;
BEGIN
<<my_label>>
-- Displaying values from 1 to 5
WHILE a <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('Value of a: ' || a);
a := a + 1;
IF a = 4 THEN
GOTO my_label; -- Redirects control to the label
'my_label'
END IF;
END LOOP;
END;
/
Output:

14 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
7.What does the GOTO end_of_program; statement do in the following PL/SQL block?
CODE:
DECLARE
num NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Welcome to the Program');
num := -2;

IF num > 0 THEN

DBMS_OUTPUT.PUT_LINE('You entered a positive number.');
GOTO end_of_program;
ELSE
DBMS_OUTPUT.PUT_LINE('You entered either zero or a negative
number.');
END IF;
<<end_of_program>>
DBMS_OUTPUT.PUT_LINE('End of the Program');
END;
/
OUTPUT:

8.Write PL/SQL code to print odd number from 1 to 10 using CONTINUE statement.
CODE:
BEGIN
FOR num IN 1 .. 10 LOOP
IF MOD(num, 2) = 0 THEN
CONTINUE;
END IF;

15 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 DBMS_OUTPUT.PUT_LINE(num);
END LOOP;
END;
/
OUTPUT:

9.Write a PL/SQL code Loop Exit query. (WHILE LOOP)

CODE:
DECLARE
n_counter NUMBER := 1;
BEGIN
WHILE n_counter <= 5
LOOP
DBMS_OUTPUT.PUT_LINE( 'Counter : ' || n_counter );
n_counter := n_counter + 1;
EXIT WHEN n_counter = 3;
END LOOP;
END;
/
OUTPUT:

10.Write pl/sql program to define NOT NULL constraint

Code:
DECLARE
salary number(4);
dept varchar2(10) NOT NULL := 'HR Dept';
BEGIN
Salary := 200;
dbms_output.put_line(' The salary= '||salary ||' and Dept Name= '||dept);
END;

16 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
/
OUTPUT:

17 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 Practical 3: Procedure
Aim:- Concept of Procedure.
Theory:- PL/SQL procedures are reusable code blocks that perform specific actions or
logic within a database environment. They consist of two main components such as the
procedure header which defines the procedure name and optional parameters and the
procedure body which contains the executable statements implementing the desired
business logic.
Syntax:-
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
[declaration_section] BEGIN
< procedure_body >
END procedure_name;
/
Questions:
1: Write a PL/SQL Procedure (greeting) for Hello world?
Code:
CREATE OR REPLACE procedure greetingsAS
BEGIN
dbms_output.put_line(‘H
ello world’);
END;
/
execute greetings
Output:

2: Write a PL/SQL code for procedure with an

IN parameter(for user_name)?
Code:
CREATE OR REPLACE PROCEDURE greetings(user_name IN VARCHAR2)AS
BEGIN dbms_output.put_line('Hello ' || user_name);
END greetings ;
/

18 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 Output:

3.Write a PL/SQL block that defines a procedure findMin to find the minimum of two
numbers using an OUT parameter and then call this procedure to determine the
minimum of 23 and 45.
Code:
DECLARE
a number;
b number;
c number;
PROCEDURE findmin(x IN number,y IN number, z OUT number)IS
BEGIN IF x<y THEN z:=x; ELSE z:=y;
END IF;
END;
BEGIN
a:=23; b:=45;
findmin(a,b,c);
dbms_output.put_line('Minimum of(23,45):'||c);
END;
/
Output:

4.Write a PL/SQL block that defines a procedure squareNum using an IN OUT

parameter to compute the square of a number and then call this procedure to calculate
the square of 23.
Code:
DECLARE a number;
PROCEDURE squareNum(x IN OUT number)
IS
BEGIN
x:= x*x; END;
BEGIN
a:= 23;
squareNum(a); dbms_output.put_line ('Square of(23):' || a);

19 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
END;
/
Output:

5. Write a PL/SQL procedure named increase_salary that takes anemployee's

salary as an IN OUT parameter and a percentage increase as an IN parameter,
and increases the salary by the specified percentage.
Code:
CREATE OR REPLACE PROCEDURE increase_salary(salary IN OUT
NUMBER,increase_percentage IN NUMBER)
IS
BEGIN
salary := salary + (salary * increase_percentage/100);
END;
DECLARE employee_salary NUMBER := 5000;
percentage_increase NUMBER := 10;
BEGIN
increase_salary(employee_salary,percentage_increase);
dbms_output.put_line('The increase salary is: ' || employee_salary);
END;
/
Output:

6.How do you create a procedure with IN, OUT, and IN OUT parameters?/ "Create
a PL/SQL procedure calculate_discount that calculates a discount and final price
using IN, OUT, and IN OUT parameters.
Code:
CREATE OR REPLACE PROCEDURE calculate_discount (
price IN OUT NUMBER,
discount_pct IN NUMBER,
discount_amount OUT NUMBER
) IS
BEGIN
-- Calculate the discount amount
discount_amount := price * discount_pct / 100;

-- Adjust the price by subtracting the discount

20 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 price := price - discount_amount;
END calculate_discount;
/

DECLARE
original_price NUMBER := 1000; -- Example initial price
discount_percentage NUMBER := 15; -- Example discount percentage
discount_amount NUMBER; -- Variable to hold the discount
amount
BEGIN
calculate_discount(original_price, discount_percentage,
discount_amount);

DBMS_OUTPUT.PUT_LINE('Discount amount: ' || discount_amount);

DBMS_OUTPUT.PUT_LINE('Final price after discount: ' ||
original_price);
END;
/

7.Create a PL/SQL procedure greet_user_default with a default parameter value that

prints a welcome message, and demonstrate calling it with and without providing a
parameter.
Code:
CREATE OR REPLACE PROCEDURE greet_user_default (
username IN VARCHAR2 DEFAULT 'Guest'
) IS
BEGIN
-- Print the welcome message

21 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 DBMS_OUTPUT.PUT_LINE('Welcome, ' || username || '!');
END greet_user_default;
/

BEGIN
greet_user_default;
END;
/
Output:

8.Write a PL/SQL procedure add_student to insert a new student's details into the
student_enrollment table and demonstrate calling it to add a student with specified
attribtes.
Code:
CREATE TABLE student_enrollment (
student_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
enrollment_date DATE,
major VARCHAR2(100)
);
CREATE OR REPLACE PROCEDURE add_student (
p_student_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_enrollment_date IN DATE,
p_major IN VARCHAR2
) IS
BEGIN
INSERT INTO student_enrollment (student_id, first_name, last_name,
enrollment_date, major)
VALUES (p_student_id, p_first_name, p_last_name, p_enrollment_date,
p_major);

COMMIT; -- Commit the transaction to make the change permanent

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN

22 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 DBMS_OUTPUT.PUT_LINE('Error: A student with this ID already
exists.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
BEGIN
add_student(
p_student_id => 1, -- Use a unique ID
p_first_name => 'John',
p_last_name => 'Doe',
p_enrollment_date => TO_DATE('2024-09-23', 'YYYY-MM-DD'),
p_major => 'Computer Science'
);
END;
/

Output:

9.How can you use procedures to update data in a table?

Code:
CREATE TABLE student_enrollment (
student_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
course VARCHAR2(100)
);
CREATE OR REPLACE PROCEDURE update_student_course (
p_student_id IN NUMBER,
p_new_course IN VARCHAR2

23 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 ) IS
BEGIN
UPDATE student_enrollment
SET course = p_new_course
WHERE student_id = p_student_id;

-- Optional: Confirm the update

DBMS_OUTPUT.PUT_LINE('Course updated for student ID ' || p_student_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: No student found with ID ' ||
p_student_id);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END update_student_course;
/
BEGIN
update_student_course(101, 'Mathematics');
END;
/
Output:

10.How can you delete data using a PL/SQL procedure?

Code:
CREATE TABLE student_enrollment (
student_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
course VARCHAR2(100)
);
CREATE OR REPLACE PROCEDURE delete_student (
p_student_id IN NUMBER
) IS
BEGIN
-- Delete the student record with the specified student_id
DELETE FROM student_enrollment
WHERE student_id = p_student_id;

24 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 -- Optional: Confirm the deletion
DBMS_OUTPUT.PUT_LINE('Student with ID ' || p_student_id || ' deleted
successfully.');

-- Commit the transaction if you want to make the deletion permanent

COMMIT;

EXCEPTION
-- Handle any unexpected errors
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END delete_student;
/
BEGIN
-- Call the procedure to delete a student
delete_student(101);
END;
/
Output:

25 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 Practical 4:FUNCTIONS
Aim :Concept of Functions

Theory:
1 Definition:
• A function in PL/SQL is a named, self-contained block of code designed to
perform a specific task. It takes zero or more input parameters, executes its
logic, and returns a single value.
2 Parameters:
• Functions can accept parameters to provide inputs for their operations.
Parameters can be of different modes, including IN (input), OUT (output), and
IN OUT (both input and output). Parameters allow functions to be more flexible
and customizable based on the inputs provided at runtime.
3 Return Type:
• Each function is defined with a return type that specifies the type of value the
function will return, such as NUMBER, VARCHAR2, DATE, etc. The return
type must be explicitly declared in the function definition, and the function must
include a RETURN statement to return a value of this type.
4 Exception Handling:
• Functions can include exception handling to manage errors and exceptions that
occur during execution. By using EXCEPTION blocks, functions can handle
runtime errors gracefully, ensuring that the application can respond to
unexpected situations without crashing.
SYNTAX:-
CREATE [OR REPLACE] FUNCTION function_name

(parameter_name type [, …])

— This statement is must for functions
RETURN return_datatyp
{IS | AS}
BEGIN

— program code
[EXCEPTION exception_section;

END [function_name];
Questions:
1. Create a function to calculate the sum of two numbers.
Code:
CREATE OR REPLACE FUNCTION sum_of_two_numbers (p_num1 IN NUMBER,
p_num2 IN NUMBER) RETURN NUMBER IS

26 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
BEGIN
RETURN p_num1 + p_num2;
END;
DECLARE
v_sum NUMBER;
BEGIN
v_sum := sum_of_two_numbers(10, 15);
DBMS_OUTPUT.PUT_LINE('Sum of 10 and 15 is: ' || v_sum);
END;
Output:

2.Create
a function to return the greater of two numbers.
Code:
CREATE OR REPLACE FUNCTION greater_of_two_numbers ( num1 IN
NUMBER, num2 IN NUMBER) RETURN NUMBER
IS
BEGIN
IF num1 > num2 THEN RETURN num1;
ELSE
RETURN num2;
END IF;
END;
/
DECLARE greater
NUMBER;
BEGIN greater := greater_of_two_numbers(10,
20);
DBMS_OUTPUT.PUT_LINE('The greater number is: ' || greater); END;
/
Output:

27 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
3. Create a function to return 'Even' if a number is even, and 'Odd' if a number is odd.
Code:
CREATE OR REPLACE FUNCTION even_or_odd(num IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
IF MOD(num, 2) = 0 THEN RETURN
'Even';
ELSE
RETURN 'Odd';
END IF;
END;
/
DECLARE result
VARCHAR2(10);
BEGIN
result:= even_or_odd(7);
DBMS_OUTPUT.PUT_LINE('The number is: ' || result);
END;
/
Output:

4.Write PL/SQL code for a function named `get_square` that calculates the square of a
given number.
Code:
CREATE OR REPLACE FUNCTION get_square ( num
IN NUMBER
28 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 ) RETURN NUMBER IS BEGIN
RETURN num * num;
END;
/
DECLARE result
NUMBER;
BEGIN result
:=
get_square(5);
DBMS_OUTPUT.PUT_LINE('The square of 5 is: ' || result);
END;
/
Output:

5. Write PL/SQL code for a function named `get_square` that calculates the
square of a given number.
Code:
-- Creating or replacing the function to get the square of a number
CREATE OR REPLACE FUNCTION get_square (
num IN NUMBER -- Input number
) RETURN NUMBER IS
square_value NUMBER; -- Variable to hold the square of the number
BEGIN
-- Calculate the square of the number
square_value := num * num;
RETURN square_value; -- Return the square value
END get_square;
/

-- Anonymous block to call the function

DECLARE
result NUMBER; -- Variable to hold the result

29 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 BEGIN
-- Call the get_square function
result := get_square(5); -- Test with number 5

-- Display the result

DBMS_OUTPUT.PUT_LINE('The square is: ' || result);
END;

Output:

6. Create a function to convert a temperature from Celsius to Fahrenheit.

Code:
CREATE OR REPLACE FUNCTION celsius_to_fahrenheit (
celsius IN NUMBER -- Temperature in Celsius
) RETURN NUMBER IS
fahrenheit NUMBER; -- Variable to hold the converted temperature
BEGIN
-- Convert Celsius to Fahrenheit
fahrenheit := (celsius * 9/5) + 32;
RETURN fahrenheit; -- Return the Fahrenheit temperature
END celsius_to_fahrenheit;

DECLARE
result NUMBER; -- Variable to hold the result
BEGIN
-- Call the celsius_to_fahrenheit function
result := celsius_to_fahrenheit(25); -- Change the temperature to test
different inputs

-- Display the result

DBMS_OUTPUT.PUT_LINE('Temperature in Fahrenheit: ' || result);
END;
Output:

7. Create a function to calculate the factorial of a number.

Code:
CREATE OR REPLACE FUNCTION calculate_factorial (

30 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 num IN NUMBER -- Input number
) RETURN NUMBER IS
factorial_value NUMBER := 1; -- Variable to hold the factorial result
BEGIN
-- Check for non-negative input
IF num < 0 THEN
RETURN NULL; -- Return NULL for negative numbers
ELSIF num = 0 THEN
RETURN 1; -- Factorial of 0 is 1
ELSE
-- Calculate factorial for positive numbers
FOR i IN 1..num LOOP
factorial_value := factorial_value * i;
END LOOP;
END IF;
RETURN factorial_value; -- Return the factorial value
END calculate_factorial;
/

DECLARE
result NUMBER; -- Variable to hold the result
BEGIN
-- Call the calculate_factorial function
result := calculate_factorial(5); -- Change the number to test different
inputs

-- Display the result

DBMS_OUTPUT.PUT_LINE('Factorial: ' || result);
END;
Output:

8. Write PL/SQL for function which calculates the nth Fibonacci number using
recursion.
Code:
CREATE OR REPLACE FUNCTION fibonacci (
n IN NUMBER -- The position of the Fibonacci sequence
) RETURN NUMBER IS
BEGIN
-- Base cases
IF n < 0 THEN
RETURN NULL; -- Return NULL for negative input
ELSIF n = 0 THEN

31 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 RETURN 0; -- F(0) = 0
ELSIF n = 1 THEN
RETURN 1; -- F(1) = 1
ELSE
-- Recursive case: F(n) = F(n-1) + F(n-2)
RETURN fibonacci(n - 1) + fibonacci(n - 2);
END IF;
END fibonacci;
/

DECLARE
result NUMBER; -- Variable to hold the result
BEGIN
-- Call the fibonacci function
result := fibonacci(10); -- Change the number to test different inputs

-- Display the result

DBMS_OUTPUT.PUT_LINE('Fibonacci number: ' || result);
END;
/
Output:

32 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 Practical 5: Cursors

Aim: CURSORS
Theory:- A cursor contains information on a select statement and the rows of data
accessed by it. A cursor is used to referred to a program to fetch and process the
rows returned by the SQL statement, one at a time. There are two types of cursors:
• Implicit Cursors • Explicit Cursors IMPLICIT CURSORS: • The implicit cursors
are automatically generated by Oracle while an SQL statement is executed, if you
don't use an explicit cursor for the statement. • These are created by default to
process the statements when DML statements like INSERT, UPDATE, DELETE
etc. are executed. • There are 4 attributes : 1. %FOUND: Returns TRUE if an
INSERT, UPDATE, or DELETE statement affected one or more rows or a
SELECT INTO statement returned one or more rows.Otherwise, it returns FALSE.
2. %NOTFOUND: The logical opposite of %FOUND. It returns TRUE if an
INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO
statement returned no rows. Otherwise, it returns FALSE. 3. %ISOPEN: Always
returns FALSE for implicit cursors, because Oracle closes the SQL cursor
automatically after executing its associated SQL statement. 4. %ROWCOUNT:
Returns the number of rows affected by an INSERT, UPDATE, or DELETE
statement, or returned by a SELECT INTO statement Explicit Cursors in
PL/SQL:An explicit cursor in PL/SQL is a mechanism that allows you to fetch and
process multiple rows returned by a query one at a time. It gives the programmer
more control over the execution of SQL queries and data retrieval compared to
implicit cursors. • Declare the Cursor: Define the SQL query associated with the
cursor. • Open the Cursor: Execute the query and create a pointer to the result set. •
Fetch Rows: Retrieve rows from the result set, one at a time, into PL/SQL
variables. • Close the Cursor: Release the cursor after all rows are processed.
1. If the Employees table initially has 5 rows and all employees have their salaries
increased by 5000, what output will this block produce? Explain why.
Code:
CREATE TABLE Employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER(10, 2)
);

INSERT INTO Employees (employee_id, first_name, last_name, salary)

VALUES (1, 'John', 'Doe', 60000);
INSERT INTO Employees (employee_id, first_name, last_name, salary)
VALUES (2, 'Jane', 'Smith', 65000);
INSERT INTO Employees (employee_id, first_name, last_name, salary)
VALUES (3, 'Alice', 'Johnson', 70000);
COMMIT;

DECLARE
total_rows NUMBER(2);

33 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 BEGIN
UPDATE Employees
SET salary = salary + 5000;

total_rows := SQL%ROWCOUNT; -- Get the number of updated rows

IF total_rows = 0 THEN
DBMS_OUTPUT.PUT_LINE('No employees updated');
ELSE
DBMS_OUTPUT.PUT_LINE(total_rows || ' employees updated');
END IF;

COMMIT; -- Commit the changes

END;
/

SELECT * FROM Employees;

Output:

2. Write a PL/SQL block to update the salary of employees in the Employees table
by 10% and print the number of rows affected using an implicit cursor.
Code:
CREATE TABLE Employees (
employee_id NUMBER(10) PRIMARY KEY,
last_name VARCHAR2(50),
salary NUMBER(10, 2),
department_id NUMBER(10)
);
INSERT INTO Employees (employee_id, last_name, salary, department_id)
VALUES (101, 'Smith', 60000, 30);
INSERT INTO Employees (employee_id, last_name, salary, department_id)
VALUES (102, 'Johnson', 65000, 30);

34 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
INSERT INTO Employees (employee_id, last_name, salary, department_id)
VALUES (103, 'Williams', 70000, 40);
COMMIT; -- Commit the insertions

BEGIN
UPDATE Employees
SET salary = salary * 1.10;

IF SQL%ROWCOUNT > 0 THEN

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');
ELSE
DBMS_OUTPUT.PUT_LINE('No rows updated.');
END IF;
END;
/
SELECT * FROM Employees;
Output:

3. Describe what happens when the cursor c_emp_cursor is opened, fetched, and
closed in the PL/SQL block. If the employees table has 3 employees in department
30, what output would this block produce?
Code:
-- Step 1: Create the employees table
CREATE TABLE employees (
employee_id NUMBER(10) PRIMARY KEY,
last_name VARCHAR2(50),
department_id NUMBER(10)
);

-- Step 2: Insert sample data into the employees table

INSERT INTO employees (employee_id, last_name, department_id) VALUES
(101, 'Smith', 30);

35 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
INSERT INTO employees (employee_id, last_name, department_id) VALUES
(102, 'Johnson', 30);
INSERT INTO employees (employee_id, last_name, department_id) VALUES
(103, 'Williams', 40);
INSERT INTO employees (employee_id, last_name, department_id) VALUES
(104, 'Brown', 30);
INSERT INTO employees (employee_id, last_name, department_id) VALUES
(105, 'Davis', 40);
COMMIT; -- Commit the insertions

-- Step 3: PL/SQL block to fetch and display employee details for department 30
SET SERVEROUTPUT ON; -- Enable output in SQL*Plus or SQL Developer

DECLARE
CURSOR c_emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id = 30;

v_empno employees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
BEGIN
OPEN c_emp_cursor; -- Open the cursor

LOOP
FETCH c_emp_cursor INTO v_empno, v_lname; -- Fetch data into variables
EXIT WHEN c_emp_cursor%NOTFOUND; -- Exit if no more rows to fetch

-- Display the fetched employee details

DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
END LOOP;

CLOSE c_emp_cursor; -- Close the cursor

END;
/
Output:

4. Write a PL/SQL block that inserts a new department into the Departments table
and displays a confirmation message showing the
number of rows inserted.

Code:

36 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
CREATE TABLE Departments (
department_id NUMBER(3) PRIMARY KEY,
department_name VARCHAR2(50) NOT NULL
);

SET SERVEROUTPUT ON;

BEGIN
INSERT INTO Departments (department_id, department_name)
VALUES (101, 'Research and Development');
IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' row inserted into
Departments.');
ELSE
DBMS_OUTPUT.PUT_LINE('No rows inserted.');
END IF;
END;
/
select * from Departments;

BEGIN
INSERT INTO Departments (department_id, department_name)
VALUES (101, 'Research and Development');
IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' row inserted into
Departments.');
ELSE
DBMS_OUTPUT.PUT_LINE('No rows inserted.');
END IF;
END;
/
select * from Departments;

Output:

5.Using Cursor Variables.

Code:
-- Step 1: Create the emp table
CREATE TABLE emp (
emp_no CHAR(4),
emp_name VARCHAR2(10),
emp_sal NUMBER(8,2),

37 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 emp_addr VARCHAR2(10)
);

-- Step 2: Insert sample data into the emp table

INSERT INTO emp (emp_no, emp_name, emp_sal, emp_addr) VALUES
('1001', 'John', 25000, 'NY');
INSERT INTO emp (emp_no, emp_name, emp_sal, emp_addr) VALUES
('1002', 'Jane', 30000, 'LA');
INSERT INTO emp (emp_no, emp_name, emp_sal, emp_addr) VALUES
('1003', 'Mike', 28000, 'SF');
INSERT INTO emp (emp_no, emp_name, emp_sal, emp_addr) VALUES
('1004', 'Anna', 32000, 'TX');
COMMIT; -- Commit the insertions

-- Step 3: PL/SQL block to update employee salaries

DECLARE
var_emp_no CHAR(4);
var_emp_name VARCHAR2(10);
var_emp_sal NUMBER(8,2);
var_emp_addr VARCHAR2(10);
var_new_sal NUMBER(8,2);
CURSOR c_emp IS
SELECT emp_no, emp_name, emp_sal, emp_addr FROM emp;
BEGIN
OPEN c_emp; -- Open the cursor
LOOP
FETCH c_emp INTO var_emp_no, var_emp_name, var_emp_sal,
var_emp_addr; -- Fetch data
EXIT WHEN c_emp%NOTFOUND; -- Exit if no more rows
var_new_sal := var_emp_sal + 5000; -- Calculate new salary

-- Update the employee's salary

UPDATE emp
SET emp_sal = var_new_sal
WHERE emp_no = var_emp_no;

DBMS_OUTPUT.PUT_LINE('Updated ' || var_emp_name || ' salary to ' ||

var_new_sal);
END LOOP;
CLOSE c_emp; -- Close the cursor
END;
/

Output:

38 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 6. Write a PL/SQL block to delete employees who have left the company
(indicated by a NULL value in the end_date column)
from the Employees table. Display the number of rows deleted.

Code:
-- Step 1: Create the Employees table
CREATE TABLE Employees (
employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
hire_date DATE,
end_date DATE
);

-- Step 2: Insert sample data into the Employees table

INSERT INTO Employees (employee_id, first_name, last_name, hire_date,
end_date)
VALUES (1, 'John', 'Doe', TO_DATE('2020-01-01', 'YYYY-MM-DD'),
NULL);
INSERT INTO Employees (employee_id, first_name, last_name, hire_date,
end_date)
VALUES (2, 'Jane', 'Smith', TO_DATE('2019-06-15', 'YYYY-MM-DD'),
TO_DATE('2022-12-01', 'YYYY-MM-DD'));
INSERT INTO Employees (employee_id, first_name, last_name, hire_date,
end_date)
VALUES (3, 'Mike', 'Johnson', TO_DATE('2018-03-20', 'YYYY-MM-DD'),
TO_DATE('2021-05-10', 'YYYY-MM-DD'));
INSERT INTO Employees (employee_id, first_name, last_name, hire_date,
end_date)
VALUES (4, 'Anna', 'Brown', TO_DATE('2021-09-12', 'YYYY-MM-DD'),
NULL);
COMMIT; -- Commit the insertions

39 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 -- Step 3: PL/SQL block to delete employees with an end date
SET SERVEROUTPUT ON; -- Enable output in SQL*Plus or SQL Developer

BEGIN
DELETE FROM Employees
WHERE end_date IS NOT NULL;

IF SQL%ROWCOUNT > 0 THEN

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees
deleted.');
ELSE
DBMS_OUTPUT.PUT_LINE('No employees deleted.');
END IF;
END;
/
Output:

7. Write a PL/SQL block that updates the salary of employees in the emp table
by adding 1000 to their current salary for those
who belong to a department with the department number D001. The block
should handle three scenarios:
If the update is successful, it should output "Salary Updated...".
If no records are found for the given department, it should output "Record not
found...".
If no rows are updated, it should output "No records to be updated".

Code:
-- Create the emp table
CREATE TABLE emp (
emp_no CHAR(4),
emp_name VARCHAR2(10),
emp_sal NUMBER(8,2),
dept_no CHAR(4),
PRIMARY KEY (emp_no)
);

-- Insert sample data into the emp table

INSERT INTO emp (emp_no, emp_name, emp_sal, dept_no)
VALUES ('E001', 'John', 25000, 'D001');
INSERT INTO emp (emp_no, emp_name, emp_sal, dept_no)
VALUES ('E002', 'Jane', 30000, 'D002');
INSERT INTO emp (emp_no, emp_name, emp_sal, dept_no)

40 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 VALUES ('E003', 'Mike', 28000, 'D001');
INSERT INTO emp (emp_no, emp_name, emp_sal, dept_no)
VALUES ('E004', 'Anna', 32000, 'D003');
COMMIT; -- Commit the insertions

-- PL/SQL block to update salaries

BEGIN
-- Update the salary for employees in department D001
UPDATE emp
SET emp_sal = emp_sal + 1000
WHERE dept_no = 'D001';

-- Check the number of rows affected by the UPDATE statement

IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');
ELSE
DBMS_OUTPUT.PUT_LINE('No records to be updated.');
END IF;
END;
/
Output:

8.Write a PL/SQL block using an explicit cursor to retrieve the names and salaries of
employees
from the Employees table and display them.
Code:
CREATE TABLE Employees (
employee_id NUMBER(6),
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER(8,2)
);

INSERT INTO Employees (employee_id, first_name, last_name, salary)

VALUES (1, 'John', 'Doe', 5000.00);

INSERT INTO Employees (employee_id, first_name, last_name, salary)

VALUES (2, 'Jane', 'Smith', 6000.00);

INSERT INTO Employees (employee_id, first_name, last_name, salary)

VALUES (3, 'Mike', 'Johnson', 5500.00);

-- Commit the changes

41 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 COMMIT;

DECLARE
-- Declare a cursor to retrieve employee name and salary
CURSOR emp_cursor IS
SELECT first_name, salary
FROM Employees;

-- Declare variables to hold employee name and salary

v_first_name Employees.first_name%TYPE;
v_salary Employees.salary%TYPE;

BEGIN
-- Open the cursor
OPEN emp_cursor;

-- Fetch each record from the cursor into variables and display the result
LOOP
FETCH emp_cursor INTO v_first_name, v_salary;

-- Exit the loop if no more rows are found

EXIT WHEN emp_cursor%NOTFOUND;

-- Display the employee name and salary

DBMS_OUTPUT.PUT_LINE('Name: ' || v_first_name || ', Salary: ' ||
v_salary);
END LOOP;

-- Close the cursor

CLOSE emp_cursor;
END;
/

Output:

9.Write a PL/SQL block using an explicit cursor to give a 5% salary increase to

employees in the Employees table who belong to the 'Sales' department.
Code:
42 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 CREATE TABLE Employees (
employee_id NUMBER(6),
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER(8,2),
department_name VARCHAR2(50)
);

INSERT INTO Employees (employee_id, first_name, last_name, salary,

department_name)
VALUES (1, 'John', 'Doe', 5000.00, 'Sales');

INSERT INTO Employees (employee_id, first_name, last_name, salary,

department_name)
VALUES (2, 'Jane', 'Smith', 6000.00, 'HR');

INSERT INTO Employees (employee_id, first_name, last_name, salary,

department_name)
VALUES (3, 'Mike', 'Johnson', 5500.00, 'Sales');

-- Commit the changes

COMMIT;

DECLARE
-- Declare a cursor to select employees from the 'Sales' department
CURSOR sales_cursor IS
SELECT employee_id, salary
FROM Employees
WHERE department_name = 'Sales';

-- Variables to hold employee_id and salary

v_employee_id Employees.employee_id%TYPE;
v_salary Employees.salary%TYPE;

BEGIN
-- Open the cursor
OPEN sales_cursor;

-- Fetch each record and update the salary

LOOP
FETCH sales_cursor INTO v_employee_id, v_salary;

-- Exit the loop if no more rows are found

EXIT WHEN sales_cursor%NOTFOUND;

-- Calculate the new salary (5% increase)

v_salary := v_salary * 1.05;

43 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 -- Update the salary in the Employees table
UPDATE Employees
SET salary = v_salary
WHERE employee_id = v_employee_id;

-- Output the updated salary for the employee

DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ' new
salary: ' || v_salary);
END LOOP;

-- Close the cursor

CLOSE sales_cursor;

-- Commit the transaction to apply the updates

COMMIT;
END;
/
Output:

10.Write a PL/SQL block using an explicit cursor to calculate the average salary for
each department and
display the department name along with its average salary.
Code:
CREATE TABLE Employees (
employee_id NUMBER(6),
first_name VARCHAR2(50),
salary NUMBER(8,2),
department_name VARCHAR2(50)
);

INSERT INTO Employees (employee_id, first_name, salary,

department_name)
VALUES (1, 'John', 5000.00, 'Sales');

INSERT INTO Employees (employee_id, first_name, salary,

department_name)
VALUES (2, 'Jane', 6000.00, 'HR');

INSERT INTO Employees (employee_id, first_name, salary,

department_name)
VALUES (3, 'Mike', 5500.00, 'Sales');

-- Commit the changes

COMMIT;

44 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 DECLARE
-- Declare a cursor to select department names and calculate average salary
CURSOR dept_cursor IS
SELECT department_name, AVG(salary) AS avg_salary
FROM Employees
GROUP BY department_name;

-- Variables to hold department name and average salary

v_department_name Employees.department_name%TYPE;
v_avg_salary NUMBER(10, 2);

BEGIN
-- Open the cursor
OPEN dept_cursor;

-- Fetch each record and display the department name with average salary
LOOP
FETCH dept_cursor INTO v_department_name, v_avg_salary;

-- Exit the loop if no more rows are found

EXIT WHEN dept_cursor%NOTFOUND;

-- Output the department name and its average salary

DBMS_OUTPUT.PUT_LINE('Department: ' || v_department_name || ' |
Average Salary: ' || v_avg_salary);
END LOOP;

-- Close the cursor

CLOSE dept_cursor;
END;
/
Output:

45 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 Practical 6:Exception Handling
Aim:Concept of exception.
THEORY:- * An exception is an error which disrupts the normal flow of program
instructions. PL/SQL provides us the exception block which raises the exception
thus helping the programmer to find out the fault and resolve it. * There are two
types of exceptions defined in PL/SQL * User defined exception. * System defined
exceptions
Syntax:
DECLARE
declarations section;
BEGIN
executable command(s);
EXCEPTION
WHEN exception1 THEN
statement1; -- Exception handler code to handle the errors
WHEN exception2 THEN
statement2; -- Exception handler code to handle the errors
[WHEN others THEN]
-- not explicitly handled
/* default exception handling code */
END

Q1. Write a PL/SQL block to handle the NO_DATA_FOUND exception, which

occurs when a SELECT INTO statement returns no rows.
DECLARE
v_employee_name emp.emp_name%TYPE;
BEGIN
SELECT emp_name INTO v_employee_name FROM emp WHERE emp_no =
'E999';
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with the given employee
number.');
END;
/
Output:

Q2. create table adbms(g_id int , g_name varchar(20), marks int);

insert into adbms values(1, 'Suraj',100);
insert into adbms values(2, 'Perveen',99);

46 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
insert into adbms values(3, 'John', 95);
NO_DATA_FOUND:
DECLARE
temp varchar(20);
BEGIN
SELECT g_id into temp from adbms where
g_name=’Advanced_Database_Management_System';
exception
WHEN no_data_found THEN
dbms_output.put_line('ERROR');
dbms_output.put_line('there is no name as');
dbms_output.put_line(' Advanced_Database_Management_System in adbms
table');
end;
/
Output:

• System defined exceptions:

These exceptions are predefined in PL/SQL which get raised WHEN
certain database rule is violated.
System-defined exceptions are further divided into two categories:
• Named system exceptions.

• Unnamed system exceptions.

• Named Programmer-defined Exception

Q3. What will happen if a number is divided by zero in the following PL/SQL
block, and how does the block handle this exception?
SQL> set serveroutput on;
SQL> declare
num number;
begin
num := 78/0;
exception
when ZERO_DIVIDE then
dbms_output.put_line('Dividing by zero Error');
end
/
Output:

47 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 • TOO_MANY_ROWS:

Q4.Write a PL/SQL block to handle the TOO_MANY_ROWS exception, which

occurs when a SELECT INTO statement returns more than one row.
DECLARE
temp varchar(20);
BEGIN
SELECT g_name into temp from adbms;
dbms_output.put_line(temp);
EXCEPTION
WHEN too_many_rows THEN
dbms_output.put_line('error trying to SELECT too many rows');
end;
/
Output:

Q.5 How do you 8. What is the difference between EXCEPTION_INIT and

EXCEPTION in PL/SQL?handle exceptions in a PL/SQL block?
CODE: DECLARE
custom_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(custom_exception, -20001);
BEGIN
RAISE custom_exception;
EXCEPTION WHEN custom_exception THEN
DBMS_OUTPUT.PUT_LINE('Custom exception raised');
END;
OUTPUT:-

Q6. Write a PL/SQL block to handle the CASE_NOT_FOUND exception, which

occurs when a CASE statement does not match any condition and has no ELSE
clause.
DECLARE
v_grade CHAR(1) := 'D';
v_result VARCHAR2(20);
BEGIN
CASE v_grade
WHEN 'A' THEN v_result := 'Excellent';
WHEN 'B' THEN v_result := 'Good';
WHEN 'C' THEN v_result := 'Average';
END CASE;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);

48 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No matching case found.');
END;
/
Output:

PRACTICAL 7: PACKAGES
AIM:Concept of Packages.
THEORY:

49 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
• PL/SQL packages are a way to organize and encapsulate related
procedures, functions, variables, triggers, and other PL/SQL items into a
single item. Packages provide a modular approach to write and maintain
the code. It makes it easy to manage large codes.
• A PL/SQL package is a collection of
related Procedures, Functions, Variables, and other elements that are
grouped for Modularity and Reusability.
• The needs of the Packages are described below:
• Modularity: Packages provide a modular structure, allowing developers
to organize and manage code efficiently.
• Code Reusability: Procedures and functions within a package can be
reused across multiple programs, reducing redundancy.
• Private Elements: Packages support private procedures and functions,
limiting access to certain code components.
• Encapsulation: Packages encapsulate related logic, protecting internal
details and promoting a clear interface to other parts of the code.
Syntax:
CREATE [OR REPLACE] PACKAGE BODY package_name AS
-- Declarations of private variables, types, and cursors
-- Private procedures and functions
PROCEDURE procedure_name(parameter_list) IS
-- Implementation of the procedure
BEGIN
-- Body of the procedure
END procedure_name;
FUNCTION function_name(parameter_list) RETURN return_datatype IS
-- Implementation of the function
BEGIN
-- Body of the function
END function_name;
END package_name;
/
Syntax for compiling a package body:
• ALTER PACKAGE package_name COMPILE BODY;
OR
• ALTER PACKAGE package_name COMPILE PACKAGE;
• ALTER PACKAGE emp_sal COMPILE PACKAGE;
• DROP Package:
Drop package package_Name;
Drop package emp_sal;

50 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 1: Create a Package for Calculating Rectangle Area and Perimeter
Step 1: Package Specification for Rectangle Area and Perimeter Calculation
• Query:
CREATE OR REPLACE PACKAGE RectanglePackage AS
PROCEDURE CalculateAreaAndPerimeter (length IN NUMBER, width IN
NUMBER, area OUT NUMBER, perimeter OUT NUMBER);
END RectanglePackage;

Step 2: Package Body for Rectangle Area and Perimeter Calculation.

Query:
CREATE OR REPLACE PACKAGE BODY RectanglePackage AS
PROCEDURE CalculateAreaAndPerimeter (length IN NUMBER, width IN
NUMBER, area OUT NUMBER, perimeter OUT NUMBER) IS
BEGIN
area := length * width;
perimeter := 2 * (length + width);
END CalculateAreaAndPerimeter;
END RectanglePackage;
/

Step 3: Now Calculate Rectangle Area and Perimeter Using the Package.
Query:
DECLARE
l_length NUMBER := 5;
l_width NUMBER := 3;
l_area NUMBER;
l_perimeter NUMBER;
BEGIN

51 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 RectanglePackage.CalculateAreaAndPerimeter(l_length, l_width, l_area,
l_perimeter);
DBMS_OUTPUT.PUT_LINE('The area of the Rectangle is ' || l_area);
DBMS_OUTPUT.PUT_LINE('The Perimeter of the Rectangle is ' ||
l_perimeter);
END;
/

Q2: Temperature Conversion Package in PL/SQL

Step 1: Package Specification for Temperature
Conversion
Query:
CREATE OR REPLACE PACKAGE TemperatureConversion AS
FUNCTION CelsiusToFahrenheit (celsius IN NUMBER) RETURN
NUMBER;
FUNCTION FahrenheitToCelsius(fahrenheit IN NUMBER)
RETURN NUMBER; END TemperatureConversion;
/

Step 2: Package Body for Temperature Conversion Functions.

Query:
CREATE OR REPLACE PACKAGE BODY TemperatureConversion AS
FUNCTION CelsiusToFahrenheit (celsius IN NUMBER) RETURN
NUMBER IS
BEGIN
RETURN (celsius * 9/5) + 32;
END CelsiusToFahrenheit;

52 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 FUNCTION FahrenheitToCelsius(fahrenheit IN NUMBER) RETURN
NUMBER IS
BEGIN
RETURN (fahrenheit - 32) * 5/9;
END FahrenheitToCelsius;
END TemperatureConversion;
/

Step 3: Use the package in the PL/SQL code for converting the temperature.
Query:
DECLARE
celsius_temp NUMBER := 20;
fahrenheit_temp NUMBER;
converted_celsius_temp NUMBER;
BEGIN
fahrenheit_temp :=
TemperatureConversion.CelsiusToFahrenheit(celsius_temp);
DBMS_OUTPUT.PUT_LINE('Temperature in Fahrenheit: ' ||
fahrenheit_temp);
converted_celsius_temp :=
TemperatureConversion.FahrenheitToCelsius(fahrenheit_temp);
DBMS_OUTPUT.PUT_LINE('Converted back to Celsius: ' ||
converted_celsius_temp);
END;
/

53 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
3. Write a PL/SQL code to: Create a package named cust_sal that includes a
procedure find_sal. The procedure should take a customer ID as input and print
the salary of the customer with that ID. Step 1: Package Specification

Query:

CREATE PACKAGE cust_sal AS

PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/
Step 2: Package Body
Query:
CREATE OR REPLACE PACKAGE BODY cust_sal AS

PROCEDURE find_sal(c_id customers.id%TYPE) IS

c_sal customers.salary%TYPE;
BEGIN
SELECT salary INTO c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line('Salary: '|| c_sal);
END find_sal;
END cust_sal;
/

Step 3: Use the package in the PL/SQL code

Query:
DECLARE
code customers.id%type := &cc_id;
BEGIN
cust_sal.find_sal(code);
END;
/
4. Create a PL/SQL package to calculate the compound interest for a given
principal, rate, and time.
Step 1:
Solution:
-- Package Specification
CREATE OR REPLACE PACKAGE interest_calculator IS
FUNCTION calculate_compound_interest(principal NUMBER, rate
NUMBER, time NUMBER) RETURN NUMBER;
END interest_calculator;
/

54 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
Step 2:
Solution:
-- Package Body
CREATE OR REPLACE PACKAGE BODY interest_calculator IS
FUNCTION calculate_compound_interest(principal NUMBER, rate
NUMBER, time NUMBER) RETURN NUMBER IS
BEGIN
RETURN principal * POWER((1 + rate / 100), time);
END calculate_compound_interest;
END interest_calculator;
/

Step 3:
DECLARE
principal_amt NUMBER := 1000;
interest_rate NUMBER := 5;
time_period NUMBER := 2;
compound_interest NUMBER;
BEGIN
compound_interest := interest_calculator.calculate_compound_interest(principal_amt,
interest_rate, time_period);
DBMS_OUTPUT.PUT_LINE('Compound Interest: ' || compound_interest);
END;
/

55 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
5. Create a PL/SQL package to count the number of vowels in a string.
Step 1:
Solution:
-- Package Specification
CREATE OR REPLACE PACKAGE vowel_counter IS
FUNCTION count_vowels(input_str VARCHAR2) RETURN NUMBER;
END vowel_counter;
/

Step 2:
-- Package Body
CREATE OR REPLACE PACKAGE BODY vowel_counter IS
FUNCTION count_vowels(input_str VARCHAR2) RETURN
NUMBER IS vowel_count NUMBER := 0;
BEGIN
FOR i IN 1..LENGTH(input_str) LOOP
IF SUBSTR(UPPER(input_str), i, 1) IN ('A', 'E', 'I', 'O',
'U') THEN vowel_count := vowel_count + 1;
END IF;
END LOOP;
RETURN vowel_count;
END count_vowels;
END vowel_counter;
/

56 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
Ste
p:3
DE
CL
AR
E
input_string VARCHAR2(100) := 'Hello
World'; vowel_count NUMBER;
BEGIN vowel_count :=
vowel_counter.count_vowels(input_string);
DBMS_OUTPUT.PUT_LINE('Number of vowels in "' || input_string || '": ' ||
vowel_count); END;
/

57 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
 Practical 8:Triggers

AIM: To understand Triggers in PL SQL.

Trigger is invoked by Oracle engine automatically whenever a specified event
occurs.Trigger is stored into database and invoked repeatedly, when specific
condition match.
Triggers are stored programs, which are automatically executed or fired when some
event occurs.
Triggers are written to be executed in response to any of the following events.
o A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

o A database definition (DDL) statement (CREATE, ALTER, or DROP).

o A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or
SHUTDOWN).
Syntax for creating trigger:
1. CREATE [OR REPLACE ] TRIGGER trigger_name
2. {BEFORE | AFTER | INSTEAD OF }
3. {INSERT [OR] | UPDATE [OR] | DELETE}
4. [OF col_name]
5. ON table_name
6. [REFERENCING OLD AS o NEW AS n]
7. [FOR EACH ROW]
8. WHEN (condition)
9. DECLARE
10. Declaration-statements
11. BEGIN
12. Executable-statements
13. EXCEPTION
14. Exception-handling-statements
15. END;

Tasks:
1. What will be inserted into the Affect table when the following statement is executed:
INSERT INTO Adbms (Id, Name, Score) VALUES (6, 'Arjun', 500); using trigger?

58 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
CODE:
1. CREATE OR REPLACE TRIGGER BEFORE_INSERT
2. BEFORE INSERT ON Adbms
3. FOR EACH ROW
4. BEGIN
5. INSERT INTO Affect (Id, Name, Score)
6. VALUES (:NEW.Id, :NEW.Name, :NEW.Score);
7. END;
8. /
OUTPUT:

2. For the BEFORE_DELETE trigger, what will be inserted into the Affect table when
the following statement is executed: DELETE FROM Adbms WHERE Id = 3;?
CODE:
1. CREATE OR REPLACE TRIGGER BEFORE_DELETE
2. BEFORE DELETE ON Adbms
3. FOR EACH ROW
4. BEGIN
5. INSERT INTO Affect (Id, Name, Score)
6. VALUES (:OLD.Id, :OLD.Name, :OLD.Score);
7. END;
8. /
9. DELETE FROM Adbms WHERE Id = 3;

OUTPUT:

59 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
3. For the BEFORE_UPDATE trigger, what will be inserted into the Affect table when
the following statement is executed: UPDATE Adbms SET Score = 900 WHERE Id =
5;?
CODE:
1. CREATE OR REPLACE TRIGGER BEFORE_UPDATE
2. BEFORE UPDATE ON Adbms
3. FOR EACH ROW
4. BEGIN
5. INSERT INTO Affect (Id, Name, Score)
6. VALUES (:OLD.Id, :OLD.Name, :OLD.Score);
7. END;
8. /
OUTPUT:

4. Explain the functionality of the AFTER_DELETE trigger in the given PL/SQL code
and describe what happens when a row is deleted from the Practical table, particularly
focusing on how the trigger affects the Affect table.

60 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
CODE;
1. CREATE OR REPLACE TRIGGER AFTER_DELETE
2. AFTER DELETE ON Practical
3. FOR EACH ROW
4. BEGIN
5. INSERT INTO Affect (Id, Name, Score)
6. VALUES (:OLD.Id, :OLD.Name, :OLD.Score);
7. END;
8. /
OUTPUT:

5. How does the after_change_users trigger ensure that changes made to the Users table
are logged in the AuditLog table, and what information is captured for each type of
operation (INSERT, UPDATE, DELETE)?
CODE:
1. CREATE OR REPLACE TRIGGER after_change_users
2. AFTER INSERT OR UPDATE OR DELETE ON Users
3. FOR EACH ROW
4. BEGIN
5. IF INSERTING THEN
6. INSERT INTO AuditLog (UserId, Operation, Timestamp, NewUsername,
NewEmail)
7. VALUES (:NEW.UserId, 'INSERT', SYSDATE, :NEW.Username, :NEW.Email);
8.
9. ELSIF UPDATING THEN
10. INSERT INTO AuditLog (UserId, Operation, Timestamp, OldUsername,
OldEmail, NewUsername, NewEmail)
11. VALUES (:OLD.UserId, 'UPDATE',
SYSDATE, :OLD.Username, :OLD.Email, :NEW.Username, :NEW.Email);
12.
13. ELSIF DELETING THEN
14. INSERT INTO AuditLog (UserId, Operation, Timestamp, OldUsername,
OldEmail)
15. VALUES (:OLD.UserId, 'DELETE', SYSDATE, :OLD.Username, :OLD.Email);
16. END IF;
17. END;
18. /

61 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
OUTPUT:

6. Craete a Trigger for user name with same id as in original table.

CODE:
1. CREATE OR REPLACE TRIGGER welcome
2. AFTER INSERT ON Adbms
3. FOR EACH ROW
4. BEGIN
5. INSERT INTO Affect (Id, "time", Name)
6. VALUES (:NEW.Id, SYSDATE, USER);
7. END;
8. /
9. insert into Adbms (Id, Name, Score) VALUES (6, 'aziaf', 500);
OUTPUT:

7. Create a PL/SQL trigger to store date when values is inserted in table.

CODE:
1. CREATE OR REPLACE TRIGGER before_insert_users
2. BEFORE INSERT ON Users
3. FOR EACH ROW
4. BEGIN
5. :NEW.CreatedDate := SYSDATE;
6. :NEW.UpdatedDate := SYSDATE;
7. END;

62 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M
8. /
OUTPUT:

63 | A D V A N C E D A T A B A S E M A N A G E M E N T S Y S T E M