[Oracle]大數據類型的操作之CLOB
2024-08-29 13:50:20
供稿：網友
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等大字段?
以上測試環境為Oracle92
SQL*Plus: Release 9.2.0.1.0 - PRodUCtion on 星期四 9月 4 12:02:00 2003
Copyright ? 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.
