Section A — 1 Mark Questions
1. Define a PL/SQL block. [1 Mark]
Answer: A PL/SQL block is the basic unit of a PL/SQL program, consisting of declarative, executable, and exception-handling sections.
2. Which keyword starts the executable section in a PL/SQL block? [1 Mark]
Answer: The BEGIN keyword.
3. What is the purpose of the DECLARE section? [1 Mark]
Answer: It is used to define variables, constants, cursors, and user-defined exceptions.
4. Identify the symbol used for assignment in PL/SQL. [1 Mark]
Answer: The := operator.
5. What keyword marks the end of a PL/SQL block? [1 Mark]
Answer: The END keyword.
6. Name the conditional statement used for decision making in PL/SQL. [1 Mark]
Answer: The IF-THEN-ELSE statement.
7. Which loop executes at least once before checking the condition? [1 Mark]
Answer: The LOOP...EXIT WHEN statement.
8. What is a cursor in PL/SQL? [1 Mark]
Answer: A cursor is a pointer to a private SQL work area that stores results of a query.
9. Define a database trigger. [1 Mark]
Answer: A trigger is a stored program unit that executes automatically in response to specific events on a table or database.
10. What is the purpose of the %TYPE attribute? [1 Mark]
Answer: It declares a variable to have the same data type as a previously declared variable or table column.
11. Name the attribute used to fetch the number of rows affected by a DML statement. [1 Mark]
Answer: %ROWCOUNT.
12. What does the EXCEPTION section handle? [1 Mark]
Answer: It handles errors or abnormal conditions that occur during execution.
13. Which statement is used to exit a loop prematurely? [1 Mark]
Answer: The EXIT statement.
14. What is an explicit cursor? [1 Mark]
Answer: A programmer-defined cursor used to process multiple rows of a query result set.
15. Name the trigger event that fires before an INSERT operation. [1 Mark]
Answer: BEFORE INSERT.
16. What is the role of the WHEN clause in a trigger? [1 Mark]
Answer: It specifies a condition that must be met for the trigger body to execute.
17. How is a constant declared in PL/SQL? [1 Mark]
Answer: By using the CONSTANT keyword during variable declaration.
18. What does %ISOPEN return? [1 Mark]
Answer: It returns TRUE if the cursor is open and FALSE otherwise.
19. Can a trigger be disabled? [1 Mark]
Answer: Yes, a trigger can be disabled or enabled using the ALTER TRIGGER statement.
20. Which clause is used to handle multiple conditions in an IF statement? [1 Mark]
Answer: The ELSIF clause.
Section B — 2/3 Mark Questions
1. Differentiate between IF-THEN and IF-THEN-ELSE statements. [2 Marks]
Answer: An IF-THEN statement executes a set of actions only if the specified condition is true. An IF-THEN-ELSE statement provides an alternative path, executing one block of code if the condition is true and a different block if the condition is false.
2. Describe the structure of a basic loop. [2 Marks]
Answer: A basic loop starts with the LOOP keyword and ends with END LOOP. Inside, it requires an EXIT WHEN statement to prevent an infinite loop by specifying a condition under which the iteration must stop.
3. What is the difference between %TYPE and %ROWTYPE? [3 Marks]
Answer: %TYPE is used to declare a variable to match the data type of a single column or variable. %ROWTYPE is used to declare a record variable that represents an entire row of a table or a cursor, automatically matching the data types of all columns in that row.
4. List three attributes of an explicit cursor. [3 Marks]
Answer: Three common explicit cursor attributes are: 1. %ISOPEN (checks if cursor is open), 2. %FOUND (returns true if the last fetch returned a row), and 3. %NOTFOUND (returns true if the last fetch failed to return a row).
5. Explain the concept of a Trigger Event. [2 Marks]
Answer: A trigger event refers to the specific database action—such as INSERT, UPDATE, or DELETE—that causes a trigger to fire. These events define the timing and context in which the automated logic is executed.
6. Why are cursors necessary in PL/SQL? [3 Marks]
Answer: Cursors are necessary because SQL is set-oriented while PL/SQL is procedural. Cursors allow the developer to process the result set of a query one row at a time, providing the control required for complex business logic that cannot be handled by a single set-based SQL statement.
7. What are the components of a FOR loop in PL/SQL? [2 Marks]
Answer: A FOR loop consists of a loop counter (implicitly declared), a range specified by a lower and upper bound, and the statements to be executed within the loop iteration.
8. How do you handle errors in PL/SQL? [3 Marks]
Answer: Errors are handled in the EXCEPTION section of a block. When an error occurs, control transfers to this section where specific predefined exceptions (like NO_DATA_FOUND) or user-defined exceptions can be caught and processed to prevent program failure.
9. What is the purpose of the RAISE statement? [2 Marks]
Answer: The RAISE statement is used to explicitly trigger a user-defined or predefined exception, allowing the programmer to enforce business rules and force the control flow to jump to the exception handling section.
10. Explain the difference between Row-level and Statement-level triggers. [3 Marks]
Answer: Row-level triggers execute once for every single row affected by an SQL statement (e.g., updating 10 rows fires it 10 times). Statement-level triggers execute only once for the entire SQL statement, regardless of how many rows are affected.
Section C — 5 Mark Questions
1. Explain the architecture of a PL/SQL block in detail. [5 Marks]
Answer: A PL/SQL block is a structured unit divided into four logical parts:
- DECLARE: An optional section where variables, constants, and cursors are defined for use within the block.
- BEGIN: The mandatory section where the primary logic and executable code reside.
- EXCEPTION: An optional section where the program catches and handles errors that occurred during the execution.
- END: The mandatory keyword that signifies the end of the block.
The block structure is designed to modularize code, improve maintainability, and ensure that variables are scoped locally to the block in which they are defined.
2. Describe the syntax and working of a WHILE loop with an example. [5 Marks]
Answer: The WHILE loop is a conditional loop that continues to execute as long as a specified Boolean condition remains true. If the condition is false at the start, the loop body is never executed.
Syntax
WHILE condition LOOP statement; END LOOP;
Example
DECLARE counter NUMBER := 1; BEGIN WHILE counter ≤ 5 LOOP DBMS_OUTPUT.PUT_LINE(counter); counter := counter + 1; END LOOP; END;
This loop initializes a counter to 1, checks if it is less than or equal to 5, prints the value, increments it, and repeats until the counter exceeds 5.
3. Explain the lifecycle of an explicit cursor. [5 Marks]
Answer: Using an explicit cursor involves a specific lifecycle to manage data processing:
- Declaration: Declaring the cursor name and associating it with a SELECT statement.
- Opening: The OPEN statement allocates memory and executes the query to populate the active set.
- Fetching: Using the FETCH statement to retrieve row data into variables. This is usually done inside a loop.
- Closing: The CLOSE statement releases the memory associated with the cursor once processing is complete.
This process ensures that system resources are managed efficiently when handling large datasets that require row-by-row procedural logic.
4. Discuss the types and components of database triggers. [5 Marks]
Answer: A trigger is a stored procedure that fires automatically based on table events. Its components include:
- Triggering Event: The DML statement (INSERT, UPDATE, DELETE) that initiates the trigger.
- Trigger Timing: Whether it fires BEFORE or AFTER the event.
- Trigger Level: Whether it operates at the ROW level or STATEMENT level.
- Trigger Body: The PL/SQL code block that defines the logic to be performed.
- WHEN Clause: An optional filter that restricts the trigger to fire only if a specific condition is met.
Triggers are powerful tools for auditing changes, enforcing complex referential integrity, and automating data synchronization.
5. Elaborate on conditional statements in PL/SQL with a focus on IF-THEN-ELSIF logic. [5 Marks]
Answer: Conditional statements allow PL/SQL blocks to make decisions based on changing data. The IF-THEN-ELSIF construct is the most robust method for handling multiple mutually exclusive paths.
Mechanism
The engine evaluates the IF condition first. If it is FALSE, it moves to the ELSIF conditions sequentially. If any ELSIF condition is TRUE, that block is executed. If none match, the final ELSE block (if present) is executed.
Importance
This structure is critical for complex business logic where different outcomes depend on specific data values. It is highly readable and ensures that only one branch of the logic executes, preventing unintended side effects during processing.