程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle中表列由VARCHAR2類型改成CLOB

Oracle中表列由VARCHAR2類型改成CLOB

編輯：Oracle教程

Oracle中表列由VARCHAR2類型改成CLOB

情景

原來表中的列定義成VARCHAR2類型，眾所周知，VARCHAR2類型最大支持長度為4000。如果由於業務需要，想把此列轉換為CLOB類型，在Oracle中直接通過ALTER語句轉換是行不通的。下面根據具體事例講解在Oracle數據庫中如何把表列由VARCHAR2類型轉換為CLOB類型。

示例准備

1. 新建兩張張表TB_WITHOUT_DATA（此VARCHAR2列不包含數據）和TB_WITH_DATA（此Varchar2列包含數據)

create table TB_WITHOUT_DATA
(
  id NUMBER,
  name VARCHAR2(100),
  description VARCHAR2(2000)
);

create table TB_WITH_DATA
(
  id NUMBER,
  name VARCHAR2(100),
  description VARCHAR2(2000)
);

insert into TB_WITH_DATA VALUES (1,'David Louis','He is capable of resolving such kind of issue');
insert into TB_WITH_DATA VALUES (2,'German Noemi','She is very beatiful and charming');
insert into TB_WITH_DATA VALUES (3,'Oliver Queen','He is main actor in the Green Arrow');
insert into TB_WITH_DATA VALUES (4,'Mark Williams','He plays snooker very well');
insert into TB_WITH_DATA VALUES (5,'Sita Rama Raju Kata','I do not know this guy');
insert into TB_WITH_DATA VALUES (6,'Promethus','This is a very nice movie');
commit;

錯誤方法

ALTER TABLE TB_WITHOUT_DATA MODIFY description clob;

錯誤信息：

SQL Error: ORA-22858: invalid alteration of datatype
22858. 00000 - "invalid alteration of datatype"
*Cause: An attempt was made to modify the column type to object, REF,
nested table, VARRAY or LOB type.
*Action: Create a new column of the desired type and copy the current
column data to the new type using the appropriate type
constructor.

解決方法

方法一：對於此列沒有數據的可通過以下方法修改-首先把該列改成Long類型，然後再改成clob類型

alter table TB_WITHOUT_DATA modify description long;--首先改成Long類型
alter table TB_WITHOUT_DATA modify description clob;--在Long類型的基礎上改成clob類型

注：對於此列已經存在數據的，不能通過此方法，否則會報如下錯誤：

alter table TB_WITH_DATA modify description long;--更改包含數據的列

SQL Error: ORA-01439: column to be modified must be empty to change datatype
01439. 00000 -  "column to be modified must be empty to change datatype"

方法二：此方法適合此列包含數據和此列不包含數據兩種情況

步驟一：把原來表中該列重命名

alter table TB_WITHOUT_DATA rename column description to description_bak;
alter table TB_WITH_DATA rename column description to description_bak;

步驟二：在表中增加該列，並指定改列類型為clob

alter table TB_WITHOUT_DATA add description clob;
alter table TB_WITH_DATA add description clob;

步驟三：對此列包含數據的需要包數據從步驟一重命名列中拷出(對於此列沒有數據的此步驟省略)

update TB_WITH_DATA set description=description_bak;
commit;

步驟四：刪除步驟一中的備份列

alter table TB_WITHOUT_DATA drop column description_bak;
alter table TB_WITH_DATA drop column description_bak;

步驟五：驗證

1) 表結構驗證

DESC TB_WITHOUT_DATA
Name        Null Type          
----------- ---- ------------- 
ID               NUMBER        
NAME             VARCHAR2(100) 
DESCRIPTION      CLOB

DESC TB_WITH_DATA
Name        Null Type          
----------- ---- ------------- 
ID               NUMBER        
NAME             VARCHAR2(100) 
DESCRIPTION      CLOB

2) 數據驗證

select * from TB_WITH_DATA;

        ID NAME                       DESCRIPTION                                     
---------- -------------------------- ------------------------------------------------
         1 David Louis                He is capable of resolving such kind of issue   
         2 German Noemi               She is very beatiful and charming               
         3 Oliver Queen               He is main actor in the Green Arrow             
         4 Mark Williams              He plays snooker very well                      
         5 Sita Rama Raju Kata        I do not know this guy                          
         6 Promethus                  This is a very nice movie                       

 6 rows selected

方法三：此方法適合此列包含數據和此列不包含數據兩種情況

在講解方法三之前，需要包表恢復到准備階段，由於時間關系，直接通過drop然後re-create方法,腳本如下：

drop table TB_WITHOUT_DATA;
drop table TB_WITH_DATA;

create table TB_WITHOUT_DATA
(
  id NUMBER,
  name VARCHAR2(100),
  description VARCHAR2(2000)
);

create table TB_WITH_DATA
(
  id NUMBER,
  name VARCHAR2(100),
  description VARCHAR2(2000)
);

insert into TB_WITH_DATA VALUES (1,'David Louis','He is capable of resolving such kind of issue');
insert into TB_WITH_DATA VALUES (2,'German Noemi','She is very beatiful and charming');
insert into TB_WITH_DATA VALUES (3,'Oliver Queen','He is main actor in the Green Arrow');
insert into TB_WITH_DATA VALUES (4,'Mark Williams','He plays snooker very well');
insert into TB_WITH_DATA VALUES (5,'Sita Rama Raju Kata','I do not know this guy');
insert into TB_WITH_DATA VALUES (6,'Promethus','This is a very nice movie');
commit;

步驟一：重命名兩張表

rename TB_WITHOUT_DATA to TB_WITHOUT_DATA_BAK;
rename TB_WITH_DATA to TB_WITH_DATA_BAK;

步驟二：創建兩張新表（通過以下語句創建兩張表）

create table TB_WITHOUT_DATA
as
select id, name, to_clob(description) description
from TB_WITHOUT_DATA_BAK;

create table TB_WITH_DATA
as
select id, name, to_clob(description) description
from TB_WITH_DATA_BAK;

表結構與數據驗證：

desc TB_WITHOUT_DATA
Name        Null Type          
----------- ---- ------------- 
ID               NUMBER        
NAME             VARCHAR2(100) 
DESCRIPTION      CLOB

desc TB_WITH_DATA
Name        Null Type          
----------- ---- ------------- 
ID               NUMBER        
NAME             VARCHAR2(100) 
DESCRIPTION      CLOB 

select * from TB_WITH_DATA;

select * from TB_WITH_DATA;

        ID NAME                       DESCRIPTION                                     
---------- -------------------------- ------------------------------------------------
         1 David Louis                He is capable of resolving such kind of issue   
         2 German Noemi               She is very beatiful and charming               
         3 Oliver Queen               He is main actor in the Green Arrow             
         4 Mark Williams              He plays snooker very well                      
         5 Sita Rama Raju Kata        I do not know this guy                          
         6 Promethus                  This is a very nice movie                       

 6 rows selected

步驟三：刪除備份表：

DROP TABLE TB_WITHOUT_DATA_BAK;
DROP TABLE TB_WITH_DATA_BAK;

-----------------------------------------------------------------------------------------------------------------------------------------------------------

如果您們在嘗試的過程中遇到什麼問題或者我的代碼有錯誤的地方，請給予指正，非常感謝！

聯系方式：[email protected]

版權＠：轉載請標明出處！