AME : Harsh Agarwal
N
500124905
R252223173
B - 4
ASSIGNMENT 1
Experiment 1: Understanding the Concepts of PL/SQL Programming
Objective:Students will be able to implement thebasic concepts of PL/SQL.
1. Write a PL/SQL code to accept the value ofA, B & C and display which is greater.
DELIMITER $$
CREATE PROCEDURE FindGreatest(IN A INT, IN B INT, IN C INT)
BEGIN
IF A > B AND A > C THEN
SELECT CONCAT('A is the greatest: ', A) AS Result;
ELSEIF B > A AND B > C THEN
SELECT CONCAT('B is the greatest: ', B) AS Result;
ELSE
SELECT CONCAT('C is the greatest: ', C) AS Result;
END IF;
END$$
DELIMITER ;
CALL FindGreatest(10, 20, 15);
2. U sing PL/SQL Statements, create a simpleloop that displays the message
“Welcome to PL/SQL Programming” 20 times.
DELIMITER $$
CREATE PROCEDURE PrintMessage()
BEGIN
DECLARE counter INT DEFAULT 1;
WHILE counter <= 20 DO
SELECT 'Welcome to PL/SQL Programming' AS Message;
SET counter = counter + 1;
END WHILE;
END$$
DELIMITER ;
CALL PrintMessage();
\
3. Write a PL/SQL code block to find the factorial of a number.
DELIMITER $$
CREATE PROCEDURE FindFactorial(IN number INT)
BEGIN
DECLARE result INT DEFAULT 1;
DECLARE counter INT DEFAULT 1;
WHILE counter <= number DO
SET result = result * counter;
SET counter = counter + 1;
END WHILE;
SELECT result AS Factorial;
END$$
DELIMITER ;
CALL FindFactorial(5);
4. Write a PL/SQL program to generate the Fibonacci series.
DELIMITER $$
CREATE PROCEDURE FibonacciSeries(IN n INT)
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE b INT DEFAULT 1;
DECLARE temp INT;
DECLARE counter INT DEFAULT 1;
WHILE counter <= n DO
SELECT a AS Fibonacci;
SET temp = a + b;
SET a = b;
SET b = temp;
SET counter = counter + 1;
END WHILE;
END$$
DELIMITER ;
CALL FibonacciSeries(10);
5. Write a PL/SQL code to find the sum of the first N numbers.
DELIMITER $$
CREATE PROCEDURE SumOfNumbers(IN n INT)
BEGIN
DECLARE sum INT DEFAULT 0;
DECLARE counter INT DEFAULT 1;
WHILE counter <= n DO
SET sum = sum + counter;
SET counter = counter + 1;
END WHILE;
SELECT sum AS TotalSum;
END$$
DELIMITER ;
CALL SumOfNumbers(10);
ASSIGNMENT 2
xperiment 2: To understand concepts of function and procedure in
E
PL/SQL.
Objective: Students will be able to implement the Pl/SQL programs
using function and procedure.
1. Implement the above experiments of PL/SQL using functions and
procedures.
DELIMITER $$
REATE FUNCTION FindGreatestFunction(A INT, B INT, C INT)
C
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
DECLARE result VARCHAR(50);
IF A > B AND A > C THEN
SET result = CONCAT('A is the greatest: ', A);
ELSEIF B > A AND B > C THEN
SET result = CONCAT('B is the greatest: ', B);
ELSE
SET result = CONCAT('C is the greatest: ', C);
END IF;
ETURN result;
R
ND$$
E
DELIMITER ;
- - Example Call
SELECT FindGreatestFunction(10, 20, 15);
DELIMITER $$
REATE FUNCTION PrintMessageFunction()
C
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
DECLARE counter INT DEFAULT 1;
DECLARE message VARCHAR(50) DEFAULT '';
WHILE counter <= 20 DO
SET message = 'Welcome to PL/SQL Programming';
SET counter = counter + 1;
END WHILE;
ETURN message;
R
ND$$
E
DELIMITER ;
- - Example Call
SELECT PrintMessageFunction();
DELIMITER $$
REATE FUNCTION FindFactorialFunction(number INT)
C
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result INT DEFAULT 1;
DECLARE counter INT DEFAULT 1;
WHILE counter <= number DO
SET result = result * counter;
SET counter = counter + 1;
END WHILE;
ETURN result;
R
ND$$
E
DELIMITER ;
- - Example Call
SELECT FindFactorialFunction(5);
DELIMITER $$
REATE FUNCTION FibonacciSeriesFunction(n INT)
C
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE b INT DEFAULT 1;
DECLARE temp INT;
DECLARE counter INT DEFAULT 1;
DECLARE series VARCHAR(255) DEFAULT '';
WHILE counter <= n DO
SET series = CONCAT(series, a, ', ');
SET temp = a + b;
SET a = b;
SET b = temp;
SET counter = counter + 1;
END WHILE;
ETURN TRIM(TRAILING ', ' FROM series);
R
ND$$
E
DELIMITER ;
- - Example Call
SELECT FibonacciSeriesFunction(10);
DELIMITER $$
REATE FUNCTION SumOfNumbersFunction(n INT)
C
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE sum INT DEFAULT 0;
DECLARE counter INT DEFAULT 1;
WHILE counter <= n DO
SET sum = sum + counter;
SET counter = counter + 1;
END WHILE;
ETURN sum;
R
ND$$
E
DELIMITER ;
- - Example Call
SELECT SumOfNumbersFunction(10);
ASSIGNMENT 3
Experiment 3: To understand the concepts of implicit and explicit cursor.
Objective:Students will be able to implement theconcept of implicit and explicit
c ursor. 1. Using an implicit cursor, update the salary by an increase of 10% for all the
records in the EMPLOYEES table, and finally display how many records have been
updated. If no records exist, display the message “No Change”.
- - Create the EMPLOYEES table
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER(6) PRIMARY KEY,
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
EMAIL VARCHAR2(20),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
JOB_ID VARCHAR2(11),
SALARY NUMBER(8,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
);
- - Insert sample data into the EMPLOYEES table
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID,
DEPARTMENT_ID)
VALUES (1001, 'John', 'Doe', 'jdoe@example.com', '555-1234', TO_DATE('2022-01-15',
'YYYY-MM-DD'), 'IT_PROG', 55000, 1002, 60);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID,
DEPARTMENT_ID)
VALUES (1002, 'Jane', 'Smith', 'jsmith@example.com', '555-5678',
TO_DATE('2021-03-25', 'YYYY-MM-DD'), 'HR_MGR', 75000, NULL, 90);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID,
DEPARTMENT_ID)
VALUES (1003, 'Alice', 'Johnson', 'ajohnson@example.com', '555-9876',
TO_DATE('2020-08-10', 'YYYY-MM-DD'), 'FIN_ANALYST', 65000, 1001, 100);
SET SERVEROUTPUT ON;
BEGIN
-- Update the salary of all employees by 10%
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.10;
- - Check the number of records affected using the implicit cursor attribute
SQL%ROWCOUNT
IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' records have been updated.');
ELSE
DBMS_OUTPUT.PUT_LINE('No Change');
END IF;
END;
/
SET SERVEROUTPUT ON;
DECLARE
-- Declare variables to hold the fetched data
v_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE;
v_emp_name EMPLOYEES.FIRST_NAME%TYPE;
v_salary EMPLOYEES.SALARY%TYPE;
- - Declare an explicit cursor to select employee details
CURSOR emp_cursor IS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMPLOYEES;
BEGIN
-- Open the cursor
OPEN emp_cursor;
- - Fetch each row and print the details
LOOP
ETCH emp_cursor INTO v_emp_id, v_emp_name, v_salary;
F
EXIT WHEN emp_cursor%NOTFOUND;
- - Display the fetched employee details
DBMS_OUTPUT.PUT_LINE(v_emp_id || ' ' || v_emp_name || ' ' || v_salary);
ND LOOP;
E
- - Close the cursor
CLOSE emp_cursor;
END;
/
. Using an explicit cursor fetch the employee name, employee_id and
2
salary of all the records from the EMPLOYEES table.
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER(6) PRIMARY KEY, -- Employee ID, primary key
FIRST_NAME VARCHAR2(50), -- Employee first name
LAST_NAME VARCHAR2(50), -- Employee last name (optional)
EMAIL VARCHAR2(100), -- Email address
PHONE_NUMBER VARCHAR2(20), -- Phone number
HIRE_DATE DATE, -- Hire date
JOB_ID VARCHAR2(11), -- Job ID (optional)
SALARY NUMBER(8,2), -- Salary with two decimal places
MANAGER_ID NUMBER(6), -- Manager ID (optional)
DEPARTMENT_ID NUMBER(4) -- Department ID (optional)
);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID,
DEPARTMENT_ID)
VALUES (1001, 'Harsh', 'Harsh', 'harsh@example.com', '555-1234',
TO_DATE('2022-01-15', 'YYYY-MM-DD'), 'IT_PROG', 55000, 1002, 60);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID,
DEPARTMENT_ID)
VALUES (1002, 'Shubham', 'Shubham', 'shubham@example.com', '555-5678',
TO_DATE('2021-03-25', 'YYYY-MM-DD'), 'HR_MGR', 75000, NULL, 90);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID,
DEPARTMENT_ID)
VALUES (1003, 'rohit', 'rohit', 'rohit@example.com', '555-9876', TO_DATE('2020-08-10',
'YYYY-MM-DD'), 'FIN_ANALYST', 65000, 1001, 100);
DECLARE
v_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE; -- Variable for Employee ID
v_emp_name EMPLOYEES.FIRST_NAME%TYPE; -- Variable for First Name
v_salary EMPLOYEES.SALARY%TYPE; -- Variable for Salary
- - Declare a cursor to select employee details
CURSOR emp_cursor IS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES;
BEGIN
-- Open the cursor
OPEN emp_cursor;
- - Loop through the result set
LOOP
-- Fetch the cursor values into variables
FETCH emp_cursor INTO v_emp_id, v_emp_name, v_salary;
- - Exit the loop if there are no more rows
EXIT WHEN emp_cursor%NOTFOUND;
- - Display the employee details
DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ' Name: ' || v_emp_name || ' Salary:
' || v_salary);
END LOOP;
-- Close the cursor
LOSE emp_cursor;
C
ND;
E
. Using an explicit cursor Insert the records from EMPLOYEES table for
3
the columns employee_id, Last_Name and salary for those records whose
salary exceeds 2500 into a new table TEMP_EMP.
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER(6), -- Employee ID
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50), -- Employee last name
SALARY NUMBER(8,2) -- Salary with two decimal places
);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
SALARY)
VALUES (1001, 'Harsh', 'Doe', 5500);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
SALARY)
VALUES (1002, 'Jane', 'Smith', 1500); -- Won't be inserted since salary is less
than 2500
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
SALARY)
VALUES (1003, 'Shubham', 'Johnson', 3000);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
SALARY)
ALUES (1004, 'Bob', 'Brown', 2000); -- Won't be inserted since salary is less
V
than 2500
ELECT * FROM EMPLOYEES;
S
DECLARE
v_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE; -- Variable for Employee
ID
v_last_name EMPLOYEES.LAST_NAME%TYPE; -- Variable for Last Name
v_salary EMPLOYEES.SALARY%TYPE; -- Variable for Salary
- - Declare a cursor to select employee details with salary greater than 2500
CURSOR emp_cursor IS
SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 2500;
BEGIN
-- Open the cursor
OPEN emp_cursor;
- - Loop through the result set
LOOP
-- Fetch the cursor values into variables
FETCH emp_cursor INTO v_emp_id, v_last_name, v_salary;
- - Exit the loop if there are no more rows
EXIT WHEN emp_cursor%NOTFOUND;
- - Insert the selected records into the TEMP_EMP table
INSERT INTO EMPLOYEES (EMPLOYEE_ID, LAST_NAME, SALARY)
VALUES (v_emp_id, v_last_name, v_salary);
ND LOOP;
E
- - Close the cursor
CLOSE emp_cursor;
-- Commit the transaction to save the inserted records
OMMIT;
C
ND;
E
