Sqlserver2005迁移至Oracle系列之一：生成表(下)
2009-03-03 13:11:23 来源：WEB开发网 --列尾部
begin
fetch next from cr_table_column into @colname,@typename,@length,@precision,@scale,@isnull,@default,@isidentity,@seed,@incre
if @@fetch_status = 0
set @col_define = @col_define + ','
else if @exiests_constraints = 1
set @col_define = @col_define + ','
insert into ##table(ident,ctext) select 3,@col_define
end
end
close cr_table_column
deallocate cr_table_column
end
--主键、唯一键约束定义
if @exiests_constraints = 1
begin
declare cr_constraints_name cursor for
select distinct keyname,[type],isclusted
from #table_constraints
where (tabname = @tablename)
open cr_constraints_name
fetch next from cr_constraints_name into @keyname,@type,@isclusted
if @@fetch_status = 0
--定义约束
while @@fetch_status = 0
begin
--约束头部
set @constraint_define = 'CONSTRAINT ' + quotename(left(upper(@keyname),30),'"')
+ case @type
when 'PK' then ' PRIMARY KEY '
when 'UQ' then ' UNIQUE '
else ' '
end
+ '('
--约束列
begin
set @col_list = ''
declare cr_constraints cursor for
select colname,isdesc
from #table_constraints
where (tabname = @tablename) and (keyname = @keyname)
open cr_constraints
fetch next from cr_constraints into @colname,@isdesc
while @@fetch_status = 0
begin
set @col_list = @col_list + quotename(upper(@colname),'"')
--是否有下一个约束，决定是否加上‘,’
fetch next from cr_constraints into @colname,@isdesc
if @@fetch_status = 0
set @col_list = @col_list + ','
end
close cr_constraints
deallocate cr_constraints
end
--约束尾部
set @constraint_define = @constraint_define + @col_list + ')'
--约束列游标下移
fetch next from cr_constraints_name into @keyname,@type,@isclusted
--是否有下一个约束，决定是否加上‘,’
if @@fetch_status = 0
set @constraint_define = @constraint_define + ','
insert into ##table(ident,ctext) select 3,@constraint_define
end
--表约束游标下移
close cr_constraints_name
deallocate cr_constraints_name
end
--表尾部
begin
declare @partitioncolumn sysname
set @partitioncolumn = null
if @tablename in ('plat_uidlist','plat_feeinfo','plat_uidinfo','plat_reginfo','plat_freeuid','plat_linkgroup','plat_linkman','plat_reglog','plat_reglog_2007','plat_reglog_2008' )
set @partitioncolumn = '"UID"'
else if @tablename in ('plat_phoneinfo','plat_phonelimited','plat_phoneseglimited','plat_freephone','plat_areaphonelist')
set @partitioncolumn = 'PHONE'
else if @tablename in ('sms_recdmsgret','sms_recdmsgret_2007','sms_recdmsgret_2008','sms_sentmsgret','sms_sentmsgret_2007','sms_sentmsgret_2008')
set @partitioncolumn = 'CHANNO'
if (not @partitioncolumn is null) and (@isenterprise = 1)
begin
begin
insert into ##table(ident,ctext) select 2,')'
insert into ##table(ident,ctext) select 2,'PARTITION BY HASH(' + @partitioncolumn + ')'
insert into ##table(ident,ctext) select 2,'PARTITIONS 32 STORE IN'
declare @partition varchar(2000)
declare @i int
declare @si varchar(100)
set @partition = ''
set @i = 1
while @i < 32
begin
set @si = 'USER_' + REPLICATE('0',2-LEN(CAST(@i as varchar(2)))) + CAST(@i as varchar(2))
set @i = @i + 1
set @partition = @partition + @si + ','
if @i = 32
begin
set @si = 'USER_' + REPLICATE('0',2-LEN(CAST(@i as varchar(2)))) + CAST(@i as varchar(2))
set @partition = @partition + @si
end
end
insert into ##table(ident,ctext) select 3,'(' + @partition + ')'
insert into ##table(ident,ctext) select 2,''';'
end
end
else
insert into ##table(ident,ctext) select 2,')'';'
insert into ##table(ident,ctext) select -1,''
end
end
end
if (@issequence = 1)
begin
begin
set @sequence_is = 0
select @sequence_is = isidentity,@sequence_seed = cast(seed as bigint),@sequence_incre = cast(incre as bigint)
from #table_define
where (tabname = @tablename) and (isidentity = 1)
end
if @sequence_is = 1
begin
set @sequence = left(upper(@tablename + '_ID'),30)
--先删除（清理）
if @onlydelseq = 1
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '删除该表的序列'
else
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '创建该表的序列'
insert into ##table(ident,ctext) select 1,'SELECT COUNT(1) INTO VA_EXIST_SEQUENCE FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = ''' + upper(@user) + ''' AND SEQUENCE_NAME = ' + quotename(@sequence,'''') + ';'
insert into ##table(ident,ctext) select 1,'IF VA_EXIST_SEQUENCE > 0 THEN '
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE '
insert into ##table(ident,ctext) select 2,'''' + 'DROP SEQUENCE ' + quotename(upper(@user),'"') + '.' + quotename(@sequence,'"') + ''';'
insert into ##table(ident,ctext) select 2,'VA_EXIST_SEQUENCE := 0;'
insert into ##table(ident,ctext) select 1,'END IF;'
insert into ##table(ident,ctext) select -1,''
--创建序列
if @onlydelseq = 0
begin
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE '
insert into ##table(ident,ctext) select 2,'''' + 'CREATE SEQUENCE ' + quotename(upper(@user),'"') + '.' + quotename(@sequence,'"')
insert into ##table(ident,ctext) select 4,'START WITH 100000000' -- + cast(@sequence_seed as varchar(50)) --ver1.1
insert into ##table(ident,ctext) select 4,'INCREMENT BY ' + cast(@sequence_incre as varchar(50))
insert into ##table(ident,ctext) select 4,'NOMAXVALUE'
insert into ##table(ident,ctext) select 4,'NOCYCLE'
insert into ##table(ident,ctext) select 4,'NOCACHE'
insert into ##table(ident,ctext) select 4,'NOORDER'
insert into ##table(ident,ctext) select 2,''';'
insert into ##table(ident,ctext) select -1,''
end
end
end
--创建索引
if @isindex = 1
begin
declare cr_index_name cursor for
select distinct indname,isclusted,isunique,ignore_dup_key
from #table_indexes
where tabname = @tablename
open cr_index_name
fetch next from cr_index_name into @indname,@isclusted,@isunique,@ignore_dup_key
while @@fetch_status = 0
begin
--删除索引部分
if @onlydelindex = 1
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '删除该表的索引'
else
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '创建该表的索引'
insert into ##table(ident,ctext) select 1,'SELECT COUNT(1) INTO VA_EXIST_INDEX'
insert into ##table(ident,ctext) select 1,'FROM DBA_INDEXES A LEFT JOIN ALL_CONSTRAINTS B ON A.INDEX_NAME = B.CONSTRAINT_NAME'
insert into ##table(ident,ctext) select 1,'WHERE (A.OWNER = '''+ upper(@user) + ''') AND (B.CONSTRAINT_NAME IS NULL) AND A.INDEX_NAME = ''' + left(upper(@indname),30) + ''';'
insert into ##table(ident,ctext) select 1,'IF VA_EXIST_INDEX > 0 THEN '
insert into ##table(ident,ctext) select 2,'EXECUTE IMMEDIATE ''DROP INDEX ' + quotename(upper(@user),'"') + '.' + quotename(left(upper(@indname),30),'"') + ''';'
insert into ##table(ident,ctext) select 2,'VA_EXIST_INDEX := 0;'
insert into ##table(ident,ctext) select 1,'END IF;'
insert into ##table(ident,ctext) select -1,''
--创建索引部分，如果不只是删除索引
if @onlydelindex = 0
begin
--index头部
set @index_define = 'CREATE '
+ case @isunique
when 1 then 'UNIQUE '
else ''
end
+ 'INDEX ' + quotename(upper(@user),'"') + '.' + quotename(left(upper(@indname),30),'"')
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE '
insert into ##table(ident,ctext) select 2,'''' + @index_define
--index列明细
begin
set @index_define = 'ON ' + quotename(upper(@user),'"') + '.' + quotename(upper(@tablename),'"') + '('
set @col_list = ''
declare cr_index cursor for
select colname,isdesc
from #table_indexes
where (tabname = @tablename) and (indname = @indname)
open cr_index
fetch next from cr_index into @colname,@isdesc
while @@fetch_status = 0
begin
set @col_list = @col_list + quotename(upper(@colname),'"')
fetch next from cr_index into @colname,@isdesc
if @@fetch_status = 0
set @col_list = @col_list + ' ' + @isdesc + ','
end
close cr_index
deallocate cr_index
end
--index尾部
set @index_define = @index_define + @col_list + ')'
if @tablename in( 'plat_uidlist','plat_feeinfo','plat_uidinfo','plat_reginfo','plat_freeuid','plat_linkgroup','plat_linkman','plat_reglog','plat_reglog_2007','plat_reglog_2008',
'plat_phoneinfo','plat_phonelimited','plat_phoneseglimited','plat_freephone','plat_areaphonelist',
'sms_recdmsgret','sms_recdmsgret_2007','sms_recdmsgret_2008','sms_sentmsgret','sms_sentmsgret_2007','sms_sentmsgret_2008'
)
if @isenterprise = 1
insert into ##table(ident,ctext) select 3,@index_define + 'ONLINE NOLOGGING LOCAL'';'
else
insert into ##table(ident,ctext) select 3,@index_define + 'NOLOGGING'';'
else
if @isenterprise = 1
insert into ##table(ident,ctext) select 3,@index_define + 'ONLINE NOLOGGING'';'
else
insert into ##table(ident,ctext) select 3,@index_define + 'NOLOGGING'';'
end
fetch next from cr_index_name into @indname,@isclusted,@isunique,@ignore_dup_key
insert into ##table(ident,ctext) select 1,''
end
close cr_index_name
deallocate cr_index_name
end
--创建表的public同义词，去掉‘_’字符前面的部分
if @issynonym = 1
begin
set @pos = charindex('_',@tablename)
if @pos > 0 and @pos < len(@tablename)
begin
set @synonyms = upper(substring(@tablename,@pos +1,256))
--删除已存在的同义词
if @onlydelsyn = 1
begin
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '删除该表的同义词'
insert into ##table(ident,ctext) select 1,'SELECT COUNT(1) INTO VA_EXIST_SYNONYMS FROM ALL_SYNONYMS'
insert into ##table(ident,ctext) select 1,'WHERE OWNER = '''+ upper(@user) + ''' AND SYNONYM_NAME = ''' + @synonyms + ''';'
insert into ##table(ident,ctext) select 1,'IF VA_EXIST_SYNONYMS > 0 THEN '
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE ''DROP SYNONYM ' + quotename(upper(@user),'"') + '.' + quotename(@synonyms,'"') + ''';'
insert into ##table(ident,ctext) select 2,'VA_EXIST_SYNONYMS := 0;'
insert into ##table(ident,ctext) select 1,'END IF;'
insert into ##table(ident,ctext) select -1,''
end
else
begin
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '创建该表的同义词'
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE ''CREATE OR REPLACE SYNONYM ' + quotename(upper(@user),'"') + '.' + quotename(@synonyms,'"') + ' FOR ' + quotename(upper(@user),'"') + '.' + quotename(upper(@tablename),'"') + ''';'
insert into ##table(ident,ctext) select -1,''
end
end
end
--表游标下移
fetch next from cr_table_name into @tablename
end
close cr_table_name
deallocate cr_table_name
insert into ##table(ident,ctext) select 0,'END;'
end--PL/SQL块尾部定义
- ››oracle 恢复误删除的表和误更新的表
- ››Oracle分页查询排序数据重复问题
- ››Oracle创建dblink报错:ORA-01017、ORA-02063解决
- ››Oracle 提高SQL执行效率的方法
- ››Oracle 动态查询，EXECUTE IMMEDIATE select into...
- ››Oracle 11g必须开启的服务及服务详细介绍
- ››oracle性能34条优化技巧
- ››oracle数据库生成随机数的函数
- ››Oracle 数据库表空间容量调整脚本
- ››oracle单库彻底删除干净的方法
- ››sqlserver 每30分自动生成一次
- ››Oracle创建表空间、创建用户以及授权、查看权限
中查找“Sqlserver2005迁移至Oracle系列之一：生成表(下)”更多相关内容
中查找“Sqlserver2005迁移至Oracle系列之一：生成表(下)”更多相关内容更多精彩
赞助商链接
