大家在应用ORACLE的时候, 可能经常会用到对大数据类型的操作, 在这里我简单的总结一下, 希望对大家能有所帮助! 也欢迎大家一起来探讨, 以便共同进步, 共同发展!
本文对ORACLE的高手来说是不用看的.
1. 如何创建带有CLOB类型字段的表?
CREATE TABLE TEST2 (AAA CLOB);
2. 如何对带有CLOB类型字段的表增加记录?
INSERT INTO TEST2 VALUES('CCBZZPCCBZZP');
3. 如何SELECT带有CLOB类型字段的表?
SELECT * FROM TEST2;
SELECT * FROM TEST2 WHERE DBMS_LOB.INSTR(TEST2.AAA,'B',1,1)0;
4. 如何对带有CLOB类型字段的表更换表空间?
ALTER TABLE
TEST2 MOVE TABLESPACE USERS;
5. 如何EXP带有CLOB类型字段的表?
EXP USER/PASSWORD FILE=A.DMP
TABLES=(TEST2);
6. 如何IMP带有CLOB类型字段的表?
IMP USER/PASSWORD FILE=A.DMP FULL=Y;
7. 从哪个版本开始支持CLOB,BLOB等大字段?
8.0
以上测试环境为ORACLE92
SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 9月 4 12:02:00 2003
Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved.
连线到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL DROP TABLE TEST2;
DROP TABLE TEST2
*
ERROR 在行 1:
ORA-00942: 表格或视观表不存在
SQL
CREATE TABLE TEST2 (AAA CLOB);
已建立表格.
SQL INSERT INTO TEST2 VALUES('CCBZZPCCBZZP');
已建立 1 个资料列.
SQL SELECT * FROM TEST2;
AAA
--------------------------------------------------------------------------------
CCBZZPCCBZZP
SQL SELECT * FROM TEST2 WHERE DBMS_LOB.INSTR(TEST2.AAA,'B',1,1)0;
AAA
--------------------------------------------------------------------------------
CCBZZPCCBZZP
SQL ALTER TABLE
TEST2 MOVE TABLESPACE USERS;
已更改表格.
SQL
d: exp test/test file=a.dmp tables=(test2)
......
Export done in ZHT16BIG5 character set and ZHT16BIG5 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table
TEST2
0 rows exported
Export terminated successfully without warnings.
SQL drop table test2;
已删除表格.
d: imp test/test file=a.dmp tables=(test2);
......
import done in ZHT16BIG5 character set and ZHT16BIG5 NCHAR character set
. importing TEST's objects into TEST
. . importing table
"TEST2"
0 rows imported
Import terminated successfully without warnings.
待续...