[转载]ORACLE大数据类型的操作之CLOB
<p>信息来源: it168</p><p><strong>1. 如何创建带有CLOB类型字段的表?<br /></strong></p><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="3"><div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> TEST2 (AAA CLOB);</span></div>
</div></pre></div><p /><div id="4"><div id="14"><br /><br /></div><strong> 2. 如何对带有CLOB类型字段的表增加记录?</strong><br /></div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="5">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #0000ff">INSERT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INTO</span><span style="COLOR: #000000"> TEST2 </span><span style="COLOR: #0000ff">VALUES</span><span style="COLOR: #000000">(</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">CCBZZPCCBZZP</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);</span></div>
</div></pre></div><p /><div id="6"><div id="14"><br /><br /></div> <strong>3. 如何SELECT带有CLOB类型字段的表?</strong><br /></div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="7">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> TEST2;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> TEST2 </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"> DBMS_LOB.INSTR(TEST2.AAA,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">B</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">,</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">)</span><span style="COLOR: #808080">></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><p /><div id="8"><div id="14"><br /><br /></div> <strong>4. 如何对带有CLOB类型字段的表更换表空间?</strong><br /></div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="9">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #0000ff">ALTER</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> TEST2 MOVE TABLESPACE USERS;</span></div>
</div></pre></div><p /><div id="10"><div id="14"><br /><br /></div> <strong>5. 如何EXP带有CLOB类型字段的表?</strong><br /></div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="11">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #ff00ff">EXP</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">USER</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">PASSWORD </span><span style="COLOR: #0000ff">FILE</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">A.DMP TABLES</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">(TEST2);</span></div>
</div></pre></div><p /><div id="12"><div id="14"><br /><br /></div> <strong>6. 如何IMP带有CLOB类型字段的表?</strong><br /></div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="13">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">IMP </span><span style="COLOR: #ff00ff">USER</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">PASSWORD </span><span style="COLOR: #0000ff">FILE</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">A.DMP </span><span style="COLOR: #0000ff">FULL</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">Y;</span></div></div></pre></div><p><strong>7. 从哪个版本开始支持CLOB,BLOB等大字段?<br /></strong><br /> 以上测试环境为ORACLE92<br /> <br /> SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 9月 4 12:02:00 2003<br /> <br /> Copyright ? 1982, 2002, Oracle Corporation. All rights reserved.<br /> <br /> <br /> 联机到: <br /> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production<br /> With the Partitioning, OLAP and Oracle Data Mining options<br /> JServer Release 9.2.0.1.0 - Production<br /> <br /></p><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="2">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DROP</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> TEST2;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">DROP</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> TEST2
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> ERROR 在行 </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">:
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> ORA</span><span style="COLOR: #808080">-</span><span style="FONT-WEIGHT: bold; COLOR: #800000">00942</span><span style="COLOR: #000000">: 表格或视观表不存在
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> TEST2 (AAA CLOB);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> 已建立表格.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INSERT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INTO</span><span style="COLOR: #000000"> TEST2 </span><span style="COLOR: #0000ff">VALUES</span><span style="COLOR: #000000">(</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">CCBZZPCCBZZP</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> 已建立 </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"> 个数据列.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> TEST2;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> AAA
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">------------------------------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000"> CCBZZPCCBZZP
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> TEST2 </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"> DBMS_LOB.INSTR(TEST2.AAA,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">B</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">,</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">)</span><span style="COLOR: #808080">></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> AAA
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">------------------------------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000"> CCBZZPCCBZZP
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">ALTER</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> TEST2 MOVE TABLESPACE USERS;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> 已更改表格.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> d:</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">exp</span><span style="COLOR: #000000"> test</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">test </span><span style="COLOR: #0000ff">file</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">a.dmp tables</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">(test2)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> ......
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> Export done </span><span style="COLOR: #808080">in</span><span style="COLOR: #000000"> ZHT16BIG5 </span><span style="FONT-WEIGHT: bold; COLOR: #000000">character</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">and</span><span style="COLOR: #000000"> ZHT16BIG5 </span><span style="FONT-WEIGHT: bold; COLOR: #000000">NCHAR</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">character</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> About </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #000000"> export specified tables via Conventional Path ...
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> . . exporting </span><span style="COLOR: #0000ff">table</span><span style="COLOR: #000000"> TEST2 </span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> rows exported
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> Export terminated successfully without warnings.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">drop</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">table</span><span style="COLOR: #000000"> test2;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> 已删除表格.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> d:</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"> imp test</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">test </span><span style="COLOR: #0000ff">file</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">a.dmp tables</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">(test2);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> ......
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> import done </span><span style="COLOR: #808080">in</span><span style="COLOR: #000000"> ZHT16BIG5 </span><span style="FONT-WEIGHT: bold; COLOR: #000000">character</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">and</span><span style="COLOR: #000000"> ZHT16BIG5 </span><span style="FONT-WEIGHT: bold; COLOR: #000000">NCHAR</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">character</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> . importing TEST</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">s objects into TEST
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> . . importing table "TEST2" 0 rows imported
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> Import terminated successfully without warnings.</span></div>
</div></pre></div>
页:
[1]