Oracle的CLOB大数据字段类型

一、Oracle中的varchar2类型

成都创新互联从2013年成立，先为西平等服务建站，西平等地企业，进行企业商务咨询服务。为西平企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

  1. 我们在Oracle数据库存储的字符数据一般是用VARCHAR2。VARCHAR2既分PL/SQL Data Types中的变量类型，也分Oracle Database中的字段类型，不同场景的***长度不同。
  2. 在Oracle Database中，VARCHAR2 字段类型，***值为4000;PL/SQL中 VARCHAR2 变量类型，***字节长度为32767。
  3. 当 VARCHAR2 容纳不下我们需要存储的信息时，就出来的Oracle的大数据类型LOB( Large Object，大型对象)。

二、Oarcle中的LOB类型

  1. 在Oracle中，LOB(Large Object，大型对象)类型的字段现在用得越来越多了。因为这种类型的字段，容量大(最多能容纳4GB的数据)，且一个表中可以有多个这种类型的字段，很灵活，适用于数据量非常大的业务领域(如图象、档案等)。
  2. LOB类型分为BLOB和CLOB两种：BLOB即二进制大型对象(Binary Large Object)，适用于存贮非文本的字节流数据(如程序、图象、影音等)。
  3. 而CLOB，即字符型大型对象(Character Large Object)，则与字符集相关，适于存贮文本型的数据(如历史档案、大部头著作等)。

三、DB中使用CLOB类型字段

(一)、创建表(使用sql或者直接在PL/SQL客户端创建)，字段类型CLOB

 
 
 
 
      
  
  
  
  1. -- Create table 
    2.   
  
  
  
  2. create table TEMP 
    3.   
  
  
  
  3. ( 
    4.   
  
  
  
  4.   name      VARCHAR2(200), 
    5.   
  
  
  
  5.   age       NUMBER, 
    6.   
  
  
  
  6.   temp_clob CLOB 
    7.   
  
  
  
  7. ) 
    8.   
  
  
  
  8. tablespace INSIGHTDATA3_TS 
    9.   
  
  
  
  9.   pctfree 10 
    10.   
  
  
  
  10.   initrans 1 
    11.   
  
  
  
  11.   maxtrans 255 
    12.   
  
  
  
  12.   storage 
    13.   
  
  
  
  13.   ( 
    14.   
  
  
  
  14.     initial 160K 
    15.   
  
  
  
  15.     next 1M 
    16.   
  
  
  
  16.     minextents 1 
    17.   
  
  
  
  17.     maxextents unlimited 
    18.   
  
  
  
  18.   ); 
    19.

(二)、增删改查

先看一下使用普通方式操作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

 
 
 
 
      
  
  
  
  1. DECLARE  
    2.   
  
  
  
  2.        V_LANG CLOB := '待插入的海量字符串'; 
    3.   
  
  
  
  3.         
    4.   
  
  
  
  4.        V_UPDATE CLOB := '更新的海量字符串'; 
    5.   
  
  
  
  5. BEGIN 
    6.   
  
  
  
  6.  
    7.   
  
  
  
  7.   INSERT INTO temp t VALUES ('Grand.Jon', 22, V_LANG);        --增加 
    8.   
  
  
  
  8.  
    9.   
  
  
  
  9.   UPDATE temp t SET t.temp_clob = V_UPDATE WHERE rownum = 1;  --修改 
    10.   
  
  
  
  10.  
    11.   
  
  
  
  11.   SELECT t.NAME, dbms_lob.substr(t.temp_clob) FROM TEMP t;    --查询　　将CLOB转成字符类型 
    12.   
  
  
  
  12.  
    13.   
  
  
  
  13.   DELETE temp t WHERE rownum = 1;                             --按列删除　　 
    14.   
  
  
  
  14.  
    15.   
  
  
  
  15.   COMMIT; 
    16.   
  
  
  
  16.  
    17.   
  
  
  
  17. END; 
    18.   
  
  
  
  18. / 
    19.

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

dbms_lob 方法总结

 
 
 
 
      
  
  
  
  1. dbms_lob.createtemporary(V_SQL,true);     --创建一个临时clob，用来存储拼接的sql 
    2.   
  
  
  
  2. dbms_lob.write(v_SQL,'写入信息');          --写入操作 
    3.   
  
  
  
  3. dbms_lob.append(v_SQL,',');               --拼接clob 
    4.   
  
  
  
  4. dbms_lob.substr(v_SQL);                   --截取clob，不传参数就是全部读取 
    5.   
  
  
  
  5. dbms_lob.freetemporary(v_SQL);            --释放clob 
    6.

查询结果如下：

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

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

 
 
 
 
      
  
  
  
  1. PROCEDURE P_AND_CPT_RATIOOTH_APP_BAK2_N( 
    2.   
  
  
  
  2.             V_APPIDS IN VARCHAR2, 
    3.   
  
  
  
  3.             V_TYPE IN VARCHAR2, 
    4.   
  
  
  
  4.             V_CHANNEL IN VARCHAR2, 
    5.   
  
  
  
  5.             V_TABLE IN VARCHAR2, 
    6.   
  
  
  
  6.             V_START IN VARCHAR2, 
    7.   
  
  
  
  7.             V_END IN VARCHAR2, 
    8.   
  
  
  
  8.             RESULT OUT mycursor 
    9.   
  
  
  
  9. ) IS 
    10.   
  
  
  
  10.  
    11.   
  
  
  
  11.  
    12.   
  
  
  
  12. V_SQL CLOB; 
    13.   
  
  
  
  13. V_SQLWHERE VARCHAR2(32767) default  ''; 
    14.   
  
  
  
  14. V_SQLWHERE_CHANNEL VARCHAR2(32767) default ''; 
    15.   
  
  
  
  15. V_SQL_DATES  CLOB; 
    16.   
  
  
  
  16. V_Sdate  DATE; 
    17.   
  
  
  
  17. V_Edate  DATE; 
    18.   
  
  
  
  18. V_TABLE_DATE VARCHAR2(50); 
    19.   
  
  
  
  19. V_TABLE_TYPE VARCHAR2(50); 
    20.   
  
  
  
  20. V_START_DATE VARCHAR2(50); 
    21.   
  
  
  
  21. V_END_DATE   VARCHAR2(50); 
    22.   
  
  
  
  22.  
    23.   
  
  
  
  23. V_DAY VARCHAR2(50); 
    24.   
  
  
  
  24.  
    25.   
  
  
  
  25. BEGIN 
    26.   
  
  
  
  26.     select column_name into V_TABLE_DATE from user_tab_columns where table_name=''||V_TABLE||'' and column_id=1; 
    27.   
  
  
  
  27.     select column_name into V_TABLE_TYPE from user_tab_columns where table_name=''||V_TABLE||'' and column_id=5; 
    28.   
  
  
  
  28.    
    29.   
  
  
  
  29.     dbms_lob.createtemporary(V_SQL,true);--创建一个临时lob 
    30.   
  
  
  
  30.     dbms_lob.createtemporary(V_SQL_DATES,true);--创建一个临时lob 
    31.   
  
  
  
  31.      
    32.   
  
  
  
  32.     IF V_APPIDS is NOT NULL THEN 
    33.   
  
  
  
  33.        V_SQLWHERE := 'AND t.appid in ('||V_APPIDS||')'; 
    34.   
  
  
  
  34.     END IF; 
    35.   
  
  
  
  35.  
    36.   
  
  
  
  36.     IF V_CHANNEL IS NOT NULL THEN 
    37.   
  
  
  
  37.        V_SQLWHERE_CHANNEL := 'AND t.channel = '''||V_CHANNEL||''''; 
    38.   
  
  
  
  38.     END IF; 
    39.   
  
  
  
  39.      
    40.   
  
  
  
  40.     IF V_TABLE_DATE = 'MON' THEN 
    41.   
  
  
  
  41.        V_START_DATE := SUBSTR(V_START,0,6); 
    42.   
  
  
  
  42.        V_END_DATE :=  SUBSTR(V_END,0,6); 
    43.   
  
  
  
  43.        v_sdate := to_date(V_START_DATE, 'yyyymm'); 
    44.   
  
  
  
  44.        v_edate := to_date(V_END_DATE, 'yyyymm'); 
    45.   
  
  
  
  45.         
    46.   
  
  
  
  46.         WHILE (v_sdate <= v_edate) LOOP 
    47.   
  
  
  
  47.               dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymm'));--把临时字符串付给v_str 
    48.   
  
  
  
  48.         IF v_sdate != v_edate THEN 
    49.   
  
  
  
  49.               dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str 
    50.   
  
  
  
  50.         END IF; 
    51.   
  
  
  
  51.         v_sdate := add_months(v_sdate,1); 
    52.   
  
  
  
  52.      
    53.   
  
  
  
  53.         END LOOP; 
    54.   
  
  
  
  54.     ELSE  --周和日 类型 都是 DAY 
    55.   
  
  
  
  55.        
    56.   
  
  
  
  56.        v_sdate := to_date(V_START, 'yyyymmdd'); 
    57.   
  
  
  
  57.        v_edate := to_date(V_END, 'yyyymmdd'); 
    58.   
  
  
  
  58.        V_END_DATE :=  V_END; 
    59.   
  
  
  
  59.         
    60.   
  
  
  
  60.        IF SUBSTR(V_TYPE,0,1)='d' THEN 
    61.   
  
  
  
  61.             V_START_DATE := to_char(v_sdate, 'yyyymmdd'); 
    62.   
  
  
  
  62.  
    63.   
  
  
  
  63.             WHILE (v_sdate <= v_edate) LOOP 
    64.   
  
  
  
  64.                   dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymmdd'));--把临时字符串付给v_str 
    65.   
  
  
  
  65.             IF v_sdate != v_edate THEN 
    66.   
  
  
  
  66.                   dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str 
    67.   
  
  
  
  67.             END IF; 
    68.   
  
  
  
  68.             v_sdate := v_sdate+1; 
    69.   
  
  
  
  69.          
    70.   
  
  
  
  70.             END LOOP; 
    71.   
  
  
  
  71.         
    72.   
  
  
  
  72.        ELSIF SUBSTR(V_TYPE,0,1)='w' THEN 
    73.   
  
  
  
  73.  
    74.   
  
  
  
  74.            select to_char(V_Sdate,'d') INTO V_DAY from dual; 
    75.   
  
  
  
  75.             IF V_DAY!=2 THEN 
    76.   
  
  
  
  76.              V_Sdate:=V_Sdate-7; 
    77.   
  
  
  
  77.             END IF; 
    78.   
  
  
  
  78.           V_START_DATE := to_char(v_sdate, 'yyyymmdd'); 
    79.   
  
  
  
  79.  
    80.   
  
  
  
  80.           WHILE (v_sdate <= v_edate) LOOP 
    81.   
  
  
  
  81.                select to_char(V_Sdate,'d') INTO V_DAY from dual; 
    82.   
  
  
  
  82.               IF V_DAY=2 THEN 
    83.   
  
  
  
  83.                  dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymmdd'));--把临时字符串付给v_str 
    84.   
  
  
  
  84.                  IF V_Edate-v_sdate >7 THEN 
    85.   
  
  
  
  85.                    dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str 
    86.   
  
  
  
  86.                  END IF; 
    87.   
  
  
  
  87.  
    88.   
  
  
  
  88.               END IF; 
    89.   
  
  
  
  89.             v_sdate := v_sdate+1; 
    90.   
  
  
  
  90.            END LOOP; 
    91.   
  
  
  
  91.  
    92.   
  
  
  
  92.        END IF; 
    93.   
  
  
  
  93.  
    94.   
  
  
  
  94.     END IF; 
    95.   
  
  
  
  95.      
    96.   
  
  
  
  96.     dbms_lob.append(v_sql,'SELECT * FROM( SELECT * 
    97.   
  
  
  
  97.             FROM '||V_TABLE||' t 
    98.   
  
  
  
  98.            WHERE  
    99.   
  
  
  
  99.                  t.'||V_TABLE_TYPE||' = '''||V_TYPE||''' 
    100.   
  
  
  
  100.              AND t.'||V_TABLE_DATE||' >= '''||V_START_DATE||''' 
    101.   
  
  
  
  101.              AND t.'||V_TABLE_DATE||' <= '''||V_END_DATE||''' 
    102.   
  
  
  
  102.              '||V_SQLWHERE||'          
    103.   
  
  
  
  103.              '||V_SQLWHERE_CHANNEL||' ) t1 
    104.   
  
  
  
  104.   
    105.   
  
  
  
  105.            pivot(sum(MARKETSHARE) 
    106.   
  
  
  
  106.              for '||V_TABLE_DATE||' in('); 
    107.   
  
  
  
  107.               
    108.   
  
  
  
  108.     dbms_lob.append(v_sql,v_SQL_DATES); 
    109.   
  
  
  
  109.     dbms_lob.append(v_sql,'))'); 
    110.   
  
  
  
  110.     dbms_output.put_line(v_sql);       
    111.   
  
  
  
  111.     OPEN result FOR v_sql;    
    112.   
  
  
  
  112.   
    113.   
  
  
  
  113.        dbms_lob.freetemporary(v_sql);--释放lob 
    114.   
  
  
  
  114.        dbms_lob.freetemporary(v_SQL_DATES);--释放lob 
    115.   
  
  
  
  115.        --dbms_output.put_line(V_SQLDATE); 
    116.   
  
  
  
  116.  
    117.   
  
  
  
  117.       -- dbms_output.put_line(v_SQL_DATES); 
    118.   
  
  
  
  118.  
    119.   
  
  
  
  119.  
    120.   
  
  
  
  120. --记录操作日志及错误日志 
    121.   
  
  
  
  121. END;  
    122.

五、使用Java开发操作CLOB字段

(一)、原生JDBC处理CLOB类型

  • 增加，一般会插入一个空的clob到数据库对应的字段,然后锁定该列，用Write将待插入字符串写入进去。

重点：这两步操作要放在同一个事务里面。具体增加的方法如下:

 
 
 
 
      
  
  
  
  1. public boolean save(Article article){ 
    2.   
  
  
  
  2.         boolean result = true; 
    3.   
  
  
  
  3.         Connection conn = ConnectionUntils.getInstance(); 
    4.   
  
  
  
  4.         String sql = "insert into temp values(?,?,empty_clob())"; 
    5.   
  
  
  
  5.         //锁住该列，防止并发写入时候该字段同时被多次写入造成错误 
    6.   
  
  
  
  6.         String sqlClob = "select temp_clob from temp t where t.name=? for update"; 
    7.   
  
  
  
  7.         PreparedStatement pst =null; 
    8.   
  
  
  
  8.         ResultSet rs = null; 
    9.   
  
  
  
  9.         Writer writer = null; 
    10.   
  
  
  
  10.         try { 
    11.   
  
  
  
  11.             conn.setAutoCommit(false);//设置不自动提交，开启事务 
    12.   
  
  
  
  12.             pst = conn.prepareStatement(sql); 
    13.   
  
  
  
  13.             pst.setString(1,article.getName()); 
    14.   
  
  
  
  14.             pst.setString(2,article.getAge()); 
    15.   
  
  
  
  15.             pst.executeUpdate(); 
    16.   
  
  
  
  16.  
    17.   
  
  
  
  17.             pst= conn.prepareStatement(sqlClob); 
    18.   
  
  
  
  18.             pst.setInt(1, article.getId()); 
    19.   
  
  
  
  19.  
    20.   
  
  
  
  20.             rs = pst.executeQuery(); 
    21.   
  
  
  
  21.             CLOB clob = null; 
    22.   
  
  
  
  22.             if(rs.next()){ 
    23.   
  
  
  
  23.                 try { 
    24.   
  
  
  
  24.                     clob = (CLOB) rs.getClob(1); 
    25.   
  
  
  
  25.                     writer = clob.getCharacterOutputStream(); //拿到clob的字符输入流 
    26.   
  
  
  
  26.                     writer.write(article.getContent()); 
    27.   
  
  
  
  27.                     writer.flush(); 
    28.   
  
  
  
  28.                     writer.close(); 
    29.   
  
  
  
  29.                 } catch (IOException e) { 
    30.   
  
  
  
  30.                     e.printStackTrace(); 
    31.   
  
  
  
  31.                 } 
    32.   
  
  
  
  32.             } 
    33.   
  
  
  
  33.             conn.commit(); 
    34.   
  
  
  
  34.         } catch (SQLException e) { 
    35.   
  
  
  
  35.             result = false; 
    36.   
  
  
  
  36.             try { 
    37.   
  
  
  
  37.                 conn.rollback();//当commit或者rollback后会自动释放该列的锁定 
    38.   
  
  
  
  38.             } catch (SQLException e1) { 
    39.   
  
  
  
  39.                 e1.printStackTrace(); 
    40.   
  
  
  
  40.             } 
    41.   
  
  
  
  41.             e.printStackTrace(); 
    42.   
  
  
  
  42.         } finally { 
    43.   
  
  
  
  43.             conn.setAutoCommit(true);//还原 
    44.   
  
  
  
  44.             ConnectionUntils.close(rs, pst, conn); 
    45.   
  
  
  
  45.         } 
    46.   
  
  
  
  46.         return result; 
    47.   
  
  
  
  47.     }  
    48.
  • update操作，update时候主要利用PreparedStatement的setClob方法:
 
 
 
 
      
  
  
  
  1. public boolean update(String name,String content){ 
    2.   
  
  
  
  2.         int result = 0; 
    3.   
  
  
  
  3.         Connection conn = ConnectionUntils.getInstance(); 
    4.   
  
  
  
  4.         String sql = "update temp set temp_clob=? where name=?"; 
    5.   
  
  
  
  5.         PreparedStatement pst =null; 
    6.   
  
  
  
  6.         try { 
    7.   
  
  
  
  7.             CLOB clob   = oracle.sql.CLOB.createTemporary(conn, false,oracle.sql.CLOB.DURATION_SESSION); 
    8.   
  
  
  
  8.             clob.setString(1L, content); 
    9.   
  
  
  
  9.             pst = conn.prepareStatement(sql); 
    10.   
  
  
  
  10.             pst.setClob(1, clob); 
    11.   
  
  
  
  11.             pst.setString(2,name); 
    12.   
  
  
  
  12.             result = pst.executeUpdate(); 
    13.   
  
  
  
  13.         } catch (SQLException e) { 
    14.   
  
  
  
  14.             e.printStackTrace(); 
    15.   
  
  
  
  15.         }finally{ 
    16.   
  
  
  
  16.             ConnectionUntils.close(null, pst, conn); 
    17.   
  
  
  
  17.         } 
    18.   
  
  
  
  18.         if(result==0) 
    19.   
  
  
  
  19.             return false; 
    20.   
  
  
  
  20.         return true; 
    21.   
  
  
  
  21.     }  
    22.
  • 查询就主要是从结果集ResultSet中定位到对应的字段后，往外读：
 
 
 
 
      
  
  
  
  1. public Article select(String name){ 
    2.   
  
  
  
  2.        Article article = new Article(); 
    3.   
  
  
  
  3.        Connection conn = ConnectionUntils.getInstance(); 
    4.   
  
  
  
  4.        String sql = "select name,age,temp_clog from temp where name = ?"; 
    5.   
  
  
  
  5.        PreparedStatement pst =null; 
    6.   
  
  
  
  6.        ResultSet rs = null; 
    7.   
  
  
  
  7.        try { 
    8.   
  
  
  
  8.            pst = conn.prepareStatement(sql); 
    9.   
  
  
  
  9.            pst.setInt(1,id); 
    10.   
  
  
  
  10.            rs = pst.executeQuery(); 
    11.   
  
  
  
  11.            StringBuilder builder = new StringBuilder(); 
    12.   
  
  
  
  12.            if(rs.next()){ 
    13.   
  
  
  
  13.                Clob clob = rs.getClob("temp_clog"); 
    14.   
  
  
  
  14.                Reader rd = clob.getCharacterStream(); 
    15.   
  
  
  
  15.                char [] str = new char[12]; 
    16.   
  
  
  
  16.                while(rd.read(str) != -1) { 
    17.   
  
  
  
  17.                    builder.append(new String(str)); 
    18.   
  
  
  
  18.                } 
    19.   
  
  
  
  19.                article.setContent(builder.toString()); 
    20.   
  
  
  
  20.                article.setName(rs.getString("name")); 
    21.   
  
  
  
  21.                article.setAge(rs.getInt("age")); 
    22.   
  
  
  
  22.            } 
    23.   
  
  
  
  23.        } catch (SQLException e) { 
    24.   
  
  
  
  24.            e.printStackTrace(); 
    25.   
  
  
  
  25.        } catch (IOException e) { 
    26.   
  
  
  
  26.            e.printStackTrace(); 
    27.   
  
  
  
  27.        }finally{ 
    28.   
  
  
  
  28.            ConnectionUntils.close(rs, pst, conn); 
    29.   
  
  
  
  29.        } 
    30.   
  
  
  
  30.        return article; 
    31.   
  
  
  
  31.    } 
    32.

(二)、Hibernate、Mybatis框架操作

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

譬如：Mybatis

 
 
 
 
      
  
  
  
  1.  
    2.

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

六、总结

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

分享文章：Oracle的CLOB大数据字段类型
网站链接：http://www.shufengxianlan.com/qtweb/news18/15918.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

网站导航知识

各行业网站

医药医疗网站建设方案　　 　温江电信机房　　 　内江智速网站建设　　 　网站托管运营　　 　成都网站维护　　 　成都商城网站建设　　 　成都网站建设公司　　 　成都网站建设　　 　四川成都网站设计　　 　响应式建站　　 　成都包装设计公司　　 　企业官网建设　　 　ssl证书申请　　 　名片　　 　手机网站制作设计　　 　IOS开发　　 　四川成都网站制作　　 　网站免费收录　　 　成都网站建设　　 　江油网站建设