25. Oracle PL/SQL Advanced-control structure (branch, loop, control)

First, the advanced level of PL/SQL-control structure
In any computer language (c,java,pascal) there are various control statements (conditional statements, loop structures, sequential control structures ...), and there are also such control structures in PL/SQL.
After the completion of this part of the study, I hope you reach:
1. Use various IF statements
2. Using Loop statements
3. Use control statements--goto and Null (Goto statement is not recommended);

Second, Conditional branch statement
Three conditional branch statements If-then,if–then–else,if–then–else If–then are available in PL/SQL.
Here we can make a comparison with the Java statement.

1), simple conditions to judge If–then
Issue: Write a procedure that allows you to enter an employee's name and, if the employee's salary is less than 2000, increase the employee's salary by 10%.

SET ServeroutputOn;
CREATEORREPLACEPROCEDURE Sp_pro6 (spnameVARCHAR2)Is
 --Defined
V_sal EMP. SAL%TYPE;
BEGIN
 --Perform
 SELECT SALInto V_salFrom EMPWHERE ename= Spname;
 --Judge
 IF V_sal<2000Then
UPDATE EMP SET sal = sal + sal * 0.1 WHERE ename = spname;
COMMIT;
END IF;
END;
/

-- Call the stored procedure
exec Sp_pro6 ('ALLEN');             

2), double condition branch If–then–else
Problem: Write a process that can enter an employee name, if the employee's subsidy is not 0 on the original basis to increase 100, if the subsidy is 0, set the subsidy to 200;

CREATEORREPLACEPROCEDURE Sp_pro6 (spnameVARCHAR2)Is
 --Defined
V_comm Emp.comm%TYPE;
BEGIN
 --Perform
 SELECT COMMInto V_commFrom EMPWHERE ename= Spname;
 --Judge
 IF V_comm<>0Then
 UPDATE EMPSET COMM= COMM+100WHERE ename= spname; 
 else 
 update EMP set COMM = COMM + 200 where ename = spname; 
 end IF; 
 COMMIT; 
 end; 
 /
 
 -- call the stored procedure Span style= "color: #008080;" > 
 exec Sp_pro6 (  Allen           

3), multiple conditional branching if–then–elsif–then
Issue: Write a procedure that allows you to enter an employee number, and if the employee's position is president, increase his salary by 1000, and if the employee's position is manager, increase his salary by 500, and the employee's salary in other positions increases by 200.

CREATEORREPLACEPROCEDURE Sp_pro6 (spnoNumber)Is
 --Defined
V_job EMP. JOB%TYPE;
BEGIN
 --Perform
 SELECT JOBInto V_jobFrom EMPWHERE EMPNO= Spno;
 IF V_job=‘President‘Then
 UPDATE EMPSET SAL= SAL+1000WHERE EMPNO= Spno;
elsif V_job=‘MANAGER‘Then
 UPDATE EMPSET SAL= SAL+500 where EMPNO = Spno; 
 else 
 update EMP set sal = sal + 200 where EMPNO = spno; 
 end IF; 
 COMMIT; 
 end; 
 /
 -- call stored procedure  
 exec Sp_pro6 (7499);     

Third, circular statement –loop
Is the simplest circular statement in PL/SQL, which begins with loop and ends with an end loop, which is executed at least once.
Case: Existing table users, the table structure is as follows:
User vid | User name uname

TABLE USERS (
Number (5),
VARCHAR2 (30)
);

Write a procedure that allows you to enter a user name and iterate through the addition of 10 users to the users table, with the user number increasing from 1.

CREATEORREPLACEPROCEDURE Sp_pro6 (spnameVARCHAR2)Is
 --Definition: = indicates assignment
V_numNumber:=1;
BEGIN
LOOP
 INSERTInto USERSVALUES (V_num, spname);
 --Determine if you want to exit the loop 
 exit when V_num Span style= "color: #808080;" >= 10; 
 -- auto  
 V_num: Span style= "color: #808080;" >= v_num 1; 
 end LOOP; 
 commit; 
 end; 
 /
 
 -- call the stored procedure  
 exec Sp_pro6 (allen           

IV. Cyclic statement –while cycle
The basic loop executes at least once for the loop body, whereas for a while loop, the loop body statement executes only if the condition is true, and the while loop starts with While...loop and ends with end loop.
Case: Existing table users, the table structure is as follows:
User vid | User name uname
Issue: Write a procedure that allows you to enter a user name and iterate through the addition of 10 users to the users table, with the user number increasing from 11.

CREATEORREPLACEPROCEDURE Sp_pro6 (spnameVARCHAR2)Is
 --Definition: = indicates assignment
V_numNumber:=11;
BEGIN
 While V_num<=20 LOOP 
 -- execute  
 insert into USERS values (V_num, spname); 
 V_num: = v_num + 1; 
 end LOOP; 
 COMMIT; 
 end; 
 /
 
 -- call the stored procedure Span style= "color: #008080;" > 
 exec Sp_pro6 (  Allen           

V. Cyclic statement –for cycle
The basic structure of the basic for loop is as follows

CREATEORREPLACEPROCEDURE Sp_pro6Is--Note If you do not remember to add ()
BEGIN
 for I in reverse 1. 10 LOOP--reverse inversion function, which means I decrement from 10 to 1, Remove reverse indicates that I increment 
 insert into USERS values (I, shunping end LOOP; 
 end; 
 /
 
 -- call the stored procedure  
 exec Sp_pro6;       

We can see the control variable I, which is continuously increasing in the implication.

Six, sequential control statements –goto, NULL
1), goto statement
The goto statement is used to jump to a specific symbol to execute a statement. Note Because the use of GOTO statements increases the complexity of the program and makes the application less readable, it is recommended that you do not use goto statements for general application development.
The basic syntax is goto lable, where lable is a label name that is already defined

Set ServeroutputOn
DECLARE
IInt:=1;
BEGIN
LOOP
Dbms_output. Put_Line (‘ output I= | | I); 
 if I = 1 then 
 goto End_loop; 
 end IF; 
 I: = I + 1; 
 end LOOP; 
 <<end_loop>> 
 Dbms_output. Put_Line ( '  loop over  "); 
 end; 
             

2), NULL statement
The null statement will not take any action and will pass control directly to the next statement. The primary benefit of using a null statement is to improve the readability of PL/SQL.

SET ServeroutputOn;
DECLARE
V_sal EMP. SAL%TYPE;
V_ename EMP. Ename%TYPE;
BEGIN
 SELECT ename, SALInto V_ename, v_salFrom EMPWHERE EMPNO=&NO;
 IF V_sal<3000Then
 UPDATE EMPset COMM = SAL * 0.1 where ename = v_ename; 
 Dbms_output.put_line ( Span style= "color: #ff0000;" > '