How to use Oracle database stored procedures and functions in PHP

How to use stored procedures and functions of Oracle database in PHP

Introduction:
Oracle is a commonly used relational database management system. In development, we often need to use stored procedures and functions. To provide more efficient and flexible data processing capabilities. This article will introduce how to use Oracle database stored procedures and functions in PHP, and provide some practical code examples.

1. Create a stored procedure
In Oracle, a stored procedure is a set of precompiled SQL statements that can receive parameters and return results. Below is a simple example that shows how to create a stored procedure in an Oracle database that calculates the sum of two numbers.

1. First, open the SQL command line tool in the Oracle database or use a visual tool (such as PL/SQL Developer) to connect to the database.
2. Then, enter the following SQL statement to create the stored procedure:

CREATE OR REPLACE PROCEDURE calculate_sum(
    num1 IN NUMBER,
    num2 IN NUMBER,
    sum OUT NUMBER
)
AS
BEGIN
    sum := num1 + num2;
END;
/

In the above example, calculate_sum is the name of the stored procedure, num1 and num2 are input parameters, and sum is an output parameter.

3. Next, save and compile the stored procedure.

2. Use stored procedures
In PHP, we can connect to the Oracle database through the OCI8 extension and call stored procedures.

1. First, make sure the OCI8 extension is installed and enabled.
2. In PHP code, use the following code example to connect to the Oracle database:

<?php
    $conn = oci_connect('username', 'password', 'connection_string');
    if (!$conn) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }
?>

In the above code, replace username with the username of the database , replace password with the database password and connection_string with the database connection string.

3. Next, the stored procedure can be called using the following code example:

<?php
    $sql = "BEGIN calculate_sum(:num1, :num2, :sum); END;";
    $stmt = oci_parse($conn, $sql);

    $num1 = 10;
    $num2 = 20;
    oci_bind_by_name($stmt, ':num1', $num1);
    oci_bind_by_name($stmt, ':num2', $num2);
    oci_bind_by_name($stmt, ':sum', $sum, 20);

    oci_execute($stmt);

    echo "Sum: " . $sum;

    oci_free_statement($stmt);
    oci_close($conn);
?>

In the above code, calculate_sum is the name of the stored procedure, :num1, :num2 and :sum are parameter names. You can use the oci_bind_by_name function to bind PHP variables to the parameters of a stored procedure in order to pass parameters and receive results.

Please note that the oci_parse function is used to parse the SQL statement and returns a statement handle, and the oci_execute function is used to execute the SQL statement.

3. Create functions
In addition to stored procedures, Oracle also supports creating functions. A function is a set of precompiled SQL statements that can receive parameters and return a single result. Below is a simple example that shows how to create a function in Oracle database that calculates the difference between two numbers.

1. Open the SQL command line tool in the Oracle database or use a visual tool (such as PL/SQL Developer) to connect to the database.
2. Enter the following SQL statement to create the function:

CREATE OR REPLACE FUNCTION calculate_difference(
    num1 IN NUMBER,
    num2 IN NUMBER
)
RETURN NUMBER
AS
    diff NUMBER;
BEGIN
    diff := num1 - num2;
    RETURN diff;
END;
/

In the above example, calculate_difference is the name of the function, num1 and num2 is the input parameter.

3. Save and compile the function.

4. Using functions
The method of using Oracle functions is similar to using stored procedures. The following is a sample code for calling the above function in PHP:

<?php
    $conn = oci_connect('username', 'password', 'connection_string');
    if (!$conn) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }

    $sql = "SELECT calculate_difference(:num1, :num2) AS difference FROM DUAL";
    $stmt = oci_parse($conn, $sql);

    $num1 = 50;
    $num2 = 30;
    oci_bind_by_name($stmt, ':num1', $num1);
    oci_bind_by_name($stmt, ':num2', $num2);

    oci_execute($stmt);

    $result = oci_fetch_assoc($stmt);
    echo "Difference: " . $result['DIFFERENCE'];

    oci_free_statement($stmt);
    oci_close($conn);
?>

In the above code, :num1 and :num2 are the parameter names of the function, DUAL is a virtual table in Oracle, used to return single row and single column results.

Conclusion:
By using stored procedures and functions, we can provide more efficient and flexible data processing capabilities. When using the stored procedures and functions of the Oracle database in PHP, you can connect to the database through the OCI8 extension and call the stored procedures and functions. This article provides relevant code examples, hoping to help readers better understand and apply.

The above is the detailed content of How to use Oracle database stored procedures and functions in PHP. For more information, please follow other related articles on the PHP Chinese website!