sql - 有没有办法在 Oracle 中将所有 char(1) 列更改为 number(1) 列？

Question

我们使用 char(1)在 Oracle 中存储 bool 值的列，其中值是 "1"或 "0"来代表真假。但是，我想将这些更改为 number(1)列，其中值是 1或 0 .如果表中已经存在值，您似乎无法更改此列的类型。所以我必须做这样的事情:

ALTER TABLE TPM_TRAININGPLANSOLUTIONS DROP CONSTRAINT SYS_C0010178;
ALTER TABLE TPM_TRAININGPLANSOLUTIONS RENAME COLUMN ISMARKERCOMPLETION TO ISMARKER_CHAR;
ALTER TABLE TPM_TRAININGPLANSOLUTIONS ADD (ISMARKERCOMPLETION NUMBER(1) NOT NULL);
UPDATE TPM_TRAININGPLANSOLUTIONS SET ISMARKERCOMPLETION = ISMARKER_CHAR; -- This takes about 10 min
ALTER TABLE TPM_TRAININGPLANSOLUTIONS DROP COLUMN ISMARKER_CHAR; -- Also very slow

ALTER TABLE TPMDBO.TPM_TRAININGPLANSOLUTIONS
    ADD ( CONSTRAINT SYS_C0010178
    CHECK (ISMARKERCOMPLETION in (0,1))
    NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE );

但是，我们的数据库中有几十个这样的列。有没有更快的方法来做到这一点？

Answer 1

没有简单的方法来做你正在做的事情；您可以使用系统表为您生成 DDL，但您仍然需要知道列名。但是，有更简单的方法可以将其映射到数字，而无需更改任何内容。

首先，您需要确保您的列实际上是您分配的值。

ALTER TABLE TPMDBO.TPM_TRAININGPLANSOLUTIONS
    ADD ( CONSTRAINT chk_TPM_TRAININGPLANSOLUTIONS_IMC
    CHECK (ISMARKERCOMPLETION in ('0','1'))
    NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE );

然后您可以创建一个 virtual column在 table 上:

alter table tpm_trainingplansolutions add ( 
   ismarkercompletion_num generated always as (to_number(ismarkercompletion)) virtual
   );

或者在 table 顶部的 View ，其中 casts该特定列到 NUMBER。

两者都可能会导致工作量减少，但现在您需要知道列名。做你正在做的事情并确保你的数据库是正确的。

顺便说一句，您正在创建一个前缀为 SYS_ 的约束。 ，请不要这样做...用有意义的名称创建约束，不要试图模仿 Oracle。

如果要更改所有 CHAR(1) 列，可以使用 USER_TAB_COLUMNS识别具有此特征的列并使用它为您生成 DDL，例如

select 'ALTER TABLE '
        || table_name 
        || ' RENAME COLUMN '
        || column_name 
        || ' TO ' || substr(column_name, 1, length(column_name) - 5) 
        || '_CHAR ;'
  from user_tab_columns
 where data_type = 'CHAR'
   and data_length = 1

您可能必须使用 USER_CONS_COLUMNS还有……