查看: 330|回复: 0

[Oracle数据库] Oracle的CLOB大数据字段类型操作方法

发表于 2017-10-10 08:00:04
尚学堂AD

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

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

(二)、增删改查

先看一下使用普通方式操作CLOB类型:

  1. SELECT t.name, t.temp_clob FROM temp t; --普通查询 INSERT INTO temp t VALUES ('Grand.Jon', 22, '加入博客园的第一天');
复制代码

  查询因为不是varchar2类型,所以普通查询看不到CLOB类型的字段内容,结果如下

而普通插入操作也会因为Oracle的隐式转换,默认把字符串转换成varchar2类型,一旦字符串内容超过varchar2的最大限度就会报会报ora-01704(字符串太长)错误。

正确操作

  1. --使用PL/SQL语法,采取绑定变量的方式解决,而不是直接拼接SQL
  2. DECLARE
  3. V_LANG CLOB := '待插入的海量字符串';
  4. V_UPDATE CLOB := '更新的海量字符串';
  5. BEGIN
  6. INSERT INTO temp t VALUES ('Grand.Jon', 22, V_LANG); --增加
  7. UPDATE temp t SET t.temp_clob = V_UPDATE WHERE rownum = 1; --修改
  8. SELECT t.NAME, dbms_lob.substr(t.temp_clob) FROM TEMP t; --查询  将CLOB转成字符类型
  9. DELETE temp t WHERE rownum = 1; --按列删除  
  10. COMMIT;
  11. END;
  12. /
复制代码

对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。

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

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

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

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

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

  1. public boolean save(Article article){
  2. boolean result = true;
  3. Connection conn = ConnectionUntils.getInstance();
  4. String sql = "insert into temp values(?,?,empty_clob())";
  5. //锁住该列,防止并发写入时候该字段同时被多次写入造成错误
  6. String sqlClob = "select temp_clob from temp t where t.name=? for update";
  7. PreparedStatement pst =null;
  8. ResultSet rs = null;
  9. Writer writer = null;
  10. try {
  11. conn.setAutoCommit(false);//设置不自动提交,开启事务
  12. pst = conn.prepareStatement(sql);
  13. pst.setString(1,article.getName());
  14. pst.setString(2,article.getAge());
  15. pst.executeUpdate();
  16. pst= conn.prepareStatement(sqlClob);
  17. pst.setInt(1, article.getId());
  18. rs = pst.executeQuery();
  19. CLOB clob = null;
  20. if(rs.next()){
  21. try {
  22. clob = (CLOB) rs.getClob(1);
  23. writer = clob.getCharacterOutputStream(); //拿到clob的字符输入流
  24. writer.write(article.getContent());
  25. writer.flush();
  26. writer.close();
  27. } catch (IOException e) {
  28. e.printStackTrace();
  29. }
  30. }
  31. conn.commit();
  32. } catch (SQLException e) {
  33. result = false;
  34. try {
  35. conn.rollback();//当commit或者rollback后会自动释放该列的锁定
  36. } catch (SQLException e1) {
  37. e1.printStackTrace();
  38. }
  39. e.printStackTrace();
  40. } finally {
  41. conn.setAutoCommit(true);//还原
  42. ConnectionUntils.close(rs, pst, conn);
  43. }
  44. return result;
  45. }
复制代码

update操作,update时候主要利用PreparedStatement的setClob方法:

  1. public boolean update(String name,String content){
  2. int result = 0;
  3. Connection conn = ConnectionUntils.getInstance();
  4. String sql = "update temp set temp_clob=? where name=?";
  5. PreparedStatement pst =null;
  6. try {
  7. CLOB clob = oracle.sql.CLOB.createTemporary(conn, false,oracle.sql.CLOB.DURATION_SESSION);
  8. clob.setString(1L, content);
  9. pst = conn.prepareStatement(sql);
  10. pst.setClob(1, clob);
  11. pst.setString(2,name);
  12. result = pst.executeUpdate();
  13. } catch (SQLException e) {
  14. e.printStackTrace();
  15. }finally{
  16. ConnectionUntils.close(null, pst, conn);
  17. }
  18. if(result==0)
  19. return false;
  20. return true;
  21. }
复制代码

查询就主要是从结果集ResultSet中定位到对应的字段后,往外读:

  1. public Article select(String name){
  2. Article article = new Article();
  3. Connection conn = ConnectionUntils.getInstance();
  4. String sql = "select name,age,temp_clog from temp where name = ?";
  5. PreparedStatement pst =null;
  6. ResultSet rs = null;
  7. try {
  8. pst = conn.prepareStatement(sql);
  9. pst.setInt(1,id);
  10. rs = pst.executeQuery();
  11. StringBuilder builder = new StringBuilder();
  12. if(rs.next()){
  13. Clob clob = rs.getClob("temp_clog");
  14. Reader rd = clob.getCharacterStream();
  15. char [] str = new char[12];
  16. while(rd.read(str) != -1) {
  17. builder.append(new String(str));
  18. }
  19. article.setContent(builder.toString());
  20. article.setName(rs.getString("name"));
  21. article.setAge(rs.getInt("age"));
  22. }
  23. } catch (SQLException e) {
  24. e.printStackTrace();
  25. } catch (IOException e) {
  26. e.printStackTrace();
  27. }finally{
  28. ConnectionUntils.close(rs, pst, conn);
  29. }
  30. return article;
  31. }
复制代码

(二)、Hibernate、Mybatis框架操作

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

  譬如:Mybatis

  1. <result column="temp_clob" property="content" jdbcType="Clob" typeHandler="org.apache.ibatis.type.ClobTypeHandler"/>
复制代码

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

总结

以上所述是小编给大家介绍的Oracle的CLOB大数据字段类型操作方法,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对程序员之家网站的支持!



回复

使用道具 举报