oracle 如何在oracle中将number(7,2)更改为varchar？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11177520/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to change number(7,2) to varchar in oracle?
提问by Aamir Rizwan
I'm using Oracle 10g. I want to append # to all values in 'sal' column. To acomplish this first I'm trying to change data type of 'sal' column from numeric to varchar but getting following error
我正在使用 Oracle 10g。我想将 # 附加到“sal”列中的所有值。为了首先实现这一点，我试图将“sal”列的数据类型从数字更改为 varchar，但出现以下错误
What am I doing wrong ?
我究竟做错了什么 ？
回答by vikiiii
You should use modifykeyword instead of your second alter.
您应该使用modify关键字而不是您的第二个alter.
alter table
emp
modify
(
sal varchar2(10)
);
When modifying a tables column datatype , you need to use modifykeyword.
修改表列数据类型时，需要使用modify关键字。
Of course, you must deal with existing column data. When modifying a tables column datatype you may want to export the rows, redefine the table and then re-import you data.
当然，您必须处理现有的列数据。修改表列数据类型时，您可能希望导出行、重新定义表，然后重新导入数据。
In this you would need to follow these steps to alter a column data type:
在这种情况下，您需要按照以下步骤更改列数据类型：
- Create the new column at the end of the table.
- Run an update to populate the new table column
- Drop the old table column
- Re-name the new column to the original column name
- 在表的末尾创建新列。
- 运行更新以填充新表列
- 删除旧表列
- 将新列重命名为原始列名
回答by Hein
It does not seem a smart idea to take a perfectly good number and ruin in for the rest of the user by appending a piece of string. Just add the string on the select directly or through a view?
通过附加一段字符串来取一个完美的数字并破坏其他用户似乎不是一个聪明的主意。直接或通过视图在选择上添加字符串？
Something like:
就像是：
SQL> create view emp_horked
as
select ename, sal, sal || '#' hash, to_char(SAL,'9999.99') || '#' sal_padded
from emp;
View created.
SQL> select * from emp_horked where rownum < 5;
ENAME SAL HASH SAL_PADDE
---------- ---------- ----------------------------------------- ---------
SMITH 800 800# 800.00#
ALLEN 1600 1600# 1600.00#
WARD 1250 1250# 1250.00#
JONES 2975 2975# 2975.00#
More on format modelsfor to_char
更多关于格式的模型为to_char
