更改生产数据库中的列数据类型
我正在寻找更改填充表中列的数据类型的最佳方法。 Oracle 只允许更改具有空值的列中的数据类型。
到目前为止,我的解决方案是一个 PLSQL 语句,它将要修改的列的数据存储在集合中,更改表,然后迭代集合,通过转换的数据类型恢复原始数据。
-- Before: my_table ( id NUMBER, my_value VARCHAR2(255))
-- After: my_table (id NUMBER, my_value NUMBER)
DECLARE
TYPE record_type IS RECORD ( id NUMBER, my_value VARCHAR2(255));
TYPE nested_type IS TABLE OF record_type;
foo nested_type;
BEGIN
SELECT id, my_value BULK COLLECT INTO foo FROM my_table;
UPDATE my_table SET my_value = NULL;
EXECUTE IMMEDIATE 'ALTER TABLE my_table MODIFY my_value NUMBER';
FOR i IN foo.FIRST .. foo.LAST
LOOP
UPDATE my_table
SET = TO_NUMBER(foo(i).my_value)
WHERE my_table.id = foo(i).id;
END LOOP;
END;
/
我正在寻找一种更有经验的方法来做到这一点。
I'm looking for the best way to change a data type of a column in a populated table. Oracle only allows changing of data type in colums with null values.
My solution, so far, is a PLSQL statement which stores the data of the column to be modified in a collection, alters the table and then iterates over the collection, restoring the original data with data type converted.
-- Before: my_table ( id NUMBER, my_value VARCHAR2(255))
-- After: my_table (id NUMBER, my_value NUMBER)
DECLARE
TYPE record_type IS RECORD ( id NUMBER, my_value VARCHAR2(255));
TYPE nested_type IS TABLE OF record_type;
foo nested_type;
BEGIN
SELECT id, my_value BULK COLLECT INTO foo FROM my_table;
UPDATE my_table SET my_value = NULL;
EXECUTE IMMEDIATE 'ALTER TABLE my_table MODIFY my_value NUMBER';
FOR i IN foo.FIRST .. foo.LAST
LOOP
UPDATE my_table
SET = TO_NUMBER(foo(i).my_value)
WHERE my_table.id = foo(i).id;
END LOOP;
END;
/
I'm looking for a more experienced way to do that.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
解决方案是错误的。 alter table 语句执行隐式提交。所以解决方案有以下问题:
相反你应该看看在oracle在线重新定义。
The solution is wrong. The alter table statement does an implicit commit. So the solution has the following problems:
Instead you should have a look at oracle online redefinition.
你的解决方案对我来说看起来有点危险。将值加载到集合中并随后从表中删除它们意味着这些值现在仅在内存中可用。如果出现问题,他们就会迷失。
正确的过程是:
Your solution looks a bit dangerous to me. Loading the values into a collection and subsequently deleting them fom the table means that these values are now only available in memory. If something goes wrong they are lost.
The proper procedure is: