Oracle condition selection statement and loop statement

Oracle condition selection statement and loop statement

Oracle Condition Selection statements (IF, CASE), LOOP statements (LOOP, WHILE, and FOR), and sequential control statements (GOTO, NULL ).
1. IF condition selection statement
1.1 simple condition judgment

DECLARE

V_sal NUMBER (6, 2 );

BEGIN

SELECT sal INTO v_sal FROM emp WHERE lower (ename) = lower ('& name ');

IF v_sal <2000 THEN

UPDATE emp SET sal = v_sal + 200 WHERE lower (ename) = lower ('& name ');

End if;

END;

 
1.2 dual-condition Branch

DECLARE

V_comm NUMBER (6, 2 );

BEGIN

SELECT comm INTO v_comm FROM emp WHERE empno = & no;

IF v_comm <> 0 THEN

UPDATE emp SET comm = v_comm + 100 WHERE empno = & no;

ELSE

UPDATE emp SET comm = 200 WHERE empno = & no;

End if;

END;

 
1.3 multi-condition Branch

DECLARE

V_job VARCHAR2 (10 );

V_sal NUMBER (6, 2 );

BEGIN

SELECT job, sal INTO v_job, v_sal FROM emp WHERE empno = & no;

IF v_job = 'President 'THEN

UPDATE emp SET sal = v_sal + 1000 WHERE empno = & no;

ELSEIF

V_job = 'manager' THEN

UPDATE emp SET sal = v_sal + 500 WHERE empno = & no;

ELSE

UPDATE emp SET sal = v_sal + 200 WHERE empno = & no;

End if;

END;

 
2. CASE condition selection statement
2.1 use a single selector for equivalent comparison

DECLARE

V_deptno emp. deptno % TYPE;

BEGIN

V_deptno: = & no;

CASE v_deptno

WHEN 10 THEN

UPDATE emp SET comm = 100 WHERE deptno = v_deptno;

WHEN 20 THEN

UPDATE emp SET comm = 80 WHERE deptno = v_deptno;

WHEN 30 THEN

UPDATE emp SET comm = 30 WHERE deptno = v_deptno;

ELSE

DBMS_OUTPUT.PUT_LINE ('the department does not exist ');

End case;

END;

NOTE: If ELSE does not meet the conditions, the processing result is returned by default.

2.2 comparison of multiple conditions in CASE statements
WHEN you use a single condition selector for equivalent comparison, you can use the CASE xxx syntax. If there are multiple conditions for unequal comparison, you must specify the comparison condition in the WHEN clause.

DECLARE

V_sal emp. sal % TYPE;

V_ename emp. ename % TYPE;

BEGIN

SELECT sal, ename INTO v_sal, v_ename FROM emp WHERE empno = & no;

CASE

WHEN v_sal <1000 THEN

UPDATE emp SET comm = 100 WHERE ename = v_ename;

WHEN v_sal <2000 THEN

UPDATE emp SET comm = 80 WHERE ename = v_ename;

WHEN v_sal <6000 THEN

UPDATE emp SET comm = 50 WHERE ename = v_ename;

End case;

END;

 
3. LOOP

DECLARE

I NUMBER (10): = 1;

BEGIN

LOOP

DBMS_OUTPUT.PUT_LINE (I );

I: = I + 1;

Exit when I = 10;

End loop;

END;

Note: The WHEN clause must be used to exit the loop with conditions.
 
4. WHILE Loop
The statement in the loop body is executed at least once in a basic loop. For a WHILE loop, the statement in the loop body is executed only when the condition is TRUE. End loop starts with WHILE .. LOOP.

DECLARE

I NUMBER (10): = 1;

BEGIN

WHILE I <= 10 LOOP

DBMS_OUTPUT.PUT_LINE (I );

I: = I + 1;

End loop;

END;

 
5. FOR Loop

BEGIN

FOR I IN 1 .. 10 LOOP

DBMS_OUTPUT.PUT_LINE (I );

End loop;

END;

 

 

BEGIN

FOR I IN REVERSE 1 .. 10 LOOP

DBMS_OUTPUT.PUT_LINE (I );

End loop;

END;

Note: The keyword REVERSE indicates REVERSE, that is, descending from the maximum value. A FOR loop can be nested.
 
6. GOTO statements
Jump to a tag unconditionally. Eg: goto tag name;
Note: Oracle labels are defined as: <Tag Name>
Oracle uses two single quotes to indicate the escape meaning. For example: ''' to indicate a single quotation mark.
GOTO statements have a great impact on system performance. We recommend that you use them as few as possible.
 
7. NULL statement
Do nothing, just give control to the next statement.