行政区划数据数据库的设计(脚本)

/**************************************************************************
Author:   LzmTW(水如烟)
Create date: 20060921
Description: 行政区划数据数据库生成脚本,适用于SQLServer2005
***************************************************************************/

USE [master]
GO
/****** 对象:  Database [RegionalCodeWorks]    脚本日期: 09/21/2006 20:38:21 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'RegionalCodeWorks')
 DROP DATABASE RegionalCodeWorks

GO
CREATE DATABASE RegionalCodeWorks

GO
EXEC dbo.sp_dbcmptlevel @dbname=N'RegionalCodeWorks', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [RegionalCodeWorks].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [RegionalCodeWorks] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET ANSI_NULLS OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET ANSI_PADDING OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET ARITHABORT OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [RegionalCodeWorks] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [RegionalCodeWorks] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [RegionalCodeWorks] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET  ENABLE_BROKER
GO
ALTER DATABASE [RegionalCodeWorks] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [RegionalCodeWorks] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [RegionalCodeWorks] SET  READ_WRITE
GO
ALTER DATABASE [RegionalCodeWorks] SET RECOVERY SIMPLE
GO
ALTER DATABASE [RegionalCodeWorks] SET  MULTI_USER
GO
ALTER DATABASE [RegionalCodeWorks] SET PAGE_VERIFY CHECKSUM 
GO
ALTER DATABASE [RegionalCodeWorks] SET DB_CHAINING OFF
USE [RegionalCodeWorks]
GO
/****** 对象:  Schema [Program]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Program')
EXEC sys.sp_executesql N'CREATE SCHEMA [Program] AUTHORIZATION [dbo]'

GO
/****** 对象:  Schema [FUNCTION]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'FUNCTION')
EXEC sys.sp_executesql N'CREATE SCHEMA [FUNCTION] AUTHORIZATION [dbo]'

GO
/****** 对象:  Schema [Edit]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Edit')
EXEC sys.sp_executesql N'CREATE SCHEMA [Edit] AUTHORIZATION [dbo]'

GO
/****** 对象:  Schema [Base]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Base')
EXEC sys.sp_executesql N'CREATE SCHEMA [Base] AUTHORIZATION [dbo]'

GO
/****** 对象:  Schema [Action]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Action')
EXEC sys.sp_executesql N'CREATE SCHEMA [Action] AUTHORIZATION [dbo]'

GO
/****** 对象:  Table [Base].[行政区级别]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Base].[行政区级别]') AND type in (N'U'))
BEGIN
CREATE TABLE [Base].[行政区级别](
 [级别ID] [smallint] IDENTITY(0,1) NOT NULL,
 [级别] [nchar](10) NOT NULL,
 CONSTRAINT [PK_行政区级别] PRIMARY KEY CLUSTERED
(
 [级别ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_行政区级别] UNIQUE NONCLUSTERED
(
 [级别] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** 对象:  Default [Base].[RegionalDateDefault]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Base].[RegionalDateDefault]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
EXEC dbo.sp_executesql N'/****** 对象:  Default [Salary].[SalaryDateDefault]    脚本日期: 09/17/2006 14:44:13 ******/
--USE EmployeeWorks
CREATE DEFAULT [Base].[RegionalDateDefault] AS N''Current'''
GO
/****** 对象:  Rule [Base].[RegionalCodeRule]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Base].[RegionalCodeRule]') AND OBJECTPROPERTY(id, N'IsRule') = 1)
EXEC dbo.sp_executesql N'
CREATE RULE [Base].[RegionalCodeRule]
AS
@value LIKE ''[1-9][0-9][0-9][0-9][0-9][0-9]''
'
GO
/****** 对象:  Table [Base].[行政区类型]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Base].[行政区类型]') AND type in (N'U'))
BEGIN
CREATE TABLE [Base].[行政区类型](
 [类型ID] [smallint] IDENTITY(0,1) NOT NULL,
 [类型] [nvarchar](50) NOT NULL,
 [级别ID] [smallint] NOT NULL,
 CONSTRAINT [PK_行政区类型] PRIMARY KEY CLUSTERED
(
 [类型ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_行政区类型] UNIQUE NONCLUSTERED
(
 [类型] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** 对象:  UserDefinedDataType [Base].[RegionalDate]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'RegionalDate' AND ss.name = N'Base')
CREATE TYPE [Base].[RegionalDate] FROM [nvarchar](8) NOT NULL
GO
EXEC sys.sp_bindefault @defname=N'[Base].[RegionalDateDefault]', @objname=N'[Base].[RegionalDate]' , @futureonly='futureonly'
GO
/****** 对象:  UserDefinedDataType [Base].[RegionalCode]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'RegionalCode' AND ss.name = N'Base')
CREATE TYPE [Base].[RegionalCode] FROM [nchar](6) NOT NULL
GO
EXEC sys.sp_bindrule @rulename=N'[Base].[RegionalCodeRule]', @objname=N'[Base].[RegionalCode]' , @futureonly='futureonly'
GO
/****** 对象:  Table [Base].[区划码]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Base].[区划码]') AND type in (N'U'))
BEGIN
CREATE TABLE [Base].[区划码](
 [区划码ID] [smallint] IDENTITY(1,1) NOT NULL,
 [区划码] [Base].[RegionalCode] NOT NULL,
 [一级]  AS (substring([区划码],(1),(2))),
 [二级]  AS (substring([区划码],(3),(2))),
 [三级]  AS (substring([区划码],(5),(2))),
 CONSTRAINT [PK_区划码] PRIMARY KEY CLUSTERED
(
 [区划码ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_区划码] UNIQUE NONCLUSTERED
(
 [区划码] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
/****** 对象:  Index [IX_区划码_1]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Base].[区划码]') AND name = N'IX_区划码_1')
CREATE NONCLUSTERED INDEX [IX_区划码_1] ON [Base].[区划码]
(
 [一级] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
/****** 对象:  Index [IX_区划码_2]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Base].[区划码]') AND name = N'IX_区划码_2')
CREATE NONCLUSTERED INDEX [IX_区划码_2] ON [Base].[区划码]
(
 [二级] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
/****** 对象:  Index [IX_区划码_3]    脚本日期: 09/21/2006 20:38:24 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Base].[区划码]') AND name = N'IX_区划码_3')
CREATE NONCLUSTERED INDEX [IX_区划码_3] ON [Base].[区划码]
(
 [三级] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
EXEC sys.sp_bindrule @rulename=N'[Base].[RegionalCodeRule]', @objname=N'[Base].[区划码].[区划码]' , @futureonly='futureonly'
GO
/****** 对象:  Table [Edit].[区划码情况]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Edit].[区划码情况]') AND type in (N'U'))
BEGIN
CREATE TABLE [Edit].[区划码情况](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [区划码ID] [smallint] NOT NULL,
 [截止日期] [Base].[RegionalDate] NOT NULL,
 [名称] [nvarchar](100) NOT NULL,
 [起始日期] [Base].[RegionalDate] NOT NULL,
 CONSTRAINT [PK_区划码情况] PRIMARY KEY CLUSTERED
(
 [区划码ID] ASC,
 [截止日期] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_bindefault @defname=N'[Base].[RegionalDateDefault]', @objname=N'[Edit].[区划码情况].[截止日期]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[Base].[RegionalDateDefault]', @objname=N'[Edit].[区划码情况].[起始日期]' , @futureonly='futureonly'
GO
/****** 对象:  Table [Edit].[行政区类型情况]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Edit].[行政区类型情况]') AND type in (N'U'))
BEGIN
CREATE TABLE [Edit].[行政区类型情况](
 [ID] [smallint] IDENTITY(1,1) NOT NULL,
 [类型ID] [smallint] NOT NULL,
 [范围] [nvarchar](max) NOT NULL,
 [截止日期] [Base].[RegionalDate] NOT NULL,
 [起始日期] [Base].[RegionalDate] NOT NULL,
 [备注] [nvarchar](max) NULL,
 CONSTRAINT [PK_行政区类型情况] PRIMARY KEY CLUSTERED
(
 [类型ID] ASC,
 [截止日期] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_bindefault @defname=N'[Base].[RegionalDateDefault]', @objname=N'[Edit].[行政区类型情况].[截止日期]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[Base].[RegionalDateDefault]', @objname=N'[Edit].[行政区类型情况].[起始日期]' , @futureonly='futureonly'
GO
/****** 对象:  UserDefinedFunction [FUNCTION].[行政区类型情况]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FUNCTION].[行政区类型情况]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [FUNCTION].[行政区类型情况]
( 
  @当前时间 [Base].[RegionalDate] = N''Current''
)
RETURNS TABLE
AS
RETURN
(
 SELECT a.*
 FROM [Edit].[行政区类型情况] a
 RIGHT OUTER JOIN
 (
  SELECT [类型ID]
    ,MIN(DISTINCT [截止日期])AS 查询时间
  FROM [Edit].[行政区类型情况]
  WHERE   @当前时间 BETWEEN [起始日期] AND [截止日期]
  GROUP BY[类型ID]
 ) b
 ON a.类型ID = b.类型ID AND a.截止日期 = b.查询时间
)
'
END

GO
/****** 对象:  UserDefinedFunction [FUNCTION].[区划码情况]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FUNCTION].[区划码情况]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [FUNCTION].[区划码情况]
( 
  @当前时间 [Base].[RegionalDate] = N''Current''
)
RETURNS TABLE
AS
RETURN
(
 SELECT a.*
 FROM [Edit].[区划码情况] a
 RIGHT OUTER JOIN
 (
  SELECT [区划码ID]
    ,MIN(DISTINCT [截止日期])AS 查询时间
  FROM [Edit].[区划码情况]
  WHERE   @当前时间 BETWEEN [起始日期] AND [截止日期]
  GROUP BY[区划码ID]
 ) b
 ON a.区划码ID = b.区划码ID AND a.截止日期 = b.查询时间
)
'
END

GO
/****** 对象:  StoredProcedure [Program].[增添记录]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Program].[增添记录]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:  LzmTW
-- Create date: 20060917
-- Description: 增添记录
-- =============================================
CREATE PROCEDURE [Program].[增添记录]
  @区划码 [Base].[RegionalCode]
 ,@名称  nvarchar(100)
 ,@起始日期 [Base].[RegionalDate]
AS
BEGIN
 SET NOCOUNT ON;

 --如果@起始日期小于表区划码情况的最新起始日期，则退出
 DECLARE @最新起始日期 [Base].[RegionalDate]

 SELECT @最新起始日期 = MAX(起始日期)
 FROM [Edit].区划码情况

 IF @起始日期 < @最新起始日期
  RETURN

 --如果主表区划码没有记录，则更新;取区划码ID
 DECLARE @区划码ID smallint

 SELECT @区划码ID = 区划码ID
 FROM [Base].[区划码]
 WHERE 区划码 = @区划码;

 IF @区划码ID IS NULL
 BEGIN
  INSERT INTO [Base].[区划码](区划码)
  VALUES(@区划码)

  SET  @区划码ID = @@IDENTITY
 END
 
 --表区划码情况的更新
 DECLARE  @ID  int
   ,@原名称 nvarchar(100)

 SELECT  @ID = ID
   ,@原名称 = 名称
 FROM  [FUNCTION].区划码情况(@起始日期)
 WHERE 区划码ID = @区划码ID

 IF @ID IS NULL
  BEGIN
   INSERT INTO [Edit].[区划码情况]
    (
      区划码ID
     ,名称
     ,起始日期
    )
   VALUES
    (
      @区划码ID
     ,@名称
     ,@起始日期
    )
  END
 ELSE
  BEGIN
           --忽略同名的记录，也就是保持Current有效
   IF @名称 = @原名称
    RETURN
   ELSE
    BEGIN
     --将原记录的截止日期更新为当前起始日期的前一日
     UPDATE [Edit].[区划码情况]
     SET 截止日期 = CONVERT(nchar(8) ,DATEADD(d, -1, CAST(@起始日期 as smalldatetime)) ,112)
     WHERE ID = @ID

     INSERT INTO [Edit].[区划码情况]
      (
        区划码ID
       ,名称
       ,起始日期
      )
     VALUES
      (
        @区划码ID
       ,@名称
       ,@起始日期
      )    
    END
  END 
END
'
END
GO
/****** 对象:  StoredProcedure [Action].[区划码类型情况]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Action].[区划码类型情况]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:  LzmTW
-- Create date: 20060920
-- Description: 取区划码的类型ID
-- =============================================
CREATE PROCEDURE [Action].[区划码类型情况]
  @当前时间 [Base].[RegionalDate] = N''Current''
AS
BEGIN
 --通过临时表来处理数据

 IF EXISTS
 (
  SELECT 1 FROM tempdb.dbo.sysobjects
  WHERE [name] =  N''##区划码类型情况''  AND xtype=N''U''
 )
  DROP TABLE ##区划码类型情况

 CREATE TABLE ##区划码类型情况
  (
    区划码ID smallint
   ,类型ID smallint
  )

 --取类型定义为未知的ID
 DECLARE @未知ID smallint

 SELECT @未知ID = 类型ID
 FROM [Base].行政区类型
 WHERE 类型 = N''未知''

 --所有区划码的类型初始化为未知
 INSERT INTO ##区划码类型情况
 SELECT   区划码ID
   ,@未知ID as 类型ID
 FROM [FUNCTION].区划码情况(@当前时间)

 --以下处理的正确性取决于类型定义不存在交集!!
 DECLARE  @Building nvarchar(MAX)
   ,@SQL  nvarchar(MAX)
   ,@类型ID smallint
   ,@范围  nvarchar(MAX)

 SET @Building = N''
  UPDATE ##区划码类型情况
  SET 类型ID = b.类型ID
  FROM ##区划码类型情况 a
  INNER JOIN
  (
   SELECT   区划码ID
     ,{类型ID} AS 类型ID
   FROM [Action].[行政区情况]('''''' + @当前时间 + N'''''')
   WHERE {WHERE}
  ) b
  ON a.区划码ID = b.区划码ID ''

 --遍历类型定义表更新区划码类型
 DECLARE 行政区类型_Cursor CURSOR FOR
 SELECT   类型ID
   ,范围
 FROM  [FUNCTION].行政区类型情况(@当前时间)
 

 OPEN 行政区类型_Cursor

 FETCH NEXT
 FROM 行政区类型_Cursor
 INTO @类型ID, @范围

 WHILE (@@fetch_status<>-1)
 BEGIN

  IF (@@fetch_status<>-2)
  BEGIN
      
   SET @SQL = REPLACE(@Building, N''{类型ID}'', @类型ID)
   SET @SQL = REPLACE(@SQL, N''{WHERE}'', @范围)

   EXEC (@SQL)

   FETCH NEXT
   FROM 行政区类型_Cursor
   INTO @类型ID, @范围
  END

 END

 CLOSE 行政区类型_Cursor
 DEALLOCATE 行政区类型_Cursor 

 SELECT *
 FROM ##区划码类型情况
END
'
END
GO
/****** 对象:  UserDefinedFunction [Action].[行政区情况]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Action].[行政区情况]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [Action].[行政区情况]
( 
  @当前时间 [Base].[RegionalDate] = N''Current''
)
RETURNS TABLE
AS
RETURN
(
 SELECT  a.区划码ID
   ,a.区划码
   ,b.名称
   ,a.一级
   ,a.二级
   ,a.三级
   ,b.截止日期
   ,b.起始日期
 FROM [Base].[区划码] a
 INNER JOIN [FUNCTION].[区划码情况](@当前时间) b
 ON a.区划码ID = b.区划码ID
)
'
END

GO
/****** 对象:  UserDefinedFunction [Action].[全名]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Action].[全名]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:  LzmTW
-- Create date: 20060921
-- Description: 取行政区的全名
-- =============================================
CREATE FUNCTION [Action].[全名]
(
  @当前时间 [Base].[RegionalDate] = N''Current''
 ,@一级  nchar(2)
 ,@二级  nchar(2)
 ,@三级  nchar(2)
)
RETURNS nvarchar(200)
AS
BEGIN
 --初始化变量

 DECLARE  @全名  nvarchar(200)
   ,@一级名称 nvarchar(100)
   ,@二级名称 nvarchar(100)
   ,@三级名称 nvarchar(100)

 SET @一级名称 = N''''
 SET @二级名称 = N''''
 SET @三级名称 = N''''

 DECLARE  @一级区划码 [Base].RegionalCode
   ,@二级区划码 [Base].RegionalCode 
   ,@当前区划码 [Base].RegionalCode

 SET @一级区划码 = @一级 + N''00'' + N''00''
 SET @二级区划码 = @一级 + @二级 + N''00''
 SET @当前区划码 = @一级 + @二级 + @三级

 --分三种情形取全名

 IF @当前区划码 = @一级区划码
  SELECT @一级名称 = 名称
  FROM [Action].[行政区情况](@当前时间)
  WHERE 区划码 = @当前区划码

 ELSE
  IF @当前区划码 = @二级区划码
   BEGIN
    SELECT @一级名称 = 名称
    FROM [Action].[行政区情况](@当前时间)
    WHERE 区划码 = @一级区划码

    SELECT @二级名称 = 名称
    FROM [Action].[行政区情况](@当前时间)
    WHERE 区划码 = @当前区划码
   END
  ELSE
   BEGIN

    SELECT @一级名称 = 名称
    FROM [Action].[行政区情况](@当前时间)
    WHERE 区划码 = @一级区划码

    SELECT @二级名称 = 名称
    FROM [Action].[行政区情况](@当前时间)
    WHERE 区划码 = @二级区划码

    SELECT @三级名称 = 名称
    FROM [Action].[行政区情况](@当前时间)
    WHERE 区划码 = @当前区划码

   END

 IF @二级名称 IN(N''市辖区'', N''市'', N''县'')
  SET @二级名称 = N''''

 SET @全名 =   @一级名称
    + @二级名称
    + @三级名称

 RETURN @全名

END
'
END

GO
/****** 对象:  StoredProcedure [Program].[行政区情况]    脚本日期: 09/21/2006 20:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Program].[行政区情况]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:  LzmTW
-- Create date: 20060921
-- Description: 取行政区的全名
-- =============================================
CREATE PROCEDURE [Program].[行政区情况]
  @当前时间 [Base].[RegionalDate] = N''Current''
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @类型情况 TABLE
 (
   区划码ID smallint
  ,类型ID  smallint
 )

 INSERT INTO @类型情况
 EXEC [Action].[区划码类型情况] @当前时间

 SELECT TOP 100 PERCENT
    d.区划码ID
   ,d.区划码
   ,d.名称
   ,[Action].全名(@当前时间, d.一级, d.二级, d.三级) AS 全名
   ,a.类型
   ,b.级别
   ,d.一级
   ,d.二级
   ,d.三级
   ,d.截止日期
   ,d.起始日期
   ,a.类型ID
   ,b.级别ID
 FROM Base.行政区类型 AS a
 INNER JOIN Base.行政区级别 AS b
 ON a.级别ID = b.级别ID
 INNER JOIN @类型情况 AS c
 ON a.类型ID = c.类型ID
 INNER JOIN [Action].行政区情况(@当前时间) AS d
 ON d.区划码ID = c.区划码ID
 ORDER BY d.一级, d.二级, d.三级
END
'
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[Base].[FK_行政区类型_行政区级别]') AND parent_object_id = OBJECT_ID(N'[Base].[行政区类型]'))
ALTER TABLE [Base].[行政区类型]  WITH CHECK ADD  CONSTRAINT [FK_行政区类型_行政区级别] FOREIGN KEY([级别ID])
REFERENCES [Base].[行政区级别] ([级别ID])
GO
ALTER TABLE [Base].[行政区类型] CHECK CONSTRAINT [FK_行政区类型_行政区级别]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[Edit].[FK_区划码情况_区划码]') AND parent_object_id = OBJECT_ID(N'[Edit].[区划码情况]'))
ALTER TABLE [Edit].[区划码情况]  WITH CHECK ADD  CONSTRAINT [FK_区划码情况_区划码] FOREIGN KEY([区划码ID])
REFERENCES [Base].[区划码] ([区划码ID])
GO
ALTER TABLE [Edit].[区划码情况] CHECK CONSTRAINT [FK_区划码情况_区划码]
GO
IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Edit].[CK_区划码情况]') AND parent_object_id = OBJECT_ID(N'[Edit].[区划码情况]'))
ALTER TABLE [Edit].[区划码情况]  WITH CHECK ADD  CONSTRAINT [CK_区划码情况] CHECK  (([截止日期]>=[起始日期]))
GO
ALTER TABLE [Edit].[区划码情况] CHECK CONSTRAINT [CK_区划码情况]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[Edit].[FK_行政区类型情况_行政区类型]') AND parent_object_id = OBJECT_ID(N'[Edit].[行政区类型情况]'))
ALTER TABLE [Edit].[行政区类型情况]  WITH CHECK ADD  CONSTRAINT [FK_行政区类型情况_行政区类型] FOREIGN KEY([类型ID])
REFERENCES [Base].[行政区类型] ([类型ID])
GO
ALTER TABLE [Edit].[行政区类型情况] CHECK CONSTRAINT [FK_行政区类型情况_行政区类型]
GO
IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Edit].[CK_行政区类型情况]') AND parent_object_id = OBJECT_ID(N'[Edit].[行政区类型情况]'))
ALTER TABLE [Edit].[行政区类型情况]  WITH CHECK ADD  CONSTRAINT [CK_行政区类型情况] CHECK  (([截止日期]>=[起始日期]))
GO
ALTER TABLE [Edit].[行政区类型情况] CHECK CONSTRAINT [CK_行政区类型情况]
GO
INSERT INTO [Base].[行政区级别]([级别]) VALUES (N'未知')
INSERT INTO [Base].[行政区级别]([级别]) VALUES (N'一级')
INSERT INTO [Base].[行政区级别]([级别]) VALUES (N'二级')
INSERT INTO [Base].[行政区级别]([级别]) VALUES (N'三级')

GO
INSERT INTO [Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'未知' ,0)
INSERT INTO [Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'省' ,1)
INSERT INTO [Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'省直辖市' ,2)
INSERT INTO [Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'地区州盟' ,2)
INSERT INTO [Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'市地辖区' ,3)
INSERT INTO [Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'县旗' ,3)
INSERT INTO [Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'省直辖县级市' ,3)

GO
INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (1
           ,N'二级 = N''00'' AND 三级 = N''00'''
           ,N'20000101'
           ,N'省')

INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (2
           ,N'(二级 BETWEEN N''01'' AND N''20'' OR 二级 BETWEEN N''51'' AND N''99'' ) AND 三级 = N''00'' '
           ,N'20000101'
           ,N'省直辖市')

INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (3
           ,N'二级 BETWEEN N''21'' AND N''50'' AND 三级 = N''00'' '
           ,N'20000101'
           ,N'地区州盟')

INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (4
           ,N'三级 BETWEEN N''01'' AND N''20''  '
           ,N'20000101'
           ,N'市地辖区')

INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (5
           ,N'三级 BETWEEN N''21'' AND N''80'' '
           ,N'20000101'
           ,N'县旗')

INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (6
           ,N'三级 BETWEEN N''81'' AND N''99'' '
           ,N'20000101'
           ,N'省直辖县级市')