Procedural Language SQL Overview

Questions and Answers

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

Table-name.Column-name Variable-name%TYPE;

Variable-name Table-name.Column-name%TYPE; (correct)

Table-name.Column-name %TYPE Variable-name;

Variable-name %TYPE Table-name.Column-name;

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

DML statements only

SELECT statements only

DDL statements only

DML and transaction control statements (correct)

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

LOOP...EXIT Loop

LOOP...EXIT WHEN Loop (correct)

FOR Loop

WHILE...LOOP Loop

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;'?

<p>aI: 1 bI: 2 aI: 2 bI: 3 aI: 3 bI: 4 aI: 4 bI: 5 aI: 5</p> Signup and view all the answers

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

<p>FOR Loop</p> Signup and view all the answers

Study Notes

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.

Description

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.