Java µ÷Óà Oracle ´æ´¢¹ý³Ì·µ»Ø½á¹û¼¯
Oracle ´æ´¢¹ý³Ì·µ»Ø½á¹û¼¯Óà ref cursor ʵÏÖ¡£
ÊÔÑé²½ÖèÈçÏ£º
1. ½¨Á¢ ref cursor ÀàÐͺ͹ý³Ì
CREATE OR REPLACE PACKAGE types
AS
TYPE ref_cursor IS REF CURSOR;
END;
/
CREATE TABLE STOCK_PRICES(
RIC VARCHAR(6) PRIMARY KEY,
PRICE NUMBER(7,2),
UPDATED DATE );
/
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;
2. Óà sqlplus ²âÊÔ¹ý³Ì
SQL> var results refcursor
SQL> exec :results := sp_get_stocks(20.0)
SQL> print results
3. ´Ó Java µ÷ÓÃ
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
public class JDBCDemo {
/**
* Compile-time flag for deciding which query to use
*/
private boolean useOracleQuery = true;
/**
* Class name of Oracle JDBC driver
*/
private String driver = "oracle.jdbc.driver.OracleDriver";
/**
* Initial url fragment
*/
private String url = "jdbc:oracle:thin:@";
/**
* Standard Oracle listener port
*/
private String port = "1521";
/**
* Oracle style of calling a stored procedure
*/
private String oracleQuery = "begin ? := sp_get_stocks(?); end;";
/**
* JDBC style of calling a stored procedure
*/
private String genericQuery = "{ call ? := sp_get_stocks(?) }";
/
Ïà¹ØÎĵµ£º
JAVAÊÇÒ»ÖÖÆ½Ì¨£¬Ò²ÊÇÒ»ÖÖ³ÌÐòÉè¼ÆÓïÑÔ£¬ÈçºÎѧºÃ³ÌÐòÉè¼Æ²»½ö½öÊÊÓÃÓÚJAVA£¬¶ÔC++µÈÆäËû³ÌÐòÉè¼ÆÓïÑÔÒ²Ò»Ñù¹ÜÓá£Óбà³Ì¸ßÊÖÈÏΪ£¬JAVAÒ²ºÃCÒ²ºÃûʲô·Ö±ð£¬ÄÃÀ´¾ÍÓá£ÎªÊ²Ã´ËûÃÇÄÜ´ïµ½Èç´Ë¾³½ç£¿ÎÒÏëÊÇÒòΪ±à³ÌÓïÑÔÖ®¼äÓй²Í¨Ö®´¦£¬Áì»áÁ˱à³ÌµÄ¾«Ë裬×ÔÈ»Äܹ»×öµ½Ò»Í¨°Ùͨ¡£ÈçºÎѧϰ³ÌÐòÉè¼ÆÀíËùµ±È»Ò²ÓÐÐí¶à¹²Í¨µÄµØ·½ ......
´ËÎÄÖеĴúÂëÖ÷ÒªÁгöÁ¬½ÓÊý¾Ý¿âµÄ¹Ø¼ü´úÂ룬ÆäËû·ÃÎÊÊý¾Ý¿â´úÂëÊ¡ÂÔ
¡¡¡¡1¡¢Oracle8/8i/9iÊý¾Ý¿â£¨thinģʽ£©
¡¡¡¡¡¡¡¡Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
¡¡¡¡¡¡¡¡String url="jdbc:oracle:thin:@localhost:1521:orcl";
¡¡¡¡¡¡¡¡//orclΪÊý¾Ý¿âµÄSID
¡¡¡¡¡¡¡¡String user="test";
¡ ......
¶à̬µÄÄ¿µÄ
ͨ¹ýÀàÐÍת»»£¬°ÑÒ»¸ö¶ÔÏóµ±×÷ËüµÄ»ùÀà¶ÔÏó¶Ô´ý¡£
´ÓÏàͬµÄ»ùÀàÅÉÉú³öÀ´µÄ¶à¸öÅÉÉúÀà¿É±»µ±×÷ͬһ¸öÀàÐͶԴý£¬¿É¶ÔÕâЩ²»Í¬µÄÀàÐͽøÐÐͬÑùµÄ´¦Àí¡£
ÕâЩ²»Í¬ÅÉÉúÀàµÄ¶ÔÏóÏìӦͬһ¸ö·½·¨Ê±µÄÐÐΪÊÇÓÐËù²î±ðµÄ£¬ÕâÕýÊÇÕâЩÏàËÆµÄÀàÖ®¼ä±Ë´ËÇø±ðµÄ²»Í¬Ö®´¦¡£
¶¯Ì¬°ó¶¨
½«Ò»¸ö·½·¨µ÷ÓúÍÒ»¸ö·½·¨Ö÷ÌåÁ¬½Óµ½Ò»Æð ......
ÔÚwindowsÏÂsqlplusÍêÈ«Õý³££¬¿ÉÊǵ½linuxÏ£¬sqlplusÖÐÎÄÏÔʾ¾Í³öÎÊÌâÁË£¬×ÜÊÇÏÔʾ“??”£¬Õâ¸öÎÊÌâÓÖÔõô½â¾öÄØ£¿
¾¹ýÔÚÍøÂçÉϲé×ÊÁÏ£¬ÒÔ¼°³¢ÊÔ£¬µÃµ½µÄ½â·¨ÈçÏ£º
ÆäʵºÜ¼òµ¥ÎÒÃÇÖ»ÒªÉèÖÃÍ˳ösqlplus,ÉèÖÃÏàÓ¦µÄ»·¾³±äÁ¿NLS_LANG
export NLS_LANG="SIMPLI ......