oracle stored procedure if statement

In Oracle, a stored procedure is a code block that combines multiple SQL statements and encapsulates them in a unit. Stored procedures can help us perform complex data processing operations in the database, thereby improving database performance and efficiency. In stored procedures, the if statement is a common flow control statement that can execute different SQL statements based on conditional judgment. This article will introduce the usage of if statements in Oracle stored procedures.

1. Basic usage of if statement

The if statement in Oracle stored procedure is similar to the if statement in other programming languages. It can execute different SQL based on conditional judgment. statement. The basic syntax of the if statement is as follows:

IF condition THEN
    statement1;
ELSE
    statement2;
END IF;

Among them, condition is a conditional expression. If its value is TRUE, statement1 is executed, otherwise statement2 is executed. It should be noted that the if statement must end with END IF.

The following is a simple example:

CREATE OR REPLACE PROCEDURE check_salary (emp_id IN NUMBER) AS
    salary NUMBER;
BEGIN
    SELECT salary INTO salary FROM employees WHERE employee_id = emp_id;
    IF salary > 5000 THEN
        DBMS_OUTPUT.PUT_LINE('This employee earn more than 5000.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('This employee earn less than or equal to 5000.');
    END IF;
END;

This stored procedure check_salary accepts a parameter emp_id, then queries the employee's salary from the employees table, and outputs different values ​​based on whether the salary is greater than 5000 information. If the salary is greater than 5000, output This employee earns more than 5000., otherwise output This employee earns less than or equal to 5000..

2. if-elsif statement

In Oracle stored procedures, if statements can also be nested to form an if-elsif statement structure to achieve multi-condition judgment. The basic syntax of the if-elsif statement is as follows:

IF condition1 THEN
    statement1;
ELSIF condition2 THEN
    statement2;
ELSIF condition3 THEN
    statement3;
ELSE
    statement4;
END IF;

Among them, condition1, condition2 and condition3 are three conditional expressions. If their values ​​are TRUE in turn, statement1, statement2 and statement3 will be executed, otherwise statement4 will be executed. Multiple elsif clauses can be added as needed.

The following is an example:

CREATE OR REPLACE PROCEDURE check_grade (stu_id IN NUMBER) AS
    grade NUMBER;
BEGIN
    SELECT score INTO grade FROM student WHERE student_id = stu_id;
    IF grade >= 90 THEN
        DBMS_OUTPUT.PUT_LINE('The student got A.');
    ELSIF grade >= 80 THEN
        DBMS_OUTPUT.PUT_LINE('The student got B.');
    ELSIF grade >= 70 THEN
        DBMS_OUTPUT.PUT_LINE('The student got C.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The student failed.');
    END IF;
END;

This stored procedure check_grade accepts a parameter stu_id, then queries the student's score from the student table, determines the grade based on the score, and outputs the corresponding information .

3. If statements are nested and used

In Oracle stored procedures, if statements can also be nested to achieve more complex conditional judgments. The following is an example:

CREATE OR REPLACE PROCEDURE check_employee (emp_id IN NUMBER) AS
    salary NUMBER;
BEGIN
    SELECT salary INTO salary FROM employees WHERE employee_id = emp_id;
    IF salary > 10000 THEN
        DBMS_OUTPUT.PUT_LINE('This employee is a senior manager.');
    ELSE
        IF salary > 5000 THEN
            DBMS_OUTPUT.PUT_LINE('This employee is a manager.');
        ELSE
            DBMS_OUTPUT.PUT_LINE('This employee is a staff.');
        END IF;
    END IF;
END;

This stored procedure check_employee accepts a parameter emp_id, then queries the employee's salary from the employees table, determines his rank based on the salary, and outputs the corresponding information. If the salary is greater than 10,000, it is a senior manager; if the salary is between 5,000 and 10,000, it is a manager; otherwise, it is an ordinary employee.

4. Summary

In Oracle stored procedures, the if statement is a common process control statement, which can execute different SQL statements based on conditional judgment to achieve complex Data processing operations. If statements can also be nested to form an if-elsif statement structure or a multi-layer if statement structure to achieve more flexible conditional judgment. Mastering the usage of if statements will help develop efficient and stable Oracle stored procedures.

The above is the detailed content of oracle stored procedure if statement. For more information, please follow other related articles on the PHP Chinese website!