| Комментировать | « Пред. запись — К дневнику — След. запись » | Страницы: [1] [Новые] |
ATUM обратиться по имени
Вторник, 27 Ноября 2012 г. 23:04 (ссылка)
try {
CallableStatement cstmt = conn.prepareCall("{? = CALL balance(?)}");
cstmt.registerOutParameter(1, Types.FLOAT);
cstmt.setInt(2, acctNo);
cstmt.executeUpdate();
float acctBal = cstmt.getFloat(1);
}
catch (SQLException e)
{
System.out.println ("это я, ошибка:" + e.getMessage ());
e.printStackTrace (); //по желанию
}
* This source code was highlighted with Source Code Highlighter.We can now write a stored procedure that returns a REF CURSOR, that in JDBC we can process as a result set.
Assume we start from a table defined as below.
CREATE TABLE STOCK_PRICES(
RIC VARCHAR(6) PRIMARY KEY,
PRICE NUMBER(7,2),
UPDATED DATE )
Here we have a table of stock prices, with the RIC (Reuters Instrument Code) as the primary key. We define a PL/SQL function that simply declares a cursor that returns all columns for stocks below a certain price.
CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER)
RETURN types.ref_cursor
AS
stock_cursor types.ref_cursor;
BEGIN
OPEN stock_cursor FOR
SELECT ric,price,updated FROM stock_prices
WHERE price < v_price;
RETURN stock_cursor;
END;
These can all be cre
* This source code was highlighted with Source Code Highlighter.SQL> exec :results := sp_get_stocks(20.0)
PL/SQL procedure successfully completed.
Finally, use the sqlplus print statement to print out the result set
SQL> print results
RIC PRICE UPDATED
------ --------- ---------
AMZN 15.5 21-OCT-01
SUNW 16.25 21-OCT-01
ORCL 14.5 21-OCT-01
String query = "begin ? := sp_get_stocks(?); end;";
CallableStatement stmt = conn.prepareCall(query);
// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.CURSOR);
// set the in param
stmt.setFloat(2, price);
// execute and retrieve the result set
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);
// print the results
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" +
rs.getFloat(2) + "\t" +
rs.getDate(3).toString());
}
* This source code was highlighted with Source Code Highlighter.| Комментировать | « Пред. запись — К дневнику — След. запись » | Страницы: [1] [Новые] |