PL/SQL control structures

Conditional control: IF and CASE Statements.

- IF statement- It is a conditional statement in which if the condition is true, the statement is processed.

Example :
IF sal > 10000 THEN
   compute_bonus(empid);
   UPDATE salary SET appraisal = appraisal + bonus WHERE empno = emp_id;
END IF;

CASE statement- It is again a conditional statement that executes the statement if the condition in CASE is true.
IF salary = '1000' THEN
   dbms_output.put_line('LOW SALARY');
ELSIF grade = '2000' THEN
   dbms_output.put_line('HIGH SALARY’);
END IF;

Iterative Control: LOOP and EXIT Statements.

- LOOP:- Loop executes a statement multiple times in loop. EXIT loop forces the LOOP to exit forcibly.

Example:
LOOP
   IF university_rating < 3 THEN
   ...
   EXIT; -- exit loop immediately
   END IF;
END LOOP;

Sequential Control: GOTO and NULL Statements. Explain with an example for each

GOTO statement takes the control to the labeled statement.

Example:
BEGIN
   ...
   GOTO label1;
   ...
   <<label1>>
   INSERT INTO emp VALUES ...
END;

NULL statement- Simply passes the control to the next statement.

Example:
IF salary < 9000 THEN
   compute_appraisal(emp_id);
ELSE
   NULL;
END IF;
PL/SQL collections
PL/SQL collections - What is a Collection?, Explain collection types. i.e. Index-by tables, Nested tables, Varrays, Nested Tables vs. Associative Arrays, Nested Tables vs. Varrays...
PL/SQL record data type
PL/SQL record data type - What is a PL/SQL Record data type?, Define and declare Records, Different Types of Records - Table-based, Cursor-based, Programmer-defined, Benefits of using Records, Guidelines for using Records...
PL/SQL cursors
PL/SQL cursors - What are Cursors?, Types of cursors in PL/SQL - Implicit cursors, Explicit cursors, What are cursor variables?, Significance of SELECT FOR UPDATE clause, Significance of WHERE CURRENT OF clause...
Post your comment

You may like