Summary of calling the Oracle stored procedure in Java

1. What is a stored procedure. A stored procedure is a section of the database server.ProgramIt has two types. A SELECT query is used to retrieve data. The retrieved data can be returned to the customer in the form of a dataset. The other is similar to an insert or delete query. It does not return data, but only executes an action. Some servers allow the same stored procedure to return both data and perform actions.
2. When to use stored procedures
If the server defines a stored procedure, you should decide whether to use the stored procedure as needed. Stored procedures are usually tasks that are frequently executed. These tasks are usually performed for a large number of records. Executing stored procedures on the server can improve the performance of applications. This is because:
. Servers often have powerful computing power and speed.
. Avoid downloading a large amount of data to the client, and reduce the amount of transmission on the network.
For example, assume that an application needs to compute a piece of data, which involves many records. If the stored procedure is not used, download the data to the client, resulting in a sharp increase in network traffic.
In addition, the client may be an old computer, and its operation speed is very slow. After switching to a stored procedure, the server will quickly calculate the data and transmit only one data to the client, which is very efficient.
3. stored procedure parameters
To execute the stored procedure on the server, some parameters are often required. These parameters are divided into four types:
The first type is the input parameter, which is passed by the customer program to the stored procedure.
The second type is the output parameter. The stored procedure returns the result to the client program.
The third type is the input/output parameter, which can be used to pass a value to a stored procedure or to return a result to the client program.
The fourth type is the state parameter. The stored procedure returns an error message to the client program.
Note that not all servers support the preceding four types of parameters. For example, Interbase does not support status parameters.
4. Basic Oracle Stored Procedure syntax

1. Basic Structure

Create OrReplaceprocedureIn Number, Parameter 2In Number)IsVariable 1Integer:=0; Variable 2 date;BeginEndStored Procedure name
 

2. Select into statement
Save the result of the SELECT query to a variable. Multiple columns can be stored in multiple variables at the same time. One
Record; otherwise, an exception is thrown (if no record exists, no_data_found is thrown)
Example:

BeginSelectCol1, col2IntoVariable 1, variable 2FromTypestructWhereXxx; ExceptionWhenNo_data_foundThenXxxx;End;
 

1. Stored Procedure without return values
The stored procedure is:

Create Or Replace ProcedureTesta (para1In Varchar2, Para2In Varchar2)AsBeginInsert IntoHyq. B _id (I _id, I _name) S (para1, para2 );EndTesta;
 

Then, when calling in Java, use the followingCode:

 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 ProcedureTestb (para1In Varchar2, Para2 outVarchar2)AsBeginSelect IntoPara2FromTesttbWhereI _id=Para1;EndTestb;
 

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 ReplacePackage testpackageAsType test_cursorIsRefCursor;EndTestpackage;
 

2. Create a stored procedure:

Create Or Replace ProcedureTestc (p_cursor out testpackage. test_cursor)IsBeginOpenP_cursorFor Select * FromHyq. testtb;EndTestc;
 

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

Note that the Oracle driver package must be placed in the class path before execution. Otherwise, an error is reported.