你的位置:首页 > 数据库

[数据库]数据库设计(6/9):存储过程主体


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


在上一篇文章里已经介绍了SQL Server里的存储过程标题,Joe会继续谈下存储过程内容的话题。在这篇文章里,他会概况谈下作为过程化语言的T-SQL的局限性,当决定如何使用它们时要记住那些。

在第一篇到第四篇,我们创建了表,架构的基础和可视化。但我们还没结束,因为架构不止这些。在一个真正的数据库里,有更多的结构需要考虑。在这些其它架构层级外的东西是:游标,触发器和存储过程。还有其它像核对,翻译,特权(collations, translations, privileges)和像这样的东西。我只谈这三个东西——游标,触发器和存储过程——我只用最常规的方式命名。5-SQL和其它产品可以有更高的专利,不管ANSI/ISO标准。理由很简单:这些东西是建立在早期SQL产品使用的现有文件系统之上。这些过程化的结构是用来弥补早期产品声明式代码的缺陷。供应商有锁在“代码博物馆”里的用户,不会放弃他们的客户基础。

在第5篇,对于存储过程,我们讨论存储过程标题有什么和它是如何工作的,就像一个黑盒子。在第6篇,我们到黑盒子里面看看。

过程化SQL

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

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

T-SQL是个一次通过的编译器。这是你必须前置本地变量,使用@(@标志,“蜗牛(snail)”或“小蜗牛(petite escargot)”)的参数,@@是系统级的变量,#(井号)和##是临时表。多通道编译器创建符号表,然后用每个通道探索程序对象的东西。当它第一次找到它时,一次通过编译器需要用新的符号来告知做什么。一旦它们传过来,因此@表示“为我分配本地存储”,@@表示“在程序外找我,对它我是全局的”,#表示“对于当前会话在tempdb里创建我”,##表示“在临时表里创建并保持我”。其它的一起是假定在DDL里定义。

SQL不计算

你不能期望T-SQL来做过程化代码的优化。那需要多通道。例如,大部分FORTRAN编译器使用代数学写入来进行计算上的优化。来自IBM的F和G系列可以给学生完整错误信息的快速编译器,慢但可以为产成品优化性能。

1960年期间一个经典的IT故事是,IBM的国防部(DoD (Department of Defense) )测试和通用计算机的FORTRAN编译器。IBM编译器运行了很长时间,生成一个压缩的可执行模块,当运行的时候,很快得出正确答案。通用编译器运行了很长时间,生成了很小的可执行模块,包含一个WRITE语句,立即打印出正确的答案。啥问题?问题是涉及函数和它们的取消退出逆转。关键是浮点取整错误。通用FORTRAN编译器成功配对函数和它们的逆向,完成代数并并以常量生成答案。

在T-SQL里避免浮点数和实数。在T-SQL里有同样的数据类型,但在标准SQL里没有。问题是浮点数需要特定来处理避免取整错误和比较。有个它们需要调用近似数字数据类型的特定原因。这个特定处理需要要么是软件內建的,要么是硬件的一部分,这就说你需要浮点处理器。同样,你的老板不会为你的桌面安装游戏显卡。商业应用服务器通常不需要这些昂贵的功能,不管你在工作的时候花多少时间在玩Halo或Doom游戏上。

即使芯片便宜,你也不能合理期望期望T-SQL来做数学上优化的事。SQL是个数据检索和管理语言,不是用来计算的。你想要的是写出传给统计软件包来获得数据的好查询,一个报表或其他特定工具。

如果需要十进制的地方,那么就使用DECIMAL数据类型。它们可以很好处理。窍门就是给你自己足够的十进制控件来获得正确的整数。那意味着你需要知道你行业的标准。尤其是,如果你用欧元,你需要知道“欧元三角(euro triangulation)”,货币转换和记账规则。

最好亲自做下代数,让算法尽可能简单。这样建议也适用于字符和时间数据。

T-SQL有基于C的函数库。这是为什么可以使用%来代替标准mod函数的原因。

SQL不用来显示

再次强调,SQL是数据检索和管理语言,不是用来做前端显示的。在SQL数据类型里一起拿到数据,把它们“FQ(over the wall)”传给前端程序,例如报表编辑器和图形包,这样看起来会更好。

但是因为过程语言是焊接到它们的文件,程序员写单片程序成长起来。COBOL只是字符串和显示模板。FORTRAN有它自己的格式化语句。BASIC版本有使用#和其它符号的图片选项。即使像C的低级语言,在它的printf函数里有精确的格式化选项!

长期的过程化语言编程后,对于很多程序员,分层的概念非常困难。事实上,在现在,你还是可以听到抗议:“在数据库我就可以完整这个并节约时间”。

有时候拿是对的。但大多时候,这不会节约。显示格式化会从在基本列上使用索引阻止优化器。前端然后会拆回格式化列到它们的源数据或另一个格式。比起在它们的列里有基本数据类型的简单列,真正的损失是这更难维护。

让我给你2个常见的例子。使用专门的CONVERT()函数把时间数据转为字符来显示。让程序为你做这个;它们有函数库来做这个。你不用担心国家设置或正确的取整(可以是通过程序设计决定的程序)。当你有DATEPART()和CAST()时,CONVERT()的最坏使用是对字符处理。可以看下两个日期转为字符串,然后比较字符串。

第2个常见例子是从姓和名组合为姓名。这会阻止在姓列上的索引使用,会给前端带来可用空间和规则的重格式化问题。你会看到翻转名字顺序(名,姓)的前端代码

基本声明式编程启发法(Basic Declarative Programming Heuristics)

结构化编程实际上有修正性的数学证据。你可以且应该看下Dijkstra, Wirth and Manna。这实际上是会帮你编程的理论。声明式编程还没到那个点。但可以给你写启发。当你看到一个特定情形时可以尝试些事情;它们不是宇宙法则,就像精明投资者的押注。

关于这个话题有2个系列(看下下面参考文章)可以给你过程化的例子,半过程化和声明式编程风格。但现在,让我给你有帮助的“高水平提示”的快速清单。

倾向一句顶多句

在一个没有使用T-SQL流程控制的一个SQL语句里,你可以做的更多工作,代码越好,工作越顺。因此,如果你的存储过程主体有两个或更多引用到同个表,你大可以组合它们并一次访问那个表。

你可以使用CASE表达式来避免很多的IF-THEN-ELSE控制逻辑。在CASE表达式前,是应用逻辑到SQL的表达式。经典的例子是多年来Sybase/SQL服务器类一部分的UPDATE语句。你有个书店,想修改书的价格。超过25美元的书上涨10%(这个会做广告),低于25美元打85折(这个不会做广告)。

经典的在伪代码里,结构化编程的答案如下:

BEGINOPEN FILE (Bookstore);READ (price) FROM Bookstore;WHILE NOT EOF (bookstore)DO BEGIN  IF price < 25.00  THEN UPDATE Books SET price = price * 1.10  ELSE UPDATE Books SET price = price * 0.85;  FETCH NEXT Bookstore;  END IF;END WHILE;CLOSE FILE (Bookstore);END:

很容易把伪代码准换为游标。纯粹的SQL语句会如下:

BEGINUPDATE Books  SET price = price * 1.10 WHERE price < 25.00;UPDATE Books  SET price = price * 0.85 WHERE price >= 25.00;END;

但这不对!如果一本书现在售价是24.95美元。当第一个UPDATE语句执行后,会是27.45美元。但当我们执行第2个UPDATE时,最后的价格会是23.33美元。这不是我们想要的。交换下UPDATE语句也没用;在顶部的书会更新2次。

这是对游标的经典异议。在那些日子里,对于这类问题,我们有各类可怕的多个表扫描存储过程。现在,我们有了CASE表达式,它是声明式,做一次表扫描。

 UPDATE Books  SET price    = CASE     WHEN price < 25.00     THEN price * 1.10     ELSE price * 0.85     END;

这个启发式有个部分:

  1. 在多个语句里查找出现的同样表;它们是可以组合一起的。
  2. 如果用IF-THEN-ELSE控制语句的话,可以在单个语句里用CASE表达式替换分支。

同样的启发式适用于INSERT INTO语句。这个的一个格式是插入初始的一些行,随后是选择的一些行。结构如下:

1 INSERT INTO Foobar (..) VALUES(..);2 INSERT INTO Foobar SELECT .. FROM.. WHERE..;

也可以写成这样:

1 INSERT INTO Foobar (..)2 (SELECT X.* FROM (VALUES (..)) AS X)3 UNION ALL4 SELECT .. FROM.. WHERE..;

当然CASE表达式也可以用在SELECT语句里。

或许这个启发式的最佳例子是MERGE语句,可以让你把INSERT和UPDATE组合为一个语句。这里我不会讨论它,但强烈建议你看下它。

避免本地变量

T-SQL必须分配本地本地变量,它们经常是不需要的。一个常见的模式:

1 CREATE FUNCTION Foobar (..)2 RETURNS <data type>3 AS4 BEGIN5 DECLARE @local_return_holder <data type>;6 SET @local_return_holder7   = <scalar query>:8 END;

可以更简单:

1 CREATE FUNCTION Foobar (..)2 RETURNS <data type>3 AS4 BEGIN5 RETURN (<scalar query>);6 END;

本地变量的其他缺点它们会从优化器隐藏表达式。

BEGINSET @local_x = (<scalar query>); -- has to load local variable..<statement using @local_x>;END;

可以是:

BEGIN..<statement using (<scalar query>)>; --optimizes whole expressionEND;

你也可以嵌套调用函数,不用在本地变量里的直接值逐步处理。这个的最好例子是REPLACE()的如下系列调用:

SET @x = REPLACE (@x, 'a', 'A');SET @x = REPLACE (@x, 'B', 'b');ETC

使用REPLACE (REPLACE..(REPLACE (@x, 'z', 'Z') ..))最多你可以32层。

对此概念有问题,你可以和LISP程序员谈下。这个语言只有嵌套函数调用。

倾向JOIN非Loop

有很多其他技巧可以避免逐行处理。例如,不用说太多,for循环通常可以用join到系列表(Series table)来代替。系列表(Series table)是来一个到上限的一系列整数。

寻找应该在DDL里的东西

在存储过程里IF-THEN逻辑的使用在运行时清理数据,这是你真的在DDL里需要CHECK(),在第一时间就阻止出错。例如:

1 SET T.x = COALESCE (T.x, 0);2 IF (x > 12)..;

这是你需要你在一些列上有默认值和约束的标志。在表里修改“x INTEGER”如下:

 CREATE TABLE T(.. x INTEGER DEFAULT 0 NOT NULL   CHECK (x BETWEEN 0 AND 12),..);

避免CLR和

保持外部语言在架构之外。不添加其他语言来混合的SQL已经很难维护。当你在语句里找到一个CLR模块你不知道,你会怎么办?它们不会遵循例如MOD(),SUBSTRING()和算术取整等同样的定义。最好的例子是C#和VB之间的区别,2个微软专属语言在布尔值表达上却是一致的。

参考文章:

https://www.simple-talk.com/sql/t-sql-programming/procedural,-semi-procedural-and-declarative-programming-in-sql/

https://www.simple-talk.com/sql/t-sql-programming/procedural,-semi-procedural-and-declarative-programing-part-ii/

原文链接:

http://www.sqlservercentral.com/articles/Stairway+Series/70950/