Procedural Language SQL Overview

What is the correct syntax for declaring a variable using %TYPE in PL/SQL?

In PL/SQL, which of the following statements can be executed inside a PL/SQL block?

Which type of loop in PL/SQL requires a specific condition to exit before proceeding with further statements?

What will the output be after the execution of this block of code: 'LOOP I := 1; DBMS_OUTPUT.PUT_LINE(‘aI: ’ || I); I := I + 1; IF I > 5 THEN EXIT; END IF; DBMS_OUTPUT.PUT_LINE(‘bI: ’ || I); END LOOP;'?

Which looping structure in PL/SQL iterates through a predefined set of values?

Procedural Language SQL

Use %TYPE to declare variables with the same data type as a specific column in a table.
Syntax: Variable-name Table-name.Column-name %TYPE;
Example: For a variable X with the type of STNO in the STUDENT table: X STUDENT.STNO%TYPE;

PL/SQL - SQL Integration

SQL statements can run within PL/SQL, specifically using DML commands like SELECT, INSERT, UPDATE, and DELETE.
Transaction control statements, such as COMMIT, ROLLBACK, and SAVEPOINT, can also be utilized.
Example of inserting new products:
- Declare a variable P with the type of PRODUCT.PID: P PRODUCT.PID%TYPE;
- Begin block inserts products into the database and commits the transaction after updates.

Looping Structures in PL/SQL

PL/SQL includes five types of looping structures for control flow:
- LOOP...EXIT Loop
- LOOP...EXIT WHEN Loop
- WHILE...LOOP Loop
- FOR Loop

LOOP...EXIT Loop

General format:
- Begin with LOOP and include various statements.
- Use an IF condition to determine when to exit the loop with EXIT.
- End the loop with END LOOP;

Example of LOOP...EXIT Loop

Declared a variable I of type NUMBER(6).
Start with I initialized to 1.
Inside the loop, output the current value of I, increment it, and check if it exceeds 5 to exit.
Demonstrates the progression through iterations, showing the output for both aI and bI.

This quiz covers various aspects of Procedural Language SQL, focusing on variable declaration, SQL integration within PL/SQL, and different looping structures used for control flow. Test your understanding of how to effectively use SQL commands and PL/SQL features in database management.