Java call Oracle Stored Procedure tutorial

Among Java and Oracle, the most common method is to call the Oracle stored procedure. The following briefly describes how Java calls the Oracle stored procedure.

Among Java and Oracle, the most common method is to call the Oracle stored procedure. The following briefly describes how Java calls the Oracle stored procedure.

Among Java and Oracle, the most common method is to call the Oracle stored procedure. The following briefly describes how Java calls the Oracle stored procedure.

I. Java calls the Oracle stored procedure [without output parameters]

The process name is pro1, the number of parameters is 1, and the data type is integer data.

Import java. SQL .*;

Public class ProcedureNoArgs {
Public static void main (String args []) throws Exception {
// Load the Oracle driver
DriverManager. registerDriver (new oracle. jdbc. driver. OracleDriver ());
// Obtain the Oracle database connection
Connection conn = DriverManager. getConnection (
"Jdbc: oracle: thin :@ MyDbComputerNameOrIP: 1521: ORCL", sUsr, sPwd );
// Create a stored procedure object
CallableStatement c = conn. ppareCall ("{call pro1 (?)} ");
// Set the parameter value for the Oracle stored procedure, and set the value of the first parameter to 188
C. setInt (1,188 );
// Execute the Oracle Stored Procedure
C.exe cute ();
Conn. close ();
}
}

Ii. JAVA calls the Oracle stored procedure [with output parameters]
The process name is pro2, the number of parameters is 2, the data type is integer data, the return value is Integer type

Import java. SQL .*;

Public class ProcedureWithArgs {
Public static void main (String args []) throws Exception {
// Load the Oracle driver
DriverManager. registerDriver (new oracle. jdbc. driver. OracleDriver ());
// Obtain the Oracle database connection
Connection conn = DriverManager. getConnection (
"Jdbc: oracle: thin :@ MyDbComputerNameOrIP: 1521: ORCL", sUsr, sPwd );
// Create an Oracle Stored Procedure object and call the Stored Procedure
CallableStatement c = conn. ppareCall ("{call pro2 (?,?)} ");
// Set the parameter value of the Oracle stored procedure to 188.
C. setInt (1,188 );
// Register the second parameter of the stored procedure
C. registerOutParameter (2, java. SQL. Types. INTEGER );
// Execute the Oracle Stored Procedure
C.exe cute ();
// Obtain and print the output parameter value of the stored procedure
System. out. println (c. getInt (2 ));
Conn. close ();
}
}

The above is the simplest example of calling the Oracle stored procedure in JAVA. Next we will introduce other aspects about the JAVA and Oracle stored procedures.

Ii. Create an Oracle stored procedure using JAVA
Let's take a look at how to use JAVA to compile the Oracle stored procedure. We will write statements in Oracle SQL plus.
I. Start SQL PLUS
Ii. Compile a JAVA class and define a JAVA class name. This stored procedure outputs the Square Value of the input parameter.

Create or replace and compile java source named "PF"
/**
* Create an Oracle stored procedure using the JAVA class
*/
Package org. oraclejava. pro;

Public class javaCreatePro {
Public static String test (int num ){
Return num + ":" + num * num;
}
}

Iii. Create the method test in the JAVA class as an Oracle Function Method

Create or replace function PF_FUN (name integer) return varchar2 as language java name 'org. oraclejava. pro. javaCreatePro. test (java. lang. Integer) return java. lang. string ';

Iv. directly call the created Oracle function on the console

Select PF_FUN (10) from dual;

V. output results
10 square meters: 100