- 论坛徽章:
- 0
|
回复 #2 renxiao2003 的帖子
吐血了,猜猜我怎么做的?
我绕地球跑了一圈:
--------------------------------------------
1. CREATE TABLE view_sites_info
(
site_id NUMBER(3),
audio BLOB DEFAULT empty_blob(),
document CLOB DEFAULT empty_clob(),
video_file BFILE DEFAULT NULL,
CONSTRAINT PK_TAB_view_sites_info PRIMARY KEY (site_id)
)
2. delete from view_sites_info;
3. insert into view_sites_info(site_id) values (100);
4. select * from view_sites_info;
5. --************************************** below is write
DECLARE
lobloc CLOB;
buffer VARCHAR2(2000);
amount NUMBER := 20;
offset NUMBER := 1;
BEGIN
buffer := 'This is a writing example';
amount := length(buffer);
SELECT document INTO lobloc FROM view_sites_info WHERE site_id = 100 FOR UPDATE;
dbms_lob.write(lobloc,amount,1,buffer);
COMMIT;
END;
6. --*************************************** below is read
declare
doc_len NUMBER;-- clob length
doc_loc CLOB;--clob content
buffer_read VARCHAR2(2000);
offset NUMBER := 1;
amount NUMBER := 2000;
BEGIN
SELECT length(document), document INTO doc_len, doc_loc FROM view_sites_info WHERE site_id = 100;
dbms_lob.read(doc_loc,amount,offset,buffer_read);
dbms_output.put_line(buffer_read);
END;
7. --****************************************** below is update
declare
step_char VARCHAR2(1);
pos NUMBER := 1;-- position
doc_len NUMBER;-- clob length
doc_loc CLOB;--clob location
buffer_read VARCHAR2(2000);
buffer_write VARCHAR2(2000);
offset NUMBER := 1;
amount NUMBER := 2000;
BEGIN
SELECT length(document), document INTO doc_len, doc_loc FROM view_sites_info WHERE site_id = 100 FOR UPDATE;
dbms_lob.read(doc_loc,amount,offset,buffer_read);
WHILE (pos < doc_len + 1) LOOP
--select substr(buffer_read,pos, 1) INTO step_char FROM dual;
step_char := substr(buffer_read,pos, 1);
IF step_char = 'i' Then
step_char := 'a';
END IF;
buffer_write := buffer_write || step_char;
pos := pos + 1;
END LOOP;
dbms_lob.write(doc_loc, amount, 1, buffer_write);
COMMIT;
END;
[ 本帖最后由 TOADLover 于 2010-1-8 15:59 编辑 ] |
|