IF

IF ? THEN ? ELSE

Syntax:

IF condition THEN

Statements; INDENT FOUR SPACES

[ELSIF condition THEN -- This isn't a mis-spelling, the e in Elsif isn't part of the syntax.

statements;] INDENT FOUR SPACES

[ELSE

statements;] INDENT FOUR SPACES

END IF;

Note the indentation above, failure to indent for readability will result in one full letter grade deduction and your work being returned to you to fix. Also I will not help you with any program that is NOT readable.

Example:

ACCEPT p_empno PROMPT 'Enter Employee #';
DECLARE
          V_empno emp.empno%TYPE := &p_empno;
          V_curr_sal emp.sal%TYPE;
          V_avg_sal emp.sal%TYPE;
BEGIN
          SELECT sal INTO v_curr_sal FROM emp
             WHERE empno = v_empno;

SELECT AVG(sal) INTO v_avg_sal FROM emp;

IF (v_curr_sal < 2000 OR v_curr_sal < v_avg_sal) THEN
             UPDATE emp SET sal = sal * 1.1
                    WHERE empno = v_empno;
            ELSIF (v_curr_sal > 4500) THEN
               UPDATE emp SET sal = sal * 1.2
                    WHERE empno = v_empno;
          ELSE
               UPDATE emp SET sal = sal * 1.05
                    WHERE empno = v_empno;
          END IF;

COMMIT WORK;
END;
/

The following is identical to the above. You tell me which is more readable!
ACCEPT p_empno PROMPT 'Enter Employee #';
DECLARE
V_empno emp.empno%TYPE := &p_empno;
V_curr_sal emp.sal%TYPE;
V_avg_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_curr_sal FROM emp
WHERE empno = v_empno;
SELECT AVG(sal) INTO v_avg_sal FROM emp;
IF (v_curr_sal < 2000 OR v_curr_sal < v_avg_sal) THEN
UPDATE emp SET sal = sal * 1.1
WHERE empno = v_empno;
ELSIF (v_curr_sal > 4500) THEN
UPDATE emp SET sal = sal * 1.2
WHERE empno = v_empno;
ELSE
UPDATE emp SET sal = sal * 1.05
WHERE empno = v_empno;
END IF;
COMMIT WORK;
END;
/

LOOPING:

- A simple Loop

Syntax:

LOOP

Statement1; INDENT FOUR SPACES

Statement2; INDENT FOUR SPACES

EXIT [WHEN condition]; INDENT FOUR SPACES

END LOOP;

Continues to loop until the EXIT condition is met or executed.

Example:

ACCEPT p_ord_id PROMPT 'Enter Order ID:';
DECLARE
          V_ord_id item.ordid%TYPE := &p_ord_id;
          V_line_item item.itemid%TYPE := 1;
BEGIN
          LOOP
                    INSERT INTO item (ordid, itemid)
                             VALUES (v_ord_id, v_line_item);

V_line_item := v_line_item +1;

EXIT WHEN v_line_item > 10;
END LOOP;
END;
/

Labeled Loops:
<<outer_loop>>
LOOP
        -- Some commands...
        <<inner_loop>>
        LOOP
                -- Some more commands...
                <<inner_most_loop>>
                LOOP
                        -- Some more commands...
                        EXIT outer_loop;
                END LOOP inner_most_loop;
                -- Some more commands...
       END LOOP inner_loop;
END LOOP outer_loop;

Numeric For Loop:

Syntax:

FOR index IN [REVERSE] lower..upper LOOP
Statement1;
Statement2;
END LOOP;

Example:

ACCEPT p_ord_id PROMPT 'Enter Order ID:';
DECLARE
          V_ord_id item.ordid%TYPE := &p_ord_id;
BEGIN
          FOR v_index IN 1..10 LOOP
                    INSERT INTO item (ordid, itemid)
                             VALUES (v_ord_id, v_index);
          END LOOP;
END;
/

While Loop:

Syntax:

WHILE condition LOOP
Statement1;
Statement2;
END LOOP;

Example:

ACCEPT p_ord_id PROMPT 'Enter Order ID:';
DECLARE
          V_ord_id item.ordid%TYPE := &p_ord_id;
          V_line_item item.itemid%TYPE := 1;
BEGIN
          WHILE v_line_item <= 10 LOOP
                    INSERT INTO item (ordid, itemid)
                             VALUES (v_ord_id, v_line_item);
                    V_line_item := v_line_item +1;
          END LOOP;
END;
/

Goto Statement:

Syntax:

<<label_name>>
statement1;
statement2;

GOTO label_name;

Notes:

- Don?t use GOTO unless absolutely necessary

- Don?t branch into an IF statement, LOOP, or sub-block

Example:

ACCPET p_ord_id PROMPT 'Enter Order ID:';
DECLARE
          V_ord_id item.ordid%TYPE := &p_ord_id;
          V_line_item item.itemid%TYPE := 1;
BEGIN
          <<repeat>>
                    INSERT INTO item (ordid, itemid)
                             VALUES (v_ord_id, v_line_item);

          V_line_item := v_line_item +1;
          IF v_line_item <= 10 THEN
                    GOTO repeat;
          END IF;
END;
/

NO CONTINUE STATEMENT in Oracle through version 10g.

continue statement added to 11g.

while (condition == true)
{
    if (this==that)
        continue;
}

WHILE condition = true LOOP

    IF this=that THEN
        GOTO end_of_loop;
        -- This helps to emulate a contiue statement.
    END IF;

<<end_of_loop>> -- This helps to emulate a contiue statement.
END LOOP;