How to call oracle stored procedure

Oracle stored procedure is a precompiled PL/SQL code block that contains a set of SQL statements to be executed. Stored procedures can be used to process or manipulate data in a database, or they can be used to implement business logic or application functions.

In Oracle database, the calling of stored procedures can be implemented in the following ways.

1. Using SQL command line tools

Using SQL command line tools (such as SQL*Plus), you can call stored procedures through the CALL statement. The syntax format of the CALL statement is:

CALL 存储过程名(参数1, 参数2, ...);

Among them, the stored procedure name refers to the name of the stored procedure to be called, and parameter 1, parameter 2, etc. are the input parameters required by the stored procedure. If the stored procedure has no input parameters, the parameter list can be omitted.

For example, calling a stored procedure named "my_proc" requires two input parameters, and its syntax format is:

CALL my_proc('参数1值', '参数2值');

2. Using PL/SQL block

Stored procedures can also be called using PL/SQL blocks. PL/SQL blocks are a way of writing and executing PL/SQL code, similar to functions or subroutines in other programming languages. Within a PL/SQL block, you can use BEGIN and END blocks to define the code to be executed, as well as the associated input and output parameters.

For example, the following is a simple PL/SQL block for calling a stored procedure named "my_proc":

DECLARE
    参数1 VARCHAR2(50) := '参数1值';
    参数2 VARCHAR2(50) := '参数2值';
BEGIN
    my_proc(参数1, 参数2);
END;

Among them, the DECLARE keyword is used to declare variables, parameters 1 and Parameter 2 is the variable name of the input parameter, := is used to assign an initial value to the variable, and my_proc is the name of the stored procedure to be called.

3. Using Java program

In a Java program, you can use the JDBC API to call Oracle stored procedures. JDBC provides a CallableStatement class for calling stored procedures. You can pass the parameters of the stored procedure to the CallableStatement object and call it together with the stored procedure during execution.

For example, the following is a sample program for calling a stored procedure and printing the results:

import java.sql.*;

public class CallStoredProcedure {
    public static void main(String[] args) throws Exception {
        String driverName = "oracle.jdbc.driver.OracleDriver";
        String connectionString = "jdbc:oracle:thin:@//localhost:1521/orcl";
        String userName = "USERNAME";
        String password = "PASSWORD";
        String storedProcedure = "my_proc";
        String param1 = "参数1值";
        String param2 = "参数2值";
          
        Class.forName(driverName);
        Connection con = DriverManager.getConnection(connectionString, userName, password);
      
        CallableStatement cst = con.prepareCall("{call " + storedProcedure + "(?,?)}");
        cst.setString(1, param1);
        cst.setString(2, param2);
        cst.execute();
      
        ResultSet rs = cst.getResultSet();
        while (rs.next()) {
            // 处理结果集
        }
      
        cst.close();
        con.close();
    }
}

Among them, driverName and connectionString are the driver and connection characters required when JDBC connects to the Oracle database The string, userName and password are the database login name and password. storedProcedure is the name of the stored procedure to be called, and param1 and param2 are the values ​​of the input parameters.

In short, Oracle stored procedures can be called in a variety of ways, including SQL command line tools, PL/SQL blocks, and Java programs. In practical applications, we can choose the appropriate way to call stored procedures based on specific needs and environments to better complete the tasks of data processing and logic implementation.

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