Shulou(Shulou.com)06/01 Report--

This paper introduces the process control statements of PL/SQL, including the following three categories:

L control statement: IF statement

L loop statement: LOOP statement, EXIT statement

L sequential statements: GOTO statements, NULL statements

1 conditional statement

IF THEN

PL/SQL and SQL statements

END IF;IF THEN

PL/SQL and SQL statements

ELSE

Other statements

END IF

IF THEN

PL/SQL and SQL statements

ELSIF

< 其它布尔表达式>

THEN

Other statements

ELSIF

< 其它布尔表达式>

THEN

Other statements

ELSE

Other statements

END IF

Tip: ELSIF cannot be written as ELSEIF

Example 1:

SQL > conn hr/hr@pdbtest

Connected.

SQL > set serveroutput on

SQL > DECLARE

2 v_empno employees.employee_id%TYPE: = & empno

3 V_salary employees.salary%TYPE

4 V_comment VARCHAR2 (35)

5 BEGIN

6 SELECT salary INTO v_salary FROM employees

7 WHERE employee_id = v_empno

8 IF v_salary

< 1500 THEN 9 V_comment:= '太少了,加点吧~!'; 10 ELSIF v_salary DECLARE 2 V_grade char(1) := UPPER('&p_grade'); 3 V_appraisal VARCHAR2(20); 4 BEGIN 5 V_appraisal := 6 CASE v_grade 7 WHEN 'A' THEN 'Excellent' 8 WHEN 'B' THEN 'Very Good' 9 WHEN 'C' THEN 'Good' 10 ELSE 'No such grade' 11 END; 12 DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||' Appraisal: '|| v_appraisal); 13 END; 14 / Enter value for p_grade: D old 2: V_grade char(1) := UPPER('&p_grade'); new 2: V_grade char(1) := UPPER('D'); Grade:D Appraisal: No such grade PL/SQL procedure successfully completed. 3 循环 1 简单循环 LOOP 要执行的语句; EXIT WHEN --条件满足，退出循环语句 END LOOP; 3.2 WHILE 循环 WHILE LOOP 要执行的语句; END LOOP; 3.3 数字式循环 [] FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP 要执行的语句; END LOOP [循环标签]; 每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT 退出循环。 实例参见Oracle LOOP循环控制语句 例1 在While循环中嵌套loop循环 SQL>

DECLARE

2 vomerm NUMBER: = 101

3 v_i NUMBER

4 vainn NUMBER: = 0

5 BEGIN

6 WHILE v_m

< 110 LOOP 7 v_i := 2; 8 LOOP 9 IF mod(v_m, v_i) = 0 THEN 10 v_i := 0; 11 EXIT; 12 END IF; 13 14 v_i := v_i + 1; 15 EXIT WHEN v_i >

Vault m-1

16 END LOOP

seventeen

18 IF Veteri > 0 THEN

19 vs. n: = vs. n + 1

20 DBMS_OUTPUT.PUT_LINE ('th'| | vSecretn | | 'primes are' | | vSecretm)

21 END IF

twenty-two

23 vcm: = vumbm + 2

24 END LOOP

25 END

26 /

The first prime is 101.

The second prime is 103.

The third prime is 107.

The fourth prime is 109

PL/SQL procedure successfully completed.

4 label and GOTO

The GOTO statement in PL/SQL means to jump unconditionally to a specified label. The syntax is as follows:

GOTO label

.

/ the label is an enclosed identifier /

Note that it is illegal to use it in the following places, and errors will occur at compile time.

Jump to the front of the non-executable statement.

Jump to a subblock.

Jump to a loop statement.

Jump to a conditional statement.

Jump from the exception handling section to execution.

Jump from one part of a conditional statement to another.

Example 1:

SQL > DECLARE

2 V_counter NUMBER: = 1

3 BEGIN

4 LOOP

5 DBMS_OUTPUT.PUT_LINE (the current value of 'V_counter is:' | | V_counter)

6 V_counter: = v_counter + 1

7 IF v_counter > 10 THEN

8 GOTO labelOffLOOP

9 END IF

10 END LOOP

eleven

12 DBMS_OUTPUT.PUT_LINE (the current value of 'V_counter is:' | | V_counter)

13 END

14 /

The current value of V_counter is: 1

The current value of V_counter is: 2

The current value of V_counter is: 3

The current value of V_counter is: 4

The current value of V_counter is: 5

The current value of V_counter is: 6

The current value of V_counter is: 7

The current value of V_counter is: 8

The current value of V_counter is: 9

The current value of V_counter is: 10

The current value of V_counter is: 11

PL/SQL procedure successfully completed.

5 NULL statement

In the PL/SQL program, the NULL statement is an executable statement, which can be used to explain the meaning of "don't do anything" with the null statement, which is equivalent to a placeholder or an empty statement that does not perform any operation. It can make some sentences meaningful, improve the readability of the program, and ensure the integrity and correctness of other sentence structures. Such as:

Example 1:

SQL > DECLARE

2 v_emp_id employees.employee_id%TYPE

3 v_first_name employees.first_name%TYPE

4 v_salary employees.salary%TYPE

5 v_sal_raise NUMBER (3par 2)

6 BEGIN

7 v_emp_id: = & emp_id

8 SELECT first_name, salary INTO v_first_name, v_salary

9 FROM employees WHERE employee_id = v_emp_id

10 IF v_salary

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.