Oracle中CLOB大數據字段類型操作的示例分析

這篇文章主要為大家展示了“Oracle中CLOB大數據字段類型操作的示例分析”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“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 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"/>

　　貌似Hibernate5內部做了對應的處理，可以直接當string類型一樣處理即可。

以上是“Oracle中CLOB大數據字段類型操作的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！