JDBC calls stored procedures and functions

How to invoke a stored procedure in 1.JAVA

CallableStatement: interfaces for executing SQL stored procedures and functions

Simple steps:

A. Write your own stored procedures (of course, you'd better pass the test first)

B. Written Java invocation classes

C. Get the desired output

A: Declaring stored procedures:

CREATE OR REPLACE PROCEDURE pro_query_dept (

P_deptno in DEPT. Deptno%type,

P_dname out DEPT. Dname%type

)

As

BEGIN

SELECT dname into P_dname from DEPT WHERE DEPTNO = P_deptno;

Dbms_output. Put_Line (P_dname);

EXCEPTION

When others THEN

Dbms_output. Put_Line (sqlcode| | SQLERRM);

End;

instance of executing stored procedures under Oracle:

Declare
Name VARCHAR2 (50);
Birthday date;
Begin
Pro_unittest_students (1,name,birthday);
Dbms_output.put_line (name);
Dbms_output.put_line (birthday);
End

B: Use JDBC to invoke:

Class.forName ("Oracle.jdbc.driver.OracleDriver");

String url = "Java:oracle:thin:@127.0.0.1:1521:guzhe";

String user = "Scott";

String password = "Tiger";

Connection con = drivermanager.getconnection (url, user, password); Connecting to a database

String sql = "{call pro_quary_dept (?,?)}"; Executes the SQL statement called with the call clause + stored procedure name + parameter.

CallableStatement CST = con.preparecall (SQL); SQL statement Analysis container for executable SQL stored procedures and functions

Cst.setint (1, 30); Set the value of the first parameter

Cst.registeroutparameter (2, Types.varchar); Declares that the second parameter is out output and describes its return type

Cst.execute (); Perform

String str = NULL;

str = cst.getstring (2);//Get the value of the second parameter, which is the desired output data

System.out.println (str);

2. Use JDBC to invoke functions

The procedure is very similar to the JDBC call stored procedure

To declare a function:

CREATE OR REPLACE FUNCTION func_query_dept (

P_deptno in DEPT. Deptno%type,

P_dname out DEPT. Dname%type

)

Return VARCHAR2

As

BEGIN

SELECT dname into P_dname from DEPT WHERE DEPTNO = P_deptno;

Dbms_output. Put_Line (P_dname);

Return ' OK ';

EXCEPTION

When others THEN

Dbms_output. Put_Line (sqlcode| | SQLERRM);

End;

Oracle reduction Function:

Sql>var Income Number
Sql>call annual.income (' SCOTT ') into:imcome;
Sql>print Income

Use JDBC to invoke this function:

Class.forName ("Oracle.jdbc.driver.OracleDriver");

String url = "Java:oracle:thin:@127.0.0.1:1521:guzhe";

String user = "Scott";

String password = "Tiger";

Connection con = drivermanager.getconnection (url, user, password); Connecting to a database

String sql = "{? = Call Func_quary_dept (?,?)} "; Execute the SQL statement, use the call clause + function name + parameter to invoke

CallableStatement CST = con.preparecall (SQL); SQL statement Analysis container for executable SQL stored procedures and functions

Cst.setint (2, 30); Set the value of the second parameter

Cst.registeroutparameter (1, Types.varchar); Declares that the first parameter is out output and describes its return type (the first question mark, the return value of the function)

Cst.registeroutparameter (3, Types.varchar); Declares that the third parameter is out output and describes its return type (third question mark)

Cst.execute (); Perform

String str = NULL;

str = cst.getstring (2);//Get the value of the second parameter, which is the desired output data

System.out.println (str);

3. Using JDBC to call functions or procedures to get multiple-line query results, you need to use cursors

Two ways:

A. Declare a cursor in the package, then define a stored procedure or function, and use the defined cursor as the output parameter

First you need to define a cursor type so that you can reference this type in the parameters of the function

Crate OR REPLACE PACKAGE pack_dept

Is

TYPE CUR is REF CURSOR;

End;

Define a procedure again with a parameter that is the type of cursor just defined in the package

CREATE OR REPLACE PROCEDURE pro_find_depts (

My_cur out CUR pack_dept. CUR