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(?) }";
/**
Ïà¹ØÎĵµ£º
1)ÔÚdelphiÖÐ,¾³£ÓÐÈËдSQL²¢´«Èëµ½Êý¾Ý¿âÖÐÖ´ÐÐ,Ò»°ã¶¼ÊǰÑsqlÆ´³öÀ´,ÈçÏÂ:
sSql := 'select * from ContainerStateH where container like '+QuotedStr('%'+trim(Edit2.Text)+'%') +' order by BeginDay';
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add(sSql);
ADOQuery1.Open;
µ«ÊÇ´ÓoracleÊý¾Ý¿â½Ç¶È¿¼Â ......
ÔÚ¶ÔÊý¾Ý¿â½øÐвÙ×÷¹ý³ÌÖÐÎÒÃÇ¿ÉÄÜ»áÓöµ½ÕâÖÖÇé¿ö£¬±íÖеÄÊý¾Ý¿ÉÄÜÖØ¸´³öÏÖ£¬Ê¹ÎÒÃǶÔÊý¾Ý¿âµÄ²Ù×÷¹ý³ÌÖдøÀ´ºÜ¶àµÄ²»±ã£¬ÄÇôÔõôɾ³ýÕâÐ©ÖØ¸´Ã»ÓÐÓõÄÊý¾ÝÄØ?
¡¡¡¡Öظ´Êý¾Ýɾ³ý¼¼Êõ¿ÉÒÔÌṩ¸ü´óµÄ±¸·ÝÈÝÁ¿£¬ÊµÏÖ¸ü³¤Ê±¼äµÄÊý¾Ý±£Áô£¬»¹ÄÜʵÏÖ±¸·ÝÊý¾ÝµÄ³ÖÐøÑéÖ¤£¬Ìá¸ßÊý¾Ý»Ö¸´·þÎñˮƽ£¬·½±ãʵÏÖÊý¾ÝÈÝÔֵȡ£ ÖØ¸´µÄÊý¾ ......
Òª´´½¨Á½¸öÎļþ
1: runBatch.bat
2: sql.txt
runBatch.bat ÄÚÈÝÈçÏ£º
sqlplus username/password @sql.txt
pause
sql.txtÄÚÈÝÈçÏ£º
spool sql.log
create table t1(cname char(20));
insert into t1(cname) values('test');
select * from t1;
spool off
exit
Ë«»÷runBatch.bat¾Í¿ÉÒÔÅúÁ¿Ö´ÐÐsql.txtÖÐ ......
ΪÕâЩÓû§ÃûÉèÖÃ×㹻ǿ׳µÄ¿ÚÁî»òËø¶¨ÎÞÓõÄĬÈÏÓû§¡£
SQLÓï¾äÐ޸ģº
Á¬½ÓÖÁOracleºóʹÓÃÈçÏÂÃüÁîÐ޸ĿÚÁî:
alter user system£¨Óû§Ãû£© identified by manager£¨ÃÜÂ룩;
Á¬½ÓÖÁOracleºóʹÓÃÈçÏÂÃüÁîËø¶¨Õ˺Å:
alter user test£¨Óû§Ãû£© account lock;
ͼÐδ°¿ÚÐ޸ģº
Oracle·þÎñopenviewÉϵÄÓû§£ºSCOT ......
if %time:~,2% leq 9 (set hh=0%time:~1,1%) else (set hh=%time:~,2%)
exp wzolerp/wzolerp@wzol file=E:\Êý¾Ý¿â±¸·Ý\wzolerp%date:~0,10%ÈÕ%hh%ʱ%time:~3,2%·Ö.dmp log=E:\Êý¾Ý¿â±¸·Ý\wzolerp%date:~0,10%ÈÕ%hh%ʱ%time:~3,2%·Ö.log
exp wzolwms/wzolwms@wzol file=E:\Êý¾Ý¿â±¸·Ý\wzolwms%date:~0,10%ÈÕ ......