Java and Oracle stored procedures (i)

In a large database system, there are two important functions, that is, stored procedures and triggers. In a database system, both stored procedures and triggers are accomplished through a collection of SQL statements and control process statements. In contrast, a trigger in a database system is also a stored procedure. Stored procedures automatically generate a variety of execution methods when they are operating in a database, thus greatly increasing the speed at which they are executed. In large database systems such as Oracle, SQL Server not only provides the functionality of user-defined stored procedures, but also provides many systems that can be invoked as tools with their own stored procedures.

A stored procedure (Stored Procedure) is a set of SQL statements that is used to complete a specific database function, which is compiled and stored in the database system. When used, the user completes one or a series of database operations by specifying the stored procedure name that has been defined and giving the appropriate stored procedure parameters to invoke and execute it.

Because the Java EE system generally establishes large-scale enterprise application system, and generally is equipped with large database system such as Oracle or SQL Server, in this paper, in this article, the java-based and Oracle stored procedures will introduce the mutual application between Java and Oracle stored procedures and the various calls between them.

Java invoke Oracle Stored procedures

Java calls Oracle's stored procedures, the most common among them, is the following brief description of how Java invokes Oracle stored procedures.

Ⅰ, without output parameters

Procedure name is Pro1, parameter number 1, data type is shaping data

import　java.sql. * ;
public　 class　ProcedureNoArgs　
　{
public　 static　 void　main(String args[])　throws　Exception
{
　 // 加载Oracle驱动　
　 DriverManager.registerDriver( new　oracle.jdbc.driver.OracleDriver());
　 // 获得Oracle数据库连接　
　 Connection conn = DriverManager.getConnection( " jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd " );
　　 // 创建存储过程的对象　
　　 CallableStatement c = conn.divpareCall( " {call pro1(?)} " );
　　 // 给Oracle存储过程的参数设置值 ，将第一个参数的值设置成188　
　　 c.setInt( 1 , 188 );
　　 // 执行Oracle存储过程　
　　 c.execute();
　　conn.close();
}　
}　

Ⅱ, with output parameters of the case

The procedure name is Pro2, the number of parameters is 2, the data type is shaping data, the return value is the shaping type

import java.sql.*;
public class ProcedureWithArgs　
　{
public static void main(String args[]) throws Exception
{
　 //加载Oracle驱动
　 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
　 //获得Oracle数据库连接
　 Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd ");
　　//创建Oracle存储过程的对象，调用存储过程
　　CallableStatement c=conn.divpareCall("{call pro2(?,?)}");
　　//给Oracle存储过程的参数设置值 ，将第一个参数的值设置成188
　　c.setInt(1,188);
　　//注册存储过程的第二个参数　
　　　c.registerOutParameter(2,java.sql.Types.INTEGER);
　　//执行Oracle存储过程
　　c.execute();
　　　//得到存储过程的输出参数值并打印出来
　　　System.out.println (c.getInt(2));　
　　conn.close();
}
}

This is the simplest instance of invoking an Oracle stored procedure in Java, followed by other aspects of Java and Oracle stored procedures.