开发者社区> 范大脚脚> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

过程化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折(这个不会做广告)。

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

复制代码
BEGIN
OPEN 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语句会如下:

复制代码
BEGIN
UPDATE 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 ALL
4  SELECT .. FROM.. WHERE..;

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

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

避免本地变量

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

复制代码
1  CREATE FUNCTION Foobar (..)
2 RETURNS <data type>
3 AS
4 BEGIN
5 DECLARE @local_return_holder <data type>;
6 SET @local_return_holder
7     = <scalar query>:
8 END;
复制代码

可以更简单:

复制代码
1 CREATE FUNCTION Foobar (..)
2 RETURNS <data type>
3 AS
4 BEGIN
5 RETURN (<scalar query>);
6 END;
复制代码

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

复制代码
BEGIN
SET @local_x = (<scalar query>); -- has to load local variable
..
<statement using @local_x>;
END;
复制代码

可以是:

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

你也可以嵌套调用函数,不用在本地变量里的直接值逐步处理。这个的最好例子是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和XML混用

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



本文转自Woodytu博客园博客,原文链接:http://www.cnblogs.com/woodytu/p/5627423.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
项目中的那些SQL语句规范,你做到了几条?
公司有SQL语句规范的参考文档,这里特别做个笔记。
0 0
存储过程--机房收费系统
<h1> <span style="font-size:24px">1、什么是存储过程?</span><span style="font-size:24px"> </span> </h1> <p></p> <p style="line-height:24px"><span style="font-family:KaiTi_GB2312; font-size:18px"> 定义:</sp
973 0
通过已有SQL语句,生成数据库模型PDM
  前提: 我用的是 PowerDesing15.1   步骤如下: 第一步:File->ReverseEnginner->Databases...   第二步:选择适用你SQL文件数据库类型,->点击“确定” 第三步:点击实用“Usingscript files:”选项,然后点击,选中你SQL文件所在位置,然后点击确定,等待所有OK
695 0
【自然框架】之通用权限:用PowerDesigner重新设计了一下数据库,有ER图和表关系图
好像以前做的那个数据库设计大家都没太看懂,究其原因似乎大家都比较习惯使用PowerDesinger来设计。而我用Excel画出来的图大家看着特别别扭,而且还没有总体的图,也没有ER图,所以大家也就没有心情看了吧。
1169 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载