re: 关于数据库存储过程的一些讨论

newkid

原帖由 dayspring_chen 于 2011-2-14 13:22 发表
呵呵，真理多走一步就是谬误。业务场景永远是最重要的。我的用法其实跟你的反驳并无本质区别。
----------------------------------------------------------------------------------------------------------
表函数主要是一种简化调用的方式：在某些返回结果集确定，中间需要根据规则改变计算关系的情况下用表函数。
这些表函数一定是直接被最终应用直接调用的，一定会避免嵌套调用的混乱局面。
例如我们的通用即席查询：动态sql的骨架是固定的，但其中部分语句是根据规则拼接而成（包括用聚合规则、集合操作），再以中间表、嵌套sql、标题子查询进行串联。这种是无法以bind的方式简单搞定的。
我们是采用global temporary table，将根据规则生成的结果放入中间表（中间表可能有多个），再以表函数形式进行输出。
事物处理是显示的commit和rollback，不会搞到动态sql里去。
---------------------------------------------------------------------------------------------------------
当前主要是用在一些BI类别的系统中，最近也想进行另外一种实践方式用于交易
即把元数据与执行数据库（目标库）分开，元数据里存储规则，在中间件里根据请求格式去读取规则，再根据目标库的类型（oracle、DB2、SYBASE、MYSQL、LDAP）等去形成持久化语句，期间实现数据检查、业务规则检查、日志、出错处理。这样元数据就跟配置文件类似，但能获得一些配置文件没有的优势。
总而言之，oracle到一定地步，sql与存储过程并无本质之区别。重要的是团队能力也业务场景。
---------------------------------------------------------------------------------------------------------

我很不赞同你的一些做法。
2.为了更方便不同客户端调用，可以用表函数作为访问方式（以自治事务进行DML，用表函数返回状态信息）
为什么用表函数就更方便？
你全都是用表函数返回嵌套表，然后用SELECT...FROM TABLE(...)? 这样有什么好处？
如果要返回结果集，可以打开一个REF CURSOR并返回。
自治事务要特别小心。基本上，除了写日志，其他的应用都是错的。你这样做，将不得不把COMMIT放到你的函数里。这是不提倡的，COMMIT最好交给客户端去处理。另外你的函数将很难模块化，因为每个都是不同的事务！比如函数A调用函数B, A和B应该在同一个事务里，你如果在A,B里用了自治事务，全乱套了。

3.存储过程里面用动态sql.....
动态SQL是应该尽力避免的。它的应用范围及其有限，一般是为了组合灵活的查询条件。如果你把事务处理也用动态SQL实现，一定有问题。
动态SQL程序很难读，很难维护，无法在编译的时候发现语法问题；因为多隔了一层，效率也要打个折扣。
你可以把业务逻辑中一些灵活的部分抽出来进行参数化，也就是通过配置的方式来进行控制，但是“执行动作”应该都是静态代码，它只是根据不同的配置走不同的分支。
如果你的需求决定了有很多雷同的代码，那么你可以利用你的元数据来生成静态代码，这样节省了人工，但是最终编译、执行的代码还是静态的。
4.通过维护元数据表来进行....
如果元数据表是我上述提到的配置表，这样的做法是可取的。

你是将结果放到临时表，然后把临时表放到嵌套表，然后函数返回嵌套表？为什么不用返回REF CURSOR游标（或动态游标）的办法？大部分情况下根本不需要用临时表。
你的应用果然就是我说的动态组合查询条件，但是看起来很复杂，要记住这样的程序维护代价是很高的。
曾经有人ASKTOM上问，如果动态查询条件的组合有50种，他是倾向写50个SQL还是写动态SQL? TOM的回答是写50个静态SQL. 你可以不同意他的做法，但是动态SQL的代价可见一斑。

"最近也想进行另外一种实践方式用于交易...."
你这种实践方法已经是放弃了存储过程，你实际上想要实现的是中间层的RULE ENGINE，和存储过程区别大了去。
我的理念：业务规则就是代码，把代码作为元数据那是ORACLE干的事，你在应用层想要重新做一遍，不仅毫无必要，而且效率低下，难以维护。

newkid

我在文档《面向程序员的数据库访问性能优化法则》http://blog.csdn.net/yzsind/archive/2010/12/06/6059209.aspx
里写了存储过程性能的优点，主要是减少网络交互。
以下存储过程存在问题的详细描述：
a、不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼容多种数据库时最好不要用存储过程。

我上次已经说得很清楚了，现在抄一段TOM的书：Oracle 9i10g编程艺术
1.3.4 数据库独立性?
至此，你可能想到这一节要讲什么了。我提到了其他的数据库，也谈到各个数据库中会以不同的方
式实现特性。除了一些只读应用外，我的观点是：要构建一个完全数据库独立的应用，而且是高度可扩缩
的应用，是极其困难的。实际上，这几乎不可能，除非你真正了解每个数据库具体如何工作。另外，如果
你清楚每个数据库工作的具体细节，就会知道，数据库独立性可能并不是你真正想要的（这个说法有点
绕！）。
例如，再来看最早提到的资源调度例子（增加FOR UPDATE 子句之前）。假设在另一个数据库上开发这
个应用，这个数据库有着与Oracle 完全不同的锁定/并发模型。我想说的是，如果把应用从一个数据库移
植到另一个数据库，就必须验证它在完全不同的环境下还能正常地工作，而且为此我们要做大幅修改！
假设把这个资源调度应用部署在这样一个数据库上，它采用了阻塞读机制（读会被写阻塞）。现在业
务规则通过一个数据库触发器实现（在INSERT 之后，但在事务提交之前，我们要验证表中对应特定时间片
的记录只有一行，也就是刚插入的记录）。在阻塞读系统中，由于有这种新插入的数据，所以表的插入要串
行完成。第一个人插入他（她）的请求，要在星期五的下午2:00 到下午3:00 预订“房间A”， 然后运行
Begin
or x in (select * from t)
loop
insert into t values (x.username, x.user_id, x.created);
end loop;
end;
insert into t select * from t;
71
/ 849
一个查询查看有没有重叠的预订。下一个人想插入一个重叠的请求，查找重叠情况时，这个请求会被阻塞
（它发现有新插入的数据，但要等待直到这些数据确实可以读取）。在这个采用阻塞读机制的数据库中，
我们的应用显然可以正常工作（不过如果两个人都插入自己的行，然后试图读对方的数据，就有可能得到
一个死锁，这个概念将在第6 章讨论），但不能并发工作，因为我们是一个接一个地检查是否存在重叠的
资源分配。
如果把这个应用移植到Oracle，并简单地认为它也能同样地工作，结果可能让人震惊。由于Oracle
会在行级锁定，并提供了非阻塞读，所以看上去一切都乱七八糟。如前所示，必须使用FOR UPDATE 子句来
完成串行访问。如果没有这个子句，两个用户就可能同时调度同一个资源。如果不了解所用数据库在多用
户环境中如何工作，就会导致这样的直接后果。
将应用从数据库A 移植到数据库B 时，我时常遇到这种问题：应用在数据库A 上原本无懈可击，到了
数据库B 上却不能工作，或者表现得很离奇。看到这种情况，我们的第一个想法往往是，数据库B 是一个
“不好的”数据库。而真正的原因其实是数据库B 的工作方式完全不同。没有哪个数据库是错的或“不好
的”，它们只是有所不同而已。应当了解并理解它们如何工作，这对于处理这些问题有很大的帮助。将应用
从Oracle 移植到SQL Server 时，也会暴露SQL Server 的阻塞读和死锁问题，换句话说，不论从哪个方向
移植都可能存在问题。
................

b、学习成本高，DBA一般都擅长写存储过程，但并不是每个程序员都能写好存储过程，除非你的团队有较多的开发人员熟悉写存储过程，否则后期系统维护会产生问题。
这里说的学习成本高是一种相对开发人员的说法，一般程序员在学校里更多掌握的是常用开发语言，很少有人掌握PL/SQL，所以当你大量采用存储过程时，招来的人都需要培训PL/SQL。
我以前的一个团队有6个人，4个人都熟悉PL/SQL，C/S应用，因此很多业务逻辑都采用了存储过程，开发速度也不错，但后来换成B/S架构，系统也更复杂了，有20个人都不会PL/SQL，在一次系统重构时将一些业务逻辑的存储过程全换了，因为这些逻辑性能不是问题，取消存储过程后让普通程序员调试更简单，代码管理也更简单。
存储过程的语法、事务控制、调试方法这些与普通开发语言不同，当大量程序员不熟悉存储过程时架构师也不愿意把原本简单的架构搞得更复杂。

实际上，如果已经掌握了SQL和其他开发语言，学习PLSQL的成本并不高，几天之内就可以搞定。如果不习惯SQL的思维方式，一切都想用嵌套循环来搞定，那么就有一个适应的过程。但是学习SQL的付出是值得的，如果你依靠SQL生成器，那就写不出好的应用。
学习PLSQL的成本也是值得的。TOM有句话：applications come, applications go, data stays forever. 而PLSQL是最靠近数据的一层，从来没有一种语言能够这样和SQL无缝结合，如果你采用PLSQL开发，你的代码就有可能stays forever. 而那些华丽的包装器，往往只是各领风骚若干年，今天的新潮明天就成老土，你可能不得不反复投资才跟得上。PLSQL看似粗陋，实则强大，具有顽强的生命力。
关于这个话题我以前有过一个超长贴：
http://www.itpub.net/thread-1088197-1-1.html

c、业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试成本。
业务逻辑集中管理会更易于维护与调试，你很难做到业务逻辑都放在存储过程里，比如关于一些客户输入数当据的简单校验，会话数据的校验，应用服务器缓存数据的校验。

你说的这些，并不能取代数据库层的校验。类似以前讨论过的，应用程序校验过了，还要不要外键？数据库不是只为一个应用服务的，数据库应该有自己的校验，不管前端已经校验过多少次。前端的校验只是为了改善用户体验，及时拦截无效数据，最后一道防线设在数据库，如果只设一层，那应该放弃的也是其他层。

d、存储过程和常用应用程序语言不一样，它支持的函数及语法有可能不能满足需求，有些逻辑就只能通过应用程序处理。
这个不太想举例，因为环境不一样，每系统的需求也不一样，如AES高级加密算法，也许这些在应用程序中已经有好的实现，那就不需要在存储过程中再搞一个了。

我也同意加密可以在数据库之外实现，这不属于业务逻辑。如果你应用程序还没有实现，而数据库又能做到，放到存储过程也未尝不可。

e、如果存储过程中有复杂运算的话，会增加一些数据库服务端的处理成本，对于集中式数据库可能会导致系统可扩展性问题。
select sum(object_id) c1 from all_objects;
select sum(power(object_id, 1.1)) c1 from all_objects;
可以试试这两条SQL的性能。power带小数的运算性能不好，如果在数据库中有大量这样的运算，CPU很容易成为瓶颈，而应用服务器可以更好的扩展，而且更善于做CPU运算的优化，所以这类会让CPU成为瓶颈的运算不宜放在数据库端处理。当然，这种需求不常见。

据我的经验，密集型的运算都是很简单的四则运算而已，你这类需求及其罕见，就是有也不会密集出现，因此我放到数据库实现也不用担心CPU问题。

f、为了提高性能，数据库会把存储过程代码编译成中间运行代码(类似于java的class文件)，所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后，存储过程需要重新编译才能生效，在24*7高并发应用场景，一般都是在线变更结构的，所以在变更的瞬间要同时编译存储过程，这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)。
这个问题只是针对高并发应用的系统，如果你的系统并发很低，每秒只有几次或几十次调用，可以完全不用考虑这个问题。

等会我就贴出顶楼文章的翻译，这个问题ORACLE已经完美解决了。
难道把代码写到应用服务器，你就不用编译？

个人观点：普通业务逻辑尽量不要使用存储过程，定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。存储过程可以快速解决问题，但是移植性、维护性、扩展性不好，它有时会约束软件的架构，约速程序员的思维，在你的系统没有性能问题时不建议用存储过程。如果你要完成的功能只是一次或有限次的工作，如数据订正、数据迁移等等，存储过程也可以拿上用场。
如果你的系统很小，并且有50%的开发人员熟练掌握PL/SQL，人员结构稳定，那存储过程可以减少很多代码量，并且性能不错。当系统变复杂了，开发人员多了，存储过程的弊端就会呈现，这时你需要痛下决心了。

开发人员多了，其他语言就不存在管理问题？

newkid

顶楼提及的TOM写的文章翻译已经登出：
http://www.itpub.net/thread-1396183-1-1.html

qingyun

newkid兄，真知灼见，我也和您观点相同；

不过现在的潮流好像确实是把业务在中间层的java或C#用各种框架动态生成一坨坨SQL和数据库交互；


我做项目，基本是滥用PL/SQL ;所有业务都用存储过程写，尽量避免游标，滥用各种oracle特有的SQL，滥用分析函数；
如果不用存储过程，我都不知道高并发情况下，如何防止住各个地方隐藏的并发脏数据漏洞；

其他外围的功能，我全部用PL/SQL ;
比如：导出excel,xml，调用webService ,tcp/ip通讯；调用dll等等，我全部用oracle的存储过程实现，目的只有一个：
就是不受前台语言限制，方便移植；前台N种语言任意更换，都很easy，因为前台只是一个shell.

[ 本帖最后由 qingyun 于 2011-2-15 02:58 编辑 ]

newkid

原帖由 qingyun 于 2011-2-15 02:55 发表
newkid兄，真知灼见，我也和您观点相同；

不过现在的潮流好像确实是把业务在中间层的java或C#用各种框架动态生成一坨坨SQL和数据库交互；


我做项目，基本是滥用PL/SQL ;所有业务都用存储过程写，尽量避免游标，滥用各种oracle特有的SQL，滥用分析函数；
如果不用存储过程，我都不知道高并发情况下，如何防止住各个地方隐藏的并发脏数据漏洞；

其他外围的功能，我全部用PL/SQL ;
比如：导出excel,xml，调用webService ,tcp/ip通讯；调用dll等等，我全部用oracle的存储过程实现，目的只有一个：
就是不受前台语言限制，方便移植；前台N种语言任意更换，都很easy，因为前台只是一个shell.

我喜欢你这“一坨坨”的说法！确实有时候臭不可闻，哈哈！
潮流不说明什么，大部分跟潮流的人都是稀里糊涂的。

newkid

关于AES加密，随便GOOGLE了一下：

http://download.oracle.com/docs/ ... b14258/d_crypto.htm

DBMS_CRYPTO provides an interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs running network communications. It provides support for several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm. AES has been approved by the National Institute of Standards and Technology (NIST) to replace the Data Encryption Standard (DES).
......

虽然此类操作比较昂贵，但如果非密集型调用，我还是更愿意放到数据库里来实现。

newkid

性能如何？马上做个测试：

CREATE OR REPLACE FUNCTION f_aes(p_str IN VARCHAR2)
RETURN RAW
AS
BEGIN
   RETURN DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (p_str,'AL32UTF8'),DBMS_CRYPTO.ENCRYPT_AES256+ DBMS_CRYPTO.CHAIN_CBC+ DBMS_CRYPTO.PAD_PKCS5,HEXTORAW('1A32602CA9DC740A53F3E9B7151BCA5BC040835F531D3DDE85869C05B3447FF0'));
END f_aes;
/


CREATE TABLE TEST_AES (OBJECT_NAME VARCHAR2(30),ENC_NAME RAW(32));

INSERT INTO TEST_AES
SELECT OBJECT_NAME,f_aes(OBJECT_NAME)
   FROM ALL_OBJECTS;

55868 rows created.

Elapsed: 00:00:10.44

偶尔用用还是没关系的。你可别说淘宝的每笔交易都要用AES加密。

〇〇

nice 一坨坨

atgc

想要用"标准SQL" 去适应不同数据库,我觉得是很难的事情,基本不太可能吧
比如分页,不同的数据库不同的语法,没有标准

yzsind

原帖由 atgc 于 2011-2-15 08:41 发表
想要用"标准SQL" 去适应不同数据库,我觉得是很难的事情,基本不太可能吧
比如分页,不同的数据库不同的语法,没有标准

从DBA的角度看确实很麻烦，但是在架构师眼里不是问题，因为很多系统的底层框架就支持了不同数据库的分页，程序员只要给出SQL,底层框架自动会转换成不同数据库的分页语法。实际上很多实现跨平台数据库的解决方案都需要有底层框架的支持，程序员只要按规范做就OK了。
很多系统要求跨数据库平台都是出于客户需求。如，你一个软件开发出来，软件卖5万元一套，数据库支持的SQLSERVER（2万元），当有一天你的新客户说我不想采购SQLSERVER，因为我已经有了ORACLE数据库，而且我有ORACLE专业DBA维护系统，如果你的软件不支持ORACLE那我就选择其它软件。OK，软件开发商就会研究如何让他的软件适应于ORACLE。当客户越来越多了，为了打开市场，他可能又开始支持DB2,MYSQL之类的数据库，在这样的发展过程中架构师首先想到的是减少非标准SQL，并且通过底层架构去屏蔽不同数据库的语法，最终实现了跨数据库支持。
存储过程由于语法差异太大，而且是放在数据库系统里，所以从架构上很难支持跨平台。