想写一个存储过程,修改库中指定表指定字段(该字段为Date类型)类型为varchar2(20),
我的做法是:新增一列(名字与原列名不一样),拷贝数据,删除原来列,修改新列列名为原列名;
实现这个做法的存储过程如下,但是创建的时候编译出错,
各位高手帮忙看看错在哪里?CREATE OR REPLACE PROCEDURE UP_AlterDateColumn
(tName varchar2,
cName varchar2
)
AS
cName_temp varchar2;
v_sql varchar2(3000);
begin
cName_temp := cName || '_temp';
begin
--增加一列
v_sql := 'alter table ' || tName || ' add ' || cName_temp || ' varchar2(20)';
execute immediate v_sql;
--Copy数据
v_sql := 'update ' || tName || ' set ' || cName_temp || ' = ' || cName;
execute immediate v_sql;
--删除列
v_sql := 'alter table ' || tName || ' drop column ' || cName;
execute immediate v_sql;
--修改列名
v_sql := 'alter table ' || tName || ' rename column ' || cName_temp || ' to ' || cName;
execute immediate v_sql;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
end;
end UP_AlterDateColumn;
我的做法是:新增一列(名字与原列名不一样),拷贝数据,删除原来列,修改新列列名为原列名;
实现这个做法的存储过程如下,但是创建的时候编译出错,
各位高手帮忙看看错在哪里?CREATE OR REPLACE PROCEDURE UP_AlterDateColumn
(tName varchar2,
cName varchar2
)
AS
cName_temp varchar2;
v_sql varchar2(3000);
begin
cName_temp := cName || '_temp';
begin
--增加一列
v_sql := 'alter table ' || tName || ' add ' || cName_temp || ' varchar2(20)';
execute immediate v_sql;
--Copy数据
v_sql := 'update ' || tName || ' set ' || cName_temp || ' = ' || cName;
execute immediate v_sql;
--删除列
v_sql := 'alter table ' || tName || ' drop column ' || cName;
execute immediate v_sql;
--修改列名
v_sql := 'alter table ' || tName || ' rename column ' || cName_temp || ' to ' || cName;
execute immediate v_sql;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
end;
end UP_AlterDateColumn;
cName_temp varchar2;
后面没定义长度2、¦ ¦ 注意半角全角
. ( * @ % & = - + ; < / > at
in is mod remainder not rem <an exponent (**)> <> or != or ~=
>= <= <> and or like between || multiset member SUBMULTISET_
行:12
文本:v_sql := 'alter table ' | | tName | | ' add ' | | cName_temp | | ' varchar2(20)';所有的” | | “ 引号中为你的命令都多了空格
--------------------------------CREATE OR REPLACE PROCEDURE UP_AlterDateColumn
(tName varchar2,
cName varchar2
)
AS
cName_temp varchar2(20);
v_sql varchar2(3000);
begin
cName_temp := cName || '_temp';
begin
--增加一列
v_sql := 'alter table '|| tName || ' add ' || cName_temp || ' varchar2(20)';
execute immediate v_sql;
--Copy数据
v_sql := 'update ' || tName || ' set ' || cName_temp || ' = ' || cName;
execute immediate v_sql;
--删除列
v_sql := 'alter table ' || tName || ' drop column ' || cName;
execute immediate v_sql;
--修改列名
v_sql := 'alter table ' || tName || ' rename column ' || cName_temp || ' to ' || cName;
execute immediate v_sql;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
end;
end UP_AlterDateColumn;
-----------------------------------------------编译成功