PL/SQL学习笔记[5]-流程控制语句

1 条件分支语句

1.1 IF语句

语法

 写道

IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;

 

 

示例

 

DECLARE
	v_age NUMBER(3);
BEGIN
	SELECT age INTO v_age FROM user
	WHERE lower(name) = lower('&&name');
	IF v_age < 20 THEN
		UPDATE user SET age = v_age*2 WHERE lower(name) = lower('&&name');
	ELSIF v_age < 40 THEN
	  UPDATE user SET age = v_age*3 WHERE lower(name) = lower('&&name');
	ELSE
		UPDATE user SET age = v_age/2 WHERE lower(name) = lower('&&name');
	END IF;
END;

 

 

1.2 CASE语句

语法

 

 写道

[单一条件]
CASE selector
WHEN expression1 THEN sequence_of_statements1;
WHEN expression1 THEN sequence_of_statements2;
...
WHEN expression1 THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE;

[多条件]

CASE
WHEN selector_condition1 THEN sequence_of_statements1;
WHEN selector_condition2 THEN sequence_of_statements2;
...
WHEN selector_conditionN THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE;

 

 

示例

 

DECLARE
	v_uid  user.uid%TYPE;
BEGIN
	v_uid := &id;
	CASE v_uid
		WHEN 1 THEN UPDATE user SET age = 20 WHERE uid = v_uid;
		WHEN 2 THEN UPDATE user SET age = 40 WHERE uid = v_uid;
		ELSE
			dbms_out.put_line('不存在该用户');
	END CASE;
END;

DECLARE
	v_uid  user.uid%TYPE;
BEGIN
	v_uid := &id;
	CASE 
		WHEN v_uid == 1 THEN UPDATE user SET age = 20 WHERE uid = v_uid;
		WHEN v_uid == 2 THEN UPDATE user SET age = 40 WHERE uid = v_uid;
		ELSE
			dbms_out.put_line('不存在该用户');
	END CASE;
END;

 

 

2 循环语句

2.1 LOOP 循环

语法

 

 写道

LOOP
statement1;
EXIT [WHEN condition];
END LOOP;

使用该语句statement1至少会被执行一次。相当于do...while

 

示例

 

DELCARE
	i INT :=1;
BEGIN
	LOOP
		UPDATE user SET createDate = SYSDATE WHERE uid = i;
		EXIT WHEN i = 10 ;
		i := i+1;
		
		
	END LOOP;
	COMMIT;
END;

 

2.2 WHILE 循环

语法

 

 写道

WHILE condition LOOP
statement1;
statement2;
...
END LOOP;

 

 

示例

 

DECLARE 
	i INT := 1;
BEGIN
	WHILE i <= 10 LOOP
		UPDATE user SET createDate = SYSDATE WHERE uid = i;
		i := i+1;
	END LOOP;
	COMMIT;			
END;

 

 

 

2.3 FOR 循环

语法

 

 写道

FOR counter IN [REVERSE]
min_bound..upper_bound LOOP
statement1;
statement1;
...
END LOOP;

 

counter : 循环控制变量，由Oracle隐含定义，不需要显示定义

min_bound、upper_bound ：循环控制变量的上下界；

默认情况下FOR循环在每执行一次后，控制变量会自增一；如果指定REVERSE选项，则会减一

 

示例

 

BEGIN
	FOR i IN 1..10 LOOP
		UPDATE user SET createDate = SYSDATE WHERE uid = i;
	END LOOP;
	COMMIT;
END;

 

 

2.4 嵌套循环和标号

 

-- 该示例中的<<waibu>>、<<neibu>>为标号，该名称可以自定义。
DECLARE
	result INT;
BEGIN
	<<waibu>>
	FOR i IN 1..10 LOOP
		<<neibu>>
		FOR j IN 1..10 LOOP
		result := i * j;
		dbms_output.put_line(result);
		EXIT waibu WHEN result = 10; -- 当 result=10 时，退出外部循环，本例中是当i=5时退出。相当于调用break语句
		EXIT WHEN j = 2; -- 当 j = 2 时，退出内部循环。相当于调用break语句
		END LOOP neibu;
	END LOOP waibu;
	dbms_output.put_line(result);
END;

 

 

3 顺序控制语句

3.1 GOTO 语句

语法

 

 写道

GOTO label_name;

 

 GOTO语句用于跳转到特定标号处。一般不建议使用

 

示例

 

DECLARE
	i INT := 1; 
BEGIN 
	LOOP
		IF i = 10 THEN
			GOTO jump_loop;
		END IF;
		EXIT WHEN i > 11;
		dbms_output.put_line('i-->'||i); -- 这条语句是不会被执行的！
		i := i+1;
	END LOOP;
	<<jump_loop>>
		dbms_output.put_line('i == 10 ! ');
END;

 

 

3.2 NULL 语句

NULL 语句不执行任何操作，并且直接将控制传递到下一条语句。使用NULL可以提高PL/SQL程序的可读性

 

DECLARE
	v_uid user.uid%TYPE := &di;
	v_age user.age%TYPE;
BEGIN
	SELECT age INTO v_age FROM user WHERE uid = v_uid;
	IF v_age < 20 THEN
		UPDATE user SET age = 100 WHERE uid = v_uid;
		commit;
	ELSE
		NULL;
	END IF;
END;