Oracle condition selection statement and loop statement

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.