In this Article I Will Write some plsql Loops Interview Questions
Here are the answers to the practical questions on Oracle PL/SQL loops:
1. Basic Loop Structure
Question: Write a basic loop that prints the numbers from 1 to 10.
Answer:
DECLARE
counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(counter);
counter := counter + 1;
EXIT WHEN counter > 10;
END LOOP;
END;
/
2. Using EXIT Condition in Basic Loop
Question: Create a basic loop that counts from 1 to 20 but stops and exits when the counter reaches 15.
Answer:
DECLARE
counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(counter);
counter := counter + 1;
EXIT WHEN counter = 15;
END LOOP;
END;
/
3. Basic Loop with a Conditional Check
Question: Write a basic loop that adds up numbers from 1 to 100, but stops adding when the sum exceeds 500. Print the total sum at the end.
Answer:
DECLARE
counter NUMBER := 1;
total_sum NUMBER := 0;
BEGIN
LOOP
total_sum := total_sum + counter;
IF total_sum > 500 THEN
EXIT;
END IF;
counter := counter + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total sum: ' || total_sum);
END;
/
4. WHILE Loop to Find Factorial
Question: Write a WHILE loop to calculate the factorial of a number, say 6.
Answer:
DECLARE
num NUMBER := 6;
factorial NUMBER := 1;
BEGIN
WHILE num > 0 LOOP
factorial := factorial * num;
num := num - 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Factorial: ' || factorial);
END;
/
5. WHILE Loop with User Input
Question: Create a WHILE loop that repeatedly asks the user to enter a positive number. The loop should only stop when the user enters a number greater than 100.
Answer:
DECLARE
num NUMBER;
BEGIN
WHILE num <= 100 LOOP
-- Assuming user input is handled through substitution variable or as per environment
num := &user_input; -- Example for user input
IF num <= 100 THEN
DBMS_OUTPUT.PUT_LINE('Please enter a number greater than 100.');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('You entered: ' || num);
END;
/
6. WHILE Loop for Counting Down
Question: Write a WHILE loop that counts down from 10 to 1 and prints each number. When it reaches 1, print “Blast Off!”
Answer:
DECLARE
counter NUMBER := 10;
BEGIN
WHILE counter > 0 LOOP
DBMS_OUTPUT.PUT_LINE(counter);
counter := counter - 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Blast Off!');
END;
/
7. FOR Loop to Print Even Numbers
Question: Write a FOR loop that iterates through numbers from 1 to 50 and prints only the even numbers.
Answer:
BEGIN
FOR counter IN 1..50 LOOP
IF MOD(counter, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE(counter);
END IF;
END LOOP;
END;
/
8. FOR Loop with a Step Size
Question: Create a FOR loop that prints numbers from 10 to 100, but only prints every 10th number (e.g., 10, 20, 30,...).
Answer:
BEGIN
FOR counter IN 10..100 BY 10 LOOP
DBMS_OUTPUT.PUT_LINE(counter);
END LOOP;
END;
/
9. FOR Loop with Collection
Question: Create a FOR loop that iterates over a collection of strings (e.g., a list of days of the week) and prints each day in uppercase.
Answer:
DECLARE
TYPE days_type IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
days days_type;
BEGIN
days(1) := 'Monday';
days(2) := 'Tuesday';
days(3) := 'Wednesday';
days(4) := 'Thursday';
days(5) := 'Friday';
days(6) := 'Saturday';
days(7) := 'Sunday';
FOR i IN 1..7 LOOP
DBMS_OUTPUT.PUT_LINE(UPPER(days(i)));
END LOOP;
END;
/
10. Nested Loop for Multiplication Table
Question: Write a nested FOR loop to display the multiplication table for the numbers 1 through 5.
Answer:
BEGIN
FOR i IN 1..5 LOOP
FOR j IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(i || ' x ' || j || ' = ' || (i * j));
END LOOP;
END LOOP;
END;
/
These answers cover the key concepts for each type of loop in Oracle PL/SQL: basic loops, WHILE loops, and FOR loops, including nested loops and working with collections.
Happy Learning
コメント