Scribd
Upload
9 views3 pages

PL SQL Practice Questions

The document outlines the basic structure of PL/SQL blocks, including variable declaration, executable statements, and exception handling. It provides examples of conditional statements, loops, procedures, functions, and user input handling. Each example demonstrates different functionalities of PL/SQL, such as printing messages, performing calculations, and managing exceptions.

Uploaded by

Akash Yadav
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views3 pages

PL SQL Practice Questions

The document outlines the basic structure of PL/SQL blocks, including variable declaration, executable statements, and exception handling. It provides examples of conditional statements, loops, procedures, functions, and user input handling. Each example demonstrates different functionalities of PL/SQL, such as printing messages, performing calculations, and managing exceptions.

Uploaded by

Akash Yadav
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Basic PL/SQL Block Structure

DECLARE
-- Variable declaration
v_message VARCHAR2(100);
BEGIN
-- Executable statements
v_message := 'Hello, PL/SQL!';
DBMS_OUTPUT.PUT_LINE(v_message);
EXCEPTION
-- Exception handling
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred');
END;
/

Example 1: Variable and Condition


FIRST WRITE THIS-SET SERVEROUTPUT ON;
DECLARE
v_num NUMBER := 10;
BEGIN
IF v_num > 5 THEN
DBMS_OUTPUT.PUT_LINE('Number is greater than 5');
ELSE
DBMS_OUTPUT.PUT_LINE('Number is 5 or less');
END IF;
END;
/

Example 2: Loop
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;
END;
/

Example 3: Procedure
CREATE OR REPLACE PROCEDURE greet_user(p_name VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello ' || p_name);
END;
/

Call the procedure:

BEGIN
greet_user('John');
END;
/

Example 4: Function
CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN a + b;
END;
/

Call the function:

DECLARE
result NUMBER;
BEGIN
result := add_numbers(5, 3);
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
END;
/

Example 5: Exception Handling


DECLARE
v_result NUMBER;
BEGIN
v_result := 10 / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Cannot divide by zero');
END;
/
Add Two Numbers (User Input)
SET SERVEROUTPUT ON;

DECLARE
num1 NUMBER;
num2 NUMBER;
result NUMBER;
BEGIN
num1 := &Enter_First_Number;
num2 := &Enter_Second_Number;

result := num1 + num2;

DBMS_OUTPUT.PUT_LINE('Sum = ' || result);


END;
/

You might also like