Getting Started with Oracle (7)--Stored procedures

"This article describes"

Once you are familiar with PL/SQL syntax, implementing Java calls to Oracle stored procedures is the primary purpose. This article describes how to write stored procedures, and how Java calls stored procedures.

"Introduction to Stored Procedures"

Aside from the professional description, the stored procedure is to write some functions in the database, we call these functions in the code (such as Java) to implement the operation of the database, the database to avoid the parsing of SQL statements, for the need to send multiple SQL statements to complete the database operation function, the speed has risen a notch. However, the maintenance of the program in manipulating the database is reduced because the stored procedure is written in the database, not written in the program.

"How to write a stored procedure with the ability to pass a value and have a return value (not a return list)"

A non-return list of stored procedures is easier, directly following the function name with parameters, and give the type of the parameter.

Here's an example:

Stored Procedure Code:

CREATE OR REPLACE procedure inch VARCHAR2 VARCHAR2 asBEGINSelect "user into from"user  where "user". " Name "=para1; End;

Java code :

 Packagecom.zjm.www.test;Importjava.sql.CallableStatement;Importjava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.sql.Statement;Importjava.sql.Types;Importjava.util.Date;ImportOrg.junit.After;ImportOrg.junit.Before;Importorg.junit.Test; Public classTest {Private StaticString driverclass= "Oracle.jdbc.driver.OracleDriver"; //Local    Private StaticString url= "Jdbc:oracle:thin: @localhost: 1521:orcl"; Private StaticString username= "Test"; Private StaticString password= "Aaa38324836"; Private StaticString sql= "";Private StaticConnection conn =NULL; Private StaticStatement stmt =NULL; Private StaticResultSet rs =NULL; Private Staticcallablestatement proc =NULL;; @Before Public voidbefore () {Try{class.forname (driverclass). newinstance ();//Load DriverConn=drivermanager.getconnection (Url,username,password);//Get Connectedstmt=conn.createstatement (); } Catch(Exception e) {e.printstacktrace (); }} @After Public voidAfter () {Try {            if(Conn! =NULL) {conn.close (); }        } Catch(Exception e) {e.printstacktrace (); }        Try {            if(stmt! =NULL) {stmt.close (); }        } Catch(Exception e) {e.printstacktrace (); }        Try {            if(rs! =NULL) {rs.close (); }        } Catch(Exception e) {e.printstacktrace (); }             }         /*** Test Call stored procedure time *@throwsSQLException*/@Test Public voidGetdatebymethod () {intPre = (int) System.currenttimemillis (); Try {
　　　　　　　//Call a stored procedure, the number of question marks represents the number of parameter stored procedures, and the order to correspond proc= Conn.preparecall ("{Call Serachusermethod (?,?)}");
　　　　　　　//value proc.setstring (1, "Aaafb7e4b4d14475ad994310ef62eba7");
　　　　　　　//Register return value proc.registeroutparameter (2, Types.varchar);
　　　　　　　//Submit Proc.execute ();
　　　　　　  //Remove return value System.out.println (Proc.getstring (2)); } Catch(SQLException e) {e.printstacktrace (); } finally {

Proc.close ();

}                  int post= (int) system.currenttimemillis ();                 System.out.println ("Test Call stored procedure elapsed time" + (post-pre));}     }

Note that the value in proc.getstring (2) here is not arbitrary, but corresponds to the parameter column in the stored procedure, if out is in the first position, that is proc.getstring (1), if it is a third position, is proc.getstring (3), of course, you can also have multiple return values, that is, add a few out parameters.

"Returning stored procedures for a list"

From the above example, return the normal int, stirng type parameter is still no problem, but this can never meet the demand, we generally want to return all the properties of a table in the database? Of course.

First step: Build the package (cursor).

A stored procedure cannot directly return an object (the object has n properties), but it has another way of creating a "pointer" cursor that points to some data and finally returning the "cursor" so we can follow the cursor to get the data we want.

Create or Replace  as is cursor;          End

Step two: Create a stored procedure.

Note: The type of the return parameter is the type of "cursor" we just established

Create or Replace procedure TESTC (p_cursor out testpackage. Test_cursor)   is the   begin    openforselect*   from t_ap_za_lyt_gnlk;    End  TESTC;  

Step Three: Java code:

 Packagecom.zjm.www.test;Importjava.sql.CallableStatement;Importjava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.sql.Statement;Importjava.sql.Types;Importjava.util.Date;ImportOrg.junit.After;ImportOrg.junit.Before;Importorg.junit.Test; Public classTest {Private StaticString driverclass= "Oracle.jdbc.driver.OracleDriver"; //Local    Private StaticString url= "Jdbc:oracle:thin: @localhost: 1521:orcl"; Private StaticString username= "Test"; Private StaticString password= "Aaa38324836"; Private StaticString sql= "";//remember to enclose the table name in "" .    Private StaticConnection conn =NULL; Private StaticStatement stmt =NULL; Private StaticResultSet rs =NULL; Private Staticcallablestatement proc =NULL;; @Before Public voidbefore () {Try{class.forname (driverclass). newinstance ();//Load DriverConn=drivermanager.getconnection (Url,username,password);//Get Connectedstmt=conn.createstatement (); } Catch(Exception e) {e.printstacktrace (); }} @After Public voidAfter () {Try {            if(Conn! =NULL) {conn.close (); }        } Catch(Exception e) {e.printstacktrace (); }        Try {            if(stmt! =NULL) {stmt.close (); }        } Catch(Exception e) {e.printstacktrace (); }        Try {            if(rs! =NULL) {rs.close (); }        } Catch(Exception e) {e.printstacktrace (); }} @Test Public voidgetDateByMethod2 () {intPre = (int) System.currenttimemillis (); Try{proc= Conn.preparecall ("{Call TESTC (?)}"));
　　　　　　　//Register the return value parameter, note the cursor type proc.registeroutparameter (1, Oracle.jdbc.OracleTypes.CURSOR); Proc.execute ();
　　　　　　　//The returned data is stored in a resultset ResultSet RS= (ResultSet) proc.getobject (1);  while(Rs.next ()) {
　　　　　　　　　　　　　　//getString (...) The field name of the corresponding database table that is filled in System.out.println ("<tr><td>" + rs.getstring ("id") + "</td><td>" +rs.getstring ("name") + "</td></tr> ");        } proc.close (); } Catch(SQLException e) {e.printstacktrace (); }                 intPost= (int) System.currenttimemillis (); System.out.println ("Test call stored Procedure elapsed" + (post-pre)); }     }