[Oracle]CLOB⼤数据字段类型操作⽅法(转)
VARCHAR2既分PL/SQL Data Types中的变量类型,也分Oracle Database中的字段类型,不同场景的最⼤长度不同。接下来通过本⽂给⼤家分享Oracle的CLOB⼤数据字段类型操作⽅法,感兴趣的朋友⼀起看看吧
⼀、Oracle中的varchar2类型
我们在Oracle数据库存储的字符数据⼀般是⽤VARCHAR2。VARCHAR2既分PL/SQL Data Types中的变量类型,也分Oracle Database中的字段类型,不同场景的最⼤长度不同。
在Oracle Database中,VARCHAR2 字段类型,最⼤值为4000;PL/SQL中 VARCHAR2 变量类型,最⼤字节长度为32767。
当 VARCHAR2 容纳不下我们需要存储的信息时,就出来的Oracle的⼤数据类型LOB( Large Object,⼤型对象)。
⼆、Oarcle中的LOB类型
在Oracle中,LOB(Large Object,⼤型对象)类型的字段现在⽤得越来越多了。因为这种类型的字段,
容量⼤(最多能容纳4GB的数据),且⼀个表中可以有多个这种类型的字段,很灵活,适⽤于数据量⾮常⼤的业务领域(如图象、档案等)。
LOB类型分为BLOB和CLOB两种:BLOB即⼆进制⼤型对象(Binary Large Object),适⽤于存贮⾮⽂本的字节流数据(如程序、图象、影⾳等)。
⽽CLOB,即字符型⼤型对象(Character Large Object),则与字符集相关,适于存贮⽂本型的数据(如历史档案、⼤部头著作等)。
三、DB中使⽤CLOB类型字段
(⼀)、创建表(使⽤sql或者直接在PL/SQL客户端创建),字段类型CLOB
-- Create table
create table TEMP
(
name  VARCHAR2(200),
age    NUMBER,
temp_clob CLOB
)
tablespace INSIGHTDATA3_TS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 160K
next 1M
minextents 1
maxextents unlimited
);
(⼆)、增删改查
先看⼀下使⽤普通⽅式操作CLOB类型:
SELECT t.name, t.temp_clob FROM temp t; --普通查询 INSERT INTO temp t VALUES ('Grand.Jon', 22, '加⼊博客园的第⼀天');
查询因为不是varchar2类型,所以普通查询看不到CLOB类型的字段内容,结果如下:
⽽普通插⼊操作也会因为Oracle的隐式转换,默认把字符串转换成varchar2类型,⼀旦字符串内容超过varchar2的最⼤限度就会报会报ora-01704(字符串太长)错误。
正确操作:
--使⽤PL/SQL语法,采取绑定变量的⽅式解决,⽽不是直接拼接SQL
DECLARE
V_LANG CLOB := '待插⼊的海量字符串';
V_UPDATE CLOB := '更新的海量字符串';
BEGIN
INSERT INTO temp t VALUES ('Grand.Jon', 22, V_LANG);    --增加
UPDATE temp t p_clob = V_UPDATE WHERE rownum = 1; --修改
SELECT t.NAME, dbms_lob.p_clob) FROM TEMP t;  --查询  将CLOB转成字符类型
DELETE temp t WHERE rownum = 1;              --按列删除 
COMMIT;
END;
/
对CLOB的操作我们在存储过程中基本上使⽤ dbms_lob 中 substr , append , write 等⽅法。
查询结果如下:
dbms_lob ⽅法总结
atetemporary(V_SQL,true);  --创建⼀个临时clob,⽤来存储拼接的sql
dbms_lob.write(v_SQL,'写⼊信息');    --写⼊操作
dbms_lob.append(v_SQL,',');        --拼接clob
dbms_lob.substr(v_SQL);          --截取clob,不传参数就是全部读取
dbms_lob.freetemporary(v_SQL);      --释放clob
四、在存储过程中使⽤CLOB类型实例
需求:以开发的存储过程为例,需要循环遍历时间范围拼接sql,将时间⽇期按列反转(pivot),如果时间太长(1年以上)sql语句(varchar2)就会超出范围报错,这时候就需要使⽤CLOB来存储拼接的sql。
PROCEDURE P_AND_CPT_RATIOOTH_APP_BAK2_N(
V_APPIDS IN VARCHAR2,
V_TYPE IN VARCHAR2,
V_CHANNEL IN VARCHAR2,
V_TABLE IN VARCHAR2,
V_START IN VARCHAR2,
V_END IN VARCHAR2,
RESULT OUT mycursor
) IS
V_SQL CLOB;
V_SQLWHERE VARCHAR2(32767) default '';
V_SQLWHERE_CHANNEL VARCHAR2(32767) default '';
V_SQL_DATES CLOB;
V_Sdate DATE;
V_Edate DATE;
V_TABLE_DATE VARCHAR2(50);
V_TABLE_TYPE VARCHAR2(50);
V_START_DATE VARCHAR2(50);
V_END_DATE  VARCHAR2(50);
V_DAY VARCHAR2(50);
BEGIN
select column_name into V_TABLE_DATE from user_tab_columns where table_name=''||V_TABLE||'' and column_id=1;  select column_name into V_TABLE_TYPE from user_tab_columns where table_name=''||V_TABLE||'' and column_id=5;  atetemporary(V_SQL,true);--创建⼀个临时lob
atetemporary(V_SQL_DATES,true);--创建⼀个临时lob
IF V_APPIDS is NOT NULL THEN
V_SQLWHERE := 'AND t.appid in ('||V_APPIDS||')';
END IF;
IF V_CHANNEL IS NOT NULL THEN
V_SQLWHERE_CHANNEL := 'AND t.channel = '''||V_CHANNEL||'''';
END IF;
IF V_TABLE_DATE = 'MON' THEN
V_START_DATE := SUBSTR(V_START,0,6);
V_END_DATE := SUBSTR(V_END,0,6);
v_sdate := to_date(V_START_DATE, 'yyyymm');
v_edate := to_date(V_END_DATE, 'yyyymm');
WHILE (v_sdate <= v_edate) LOOP
dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymm'));--把临时字符串付给v_str
IF v_sdate != v_edate THEN
dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str
END IF;
v_sdate := add_months(v_sdate,1);
END LOOP;
ELSE --周和⽇类型都是 DAY
v_sdate := to_date(V_START, 'yyyymmdd');
v_edate := to_date(V_END, 'yyyymmdd');
V_END_DATE := V_END;
IF SUBSTR(V_TYPE,0,1)='d' THEN
V_START_DATE := to_char(v_sdate, 'yyyymmdd');
WHILE (v_sdate <= v_edate) LOOP
dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymmdd'));--把临时字符串付给v_str
IF v_sdate != v_edate THEN
dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str
END IF;
v_sdate := v_sdate+1;
END LOOP;
ELSIF SUBSTR(V_TYPE,0,1)='w' THEN
select to_char(V_Sdate,'d') INTO V_DAY from dual;
IF V_DAY!=2 THEN
V_Sdate:=V_Sdate-7;
END IF;
V_START_DATE := to_char(v_sdate, 'yyyymmdd');
WHILE (v_sdate <= v_edate) LOOP
select to_char(V_Sdate,'d') INTO V_DAY from dual;
IF V_DAY=2 THEN
dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymmdd'));--把临时字符串付给v_str
IF V_Edate-v_sdate >7 THEN
dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str
END IF;
END IF;
v_sdate := v_sdate+1;
END LOOP;
END IF;
END IF;
dbms_lob.append(v_sql,'SELECT * FROM( SELECT *
FROM '||V_TABLE||' t
WHERE
t.'||V_TABLE_TYPE||' = '''||V_TYPE||'''
AND t.'||V_TABLE_DATE||' >= '''||V_START_DATE||'''
AND t.'||V_TABLE_DATE||' <= '''||V_END_DATE||'''
'||V_SQLWHERE||'
'||V_SQLWHERE_CHANNEL||' ) t1
pivot(sum(MARKETSHARE)
for '||V_TABLE_DATE||' in(');
dbms_lob.append(v_sql,v_SQL_DATES);
dbms_lob.append(v_sql,'))');
dbms_output.put_line(v_sql);
OPEN result FOR v_sql;
dbms_lob.freetemporary(v_sql);--释放lob
dbms_lob.freetemporary(v_SQL_DATES);--释放lob
--dbms_output.put_line(V_SQLDATE);
-- dbms_output.put_line(v_SQL_DATES);
--记录操作⽇志及错误⽇志
END;
五、使⽤Java开发操作CLOB字段
(⼀)、原⽣JDBC处理CLOB类型
增加,⼀般会插⼊⼀个空的clob到数据库对应的字段,然后锁定该列,⽤Write将待插⼊字符串写⼊进去。      重点:这两步操作要放在同⼀个事务⾥⾯。具体增加的⽅法如下:
public boolean save(Article article){
boolean result = true;
Connection conn = Instance();
String sql = "insert into temp values(?,?,empty_clob())";
//锁住该列,防⽌并发写⼊时候该字段同时被多次写⼊造成错误
String sqlClob = "select temp_clob from temp t where t.name=? for update";    PreparedStatement pst =null;
ResultSet rs = null;
Writer writer = null;
try {
conn.setAutoCommit(false);//设置不⾃动提交,开启事务
pst = conn.prepareStatement(sql);
pst.setString(Name());
pst.setString(Age());
pst= conn.prepareStatement(sqlClob);
pst.setInt(1, Id());
rs = uteQuery();
CLOB clob = null;
()){
try {
clob = (CLOB) rs.getClob(1);
writer = CharacterOutputStream(); //拿到clob的字符输⼊流
writer.Content());
writer.flush();
writer.close();resultset 遍历
} catch (IOException e) {
e.printStackTrace();
}
}
connmit();
} catch (SQLException e) {
result = false;
try {
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
conn.setAutoCommit(true);//还原
ConnectionUntils.close(rs, pst, conn);
}
return result;
}
update操作,update时候主要利⽤PreparedStatement的setClob⽅法:

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。