行政区划数据数据库的设计(一)
Author:水如烟
这是《HOW TO:利用Excel的QueryTable下载网上数据 》的续篇。
代码写得怎么不好,至少给出了一种途径,所以可以冠名HOW TO;数据库设计就不能HOW TO了,只能是给出自己的思维供各位指正,仅供参考。
关于数据库的设计我是没有看过有关书籍,全是凭自己领悟感觉,零散的吸收网上的介绍。这里说设计,纯粹是非专业者的设计,所以下面的设计“动作”,读者不可轻易接受或相信。
在上一文中,已经实现了从网上下载数据到本地的DataTable.
行政区划数据,自2002年6月30日起至2005年12月31日,已经有8个版本的数据。
每个版本的数据至少有3500条,后期版本在前期版本基础上增加区划码或是修改区划名称。
我们的目的是查询某时间某个区划码是否存在,若存在当时的区划名称是什么。
数据库基于SQLServer2005,用它也算是接触学习吧。
2005有了架构,我当是Framework里头的命名空间。
一般的,建立一个数据库时,我先建立如下架构(命名空间):
Base,基础的;
Edit,编辑的;
FUNCTION,一般函数或存储过程;
Action,库内的处理函数或存储过程;
Program,外部程序调用的函数或存储过程。
用命名空间的好处,不只是带来库文件组织的便利,还可以方便的设计用户权限。
在表名列名的命名方面,我直接用中文,为的是不写文档,从中文名字中看它的意义。这个好多人看着肯定就不舒服的了。
在具体“说”之前,我先将目前的设计和效果以图片形式展现出来,给各位一个直观印象,具体说的另一文。
数据库组织:
表关系:
查询效果:
某一区划码不同时期的意义:
.
在具体设计数据库之前,我们应该先熟悉“数据对象”的特性。在这里,面对的数据对象就是区划码,它的格式如下:
六位数字名称
如:
340302 东市区
它的特性如何?它是国家的权威性的数据,由不得我们人为的臆想,所以必须寻求权威性的也就是权威发布机构的说明。就算里面的数据是错的,对于我们使用方来说,也须忠实的使用“错误”数据。
那几个版本的数据我都看了,也比较了更改过名称的数据,一部分是更名,一部分它真是修正了前期版本的名称。
更名部分还有一种情形,就是两个字的,只是中间空格数不一样。这种情形我进行了处理。在下载网上数据后,凡名称字串里有空格的,在写入本地数据库前,都去掉。
Author:水如烟
这是《HOW TO:利用Excel的QueryTable下载网上数据 》的续篇。
代码写得怎么不好,至少给出了一种途径,所以可以冠名HOW TO;数据库设计就不能HOW TO了,只能是给出自己的思维供各位指正,仅供参考。
关于数据库的设计我是没有看过有关书籍,全是凭自己领悟感觉,零散的吸收网上的介绍。这里说设计,纯粹是非专业者的设计,所以下面的设计“动作”,读者不可轻易接受或相信。
在上一文中,已经实现了从网上下载数据到本地的DataTable.
行政区划数据,自2002年6月30日起至2005年12月31日,已经有8个版本的数据。
每个版本的数据至少有3500条,后期版本在前期版本基础上增加区划码或是修改区划名称。
我们的目的是查询某时间某个区划码是否存在,若存在当时的区划名称是什么。
数据库基于SQLServer2005,用它也算是接触学习吧。
2005有了架构,我当是Framework里头的命名空间。
一般的,建立一个数据库时,我先建立如下架构(命名空间):
Base,基础的;
Edit,编辑的;
FUNCTION,一般函数或存储过程;
Action,库内的处理函数或存储过程;
Program,外部程序调用的函数或存储过程。
用命名空间的好处,不只是带来库文件组织的便利,还可以方便的设计用户权限。
在表名列名的命名方面,我直接用中文,为的是不写文档,从中文名字中看它的意义。这个好多人看着肯定就不舒服的了。
在具体“说”之前,我先将目前的设计和效果以图片形式展现出来,给各位一个直观印象,具体说的另一文。
数据库组织:
表关系:
查询效果:
某一区划码不同时期的意义:
.
Private Sub AddRow(ByVal table As DataTable, ByVal line As String)
line = line.Trim
If line.Length < 7 Then Exit Sub
Dim tmpCode As String
Dim tmpName As String
tmpCode = line.Substring(0, 6)
tmpName = line.Substring(6).Trim
If Not IsNumeric(tmpCode) Then Exit Sub '前六位需是数字
'去掉名称中间的空格
table.Rows.Add(New String() {tmpCode, tmpName.Replace(" ", "")})
End Sub
|
●
六位数字的区划码相关规约有:
代码结构:
我还是比较了数据,发现:
首组,代表省;
中组,其中01-20、51-99表示省直辖市,21-50表示地区(州、盟);
尾组,01-20表示市辖区或地辖区,21-80表示县(旗),81-99表示省直辖县级市。
在设计数据库时,我考虑了这种动态定义。如果自己理解不对,可以修改。
这篇简单说了设计前的基本的准备工作,确定了设计中应把握的基本原则。
下篇转入技术上的设计。
我以前从来不说,只写代码,就算也有“系列”的,也全是代码的。基础差,脑袋中没几个专用术语,所以是“说”的话会误导读者,特别担心会误导初学者。所以一再强调“仅供参考”。别信我说的,但可以参考我的思维方法。
写这个“系列”,一是可以总结一下自己的数据库设计方面的“想法”,二是代码贴在自己的BLOG上当作备份,三呢从头到尾的写出来,给初学者了解一个解决方案的大概过程。
文字的排版方面我不大热心,以后找个时间熟悉一下吧。现在只是多用换行,尽量的使界面清晰一些。
●
转到“说”技术,真头疼了。我不能边从MSDN上找术语边说,只能用自己的“术语”了。
我只说自己的做法,不跟别的方法进行对比,对比方法优劣不是我的能力所及。我甚至不知道如何有效使用索引。
整个数据库的设计核心是引入了“稳定数据日期”概念。所有的函数包括存储过程都围绕这个“稳定数据日期”进行操作。
如果不了解这个概念,就不容易读懂这个数据库设计。或许专业设计上已有这种模式,但是我不知道,所以下面还是说自己的。
一项数据,在一定的时间内它的属性值(有意义的字段值)如果相对的稳定,并且要反映不同时期它的属性值,就可以引入这个概念。
除了这里的区划码数据适合这种情形,日常中还有职员的岗位变动、考核的参照标准等等,在法津上约定的还有职员的年龄退休年限、工龄退休年限、个税基数等等。适用范围很广。
针对这种数据的“稳定数据日期”,定义为8位的nvarchar,通常的日期转换为八位数字串,如“2006-9-19”转换为“20060919”。它具有一个默认值,“Current”。
“Currnet”总影射到最近的时间,比如我现在要查2008年8月8日的数据,虽然实际时间还没到,它还是一样的起作用,指向了2008年8月8日的数据。
凡使用这个概念的表,它都有两个字段,如图中所示的“截止日期”和“起始日期”,这两个字段的类型都是“稳定数据日期”类型。这种表至少有一个约束,指定“截止日期”不小于“起始日期”。
“起始日期”表示这项数据自这个日期起生效;“截止日期”表示这项数据生效的最后日期。如果“截止日期”值是“Current”,那么它适用到现在,并且随时间推移一直生效下去。
这种数据具有时间顺序的强制性。如果表中记录已存在起始日期为20060101的数据,再插入或修改2005年的数据,那是不充许的,也不会成功。
这种表由“截止日期”和数据的特定外键组成主键,保证某一时间内(日期范围)它的记录项是唯一的。
当某一数据变动了,如果表中有相应的特定记录,它将相应记录载止日期为“Current”的记录进行修改,把载止日期的值改为最新数据的起始日期的前一天;表中存在还是不存在的相应记录,最后都插入一条新记录,新记录的截止日期值为默认值,也就是“Current”。
当某一属性在某一时间之后停止使用了,将相应记录载止日期的“Current”值修改为截止有效日期。那样,查询该日期之后日期的数据中,不会再有反映这个属性的数据。其实质是取消了该记录的“Current”影射。
在这里,区划码的数据最旧的起始日期为20020331,所以只能查自2002年3月31日起的数据,再前的查不到了。所以初始化数据库时,应考虑把它延后至某一个日期,比如19491001,不过那时香港还不是特别行政区哪。
这篇介绍了数据库设计的核心,是引入了“稳定数据日期”概念。
USE [RegionalCodeWorks]
GO
/****** 对象: Schema [Program] 脚本日期: 09/17/2006 19:14:49 ******/
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/17/2006 19:14:49 ******/
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/17/2006 19:14:49 ******/
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/17/2006 19:14:49 ******/
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/17/2006 19:14:49 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Action')
EXEC sys.sp_executesql N'CREATE SCHEMA [Action] AUTHORIZATION [dbo]'
USE [RegionalCodeWorks]
GO
/****** 对象: Default [Base].[RegionalDateDefault] 脚本日期: 09/17/2006 19:14:49 ******/
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
这一篇主要说基本信息也就是“[Base].区划码”和“[Edit].区划码情况”的设计,以及对它操作的函数“[FUNCTION].区划码情况”和存储过程“[Program].增添记录”。
当本“系列”结束时,我将提供整个数据库的生成脚本和程序代码供下载。
设计数据库时,针对某一信息建表,我把握的准则是先弄清楚那些是主体表,那些是附属表。主体是信息承载的宿主,附属是为方便查询主体信息引进的。
如这里的表,图中的“基本信息”就是主体的,“属性信息”就是附属的。没有附属的也可以,只是查询起来不方便。因为附属的是为查询服务的,在实际使用中,附属的反客为主,查询中基本是针对附属表及相关的函数、存储过程;信息能量的利用发掘,也体现在附属的上面。主体的通常只在数据编辑时用到。
设计初期重点考虑主体表。附属表可以在使用中再设计再增加也不迟。在我的设计过程中,开始也仅只有文首提到的四个对象。在这些文中,我尽可能反映我的设计过程的原貌,让各位能够把握这个过程中我的思维。原本也有把自己想法记载下来的目的。
△
针对数据本身,也要分析主体和附属。附属的作用于主体,分开处理。看过网上的一些关于数据库设计方面的文章,针对不同的场境应用不同的模式,我记不住,也悟不透,只好还是跟着感觉走。
下面入“正题”。
△
建库RegionalCodeWorks,Works尾缀吸收了SQLServer2005的示例命名做法,加上它觉得整个库都有生机了,特舒服。
脚本是针对我的机器的。如使用这个脚本要修改成本地参数,最好在Management上直接建。
USE [master]
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'RegionalCodeWorks')
BEGIN
CREATE DATABASE [RegionalCodeWorks] ON PRIMARY
( NAME = N'RegionalCodeWorks', FILENAME = N'g:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATARegionalCodeWorks.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'RegionalCodeWorks_log', FILENAME = N'g:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATARegionalCodeWorks_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
END
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'RegionalCodeWorks')
BEGIN
CREATE DATABASE [RegionalCodeWorks] ON PRIMARY
( NAME = N'RegionalCodeWorks', FILENAME = N'g:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATARegionalCodeWorks.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'RegionalCodeWorks_log', FILENAME = N'g:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATARegionalCodeWorks_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
END
△
建命名空间(也就是架构):
以下的脚本可以直接使用了。
USE [RegionalCodeWorks]
GO
/****** 对象: Schema [Program] 脚本日期: 09/17/2006 19:14:49 ******/
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/17/2006 19:14:49 ******/
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/17/2006 19:14:49 ******/
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/17/2006 19:14:49 ******/
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/17/2006 19:14:49 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Action')
EXEC sys.sp_executesql N'CREATE SCHEMA [Action] AUTHORIZATION [dbo]'
△
建默认值:
USE [RegionalCodeWorks]
GO
/****** 对象: Default [Base].[RegionalDateDefault] 脚本日期: 09/17/2006 19:14:49 ******/
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
△
建自定义数据类型:
USE [RegionalCodeWorks]
GO
/****** 对象: UserDefinedDataType [Base].[RegionalDate] 脚本日期: 09/17/2006 19:14:50 ******/
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/17/2006 19:14:49 ******/
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
GO
/****** 对象: UserDefinedDataType [Base].[RegionalDate] 脚本日期: 09/17/2006 19:14:50 ******/
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/17/2006 19:14:49 ******/
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
△
建表[Base].区划码
USE [RegionalCodeWorks]
GO
/****** 对象: Table [Base].[区划码] 脚本日期: 09/19/2006 19:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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]
GO
EXEC sys.sp_bindrule @rulename=N'[Base].[RegionalCodeRule]', @objname=N'[Base].[区划码].[区划码]' , @futureonly='futureonly'
GO
/****** 对象: Table [Base].[区划码] 脚本日期: 09/19/2006 19:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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]
GO
EXEC sys.sp_bindrule @rulename=N'[Base].[RegionalCodeRule]', @objname=N'[Base].[区划码].[区划码]' , @futureonly='futureonly'
△
建表[Edit].区划码情况
USE [RegionalCodeWorks]
GO
/****** 对象: Table [Edit].[区划码情况] 脚本日期: 09/19/2006 19:04:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Edit].[区划码情况](
[ID] [int] IDENTITY(1,1) NOT NULL,
[区划码ID] [smallint] NOT NULL,
[截止日期] [Base].[RegionalDate] NOT NULL,
[名称] [nvarchar](100) COLLATE Chinese_PRC_CI_AS 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]
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
ALTER TABLE [Edit].[区划码情况] WITH CHECK ADD CONSTRAINT [FK_区划码情况_区划码] FOREIGN KEY([区划码ID])
REFERENCES [Base].[区划码] ([区划码ID])
GO
ALTER TABLE [Edit].[区划码情况] CHECK CONSTRAINT [FK_区划码情况_区划码]
GO
ALTER TABLE [Edit].[区划码情况] WITH CHECK ADD CONSTRAINT [CK_区划码情况] CHECK (([截止日期]>=[起始日期]))
GO
ALTER TABLE [Edit].[区划码情况] CHECK CONSTRAINT [CK_区划码情况]
GO
/****** 对象: Table [Edit].[区划码情况] 脚本日期: 09/19/2006 19:04:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Edit].[区划码情况](
[ID] [int] IDENTITY(1,1) NOT NULL,
[区划码ID] [smallint] NOT NULL,
[截止日期] [Base].[RegionalDate] NOT NULL,
[名称] [nvarchar](100) COLLATE Chinese_PRC_CI_AS 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]
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
ALTER TABLE [Edit].[区划码情况] WITH CHECK ADD CONSTRAINT [FK_区划码情况_区划码] FOREIGN KEY([区划码ID])
REFERENCES [Base].[区划码] ([区划码ID])
GO
ALTER TABLE [Edit].[区划码情况] CHECK CONSTRAINT [FK_区划码情况_区划码]
GO
ALTER TABLE [Edit].[区划码情况] WITH CHECK ADD CONSTRAINT [CK_区划码情况] CHECK (([截止日期]>=[起始日期]))
GO
ALTER TABLE [Edit].[区划码情况] CHECK CONSTRAINT [CK_区划码情况]
△
建函数[FUNCTION].区划码情况,作用是查询指定日期的[Edit].区划码情况中的所有数据。
USE [RegionalCodeWorks]
GO
/****** 对象: UserDefinedFunction [FUNCTION].[区划码情况] 脚本日期: 09/19/2006 19:06:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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.查询时间
)
GO
/****** 对象: UserDefinedFunction [FUNCTION].[区划码情况] 脚本日期: 09/19/2006 19:06:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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.查询时间
)
△
建存储过程[Program].增添记录
USE [RegionalCodeWorks]
GO
/****** 对象: StoredProcedure [Program].[增添记录] 脚本日期: 09/19/2006 20:49:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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
GO
/****** 对象: StoredProcedure [Program].[增添记录] 脚本日期: 09/19/2006 20:49:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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
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
在下一文中,将回到程序的设计,先将网上的数据导进数据库再说。