Ex-11.md

PL/SQL Procedures

set serveroutput on;

1) Procedure to display 'Hello World!'

CREATE OR REPLACE PROCEDURE display_hello_world
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World!');
END;

EXECUTE display_hello_world;

2) Procedure to find the minimum of two values

CREATE OR REPLACE PROCEDURE find_minimum(
    num1 IN NUMBER,
    num2 IN NUMBER,
    min_num OUT NUMBER
)
IS
BEGIN
  IF num1 < num2 THEN
    min_num := num1;
  ELSE
    min_num := num2;
  END IF;
END;

DECLARE
  num1 NUMBER := 10;
  num2 NUMBER := 5;
  min_num NUMBER;
BEGIN
  find_minimum(num1, num2, min_num);
  DBMS_OUTPUT.PUT_LINE('Minimum value: ' || min_num);
END;

3) Procedure to get cube of a passed number

CREATE OR REPLACE PROCEDURE get_cube(
    num IN NUMBER,
    cube OUT NUMBER
)
IS
BEGIN
  cube := num * num * num;
END;

4) Procedure to reverse an input string and check if it's palindrome or not

CREATE OR REPLACE PROCEDURE check_palindrome(
    str IN VARCHAR2,
    is_palindrome OUT BOOLEAN
)
IS
  reversed_str VARCHAR2(100);
BEGIN
  -- Reverse the input string
  FOR i IN REVERSE 1..LENGTH(str)
  LOOP
    reversed_str := reversed_str || SUBSTR(str, i, 1);
  END LOOP;

  -- Check if the reversed string is same as original string
  IF reversed_str = str THEN
    is_palindrome := TRUE;
  ELSE
    is_palindrome := FALSE;
  END IF;
END;

DECLARE
  str VARCHAR2(100) := 'racecar';
  is_palindrome BOOLEAN;
BEGIN
  check_palindrome(str, is_palindrome);
  IF is_palindrome THEN
    DBMS_OUTPUT.PUT_LINE(str || ' is a palindrome.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(str || ' is not a palindrome.');
  END IF;
END;

5) Procedure to delete a specific row from a table

CREATE TABLE student4 (
  id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(100) NOT NULL,
  email VARCHAR2(100) UNIQUE,
  phone VARCHAR2(20),
  age NUMBER(3),
  gender VARCHAR2(10),
  address VARCHAR2(200)
);

INSERT INTO student4 (id, name, email, phone, age, gender, address)
VALUES (101, 'John Smith', 'john.smith@example.com', '555-1234', 25, 'Male', '123 Main St');

INSERT INTO student4 (id, name, email, phone, age, gender, address)
VALUES (202, 'Jane Doe', 'jane.doe@example.com', '555-5678', 22, 'Female', '456 Maple Ave');

INSERT INTO student4 (id, name, email, phone, age, gender, address)
VALUES (303, 'Bob Johnson', 'bob.johnson@example.com', '555-2468', 28, 'Male', '789 Elm St');

CREATE OR REPLACE PROCEDURE delete_row(
    row_id IN NUMBER
)
IS
BEGIN
  DELETE FROM student4 WHERE id = row_id;
  COMMIT;
END;

DECLARE
  row_id NUMBER := 101;
BEGIN
  delete_row(row_id);
  DBMS_OUTPUT.PUT_LINE('Row with ID ' || row_id || ' deleted.');
END;