一、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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
-- Create tablecreate 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類型：
|
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
2
3
4
5
6
7
8
9
10
11
12
|
--使用PL/SQL語法，采取綁定變量的方式解決，而不是直接拼接SQLDECLARE 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 方法總結
|
1
2
3
4
5
|
dbms_lob.createtemporary(V_SQL,true); --創建一個臨時clob，用來存儲拼接的sqldbms_lob.write(v_SQL,'寫入信息'); --寫入操作dbms_lob.append(v_SQL,','); --拼接clobdbms_lob.substr(v_SQL); --截取clob，不傳參數就是全部讀取dbms_lob.freetemporary(v_SQL); --釋放clob |
查詢結果如下：
四、在存儲過程中使用CLOB類型實例
需求：以開發的存儲過程為例，需要循環遍歷時間范圍拼接sql，將時間日期按列反轉（pivot），如果時間太長（1年以上）sql語句（varchar2）就會超出范圍報錯，這時候就需要使用CLOB來存儲拼接的sql。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
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) ISV_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將待插入字符串寫入進去。
重點：這兩步操作要放在同一個事務里面。具體增加的方法如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
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方法:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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中定位到對應的字段后，往外讀：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
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
|
1
|
<result column="temp_clob" property="content" jdbcType="Clob" typeHandler="org.apache.ibatis.type.ClobTypeHandler"/> |
貌似Hibernate5內部做了對應的處理，可以直接當string類型一樣處理即可。
總結
以上所述是小編給大家介紹的Oracle的CLOB大數據字段類型操作方法，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對服務器之家網站的支持！
