读取函数如下:----------------------------------output text of lob------------------------------- CREATE TYPE content_table_type IS TABLE OF VARCHAR2(100);CREATE OR REPLACE FUNCTION get_content(no NUMBER) RETURN content_table_type IS clob_loc CLOB; len INT; amount INT; offset INT:=1; temp INT; content_table content_table_type:=content_table_type('A'); BEGIN SELECT resume INTO clob_loc FROM clob_table WHERE id=no; len:=DBMS_LOB.getlength(clob_loc); temp:=TRUNC((len-1)/100); IF temp>0 THEN content_table.extend(temp); END IF; FOR i IN 1..(temp+1) LOOP IF TRUNC((len-offset+1)/100)>0 THEN amount:=100; DBMS_LOB.read(clob_loc,amount,offset,content_table(i)); offset:=100*i+1; ELSE amount:=len-offset+1; DBMS_LOB.read(clob_loc,amount,offset,content_table(i)); END IF; END LOOP; RETURN content_table; END;建表语句如下: -- Create table create table CLOB_TABLE ( ID NUMBER(4) not null, NAME VARCHAR2(10), RESUME CLOB ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Create/Recreate primary, unique and foreign key constraints alter table CLOB_TABLE add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );这个写的只是读出来,复制的话,你自己再看看吧
CREATE TYPE content_table_type IS TABLE OF VARCHAR2(100);CREATE OR REPLACE FUNCTION get_content(no NUMBER)
RETURN content_table_type IS
clob_loc CLOB;
len INT;
amount INT;
offset INT:=1;
temp INT;
content_table content_table_type:=content_table_type('A');
BEGIN
SELECT resume INTO clob_loc FROM clob_table WHERE id=no;
len:=DBMS_LOB.getlength(clob_loc);
temp:=TRUNC((len-1)/100);
IF temp>0 THEN
content_table.extend(temp);
END IF;
FOR i IN 1..(temp+1) LOOP
IF TRUNC((len-offset+1)/100)>0 THEN
amount:=100;
DBMS_LOB.read(clob_loc,amount,offset,content_table(i));
offset:=100*i+1;
ELSE
amount:=len-offset+1;
DBMS_LOB.read(clob_loc,amount,offset,content_table(i));
END IF;
END LOOP;
RETURN content_table;
END;建表语句如下:
-- Create table
create table CLOB_TABLE
(
ID NUMBER(4) not null,
NAME VARCHAR2(10),
RESUME CLOB
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table CLOB_TABLE
add primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);这个写的只是读出来,复制的话,你自己再看看吧