Shulou(Shulou.com)06/03 Report--

This article is about how JDBC stored procedures get result sets in Oracle. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

1. About Oracle and Result Sets

In most cases, we don't need to return one or more result sets from JDBC stored procedures in Oracle unless we have to. If you've ever used MS SQL Server or Sybase SQL Server, returning a dynamic result set from a stored procedure is a very easy thing to do, simply write it at the end of the stored procedure

"select column1,column2,.... from table_list where condition"

That's it.

This is not possible in Oracle. We have to use Oracle Cursor. In Oracle PL/SQL Cursor is used to return one or more rows of records, with Cursor we can get all records from the result set.

Cursor is not difficult, but to return the result set from Oracle JDBC stored procedures, you need to use Cursor variable,Cursor variable Oracle PL/SQL type is REF CURSOR, we can use Cursor variable as long as we define REF CURSOR type. For example, we can define it this way:

TYPE ref_cursor IS REF CURSOR;

Now that you know Cursor and Cursor variables, here's how to use Cursor variables to return a result set to JDBC.

2. definition table structure

In the following example, we will use a table Hotline.

Create table hotline(country varchar2(50),pno varchar2(50));

3. Defining stored procedures

create or replace package PKG_HOTLINE istype HotlineCursorType is REF CURSOR;

function getHotline return HotlineCursorType;

end;

create or replace package body PKG_HOTLINE isfunction getHotline return HotlineCursorType

ishotlineCursor HotlineCursorType;

beginopen hotlineCursor for select * from hotline;

return hotlineCursor;

end;

end;

In this stored procedure, we define the HotlineCursorType type, and simply find all records in the stored procedure and return HotlineCursorType.

4. Testing JDBC Stored Procedures

Log in to the database in Oracle SQL/Plus. Press the following input to see the returned result set.

SQL> var rs refcursor;SQL> exec :rs := PKG_HOTLINE.getHotline;SQL> print rs;

5. Java call

Simply write a Java Class.

….public void openCursor(){Connection conn = null;ResultSet rs = null; CallableStatement stmt = null; String sql = "{? = call PKG_HOTLINE.getHotline()}"; try{conn = getConnection();stmt = conn.prepareCall(sql); stmt.registerOutParameter(1,OracleTypes.CURSOR); stmt.execute(); rs = ((OracleCallableStatement)stmt).getCursor(1); while(rs.next()){String country = rs.getString(1); String pno = rs.getString(2); System.out.println("country:" country "|pno:" pno); }}catch(Exception ex){ex.printStackTrace(); }finally{closeConnection(conn,rs,stmt); }}…..

This completes the JDBC stored procedure invocation of the Oracle result set.

Thank you for reading! About "JDBC stored procedure in Oracle how to get the result set" This article is shared here, I hope the above content can be of some help to everyone, so that we can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.