PL/SQL--flow control statement

First, Introduction

Like programming languages, Oracle PL/SQL also has its own process control statements. With Process Control statements, we can implement more complex business logic operations in PL/SQL. Without going to the program to control, to a certain extent, improve the efficiency, which is the strength of PL/SQL. There are several types of PL/SQL Process Control statements:

• Control statements: Including if statements, case statements
• Loop statement: Includes LOOP statement, while statement, for statement
• Sequential statements: including GOTO statements

II. Types of statements

1. Control statements

A, if statement

The syntax is as follows:

IF condition Statement Tehn execution statement

elsif Conditional Statement Tehn execution statement

ELSE EXECUTE statement

END IF;

Examples are as follows:

DECLAREI Number; Str VARCHAR2( -) := 'b';BEGIN    --Digital Judgment    if(I= 6) Then                NULL;--If nothing is done, it is recommended to write NULL to guarantee the integrity of the statementelsif (i is NULL) ThenDbms_output.put_line ('NULL'); ELSEDbms_output.put_line ('NULL'); END IF; --string Judgments    if(Str = 'a') ThenDbms_output.put_line ('if'); elsif (Str = 'b') ThenDbms_output.put_line ('elsif'); ELSEDbms_output.put_line ('Else'); END IF;END;

B. Case statement

The syntax is as follows:

1. Variable judgment

Case variable

When value then EXECUTE statement

When value then EXECUTE statement

END case;

2. Expression judgment

Case

When expression Tehn EXECUTE statement

When expression Tehn EXECUTE statement

END case;

The sample code is as follows:

DECLARE    Str VARCHAR2( -) := 'B'; Num Number:=  -; StrresultVARCHAR2(Ten); NumresultVARCHAR2(Ten);BEGIN    --Variable judgment     Case Str             when 'A'  ThenStrresult:= 'Excellent';  when 'B'  ThenStrresult:= 'Good';  when 'C'  ThenStrresult:= 'qualified';  when 'D'  ThenStrresult:= 'inferior lattice'; END  Case; --Expression Judgment     Case         whenNum<  -  ThenNumresult:= 'inferior lattice';  whenNum<  -  ThenNumresult:= 'qualified';  whenNum<  -  ThenNumresult:= 'Good';  whenNum<  -  ThenNumresult:= 'Excellent'; END  Case;    Dbms_output.put_line (strresult); Dbms_output.put_line (Numresult);END;

2. Circular statements

A, loop statement

The syntax is as follows:

LOOP

EXECUTE statement

EXIT when condition

END LOOP;

Example code:

 declare   i  number : =  0   begin   LOOP I:  =  i 1  ;         Dbms_output.put_line (i);  exit  when  i =  5   end   LOOP;  end ; 

B, while statement

The syntax is as follows:

While condition LOOP

EXECUTE statement

END LOOP;

--Print I the value after each incrementDECLAREI Number:= 0 ;BEGIN     whileI< 5LOOP I:=I+ 1 ; Dbms_output.put_line ('I:' ||i); ENDLOOP;END;

C, for statement

The syntax is as follows:

For variable in (REVERSE) digits: Digital LOOP

EXECUTE statement

END LOOP;

Plus reverse is the reverse of the numbers.

The sample code is as follows:

--index is set for the start.BEGIN    --print numbers before 1 to 5     forIinch 1..5LOOP Dbms_output.put_line ('I:' ||i); ENDLOOP; --Print 5-to 20-in reverse order     forJinch REVERSE 5.. -LOOP Dbms_output.put_line ('J:' ||j); ENDLOOP;END;

　3. Sequential statements

A, goto statement

The syntax is as follows:

GOTO label;

A goto statement is typically a row that jumps to another line.

The sample code is as follows:

1 DECLARE2I Number:= 1 ;3 BEGIN4 Loop5 Dbms_output.put_line (i);6I=I+ 1 ;7     IFI=10 Then8         GOTOlabel;9     END IF ;Ten ENDLoop; One     <<Label>>  ADbms_output.put_line ('label') ; - END;

In this example I always loop plus one, when I equals 10 o'clock, jump out of the loop, skip to line 11th and execute.

PL/SQL--flow control statement