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 ));
}