共計 7657 個字符，預計需要花費 20 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關 Oracle 中如何使用 CLOB 大數據字段類型，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

一、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 方法總結

dbms_lob.createtemporary(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; 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

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

關于 Oracle 中如何使用 CLOB 大數據字段類型就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。