[[202358]]

一、Oracle中的varchar2類(lèi)型

1. 我們?cè)贠racle數(shù)據(jù)庫(kù)存儲(chǔ)的字符數(shù)據(jù)一般是用VARCHAR2。VARCHAR2既分PL/SQL Data Types中的變量類(lèi)型，也分Oracle Database中的字段類(lèi)型，不同場(chǎng)景的***長(zhǎng)度不同。
2. 在Oracle Database中，VARCHAR2 字段類(lèi)型，***值為4000;PL/SQL中 VARCHAR2 變量類(lèi)型，***字節(jié)長(zhǎng)度為32767。
3. 當(dāng) VARCHAR2 容納不下我們需要存儲(chǔ)的信息時(shí)，就出來(lái)的Oracle的大數(shù)據(jù)類(lèi)型LOB( Large Object，大型對(duì)象)。

二、Oarcle中的LOB類(lèi)型

1. 在Oracle中，LOB(Large Object，大型對(duì)象)類(lèi)型的字段現(xiàn)在用得越來(lái)越多了。因?yàn)檫@種類(lèi)型的字段，容量大(最多能容納4GB的數(shù)據(jù))，且一個(gè)表中可以有多個(gè)這種類(lèi)型的字段，很靈活，適用于數(shù)據(jù)量非常大的業(yè)務(wù)領(lǐng)域(如圖象、檔案等)。
2. LOB類(lèi)型分為BLOB和CLOB兩種：BLOB即二進(jìn)制大型對(duì)象(Binary Large Object)，適用于存貯非文本的字節(jié)流數(shù)據(jù)(如程序、圖象、影音等)。
3. 而CLOB，即字符型大型對(duì)象(Character Large Object)，則與字符集相關(guān)，適于存貯文本型的數(shù)據(jù)(如歷史檔案、大部頭著作等)。

三、DB中使用CLOB類(lèi)型字段

(一)、創(chuàng)建表(使用sql或者直接在PL/SQL客戶(hù)端創(chuàng)建)，字段類(lèi)型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類(lèi)型：

SELECT t.name, t.temp_clob FROM temp t; --普通查詢(xún) INSERT INTO temp t VALUES ('Grand.Jon', 22, '加入博客園的***天');

查詢(xún)因?yàn)椴皇莢archar2類(lèi)型，所以普通查詢(xún)看不到CLOB類(lèi)型的字段內(nèi)容，結(jié)果如下

而普通插入操作也會(huì)因?yàn)镺racle的隱式轉(zhuǎn)換，默認(rèn)把字符串轉(zhuǎn)換成varchar2類(lèi)型，一旦字符串內(nèi)容超過(guò)varchar2的***限度就會(huì)報(bào)會(huì)報(bào)ora-01704(字符串太長(zhǎng))錯(cuò)誤。

正確操作

--使用PL/SQL語(yǔ)法，采取綁定變量的方式解決，而不是直接拼接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;    --查詢(xún)　　將CLOB轉(zhuǎn)成字符類(lèi)型 
 
  DELETE temp t WHERE rownum = 1;                             --按列刪除　　 
 
  COMMIT; 
 
END; 
/ 

對(duì)CLOB的操作我們?cè)诖鎯?chǔ)過(guò)程中基本上使用 dbms_lob 中 substr , append , write 等方法。

dbms_lob 方法總結(jié)

dbms_lob.createtemporary(V_SQL,true);     --創(chuàng)建一個(gè)臨時(shí)clob，用來(lái)存儲(chǔ)拼接的sql 
dbms_lob.write(v_SQL,'寫(xiě)入信息');          --寫(xiě)入操作 
dbms_lob.append(v_SQL,',');               --拼接clob 
dbms_lob.substr(v_SQL);                   --截取clob，不傳參數(shù)就是全部讀取 
dbms_lob.freetemporary(v_SQL);            --釋放clob 

查詢(xún)結(jié)果如下：

四、在存儲(chǔ)過(guò)程中使用CLOB類(lèi)型實(shí)例

需求：以開(kāi)發(fā)的存儲(chǔ)過(guò)程為例，需要循環(huán)遍歷時(shí)間范圍拼接sql，將時(shí)間日期按列反轉(zhuǎn)(pivot)，如果時(shí)間太長(zhǎng)(1年以上)sql語(yǔ)句(varchar2)就會(huì)超出范圍報(bào)錯(cuò)，這時(shí)候就需要使用CLOB來(lái)存儲(chǔ)拼接的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);--創(chuàng)建一個(gè)臨時(shí)lob 
    dbms_lob.createtemporary(V_SQL_DATES,true);--創(chuàng)建一個(gè)臨時(shí)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'));--把臨時(shí)字符串付給v_str 
        IF v_sdate != v_edate THEN 
              dbms_lob.append(v_SQL_DATES,',');--把臨時(shí)字符串付給v_str 
        END IF; 
        v_sdate := add_months(v_sdate,1); 
     
        END LOOP; 
    ELSE  --周和日 類(lèi)型 都是 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'));--把臨時(shí)字符串付給v_str 
            IF v_sdate != v_edate THEN 
                  dbms_lob.append(v_SQL_DATES,',');--把臨時(shí)字符串付給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'));--把臨時(shí)字符串付給v_str 
                 IF V_Edate-v_sdate >7 THEN 
                   dbms_lob.append(v_SQL_DATES,',');--把臨時(shí)字符串付給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); 
 
 
--記錄操作日志及錯(cuò)誤日志 
END;  

五、使用Java開(kāi)發(fā)操作CLOB字段

(一)、原生JDBC處理CLOB類(lèi)型

• 增加，一般會(huì)插入一個(gè)空的clob到數(shù)據(jù)庫(kù)對(duì)應(yīng)的字段,然后鎖定該列，用Write將待插入字符串寫(xiě)入進(jìn)去。

重點(diǎn)：這兩步操作要放在同一個(gè)事務(wù)里面。具體增加的方法如下:

public boolean save(Article article){ 
        boolean result = true; 
        Connection conn = ConnectionUntils.getInstance(); 
        String sql = "insert into temp values(?,?,empty_clob())"; 
        //鎖住該列，防止并發(fā)寫(xiě)入時(shí)候該字段同時(shí)被多次寫(xiě)入造成錯(cuò)誤 
        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);//設(shè)置不自動(dòng)提交，開(kāi)啟事務(wù) 
            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();//當(dāng)commit或者rollback后會(huì)自動(dòng)釋放該列的鎖定 
            } catch (SQLException e1) { 
                e1.printStackTrace(); 
            } 
            e.printStackTrace(); 
        } finally { 
            conn.setAutoCommit(true);//還原 
            ConnectionUntils.close(rs, pst, conn); 
        } 
        return result; 
    }  

• update操作，update時(shí)候主要利用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; 
    }  

• 查詢(xún)就主要是從結(jié)果集ResultSet中定位到對(duì)應(yīng)的字段后，往外讀：

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框架操作

• 這個(gè)因?yàn)榭蚣芏挤庋b集成好了，所以我們使用的時(shí)候直接配置變量的類(lèi)型為CLOB就可以。

譬如：Mybatis

<result column="temp_clob" property="content" jdbcType="Clob" typeHandler="org.apache.ibatis.type.ClobTypeHandler"/> 

貌似Hibernate5內(nèi)部做了對(duì)應(yīng)的處理，可以直接當(dāng)string類(lèi)型一樣處理即可。

六、總結(jié)

雖然CLOB能解決VARCHAR2字符大小的限制，但是我們的DBA們都不建議用這些來(lái)處理，可能效率問(wèn)題吧，大的文件應(yīng)該放在服務(wù)器上，然后Database中存響應(yīng)地址即可。