Oracle query of all sequences JAVA + Oracle Functions

-- View All sequences of current users

Select SEQUENCE_OWNER, SEQUENCE_NAME from dba_sequences where sequence_owner = 'username ';

Note:

1. You must log on as an administrator;

2. sequence_owner must be in upper case, regardless of whether your user name is in upper case. Only uppercase letters can be recognized.

Take the query of a BOOK table as an example:

Create table BOOK
(
Booknumber char (3) not null,
BOOKNAME VARCHAR2 (50 ),
Bookprice number (18, 2)
)

To return a dataset, you need to use the reference cursor type in Oracle. However, the method used in Oracle is more complicated (I don't understand why Oracle is so complicated ), first, declare a referenced cursor type, which is achieved by creating a package:

Create or replace package PKG_TEST
AS
Type refcursor is ref cursor;
END PKG_TEST;

 
Then create our function stored procedure:
Create or replace function query_book (key varchar) return PKG_TEST.REFCURSOR is
Result PKG_TEST.REFCURSOR;
Begin
Open Result
Select * from book where key = book. booknumber;

Return (Result );
End query_book;

In this stored procedure, we passed in the query key value key and returned a DataSet object.
In this way, we have a stored procedure named query_book, which can return a result dataset. To call this stored procedure, you can call it through the CallableStatement interface. In this case, you need to write a statement similar '{? = Call query_book (?)} ', And the parameters and return values must be set using code. Here, some simple stored procedures can also be called through queries. The following is the call method I used:

Public class FuncTest {
Public static void main (String [] args) throws Exception {
Class. forName ("oracle. jdbc. driver. OracleDriver ");
Connection connection = DriverManager. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: LeeDB ",
"TEST", "test ");
Statement stmt = connection. createStatement ();
ResultSet dataset = stmt.exe cuteQuery ("SELECT query_book ('001') from dual ");
If (dataset. next ()){
ResultSet ret = (ResultSet) dataset. getObject (1 );

While (ret. next ()){
System. out. print (ret. getString (1 ));
System. out. print ("\ t ");
System. out. print (ret. getString (2 ));
System. out. print ("\ t ");
System. out. println (ret. getDouble (3 ));
}
}
}
}

Note that the data returned here is embedded with the dataset returned by function, which requires special processing.