Sqlserver2005迁移至Oracle系列之一：生成表(上)
2009-03-03 13:11:25 来源：WEB开发网--表名称游标
declare cr_table_name cursor for
select distinct tabname from #table_define
open cr_table_name
fetch next from cr_table_name into @tablename
if @@fetch_status = 0
begin
--PL/SQL块头部定义
insert into ##table(ident,ctext) select 0,'DECLARE'
insert into ##table(ident,ctext) select 1,'VA_EXIST_TABLE INT:=0;'
insert into ##table(ident,ctext) select 1,'VA_EXIST_SEQUENCE INT:=0;'
insert into ##table(ident,ctext) select 1,'VA_EXIST_INDEX INT:=0;'
insert into ##table(ident,ctext) select 1,'VA_EXIST_SYNONYMS INT:=0;'
insert into ##table(ident,ctext) select 0,'BEGIN'
end
else
begin
close cr_table_name
deallocate cr_table_name
print '没有表对象需要生成！'
return
end
while @@fetch_status = 0
begin
insert into ##table(ident,ctext) select -1,'--' + replicate('-',30) + quotename(upper(@tablename),'"') + ' @ ' + quotename(convert(varchar(19),getdate(),121)) + replicate('-',20)
--生成或删除表
if @istable = 1
begin
--表头定义,删除部分
if @onlydeltable = 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(*) INTO VA_EXIST_TABLE FROM ALL_TABLES WHERE OWNER = ''' + upper(@user) + ''' AND TABLE_NAME = ' + quotename(upper(@tablename),'''') + ';'
insert into ##table(ident,ctext) select 1,'IF VA_EXIST_TABLE > 0 THEN '
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE '
insert into ##table(ident,ctext) select 2,'''' + 'DROP TABLE ' + quotename(upper(@user),'"') + '.' + quotename(upper(@tablename),'"') + ' CASCADE CONSTRAINTS PURGE'';'
insert into ##table(ident,ctext) select 2,'VA_EXIST_TABLE := 0;'
insert into ##table(ident,ctext) select 1,'END IF;'
--表头定义,创建部分,如果不只是删除
if @onlydeltable = 0
begin
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE '
insert into ##table(ident,ctext) select 2,'''' + 'CREATE TABLE ' + quotename(upper(@user),'"') + '.' + quotename(upper(@tablename),'"')
insert into ##table(ident,ctext) select 2,'('
--根据是否存在表约束来决定列定义后是否跟随‘，’
if exists( select *
from #table_constraints
where (tabname = @tablename)
--and (@tablename not like 'plat_reglog%') --ver1.1
--and (@tablename <> 'PLAT_RegUpgrade') --ver1.1
)
set @exiests_constraints = 1
else
set @exiests_constraints = 0
--表列定义
begin
set @sequence_is = 0
declare cr_table_column cursor for
select colname,typename,length,[precision],scale,[isnull],[default],isidentity,seed,incre
from #table_define
where tabname = @tablename
open cr_table_column
fetch next from cr_table_column into @colname,@typename,@length,@precision,@scale,@isnull,@default,@isidentity,@seed,@incre
while @@fetch_status = 0
begin
--列明细
begin
set @col_define = ''
set @colname = @colname
set @length = case
when (@typename in('char','nchar','varchar','nvarchar','binary','varbinary','sysname')) and (@length > 0) then @length
when (@typename in('char','nchar','varchar','nvarchar','binary','varbinary')) and (@length = -1) then '-1' --MAX
else null
end
set @precision = case
when @typename in('bigint','smallmoney','money','decimal','numeric','real','float') then @precision
else null
end
set @scale = case
when @typename in('bigint','smallmoney','money','decimal','numeric','real','float') then @scale
else null
end
set @oratypename =
(case @typename
when 'numeric' then 'numeric'
when 'image' then 'blob'
when 'datetime' then 'timestamp'
when 'smalldatetime' then 'timestamp'
when 'real' then 'float'
when 'decimal' then 'numeric'
when 'xml' then 'xml'
when 'timestamp' then 'timestamp'
when 'char' then 'char'
when 'nchar' then 'nchar'
when 'varchar' then 'varchar2'
when 'nvarchar' then 'nvarchar2'
when 'sysname' then 'nvarchar2'
when 'uniqueidentifier' then 'rowid'
when 'float' then 'float'
when 'bit' then 'int'
when 'sql_variant' then 'nvarchar2' --sql_variant
when 'int' then 'int'
when 'bigint' then 'numeric'
when 'smallint' then 'int'
when 'tinyint' then 'int'
when 'text' then 'clob'
when 'ntext' then 'nclob'
when 'money' then 'numeric'
when 'smallmoney' then 'numeric'
when 'binary' then 'raw'
when 'varbinary' then 'raw'
end)
set @oratypename =
(case
when (@typename in ('binary','varbinary')) and ((@length = -1) OR (@length > 2000)) then 'long raw'
when (@typename in ('char','nchar')) and ((@length = -1) OR (@length > 2000)) then 'long'
when (@typename in ('varchar','nvarchar')) and ((@length = -1) OR (@length > 4000)) then 'long'
else @oratypename
end)
set @length = case @oratypename
when 'long' then null
when 'long raw' then null
else @length
end
set @default = case
when @default like '((%' then right(left(@default,len(@default)-2),len(@default)-4)
when @default like '(''%' then '''' + right(left(@default,len(@default)-1),len(@default)-2) + ''''
else null
end
set @col_define = replicate(' ',4) + quotename(upper(@colname),'"') + replicate(' ',4) + upper(@oratypename) + isnull(('(' + cast(@length as varchar(50)) + ')'),'')
+ isnull('(' + cast(@precision as varchar(50)) + isnull(',' + cast(@scale as varchar(50)),'') + ')','')
+ replicate(' ',4) + isnull('DEFAULT ' + @default,'')
+ replicate(' ',4) + (case @isnull when 1 then '' else 'NOT NULL' end)
set @col_define = rtrim(@col_define)
if @isidentity = 1
begin
set @sequence_is = 1
set @sequence_colname = @colname
set @sequence_seed = cast(@seed as bigint)
set @sequence_incre = cast(@incre as bigint)
end
end
系列文章：
Sqlserver2005迁移至Oracle系列之一：生成表(上)
Sqlserver2005迁移至Oracle系列之一：生成表(下)
Sqlserver2005迁移至Oracle系列之二：生成存储过程
Sqlserver2005迁移至Oracle系列之三：在Oracle中创建sql中的常见函数
Sqlserver2005迁移至Oracle系列之四：在Oracle中创建位或运算函数-bitor
Sqlserver2005迁移至Oracle系列之五：角色、用户、及权限
- ››oracle 中 UPDATE nowait 的使用方法
- ››Oracle ORA-12560解决方法
- ››Oracle 10g RAC 常用维护命令
- ››Oracle如何在ASM中定位文件的分布
- ››sqlserver安装和简单的使用
- ››Oracle的DBMS_RANDOM.STRING 的用法
- ››oracle 外部表导入时间日期类型数据，多字段导入
- ››Oracle中查找重复记录
- ››oracle修改用户登录密码
- ››Oracle创建删除用户、角色、表空间、导入导出等命...
- ››Oracle中登陆时报ORA-28000: the account is lock...
- ››Oracle数据库在配置文件中更改最大连接数
中查找“Sqlserver2005迁移至Oracle系列之一：生成表(上)”更多相关内容
中查找“Sqlserver2005迁移至Oracle系列之一：生成表(上)”更多相关内容更多精彩
赞助商链接
