Detailed examples of Oracle stored procedures and if nesting

Oracle stored procedure is a precompiled program stored in the database. It can be called by multiple users and can be shared between multiple applications. In Oracle stored procedures, the if statement is a frequently used conditional control statement. It can perform some operations when a condition is true and perform other operations when the condition is false. The if statement can also be nested. This There are many ways to write nested if statements, and you can choose different ways according to the actual situation.

The if-else statement is the most common nested if statement. Its basic syntax is as follows:

if (condition1)
    statement1;
else if (condition2)
    statement2;
else if (condition3)
    statement3;
else
    statement4;

Among them, condition1, condition2, and condition3 are if conditions, and statement1, statement2, statement3, and statement4 are respectively Represents the actions that need to be performed when different conditions are met.

For example, we can implement a stored procedure through the following code, which is used to find the salary of an employee:

CREATE OR REPLACE PROCEDURE get_salary (
    p_empno      IN NUMBER,
    p_salary OUT NUMBER
)
AS
BEGIN
    IF p_empno < 1000 THEN
        p_salary := 1000;
    ELSE
        IF p_empno < 2000 THEN
            p_salary := 2000;
        ELSE
            p_salary := 3000;
        END IF;
    END IF;
END;

In the above stored procedure, when p_empno is less than 1000, p_salary is assigned a value is 1000; when p_empno is between 1000 and 2000, p_salary is assigned a value of 2000; when p_empno is greater than or equal to 2000, p_salary is assigned a value of 3000.

In addition to nested if-else statements, we can also use nested if statements to implement more complex programming logic. For example, the following example demonstrates how to use an if statement to process the status of a food order:

CREATE OR REPLACE PROCEDURE update_order_status (
    p_order_no IN NUMBER,
    p_status   IN VARCHAR2
)
AS
    v_order_status VARCHAR2(10);
BEGIN
    SELECT order_status INTO v_order_status
    FROM orders
    WHERE order_no = p_order_no;

    IF v_order_status = 'NEW' THEN
        IF p_status = 'APPROVED' THEN
            UPDATE orders SET order_status = 'APPROVED' WHERE order_no = p_order_no;
        ELSE
            UPDATE orders SET order_status = 'REJECTED' WHERE order_no = p_order_no;
        END IF;
    ELSIF v_order_status = 'APPROVED' THEN
        IF p_status = 'SHIPPED' THEN
            UPDATE orders SET order_status = 'SHIPPED' WHERE order_no = p_order_no;
        ELSE
            UPDATE orders SET order_status = 'CANCELED' WHERE order_no = p_order_no;
        END IF;
    ELSE
        RAISE_APPLICATION_ERROR(-20001,'The order is already shipped, cannot be updated.');
    END IF;
END;

In the above stored procedure, we use nested if statements to determine the update based on the current order status and the status that needs to be updated. operate. When the order status is 'NEW', if you need to update the order status to 'APPROVED', execute UPDATE orders SET order_status = 'APPROVED' WHERE order_no = p_order_no; otherwise, update the order status to 'REJECTED'; when the order status is When 'APPROVED', if you need to update the order status to 'SHIPPED', execute UPDATE orders SET order_status = 'SHIPPED' WHERE order_no = p_order_no; otherwise update the order status to 'CANCELED'; finally, when the order status is 'SHIPPED' , no further updates to the order status are allowed, otherwise an application error will be thrown.

In short, the if statement in Oracle stored procedures is very flexible and can be nested and used according to the actual situation to implement various complex programming logic. When using if statements, you should clearly define conditions and operations, and try to avoid too deep nesting to prevent the code from being too complex and difficult to understand.

The above is the detailed content of Detailed examples of Oracle stored procedures and if nesting. For more information, please follow other related articles on the PHP Chinese website!