--给个例子吧,其实文档上多的是。
--比较表结构的差异
-- BEGIN PL/SQL BLOCK (do not remove this line) --------------------------------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;
/
exec wffz_struct;drop table table_added;
drop table table_modified;create table table_added
(tablename varchar2(30) primary key
);create table table_modified
(tablename varchar2(30),
sql_modified varchar2(100)
);
--比较表结构的差异
-- BEGIN PL/SQL BLOCK (do not remove this line) --------------------------------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;
/
exec wffz_struct;drop table table_added;
drop table table_modified;create table table_added
(tablename varchar2(30) primary key
);create table table_modified
(tablename varchar2(30),
sql_modified varchar2(100)
);
insert into xzx_table(vname) values(vname);
fetch c1 into vname;这个语句吗?
select table_name from sys.user_tables;
查看一下是不是xzx_table表都是大写字母,如果含有小写字母,就用双引号扩起来,例如:
insert into "XZX_Table" (vname) values(vname);
另外,你的插入语句中vname的列和vname的变量同名,你可以将变量的名字改一下试试。
最后,你可以简练一些写法,再加上异常处理,如下:
declare
cursor c1 is select view.name from all_views where rownum<10
order by view_name;
vname1 varchar2(40);
begin
open c1;
loop
fetch c1 into vname1;
if c1%notfound then
exit;
end if;
insert into xzx_table(vname) values(vname1);
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
...
end;
if c1%notfound then
exit;
end if;
改为:
EXIT WHEN ci%NOTFOUND;
if c1%notfound then
exit;
end if;
改为:
EXIT WHEN ci%NOTFOUND;
insert into xzx_table(vname) values(vname);
第一个vname系统会解释成表xzx_table中的一个列,第二个vname才是一个pl/sql变量。如果你表中没有vname这样一个列,那就会出错。如果错误信息是指无法找到xzx_table,那么问题应该出在当前用户或者公共同义词中没有对该表(该表属于其他的用户)。请给出具体的ORA错误代码
PLS-00201:identifier 'XZX_TABLE' must be declared
这个表是存在的.把vname变量改了也不行.
我是在pb里建的表,但是PB和SQLPLUS进的却不是同一个数据库,因此.....
真是不好意思.老糊涂了.哈.