During this time, I started to learn about the storage process. The main reason is because of work needs. I thought it was very simple. However, after some setbacks, I was exhausted, but I finally succeeded, in order to avoid detours for later users, we would like to describe this and encourage ourselves.
1. Stored Procedure without return values
The stored procedure is:
Create or replace procedure TESTA (para1 in varchar2, para2 in varchar2)
Begin
Insert into hyq. B _id (I _id, I _name) values (para1, para2 );
End Testa;
Then, the following code is used for calling in Java:
Package com. hyq. SRC;Import java. SQL .*;
Import java. SQL. resultset; Public class testprocedureone {
Public testprocedureone (){
}
Public static void main (string [] ARGs ){
String driver = "oracle. JDBC. Driver. oracledriver ";
String strurl = "JDBC: oracle: thin: @ 127.0.0.1: 1521: hyq ";
Statement stmt = NULL;
Resultset rs = NULL;
Connection conn = NULL;
Callablestatement cstmt = NULL; Try {
Class. forname (driver );
Conn = drivermanager. getconnection (strurl, "hyq", "hyq ");
Callablestatement proc = NULL;
Proc = conn. preparecall ("{call hyq. TESTA (?,?) }");
Proc. setstring (1, "100 ");
Proc. setstring (2, "testone ");
Proc.exe cute ();
}
Catch (sqlexception ex2 ){
Ex2.printstacktrace ();
}
Catch (exception ex2 ){
Ex2.printstacktrace ();
}
Finally {
Try {
If (RS! = NULL ){
Rs. Close ();
If (stmt! = NULL ){
Stmt. Close ();
}
If (Conn! = NULL ){
Conn. Close ();
}
}
}
Catch (sqlexception ex1 ){
}
}
}
}
Of course, we need to create a table named testtb, which contains two fields (I _id and I _name ).
2. Stored Procedures with returned values (non-list)
The stored procedure is:
Create or replace procedure testb (para1 in varchar2, para2 out varchar2)
Begin
Select into para2 from testtb where I _id = para1;
End testb;
Use the following code when calling in Java:
Package com. hyq. SRC; Public class testproceduretwo {
Public testproceduretwo (){
}
Public static void main (string [] ARGs ){
String driver = "oracle. JDBC. Driver. oracledriver ";
String strurl = "JDBC: oracle: thin: @ 127.0.0.1: 1521: hyq ";
Statement stmt = NULL;
Resultset rs = NULL;
Connection conn = NULL;
Try {
Class. forname (driver );
Conn = drivermanager. getconnection (strurl, "hyq", "hyq ");
Callablestatement proc = NULL;
Proc = conn. preparecall ("{call hyq. testb (?,?) }");
Proc. setstring (1, "100 ");
Proc. registeroutparameter (2, types. varchar );
Proc.exe cute ();
String testprint = Proc. getstring (2 );
System. Out. println ("= testprint = is =" + testprint );
}
Catch (sqlexception ex2 ){
Ex2.printstacktrace ();
}
Catch (exception ex2 ){
Ex2.printstacktrace ();
}
Finally {
Try {
If (RS! = NULL ){
Rs. Close ();
If (stmt! = NULL ){
Stmt. Close ();
}
If (Conn! = NULL ){
Conn. Close ();
}
}
}
Catch (sqlexception ex1 ){
}
}
}
} }
Note that the proc. the value 2 in getstring (2) is not arbitrary, but corresponds to the out column in the stored procedure. If the out column is in the first position, it is Proc. getstring (1). If it is the third position, it is Proc. getstring (3), of course, you can also have multiple return values at the same time, that is, add a few more out parameters.
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 testpackage
Type test_cursor is ref cursor;
End testpackage;
2. Create a stored procedure:
Create or replace procedure testc (p_cursor out testpackage. test_cursor) is
Begin
Open p_cursor for select * From hyq. testtb;
End testc;
It can be seen that the cursor (which can be understood as a pointer) is returned as an out parameter.
Use the following code when calling in Java:
Package com. hyq. SRC;
Import java. SQL .*;
Import java. Io. outputstream;
Import java. Io. Writer;
Import java. SQL. preparedstatement;
Import java. SQL. resultset;
Import oracle. JDBC. Driver .*; Public class testprocedurethree {
Public testprocedurethree (){
}
Public static void main (string [] ARGs ){
String driver = "oracle. JDBC. Driver. oracledriver ";
String strurl = "JDBC: oracle: thin: @ 127.0.0.1: 1521: hyq ";
Statement stmt = NULL;
Resultset rs = NULL;
Connection conn = NULL; Try {
Class. forname (driver );
Conn = drivermanager. getconnection (strurl, "hyq", "hyq "); Callablestatement proc = NULL;
Proc = conn. preparecall ("{call hyq. testc (?) }");
Proc. registeroutparameter (1, Oracle. JDBC. oracletypes. cursor );
Proc.exe cute ();
Rs = (resultset) Proc. GetObject (1 ); While (Rs. Next ())
{
System. out. println ("<tr> <TD>" + Rs. getstring (1) + "</TD> <TD>" + Rs. getstring (2) + "</TD> </tr> ");
}
}
Catch (sqlexception ex2 ){
Ex2.printstacktrace ();
}
Catch (exception ex2 ){
Ex2.printstacktrace ();
}
Finally {
Try {
If (RS! = NULL ){
Rs. Close ();
If (stmt! = NULL ){
Stmt. Close ();
}
If (Conn! = NULL ){
Conn. Close ();
}
}
}
Catch (sqlexception ex1 ){
}
}
}
} |
|
