Control Structure of PLSQL

In any computer language (C, Java, Pascal), there are various control statements (conditional statements, loop structures, sequential control structures ...) this control structure also exists in PL/SQL. These control statements also exist in PLSQL.
1. Condition branch statement
PL/SQL provides three conditional branch statements: If-then, if-then-Else, if-then-elsif-then
(1). Simple Condition judgment if-then
Question: When writing a process, you can enter an employee name. If the employee's salary is lower than 2000, the employee's salary will be increased by 10%.
Create or replace procedure sp_pro6 (spname varchar2) is <br/> -- Definition <br/> v_sal EMP. sal % type; <br/> begin <br/> -- run <br/> select Sal into v_sal from EMP where ename = spname; <br/> -- Judge <br/> If v_sal <2000 then <br/> Update EMP set sal = Sal + Sal * 10% where ename = spname; <br/> end if; <br/> end; <br/>

(2). If-then-Else
Question: When writing a process, you can enter an employee name. If the employee's subsidy is not 0, it will increase by 100 based on the original one; if the subsidy is 0, it will set the subsidy to 200;
Create or replace procedure sp_pro6 (spname varchar2) is <br/> -- Definition <br/> v_comm EMP. comm % type; <br/> begin <br/> -- run <br/> select comm into v_comm from EMP where ename = spname; <br/> -- Judge <br/> If v_comm <> 0 then <br/> Update EMP set comm = comm + 100 Where ename = spname; <br/> else <br/> Update EMP set comm = comm + 200 where ename = spname; <br/> end if; <br/> end; <br/>
(3). Multi-condition branch if-then-elsif-then
Question: When writing a process, you can enter an employee number. If the employee's position is president, the employee's salary will be increased by 1000, if the employee is a manager, his salary will be increased by 500, and the salary of other employees will be increased by 200.
Create or replace procedure sp_pro6 (spno number) is <br/> -- Definition <br/> v_job EMP. job % type; <br/> begin <br/> -- run <br/> select job into v_job from EMP where empno = spno; <br/> If v_job = 'President 'Then <br/> Update EMP set sal = Sal + 1000 where empno = spno; <br/> elsif v_job = 'manager' then <br/> Update EMP set sal = Sal + 500 where empno = spno; <br/> else <br/> Update EMP set sal = Sal + 200 where empno = spno; <br/> end if; <br/> end; <br/>

2. Loop statements
(1). Loop statement-loop statement
It is the simplest loop statement in PL/SQL. This loop statement starts with loop and ends with end loop. This loop will be executed at least once.
Case: there is a table named users, which has two fields: ID and name.
Write a process, enter the user name, and add 10 users to the users table cyclically. The user number starts from 1.

Create or replace procedure sp_pro6 (spname varchar2) is <br/> -- Definition: = indicates a value <br/> v_num number: = 1; <br/> begin <br/> loop <br/> insert into users values (v_num, spname ); <br/> -- determine whether to exit the loop <br/> exit when v_num = 10; <br/> -- auto-increment <br/> v_num: = v_num + 1; <br/> end loop; <br/> end; <br/>

(2). Loop statement-while loop
The basic loop must be executed at least once. For a while loop, the loop body statement is executed only when the condition is true... start with loop and end with end loop.
Question: Please write a process. You can enter the user name and add 10 users to the users table cyclically. The user number starts from 11.
Create or replace procedure sp_pro6 (spname varchar2) is <br/> -- Definition: = indicates a value <br/> v_num number: = 11; <br/> begin <br/> while v_num <= 20 loop <br/> -- run <br/> insert into users values (v_num, spname ); <br/> v_num: = v_num + 1; <br/> end loop; <br/> end; <br/>

(3). Loop statement-For Loop
The basic structure of the For Loop is as follows:
Begin <br/> for I in reverse 1 .. 10 loop <br/> insert into users values (I, 'shunping'); <br/> end loop; <br/> end; <br/>
We can see that the control variable I is constantly increasing in the implicit.
3. Sequential Control statement-Goto, null
(1). GOTO statement
The GOTO statement is used to jump to a specific symbol to execute the statement. Note that the use of the GOTO statement increases the complexity of the program and makes the application become less readable. Therefore, we recommend that you do not use the GOTO statement during general application development.
The basic syntax is as follows: goto lable, where lable is the defined label name.
Declare <br/> I int: = 1; <br/> begin <br/> loop <br/> dbms_output.put_line ('output I = '| I ); <br/> If I = 1 {}then <br/> goto end_loop; <br/> end if; <br/> I: = I + 1; <br/> end loop; <br/> <end_loop> <br/> dbms_output.put_line ('loop termination'); <br/> end; <br/>

(2). null
The null statement does not execute any operation and directly passes the control to the next statement. The main benefit of using a null statement is that it can improve the readability of PL/SQL.
Declare <br/> v_sal EMP. sal % type; <br/> v_ename EMP. ename % type; <br/> begin <br/> select ename, Sal into v_ename, v_sal from EMP where empno = & No; <br/> If v_sal <3000 then <br/> Update EMP set comm = Sal * 0.1 where ename = v_ename; <br/> else <br/> NULL; <br/> end if; <br/> end; <br/>