CREATE TABLE sammy_test_clob (
ID NUMBER,
site VARCHAR2(4000),
mail_data CLOB
)
INSERT INTO sammy_test_clob
VALUES ( 1, 'BQA', EMPTY_CLOB( ));
INSERT INTO sammy_test_clob
VALUES ( 2, 'BQP', EMPTY_CLOB( ));
INSERT INTO sammy_test_clob
VALUES ( 3, 'BQA', EMPTY_CLOB( ));
--读
DECLARE
hh VARCHAR2( 4000 );
hhh VARCHAR2( 5000 );
BEGIN
SELECT getclob( 'sammy_test_clob',
'id',
'mail_data',
2,
1 )
INTO hh
FROM DUAL;
-- for v IN 1..length(hh)/255 loop
--hhh := substr(hh,0,255);
--hh := substr(hh,v*255);
hhh := TRIM( hh );
DBMS_OUTPUT.put_line( hhh );
--end loop;
-- DBMS_OUTPUT.put_line( hhh );
END;
--写
DECLARE
lobloc CLOB;
buffer VARCHAR2( 2000 );
amount NUMBER := 20;
offset NUMBER := 1;
BEGIN
----初始化要写入的数据
buffer := 'This is a writing exampleGGGGGGFFFFFFFFF';
amount := LENGTH( buffer );
SELECT mail_data
INTO lobloc -- 获取定位器并锁定行
FROM sammy_test_clob
WHERE site = 'BQP'
FOR UPDATE;
DBMS_LOB.WRITE( lobloc,
amount,
1,
buffer );
COMMIT;
END;
--其它测试
SELECT *
FROM sammy_test_clob;
CALL UPDATECLOB('sammy_test_clob','id','mail_data',2,1,'89898989898989898')
DECLARE
hh VARCHAR2( 5000 );
hhh VARCHAR2( 5000 );
BEGIN
SELECT STRING_AGGREGATE_FUN( mail_data )
INTO hh
FROM sammy_test_clob
WHERE ID = 2
GROUP BY site;
FOR v IN 1 .. LENGTH( hh ) / 255
LOOP
hhh := SUBSTR( hh,
0,
255 );
hh := SUBSTR( hh, v * 255 );
DBMS_OUTPUT.put_line( hhh );
END LOOP;
END;
UPDATE sammy_test_clob
SET mail_data =
'45dsfffdffffdffffffffffffffffffkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'
WHERE ID = 3
