PL/SQL Process Control statements for Oracle databases

Describes the Process Control statements for PL/SQL, including the following three classes:

1. Control statement: IF statement

2. Loop statement: Loop statement, EXIT statement

3. Sequential statement: GOTO statement, NULL statement

a conditional statement

IF <Boolean expression>  ThenPL/SQL and SQL statements;END IF;

IF <Boolean expression>  ThenPL/SQL and SQL statements;ELSEother statements;END IF; IF <Boolean expression>  ThenPL/SQL and SQL statements; elsif<Other Boolean expressions>  Thenother statements; elsif<Other Boolean expressions>  Thenother statements;ELSEother statements;END IF;

Tips : elsif can't write ELSEIF .

Example 1:

DECLAREv_empno Emp.empno%TYPE; V_salary Emp.sal%TYPE; V_commentVARCHAR2( *);
BEGINSELECTSal intoV_salary fromEmpWHEREEmpno=V_empno;
IFV_salary< the  Thenv_comment:=' fairly less '; elsif v_salary< the  Thenv_comment:=' A little more ';ELSEv_comment:=' Lots ofsalary ';END IF;D Bms_output. Put_Line (v_comment);END;

Second, case expression

 Case selector  when  Then RESULT1  when  Then result2  when  Then Resultn [  ELSE resultn+1] END

DECLAREV_gradeChar(1) ; V_appraisalVARCHAR2( -);BEGINv_appraisal:= CaseV_grade whenA Then' excellent ' whenB Then' Very good ' whenC Then' Good 'ELSE' No such grade 'END;D Bms_output. Put_Line (' Grade: '||V_grade||' Appraisal: '||v_appraisal);END;

three cycles

1. Simple cycle

Loop to execute the statement; EXIT  when < conditional statement >/* condition satisfied, exit Loop statement */END Loop;

Example 3.

DECLAREint  Number(2) :=0;BEGINLOOPint:= int + 1;D Bms_output. Put_Line ('the current value of int is:'||int);
EXIT  when int =Ten;ENDLOOPEND

2. while Loops ( compared with 1, 2 recommended)

 while < boolean expression >  Loop to execute the statement; END LOOP;

Example 4.

 declare  x  number : =  1  ;  begin  while  x<=  10  LOOP 
 Dbms_output. Put_Line ( " x The current value is:   " | |  x); X:  =  x+  1  ;  end  LOOP; 
 
 end  ;  

3. Digital circulation

 for inch []  lower limit: Upper loop to execute the statement; END LOOP;

For Each loop, the loop variable is automatically added 1, and the loop variable is automatically minus 1 using the keyword REVERSE. The numbers following the in REVERSE must be in order from small to large, and must be integers, not variables or expressions . You can use exit to exit the loop.

Example 5.

BEGIN  for int inch 1.. ten loops dbms_output. Put_Line ("| | int ); END LOOP;

End

Example 6.

CREATE TABLETemp_table (Num_col Number);DECLAREV_counter Number:= Ten;BEGININSERT  intoTemp_table (Num_col)VALUES(V_counter); forV_counterinch  -.. -LOOPINSERT  intoTemp_table (Num_col)VALUES(V_counter);ENDLOOP;INSERT  intoTemp_table (Num_col)VALUES(V_counter); forV_counterinch REVERSE  -.. -LOOPINSERT  intoTemp_table (Num_col)VALUES(v_counter);ENDLOOP;END;

Third, Marking and GOTO

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

GOTO label;

<<label>>/* Designator is an identifier that is enclosed in << >> */

Example 7:

DECLAREV_counter Number:= 1;BEGINLoopdbms_output. Put_Line ('the current value of the V_counter is:'||v_counter); V_counter:=V_counter+ 1;IFV_counter> Ten  Then GOTOL_endofloop;END IF;ENDLOOP;<<L_endofloop>>Dbms_output. Put_Line ('the current value of the V_counter is:'||v_counter);END;

Four, NULL Statement

In a PL/SQL program, you can use a null statement to describe the meaning of "do not do Anything", which is equivalent to a placeholder that makes some statements meaningful and improves the readability of the program. Such as:

DECLARE . . . BEGIN ... .. IF  is NULL  Then GOTO print1; END IF ;. .. <<print1>>NULL;  -- no data needs to be processed.  END;

PL/SQL Process Control statements for Oracle databases