| /*--下面我們新建一個(gè)名為company的數(shù)據(jù)庫，創(chuàng)建三個(gè)表priduct、project、tblsales，并在其中創(chuàng)建默認(rèn)值、規(guī)則、觸發(fā)器、存儲(chǔ)過程，并利用用戶、角色、權(quán)限等確保數(shù)據(jù)庫安全。*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_priduct_project]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[priduct] DROP CONSTRAINT FK_priduct_project
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getavgpbiaodi]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getavgpbiaodi]
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pinfo5000]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pinfo5000]
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pro]
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysconstraints]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[sysconstraints]
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[priduct]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[priduct]
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[project]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[project]
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblsales]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblsales]
GO if exists (select * from dbo.systypes where name = N'tele')
exec sp_droptype N'tele'
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rule_position]') and OBJECTPROPERTY(id, N'IsRule') = 1)
drop rule [dbo].[rule_position]
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@position]') and OBJECTPROPERTY(id, N'IsDefault') = 1)
drop default [dbo].[@position]
GO create default [@position] as '其它'
GO
create rule [rule_position] as @postion in('項(xiàng)目經(jīng)理','秘書','會(huì)計(jì)','職員','其它')
GO
setuser
GO EXEC sp_addtype N'tele', N'smallint', N'not null'
GO setuser
GO CREATE TABLE [dbo].[priduct] (
[產(chǎn)品ID] [int] NULL ,
[產(chǎn)品名稱] [char] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[project] (
[項(xiàng)目編號(hào)] [int] NOT NULL ,
[項(xiàng)目名稱] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[開始日期] [datetime] NULL ,
[預(yù)計(jì)日期] [int] NULL ,
[客戶編號(hào)] [int] NULL ,
[負(fù)責(zé)人編號(hào)] [int] NULL ,
[項(xiàng)目標(biāo)的] [int] NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[tblsales] (
[number] [int] NOT NULL ,
[name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[sex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[birthday] [datetime] NULL ,
[salary] [money] NULL
) ON [PRIMARY]
GO setuser
GO EXEC sp_bindefault N'[dbo].[@position]', N'[project].[項(xiàng)目標(biāo)的]'
GO EXEC sp_bindrule N'[dbo].[rule_position]', N'[project].[項(xiàng)目名稱]'
GO setuser
GO SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO --/****** Encrypted object is not transferable, and script can not be generated. ******/ GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO create procedure getavgpbiaodi
@name varchar(10),@avgpbiaodi int output
as
declare @errorsave int
set @errorsave=0
select @avgpbiaodi=avg(項(xiàng)目標(biāo)的)
from project as p inner join pmanager as pm on p.負(fù)責(zé)人ID=pm.負(fù)責(zé)人ID
where pm.姓名=@name
if(@@error<>0)
set @errorsave=@@error
return @errorsave GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO create procedure pinfo5000
as select * from project where 項(xiàng)目標(biāo)的>=5000
order by 項(xiàng)目標(biāo)的 desc GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO create procedure pro
@n1 int,@n2 int,@n3 int,@avreage int output
as select
@avreage=(@n1+@n2+@n3)/3
declare @avgscore int
exec pro1 1,2,3,@avgscore output
select 'The score is:',@avgscore GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO |