insert into tbname select * from table1 where....;
能说的具体一些么?
对记录格式进行确认?字段类型相同么?
解决方案 »
- Oracle数据库导入报错 ORA-39083 在线等。。
- About to enable constraints错误
- 更改用户锁定状态不成功
- 表或视图不存在
- 求一SQL语句~急
- 【SQL 效率】选出10000000-10000010行的结果,怎么写?Oracle(Sql Server / Access 如能做到高效也有分)
- 大家帮我看看这段pl/sql中什么地方有错误?很着急!
- 请教:关于利用一个表更新第二个表格的方法问题
- 如何解决Oracle 8i 的SQLPlus Worksheet中简体中文显示为乱码的问题?
- pl/sql数据库怎么向查出来的表中的列添加数据?求解!急!
- 请问各位,我自己用sql语句创建表的时候,自己先创建了个表空间,可用sql语句生成表的时候,它总去系统空间,不去我创建的空间,要怎么表
- 我在SqlServer中一个表有个布尔类型的字段,在中ORACLE中咋办呢?
确认
insert
是不是loop 全部记录,然后在记录里逐个进行检查?脑子里想的是一套,写不出来,咳。帮忙,谢谢!
sql我懂一些。
create table table_added
(tablename varchar2(30) primary key
);create table table_modified
(tablename varchar2(30),
sql_modified varchar2(100)
);CREATE OR REPLACE PROCEDURE wffz_struct
as
cursor c1 is
select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
from user_tab_columns@wffz_new
where substr(TABLE_NAME,1,1) <> 'V'
order by TABLE_NAME,COLUMN_NAME;
ps_TABLE_NAME varchar2(30);
ps_COLUMN_NAME varchar2(30);
ps_DATA_TYPE varchar2(30);
pn_DATA_LENGTH number;
ps_rowid VARCHAR2(30);
ps_temp VARCHAR2(250);
pi_tmp integer;
pi_tmp0 integer;
pi_count integer := 0;
begin
open c1;
fetch c1 into ps_TABLE_NAME,ps_COLUMN_NAME,ps_DATA_TYPE,pn_DATA_LENGTH;
loop
exit when c1%notfound;
select count(*) into pi_tmp from user_tab_columns@wffz_old
where TABLE_NAME = ps_TABLE_NAME;
if pi_tmp = 0 then --新增表
pi_count := pi_count + 1;
begin
insert into table_added values(ps_TABLE_NAME);
commit;
exception when others then
null;
end;
else
select count(*) into pi_tmp from user_tab_columns@wffz_old
where TABLE_NAME = ps_TABLE_NAME and
COLUMN_NAME = ps_COLUMN_NAME;
if pi_tmp = 0 then --新增字段
pi_count := pi_count + 1;
begin
insert into table_modified values(ps_TABLE_NAME,
'alter table '||ps_TABLE_NAME||' add ('||ps_COLUMN_NAME||' '||ps_DATA_TYPE||'('||pn_DATA_LENGTH||'))');
commit;
exception when others then
null;
end;
else
select count(*) into pi_tmp from user_tab_columns@wffz_old
where TABLE_NAME = ps_TABLE_NAME and
COLUMN_NAME = ps_COLUMN_NAME and
DATA_TYPE = ps_DATA_TYPE and
DATA_LENGTH <> pn_DATA_LENGTH;
if pi_tmp > 0 then
pi_count := pi_count + 1;
begin
insert into table_modified values(ps_TABLE_NAME,
'alter table '||ps_TABLE_NAME||' modify ('||ps_COLUMN_NAME||' '||ps_DATA_TYPE||'('||pn_DATA_LENGTH||'))');
commit;
exception when others then
null;
end;
end if;
end if;
end if;
--dbms_output.put_line(ps_TABLE_NAME||':'||ps_COLUMN_NAME||':'||ps_DATA_TYPE||':'||pn_DATA_LENGTH);
/* where rowid = chartorowid(ps_rowid);
exception when others then
end ;
*/
fetch c1 into ps_TABLE_NAME,ps_COLUMN_NAME,ps_DATA_TYPE,pn_DATA_LENGTH;
end loop;
close c1;
dbms_output.put_line('pi_count:'||pi_count);
end;
/
t_1 tbname%rowtype;
cursor c_1 is select * from tbname;
BEGIN
for tran_container in c_1 loop
if length(tran_container.col1)<>8 then
...end if;
if.... then
...
end if;
end loop;
....
insert into tb2 values..;
END Procdemo;
/