实验：关于LOB(LOB表的建立，存储，DBMS_LOB的一些初步学习)
我发现相当一部分贴子是有关LOB(large object)的,我想参照ORACLE8 完全参考手册的例子为大家做一个演示,加深一下印象.
实验工具:
win2000 advanced server
oracle8.1.7
ORACLE8 完全参考手册
SQL Navigator 4 (它的QUERY结果可以很方便的检查LOB的值)
SQL*Plus Worksheet (用力执行命令）
准备工作：
1.create tablespaces
CREATE TABLESPACE LOBUSER
LOGGING
DATAFILE 'E:\ORACLE\ORADATA\ORCL\LOBUSER1.ora' SIZE 100M REUSE
AUTOEXTEND
ON NEXT 1280K MAXSIZE 32767M DEFAULT
STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 )
CREATE TABLESPACE LOBLOB
LOGGING
DATAFILE 'E:\ORACLE\ORADATA\ORCL\LOBLOB.ora' SIZE 50M REUSE
AUTOEXTEND
ON NEXT 1280K MAXSIZE 32767M DEFAULT
STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 )
2. Create user LOBMAN
CREATE USER "LOBMAN" PROFILE "DEFAULT" IDENTIFIED BY "manager"
DEFAULT TABLESPACE "LOBUSER"
TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "LOBMAN";
GRANT "DBA" TO "LOBMAN";
3. Create Oracle Directory:
登陆LOBMAN/MANAGER
create or replace directory proposal_dir as 'e:\oracle\tmp'
这个object并不能在DBA studio中找到,你需要query
all_directories , 你会发现它的owner 是ＳＹＳ.
4. Create LOB Table
create table proposal
(proposal_id number(10) primary key,
recipient_name varchar2(25),
proposal_name varchar2(25),
short_description varchar2(1000),
proposal_text CLOB,
Budget BLOB,
cover_letter BFILE)
storage (initial 50K next 50K pctincrease 0)
tablespace lobuser
lob (proposal_text,Budget) store as
(tablespace loblob storage (initial 50K next 50K pctincrease 0)
chunk 16k pctversion 10 nocache logging);
这样一来，CLOB and BLOB Columns 就储存在另一个tablespace中．
注意，CLOB和BLOB是储存在数据库中，而BFILE是储存在数据库外（库中
仅仅放定位符)
5. 建立e:\oracle\tmp 目录并存入文件
操作：
1. 初始话(insert)
insert into PROPOSAL
values (1,'DOT PHILLIPS','CLEAR PHILLIPS FIELD',NULL,'This
is the text of proposal to clear Phillips field.',EMPTY_BLOB(),NULL);
commit;
insert into PROPOSAL
values (2,'BRAD OHMONT','REBUILD FENCE',NULL,EMPTY_CLOB(),EMPTY_BLOB(),BFILENAME('PROPOSAL_DIR','genmd.log'));
commit;
注意目录一定要大写！
insert into PROPOSAL
select 3,'SKIP GATES','CLEAR GATES FIELD',NULL,Proposal_text,Budget,cover_letter
from proposal
where proposal = 1;
commit;
2. Update
select proposal_text from proposal where proposal_id = 3 for update;
update proposal set proposal_text = 'This is the new proposal text .'
where proposal_id = 3;
commit;
注意：for update 是必须的，当update clob 和blob 时，必须先把记录锁住
（对于BFILE则不用）
3. DBMS_LOB Package->Read
set serveroutput on
declare
locator_var clob;
amount_var integer;
offset_var integer;
output_var varchar2(10);
begin
amount_var:=10;
offset_var:=1;
select proposal_text into locator_var
from Proposal
where proposal_id = 1;
DBMS_LOB.read(locator_var,amount_var,offset_var,output_var);
DBMS_OUTPUT.PUT_LINE('Start of proposal text: '||output_var);
end;
/
4. DBMS_LOB Package->write
set serveroutput on
declare
locator_var clob;
amount_var integer;
offset_var integer;
buffer_var varchar2(12);
begin
amount_var:=12;
offset_var:=10;
buffer_var :='ADD NEW TEXT';
select proposal_text into locator_var
from Proposal
where proposal_id = 3 for update; /*for update*/
DBMS_LOB.WRITE(locator_var,amount_var,offset_var,buffer_var);
end;
/
实验完成．DBMS_LOB package contains a lot of sub routines which are
very useful to operator the LOB column .
问题点数：0、回复次数：5Top
1 楼black_snail（●男人要忍○）回复于 2003-04-04 12:09:57 得分 0
当你query user_segments 时,你会发现每个LOB column 都有自己的SEGMENTS (LOBSEGMENT,LOBINDEX)Top
2 楼maohaisheng（虫族：无名小虫）回复于 2003-04-05 09:36:59 得分 0
支持版主Top
3 楼qxm（qxm）回复于 2003-04-15 17:15:22 得分 0
good!Top
4 楼beckhambobo（beckham）回复于 2003-04-15 18:58:21 得分 0
比较详细:供大家参考
http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76940/adl03pr4.htm#146545Top
5 楼Fred_Mark（）回复于 2003-05-17 12:39:12 得分 0
支持，收藏Top
