Oracle的CLOB大数据字段类型

逃离我推掉我的手 2022-05-17 03:39 269阅读 0赞

#### 说明：最近有个需求需要考虑Oracle数据库的大数据字段，搜集了一下相关资料，感觉这篇文章写得很棒，转载一下大家一起学习。原文地址：[https://www.cnblogs.com/Grand-Jon/p/7389427.html][https_www.cnblogs.com_Grand-Jon_p_7389427.html] ####

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

复制代码  
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;

dbms_lob.createtemporary(V_SQL,true);--创建一个临时lob
    dbms_lob.createtemporary(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 = 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);
        }
        return result;
    }

复制代码

update操作，update时候主要利用PreparedStatement的setClob方法:  
复制代码  
public boolean update(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中定位到对应的字段后，往外读：  
复制代码  
public Article select(String name)\{  
Article article = new Article();  
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 = new StringBuilder();  
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(new String(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);  
\}  
return article;  
\}  
复制代码  
（二）、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);

[https_www.cnblogs.com_Grand-Jon_p_7389427.html]: https://www.cnblogs.com/Grand-Jon/p/7389427.html