clob类型字段最大存储长度_Oracle的CLOB大数据字段类型

最新推荐文章于 2022-07-12 12:18:44 发布

weixin_39854951

最新推荐文章于 2022-07-12 12:18:44 发布

阅读量2.1k

点赞数

文章标签： clob类型字段最大存储长度

本文链接：https://blog.csdn.net/weixin_39854951/article/details/111516436

版权

一、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(

nameVARCHAR2(200),

ageNUMBER,

temp_clob CLOB

)

tablespace INSIGHTDATA3_TS

pctfree10initrans1maxtrans255storage

(

initial 160Knext1M

minextents1maxextents 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

DECLAREV_LANG CLOB := '待插入的海量字符串';

V_UPDATE CLOB := '更新的海量字符串';BEGIN

INSERT INTO temp t VALUES ('Grand.Jon', 22, V_LANG); --增加

UPDATE temp t SET t.temp_clob = V_UPDATE WHERE rownum = 1; --修改

SELECT t.NAME, dbms_lob.substr(t.temp_clob) FROM TEMP t; --查询　　将CLOB转成字符类型

DELETE temp t WHERE rownum = 1; --按列删除

COMMIT;END;/

对CLOB的操作我们在存储过程中基本上使用 dbms_lob 中 substr , append , write 等方法。

dbms_lob 方法总结

1 dbms_lob.createtemporary(V_SQL,true); --创建一个临时clob，用来存储拼接的sql

2 dbms_lob.write(v_SQL,'写入信息'); --写入操作

3 dbms_lob.append(v_SQL,','); --拼接clob

4 dbms_lob.substr(v_SQL); --截取clob，不传参数就是全部读取

5 dbms_lob.freetemporary(v_SQL); --释放clob

查询结果如下：

四、在存储过程中使用CLOB类型实例

需求：以开发的存储过程为例，需要循环遍历时间范围拼接sql，将时间日期按列反转(pivot)，如果时间太长(1年以上)sql语句(varchar2)就会超出范围报错，这时候就需要使用CLOB来存储拼接的sql。

PROCEDUREP_AND_CPT_RATIOOTH_APP_BAK2_N(

V_APPIDSIN VARCHAR2,

V_TYPEIN VARCHAR2,

V_CHANNELIN VARCHAR2,

V_TABLEIN VARCHAR2,

V_STARTIN VARCHAR2,

V_ENDIN VARCHAR2,

RESULT OUT mycursor

)ISV_SQL CLOB;

V_SQLWHEREVARCHAR2(32767) default '';

V_SQLWHERE_CHANNELVARCHAR2(32767) default '';

V_SQL_DATES CLOB;

V_Sdate DATE;

V_Edate DATE;

V_TABLE_DATEVARCHAR2(50);

V_TABLE_TYPEVARCHAR2(50);

V_START_DATEVARCHAR2(50);

V_END_DATEVARCHAR2(50);

V_DAYVARCHAR2(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;

dbms_lob.createtemporary(V_SQL,true);--创建一个临时lob

dbms_lob.createtemporary(V_SQL_DATES,true);--创建一个临时lob

IF V_APPIDS is NOT NULL THENV_SQLWHERE := 'AND t.appid in ('||V_APPIDS||')';END IF;IF V_CHANNEL IS NOT NULL THENV_SQLWHERE_CHANNEL := 'AND t.channel ='''||V_CHANNEL||'''';END IF;IF V_TABLE_DATE = 'MON' THENV_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 THENdbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str

END IF;

v_sdate := add_months(v_sdate,1);ENDLOOP;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' THENV_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 THENdbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str

END IF;

v_sdate := v_sdate+1;ENDLOOP;

ELSIF SUBSTR(V_TYPE,0,1)='w' THEN

select to_char(V_Sdate,'d') INTO V_DAY fromdual;IF V_DAY!=2 THENV_Sdate:=V_Sdate-7;END IF;

V_START_DATE := to_char(v_sdate, 'yyyymmdd');WHILE (v_sdate <=v_edate) LOOPselect to_char(V_Sdate,'d') INTO V_DAY fromdual;IF V_DAY=2 THENdbms_lob.append(v_SQL_DATES,to_char(v_sdate,'yyyymmdd'));--把临时字符串付给v_str

IF V_Edate-v_sdate >7 THENdbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str

END IF;END IF;

v_sdate := v_sdate+1;ENDLOOP;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 FORv_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 booleansave(Article article){boolean result = true;

Connection conn=ConnectionUntils.getInstance();

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(1,article.getName());

pst.setString(2,article.getAge());

pst.executeUpdate();

pst=conn.prepareStatement(sqlClob);

pst.setInt(1, article.getId());

rs=pst.executeQuery();

CLOB clob= null;if(rs.next()){try{

clob= (CLOB) rs.getClob(1);

writer= clob.getCharacterOutputStream(); //拿到clob的字符输入流

writer.write(article.getContent());

writer.flush();

writer.close();

}catch(IOException e) {

e.printStackTrace();

}

conn.commit();

}catch(SQLException e) {

result= false;try{

conn.rollback();//当commit或者rollback后会自动释放该列的锁定

} catch(SQLException e1) {

e1.printStackTrace();

}

e.printStackTrace();

}finally{

conn.setAutoCommit(true);//还原

ConnectionUntils.close(rs, pst, conn);

}returnresult;

}

update操作，update时候主要利用PreparedStatement的setClob方法:

public booleanupdate(String name,String content){int result = 0;

Connection conn=ConnectionUntils.getInstance();

String sql= "update temp set temp_clob=? where name=?";

PreparedStatement pst=null;try{

CLOB clob= oracle.sql.CLOB.createTemporary(conn, false,oracle.sql.CLOB.DURATION_SESSION);

clob.setString(1L, content);

pst=conn.prepareStatement(sql);

pst.setClob(1, clob);

pst.setString(2,name);

result=pst.executeUpdate();

}catch(SQLException e) {

e.printStackTrace();

}finally{

ConnectionUntils.close(null, pst, conn);

}if(result==0)return false;return true;

}

查询就主要是从结果集ResultSet中定位到对应的字段后，往外读：

publicArticle select(String name){

Article article= newArticle();

Connection conn=ConnectionUntils.getInstance();

String sql= "select name,age,temp_clog from temp where name = ?";

PreparedStatement pst=null;

ResultSet rs= null;try{

pst=conn.prepareStatement(sql);

pst.setInt(1,id);

rs=pst.executeQuery();

StringBuilder builder= newStringBuilder();if(rs.next()){

Clob clob= rs.getClob("temp_clog");

Reader rd=clob.getCharacterStream();char [] str = new char[12];while(rd.read(str) != -1) {

builder.append(newString(str));

}

article.setContent(builder.toString());

article.setName(rs.getString("name"));

article.setAge(rs.getInt("age"));

}

}catch(SQLException e) {

e.printStackTrace();

}catch(IOException e) {

e.printStackTrace();

}finally{

ConnectionUntils.close(rs, pst, conn);

}returnarticle;

}

(二)、Hibernate、Mybatis框架操作

这个因为框架都封装集成好了，所以我们使用的时候直接配置变量的类型为CLOB就可以。

譬如：Mybatis

貌似Hibernate5内部做了对应的处理，可以直接当string类型一样处理即可。

六、总结

虽然CLOB能解决VARCHAR2字符大小的限制，但是我们的DBA们都不建议用这些来处理，可能效率问题吧，大的文件应该放在服务器上，然后Database中存响应地址即可。

if ( U.read(me.Blog) ) $("#推荐").click();if ( U.copy(me.Blog) ) $("#u Blog").console("原文链接：http://www.cnblogs.com/Grand-Jon/p/7389427.html ");else me.Fuck(U);

weixin_39854951

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
clob类型字段最大存储长度_Oracle的CLOB大数据字段类型

一、Oracle中的varchar2类型我们在Oracle数据库存储的字符数据一般是用VARCHAR2。VARCHAR2既分PL/SQL Data Types中的变量类型，也分Oracle Database中的字段类型，不同场景的最大长度不同。在Oracle Database中，VARCHAR2 字段类型，最大值为4000；PL/SQL中 VARCHAR2 变量类型，最大字节长度为32767。当 ...
复制链接

扫一扫

clob类型字段最大存储长度_Oracle的CLOB大数据字段类型

“相关推荐”对你有帮助么？