Database and SQL

Within a PL/SQL block, only DML (Data Manipulation Language) commands like SELECT, INSERT, UPDATE, and DELETE are allowed. DDL (Data Definition Language) commands like CREATE and DROP cannot be used directly in PL/SQL - they require dynamic SQL.

Only SELECT statements use the INTO clause to capture returned values. INSERT, UPDATE, and DELETE are DML commands that modify data directly and don't return data to be stored in variables.

When the IF condition evaluates to NULL in PL/SQL, it's treated as FALSE and the entire IF-THEN-ELSE construct is skipped - neither the IF nor ELSE blocks execute. PL/SQL doesn't process NULL as a separate logical state in conditional constructs.

PL/SQL iterative control constructs include Cursor For Loop (iterates over cursor rows), Simple Loop (basic LOOP with EXIT), Numeric For Loop (iterates over number range), and While Loop (condition-based iteration). Cursors themselves aren't loops, and Exit Loop isn't a standalone construct.

Simple Loop is called an infinite loop because it has no built-in termination condition. It continues indefinitely until explicitly exited using an EXIT or EXIT WHEN statement. Unlike For Loops (with counter limits) or While Loops (with conditions), Simple Loop relies entirely on manual exit logic.

Statement labels in PL/SQL can name loops and blocks for control flow purposes like EXIT label_name or GOTO label_name. They cannot be used with IF-THEN-ELSE constructs or exception handlers directly - those structures use END IF and END exception_name instead.

Implicit cursors are automatically created by PL/SQL for all single-row DML operations (INSERT, UPDATE, DELETE) and single-row SELECT...INTO statements. Multi-row SELECT statements require explicit cursors with loops to process multiple rows.

Explicit cursors are explicitly declared and controlled by the programmer to process multi-row SELECT statements. They provide detailed control over row-by-row processing using OPEN, FETCH, and CLOSE operations, which implicit cursors cannot handle.

The %ROWCOUNT attribute returns the number of rows fetched so far by an open cursor or affected by DML statements. In contrast, %FOUND and %NOTFOUND are boolean attributes indicating whether a row was fetched, and %ISOPEN returns a boolean indicating whether the cursor is open or closed.

The %FOUND cursor attribute evaluates to TRUE if the most recent FETCH statement returned at least one row. %NOTFOUND evaluates to TRUE if no rows were returned, %ROWCOUNT returns the number of rows, and %ISOPEN checks if the cursor is open.

%NOTFOUND evaluates to TRUE when a FETCH operation fails to return a row (no more rows), and FALSE when at least one row is successfully returned. This attribute is commonly used to check if a cursor has exhausted its rows after a fetch operation. %FOUND does the opposite (TRUE when a row is found).

SUBSTR('Oracle Basics', -5, 3) starts 5 characters from the end, giving 'sics' starting from position -5, then extracts 3 characters: 'asi'. Negative positions count from the string's end. So position -5 is 's', -4 is 'i', -3 is 'c', -2 is 's', -1 is the last character. Starting at -5, three characters gives 'asi'.

The MOD function returns the remainder when the first number is divided by the second. Here, 1600 divided by 300 equals 5 with a remainder of 100 (since 300 × 5 = 1500). Option B correctly shows 100 as the remainder. Options A and C (300 and 500) are the divisor and a multiple respectively, while D (000) is just a formatting variation of zero.

The COUNT() function is specifically designed to return the number of rows in a result set or table. SUM adds numeric values, num_rows is not a standard SQL function (it may exist in specific programming interfaces), and 'total' is not a built-in SQL function. COUNT is the universal SQL method for row counting.

The WHEN OTHERS exception handler is a catch-all block that handles any exceptions not explicitly caught by preceding handlers. Because it catches everything, it must be the last handler in a PL/SQL exception block.