oracle中cascade是什么意思
级联删除,比如你删除某个表的时候后面加这个关键字
会在删除这个表的同时删除和该表有关系的其他对象
***********************************************************************************
发表于: 2007-09-20 17:01:15
在查询分析器里用如下语句:
declare @mylen int,
@dropname char(100)
set @mylen=(SELECT length FROM syscolumns WHERE name = 'fwxzdh' AND id = (SELECT id FROM sysobjects WHERE name = 'tb_fwhd_wxz'))
set @dropname=(select rtrim(name) as dropname from sysobjects where xtype='D' and parent_obj=(select id from sysobjects where name='tb_fwhd_wxz' and xtype='U') and id in (select cdefault from syscolumns where name='fwxzdh'))
if len(@dropname)>0 and @mylen<>8
alter table tb_fwhd_wxz drop constraint @dropname
alter table tb_fwhd_wxz alter column fwxzdh char(8) not null
ALTER TABLE [dbo].[tb_fwhd_wxz] WITH NOCHECK ADD CONSTRAINT [DF_tb_fwhd_wxz_fwxzdh_20070920] default(‘’) for [fwxzdh]
主要是判断fwxzdh字段长度是否为8,如果不是先删除默认值限制,再修改字段长度,最后再加上默认值限制
运行时出下面的错误:
服务器: 消息 170,级别 15,状态 1,行 8
第 8 行: '@dropname' 附近有语法错误。
因为@dropname是变量,而直接在sql语句中不能识别变量,所以出错,请问各位如何解决
非常感谢!!!
回复于: 2007-09-20 17:14:05
declare @mylen int,
--@dropname char(100)
@dropname sysname
--set @mylen=(SELECT length FROM syscolumns WHERE name = 'fwxzdh' AND id = (SELECT id FROM sysobjects WHERE name = 'tb_fwhd_wxz'))
SELECT @mylen=length FROM syscolumns WHERE name = 'fwxzdh' AND id = object_id('tb_fwhd_wxz')
--这一句自己改
set @dropname=(select rtrim(name) as dropname from sysobjects where xtype='D' and parent_obj=(select id from sysobjects where name='tb_fwhd_wxz' and xtype='U') and id in (select cdefault from syscolumns where name='fwxzdh'))
--if len(@dropname)>0 and @mylen<>8
if @dropname is not null and @mylen<>8
--alter table tb_fwhd_wxz drop constraint @dropname
exec ('alter table tb_fwhd_wxz drop constraint ' + @dropname)
alter table tb_fwhd_wxz alter column fwxzdh char(8) not null
回复于: 2007-09-20 17:14:28
alter table tb_fwhd_wxz drop constraint @dropname
就是这句话有问题,删除时不能用变量
回复于: 2007-09-20 17:24:50
alter table tb_fwhd_wxz drop constraint @dropname
就是这句话有问题,删除时不能用变量
============================================================================
declare @sql nvarchar(2000)
select @sql = N'alter table tb_fwhd_wxz drop constraint ' + @dropname
exec sp_executesql @sql