mysql long varchar2_long类型字段转换成varchar2类型

參考文档： How to Convert a Long to Varchar2 (文档 ID 228532.1)

/*long类型字段转换成varchar2类型*/

--建表

create table dxmy_long(lon long,var varchar2(100 ));

--插数

insert into dxmy_long values( '丹心','明月' );

commit;

--查看

declare

v1 varchar2(100 );

begin

select lon into v1 from dxmy_long;

dbms_output.put_line(v1);

end;

丹心

--将lon数据存到var中

declare

v1 varchar2(100 );

begin

select lon into v1 from dxmy_long;

dbms_output.put_line(v1);

update dxmy_long set var = v1;

commit;

end;

--查证

select * from dxmy_long;

LON VAR

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

丹心 丹心

--假设有多行须要转换

insert into dxmy_long values( '丹心','明月' );

insert into dxmy_long values( '丹心','明月' );

insert into dxmy_long values( '丹心','明月' );

insert into dxmy_long values( '丹心','明月' );

insert into dxmy_long values( '丹心','明月' );

insert into dxmy_long values( '丹心','明月' );

insert into dxmy_long values( '丹心','明月' );

insert into dxmy_long values( '丹心','明月' );

insert into dxmy_long values( '丹心','明月' );

commit;

declare

v1 varchar2( 100);

CURSOR c_Select IS select lon from dxmy_long;

begin

Open c_Select;

LOOP

Fetch c_Select into v1;

Exit when c_Select%NOTFOUND;

update dxmy_long set var = v1;

END LOOP;

commit;

Close c_Select;

END;

--查证

SQL> select * from dxmy_long;

LON VAR

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

丹心 丹心

丹心 丹心

丹心 丹心

丹心 丹心

丹心 丹心

丹心 丹心

丹心 丹心

丹心 丹心

丹心 丹心

丹心 丹心

10 rows selected