‭ 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 the‬‭basic concepts of PL/SQL.‬

‭1.‬ ‭Write a PL/SQL code to accept the value of‬‭A, 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 simple‬‭loop 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 the‬‭concept 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', '[email protected]', '555-1234', TO_DATE('2022-01-15',‬
‭'YYYY-MM-DD'), 'IT_PROG', 55000, 1002, 60);‬

I‭NSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,‬

‭PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID,‬
‭DEPARTMENT_ID)‬
‭VALUES (1002, 'Jane', 'Smith', '[email protected]', '555-5678',‬
‭TO_DATE('2021-03-25', 'YYYY-MM-DD'), 'HR_MGR', 75000, NULL, 90);‬
I‭NSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,‬
‭PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID,‬
‭DEPARTMENT_ID)‬
‭VALUES (1003, 'Alice', 'Johnson', '[email protected]', '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', '[email protected]', '555-1234',‬
‭TO_DATE('2022-01-15', 'YYYY-MM-DD'), 'IT_PROG', 55000, 1002, 60);‬
I‭NSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,‬
‭PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID,‬
‭DEPARTMENT_ID)‬
‭VALUES (1002, 'Shubham', 'Shubham', '[email protected]', '555-5678',‬
‭TO_DATE('2021-03-25', 'YYYY-MM-DD'), 'HR_MGR', 75000, NULL, 90);‬

I‭NSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,‬

‭PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID,‬
‭DEPARTMENT_ID)‬
‭VALUES (1003, 'rohit', 'rohit', '[email protected]', '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);‬

I‭NSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME,‬

‭SALARY)‬
‭VALUES (1002, 'Jane', 'Smith', 1500); -- Won't be inserted since salary is less‬
‭than 2500‬

I‭NSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME,‬

‭SALARY)‬
‭VALUES (1003, 'Shubham', 'Johnson', 3000);‬

I‭NSERT 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