| /*--下面我們新建一個名為company的數據庫，創建三個表priduct、project、tblsales，并在其中創建默認值、規則、觸發器、存儲過程，并利用用戶、角色、權限等確保數據庫安全。*/
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('項目經理','秘書','會計','職員','其它')
GO
setuser
GO EXEC sp_addtype N'tele', N'smallint', N'not null'
GO setuser
GO CREATE TABLE [dbo].[priduct] (
[產品ID] [int] NULL ,
[產品名稱] [char] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO CREATE TABLE [dbo].[project] (
[項目編號] [int] NOT NULL ,
[項目名稱] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[開始日期] [datetime] NULL ,
[預計日期] [int] NULL ,
[客戶編號] [int] NULL ,
[負責人編號] [int] NULL ,
[項目標的] [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].[項目標的]'
GO EXEC sp_bindrule N'[dbo].[rule_position]', N'[project].[項目名稱]'
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(項目標的)
from project as p inner join pmanager as pm on p.負責人ID=pm.負責人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 項目標的>=5000
order by 項目標的 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 |