Execution Control Statements – ORACLE PL/SQL Chapter Wise Interview Questions

Execution Control Statements – ORACLE PL/SQL Chapter Wise Interview Questions

Question 1:
What Are the Execution Control Statements in Oracle?
Answer:
PL/SQL supports three groups of execution control statements: IF Statements – Conditionally executes a block of statements.

1. CASE Statements – Selectively executes a block of statements.
2. LOOP Statements – Repeatedly executes a block of statements.
3. GOTO Statements – Unconditional changes the execution flow to a specified statement.

Question 2:
How To Use IF Statements for Multiple Conditions?
Answer:
If you have multiple blocks of codes to be executed based on different conditions, you can use the “IF … ELSIF” statement. Here is a sample script on IF statements:

DECLARE 
day VARCHAR2;
BEGIN
day := 'SUNDAY';
IF day = 'THURSDAY' THEN
DBMS_OUTPUT.PUT_LINE('Checking log files.');
ELSIF day = 'TUESDAY' THEN 
DBMS_OUTPUT.PUT_L1NE('Helping developers. ’);
ELSIF day = 'FRIDAY' THEN DBMS_OUTPUT.PUT_LlNE('Rebuild indexes.');
ELSE
DBMS_OUTPUT.PUT_LINE('Reading some papers.'); 
END IF;
END;

Question 3:
How to use WHILE statements in Oracle?
Answer:
If you have a block of codes to be executed repeatedly based a condition, you can use the “WHILE … LOOP” statement. Here is a sample script on WHILE statements:

DECLARE
declare...
BEGIN
Statements...
WHILE < condition > LOOP 
statements...
END LOOP; 
statements...
END;

Question 4:
How to use FOR statements?
Answer:
If you have a block of codes to be executed repeatedly over a range of values, you can use the “FOR… LOOP” statement. Here is a sample script on FOR statements:

DECLARE 
total NUMBER := 0; 
BEGIN
FOR i IN 1......10 LOOP 
total := total + i;
END LOOP;
END;

Note: The temporary variable “i” used in the FOR loop needs no declaration.

Question 5:
What is NULL in PL/SQL?
Answer:
NULL is a reserved key word and it stands for two things in PL/SQL:

1. NULL is an executable statement, and means doing nothing.
2. NULL is a data value, and means no value.

The following sample script shows you examples of using NULL keyword:

DECLARE 
myVar CHAR(80);
BEGIN
myVar :=NULL; -- init with NULL value

IF myVar IS NOT NULL THEN
DBMS _OUTPUT.PUT_L IN EC ('I am busy.’);
ELSE
DBMS_OUTPUT. PUT_LINE('I am free.');
END IF;
END;

Question 6:
What are Pseudocolumns?
Answer:

1. They are not actual columns. They are like Functions without arguments.
2. They typically give a different value for each row.
   Examples: ROWNUM, NEXTVAL, ROWID, VERSION_STARTTIME

Question 7:
What are the various types of RollBack Segments?
Answer:
The types of Rollback segments are as follows:

1. Public Available to all instances
2. Private Available to specific instance

Question 8:
Is the assignment given below allowed: ABC = PQR (Where ABC and PQR are records)
Answer:
Yes

Question 9:
Is this for loop allowed: For x in &Start…&End Loop
Answer:
Yes

Question 10:
Which symbol precedes the path to the table in the remote database?
Answer:
@