本讲内容包括：
介绍控制语句的类型和用法
IF语句的结构
CASE表达式
介绍不同的循环语句
使用嵌套循环和标签
一：在PL/SQL中可以使用IF和LOOP进行分支判断和嵌套循环
IF语句的结构主要有：
– IF-THEN-END IF
– IF-THEN-ELSE-END IF
– IF-THEN-ELSIF-END IF
IF语句的语法：
- IF condition THEN
- statements;
- [ELSIF condition THEN
- statements;]
- [ELSE
- statements;]
- END IF;
condition是布尔型的变量(TRUE,FALSE,NULL)和表达式,只有条件返回为TRUE的时候后面的语句才会执行：
condition is a Boolean variable or expression (TRUE, FALSE,or NULL). (It is associated with a sequence of statements, which is executed only if the expression yields TRUE.)
THEN is a clause that associates the Boolean expression that precedes it with the sequence of statements that follows it. statements can be one or more PL/SQL or SQL statements. (They may include further IF statements containing several nested IF, ELSE,and ELSIF statements.)
ELSIF is a keyword that introduces a Boolean expression. (If the first condition yields FALSE or NULL then the ELSIF keyword introduces additional conditions.)
ELSE is a keyword that executes the sequence of statements that follows it if the control reaches it.
复合型的IF条件语句，可以使用AND和OR操作符
- IF v_ename = 'Vargas' AND salary > 6500 THEN
- v_deptno := 60;
- END IF;
可以嵌套IF条件语句
- IF condition1 THEN
- statement1;
- ELSE
- IF condition2 THEN
- statement2;
- END IF;
- END IF;
- IF condition1 THEN
- statement1;
- ELSIF condition2 THEN
- statement2;
- ELSIF condition3 THEN
- statement3;
- END IF;
二：CASE表达式，可以使用CASE表达式，匹配并返回结果
- CASE selector
- WHEN expression1 THEN result1
- WHEN expression2 THEN result2
- ...
- WHEN expressionN THEN resultN
- [ELSE resultN+1;]
- END;
- SQL> set serveroutput on
- SQL> set verify off
- SQL> define p_1=c
- DECLARE
- v_1 CHAR(1) := UPPER('&p_1');
- v_2 VARCHAR2(20);
- BEGIN
- v_2 :=
- CASE v_1
- WHEN 'A' THEN 'ONE'
- WHEN 'B' THEN 'TWO'
- WHEN 'C' THEN 'THREE'
- ELSE 'NOTHING'
- END;
- DBMS_OUTPUT.PUT_LINE ( v_2 || ' map ' || v_1);
- END;
- SQL> /
- THREE map C
- PL/SQL 过程已成功完成
三：处理空值
任何值(包括空值)和空值比较返回空值，如果IF语句的条件返回为空，则后面的分支不会执行
四：loop循环；loop循环分basic,for,while三种
- basic循环语法：
- LOOP -- delimiter
- statement1; -- statements
- ...
- EXIT [WHEN condition]; -- EXIT statement
- END LOOP; -- delimiter
- condition is a Boolean variable or
- expression (TRUE, FALSE, or NULL);
- for循环语法：
- FOR counter IN [REVERSE]
- lower_bound..upper_bound LOOP
- statement1;
- statement2;
- ...
- END LOOP;
- DECLARE
- v_lower NUMBER := 1;
- v_upper NUMBER := 100
- BEGIN
- FOR i IN v_lower..v_upper LOOP
- ...
- END LOOP;
- END;
循环指导原则：
Use the basic loop when the statements inside the loop must execute at least once.
Use the WHILE loop if the condition has to be evaluated at the start of each iteration.
Use a FOR loop if the number of iterations is known.
五：嵌套循环，循环可以被无限嵌套，在不同的循环块上使用标签，使用exit退出循环
- BEGIN
- <<Outer_loop>>
- LOOP
- v_counter := v_counter+1;
- EXIT WHEN v_counter>10;
- <<Inner_loop>>
- LOOP
- ...
- EXIT Outer_loop WHEN total_done = 'YES';
- -- Leave both loops
- EXIT WHEN inner_done = 'YES';
- -- Leave inner loop only
- ...
- END LOOP Inner_loop;
- ...
- END LOOP Outer_loop;
- END;
本文转自斩月博客51CTO博客，原文链接http://blog.51cto.com/ylw6006/656775如需转载请自行联系原作者
ylw6006
