Skip to content

EXPT11.md

Latest commit

 

History

History
142 lines (131 loc) · 3.2 KB

EXPT11.md

File metadata and controls

142 lines (131 loc) · 3.2 KB
Raw
Give this Repository a ⭐️⭐️ Star ⭐️⭐️ for updates.
COPY PASTE ALL THE QUERIES ONE BY ONE IN SQLPLUS TO EXECUTE IT WITHOUT ANY ERROR

PL/SQL Functions

** Execute this command in sqlplus 1st **

TO display output in the screen – give the below command initially

set serveroutput on;

1) creates a simple procedure that displays the string Hello World!; on the screen when executed

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

2) Create a procedure to find the minimum of two values. HINT - Procedure takes two numbers using the IN mode and returns their minimum using the OUT parameters

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('The minimum of ' || num1 || ' and ' || num2 || ' is ' || min_num);
END;
/

3) Create a procedure, to get cube of passed number.

CREATE OR REPLACE PROCEDURE get_cube (
    num IN NUMBER,
    cube_num OUT NUMBER
) IS
BEGIN
    cube_num := num * num * num;
END;
/
DECLARE
    num NUMBER := 5;
    cube NUMBER;
BEGIN
    get_cube(num, cube);
    DBMS_OUTPUT.PUT_LINE('The cube of ' || num || ' is ' || cube);
END;
/

4) Write a procedure to reverse a input string and check it is palindrome or not.

CREATE OR REPLACE PROCEDURE check_palindrome(input_string IN VARCHAR2) IS
   reversed_string VARCHAR2(4000);
BEGIN
   -- Reverse the input string
   reversed_string := REVERSE(input_string);

   -- Check if the reversed string is equal to the input string
   IF input_string = reversed_string THEN
      DBMS_OUTPUT.PUT_LINE('The input string is a palindrome.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('The input string is not a palindrome.');
   END IF;
END check_palindrome;
/
BEGIN
   check_palindrome('level');
END;
/

5) Write a procedure to delete a specific row from the table already created.

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;
/