Let’s talk about Oracle output parameter stored procedure

In Oracle database, a stored procedure is a precompiled code block that can perform defined operations by calling the stored procedure when needed. In a stored procedure, in addition to executing SQL statements, you can also define parameters for input, output, or both. This article mainly introduces the output parameters and their applications in stored procedures.

1. The concept of output parameters

In a stored procedure, an output parameter is a variable used to return results from the stored procedure, usually including basic data types and custom data types. When the stored procedure is executed, the return results will be saved in the output parameters. The program can access the output parameters through the connection object and use the returned data for subsequent operations.

2. Create a stored procedure with output parameters

In the Oracle database, creating a stored procedure with output parameters requires the use of the CREATE PROCEDURE command. The following is an example:

CREATE OR REPLACE PROCEDURE p_test_out_param(
   p_in_param NUMBER,
   p_out_param OUT VARCHAR2
) AS
BEGIN
   p_out_param := 'Hello World';
   dbms_output.put_line('IN Parameter value = ' || p_in_param);
   dbms_output.put_line('OUT Parameter value = ' || p_out_param);
END;

In the above example, p_in_param is the input parameter, p_out_param is the output parameter, and the type is VARCHAR2. This stored procedure does not execute any SQL statements, it just outputs a string for p_out_param. At the end of the stored procedure, the value of p_out_param can be obtained and processed by the externally called program.

3. External programs access the output parameters of the stored procedure

In the Oracle database, external programs can use the connection object to access the output parameters of the stored procedure. The following is an example of a Java program:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class CallStoredProcedure {
   public static void main(String args[]) {
      Connection conn = null;
      CallableStatement cstmt = null;
      try {
         Class.forName("oracle.jdbc.driver.OracleDriver");
         conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

         cstmt = conn.prepareCall("{call p_test_out_param(?, ?)}");
         cstmt.setInt(1, 100);
         cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
         cstmt.executeUpdate();

         String output = cstmt.getString(2);
         System.out.println("Output Parameter : " + output);

      } catch (SQLException e) {
         e.printStackTrace();
      } catch (ClassNotFoundException e) {
         e.printStackTrace();
      } finally {
         try {
            if (cstmt != null)
               cstmt.close();
            if (conn != null)
               conn.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
   }
}

This program is written in Java language, connects to the Oracle database through the JDBC driver, and executes the stored procedure p_test_out_param. Tell the program that the type of the output parameter is VARCHAR through the registerOutParameter method, and obtain the value of the output parameter through the getString method after execution.

Summary:

In Oracle database, stored procedures are an important means of processing business logic. Using output parameters can facilitate the program to obtain the results returned by the stored procedures. Through the following three parts, it can be seen that Oracle database supports almost all mainstream programming language connection APIs, so programmers can choose the programming language they are good at to perform database operations. At the same time, Oracle database provides powerful stored procedure functions, which can improve the maintainability and performance of business logic in complex business environments.

The above is the detailed content of Let’s talk about Oracle output parameter stored procedure. For more information, please follow other related articles on the PHP Chinese website!