将NVARCHAR2转换为VARCHAR2: declare v_username varchar2(12) ; v_nm_login nvarchar2(12); begin select utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(v_nm_login)) into v_username from dual; end;
将VARCHAR2转换为NVARCHAR2: declare v_username varchar2(12) := 'pavan408'; v_nm_login nvarchar2(12); begin select utl_raw.cast_to_nvarchar2(utl_raw.cast_to_raw(v_username)) into v_nm_login from dual; end;
楼主是希望以nvarchar2形式出现在查询结果里,还是希望变更表中的字段类型
修改字段类型,需要先将该字段清空 如果需要保存数据,可以先建一个临时的字段存储这些数据,然后删除要修改的列,再重新创建 alter table TABLENAME add TEMP nvarchar2(20);--临时字段 update TABLENAME set TEMP=cast(COL1 as nvarchar2(20));--赋转换后的值 alter table TABLENAME drop column COL1;--删除字段 alter table TABLENAME add column COL1 nvarchar2(20);--重建 update TABLENAME set COL1=TEMP;--赋值 alter table TABLENAME drop column TEMP;--临时字段的使命完成了
1楼方法可行。 UTL_RAW.CAST_TO_VARCHAR2UTL_RAW.CAST_TO_VARCHAR2 converts a raw value into a value of data type VARCHAR2 with the same number of data bytes. The result is treated as if it were composed of single 8-bit bytes, not characters. Multibyte character boundaries are ignored. The data is not modified in any way, it is only changed to data type VARCHAR2.Syntaxresult := UTL_RAW.CAST_TO_VARCHAR2(input); where: result is the output value of the function. It is data type VARCHAR2. The value is null if input is null. input is the input value of datatype RAW to convert to datatype VARCHAR2.
declare
v_username varchar2(12) ;
v_nm_login nvarchar2(12);
begin
select utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(v_nm_login))
into v_username
from dual;
end;
将VARCHAR2转换为NVARCHAR2:
declare
v_username varchar2(12) := 'pavan408';
v_nm_login nvarchar2(12);
begin
select utl_raw.cast_to_nvarchar2(utl_raw.cast_to_raw(v_username))
into v_nm_login
from dual;
end;
如果需要保存数据,可以先建一个临时的字段存储这些数据,然后删除要修改的列,再重新创建
alter table TABLENAME add TEMP nvarchar2(20);--临时字段
update TABLENAME set TEMP=cast(COL1 as nvarchar2(20));--赋转换后的值
alter table TABLENAME drop column COL1;--删除字段
alter table TABLENAME add column COL1 nvarchar2(20);--重建
update TABLENAME set COL1=TEMP;--赋值
alter table TABLENAME drop column TEMP;--临时字段的使命完成了
好像不能,只能对应表一个字段一个字段的改。
UTL_RAW.CAST_TO_VARCHAR2UTL_RAW.CAST_TO_VARCHAR2 converts a raw value into a value of data type VARCHAR2 with the same number of data bytes. The result is treated as if it were composed of single 8-bit bytes, not characters. Multibyte character boundaries are ignored. The data is not modified in any way, it is only changed to data type VARCHAR2.Syntaxresult := UTL_RAW.CAST_TO_VARCHAR2(input);
where:
result is the output value of the function. It is data type VARCHAR2. The value is null if input is null.
input is the input value of datatype RAW to convert to datatype VARCHAR2.