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 serveroutput on;
CREATE OR REPLACE PROCEDURE sp_pro6 (spname VARCHAR2) is
  --Definition
  V_sal EMP. Sal%type;
BEGIN
  -Execution
  SELECT SAL into V_sal from EMP WHERE ename = spname;
  --Judgment
  IF V_sal < Then
    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;

CREATE OR REPLACE PROCEDURE sp_pro6 (spname VARCHAR2) is
  --Definition
  V_comm Emp.comm%type;
BEGIN
  -Execution
  SELECT COMM to V_comm from EMP WHERE ename = spname;
  --Judgment
  IF V_comm <> 0 Then
    UPDATE EMP SET COMM = COMM + WHERE ename = spname;
  ELSE
    UPDATE EMP SET COMM = COMM + WHERE ename = spname;
  END IF;
  COMMIT;
END;
/

--Call the stored procedure
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.

CREATE OR REPLACE PROCEDURE Sp_pro6 (spno number) is
  --Definition
  V_job EMP. Job%type;
BEGIN
  -Execution
  SELECT JOB into V_job from EMP WHERE EMPNO = spno;
  IF v_job = ' President ' Then
    UPDATE EMP SET sal = sal + EMPNO = spno;
  elsif v_job = ' MANAGER ' Then
    UPDATE EMP SET sal = sal + WHERE EMPNO = spno;
  ELSE
    UPDATE EMP SET sal = sal + EMPNO = spno;
  END IF;
  COMMIT;
END;
/
--Call the 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

CREATE TABLE USERS (
Vid number (5),
Uname 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.

CREATE OR REPLACE PROCEDURE sp_pro6 (spname VARCHAR2) is
  --Definition: = means assignment
  V_num number: = 1;
BEGIN
  LOOP
    INSERT into USERS VALUES (V_num, spname);
    --Determine if you want to exit the loop
    EXIT when v_num = 10;
    --Self-increment
    V_num: = 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.

CREATE OR REPLACE PROCEDURE sp_pro6 (spname VARCHAR2) is
  --Definition: = means assignment
  V_num number: = 11;
BEGIN
  While V_num <= LOOP
    -Execution
    INSERT into USERS VALUES (V_num, spname);
    V_num: = V_num + 1;
  END LOOP;
  COMMIT;
END;
/

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

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

CREATE OR REPLACE PROCEDURE sp_pro6 is--Note If you do not remember to add ()
BEGIN
  For I in REVERSE 1.. The LOOP--reverse inversion function, which indicates that I is descending from 10 to 1, and removing reverse indicates that I increment from 1 to 10
    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 serveroutput on;
DECLARE
  I INT: = 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 (' End of cycle ');
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 serveroutput on;
DECLARE
  V_sal   EMP. Sal%type;
  V_ename EMP. Ename%type;
BEGIN
  SELECT ename, SAL to V_ename, v_sal from EMP WHERE EMPNO = &NO;
  IF V_sal < Then
    UPDATE EMP SET COMM = SAL * 0.1 WHERE ename = v_ename;
    Dbms_output.put_line (' 1111 ');
  ELSE
    NULL;
    Dbms_output.put_line (' 2222 ');--will not be executed
  END IF;
END;
/

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