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
