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

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.