你的位置:首页 > 数据库

[数据库]数据库设计(5/9):存储过程名称


对于设计和创建数据库完全是个新手?没关系,Joe Celko, 世界上读者数量最多的SQL作者之一,会告诉你这些基础。和往常一样,即使是最专业的数据库老手,也会给他们带来惊喜。Joe是DMBS杂志是多年来最受 读者喜爱的作者。他在美国、英国,北欧,南美及非洲传授SQL知识。他在ANSI / ISO SQL标准委员会工作了10年,为SQL-89和SQL-92标准做出了杰出贡献。


 Joe Celko会谈下数据库设计里存储过程和它的位置。他所写的是令人深思的东西,即使是有经验的数据库开发人员。

在第一篇,我们因它们是什么并区分它们命名数据元。在第二篇,我们用SQL里给我们的数据类型和简单的行或列约束来模型化数据元。在第三篇,我们把这些行放入表成为实体,关系和辅助数据。在第四篇,我们有了基础表,把它们连接在一起,增加从索引,并创建了视图。

这应该会给你万事俱备,却是错误的感觉。在一个非常简单的数据库里,这是对的。但在一个真正的数据库有更多的架构来考虑。在这些架构外的其他东西事:游标(cursors)、触发器(triggers)和存储过程(stored procedures)。有更多像整理、翻译,特权和诸如此类的东西。我将只处理这3个东西——游标(cursors)、触发器(triggers)和存储过程(stored procedures)——我只用通常的方式命名。尽管ANS/ISO标准里,T-SQL和其他产品可以获得更高的占有欲。理由很简单:这些东西建立在早期SQL产品使用的现有文件系统上。这些程序结构是用来弥补在早期产品里缺少申明式代码。这些供应商有锁在今天“代码博物馆”不能地址他们客户群的用户。

存储过程名称(Procedure Headers)

SQL允许存储过程代码模块在架构里保存。同时在标准SQL里有SQL/PSM语言,你会使用像T-SQL的专门语言。这些语言通常是Algol家族的成员;那就是说他它们有IF-THEN-ELSE,WHILE循环和有BEGIN-END作用域的代码块。

这些专用语言的大多数从未想用做程序开发。对于T-SQL的首要规则(The rules of thumb)是不写超过50行的的过程,且不使用PRINT。但事实上,你可以避免所有的面向过程,每个表像文件和代码一样对待,好像数据库是个过程化的文件系统。如果你喜欢疼痛,大可敲个钉子到你身体,所以不用纠结。

存储过程的目的更像个视图。它们授予所有用户在所有时间会用同样的方式做同样的工作。视图封装了一个查询并给它一个名称,因此创建了一个虚拟表。存储过程用同样的方式封装了UPDATE,INSERT,DELETE和SELECT,但增加了参数。

在存储过程名称里一个参数(parameter )就是个“持有人(place holder)”,参数值(argument )是传给存储过程的实际值。T-SQL参数过去只限制于简单的标量值。现在,它们可以是表值和

编译器读取参数值,并检查数据类型,范围和确保它是有效的其他事项。编译器会做比你想象还多的事。看下这个T-SQL:

1 SELECT ISDATE ('2010-01-01'); -- TRUE2 SELECT CAST ('2010-01-01' AS DATE); -- no problem3 SELECT ISDATE ('010-01-01'; --TRUE4 SELECT CAST ('010-01-01' AS DATE); -- error

同事,浮点表示法有点意思。如果你以DECIMAL或FLOAT转化指数计数法,肯定没问题:

1 SELECT CAST (62.3E8 AS DECIMAL(18,5)); -- returns 6230000000.000002 SELECT CAST (62.3E8 AS FLOAT); -- returns 6230000000

但现在把字符串的指数计数法,尝试转化它为DECIMAL或FLOAT,你肯定会出现问题:

1 SELECT CAST ('62.3E8' AS DECIMAL(18,5)); -- error2 SELECT CAST ('62.3E8' AS FLOAT); -- returns 6230000000

如果转化字符,你要用这样的指数代码:

1 SELECT CAST(CAST ('62.3E8' AS FLOAT) AS DECIMAL(18,5)); -- 6230000000.00000

现在尝试传这些测试值作为参数,看看它们的结果:

1 CREATE PROCEDURE Test2 (@in_test_date DATE,3 @in_test_decimal DECIMAL(18,5))4 AS5 BEGIN6 SELECT @in_test_date, @in_test_decimal;7 END;

EXEC Test '2010-01-01', 62.3E8;

返回值(2010-01-01, 6230000000.00000)和预期的一样。

你不能传表达式作为参数值,但你可以传本地变量,它是表达式值预先设置的。这就是说这个会报错:

EXEC Test '2010-01-01', 62.3 * POWER(10, 8) ;

但这个会成功执行:

1 BEGIN2 DECLARE @local_decimal DECIMAL (18,5);3 SET @local_decimal = 62.3 * POWER(10, 8);4 EXEC Test '2010-01-01', @local_decimal ;5 END;

你会看到新的SQL编程会尝试传递

在参数列表里另一个未使用的功能是默认值。这个语法非常简单。参数声明后一个“=”和一个合适的定值。

如果参数值没有提供,就会使用默认值。

CREATE PROCEDURE Test(@in_test_date DATE = '2010-01-01', @in_test_decimal DECIMAL(18,5) = 0.00000)ASBEGINSELECT @in_test_date, @in_test_decimal;END;

除非你特别分配参数值到参数,它们是从左到右的顺序分配。

EXEC Test; -- returns (2010-01-01, 0.00000)EXEC Test '2010-12-25' -- returns (2010-12-25, 0.00000)EXEC Test @in_test_date = 789; -- returns (2010-01-01, 789.00000)

最后,参数可以用作输出。这就是说它需要在调用的模块里有个本地变量,这样的话,返回值才有地方可去。这是展示这个语法的例子:

 1 CREATE PROCEDURE Test 2 (@in_test_date DATE OUTPUT, 3 @in_test_decimal DECIMAL(18,5) OUTPUT) 4 AS 5 BEGIN 6 SET @in_test_date = '2010-12-25'; 7 SET @in_test_decimal = 789; 8 END;  9 10 BEGIN11 DECLARE @local_date DATE;12 DECLARE @local_decimal DECIMAL(18,5);13 SELECT @local_date, @local_decimal; --returns (NULL, NULL)14 EXEC Test @local_date OUTPUT, @local_decimal OUTPUT15 SELECT @local_date, @local_decimal; -- returns (2010-12-25, 789.00000)16 END;

软件工程基础

关于存储过程标题就讲这些;那存储过程的具体内容呢?嗯,我们现在暂时不讲。我们先讲下原则让,然后再看看特定的工具。我们需要一个高度来看如何编写代码——软件工程(Software Engineering)。

软件工程的基础不在SQL里修改。但现实完全不一样。我们大多数(从学LISP,APL,FP,Haskell或其它外来语言学起,对这些程序员例外)学过从Algol-60进化而来的结构化编程语言。适用于过程化语言的原则同样适用于SQL存储过程。

在近1970年,我们发现我们可以在程序里写出更好(更快,正确,更易维护的)的代码,在代码里有本地代码块规则和代码模块,都是一个入口一个出口。我们避免GO TO语句,并使用简单的一系列控制结构。这是结构化编程的进步。

内聚度(Cohesion)

内聚度是一个模块做且只做一件事会很好:那是逻辑上的内聚性。模块应该高内聚。模块的命名格式应该是“<动作><对象>”,这里“<对象>”是数据模型里特定的逻辑单元,“<动作>”是单一明确的行动。有很多内聚类型。我们从最差到最好对它们排名。

  1. 偶然内聚(coincidental cohesion)是模块部分任意分组。一个偶然模块无关行为的火车残骸。它是“Lady GaGa,鱿鱼和汽车”模块,它们的描述会是复合且复杂的句子。这个在SQL里的最佳例子是OTLT(查询表(One True Lookup Table))设计缺陷。这是它里面有对整个实体架构的查询表。
  2. 逻辑性内聚(logical cohesion)是模块部分按它们逻辑上做同样的事进行分组,即使本质上它们是不同的。在SQL里最常见的例子是在任何表上进行更新、插入或删除的通用模块——在“Lady GaGa,鱿鱼和汽车”上。用动态SQL,
  3. 时间性内聚(temporal cohesion)是模块部分按它们处理时分组。例如,对于整个系统,进行所有初始化工作的模块。
  4. 过程性内聚(procedural cohesion)是模块部分是因为它们总是紧跟特定操作顺序。例如,当一个用户登录到数据库,我们会检查用特权可并记录登录。不把这些事放在各个模块里,在上层的控制下,我们写集成的一块来完成这一切。
  5. 通信性内聚(communicational cohension)是模块部分因它们在同样的数据库元上操作进行分组。例如在一个存储过程里,UPDATE的一系列语句影响同样列,基于先前模块做的事。
  6. 顺序性内聚(sequential cohesion)是模块部分因为从一个部分的输出是另一个部分的输入,像流水线一样。在SQL里,可以看下临时表的使用,在磁带文件系统里用来替换。
  7. 功能性内聚(functional cohesion)是模块只做一件定义明确的工作,像一个数学函数。这才是我们模块里想要的,它是功能性编程语言的基础。没有副作用或外部数据。

如果在你的软件工程课程里错过这些,你可以网上找下它们的具体定义。

耦合度(Coupling)

耦合度是模块之间的相互独立性。如果你的模块需要特定的顺序执行,它们是强耦合度。如果它们之间可以独立运行,可以像乐高一样堆积,它们是松散或弱耦合的。耦合有好几类,从低到高排序是:

  1. 内容耦合(Content coupling)是一个模块的修改依赖于另一个模块的内部操作。控制模块的跳入与调出。在SQL里,用户自定义函数(UDF)的使用和CLR是最好的例子。直到运行的时候你才知道会发生什么。
  2. 公共耦合(Common coupling)是两个模块共用同样的全局数据。这听起来像SQL里的表,但它指的是在公共数据上有2个做同样工作的方法。做同样的工作很快变成几乎一样的工作。
  3. 外部耦合(External coupling)是两个模块共享一个外部强加的数据格式,通信协议,或设备接口。理想的,我们不需要担心外部环境。这是在数据库里,分层架构不做任何形式或包装。
  4. 控制耦合(Control coupling)是一个模块通过传递标记控制执行。直到它的主模块告诉它,你不知道这个模块会做什么。这也是在SQL编程里,使用BIT标记不好的一个原因。
  5. 标记耦合(Stamp coupling)是模块共享复合数据结构,且只使用它的一部分。在SQL里可以使用视图实现。
  6. 数据耦合(Data coupling)是模块共享简单数据元。想下传递参数;这是只共享的数据元。
  7. 非直接耦合(Message Coupling)是最宽松的耦合类型。模块不相互依赖,它们使用公共接口来交换少参数的信息。这更多是面向对象的方法,但你会在触发器、异常处理和其他SQL特性里看到它。

这个在我的《SQL 编程风格》书里关于存储过程编写的章节里会有简单的介绍。同时,你也可以阅读下DeMarco, Yourdon, Constantine, Myers或其它软件工程先驱。这已经不是简单的SQL编程了。在你写任何语言的代码前,这些都是你应该知道的。

好的SQL存储过程

一般而言,好的存储过程是高内聚,低耦合,它不使用控制结构的缺陷,除非是必须的。对于过程化开发人员,这个是意外。理由是尽可能多的把“编程的元素”放入单纯的SQL,这样优化器可以更好的处理代码。

如何实现?下篇会告诉你。

原文链接:

http://www.sqlservercentral.com/articles/Stairway+Series/Procedures+in+Database+design/70891/