請教:用SQL指令將金額類型字段改為數值型為什麼不行!!!

2004-07-10 04:41:49 278 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

泛微OA如何修改字段类型在泛微OA创建字段时有，有时候想改字段的类型，这时还得把原来的字段删除后再创建。如果涉及到流程，流程里面的字段还得重新放置，烦不胜烦。这时可以修改数据库字段的类型后重启服务即可。先用以下SQL查到表对应的ID： SELECT ID FROM workflow_bill WHERE TABLENAME='formtable_main_286'; 接着用以下SQL查字段 SELECT * FROM workflow_billfield WHERE billid=-286 AND FIELDNAME='szx 阅读详情

alter table abc alter column Amount numeric(18,6)

出錯:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF_ABC_Amount' is dependent on column 'Amount'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN Amount failed because one or more objects access this column.

我想用SQL指令大批量將金額類型改為數值型,怎麼辦?
請高手指點,多謝!

...全文

MySQL浮点数精度问题全解析：为什么金额字段必须用DECIMAL 在数据库开发中，浮点数精度问题常常成为隐蔽的生产事故源头，尤其在金额计算场景下，FLOAT和DOUBLE的二进制存储机制会导致十进制小数无法精确表示，从而产生0.1+0.2=0.30000000000000004的误差。理解IEEE 754标准下FLOAT与DOUBLE的存储差异，是掌握MySQL精度控制的基础。DECIMAL作为定点数类型，通过按十进制位精确存储，彻底规避了浮点舍入误差，成为订单金额、税率等需要精确计算的字段的首选。在实际工程中，从建表字段选型到SUM聚合、等值查询，都需要遵循定点数优先的阅读详情

scott21cn

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

scott21cn 2004-07-12

打赏
举报

終於解決了

SELECT SQLTEXT FROM
(SELECT o.name, c.name as tablename, SQLTEXT = 'alter table ' + c.name + ' drop constraint ' + o.name , sort=1
FROM sysobjects o inner join (SELECT id,name FROM sysobjects) as c on o.parent_obj=c.id WHERE o.id in (SELECT cdefault FROM syscolumns WHERE xtype=60)
UNION
SELECT o.name, c.name as tablename, SQLTEXT = 'GO', Sort=1 FROM sysobjects o inner join (SELECT id,name FROM sysobjects) as c
ON o.parent_obj=c.id WHERE o.id in (SELECT cdefault FROM syscolumns WHERE xtype=60)
UNION
SELECT c.name, o.name as tablename, SQLText='alter table ' + o.name + ' alter column ' + c.name + ' numeric(18,6)' +case when c.cdefault>0 then ' not null' else '' end ,sort=2
FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE (c.xtype = 60 AND o.xtype = 'U')
UNION
SELECT c.name,o.name AS tablename,SQLTEXT = 'GO',Sort=2 FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE (c.xtype = 60 AND o.xtype = 'U')
UNION
SELECT c.name, o.name as tablename, SQLText='alter table ' + o.name + ' add constraint DF_' + o.name + '_' + c.name + ' default 0 for ' + c.name ,sort=3
FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE (c.xtype = 60 AND o.xtype = 'U')
UNION
SELECT c.name,o.name AS tablename,SQLTEXT = 'GO',Sort=3 FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE (c.xtype = 60 AND o.xtype = 'U')
) AS A order by sort,tablename,name

scott21cn 2004-07-12

打赏
举报

那如何才能將syscolumns中金額字段全部刪除
delete from syscolumns where xtype=60為什麼不能刪除?

zjcxc 2004-07-11

打赏
举报

无法从名字上来判断

scott21cn 2004-07-11

打赏
举报

再請教:我如何知道各個字段在sysobjects中所對就的名字,大部分是'DF_'+表名+字段,但有小部分字段所帶表名不一樣!

zjcxc 2004-07-10

打赏
举报

--看看这个是什么? 然后再用对应的语句来删除它.

select xtype from sysobjects where name='DF_ABC_Amount'

scott21cn 2004-07-10

打赏
举报

不行,報錯:
Server: Msg 3728, Level 16, State 1, Line 1
'DF_ABC_Amount' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 1
Could not drop constraint. See previous errors.

zjcxc 2004-07-10

打赏
举报

alter table abc drop constraint DF_ABC_Amount
alter table abc alter column Amount numeric(18,6)
alter table abc add constraint DF_ABC_Amount default(0) for Amount

請教:用SQL指令將金額類型字段改為數值型為什麼不行!!!

相关推荐