----------PRACTICAL 1

(1)Plsql block FOR IF THEN ELSIF
DECLARE
var NUMBER(3) := 50;
BEGIN
IF (var = 10) THEN
DBMS_OUTPUT.put_line('Value of var is 10');
ELSIF (var = 20) THEN
DBMS_OUTPUT.put_line('Value of var is 20');
ELSIF (var = 30) THEN
DBMS_OUTPUT.put_line('Value of var is 30');
ELSE
DBMS_OUTPUT.put_line('None of the above condition is true.');
END IF;
DBMS_OUTPUT.put_line('Exact value of var is: '|| var);
END;


(2)PL/SQL Block FOR FOR LOOP:
DECLARE
BEGIN
    FOR var IN REVERSE 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(var);
    END LOOP;
END;


(3)REVERSE FOR LOOP:

DECLARE
BEGIN
    FOR var IN REVERSE 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(var);
    END LOOP;
END;


(4)PL/SQL Block FOR WHILE LOOP:

DECLARE
    num NUMBER := 1;
BEGIN
    WHILE num <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE(num);
        num := num + 1;
    END LOOP;
END;


(5)PL/SQL FOR EXIT LOOP:

DECLARE
    num NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(num);
        IF num = 10 THEN
            EXIT;
        END IF;
        num := num + 1;
    END LOOP;
END;






---------------------PRACTICAL2----------------

PL/SQL Programs

1. WRITE a PL/SQL block TO accept marks OF a candidate AND find the result OF the candidate.

Code:

 DECLARE

 marks NUMBER;
 BEGIN
 marks := &marks;
 IF marks >= 50 THEN
 DBMS_OUTPUT.PUT_LINE('Pass');
 ELSE
 DBMS_OUTPUT.PUT_LINE('Fail');
 END IF;
 END;

2. WRITE a PL/SQL program TO CHECK whether a given NUMBER IS POSITIVE, negative OR zero.
Code:
 DECLARE
 num NUMBER;
 BEGIN
 num := &num;
 IF num > 0 THEN
 DBMS_OUTPUT.PUT_LINE('Positive');
 ELSIF num < 0 THEN
 DBMS_OUTPUT.PUT_LINE('Negative');
 ELSE
 DBMS_OUTPUT.PUT_LINE('Zero');
END IF;

 END;



3. WRITE a PL/SQL program TO CHECK whether a NUMBER IS even OR odd.

Code:

 DECLARE

 num NUMBER;

 BEGIN

 num := &num;

 IF MOD(num, 2) = 0 THEN

 DBMS_OUTPUT.PUT_LINE('Even');

 ELSE

 DBMS_OUTPUT.PUT_LINE('Odd');

 END IF;

 END;



4. WRITE a PL/SQL program TO CONVERT a temperature IN scale Fahrenheit TO Celsius AND vice versa.

Code:

 DECLARE

 temp_f NUMBER;

 temp_c NUMBER;

 BEGIN

 temp_f := &temp_f;

 temp_c := (temp_f - 32) * 5/9;
 DBMS_OUTPUT.PUT_LINE('Celsius: ' || temp_c);
 END;

5. WRITE a program IN PL/SQL TO print addition OF 1st n numbers.

Code:

 DECLARE

 n NUMBER;

 SUM NUMBER := 0;

 BEGIN

 n := &n;

 FOR i IN 1..n LOOP

 SUM := SUM + i;

 END LOOP;

 DBMS_OUTPUT.PUT_LINE('Sum: ' || SUM);

 END;



6. WRITE a program IN PL/SQL TO print the prime numbers BETWEEN 1 TO 50.

Code:

 DECLARE

 i NUMBER;

 j NUMBER;

 is_prime BOOLEAN;

 BEGIN

 FOR i IN 2..50 LOOP

 is_prime := TRUE;

 FOR j IN 2..i-1 LOOP

 IF MOD(i, j) = 0 THEN

 is_prime := FALSE;
EXIT;

 END IF;

 END LOOP;

 IF is_prime THEN

 DBMS_OUTPUT.PUT_LINE(i || ' is prime');

 END IF;
 END LOOP;
 END;

7. WRITE a program IN PL/SQL TO CHECK whether a NUMBER IS prime OR NOT using GOTO statement
WITH FOR LOOP.
Code:
 DECLARE
 num NUMBER;
 i NUMBER;
 BEGIN
 num := &num;
 IF num <= 1 THEN
 DBMS_OUTPUT.PUT_LINE('Not prime');
 GOTO end_loop;
 END IF;
 FOR i IN 2..num-1 LOOP
 IF MOD(num, i) = 0 THEN
 DBMS_OUTPUT.PUT_LINE('Not prime');
 GOTO end_loop;
 END IF;
 END LOOP;

 DBMS_OUTPUT.PUT_LINE('Prime');

 <<end_loop>>

 NULL;

 END;




-------------------PRACTICAL 3---------------


 ADAVANCED DATABASE MANAGEMENT SYSTEM PRACTICALS

Aim:-
Practical Based ON PL/SQL Datatypes


(1) Basic datatype
NUMBER: Used TO store numeric VALUES.
DECLARE
  v_number NUMBER := 12345;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Number: ' || v_number);
END;

VARCHAR2: Used TO store variable-LENGTH character strings.
DECLARE
  v_name VARCHAR2(50) := 'John Doe';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
CHAR: Used TO store fixed-LENGTH character strings.
DECLARE
  v_fixed_name CHAR(10) := 'John';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Fixed Name: ' || v_fixed_name);
END;
DATE: Used TO store DATE AND TIME VALUES.
DECLARE
  v_date DATE := SYSDATE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Date: ' || TO_CHAR(v_date, 'DD-MON-YYYY HH24:MI:SS'));
END;



PROGRAM: Used CREATE a Program BY using above datatypes (NUMBER, CHAR, VARCHAR2, DATE)
DECLARE
    my_number NUMBER := 100;
    my_char CHAR(10) := 'A';
    my_varchar2 VARCHAR2(50) := 'Hello, PL/SQL!';
    my_date DATE := SYSDATE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Number: ' || my_number);
    DBMS_OUTPUT.PUT_LINE('Char: ' || my_char);
    DBMS_OUTPUT.PUT_LINE('Varchar2: ' || my_varchar2);
    DBMS_OUTPUT.PUT_LINE('Date: ' || my_date);
END;

Taking Input FROM Users.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
      -- taking input for variable a
      a NUMBER := &a;
      -- taking input for variable b
      b VARCHAR2(30) := ‘&b’;
  BEGIN
      NULL;
  END;
  /
BOOLEAN: Used TO store TRUE, FALSE, OR NULL.
DECLARE
  v_flag BOOLEAN := TRUE;
BEGIN
  IF v_flag THEN
    DBMS_OUTPUT.PUT_LINE('Flag is TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Flag is FALSE');
  END IF;
END;

COMPOSITE DATATYPES:- These Datatypes can hold multiple VALUES.
RECORD: Used TO store a collection OF related data items.
DECLARE
  TYPE Employee IS RECORD (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    emp_salary NUMBER
  );
  v_emp Employee;
BEGIN
  v_emp.emp_id := 101;
  v_emp.emp_name := 'John Doe';
  v_emp.emp_salary := 50000;
  DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp.emp_id);
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp.emp_name);
  DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || v_emp.emp_salary);
END;

TABLE: Used TO store a collection OF VALUES, LIKE arrays.
DECLARE
  TYPE NumberTable IS TABLE OF NUMBER;
  v_numbers NumberTable := NumberTable(1, 2, 3, 4, 5);
BEGIN
  FOR i IN v_numbers.FIRST .. v_numbers.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Number: ' || v_numbers(i));
  END LOOP;
END;
-----------------------------3--------------------------
1: Basic Data TYPE
    1. DECLARE Variables: WRITE a PL/SQL block TO DECLARE variables FOR each OF the following                   data types AND assign them appropriate VALUES:

Code:-
DECLARE
    my_number NUMBER := 12345;
    my_char CHAR(10) := 'A';
    my_varchar2 VARCHAR2(50) := 'Hello, PL/SQL!';
    my_boolean BOOLEAN := TRUE;
    my_date DATE := SYSDATE;
    TYPE EmployeeRecord IS RECORD (
        emp_id NUMBER,
        emp_name VARCHAR2(50),
        emp_salary NUMBER
    );
    v_emp EmployeeRecord;
    TYPE NumberTable IS TABLE OF NUMBER;
    v_numbers NumberTable := NumberTable(1, 2, 3, 4, 5);
BEGIN
    v_emp.emp_id := 101;
    v_emp.emp_name := 'John Doe';
    v_emp.emp_salary := 50000;

    DBMS_OUTPUT.PUT_LINE('Number: ' || my_number);
    DBMS_OUTPUT.PUT_LINE('Char: ' || my_char);
    DBMS_OUTPUT.PUT_LINE('Varchar2: ' || my_varchar2);
    DBMS_OUTPUT.PUT_LINE('Boolean: ' || CASE WHEN my_boolean THEN 'TRUE' ELSE 'FALSE' END);
    DBMS_OUTPUT.PUT_LINE('Date: ' || TO_CHAR(my_date, 'DD-MON-YYYY HH24:MI:SS'));

    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp.emp_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp.emp_name);
    DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || v_emp.emp_salary);

    FOR i IN v_numbers.FIRST .. v_numbers.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Number: ' || v_numbers(i));
    END LOOP;
END;
/


2: DATE Manipulation

CODE:-

DECLARE
    date1 DATE := SYSDATE;
    date2 DATE := TO_DATE('2024-01-01', 'YYYY-MM-DD');
    days_diff NUMBER;
BEGIN
    days_diff := date1 - date2;
    DBMS_OUTPUT.PUT_LINE('Days between dates: ' || days_diff);
    DBMS_OUTPUT.PUT_LINE('Formatted current date: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/
3: BOOLEAN Logic

CODE:-
DECLARE
    bool1 BOOLEAN := TRUE;
    bool2 BOOLEAN := FALSE;
BEGIN
    IF bool1 AND bool2 THEN
        DBMS_OUTPUT.PUT_LINE('Both are TRUE');
    ELSE
        DBMS_OUTPUT.PUT_LINE('At least one is FALSE');
    END IF;
    -- Boolean operation example
    DECLARE
        result BOOLEAN := TRUE AND FALSE;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Result of TRUE AND FALSE: ' || CASE WHEN result THEN 'TRUE' ELSE 'FALSE' END);
    END;
END;
/

-----------------------PRACTICAL4-----------------------

Practical Based ON PL/SQL Variables.

Code: - Basic Variable Declaration.

DECLARE
    my_variable NUMBER := 10;
BEGIN
    my_variable := my_variable + 20;
    DBMS_OUTPUT.PUT_LINE('The value of my_variable is: ' || my_variable);
END;




Code: - Calculating Area OF Circle.

DECLARE
    v_radius NUMBER := 5;  -- Radius of the circle
    v_area NUMBER;         -- Variable to store the area
BEGIN
    v_area := 3.14 * v_radius * v_radius;  -- Calculate the area
    DBMS_OUTPUT.PUT_LINE('The area of the circle with radius ' || v_radius || ' is ' || v_area);
END;
/

Code: - Determining Even AND Odd NUMBER.

DECLARE
    v_number NUMBER := 7;  -- The number to check
    v_result VARCHAR2(10); -- Variable to store the result
BEGIN
    IF MOD(v_number, 2) = 0 THEN
        v_result := 'Even';
    ELSE
        v_result := 'Odd';
    END IF;

    DBMS_OUTPUT.PUT_LINE('The number ' || v_number || ' is ' || v_result);
END;
/




Code: - Swapping two numbers.

DECLARE
    v_a NUMBER := 10;  -- First number
    v_b NUMBER := 20;  -- Second number
    v_temp NUMBER;     -- Temporary variable for swapping
BEGIN
    -- Output original values
    DBMS_OUTPUT.PUT_LINE('Before swapping:');
    DBMS_OUTPUT.PUT_LINE('v_a: ' || v_a);
    DBMS_OUTPUT.PUT_LINE('v_b: ' || v_b);

    -- Swap the values
    v_temp := v_a;
    v_a := v_b;
    v_b := v_temp;

    -- Output swapped values
    DBMS_OUTPUT.PUT_LINE('After swapping:');
    DBMS_OUTPUT.PUT_LINE('v_a: ' || v_a);
    DBMS_OUTPUT.PUT_LINE('v_b: ' || v_b);
END;
/


Code: - Calculate Compound Interest
    Formula FOR compound interest: A = P * (1 + r/n) ^ (n*t), WHERE:
    A IS the amount
    P IS the principal
    r IS the annual interest rate
    n IS the NUMBER OF times interest IS compounded per YEAR
    t IS the TIME IN YEAR
DECLARE
    -- Declare variables
    principal NUMBER := 1000;       -- Principal amount
    rate NUMBER := 0.05;            -- Annual interest rate (5%)
    years NUMBER := 5;               -- Number of years
    times_compounded NUMBER := 4;   -- Compounded quarterly

    -- Declare variable to store the final amount
    amount NUMBER;
BEGIN
    -- Calculate compound interest
    amount := principal * POWER((1 + rate / times_compounded), (times_compounded * years));

    -- Output the result
    DBMS_OUTPUT.PUT_LINE('Principal: ' || principal);
    DBMS_OUTPUT.PUT_LINE('Rate of Interest: ' || rate);
    DBMS_OUTPUT.PUT_LINE('Number of Years: ' || years);
    DBMS_OUTPUT.PUT_LINE('Amount after Compound Interest: ' || amount);
END;
/

---------------------PRACTICAL5-------------------------



Aim: Practical based ON PL/SQL Executable statements.
1: Variable Assignment AND Conditional Logic
Code:
DECLARE
    v_score NUMBER := 85; -- Student's score
    v_result VARCHAR2(10); -- Variable to store result
BEGIN
    -- Check if the score is greater than or equal to 50 to determine pass or fail
    IF v_score >= 50 THEN
        v_result := 'Pass';
    ELSE
        v_result := 'Fail';
    END IF;

    -- Output the result
    DBMS_OUTPUT.PUT_LINE('The student has: ' || v_result);
END;
/


2: Using Loops AND Conditional Statements
Code:
DECLARE
    v_number NUMBER := 5; -- Number to calculate factorial for
    v_factorial NUMBER := 1; -- Variable to store the result
BEGIN
    -- Calculate factorial using a loop
    FOR i IN 1..v_number LOOP
        v_factorial := v_factorial * i;
    END LOOP;

    -- Output the result
    DBMS_OUTPUT.PUT_LINE('The factorial of ' || v_number || ' is ' || v_factorial);
END;
/


3. Calculating AND Checking Employee Bonus
Code:
DECLARE
    v_salary NUMBER := 5000;            -- Employee salary
    v_bonus NUMBER;                     -- Bonus to be calculated
    v_threshold NUMBER := 1000;         -- Threshold for bonus check
BEGIN
    -- Calculate bonus as 10% of the salary
    v_bonus := v_salary * 0.10;

    -- Output the calculated bonus
    DBMS_OUTPUT.PUT_LINE('Calculated Bonus: ' || v_bonus);

    -- Check if the bonus exceeds the threshold
    IF v_bonus > v_threshold THEN
        DBMS_OUTPUT.PUT_LINE('Bonus exceeds the threshold.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Bonus does not exceed the threshold.');
    END IF;
END;
/
------------------PRACTICAL NO-6---------------

Aim: Practical based ON PL/SQL EXCEPTION Handling.

Handling ZERO_DIVIDE EXCEPTION WITH USER Input

Code:
DECLARE
    v_num1 NUMBER;
    v_num2 NUMBER;
    v_result NUMBER;
BEGIN
    -- Accept two numbers as input
    v_num1 := &Enter_First_Number;
    v_num2 := &Enter_Second_Number;

    -- Attempt to divide the numbers
    v_result := v_num1 / v_num2;

    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
END;
/



Handling ZERO_DIVIDE EXCEPTION WITH USER Input

Code:
(A)
DECLARE
  v_num1 NUMBER := 1000000000000000;
  v_num2 NUMBER := 1000000000000000;
  v_result NUMBER;
BEGIN
  -- Attempt to multiply two large numbers
  v_result := v_num1 * v_num2;

  -- Output the result
  DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);

EXCEPTION
  -- Handle numeric overflow
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Error: Numeric value is too large.');

  -- Handle any other exceptions
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: An unexpected error occurred.');
END;
/


(B)     VALUE Error
DECLARE
    v_small_number NUMBER(3);
BEGIN
    -- Attempt to assign a value that's too large for the declared variable
    v_small_number := 9999; -- This should cause a VALUE_ERROR

    DBMS_OUTPUT.PUT_LINE('Small Number: ' || v_small_number);
EXCEPTION
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Error: Value exceeds the allowed precision.');
END;
/



(Q)Combining Multiple Exceptions.

Code:
DECLARE
    v_num1 NUMBER := 100;
    v_num2 NUMBER := 0;
    v_result NUMBER;
BEGIN
    -- Attempt to divide numbers and handle exceptions
    v_result := v_num1 / v_num2;

    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Error: Division by zero detected.');
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Error: A value error occurred.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/



(Q)Using Raise_Application_Error.

Code:
DECLARE
    v_emp_salary NUMBER := 3000;
BEGIN
    -- Check if the salary is below the threshold
    IF v_emp_salary < 5000 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary is below the acceptable minimum.');
    END IF;

    DBMS_OUTPUT.PUT_LINE('Salary is acceptable.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/


------------------PRACTICAL NO 7---------------
Aim: Practical based ON PL/SQL Cursors.
Code: Creation OF tables, inserting records, AND using a CURSOR TO process the records.



-- Create departments table
CREATE TABLE departments (
   department_id NUMBER PRIMARY KEY,
   department_name VARCHAR2(50)
);

-- Create employees table
CREATE TABLE employees (
   employee_id NUMBER PRIMARY KEY,
   first_name VARCHAR2(50),
   last_name VARCHAR2(50),
   department_id NUMBER,
   salary NUMBER,
   FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Insert records into departments table
INSERT INTO departments (department_id, department_name) VALUES (10, 'Sales');
INSERT INTO departments (department_id, department_name) VALUES (20, 'Marketing');
INSERT INTO departments (department_id, department_name) VALUES (30, 'Human Resources');

-- Insert records into employees table
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (1, 'John', 'Doe', 10, 60000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (2, 'Jane', 'Smith', 10, 65000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (3, 'Michael', 'Johnson', 20, 70000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (4, 'Emily', 'Davis', 30, 72000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (5, 'William', 'Brown', 20, 75000);

COMMIT;

DECLARE
   -- Define a cursor to fetch employee details for a specific department
   CURSOR emp_cursor IS
      SELECT employee_id, first_name, last_name, salary
      FROM employees
      WHERE department_id = 10; -- Change this ID to fetch for a different department

   -- Define variables to hold fetched data
   v_employee_id employees.employee_id%TYPE;
   v_first_name employees.first_name%TYPE;
   v_last_name employees.last_name%TYPE;
   v_salary employees.salary%TYPE;
BEGIN
   -- Open the cursor
   OPEN emp_cursor;

   -- Fetch and process each record from the cursor
   LOOP
      FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name, v_salary;

      -- Exit the loop if no more records
      EXIT WHEN emp_cursor%NOTFOUND;

      -- Display employee details
      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id ||
                           ', Name: ' || v_first_name || ' ' || v_last_name ||
                           ', Salary: ' || v_salary);
   END LOOP;

   -- Close the cursor
   CLOSE emp_cursor;
END;
/





-----------------PRACTICAL NO 8----------------
Aim: Practical based ON PL/SQL Stored PROCEDURE.
Code: Stored PROCEDURE inserts a NEW employee INTO the employees TABLE.

--Creating a Stored Procedure to Retrieve Employee Details

CREATE TABLE employees (
    employee_id    NUMBER PRIMARY KEY,
    employee_name  VARCHAR2(100),
    salary         NUMBER(10, 2),
    department_id  NUMBER
);


INSERT INTO employees (employee_id, employee_name, salary, department_id) VALUES (101, 'John Doe', 50000, 10);
INSERT INTO employees (employee_id, employee_name, salary, department_id) VALUES (102, 'Jane Smith', 60000, 20);
INSERT INTO employees (employee_id, employee_name, salary, department_id) VALUES (103, 'Michael Johnson', 55000, 10);
INSERT INTO employees (employee_id, employee_name, salary, department_id) VALUES (104, 'Emily Davis', 70000, 30);
INSERT INTO employees (employee_id, employee_name, salary, department_id) VALUES (105, 'William Brown', 65000, 20);


CREATE OR REPLACE PROCEDURE get_employee_details (
    p_employee_id IN employees.employee_id%TYPE
) IS
    v_employee_name employees.employee_name%TYPE;
    v_salary        employees.salary%TYPE;
BEGIN
    SELECT employee_name, salary
    INTO v_employee_name, v_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with ID: ' || p_employee_id);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/


BEGIN
    get_employee_details(101);
END;
/
--Creating a Stored Procedure to Delete an Employee

CREATE OR REPLACE PROCEDURE delete_employee (
    p_employee_id IN employees.employee_id%TYPE
) IS
BEGIN
    DELETE FROM employees WHERE employee_id = p_employee_id;

    IF SQL%ROWCOUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with ID: ' || p_employee_id);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee deleted successfully with ID: ' || p_employee_id);
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        ROLLBACK;
END;
/
BEGIN
    delete_employee(103);
END;
/








-----------------PRACTICAL NO 9----------------
Aim: Practical based ON PL/SQL Functions.
Code:
CREATE TABLE departments (
   department_id NUMBER PRIMARY KEY,
   department_name VARCHAR2(50),
   manager_id NUMBER,
   location_id NUMBER
);
INSERT INTO departments (department_id, department_name, manager_id, location_id) VALUES (10, 'Administration', 200, 1700);
INSERT INTO departments (department_id, department_name, manager_id, location_id) VALUES (20, 'Marketing', 201, 1800);
INSERT INTO departments (department_id, department_name, manager_id, location_id) VALUES (30, 'Purchasing', 202, 1900);
INSERT INTO departments (department_id, department_name, manager_id, location_id) VALUES (40, 'Human Resources', 203, 2000);


COMMIT;
CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  department_id NUMBER,
  salary NUMBER
);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (101, 'John', 'Doe', 10, 60000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (102, 'Jane', 'Smith', 20, 70000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (103, 'Michael', 'Johnson', 10, 65000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (104, 'Mary', 'Brown', 30, 75000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (105, 'William', 'Davis', 20, 72000);

COMMIT;


CREATE OR REPLACE FUNCTION calculate_total_salary (
   p_department_id IN departments.department_id%TYPE
) RETURN NUMBER
IS
   v_total_salary NUMBER := 0;
BEGIN
   -- Calculate the total salary for the given department
   SELECT SUM(salary)
   INTO v_total_salary
   FROM employees
   WHERE department_id = p_department_id;

   -- Return the total salary
   RETURN NVL(v_total_salary, 0);
END calculate_total_salary;
/

DECLARE
   v_total_salary NUMBER;
BEGIN
   v_total_salary := calculate_total_salary(10);
   DBMS_OUTPUT.PUT_LINE('Total Salary for Department 10: ' || v_total_salary);
END;
/


----------------PRACTICAL NO 10----------------
Aim: Practical based ON PL/SQL Triggers.
Code:
-- Create employees table
CREATE TABLE employees (
   employee_id NUMBER PRIMARY KEY,
   first_name VARCHAR2(50),
   last_name VARCHAR2(50),
   salary NUMBER
);

-- Create audit_log table
CREATE TABLE audit_log (
   log_id NUMBER PRIMARY KEY,
   employee_id NUMBER,
   action VARCHAR2(50),
   old_salary NUMBER,
   new_salary NUMBER,