求一存储过程实现批量修改表名

sqlserver导入oracle，所有表名都加上了双引号。求一存储过程将双引号去掉。十万火急！

33 个解决方案

#1

能吗，数据类型都不一样啊

#2

应该能吧。就是取出来表名，然后进行字符串加工而已吧。只是自己不是很了解存储过程写法的。

引用 1 楼 yugiant 的回复:

能吗，数据类型都不一样啊

#3

我之前也遇到过smsql 到 oracle 表名多双引号。修改表名的办法我没试过，因为oracle查询这样的表的时候说：表或视图不存在。

我当时用powerdesign生产的sql在oracle执行。具体步骤如下：

一、用PowerDesigner连接MSSQL数据库，反向工程MSSQL数据获得PDM

二、PDM-->产生ORACLE的PDM

三、产生ORACLE的sql

四、在oracle中执行xxx.sql

#4

大哥说的有道理，错误的提示也一样：表或者视图不存在。究其根本原因就是表名加双引号引起的。
但是如何修改呢？

引用 3 楼 mahanso 的回复:

我之前也遇到过smsql 到 oracle 表名多双引号。修改表名的办法我没试过，因为oracle查询这样的表的时候说：表或视图不存在。

我当时用powerdesign生产的sql在oracle执行。具体步骤如下：

一、用PowerDesigner连接MSSQL数据库，反向工程MSSQL数据获得PDM

二、PDM-->产生ORACLE的PDM

三、产生ORACLE的sql
……

#5

CREATE OR REPLACE PROCEDURE update_tablename is
 to_update_tablename varchar2(500);
 old_tablename varchar2(500);
 new_tablename varchar2(500);
 cursor nid is --定义游标，每次取出一个表名称
 select table_name from user_tables;
begin
  open nid;
  fetch nid  into to_update_tablename;
    loop--执行循环
      old_tablename:=to_update_tablename;
      select replace(to_update_tablename,'"')into new_tablename from dual ;
      execute immediate 'alter table '||old_tablename||' rename to '||new_tablename;
      exit when not nid%found;
      fetch nid
      into to_update_tablename;
    end loop;
end update_tablename;

#6

五楼你这语句好像存在问题啊。为什么我一测试的时候提示我一下错误啊？
ora-00942 表或者视图不存在；
ora-06512 在"HTOA.UPDATE_TABLENAME",line 13
ora-06512 line 3
期待高手不吝赐教啊。。。

引用 5 楼 kurorolove 的回复:

CREATE   OR   REPLACE   PROCEDURE   update_tablename   is
  to_update_tablename   varchar2(500);
  old_tablename   varchar2(500);
  new_tablename   varchar2(500);
  cursor   nid   is   --定义游标，每次取出……

#7

楼主先从select table_name from user_tables看看你的表名是否带双引号？

#8

楼主先从select table_name from user_tables看看你的表名是否带双引号？

#9

顶起来

#10

我看了，在检索数据的时候不带双引号的。但是plsql可以看到其实表名都加双引号的。

引用 7 楼 ssqtjffcu1 的回复:

楼主先从select table_name from user_tables看看你的表名是否带双引号？

#11

等待高手帮忙解答啊。问题不是很困难吧！

#12

关注。。。。。

#13

-- 执行如下的语句：（然后将执行的结果再次在 SQL*Plus下去执行)
 select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name);

#14

从SMSQL导到oracle是这样的。

表明有双引号，在sqlplus中查看表明都是小写的。

#15

我当时用powerdesign解决的。

#16

对，确实是这样的。

引用 14 楼 mahanso 的回复:

从SMSQL导到oracle是这样的。

表明有双引号，在sqlplus中查看表明都是小写的。

#17

但是如果就想用存储过程来批量修改表名的话改如何实现呢？

引用 15 楼 mahanso 的回复:

我当时用powerdesign解决的。

#18

大姐，这句放到plsql中执行就可以了？
可以说的详细点么？
小弟基础太差了。不是很理解这个语句可以实现批量修改表名？

引用 13 楼 luoyoumou 的回复:

SQL code
-- 执行如下的语句：（然后将执行的结果再次在 SQL*Plus下去执行)
 select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name);

#19

引用 13 楼 luoyoumou 的回复:

SQL code
-- 执行如下的语句：（然后将执行的结果再次在 SQL*Plus下去执行)
 select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name);

美女方法果然好用，我测试过了，学习下。

select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name);

查出查询结果，把查出结果再次执行一下就ok了。

#20

引用 19 楼 mahanso 的回复:

引用 13 楼 luoyoumou 的回复:
SQL code
-- 执行如下的语句：（然后将执行的结果再次在 SQL*Plus下去执行)
select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name)……

-- 哪有什么美女啊：我是男的，好不好.............

#21

大哥，查询结果我倒是看到了。确实是那些我打算一步一步执行的语句。但是怎么把执行结果在此执行啊？
可以说的详细点么？

引用 19 楼 mahanso 的回复:

引用 13 楼 luoyoumou 的回复:
SQL code
-- 执行如下的语句：（然后将执行的结果再次在 SQL*Plus下去执行)
select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name)……

#22

 参照luoyoumou的 

SPOOL 111.SQL;
select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name);
SPOOL OFF;
@111.SQL;

试试

#23

高手。spool什么东东？这个语句放到pl/sql中都是错误啊？
你试试可以么？

引用 22 楼 wuguanlin 的回复:

参照luoyoumou的 

SQL code
SPOOL 111.SQL;
select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name);
SPOOL OFF;
@111.SQL;

试试……

#24

declare
  v_sql varchar2(1000);
  cursor cur is select 'alter table "'||table_name||'" rename to '||upper(table_name) as sqlstr
              from user_tables where table_name<>upper(table_name);
begin
  for rur in cur loop
    v_sql := rur.sqlstr;
    execute immediate v_sql;
  end loop;
end;
/

#25

哇，高手。实在是高手。
高手一出手，就知有没有啊。搞定了。结贴。
再次谢过luoyoumou大哥啊。

引用 24 楼 luoyoumou 的回复:

SQL code
declare
  v_sql varchar2(1000);
  cursor cur is select 'alter table "'||table_name||'" rename to '||upper(table_name) as sqlstr
              from user_tables where table_name<>upper(tabl……

#26

declare
  v_sql varchar2(1000);
  cursor cur is select 'alter table "'||t1.table_name||'" rename to '||upper(t1.table_name) as sqlstr
              from user_tables t1 
             where table_name<>upper(table_name) 
               and not exists (select 1 from user_tables t2 where t2.table_name=upper(t1.table_name) );
begin
  for rur in cur loop
    v_sql := rur.sqlstr;
    execute immediate v_sql;
  end loop;
end;
/

#27

scott@SZTYORA> create table "ttt" as select * from t;

表已创建。

已用时间:  00: 00: 00.07
scott@SZTYORA> create table ttt as select * from t;

表已创建。

已用时间:  00: 00: 00.06
scott@SZTYORA> commit;

提交完成。

已用时间:  00: 00: 00.01
scott@SZTYORA> select 'alter table "'||t1.table_name||'" rename to '||upper(t1.table_name) as sqlstr
  2                from user_tables t1
  3               where table_name<>upper(table_name)
  4                 and not exists (select 1 from user_tables t2 where t2.table_name=upper(t1.table_name) );

未选定行

已用时间:  00: 00: 00.06
scott@SZTYORA> drop table ttt;

表已删除。

已用时间:  00: 00: 00.01
scott@SZTYORA> commit;

提交完成。

已用时间:  00: 00: 00.00
scott@SZTYORA> select 'alter table "'||t1.table_name||'" rename to '||upper(t1.table_name) as sqlstr
  2                from user_tables t1
  3               where table_name<>upper(table_name)
  4                 and not exists (select 1 from user_tables t2 where t2.table_name=upper(t1.table_name) );

SQLSTR
------------------------------------------------------------------------------------------------------------------------
alter table "ttt" rename to TTT

已用时间:  00: 00: 00.07
scott@SZTYORA> declare
  2    v_sql varchar2(1000);
  3    cursor cur is select 'alter table "'||t1.table_name||'" rename to '||upper(t1.table_name) as sqlstr
  4                from user_tables t1
  5               where table_name<>upper(table_name)
  6                 and not exists (select 1 from user_tables t2 where t2.table_name=upper(t1.table_name) );
  7  begin
  8    for rur in cur loop
  9      v_sql := rur.sqlstr;
 10      execute immediate v_sql;
 11    end loop;
 12  end;
 13  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.39
scott@SZTYORA> desc "ttt";
ERROR:
ORA-04043: 对象 "ttt" 不存在


scott@SZTYORA> desc ttt;
 名称                                                              是否为空? 类型
 ----------------------------------------------------------------- -------- --------------------------------------------
 COL_NAME                                                                   VARCHAR2(20)

scott@SZTYORA>

#28

大哥，这后面的两个语句怎么？
对前面的补充？

引用 27 楼 luoyoumou 的回复:

SQL code
scott@SZTYORA> create table "ttt" as select * from t;

表已创建。

已用时间:  00: 00: 00.07
scott@SZTYORA> create table ttt as select * from t;

表已创建。

已用时间:  00: 00: 00.06
scott@SZTYORA> c……

#29

引用 23 楼 dlut_qimingxing 的回复:

高手。spool什么东东？这个语句放到pl/sql中都是错误啊？
你试试可以么？
引用 22 楼 wuguanlin 的回复:
参照luoyoumou的

SQL code
SPOOL 111.SQL;
select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables……

在sqlplus里执行或者在pl/sql中的command window里执行

#30

引用 28 楼 dlut_qimingxing 的回复:

大哥，这后面的两个语句怎么？
对前面的补充？

引用 27 楼 luoyoumou 的回复:
SQL code
scott@SZTYORA> create table "ttt" as select * from t;

表已创建。

已用时间: 00: 00: 00.07
scott@SZTYORA> create table ttt as select * from t;
……

-- 更完善的版本：比如：数据库中：有一个　表名为: "ttt" (小写) 和 另一个表名为 "TTT" 
SELECT * from ttt; -- 此时不加引号，我们选择的是 表名为 "TTT"
SELECT * from "ttt" -- 此时加引号，我们选择的是 表名为 "ttt";

-- 那么当这两张表同时存在时，我24楼的代码就会出错，提示：对象已经存在，
-- 此时：我26楼的代码不会出错（因为查询过程排除了这种情况)

#31

引用 30 楼 luoyoumou 的回复:

引用 28 楼 dlut_qimingxing 的回复:
大哥，这后面的两个语句怎么？
对前面的补充？

引用 27 楼 luoyoumou 的回复:
SQL code
scott@SZTYORA> create table "ttt" as select * from t;

表已创建。

已用时间: 00: 00: 00.07
scott@SZTYORA> create ……

-- 如果不明白的话，具体操作可看我27楼的测试代码！

#32

明白了，谢谢luoyoumou大哥啊。
真是长见识了。谢谢。

引用 31 楼 luoyoumou 的回复:

引用 30 楼 luoyoumou 的回复:
引用 28 楼 dlut_qimingxing 的回复:
大哥，这后面的两个语句怎么？
对前面的补充？

引用 27 楼 luoyoumou 的回复:
SQL code
scott@SZTYORA> create table "ttt" as select * from t;

表已创建。

已用时间: 00: 00: 00.07……

#33

--SQL SERVER 批量修改数据库表名使用说明：
--下列SQL语句中的'77Ya.Com'为要替换的表名里面的字符，
--'02590.Com'为要替后的表的前缀。
--如果只是想把表名修改为大写，
--可以用upper(name)来替换replace(cast(name as varchar(200)),'77Ya.Com','02590.Com')。
--PS：更改对象名的任一部分都可能会破坏脚本和存储过程。修改表名后存储过程中调用的数据表名别忘记做对应的修改。



declare @oldName varchar(30),
@newName varchar(30)
declare cursor_taname   CURSOR   FOR   
SELECT name,replace(cast(name as varchar(200)),'sys','yt') as newname FROM sysobjects where type='u'
OPEN   cursor_taname   
FETCH   NEXT   FROM   cursor_taname   INTO   @oldname,@newName
WHILE   @@FETCH_STATUS=0      
BEGIN 
EXEC   sp_rename @oldname,@newName
FETCH   NEXT   FROM   cursor_taname   INTO   @oldname,@newName
END      
CLOSE   cursor_taname   
DEALLOCATE   cursor_taname