Oracle stored procedure call

Statement createtablestudent (sidint, snamevarchar (20), snoint) in the Oracle table: createorreplaceprocedu

Statement create table student (sid int, sname varchar (20), sno int) in Oracle table 1: create or replace procedu

Oracle table statements

Create table student (sid int, sname varchar (20), sno int)

1. Stored Procedure without return values

Create or replace procedure proc_student (para1 Integer, para2 varchar2, para3 Integer)
Is

BEGIN
Insert into student (sid, snail M, sno) values (para1, para2, para3 );
End;

Public class TestProcedureOne {

Public static final String DRIVER = "oracle. jdbc. driver. OracleDriver ";
Public static final String URL = "jdbc: oracle: thin: @ localhost: 1521: orcl ";
Public static final String USERNAEM = "system ";
Public static final String PASSWORD = "123 ";

Public static void main (String [] args)

{

ResultSet rs = null;
Connection conn = null;
CallableStatement proc = null;
Try {
Class. forName (DRIVER );
Conn = DriverManager. getConnection (URL, USERNAEM, PASSWORD );

Proc = conn. prepareCall ("{call system. testc (?,?,?)} "); // This Is Not The PreparedStatement interface, but the interface that calls the Stored Procedure

Proc. setInt (1, 1 );
Proc. setString (2, "zhangsan ");
Proc. setInt (3, 2); // The parameters here are in the order of SQL statements.
Proc.exe cute ();

} Catch (SQLException ex2 ){
Ex2.printStackTrace ();

} Catch (Exception ex2 ){
Ex2.printStackTrace ();
} Finally {
Try {
If (rs! = Null ){
Rs. close ();
If (proc! = Null ){
Proc. close ();
}
If (conn! = Null ){
Conn. close ();
}
}
} Catch (SQLException ex1 ){
}
}
}
}

2. Stored Procedures with returned values (non-list)

Create or replace procedure proc_student2 (para_1 Integer, para_2 out varchar2, para_3 out

Integer)
Is
Begin
Select snail M, sno into para_2, para_3 from student where sid = para_1;
End proc_student2;

The main java code is as follows:

Proc = conn. prepareCall ("{call system. proc_student2 (?,?,?)} ");

Proc. setInt (1, 1 );
Proc. registerOutParameter (2, Types. VARCHAR); // type of the output parameter
Proc. registerOutParameter (3, Types. INTEGER );
Proc.exe cute ();
System. out. println (proc. getString (2) + proc. getInt (3 ));

Iii. Back to list

Since the oracle stored procedure does not return values, all of its return values are replaced by the out parameter, and the list is no exception. However, because it is a set, general parameters cannot be used, you must use pagkage. therefore, it must be divided into two parts,

1. Create a package. As follows:

Create or replace package package_1

As

Type test_cursor is ref cursor;

End package_1;

2. Create a stored procedure:

Create or replace prcedure proc_student3 (stu_cursor out package_1.test_cursor) is

Begin

Open stu_cursor for select * from system. student

End

It can be seen that the cursor (which can be understood as a pointer) is returned as an out parameter.

The main java code is as follows:

Proc = conn. prepareCall ("{call system. proc_student3 (?)} ");
Proc. registerOutParameter (1, oracle. jdbc. OracleTypes. CURSOR );
Proc.exe cute ();
Rs = (ResultSet) proc. getObject (1 );
While (rs. next ()){
System. out. println (rs. getInt (1) + rs. getString (2) + rs. getInt (3 ));

}