[oracle存储过程]更改字段类型

我会带着你远行 2021-09-28 02:42 371阅读 0赞

一、问题说明：在项目开发过程中，有时需要将多张表做union操作，会发现由于个别表的字段不一致，造成union语句查询报错。

这时有以下的解决方法：

1.较为简单：将少量的不一致字段，使用to\_number、to\_date等方式作下处理。这样能够确保查询操作正常

2.较为复杂：更改少量不一致字段的字段类型，与多数表中的字段类型保持一致

简单的方法就不用说了，下面看下复杂的情况该如何操作。接下来写一个存储过程，更改数据库中表的字段类型（同时要保证数据不能被删除掉，前提：该数据类型转换本身不会丢失精度）：

以mytest表（建表及插入测试数据的语句附在文末）为例，

![2019010921570733.png][]

mytest表中sbirthday原本打算创建成date类型，实际却是varchar2类型；

smoney原本打算创建成number类型，实际却是varchar2类型

二、具体实现

需要分别执行下面3段临时的存储过程，这样就能将sbirthday和smoney字段转换为原有的数据类型

begin
      --创建临时表
      execute immediate
      'create table temp_table(
             sid number,
             sbirthday date,
             smoney number
      )';
      --将已有表的数据备份下
      execute immediate
      'create table mytest_bak as
       select * from mytest';
    end;
    
    begin  
      --把数据插到临时表中
      insert into temp_table(sid, sbirthday, smoney)
      select pe.sid, to_date(pe.sbirthday,'yyyy-mm-dd'), pe.smoney from mytest pe;
      
      --删除原有表的数据
      update mytest set sbirthday = null, smoney = null;
      
      --修改原有表的数据类型
      execute immediate 'alter table mytest modify sbirthday date';
      execute immediate 'alter table mytest modify smoney number';
      
      --修改原有表的数据  
      update mytest pe set (pe.sbirthday, pe.smoney) =
        (select tt.sbirthday, tt.smoney from temp_table tt where tt.sid = pe.sid);
      commit;
    end;
    
    --删之前先找几个数据验证一下
    begin
      --删除临时表
      execute immediate 'drop table temp_table';
      --删除备份表
      execute immediate 'drop table mytest_bak';
    end;

正因为实际操作起来步骤稍显麻烦，大部分情况下不会选择更改字段类型，而是选择直接改sql语句。

现在把这个逻辑抽象成带参的存储过程，这样以后更改字段的数据类型就很方便了。存储过程如下：

create or replace procedure changeDataType
      (tableName varchar2,
      primKeyName varchar2,
      changeFieldName varchar2,
      toType varchar2
      )
    authid current_user as
      /** 将某张表中指定字段更改为特定数据类型
      * 参数：
      * tableName 需要操作的表
      * primKeyName 需要操作的表的主键名
      * changeFieldName 需要操作的字段
      * toType 操作的字段需要转换成的数据类型
      **/
    
      bakFieldSelectStr varchar2(300); --需要转换的字段在插入临时表时的查询字符串
    begin
      --创建临时表
      execute immediate
      'create table temp_table(
             sid number,'
             ||changeFieldName||' '||toType
      ||')';
    
      --把数据插到临时表中
      if toType = 'date' then --日期类型
        bakFieldSelectStr:='to_date(pe.'||changeFieldName||',''yyyy-mm-dd'')';
      else --普通类型
        bakFieldSelectStr:='pe.'||changeFieldName;
      end if;
    
      execute immediate
      'insert into temp_table(sid, '||changeFieldName||')
      select pe.'||primKeyName||','||bakFieldSelectStr||' from '||tableName||' pe';
    
      --删除原有表的数据
      execute immediate
      'update '||tableName||' set '||changeFieldName||' = null';
    
      --修改原有表的数据类型
      execute immediate 'alter table '||tableName||' modify '||changeFieldName||' '||toType;
    
      --修改原有表的数据
      execute immediate
      'update '||tableName||' pe set (pe.'||changeFieldName||') =
        (select tt.'||changeFieldName||' from temp_table tt where tt.'||primKeyName||' = pe.'||primKeyName||')';
    
      commit;
    
      --删除临时表
      execute immediate 'drop table temp_table';
    end;

测试使用，能够正常将字段类型进行转换，并保留数据：

begin
      changeDataType('mytest','sid','sbirthday','date');
      changeDataType('mytest','sid','smoney','number');
    end;

附：建表及插入测试数据的语句

-- Create table
    create table MYTEST
    (
      sid       NUMBER not null,
      sname     VARCHAR2(10) not null,
      ssex      CHAR(2),
      sbirthday VARCHAR2(100),
      smoney    VARCHAR2(100)
    );
    -- Add comments to the columns 
    comment on column MYTEST.sid
      is '主键';
    comment on column MYTEST.sname
      is '姓名';
    comment on column MYTEST.ssex
      is '性别';
    comment on column MYTEST.sbirthday
      is '生日';
    comment on column MYTEST.smoney
      is '金额';
    
    
    --插入测试数据
    insert into mytest (SID, SNAME, SSEX, SBIRTHDAY, SMONEY)
    values (1, '张三', '男', '1991-08-01', '1000');
    
    insert into mytest (SID, SNAME, SSEX, SBIRTHDAY, SMONEY)
    values (2, '李四', '男', '1992-05-01', '2000');
    
    insert into mytest (SID, SNAME, SSEX, SBIRTHDAY, SMONEY)
    values (3, '小红', '女', '1993-03-05', '3000');
    
    commit;

[2019010921570733.png]: /images/20210923/bacee1dd15784efc9d3135cdc317df97.png