鍦∣racle鐨勬棭鏈熺増鏈?腑锛屼娇鐢↙ONG 鍜?LONG RAW 绫诲瀷瀛樻斁澶ф暟鎹??備粠8i鐗堟湰寮?濮嬶紝Oracle寮曞叆浜哃OB鏁版嵁绫诲瀷锛屽苟涓擮racle寤鸿?寮?鍙戜汉鍛樺敖閲忓幓浣跨敤LOB绫诲瀷鑰屼笉鍘讳娇鐢?LONG 鍜?LONG RAW 銆侺OB绫诲瀷鍜孡ONG銆丩ONG RAW绫诲瀷鐩告瘮鏈夊嚑涓?笉鍚岀殑鍦版柟銆傚?锛?br>1銆丩OB(闄や簡NCOLB)鍙?互浣滀负瀵硅薄绫诲瀷鐨勪竴涓?睘鎬э紝浣哃ONG绫诲瀷涓嶅彲浠ャ??br>2銆丩OB鐨勬渶澶у?兼槸4G锛岃?孡ONG鍙?湁2G銆?br>3銆丩OB鏀?寔闅忔満璁块棶鏁版嵁锛屼絾LONG鍙?敮鎸侀『搴忚?闂???br>聽聽聽 LOB鍖呮嫭鍐呴儴LOB鍜屽?閮↙OB锛屽叾涓?唴閮↙OB(BLOB CLOB NLOB)鐨勬暟鎹?瓨鏀惧埌鏁版嵁搴撲腑锛岃?屽?閮↙OB(BFILE)鐨勬暟鎹?瓨鏀惧湪鎿嶄綔绯荤粺鐨勬枃浠朵腑銆傚叿浣撹?鏄庡?涓嬶細
BLOB:瀛樻斁浜岃繘鍒舵牸寮忕殑鏁版嵁锛屽?锛氬浘鍍忋?侀煶棰戙?佽?棰戠瓑銆?br>CLOB:瀛樻斁鏁版嵁搴撳瓧绗﹂泦鏍煎紡鐨勫瓧绗︽暟鎹?紝濡傦細澶х殑鏂囨湰鏁版嵁銆?br>NCLOB:瀛樻斁Unicode瀛楃?闆嗙殑瀛楃?鏁版嵁銆?br>BFILE:瀛樻斁鎸囧悜鎿嶄綔绯荤粺鏂囦欢鐨勬寚閽堬紝骞朵笖璇ユ枃浠跺嵆鍙?互鏄?枃鏈?枃浠讹紝涔熷彲浠ユ槸浜岃繘鍒舵枃浠躲?傚綋鎴戜滑璁块棶璇ョ被鍨嬫墍琛ㄧず鐨勫垪鏃讹紝鍙?兘璇诲彇鏁版嵁锛屼笉鑳戒慨鏀广??br>聽聽
涓?銆佺悊瑙ｄ粈涔堟槸LOB瀹氫綅鍣ㄥ拰LOB鏁版嵁
聽聽聽 LOB鏁版嵁鏄?垜浠?瓨鍏ュ埌鏁版嵁搴撲腑鐨勫?硷紝鑰孡OB 瀹氫綅鍣?鏄?搷浣滆繖浜涘?肩殑鎸囬拡銆傚綋鎴戜滑鎯宠?鍙栨垨鑰呬慨鏀筁OB鏁版嵁鏃讹紝鎴戜滑蹇呴』鍏堣?鑾峰彇LOB 瀹氫綅鍣?锛岀劧鍚庨?氳繃LOB瀹氫綅鍣ㄦ墠鑳借?鍙栨垨鑰呬慨鏀筁OB鏁版嵁锛屼笅闈㈢殑绀轰緥鑳藉?甯?姪鎴戜滑鏇村ソ鐨勭悊瑙?OB鏁版嵁鍜孡OB瀹氫綅鍣?細
DECLARE
picLOB BLOB;
BEGIN
聽聽聽--鍋囧畾 picColumn鍒楁槸BLOB绫诲瀷
聽 SELECT picColumn INTO picLOB FROM myTable WHERE ID=10;
END;
聽聽聽 褰揝ELECT INTO璇?彞鎵ц?瀹屼箣鍚庯紝 picLOB鍙橀噺灏嗚?璧嬪?硷紝浣嗘槸 picLOB鍙橀噺鐨勫?间笉鏄疞OB鏁版嵁锛岃?屾槸LOB鏁版嵁鐨勫畾浣嶅櫒銆傝?鎯宠幏鍙朙OB鏁版嵁锛屾垜浠?繀椤诲?瀹氫綅鍣ㄨ繘琛屾搷浣滐紝Oracle涓璂BMS_LOB鍖呴噷鐨勫緢澶氬嚱鏁板拰杩囩▼閮藉彲浠ュ?瀹氫綅鍣ㄨ繘琛屾搷浣滐紝濡備笅锛?br>APPEND()锛?灏嗘簮LOB涓?殑鍐呭?鍔犲埌鐩?殑LOB涓?br>COPY() 锛氫粠婧怢OB涓??鍒舵暟鎹?埌鐩?殑LOB
ERASE()锛?鍒犻櫎LOB涓?叏閮ㄦ垨閮ㄥ垎鍐呭?
TRIM()锛?灏哃OB鍊煎噺灏戝埌鎸囧畾鐨勯暱搴?br>WRITE()锛?鍚慙OB 涓?啓鍏ユ暟鎹?br>COMPARE()锛?姣旇緝涓や釜鍚岀?鏁版嵁绫诲瀷鐨凩OB鐨勯儴鍒嗘垨鍏ㄩ儴鍊兼槸鍚︾浉鍚?br>GETLENGTH()锛?鑾峰彇LOB鐨勯暱搴?br>READ()锛?浠嶭OB涓??鍑烘暟鎹?/p>
浜屻?佷娇鐢–LOB鏁版嵁绫诲瀷
绀轰緥1锛氬垱寤哄甫鏈塁LOB鏁版嵁绫诲瀷鐨勮〃锛屽苟鎻掑叆鏁版嵁
CREATE TABLE CLOB_TABLE
(
聽 id NUMBER(3) PRIMARY KEY,
聽 remark CLOB聽聽聽 --remark锛氬?娉?br>);
INSERT INTO CLOB_TABLE(id) VALUES(1);聽 --remark鍒楁病鏈夊畾浣嶅櫒锛屽苟涓旀病鏈夋暟鎹?br>INSERT INTO CLOB_TABLE VALUES(2,'ID涓?鐨勫?娉ㄤ俊鎭?);聽 --remark鍒楁湁瀹氫綅鍣?紝骞朵笖鏈夋暟鎹?br>INSERT INTO CLOB_TABLE VALUES(3,empty_clob());--remark鍚?湁瀹氫綅鍣?紝浣嗘槸娌℃湁鏁版嵁
妫?绱㈠畾浣嶅櫒鐨勫?硷細
DECLARE
myLob CLOB;
BEGIN
聽 SELECT remark INTO myLob FROM CLOB_TABLE WHERE id=___ ;
聽 IF myLob IS NULL THEN
聽聽聽 DBMS_OUTPUT.PUT_LINE('myLob IS NULL ');
聽 ELSE
聽聽聽 DBMS_OUTPUT.PUT_LINE(myLob );
聽 END IF;
END;
褰撲笅鍒掔嚎濉?叆1鏃讹紝缁撴灉濡備笅锛?br>myLob IS NULL
褰撲笅鍒掔嚎濉?叆2鏃讹紝缁撴灉濡備笅锛?-鎵撳嵃鍑哄畾浣嶅櫒鎵?鎸囧悜鐨勬暟鎹?br>ID涓?鐨勫?娉ㄤ俊鎭?br>褰撲笅鍒掔嚎濉?叆3鏃讹紝娌℃湁鎵撳嵃鍑轰俊鎭???/p>
淇?敼id涓?鐨剅emark鍒楋紝浣垮叾鍏锋湁瀹氫綅鍣?細
UPDATE CLOB_TABLE SET remark=empty_clob() WHERE id=1;
杩欐椂鍐嶈皟鐢ㄤ互涓婄殑浠ｇ爜鍧楋紝杩欐?娌℃湁鎵撳嵃鍑轰俊鎭???/p>
绀轰緥2锛氬悜CLOB鍒楄拷鍔犳暟鎹?br>DECLARE
myLob CLOB;
buffer VARCHAR2(20) := '杩欐槸绗?竴娆¤拷鍔犵殑鍐呭?';
amount INT;
offset INT;
BEGIN
聽 --瑕佹兂鑳藉?淇?敼LOB鏁版嵁锛屽繀椤绘寚瀹欶OR UPDATE瀛愬彞
聽 SELECT remark INTO myLob FROM CLOB_TABLE WHERE id=2 FOR UPDATE ;
聽 amount:=LENGTH( buffer );聽 --鑾峰彇杩藉姞鏂囨湰鐨勯暱搴?br>聽 offset:=DBMS_LOB.GETLENGTH (myLob)+1;聽 --鑾峰彇瀹氫綅鍣ㄥ紩鐢ㄧ殑LOB鏁版嵁鐨?闀垮害
聽 DBMS_LOB.WRITE (myLob,amount,offset, buffer );-- 鍚慍LOB鍒楄拷鍔犳暟鎹?紝姣忔?鏈?澶ц拷鍔?2767瀛楄妭銆傝?娉ㄦ剰蹇呴』鍏堝瓨鍦ㄥ畾浣嶅櫒鎵嶈兘杩藉姞鏁版嵁锛屽惁鍒欎細鍑虹幇invalid LOB locator specified: ORA-22275閿欒?銆?br>聽 --DBMS_LOB.WRITEAPPEND(myLob,amount, buffer );--浣跨敤杩欎釜鍑芥暟涓嶇敤鑰冭檻offset鍙傛暟
END;
绀轰緥3锛氳?鍙朇LOB鏁版嵁
DECLARE
myLob CLOB;
text VARCHAR2(40);
amount INT;
offset INT;
BEGIN
聽 SELECT remark INTO myLob FROM CLOB_TABLE WHERE id=2;
聽 amount:=15;
聽 offset:=3;
聽 --浠嶭OB鏁版嵁涓?殑绗?笁涓?瓧绗﹀紑濮嬭?鍙栧叡璇诲彇15涓?瓧绗︽斁鍏ュ埌text鍙橀噺
聽 DBMS_LOB.READ(myLob,amount,offset,text);
聽 DBMS_OUTPUT.PUT_LINE(text);
END;
涓夈?佷娇鐢˙File鏁版嵁绫诲瀷
聽聽聽 BFile绫诲瀷涓嶅瓨鏀炬枃浠跺疄闄呮暟鎹?紝鑰屾槸瀛樻斁鏂囦欢鐨勬寚閽堬紝鏂囦欢鐨勬暟鎹?瓨鍌ㄥ湪鎿嶄綔绯荤粺鐨勬枃浠剁郴缁熶笂锛屽苟涓旀垜浠?彧鑳介?氳繃BFile绫诲瀷璇诲彇鏂囦欢鐨勬暟鎹?紝涓嶈兘淇?敼銆?br>绀轰緥1锛氬垱寤篠cottDir鐩?綍锛屽苟鎺堜簣scott鐢ㄦ埛璇绘潈闄?br>CREATE DIRECTORY ScottDir AS 'E:\ScottDir'; --娉ㄦ剰姝よ?鍙ュ苟涓嶄細鍦ㄦ搷浣滅郴缁熶笂鍒涘缓鏂囦欢澶?br>GRANT READ ON DIRECTORY ScottDir TO scott;
绀轰緥2锛氬垱寤哄惈鏈塀File鏁版嵁绫诲瀷鐨勫垪锛屽苟鎻掑叆鏁版嵁
CREATE TABLE BFILE_TABLE
(
聽聽 id NUMBER PRIMARY KEY,
聽聽 remark聽 BFILE
);
鎻掑叆鏁版嵁锛?br>INSERT INTO BFILE_TABLE(id) VALUES(1);
UPDATE BFILE_TABLE SET remark=BFILENAME('SCOTTDIR ','1.txt') ;
INSERT INTO BFILE_TABLE(id,remark) VALUES(2,BFILENAME(' SCOTTDIR ','2.doc') );
娉ㄦ剰锛欱FILE鏁版嵁绫诲瀷鎸囧悜鐨勬枃浠讹紝鍗冲彲浠ユ槸鏂囨湰鏂囦欢涔熷彲浠ユ槸浜岃繘鍒舵枃浠躲?傝?屼笖BFILE鏁版嵁绫诲瀷涓嶈兘閫氳繃SELECT璇?彞鐩存帴鏌ョ湅鍏跺唴瀹广??/p>
绀轰緥3锛氬皢BFile鏂囦欢鐨勫唴瀹硅?鍙栧埌CLOB鍒?鍓嶆彁鏄疊File鏂囦欢鏄?枃鏈?枃浠?
DECLARE
src聽聽 BFILE;
des聽聽 CLOB;
amount聽聽聽 INT;
src_offset INT :=1;
des_offset INT :=1;
csid INT :=850;
lc聽聽 INT :=0;
warning INT;
BEGIN
聽 SELECT remark INTO src FROM BFile_TABLE WHERE id=1;
聽 SELECT remark INTO des FROM CLOB_TABLE WHERE id=1 FOR UPDATE ;
聽 DBMS_LOB.FILEOPEN(src,DBMS_LOB.FILE_READONLY);
聽 amount:=DBMS_LOB.GETLENGTH(src);
聽 DBMS_OUTPUT.PUT_LINE(amount);
聽DBMS_LOB.LOADCLOBFROMFILE (des,src,amount,des_offset,src_offset,csid,lc,warning);
聽 DBMS_LOB.CLOSE(src);
END;
鍥涖?佷娇鐢˙LOB鏁版嵁绫诲瀷
绀轰緥1锛氬垱寤哄甫鏈塀LOB鍒楃殑琛ㄥ苟鎻掑叆鏁版嵁
聽CREATE TABLE BLOB_TABLE
聽(
聽聽 id NUMBER(3),
聽聽 remark BLOB
聽);
INSERT INTO BLOB_TABLE VALUES(1,EMPTY_BLOB());
灏嗘枃浠跺唴瀹规彃鍏ュ埌BLOB鍒楋細
DECLARE
src聽聽 BFILE;
des聽聽 BLOB;
amount聽聽聽 INT;
src_offset INT :=1;
des_offset INT :=1;
csid INT :=0;
lc聽聽 INT :=0;
warning INT;
BEGIN
聽 src:=BFILENAME('SCOTTDIR','1.txt');
聽 SELECT remark INTO des FROM BLOB_TABLE WHERE id=1 FOR UPDATE;
聽 DBMS_LOB.FILEOPEN(src,DBMS_LOB.FILE_READONLY);
聽 amount:=DBMS_LOB.GETLENGTH(src);
聽 DBMS_OUTPUT.PUT_LINE(amount);
聽 --DBMS_LOB.LOADBLOBFROMFILE (des,src,amount,des_offset,src_offset);
聽 DBMS_LOB.CLOSE(src);
END;