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.
- CASE Statements – Selectively executes a block of statements.
- LOOP Statements – Repeatedly executes a block of statements.
- 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:
- NULL is an executable statement, and means doing nothing.
- 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:
- They are not actual columns. They are like Functions without arguments.
- 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:
- Public Available to all instances
- 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:
@