| |
Oracle conditional select statements (IF, CASE), loops (LOOP, WHILE, FOR), and sequential control statements (GOTO, NULL).
1, IF condition selection statement
1.1 simple conditions to determine
DECLARE
V_sal NUMBER (6,2);
BEGIN
SELECT sal INTO v_sal FROM emp WHERE lower (ename) = lower ( '&& name');
IFv_sal < 2000 THEN
UPDATE emp SET sal = v_sal + 200 WHERE lower (ename) = lower ( '& name');
END IF;
END;
1.2 Dual conditional branching
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;
Multiple conditional branches
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 select statement
2.1 Use a single selector for equality 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 ( 'does not exist for this department');
END CASE;
END;
Remark: ELSE is the default result when the condition is not satisfied.
2.2 CASE statement in the use of a variety of conditional comparison
You can use the CASE xxx syntax when comparing values using a single condition selector. If you have multiple conditions for unequal comparisons, 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 cycle
DECLARE
I NUMBER (10): = 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE (i);
I: = i + 1;
EXIT WHEN i = 10;
END LOOP;
END;
NOTE: You must conditionally exit the loop using the WHEN clause.
4, WHILE cycle
The basic loop executes at least one statement in the body of the loop, whereas for a WHILE loop, the statement inside the loop is executed only if the condition is TRUE. End of WHILE..LOOP END 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;
Remark: The keyword REVERSE indicates the reverse direction, that is, descending from the maximum value. FOR loops can be nested.
6, GOTO statement
Jump unconditionally to a label. Eg: goto tag name;
NOTE: The Oracle tag is defined as: < < Tag Name >>
Oracle uses two single quotation marks that escape meaning, eg: '' 'represents a single quotation mark.
GOTO statement on the system performance is great, it is recommended to use as little as possible.
7, NULL statement
Do nothing, just give control to the next statement.
|
|
|
|