PL/SQL Cursors: Implicit vs Explicit Knowledge Quiz Quiz

Explore core differences, usage scenarios, and behaviors of PL/SQL implicit and explicit cursors with this quiz designed for beginners. Enhance your understanding of cursor types, their attributes, and their roles in SQL data retrieval within PL/SQL programming.

1. Definition of Implicit Cursor

   Which statement best describes an implicit cursor in PL/SQL?

   1. It is automatically created by PL/SQL to process a single SQL statement.
   2. It needs to be opened and closed manually every time.
   3. It is used only for INSERT statements.
   4. It is a user-defined cursor that requires explicit declaration.

   Explanation: An implicit cursor is automatically managed by PL/SQL whenever a single SQL statement like SELECT INTO, INSERT, UPDATE, or DELETE is executed. Option B describes an explicit cursor, not an implicit one. Option C is incorrect because implicit cursors do not require manual opening or closing. Option D is too narrow; implicit cursors can be used for multiple types of DML statements, not only INSERT.

2. Explicit Cursor Identification

   When must a programmer define an explicit cursor in PL/SQL?

   1. When processing multiple rows returned by a SELECT statement.
   2. When performing arithmetic operations.
   3. When executing an UPDATE statement.
   4. When retrieving only one record from a table.

   Explanation: Explicit cursors are essential for handling queries that return more than one row, allowing the programmer to fetch each row individually. Option A is handled by implicit cursors. Option B uses an implicit cursor by default. Option D is unrelated to cursor management and refers to data processing, not database interaction.

3. Cursor Attribute Scope

   Which cursor attribute can be used with both implicit and explicit cursors in PL/SQL to check if the last SQL statement was successful?

   1. %LOOP
   2. %OPEN
   3. %BULK_ROWCOUNT
   4. %FOUND

   Explanation: The %FOUND attribute checks if the last fetch or DML statement affected any rows and is available for both cursor types. %OPEN is only relevant to explicit cursors, as implicit cursors are always closed automatically. %BULK_ROWCOUNT is primarily used with bulk operations, not standard cursors. %LOOP is not an actual PL/SQL cursor attribute.

4. Cursor Lifecycle Actions

   What is a required step when working with explicit cursors but not with implicit cursors in PL/SQL?

   1. Use the cursor after a DELETE statement.
   2. Declare the cursor with a SELECT statement.
   3. Let PL/SQL automatically handle all operations.
   4. Assign the output to variables automatically.

   Explanation: Explicit cursors must be declared explicitly and associated with a query so they can be used to process multiple rows. Option B is incorrect as DELETE can be handled by implicit cursors. Option C incorrectly describes implicit cursors, not explicit. Option D fails to identify the key difference: explicit cursors require manual steps, while implicit are fully managed by PL/SQL.

5. Implicit Cursor Example

   Given the code: DELETE FROM employees WHERE department_id = 50;, which cursor type does PL/SQL use by default?

   1. Global cursor
   2. Explicit cursor
   3. Static cursor
   4. Implicit cursor

   Explanation: PL/SQL automatically handles DML statements like DELETE using an implicit cursor, so no explicit declaration is necessary. Option B is incorrect because explicit cursors must be declared by the programmer. Global and static cursors (Options C and D) are not actual PL/SQL cursor types.

6. Explicit Cursor Syntax

   What is the correct way to declare an explicit cursor in PL/SQL?

   1. CURSOR cursor_name SELECT ... FROM table_name;
   2. OPEN cursor_name;
   3. DECLARE CURSOR cursor_name IS SELECT ...;
   4. SELECT * FROM table_name INTO cursor_name;

   Explanation: Explicit cursors must be declared with the syntax: DECLARE CURSOR cursor_name IS SELECT ...;. Option B is the step to open a declared cursor. Option C incorrectly attempts to assign a SELECT result directly to a cursor name. Option D is syntactically invalid in PL/SQL.

7. Fetching Data with Cursors

   In order to retrieve each row from a result set using an explicit cursor, which action is necessary?

   1. Rely on PL/SQL to fetch all rows automatically.
   2. Use the FETCH statement repeatedly within a loop.
   3. Assign the SELECT statement directly to variables.
   4. Open the cursor multiple times for each row.

   Explanation: Explicit cursors require the programmer to fetch each row using the FETCH statement, typically inside a loop. PL/SQL does not automatically fetch all rows for explicit cursors (Option B). Option C is valid only for implicit cursors when expecting one row. Opening the cursor multiple times (Option D) is not necessary and would only reset the process.

8. Cursor Attribute for Row Count

   To find the number of rows affected by the last DML statement using an implicit cursor, which attribute should be used?

   1. %ROWS
   2. %COUNT
   3. %AFFECTED
   4. %ROWCOUNT

   Explanation: %ROWCOUNT returns the number of rows affected by the most recent DML statement, making it the correct attribute. %COUNT and %ROWS are not valid PL/SQL attributes for row counting. %AFFECTED is also not a PL/SQL cursor attribute.

9. Primary Use of Implicit Cursors

   For which scenario are implicit cursors primarily used in PL/SQL?

   1. For executing blocks of anonymous PL/SQL code.
   2. For managing file operations in the database.
   3. For processing multiple rows returned by a SELECT query.
   4. For executing single-row queries and DML statements such as INSERT.

   Explanation: Implicit cursors are designed for cases where only one row is returned or for DML operations like INSERT, UPDATE, or DELETE. Option B requires explicit cursors. Options C and D are unrelated to cursor management in PL/SQL.

10. Cursor Closing Responsibility

    Who is responsible for closing explicit cursors in PL/SQL after use?

    1. PL/SQL automatically closes explicit cursors after use.
    2. The PL/SQL programmer must manually close explicit cursors.
    3. The database administrator closes all cursors.
    4. Cursors never need to be closed.

    Explanation: With explicit cursors, the programmer is responsible for closing them using the CLOSE statement to free resources. Option B is true only for implicit cursors; PL/SQL manages their closure automatically. Option C is incorrect because cursor management is a programming responsibility. Option D is inaccurate as leaving cursors open can lead to resource leaks.