今天遇到一个问题,某表中的某字段原始类型为varchar2,存的数据为日期,但是是字符串的形式,例:20100101.
由于业务需要,需要把该字段改成date类型,但是表中的数据又不能删,很明显的删除会报错. 于是我尝试了一种方法:
(1)用游标取出所有的数据,放到内存中. (2)使用动态sql删除表中的所有数据,接着使用动态的sql执行
alter table table_name modify column_name date; (3)使用loop fetch into end loop close 等语句把原数据插入了表中. 在尝试之前我觉得这个是会失败的,原因是我之前尝试过使用动态sql创建一张表,然后接着使用非动态sql往表中插入一条
数据,会报table or view has not exists,根据错误我判断oracle服务器是先编译sql语句,再编译动态sql的.
所以这次我认为也会先执行loop end loop中的insert 语句,然后才执行alter table的动态sql,这样就会报错,因为此时类
型是没有改成date的,而插入的是date类型的值.不料却成功了. 代码如下:
declare
cursor cur_test is
select * from t_test_date;
str_dynamic_sql varchar2(4000);
vstr_date01 varchar2(32);
i_total number;
str_column_name varchar2(32);
str_data_type varchar2(32);
begin
open cur_test;
str_dynamic_sql := 'delete from t_test_date';
execute immediate str_dynamic_sql;
str_dynamic_sql := 'alter table t_test_date modify str_date01 date';
execute immediate str_dynamic_sql;
loop
fetch cur_test into vstr_date01;
exit when cur_test%notfound;
insert into t_test_date values(to_date(vstr_date01,'yyyymmdd'));
end loop;
commit;
end;
在写这个讨论贴的时候,我突然领悟了,但是既然写的差不多了,那么就贴出来吧,下面是我的领悟.
oracle服务器是先编译sql再编译动态sql的,上述代码成功的原因是编译sql时,字段、权限、表结构、用户等都是存在的,此时它没有运行整个程序块,而是接着编译动态的sql,发现相关对象也是存在的,于是整个块按顺序执行.
而使用动态sql创建表,然后使用sql插入这种方式,在编译sql时就发现表不存在,所以就直接报错了,没有继续去编译动态sql.错误代码如下:
begin
execute immediate 'create table test_001 (arg01 varchar2(32))';
insert into test_001 values('123');
commit;
end;
之前的结论时:程序块或函数或过程是先执行sql再执行动态sql.
现在的结论是:顺序执行,但是编译是先编译sql,再编译动态sql. 不知道对不对呢?请高手解答.
由于业务需要,需要把该字段改成date类型,但是表中的数据又不能删,很明显的删除会报错. 于是我尝试了一种方法:
(1)用游标取出所有的数据,放到内存中. (2)使用动态sql删除表中的所有数据,接着使用动态的sql执行
alter table table_name modify column_name date; (3)使用loop fetch into end loop close 等语句把原数据插入了表中. 在尝试之前我觉得这个是会失败的,原因是我之前尝试过使用动态sql创建一张表,然后接着使用非动态sql往表中插入一条
数据,会报table or view has not exists,根据错误我判断oracle服务器是先编译sql语句,再编译动态sql的.
所以这次我认为也会先执行loop end loop中的insert 语句,然后才执行alter table的动态sql,这样就会报错,因为此时类
型是没有改成date的,而插入的是date类型的值.不料却成功了. 代码如下:
declare
cursor cur_test is
select * from t_test_date;
str_dynamic_sql varchar2(4000);
vstr_date01 varchar2(32);
i_total number;
str_column_name varchar2(32);
str_data_type varchar2(32);
begin
open cur_test;
str_dynamic_sql := 'delete from t_test_date';
execute immediate str_dynamic_sql;
str_dynamic_sql := 'alter table t_test_date modify str_date01 date';
execute immediate str_dynamic_sql;
loop
fetch cur_test into vstr_date01;
exit when cur_test%notfound;
insert into t_test_date values(to_date(vstr_date01,'yyyymmdd'));
end loop;
commit;
end;
在写这个讨论贴的时候,我突然领悟了,但是既然写的差不多了,那么就贴出来吧,下面是我的领悟.
oracle服务器是先编译sql再编译动态sql的,上述代码成功的原因是编译sql时,字段、权限、表结构、用户等都是存在的,此时它没有运行整个程序块,而是接着编译动态的sql,发现相关对象也是存在的,于是整个块按顺序执行.
而使用动态sql创建表,然后使用sql插入这种方式,在编译sql时就发现表不存在,所以就直接报错了,没有继续去编译动态sql.错误代码如下:
begin
execute immediate 'create table test_001 (arg01 varchar2(32))';
insert into test_001 values('123');
commit;
end;
之前的结论时:程序块或函数或过程是先执行sql再执行动态sql.
现在的结论是:顺序执行,但是编译是先编译sql,再编译动态sql. 不知道对不对呢?请高手解答.
何必要这样呢？
1、直接新建一个表，和你原来表结构一样，只是要将你那个varchar2的日期字段改成date类型
CREATE TABLE temp(......, rq DATE ,....) NOLOGGING;
2、将你原表数据insert到新表temp
INSERT /*+append*/ into temp SELECT ....,To_Date(rq,'yyyymmdd'),....) FROM oldtable --执行插入
3、删除原表
DROP TABLE oldtable ;
4、修改别名
ALTER TABLE temp RENAME TO oldtable
5、重建index (如果原来表有索引)
create index index_name on 。
6、恢复日志记录
alter table oldtable logging
--补充：
当然，上面这6步你可以采用过程来执行
已写入 file afiedt.buf 1 begin
2 execute immediate 'create table test_001 (arg01 varchar2(32))';
3 execute immediate 'insert into test_001 values(''123'')';
4 commit;
5* end;
SQL> /PL/SQL 过程已成功完成。SQL> select * from test_001
2 /ARG01
--------------------------------
123
把数据处理插（to_date处理)以appedn 方式插入表
提交
建索引
--改表名
rename oldtb to oldtb1
rename newtb to oldtb
但是动态执行alter table ,非动态执行insert则不会出现错误的原因.同样是DDL语句嘛.最后我得出的那个结论我想知道根据俩位的理解是不是对的.
恩,是的,不过我最想讨论的是我从那个修改方式中测试出了一些动态SQL与普通SQL执行的一个顺序.