2. Construct a PL/SQL program to find largest number from the given three numbers.
DECLARE
num1 NUMBER := 45; -- First number
num2 NUMBER := 78; -- Second number
num3 NUMBER := 23; -- Third number
largest NUMBER; -- Variable to store the largest number
BEGIN
IF (num1 >= num2) AND (num1 >= num3) THEN
largest := num1;
ELSIF (num2 >= num1) AND (num2 >= num3) THEN
largest := num2;
ELSE
largest := num3;
END IF;
DBMS_OUTPUT.PUT_LINE('The largest number is: ' || largest);
END;
OUTPUT:-
3. Build a PL/SQL program to generate all prime numbers below 100.
DECLARE
-- Declare variables
n NUMBER := 100; -- Upper limit to check primes below this number
i NUMBER; -- Iterator for the loop to check each number
j NUMBER; -- Iterator to divide i by numbers lower than it
is_prime BOOLEAN;-- Boolean to check if a number is prime
BEGIN
DBMS_OUTPUT.PUT_LINE('Prime numbers below ' || n || ':');
-- Loop through numbers from 2 to n-1 to check for primes
FOR i IN 2..n-1 LOOP
is_prime := TRUE; -- Assume number is prime initially
-- Check divisibility of i by any number less than i
FOR j IN 2..i-1 LOOP
IF (i MOD j = 0) THEN
is_prime := FALSE; -- If divisible, not prime
EXIT; -- No need to check further
END IF;
END LOOP;
-- If number is prime, display it
IF is_prime THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;
OUTPUT:-
4. Construct a PL/SQL program to demonstrate %type and %row type attributes.
CREATE TABLE employees (
emp_id NUMBER(5),
emp_name VARCHAR2(50),
salary NUMBER(10,2),
dept_id NUMBER(5)
);
DECLARE
v_emp_id employees.emp_id%TYPE;
v_emp_name employees.emp_name%TYPE;
emp_record employees%ROWTYPE;
BEGIN
v_emp_id :=142;
v_emp_name := 'Sachin Kumar';
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id);
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
SELECT * INTO emp_record
FROM employees
WHERE emp_id = 142;
DBMS_OUTPUT.PUT_LINE('Employee Details:');
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_record.emp_id);
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_record.emp_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_record.salary);
DBMS_OUTPUT.PUT_LINE('Department ID: ' || emp_record.dept_id);
END;
/
OUTPUT:-
5. Develop a PL/SQL procedure to find reverse of a given number.
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE ReverseNumber IS
v_number NUMBER; -- variable to hold user input
v_reverse NUMBER := 0;
v_temp NUMBER;
BEGIN
v_number := &Enter_a_number;
v_temp := v_number;
WHILE v_temp > 0 LOOP
v_reverse := (v_reverse * 10) + MOD(v_temp, 10);
v_temp := TRUNC(v_temp / 10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Reverse of ' || v_number || ' is ' || v_reverse);
END ReverseNumber;
/
BEGIN
ReverseNumber;
END;
/
OUTPUT:-
6. Create a PL/SQL procedure to update the salaries of all employees by 10% in
their basic pay.
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER PRIMARY KEY,
EMPLOYEE_NAME VARCHAR2(50),
SALARY NUMBER
);
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE UpdateSalaries IS
CURSOR emp_cursor IS
SELECT EMPLOYEE_ID, SALARY
FROM EMPLOYEES;
v_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
v_old_salary EMPLOYEES.SALARY%TYPE;
v_new_salary EMPLOYEES.SALARY%TYPE;
BEGIN
FOR emp_rec IN emp_cursor LOOP
v_employee_id := emp_rec.EMPLOYEE_ID;
v_old_salary := emp_rec.SALARY;
v_new_salary := v_old_salary * 1.10;
UPDATE EMPLOYEES
SET SALARY = v_new_salary
WHERE EMPLOYEE_ID = v_employee_id;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id ||
' - Old Salary: ' || v_old_salary ||
' - New Salary: ' || v_new_salary);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('All salaries have been updated by 10%.');
END UpdateSalaries;
/
BEGIN
UpdateSalaries;
END;
/
OUTPUT:-
7. Execute a PL/SQL procedure to demonstrate IN, OUT and INOUT parameters.
CREATE OR REPLACE PROCEDURE Param_Demo (
p_num IN NUMBER,
p_double OUT NUMBER,
p_inout_num INOUT NUMBER
) IS
BEGIN
p_double := p_num * 2;
p_inout_num := p_inout_num + 15;
DBMS_OUTPUT.PUT_LINE('Inside Procedure:');
DBMS_OUTPUT.PUT_LINE('IN Parameter (p_num): ' || p_num);
DBMS_OUTPUT.PUT_LINE('OUT Parameter (Double of p_num): ' || p_double);
DBMS_OUTPUT.PUT_LINE('INOUT Parameter (Modified Value): ' || p_inout_num);
END Param_Demo;
/
DECLARE
v_num NUMBER := 6;
v_double NUMBER;
v_inout_num NUMBER := 10;
BEGIN
Param_Demo(p_num => v_num, p_double => v_double, p_inout_num => v_inout_num);
DBMS_OUTPUT.PUT_LINE('After Procedure Execution:');
DBMS_OUTPUT.PUT_LINE('OUT Parameter (Double of Number): ' || v_double);
DBMS_OUTPUT.PUT_LINE('INOUT Parameter (Modified Value): ' || v_inout_num);
END;
/
OUTPUT:-
