Hoplite
ҽ
л
ҵBlog
¹鵵...
·...
...
Ķ...
...
ͳ...
վ...
Դ
===========================================================
ORACLE˾ (ת)
===========================================================


ORACLE˾

ORACLE˾֮Դ

ORACLE˾һδȻҪIBMʼ

19706£IBM˾оԱ¼ӡ (Edgar Frank Codd) Communications of ACM Ϸƪġ͹ݿݵĹϵģ͡A Relational Model of Data for Large Shared Data BanksġݿⷢչʷϵһתۡҪ֪ʱDzģͺ״ģ͵ݿƷгռҪλáƪĿʼ˹ϵݿĻ

Ȼ1970͵˹ϵģۣгϳٳٲϵݿƳҪԭǺܶ෴Ϊϵݿٶ̫ȲϵʱIJʽݿ⡣ֵúЦǣIBMȻ1973System RĿоϵݿʵʿԣҲûмʱƳIJƷΪʱIBMĵIMSIJݿ⣩гƳϵݿ⣬ǣ浽IBMܶ˵档ߣIBMӴӵĹŻھԶô

19776£Larry EllisonBob MinerEd OatesڹȹͬһΪʵңSoftware Development LaboratoriesSDLļ˾ORACLE˾ǰǸʱ32Larry EllisonҴѧûܱҵѧֻһͨʦ˾֮MinerܲãOatesΪܲãEllisonΪһͬ飬һҹ˾ϰࡣûãһλԱBruce ScottùORACLEݿ˶֪иScottûİɣûScottScottûTigerScottè֣˽MinerOatesЩֺͬʽĿǾͨǻ֪ԼܿʲôIJƷOatesȿ˰¼ӡصƪͬƪص²ƼEllisonMinerҲĶһ¡EllisonMinerԤݿľ޴DZIBMߣûǣSDLʼ߻õĹϵݿϵͳRDBMS

ܿǾŪһ̫IJƷ߾˵һDemoEllisonMinerǰһҹ˾µһ鱨ͶʵĿ,ǰƷΪORACLEΪţORACLEֵĽС, Ԥԡ֮⣩һǻ۵ԴȪ1979꣬SDLΪϵ޹˾Relational SoftwareInc.RSIϾʵҡ̫һ˾֡1983꣬Ϊͻ˾ĺIJƷRSIٴθΪORACLE

չ׳

RSI1979ļ˿DEC˾PDP-11ϵORACLEƷݿƷ˱ȽSQLʵ֣аӲѯӼԡò˵ǺȶȱҪܡгԣ˾ǸòƷĵڶ棬ȴʵϵĵһ档֮ԱΪ2ǵ1棬ΪEllisonΪDZڵĿͻԸ⹺2汾dzʼ汾ȻЩ̫ʵҪǸʮָļɡڻһЩ˾Լͻİ汾1.0 ѧѧ1979ORACLEɣԺĽ죬ORACLE˾ǵһṩ˵һSQLϵݿϵͳ

ȻǺܺãǿͻеġ鱨Ȳһǵ󡣵ѯIBM˾֮IBMûпõIJƷϵRSIRSI˵һͻڵʱ;Ļͬʱмּʱûʲôֲ˵ȻҲûоӦҲ˵PDP-11ORACLEݿⲻIBMDECVAXϡܿûͱֳORACLEܷͬʱڲͬIJϵͳУRSIµսҪMinerScott70ĩں80ڵһ㶼ƳڵһϵͳУпֲ١

19833£RSIORACLE档MinerScottCдһ汾Ҫ֪CԵʱƳãдORACLEҲǾһķյģ֮⣬֤ܿǶôȷC˶ЧORACLEƷһؼԣֲԡORACLE3滹ƳSQLġԭԡDDSQLҪôȫɹҪôȫʧܣҪôȫύҪôȫعORACLE3滹˷ѯʹô洢"before image file"еѯͻع񣬴Ӷ˶read lockʹãȻͨʹñͬ1983꣬IBMٵDatabase 2DB2ֻMVSʹáô˵ORACLEѾռȡȻ

ڿ滹ûнʱScott뿪ORACLEʱCдORACLEѹֹܴScottظѡһ֮ʣµصMinerһˡڳԼģ4ĹƱ֮Scott Gupta˾ָΪCentura SoftwarePointBase˾ṩٷ֮ٴJavaǶʽݿ⣩ǿݿصIJƷʵģ4ORACLEƱʱScottORACLEдһдļ֣ҲֻܱһЦˡܼһʼIJƸʱScottûô뷨ֻ̫ˡ

ORACLEȽֲDEC VAXϵVMSϵͳϡ1979깫˾ѾһλDEC˾ļRobot BrandtVAXORACLEĿʼʱʽޣֻܵݴѧУȥпһЩҲǽġأBrandtDZȽϳɹֲVAXСͻĴӦORACLEҲΪVAXܻӭijһҪ鹦Larryг֪Ⱦ˵IBMORACLE˾ݿĴ󴬣ôDEC˾VAXǴ﷫ˡ̶̵ļ֮ORACLEݿⱻֲҪƽ̨֮ϡORACLEƷҲһֱΪпֲؼԶЩDZڵĿͻע

OatesʱΪѶɥѾܰѾȫŵ˾ϣò뿪˾ط˾ΪORACLE޴Ĺףŵԣڹ˾Ա1˵ʱٶ뿪1999꣬Ը֣Ե֡

ܳһʱ˾зMinerеMinerӽǮΪ˵͵Ellisonķâ¶γĶԱȡڹ˾һΪϺˣҲԱEllisonǹ˾ĴԣMiner֮ijΪ˾ࡣǸĬӢۣSteve JobsSteve Wozniakһ

198410£ORACLE˵4ƷƷȶõ˵õһǿMinerĻ˵ﵽˡҵǿȡǻ⣬ûԲƷıԹƺֹһ˶һԣread consistencyݿһؼԣȷûڲѯڼ俴һµݡҲ˵һỰ޸ʱĻỰûỰδύ޸ġԿORACLEİ֮ǰƷʼDzȶģORACLEȺԱҪEllisonORACLEʱҪʣȥңúܺãòϣȷЩ档ǿ1984гΣݿгϵİAsnton-Tale˾ǵȭͷƷǸƳõdBase IIIȷе˵dBasePCϵݿոճΪȫĶ˾һ͵ڶֱ΢LotusORACLEڵʱŲϺţһ꣬Ҳƻ˾MacintoshȣSteven JobsȭͷƷսϴIBMͬһУORACLE˾ĿԱոհѲƷֲPCϡõҲǧƵС˾ݣ¹˾ORACLEβڲ֮أ

1985꣬ORACLE5.0档û˵汾ORACLEݿȶ汾ҲClient/ServerģʽеĵRDBMSƷڼϣORACLEݿʼûζPCͻϵӦóܹͨݿ1986귢5.1滹ֲַ֧ʽѯͨһԲѯʴ洢ڶλõݡ

1985꣬ʱĶ˾Cullinet(Ҫ״ݿ)Ѿǰ䡣ORACLEҪIngresݿ⡣IngresڼݴѧУҪǵʱMichael Stonebrakerڡ˵Ingresݿϸ80õݿ⣬IngresгֶĿѾORACLE˺ܴѹɵǣʱIBM˾һϵ֮֡

IngresʹõStonebraker QUELQuery LanguageIJѯIBMSQLͬijЩطQUELҪSQLIBMʱIngresQUELɱ׼ԼһԼSQLύݿ׼ίԱᡣStonebrakerڿɲQUELύݿ׼ίԱᣬѧԺɵΪʵǶɱ˴¾̵ְORACLEץסѵĻᣬORACLEȫSQLݣORACLEʱIngres PCϵİ汾ĹƣԼֵı죩ټORACLE˾ϵǿƣIngres϶ʧأȵƳ֧SQLݿʱΪʱIBMORACLEԳɳ׳ӵ׼ӵţӵ仯ORACLEڲ֮ء

1986312գORACLE˾ÿ15ԪУ20.75Ԫ̣˾ֵ2.7Ԫ313գ΢ÿ21ԪķмУ28ԪУ˾ֵﵽ7ԪԶԶORACLEɹĹ⻷΢͸ǴڸסORACLEEllisonĹâҲEllison΢Ŀʼ

ܴ

ORACLE61988귢ڹȥİ汾ڸMinerŹʦݿĽµĸдмrow-level lockingҪԣҲ˵ִдֻӰУ汾˻㲻ƵPL/SQLProcedural Language extension to SQLԡ6滹ȱݹܣʹݿܹʹùдıݣ⼫ǿ˿ԡͬʱһ꣬ORACLEʼзERP

˾չȥȽ˳جβŸոտʼ

ڹȥӵij̶ȲDDǸʱ˾ģС϶ǿͻѲԵġڵշ֮󣬺ܶȲʼʹõûԹصǸûвԺþͽзIJƷҲEllisonҪ˵ǰͷֻԳûʼORACLEORACLEһЩ֣Ҳʼ侮ʯORACLEƷһЩйԱһӦŭûһӰӵضԳнϵõһȽȶİ汾ʱƽϢûķŭ

ǣʵʵⲢͬʱҲ˾˾޴1990ȱĹһСԱ1500ԪĻʣҹ˾ԤԶʱ˾֢״ԹɶָأƱһǧɣ˾ǰƲһȿάԼݻҲƱEllisonҲŲסˡ˾´ٲ񣨲ֿܷܽ˴ij̶ֳϽ˹˾˾֧˴ԱͬʱƸרŵĹ˲š

جORACLE߰Ƴ˾Ѿ̸˺ü°汾һȱΪEllisonĹʼʩѣֱ19926²dzһι˾ȡ˵æеĽѵȡûĶ෽Ľ飬°汾˴ϸµIJԡð汾µԣֲʽܡǿĹܡӦó򿪷¹ԼȫԷORACLE7һЩ¹ܣ洢̡̺˵ԵȣʹݿľпɱһҪ˵ǣ汾ԭеĻڹŻRBO֮һµŻڿŻCost-Based Optimizer , CBOCBOݿԶͳִпӶóִмƻԺļش汾УORACLEĹʦ𲽶ŻиĽCBOȡRBO

ORACLE ߰ORACLEɫIJƷ,ȡ˾޴ijɹ汾ijǺʱʱSybase˾ݿѾռ˲ٷݶORACLEһ汾ijɹһ߻ͱ˵Sybase˾Ա㵽˸ûֿͷŵʱ򡣹˾ڰ鷳¿ʼչ۶Ҳ9215ԪΪ42Ԫ

۷

ˮEllisonһ1995еŷϢ̳ϣEllisonڼݽнNetwork ComputerNCĸ,νNCָüȴܳԴĵͼ۵ԣΪҪǣҪϵͳ߸׼ȷ˵Ҫ΢IJϵͳEllisonϣ΢ǿơܿ죬ORACLEIBMSunAppleNetscape1996ƶı׼ʵǴͷβûпһ̨NCεݽҵȻ󲨣ͨ¼ORACLE˾㹻עͬʱҲǿORACLE˾ľ޴ġ

19976£ORACLEڰ˰淢ORACLE8֧ĿµĶýӦã汾ҲΪ֧Internetȵ춨˻ͬʱһ汾ʼͬʱûͺݵԡ汾ҲȦɵˡ

19989£ORACLE˾ʽORACLE 8iiInternetһ汾˴Ϊ֧InternetƵԡһ汾ΪݿûṩȫλJava֧֡ORACLE 8iΪһȫ˱Javaʱݿ⣬JavaͿԱдORACLEĴ洢̡ԣJavaֻҪܹ΢ORACLEҪóORACLE8i SQLJһֿʽ׼ڽSQLݿǶͻJava룩ORACLE interMediaڹýݣԼXMLԡͬʱORACLE 8i ̶ԡչԺͿӦҪļУORACLE½8iļ汾һЩӦõԡԿԴ˶չORACLEȻ1998ʮORACLE˿Linuxƽ̨ORACLE 8 ԼORACLE Application Server 4.0󲻾ãORACLEַORACLE 8i for Linux .comУORACLEվڷ˼Ū

20016µORACLE OpenWorldУORACLEORACLE 9iORACLE 9iУҪľReal Application ClustersRACˡ˵ORACLEȺڵʱORACLEͿʼORACLEзORACLE Parallel Server OPSԺİ汾𽥵书ܣϸ˵OPSǸȺDzûֳȺӦеŵ㡣ȫRdbORACLE1994չCompaqRdbݿ⣬ǰRdbDEC˾DEC˾VAXʵ˵һõRdbȺݿ⣩һЩ֮ORACLEƳӦüȺRACʹöȺܹijһݿķʣԻøߵĿԡԺ;ԡORACLE 9iRACTPC-CĻ׼д¼һʱҵĿµݿ⻹ɵܣBIܡORACLE 9i2滹˺ܶҪĸĽʹORACLEݿΪһصXMLݿ⣻⻹ԶData Guardȸ߿÷ԡ

ʷڼ

200398գɽɽٰORACLE WorldϣEllisonһݿƷΪORACLE 10gORACLEӦ÷10gORACLE Application Server 10gҲΪ׹Ĺ˾һӦûܹ׼ggrid ,񡱡һԾǼĹܡν㣿԰ѷֲصļһ𣬲ҽصļԴͨٵĻɳֹԴɡͨȣͬļ㻷ۺòORACLE10gΪĻìͷֱָĵIBMġӦ䡱ORACLE˾ѾһεġעѺĴгϡǰΣĿԴ

˵IBMITҵеһͷ޾ôORACLEһһ㣺ͱˣڽ20036³ORACLEͻȻ51չʿƣPeopleSoftҵٴ𶯡ξһ¶ORACLE һڽıԡҪ֪ORACLEڷչкٶҵչģôչʿĿĺڣȣORACLEҵӦгѾãڲܽһгֶΪҪǣһɹֱӶĵIBMдSAPȾͷǿƣһΪҪʱգORACLEȻԲĿIJݵ̬ƺʿƲΣĿԴ

"ĿǻܵˡEllisonһϲ


hoplite :2005.04.07 12:19 ::: ( ݿ ) ::Ķ:(396) :: (0) :: (0)
===========================================================
SAPORACLE
===========================================================
˵Ʒеԣһԥ˵ϲSAPΪƷصעһĿοҪORACLEСܶ࣬ijǶȶĿ˵ġ

˵Ʒеԣһԥ˵ϲSAPΪƷصעһĿοҪORACLEСܶ࣬ijǶȶĿ˵ġ

Һοҵ⣬һIT㹻ǿôѡIJƷӦ÷dzӦ÷dzȫ棬ܹҵĹĻERPɫ෴ģһITǿƣôҪӭϸŶERP󣬽вϵĵƷ߱ϵԣôĿյĽֻ·һ

ͬĻںϡҺORACLESALES仰dzͣΪһֱ뽲ȴһֱҲʴĻȻܲҵǣ仰Ӧû̫İ

ERPǿֹ淶̣ҲֹĻҪֲ淶ƶõ̶ֻܴܺǸ˵ǹߡERPĿ⹤ߵӦϤ̶ȺҵڵĵλֱӰ⹤ʹõЧERPǹ淶ģôǷζҵԭе̾Ͳ淶Ȼԭе̲淶Ϊʲôֻö֮Ĵڣڵʲô˭ӦΪĴڶеΣ

ҿORACLESAPIJڴˣ

ORACLEǿϵͳܹϵԡҵDzϱ仯ģͬҵͬһȫ㣭˵Dz㡣ôORACLEϵͳܹϵԱַdzҪƣκιܹܶͨԶ̵ʱ俪㡣

SAPǿϵͳܵơ󲿷ҵ̶ͨ㡣ԺĸûSAPƷĹһҵʹõ20ѾܲˣʹǷdzӣܹӵȥȻҲͨABAPĿijЩض

͵ģ¹˵Ļԡ

ORACLESAPڲϵԼĹܣѴӲƷĽǶȥϵͳӡijЩӵ˼ȥǣ򾲾ȴеĽ ɡ


hoplite :2005.03.16 12:26 ::: ( ݿ ) ::Ķ:(373) :: (0) :: (0)
===========================================================
oracle
===========================================================


SQLеĵ¼
1.ASCII
ַָӦʮ;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32

2.CHR
,ضӦַ;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;

ZH C
-- -
A

3.CONCAT
ַ;
SQL> select concat('010-','88888888')||'ת23' Ǭ绰 from dual;

Ǭ绰
----------------
010-88888888ת23

4.INITCAP
ַַĵһĸΪд;
SQL> select initcap('smith') upp from dual;

UPP
-----
Smith


5.INSTR(C1,C2,I,J)
һַַָ,طַָλ;
C1 ַ
C2 ϣַ
I Ŀʼλ,ĬΪ1
J ֵλ,ĬΪ1
SQL> select instr('oracle traning','ra',1,2) instring from dual;

INSTRING
---------
9


6.LENGTH
ַij;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;

NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
Ǭ 3 к 6 9999.99 7


7.LOWER
ַ,еַСд
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;

AABBCCDD
--------
aabbccdd


8.UPPER
ַ,еַд
SQL> select upper('AaBbCcDd') upper from dual;

UPPER
--------
AABBCCDD


9.RPADLPAD(ճַ)
RPAD еұճַ
LPAD еճַ
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;

LPAD(RPAD('GAO',1
-----------------
*******gao*******
ַ*


10.LTRIMRTRIM
LTRIM ɾֵַ߳
RTRIM ɾұֵַ߳
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;

LTRIM(RTRIM('
-------------
gao qian jing


11.SUBSTR(string,start,count)
ȡַ,startʼ,ȡcount
SQL> select substr('13088888888',3,8) from dual;

SUBSTR('
--------
08888888


12.REPLACE('string','s1','s2')
string ϣ滻ַ
s1 滻ַ
s2 Ҫ滻ַ
SQL> select replace('he love you','he','i') from dual;

REPLACE('H
----------
i love you


13.SOUNDEX
һַַͬ
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');

SQL> select xm from table1 where soundex(xm)=soundex('weather');

XM
--------
weather
wether


14.TRIM('s' from 'string')
LEADING ǰַ
TRAILING ַ
ָ,ĬΪո

15.ABS
ֵָľֵ
SQL> select abs(100),abs(-100) from dual;

ABS(100) ABS(-100)
--------- ---------
100 100


16.ACOS
ҵֵ
SQL> select acos(-1) from dual;

ACOS(-1)
---------
3.1415927


17.ASIN
ҵֵ
SQL> select asin(0.5) from dual;

ASIN(0.5)
---------
.52359878


18.ATAN
һֵķֵ
SQL> select atan(1) from dual;

ATAN(1)
---------
.78539816


19.CEIL
شڻڸֵС
SQL> select ceil(3.1415927) from dual;

CEIL(3.1415927)
---------------
4


20.COS
һֵ
SQL> select cos(-3.1415927) from dual;

COS(-3.1415927)
---------------
-1


21.COSH
һֵַ
SQL> select cosh(20) from dual;

COSH(20)
---------
242582598


22.EXP
һenη
SQL> select exp(2),exp(1) from dual;

EXP(2) EXP(1)
--------- ---------
7.3890561 2.7182818


23.FLOOR
Ըȡ
SQL> select floor(2345.67) from dual;

FLOOR(2345.67)
--------------
2345


24.LN
һֵĶֵ
SQL> select ln(1),ln(2),ln(2.7182818) from dual;

LN(1) LN(2) LN(2.7182818)
--------- --------- -------------
0 .69314718 .99999999


25.LOG(n1,n2)
һn1Ϊn2Ķ
SQL> select log(2,1),log(2,4) from dual;

LOG(2,1) LOG(2,4)
--------- ---------
0 2


26.MOD(n1,n2)
һn1n2
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;

MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2


27.POWER
n1n2η
SQL> select power(2,10),power(3,3) from dual;

POWER(2,10) POWER(3,3)
----------- ----------
1024 27


28.ROUNDTRUNC
ָľȽ
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55


29.SIGN
ȡnķ,01,С0-1,00
SQL> select sign(123),sign(-100),sign(0) from dual;

SIGN(123) SIGN(-100) SIGN(0)
--------- ---------- ---------
1 -1 0


30.SIN
һֵֵ
SQL> select sin(1.57079) from dual;

SIN(1.57079)
------------
1


31.SIGH
˫ҵֵ
SQL> select sin(20),sinh(20) from dual;

SIN(20) SINH(20)
--------- ---------
.91294525 242582598


32.SQRT

SQL> select sqrt(64),sqrt(10) from dual;

SQRT(64) SQRT(10)
--------- ---------
8 3.1622777


33.TAN
ֵֵ
SQL> select tan(20),tan(10) from dual;

TAN(20) TAN(10)
--------- ---------
2.2371609 .64836083


34.TANH
n˫ֵ
SQL> select tanh(20),tan(20) from dual;

TANH(20) TAN(20)
--------- ---------
1 2.2371609


35.TRUNC
ָľȽȡһ
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;

TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16


36.ADD_MONTHS
ӻȥ·
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;

TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;

TO_CHA
------
199910


37.LAST_DAY
ڵһ
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;

TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;

LAST_DAY(S
----------
31-5 -04


38.MONTHS_BETWEEN(date2,date1)
date2-date1·
SQL> select months_between('19-12-1999','19-3-1999') mon_between from dual;

MON_BETWEEN
-----------
9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;

MON_BETW
---------
-60


39.NEW_TIME(date,'this','that')
thisʱ=otherʱںʱ
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;

BJ_TIME LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32


40.NEXT_DAY(date,'day')
datex֮һڵ
SQL> select next_day('18-5-2001','') next_day from dual;

NEXT_DAY
----------
25-5 -01


41.SYSDATE
õϵͳĵǰ
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;

TO_CHAR(SYSDATE,'
-----------------
09-05-2004
trunc(date,fmt)ոҪڽض,fmt='mi'ʾ,ض
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;

HH HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00


42.CHARTOROWID
ַתΪROWID
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;

ROWID ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES


43.CONVERT(c,dset,sset)
Դַ ssetһַתһĿdsetַ
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;

conver
------
strutz


44.HEXTORAW
һʮƹɵַתΪ


45.RAWTOHEXT
һƹɵַתΪʮ


46.ROWIDTOCHAR
ROWIDתΪַ


47.TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41


48.TO_DATE(string,'format')
ַתΪORACLEеһ


49.TO_MULTI_BYTE
ַеĵַֽתΪַֽ
SQL> select to_multi_byte('') from dual;

TO
--


50.TO_NUMBER
ַתΪ
SQL> select to_number('1999') year from dual;

YEAR
---------
1999


51.BFILENAME(dir,file)
ָһⲿļ
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));


52.CONVERT('x','desc','source')
xֶλԴsourceתΪdesc
SQL> select sid,serial#,username,decode(command,
2 0,'none',
3 2,'insert',
4 3,
5 'select',
6 6,'update',
7 7,'delete',
8 8,'drop',
9 'other') cmd from v$session where type!='background';

SID SERIAL# USERNAME CMD
--------- --------- ------------------------------ ------
1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none


53.DUMP(s,fmt,start,length)
DUMPfmtָڲָʽһVARCHAR2͵ֵ
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;

GLOBAL_NAME DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D


54.EMPTY_BLOB()EMPTY_CLOB()
Դֶνгʼĺ


55.GREATEST
һʽеֵ,ȽַıС.
SQL> select greatest('AA','AB','AC') from dual;

GR
--
AC
SQL> select greatest('','','') from dual;

GR
--


56.LEAST
һʽеСֵ
SQL> select least('','','') from dual;

LE
--


57.UID
رʶǰûΨһ
SQL> show user
USER Ϊ"GAO"
SQL> select username,user_id from dba_users where user_id=uid;

USERNAME USER_ID
------------------------------ ---------
GAO 25


58.USER
صǰû
SQL> select user from dual;

USER
------------------------------
GAO


59.USEREVN
صǰûϢ,opt:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 鿴ǰûǷDBA򷵻true
SQL> select userenv('isdba') from dual;

USEREN
------
FALSE
SQL> select userenv('isdba') from dual;

USEREN
------
TRUE
SESSION
ػỰ־
SQL> select userenv('sessionid') from dual;

USERENV('SESSIONID')
--------------------
152
ENTRYID
ػỰ˿ڱ־
SQL> select userenv('entryid') from dual;

USERENV('ENTRYID')
------------------
0
INSTANCE
صǰINSTANCEı־
SQL> select userenv('instance') from dual;

USERENV('INSTANCE')
-------------------
1
LANGUAGE
صǰ
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
صǰԵд
SQL> select userenv('lang') from dual;

USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
ûն˻ı־
SQL> select userenv('terminal') from dual;

USERENV('TERMINA
----------------
GAO
VSIZE(X)
XĴС(ֽ)
SQL> select vsize(user),user from dual;

VSIZE(USER) USER
----------- ------------------------------
6 SYSTEM


60.AVG(DISTINCT|ALL)
allʾеֵƽֵ,distinctֻԲֵͬƽֵ
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
Ѵ
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('zhu',5555.55);
SQLWKS> commit;

SQL> select avg(distinct sal) from gao.table3;

AVG(DISTINCTSAL)
----------------
3333.33

SQL> select avg(all sal) from gao.table3;

AVG(ALLSAL)
-----------
2592.59


61.MAX(DISTINCT|ALL)
ֵ,ALLʾеֵֵ,DISTINCTʾԲֵֵͬ,ֻͬȡһ
SQL> select max(distinct sal) from scott.emp;

MAX(DISTINCTSAL)
----------------
5000


62.MIN(DISTINCT|ALL)
Сֵ,ALLʾеֵСֵ,DISTINCTʾԲֵͬСֵ,ֻͬȡһ
SQL> select min(all sal) from gao.table3;

MIN(ALLSAL)
-----------
1111.11


63.STDDEV(distinct|all)
׼,ALLʾеֵ׼,DISTINCTʾֻԲֵͬ׼
SQL> select stddev(sal) from scott.emp;

STDDEV(SAL)
-----------
1182.5032

SQL> select stddev(distinct sal) from scott.emp;

STDDEV(DISTINCTSAL)
-------------------
1229.951


64.VARIANCE(DISTINCT|ALL)
Э

SQL> select variance(sal) from scott.emp;

VARIANCE(SAL)
-------------
1398313.9


65.GROUP BY
Ҫһͳ
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;

DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400


66.HAVING
Էͳټ
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;

DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;

DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400


67.ORDER BY
ڶԲѯĽ
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;

DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950


hoplite :2005.03.16 12:20 ::: ( ݿ ) ::Ķ:(324) :: (0) :: (0)
===========================================================
sybaseoracleñȽ
===========================================================

sybaseoracleñȽ

url: http://www.itpub.net/228889.html

ʷ

Sybase Consulting Servicesȱṩӿͻ/ʵ
͹㲩֪ʶ

Oracle Services㷺ļ֪ʶʵ飬þķ
͹ߣԽۺϵĿͻ/ǽڿϵͳ
ͻ/ΪʹھҵսȡơOracle
һϵĿͻĸּͨOracleʷֲĹߺͼ
ǿСϵͳʹ˾ͼɹ˾

Oracle Sybase
Ա Oracle ServicesĿOracleȫԱռı
25%6,000λʷֲ45ҡ

ȫֻ1,200ʣSybase޷ڽȫԿͻ/
Oracle Services Ტۡ

Oracle Servicesʲǿͻ/רң
ƶչҵҪҲָͬơ Oracle Services
רڹʡϵͳƺͰװĿȷƽ5
15ķḻ顣

SybaseʽSybaseרңSybaseƽжپ
רú򣬻ö֪

ṩķ Oracle ServicesΪҪҵȽ
͡ӿͻ/ķḻ顣ǿṩض
񣬰ûָл´ﵽѵܣڿ
ϵͳƳģݿ⣻Լøӵݲֿ
ǵĹʿڽɹĿͻ/ĸṩ
úܹ滮ϵͳϵṹ飬
ԭͣԣʵֺܵ

SybaseĹڽͻ/вĸ׶Σ
ݽṹӦóṹṹ֯ṹSybaseһ
ṩµġظĿ̵Ŀܺͷ
ʹSybaseרҵصڿͻ/Ľϣ
С͹ҲṩOracle ServicesȺ͹ȡ

Աѵ Oracle Servicesӵҵϸѵƻ¹ܵ
ѵнĹܺͼѵֻʽѵ

زҵʹǵIJƷÿʻμÿ
ѵγ̸ݹʵרҵţǼġܵģ

ҲĿġ

ٶSybaseSybaseϵõѵѵҪΡ
Ƿṩѵγ̣Ǹδ֪

ƻ Oracle ServicesӵһҵϵͳģEnterprise Systems
Centerƿͻ/ûվ㰲װǰ
IJԡַԻ䱸мרҺ͹רң
ƳиܡԼԵĴ͸ϵͳҲɲԸ
ϵͳ

SYBASEδ֪дּƻ

ɼ International Data Corp.IDCOracle Services
ITͻ/ʻǰС

Sybaseδ ITͻ/ʻǰСSybase
ҵʿƷרңǽĽߡ

ֲʽ

Sybaseֲͬʽǵ첽ƹܡSybase
ֲʽIJѯܺ÷ֲʽݵĹܾ
ֻOracle7֮Sybaseʵǵûʵʻ
ʹЩܣгɹķͼ˵û
ֻӦʹøƹֲܽʽݷʵ󡣵Ȼ
SybaseṩΨһѡ񣬵OracleˡOracleṩ걸
Ҿþֲͬʽܡ

Oracle Sybase
ɻϵṹ ֲʽOracle ServerС Oracle Server
ֱӷOracle ServerеݻͨOracle Gateways
ʷOracle Serverеݡֲʽݷ͸ĺԶġ


ͬSybaseĸƹֲͬʽδɽ
ӹܡֲʽܱñ̷ʽͨⲿԪʵ֡

͸ķֲʽѯ ӦóʹκOracle ͨһỰ
ʱݻԶݡӦóУָݵ
λá޸Ӧó뼴ɴݡӦó
ָݾλõ߼ӳ Oracle7ɡ

Ϊ˷λݣԱݿдӦó
±дԽSybase OmniSQLصӡ
Ҫݴ䵽ԶվԶݴ䵽طӦ
ó±д

Զֲʽ Oracle7ܹԶ͸ִзֲʽ˲
бӦóӦóзǷֲʽʹ
SQL׼COMMITύֲʽ񡣻ָҲԶأٳ

ΪִзֲʽûÿӦóбд룬Խ
ҪݿӺʵִ۸ӵ׶ύЭ顣
Σָܺͷֲʽݵԡ

Զ̵̹ Զ̵̹ñԶԷֹʧЧԶ̹
ǵıزΪͬһ񣬶̡һ
ʧЧбغԶ̲ύ˻ء

SybaseԤ̺ʹԵSybaseϵԤ
̣Զ̵ִ̹вΪһ֡һ
ʧЧᵼύԶ̲˻رز ˻رز
ύԶ̲

ɿXA֧ OracleѾɹָҪл
ƷtpʵXA֧֡

Sybase֧XAʵûӦóƷ
TPʵXA֧Զ

ѵ

Sybase Educational ServicesṩķĹȺϾ
OracleͬնΪûҪṩۺѵı
SybaseĿγϵʽŵĿγ̡Oracle Educationͨ
ṩʶ㷺ѵ95%ϵƽûʡ

Oracle Sybase
Ա Ϊ˾ƾ Oracle Educationӵ400רְԱ
ȫ1,500Ϊûṩѵа
250ȫְḻרҼָԱʹOracleȫ
ΧڸûҪڿΡ

Sybaseȫֻ150ѵԱ

Ͽɳ̶ Oracle Education95%ûʣ൱
ɫ

Sybase EducationĿǰ޹ûʡ

ɿķ 1995꣬ Oracle Education Ϊ70,000
ṩ˸IJƷѵ Oracle Education62
ӵ217ѵģɸûҪṩѵ

SybaseṩOracleṩγ31%ֻ23
ѵվ

Oracleÿṩ7,000ֿγ̣ÿûѵҪ
OracleѧԱ170йơߡݿ⡢
γ̡CASEӦó򡢶û͹γнѡ

Sybaseֻṩ36йݿƷʹõĿγ̡

ۺϵѵ ǵѧϰͬҲʱ
ȥϿΡ Oracle EducationṩĶѵʽѡ񣬿
ǧѵ
Instructor-led Classes170Ĵ¿γ̣ѵ
ĺûֳɽС
Tailored/Role-Based Trainingûƶ
ϰûɫְѵ
Custom Trainingground-upĿγ̣ûӦ

Computer-Based Training Self-pacedγϣûҪʱ
ʱṩ50⡣
Video-Based TrainingѧСѵĹߣƵʽ
ṩضѵ20⡣
Oracle ChannelͨתѧԱʦ֮Ľʽѵ
ڹٸͬĵҲֱûֳṩ
Change Management ServicesͨƼȷʽOCM
˺ͻڲ¼ѵҪ ҵı֡

SybaseṩThe Oracle ChannelĽʽԶ̽ѧ

Oracle Sybase
ѵĵ 38 23
ÿʽŵĿγ >7,000 2,145
ṩĴ¿γ 170 36
ѵŹģ 371 150
пĿγ 100% 90%
ѵ
ûֳѵ
ýѵ
ʽԶ̽ѧ
ûѵ



SybaseزƷ෽治šSybaseΪֲںݿ
ƷϵȱݣһǿǵزƷΡSybaseһ
أȻϱݸӡSQL Server
ݿ⼯ɲܵȻһɲ䡣

OracleмʹOracleݿ⣬ṩⲿԴֱӡ

Oracle Sybase
ϵṹ Oracleϵṹṩⲿݵֱӷʣ
Ӷܣά
磬DB2VSAMOracleݽҪTransparent
Gateway for DB2Transparent Gateway for EDA/SQL

SybaseνġֱӡôҪڵⲿԴǰ
ͨƷ
DB2VSAMSybaseݽҪ߸SybaseƷ
OmniCONNECTDatabase GatewayAccess Server for MVS/CICS
Omni Access Module for DB2Omni Access Module for InfoHub
Infohub

Ǿһֲʽ Oracle Open GatewayҪIJƷֿ֧
һԴֲʽѯӡµȵȣ
SybaseķǾһҪOmniCONNECTDirectCONNECT
Ʒ
ֵ̬ӳ ֵֶ֧̬ӳ䣬 Oracle Open Gateway
ڷʶǾһԴʱҪתĿ͵IJ
Ʒ
Sybase OmniCONNECTҪûԤĿԴ͡
ֲʽʱ SybaseҲ붨塣
һĿԴı䣬OmniCONNECTͱ¶塣

ݿ֧ Oracle Open Gatewayṩ30ֲͬԴĶ/д
ʡ
Sybaseṩ22ֲͬԴĶ/дʡ

רProcedure/RPC Access Ĺҵ׼
Oracle Procedural Gateway for APPCùҵ׼APPCִ
ʹõIJƷ
APCCΪûṩκAPCCɷԴĶ/дʣԴ
λ MVSAS/400VMDOS/VSESunHPRS/6000ϡ

Sybase ҪCICSIMS°װάרóû
Ӧó

Procedural/RPC Access֤Ҫ
ûֻһProcedural Gateway for APPC֤Ϳͨ
TPCICSIMS/TMIDMS/DCAPPC/MVSݡ

û빺άֱ CICSIMS/TM ĶƷ
֧IDMS/DCAPPC/MVS

CICS
Oracle Open GatewayCICSͿṩDB2ǹϵ
ݵSQLʡ
SybaseҪCICSʹSybaseMVSDB2ݿء

ʷǹϵ
ֻҪOracle Transparent Gateway for EDA/SQLͿɷ
ǹϵݡ
OracleĽԽʡװάʱ䣬Сʡ

ʹSybaseƷڷǾһзʷǹϵ͵ݣû
òװάOmniCONNECT ServerDatabase-Gateway
(TM) MDI Access ServerInfohubɵϵͳ


OracleGatewaysϵ֧׶ύSybase
OmniCONNECT޴˹ܡ
SybaseṩԷǹϵݵSQL/дʣṩκ
ԡ

ʹݿ
OracleOpen Gatewayϵʹûܶ³ֵݿ⹦Ѹ
á

SybaseûȴSybaseдأ¹ܣ
Ҫʱ䡣磬 OmniCONNECTSybase System
10.5ϵṹϣδʵְSybase System 11ܵ

Sybaseмϵ谭ĿǰSQL ServerĹܡ

װ͹
OracleOpen GatewayڰװάݿԱ辭
רѵͿһʱɰװװϺ
ؽжάơ

Sybaseġ콢ƷOmniCONNECTװøӣҪ
רָװ󣬻辭붨άɰװû
άȫͬ

֤
֤Oracle7ݿϲй߾ʹOracle
Open Gateway
SybaseҪһij򣬽Ϊ˹ʹ
DirectCONNECTزƷĹߵ֤ζSybase SQL
ServerϲĹ߲ҪʹSybaseء

Web OracleOpen GatewayݿܼɣˣǿԷ
ΪOracle7ġǿWebչרű̡

SybaseؽWebӦãûҪʹWeb.sqlPerl
SQL ƺͱдԶij

ʻ OracleOpen GatewayOracle7ǿĹ֧֣
ûܷأչ͹ҵ

SybaseȻҪӢIJƷṩ޵Ĺ
֧֡ƷڶԵ֧Ϊ


hoplite :2005.01.18 16:02 ::: ( ݿ ) ::Ķ:(686) :: (0) :: (0)
===========================================================
SQL
===========================================================

SQL

˵Ʊ(ֻƽṹ,Դa ±b)

SQL: select * into b from a where 1<>1

˵(,Դa Ŀb)

SQL: insert into b(a, b, c) select d,e,f from b;

˵ʾ¡ύ˺ظʱ

SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

˵Ӳѯ(1a 2b)

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

˵ճ̰ǰ

SQL: select * from ճ̰ where datediff('minute',fʼʱ,getdate())>5

˵ŹɾѾڸûеϢ

SQL:

delete from info where not exists ( select * from infobz where info.infid=infobz.infid )

˵--

SQL:

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE

FROM TABLE1,

(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE

FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,

(SELECT NUM, UPD_DATE, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,

WHERE X.NUM = Y.NUM +

AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B

WHERE A.NUM = B.NUM

˵--

SQL:

select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and ϵ='"&strdepartmentname&"' and רҵ='"&strprofessionname&"' order by Ա,Դ,߿ܳɼ

˵

ݿȥһĸλ绰ͳ(绰Ѷص绯嵥Դ

SQL:

SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC

FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration

FROM TELFEESTAND a, TELFEE b

WHERE a.tel = b.telfax) a

GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')

˵ı⣺

SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

˵õСδʹõID

SQL:

SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID

FROM Handle

WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)

ϸԭ http://expert.csdn.net/Expert/topic...l?temp=.3089716


hoplite :2005.01.10 18:45 ::: ( ݿ ) ::Ķ:(316) :: (0) :: (0)
===========================================================
ORACLEĹ
===========================================================

ORACLEĹ
http://www.chinaunix.net :swwin ڣ2003-07-27 16:50:42

ORACLE¼ģʽ:
0none
1null
2Row-S й(RS)sub share
3Row-X жռ(RX)е޸ģsub exclusive
4Share (S)ֹDMLshare
5S/Row-X жռ(SRX)ֹshare/sub exclusive
6exclusive ռ(X)ʹãexclusive

ԽԽ, ӰIJԽࡣ

1УSelectʱv$locked_object֡
2УSelect for update,Lock For Update,Lock Row Share
select for updateԻʹfor updateӴһαʱзؼежм(Row-X)ռʽֻܲѯЩУܽupdatedeleteselect for update
3УInsert, Update, Delete, Lock Row Exclusive
ûcommit֮ǰͬһ¼ûзӦ, Ϊһ3һֱȴһ3, DZͷŵһܼ
4УCreate Index, Lock Share
locked_modeΪ2,3,4ӰDML(insert,delete,update,select), DDL(alter,drop)ʾora-00054
00054, 00000, "resource busy and acquire with NOWAIT specified"
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5УLock Share Row Exclusive
Լʱupdate / delete ... ; ܻ4,5
6УAlter table, Drop table, Drop Index, Truncate table, Lock Exclusive


hoplite :2005.01.10 12:51 ::: ( ݿ ) ::Ķ:(348) :: (0) :: (0)
===========================================================
oracleдһЩʵ
===========================================================

oracleдһЩʵ

oracleУ4lobsͿãֱblob,clob,bfile,nclob
Ƕlob͵ļ򵥽ܡ
l blob:lobΪݣɴ4GBݿС
l clob:ַlob,ַ,Դﵽ4GB,ݿС
l bfile:ļ;ݿֻ֮Ͷݣ󳤶ɲϵͳơ
l nclobֶַֽ֧(nultibyte characterset)һclobС
μͲЩlobһֱoracleݿ⿪߾⡣ҽoraclelobݴһЩͼɣܸߣϣܹԶԺĿ
oracleпöַlobݡͨĴͨdbms_lob
ķʹapi(application programming interfaces)Ӧóӿںocioracle call interfaceoracleýӿڳ
һoracleǿdbms_lobdbms_lobǿ󣬼ӦáȿȡڲlobҲbfile󡣵֮䣬һ𡣴ڲlobblob,clobʱԽждⲿlobbfileʱֻܽждIJpl/sqlsqlҲԴlobҪעsqlԴlobܲlobƬ
dbms_lobڽread(),append,write()erase()copy(),getlength()substr()ȺԺܷزlobﲻۣ߿Բοص鼮
pl/sqlһּɣö̬pl/sql䴦clob
example 1.
̬PL/SQL,CLOBֶβɴݱtable_nameΨһ־ֶfield_idclobֶfield_name¼v_idʼַλv_posַv_clob
޸CLOBPL/SQḶupdateclob
create or replace procedure updateclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number,
v_clob in varchar2)
is
lobloc clob;
c_clob varchar2(32767);
amt binary_integer;
pos binary_integer;
query_str varchar2(1000);
begin
pos:=v_pos*32766+1;
amt := length(v_clob);
c_clob:=v_clob;
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id for update ';
--initialize buffer with data to be inserted or updated
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
--from pos position, write 32766 varchar2 into lobloc
dbms_lob.write(lobloc, amt, pos, c_clob);
commit;
exception
when others then
rollback;
end;
l /÷˵
ڲ޸ǰȰֶβ޸ģCLOBֶΪempty_clob()
ȻϵĹ̲204832766ַ
Ҫ32767ַһѭɽ⡣
ѯCLOBPL/SQLgetclob
create or replace function getclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number) return varchar2
is
lobloc clob;
buffer varchar2(32767);
amount number := 2000;
offset number := 1;
query_str varchar2(1000);
begin
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id ';
--initialize buffer with data to be found
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
offset:=offset+(v_pos-1)*2000;
--read 2000 varchar2 from the buffer
dbms_lob.read(lobloc,amount,offset,buffer);
return buffer;
exception
when no_data_found then
return buffer;
end;
l ÷˵
select getclob(table_name,field_id,field_name,v_id,v_pos) as partstr from dual;
ԴCLOBֶȡ2000ַpartstrУ
һѭ԰partstrϳdbms_lob.getlength(field_name)ȵĿַ
ͬĿvc,vb,pb,javaȻ¶lobĴͬォҪټ˵oracle¶lobĴ
һ pbеĴ
exampler 2.
string ls_path,ls_filename,ls_jhdh
long ll_num,ll_count,rtn
blob ole_blob
ll_num=dw_lb.getrow()
if ll_num>0 then ls_jhdh=dw_lb.object.ct_njhdh[ll_num]
select count(*) into :ll_count from sj_jh_jhfjb where ct_jhdlxbh='1' and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx;
if ll_count>0 then
rtn=messagebox("ʾ","ǷҪ޸Ĵ˸",question!,yesno!,1)
if rtn=1 then
SELECTBLOB ct_jhfjnr INTO le_blob from sj_jh_jhfjb where ct_jhdlxbh='1' and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx;
ole_1.objectdata =ole_blob
If ole_1.activate(offsite!) <> 0 Then
Messagebox("OLE Activate","ܼ")
Return -1
end If
end if
else
messagebox("ʾ","ûи")
end if
end if
vbеĴ
vbдһOO4O(oracle objects for ole)һֲ0040blobķ
γԽһļıļdocļͼļȣ浽ݿУԽݿ
Ҫcommandbutton
cmd1 cmdsave caption
cmd2 cmdread caption ȡ
һcmddialogؼ
ͬʱҪһűt_demo(ֶid number,;ֶtext blob;)
exmple 3.
Option Explicit
Dim rn As ADODB.Connection
Public Function CreateDataSource(DataSource As String, UserID As String, Password As String) As Boolean
On Error GoTo DbConErr:
Set rn = New ADODB.Connection
With rn
.ConnectionString = "Provider=OraOledb.Oracle.1;" & _
"password=" & Password & ";" & _
"User ID =" & UserID & ";" & _
"Data Source=" & DataSource & ";" & _
"Locale Identifier=2052"
.Open
End With
CreateDataSource = True
Exit Function
DbConErr:
CreateDataSource = False
End Function

Private Sub cmdRead_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = "select * from t_demo"
rs.Open
ComDlgDir.DialogTitle = "ļ"
ComDlgDir.Filter = "*.*"
ComDlgDir.ShowSave
Call BlobToFile(rs.Fields("text"), ComDlgDir.filename)
Set rs = Nothing
Exit Sub
Set rs = Nothing
End Sub

Private Sub cmdsave_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = "select * from t_demo"
rs.Open
rs.AddNew
ComDlgDir.DialogTitle = "ѡȡļ"
ComDlgDir.ShowOpen
rs.Fields("id").Value = 1
If ComDlgDir.filename <> "" Then
Call FileToBlob(rs.Fields("text"), ComDlgDir.filename)
rs.Update
End If
Set rs = Nothing
Exit Sub
Set rs = Nothing
End Sub

Private Sub Form_Load()
If Not CreateDataSource("sid", "systemp", "manager") Then
MsgBox "Connection failure!"
End If
End Sub

fld As ADODB.Field, filename As String, Optional ChunkSize As Long = 8192)
Dim fnum As Integer, bytesleft As Long, bytes As Long
Dim tmp() As Byte
If (fld.Attributes And adFldLong) = 0 Then
Err.Raise 1001, , "field doesn't support the GetChunk method."
End If
If Dir$(filename) = "" Then Err.Raise 53, , "File not found"
fnum = FreeFile
Open filename For Binary As fnum
bytesleft = LOF(fnum)
Do While bytesleft
bytes = bytesleft
If bytes > ChunkSize Then bytes = ChunkSize
ReDim tmp(1 To bytes) As Byte
Get fnum, , tmp
fld.AppendChunk tmp
bytesleft = bytesleft - bytes
Loop
Close #fnum
End Sub

Sub BlobToFile(fld As ADODB.Field, filename As String, Optional ChunkSize As Long = 8192)
Dim fnum As Integer, bytesleft As Long, bytes As Long
Dim tmp() As Byte
If (fld.Attributes And adFldLong) = 0 Then
Err.Raise 1001, , "field doesn't support the GetChunk method."
End If
If Dir$(filename) <> "" Then Kill filename
fnum = FreeFile
Open filename For Binary As fnum
bytesleft = fld.ActualSize
Do While bytesleft
bytes = bytesleft
If bytes > ChunkSize Then bytes = ChunkSize
tmp = fld.GetChunk(bytes)
Put #fnum, , tmp
bytesleft = bytesleft - bytes
Loop
Close #fnum
End Sub

()jdbclob
exmple 4.
Getting BLOB and CLOB Locators from a Result Set
// Select LOB locator into standard result set.
ResultSet rs =stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (rs.next())
{// Get LOB locators into Java wrapper classes.
oracle.jdbc2.Blob blob = (oracle.jdbc2.Blob)rs.getObject(1);
oracle.jdbc2.Clob clob = (oracle.jdbc2.Clob)rs.getObject(2);
[...process...]
}
ȻRead BLOB data from BLOB locator.
InputStream byte_stream = my_blob.getBinaryStream();
byte [] byte_array = new byte [10];
int bytes_read = byte_stream.read(byte_array);
Writing BLOB Data
java.io.OutputStream outstream;
// read data into a byte array
byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
// write the array of binary data to a BLOB
outstream = ((BLOB)my_blob).getBinaryOutputStream();
outstream.write(data);
Passing a BLOB Locator to a Prepared Statement
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
"INSERT INTO blob_table VALUES(?)");
ops.setBLOB(1, my_blob);
ops.execute();
Ӧע⣺
insertʱһҪempty_blob()ʼ
stmt.execute ("insert into my_blob_table values ('row1', empty_blob()");

ģpro*cеĴ
PRO*CַʽLOBֶδ
1The DBMS_LOB package inside PL/SQL blocks.
2OCI (Oracle Call Interface) function calls.
3Embedded SQL statements.
Embedded SQL statements.ķʽ򵥶ұȽOTNṩһӣ
In this example we will be reading data from a BLOB with an unknown arbitrary length into a buffer and then writing the data from the buffer into an external file.
Our buffer is small, so depending on the size of the BLOB we are reading, we may
be able to read the BLOB value into the buffer in a single READ statement or we
may be required to utilize a standard polling method instead.
First we start off with oci.h and some simple local variable declarations
example 5.
#include <oci.h>
OCIBlobLocator *blob ;
FILE *fp ;
unsigned int amt, offset = 1 ;
Now we need a buffer to store the BLOB value and then write to the file from:
#define MAXBUFLEN 5000
unsigned char buffer[MAXBUFLEN] ;
EXEC SQL VAR buffer IS RAW(MAXBUFLEN) ;
Allocate the BLOB host variable and select a BLOB which we will READ:
EXEC SQL ALLOCATE :blob ;
EXEC SQL SELECT a_blob INTO :blob FROM lob_table WHERE ... ;
We can then open the external file to which we will write the BLOB value:
fp = fopen((const char *)"image.gif", (const char *)"w") ;
If the buffer can hold the entire LOB value in a single READ we need to catch the
NOT FOUND condition to signal LOB READ termination:
EXEC SQL WHENEVER NOT FOUND GOTO end_of_lob ;
Now do our first READ.We set the amount to the maximum value of 4 Gigabytes. It
is larger than our buffer so if the LOB doesn't fit we will READ using a polling
mode:
amt = 4294967295 ;
EXEC SQL LOB READ :amt FROM :blob AT ffset INTO :buffer ;
If we get here then it means that the buffer was not large enough to hold the entire
LOB value, so we must write what we have using binary I/O and continue reading:
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;
We use a standard polling method to continue reading with the LOB READ inside
of an infinite loop. We can set up the NOT FOUND condition to terminate the loop:
EXEC SQL WHENEVER NOT FOUND DO break ;
while (TRUE)
{
During polling, the offset is not used so we can omit it in subsequent LOB READs.
We need the amount, however, because it will tell us how much was READ in the
last READ invocation
EXEC SQL LOB READ :amt FROM :blob INTO :buffer ;
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;
}
Here, we have reached the end of the LOB value. The amount holds the amount of
the last piece that was READ. During polling, the amount for each interim piece
was set to MAXBUFLEN, or the maximum size of our buffer:
end_of_lob:
(void) fwrite((void *)buffer, (size_t)amt, (size_t)1, fp) ;


壩 delphiеĴ
lobֶζԣΪʹñlongкܴԣlobֶοԱݣԱͼƬ֣־clobblobͼ˵blobͼƬϢclob֡
exmple 6.
Create table test_table
(c_no number(1) not null,
c_blob blob,
c_clob clob,
constraint pk_test_table primary key (c_no));

unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DBCtrls, Grids, DBGrids, DB, DBTables, ExtDlgs;

type
TForm1 = class(TForm)
Database1: TDatabase; //ݿ
Table1: TTable; //ȡϢ
DataSource1: TDataSource;
DBGrid1: TDBGrid;
DBMemo1: TDBMemo; //ʾc_clobֶ
DBImage1: TDBImage; //ʾc_blobֶ
Button1: TButton; //밴ť
Button2: TButton; //水ť
Table1C_NO: TFloatField; //Tfiled
Table1C_BLOB: TBlobField;
Table1C_CLOB: TMemoField;
OpenPictureDialog1: TOpenPictureDialog; //ļȡͼƬ
OpenDialog1: TOpenDialog; //ļȡ
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin //
with Table1 do
begin
Insert; //״̬Ϊ״̬
if OpenPictureDialog1.Execute then //ͼƬϢ
Table1C_BLOB.LoadFromFile(OpenPictureDialog1.FileName);
if OpenDialog1.Execute then //Ϣ
Table1C_CLOB.LoadFromFile(OpenDialog1.FileName);
end;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin //ύ
try
Table1.Post;
except
Application.MessageBox('','',0);
end;
end;

end.

ע⣺
openpiceturedilogֻܴdmp,ico,wmfļҪͼƬļʽ⼸ࣻ
ֶβļʱֶ
ֻǶlobֶεһСС̽÷ָ̡֮

ע:ƪ´󲿷Ӿȡַ̳İȨŸ֪һӦ

ע⣺openpiceturedilogֻܴdmp,ico,wmfļҪͼƬļʽ⼸ࣻ
ֶβļʱֶ


hoplite :2005.01.10 12:16 ::: ( ݿ ) ::Ķ:(374) :: (0) :: (0)