时间戳

首页 > 解决方案 > 用于自动更改列类型的 PLSQL 过程

sql - 用于自动更改列类型的 PLSQL 过程

问题描述

让我们假设下表：

drop table test1;
create table test1(
    A number(10)
);

insert into test1 values (1);
insert into test1 values (10);

如您所见，表 TEST1 已填充。我需要做的是将A列的类型更改为varchar2。由于此列具有值，我们不能只使用以下代码：

alter table test1 modify A varchar2(10);

所以我写了存储过程：

  1. 将 A 列重命名为 A1 ->
  2. 然后添加名为 A 类型的新列 varchar2 ->
  3. 然后用 A1 列中的值更新 A 列 ->
  4. 并最终丢弃旧列 A1。

运行此过程的代码如下：

create or replace procedure change_col_type_to_varchar2(p_tab in varchar2, p_col in varchar2)
is 
v_string clob;
cursor cur is
select column_name
from all_tab_columns 
where table_name = upper(p_tab) and
column_name in (select regexp_substr(p_col,'[^,]+', 1, level) from dual
                 connect by regexp_substr(p_col, '[^,]+', 1, level) is not null);
begin
    for i in cur loop
        v_string := 'alter table ' || p_tab || ' rename column ' || i.column_name || ' to ' || i.column_name || '1' || ';';
        dbms_lob.append(v_string,''||chr(10)||'');
        dbms_lob.append(v_string, 'alter table ' || p_tab || ' add ' || i.column_name || ' varchar2(10);');
        dbms_lob.append(v_string,''||chr(10)||'');
        dbms_lob.append(v_string, 'update ' || p_tab || ' set ' || i.column_name || ' = ' || i.column_name || '1' || ';');
        dbms_lob.append(v_string,''||chr(10)||'');
        dbms_lob.append(v_string, 'alter table ' || p_tab || ' drop column ' || i.column_name || '1' || ';');
        EXECUTE IMMEDIATE v_string;
        DBMS_OUTPUT.PUT_LINE(v_string);
        v_string := NULL;
    end loop;
end;

我正在尝试将此过程应用于 TEST1：

begin
    change_col_type_to_varchar2('TEST1', 'A');
end;

并得到错误：

Error report -
ORA-23290: This operation may not be combined with any other operation
ORA-06512: at "YAVORSKYIY_DM.CHANGE_COL_TYPE_TO_VARCHAR2", line 19
ORA-06512: at "YAVORSKYIY_DM.CHANGE_COL_TYPE_TO_VARCHAR2", line 19
ORA-06512: at line 2
23290. 00000 -  "This operation may not be combined with any other operation"
*Cause:    ALTER TABLE RENAME COLUMN/CONSTRAINT operation was given in
           conjunction with another ALTER TBALE Operation. This is not
           allowed.
*Action:   Ensure that RENAME COLUMN/CONSTRAINT is the only operation
           specified in the ALTER TABLE.

但只需输入：

alter table test1 rename column A to A1;
alter table test1 add A varchar2(100);
update test1 set A = A1;
alter table test1 drop column A1;

完美运行。有人对如何克服这个问题有任何想法吗？

感谢你的帮助。

标签： sqloracleplsql

解决方案

好吧，单独执行每个语句，而不是连接它们。而且您不需要 LOB，每个 varchar2 就足够了。

推荐阅读