SQL Server存储过程剖析

２００７年８月　广西轻工业　ＧＵＡＮＧＸＩ　ＪＯＵＲＮＡＬ　ＯＦ　ＬＩＧＨＴ　ＩＮＤＵＳＴＲＹ　计算机与信息技术　第８期（总第１０５期）　ＳＱ　Ｌ　Ｓｅｒｖｅｒ存储过程剖析　吴彦国　（河南省轻工业职工大学，河南郑州，４５０００２）　【摘　要】存储过程在ＳＱＬ　Ｓｅｒｖｅｒ数据库中是一个重要概念。存储过程封装了数据库事务处理程序，存储过程运行于主　机并能被调用。因此，在程序开发和设计中，大大减少前台程序开发和设计的工作量；其安全性高，并可设定用户使用权限。　【关键词】　系统存储过程；用户自定义的存储过程；存储过程的传回值　【中图分类号】ＴＰ３９３．１２　【文献标识码】Ａ　【文章编号】１００３—２６７３（２ｏ０７）０８一ｏ０６５一Ｏ２　存储过程在ＳＱＬ　Ｓｅｒｖｅｒ中使用灵活，应用广泛。存储过程　提供了一种封装某一个需要重复执行任务的方法。一旦定义了　一ｓｑｌｓｔａｔｅｍｅｎｔ　—个存储过程之后，在应用程序中就可以对其进行调用。在存　其中，ｏｕｔｐｕｔ，表示此参数是可传回的。ｗｉｔｈ｛ｒｅｃｏｍ—　ｐｉｌｅｌｅｎｃｒｙｐｔｉｏｎ｝选项中的ｒｅｃｏｍｐｉｌｅ表示每次执行此存储过程　时都重新编译一次，ｅｎｃｒｙｐｔｉｏｎ所创建的存储过程的内容会被　加密。　如：　储过程中，不仅可以包含程序流、逻辑以及对数据库的查询，而　且也可以接受参数、输出参数、返回单个或多个结果集。　ＳＱＬ　Ｓｅｒｖｅｒ中存储过程的种类有系统存储过程、扩展存储　过程和用户自定义的存储过程。系统存储过程以ＳＰ一开头，用　来进行系统的各项设定、取得信息，相关管理工作，如ＳＰ—ｈｅｌｐ　就是取得指定对象的相关信息；扩展存储过程以ＸＰ一开头，用　来调用操作系统提供的功能，如ｅｘｅｃ　ｍａｓｔｅｒ～ｘｐ　ｃｍｄｓｈｅｌｌ　’ｐｉｎｇ　１０．８．１６．１’；用户自定义的存储过程，这是我们所指的存储　过程。　表ｂｏｏｋ的内容如下　编号　Ｏ０１　书名　Ｃ语言入门价格　￥３０　００２　ＰｏｗｅｒＢｕｉｌｄｅｒ报表开发￥５２　通过设计自定义用户存储过程，用户可以使其实现强大的　编程功能。使用存储过程可以比单独的ＳＱＬ语句完成更为复　杂的功能，并且系统会对存储过程中的ＳＱＬ语句进行了预编　译处理，使得执行速度有了大幅度的提升。存储过程被第一次　调用后，会保存在高速缓冲区中，这样再次执行同一个存储过　程时，会提高了重复调用的效率。在实际应用中，可以将复杂的　商业规则封装在存储过程中，从而提高程序语句的利用率。　存储过程与一般的ＳＱＬ语句有这样一些优点：存储过程　只在创造时进行编译，以后每次执行存储过程都不需再重新编　译，而一般ＳＱＬ语句每执行一次就编译一次，所以使用存储过　程可提高数据库执行速度；当对数据库进行复杂操作时ｆ如对　多个表进行Ｕｐｄａｔｅ，Ｉｎｓｅｒ￣，Ｑｕｅｒｙ，Ｄｅｌｅｔｅ时），可将此复杂操作　用存储过程封装起来与数据库提供的事务处理结合一起使用；　存储过程可以重复使用，可减少数据库开发人员的工作量；安　全性高，可设定只有某些用户才具有对指定存储过程的使用　权。　ｇＯ　实例１：创建查询表Ｂｏｏｋ的内容的存储过程。　在查询分析器输入如下命令：　ｃｒｅａｔｅ　ｐｒｏｃ　ｑｕｅｒｙ—ｂｏｏｋ　ａｓ　ｓｅｌｅｃｔ　ｆｒｏｍ　ｂｏｏｋ　执行存储过程：　ｅｘｅｃ　ｑｕｅｒｙ—ｂｏｏｋ　通过执行ｑｕｅｒｙ—ｂｏｏｋ存储过程可以查询表Ｂｏｏｋ的内容。　实例２：加入一笔新记录到表ｂｏｏｋ，并查询此表中所有书　籍的总金额。　在查询分析器输入如下命令：　Ｃｒｅａｔｅ　ｐｒｏｃ　ｉｎｓｅｒｔｂｏｏｋ　．．＠ｐａｒａｍ１　ｃｈａｒ（１０），＠ｐａｒａｍ２　ｖａｒｃｈａｒ（２０），＠ｐａｒａｍ３　ｍｏｎｅｙ．　＠ｐａｒａｍ４　ｍｏｎｅｙ　ｏｕｔｐｕｔ　ｗｉｔｈ　ｅｎｃｒｙｐｔｉｏｎ一…ａｓ　一…一力Ⅱ密　用户自定义存储过程的方法既可以通过企业管理器。也可　以通过使用ＣＲＥＡＴＥ　ＰＲＯＣＥＤＲＵＥ语句的方式来创建存储　ｉｎｓｅｒｔ　ｂｏｏｋ（编号，书名，价格）Ｖａｌｕｅｓ（＠ｐａｒａｍ１，＠ｐａｒａｍ２。　＠ｐａｒａｍ３）　过程。通过企业管理器比较容易，下面主要论述一下怎么使用　命令语句创建存储过程。它的格式是：　Ｃｒｅａｔｅ　ｐｒｏｃｅｄｕｒｅ　ｐｒｏｃｅｄｕｅ—ｓｅｌｅｃｔ＠ｐａｒａｍ４＝ｓｕｍ（价格）ｆｒｏｍ　ｂｏｏｋ　ｇＯ　ｎａｍｅ　执行存储过程：　ｄｅｃｌａｒｅ＠ｔｏｔａｌ—ｐｒｉｃｅ　ｍｏｎｅｙ　ｅｘｅｃ　ｉｎｓｅｒｔ—［＠ｐａｒａｍｅｔｅｒ　ｄａｔａ—ｔｙｐｅ］［ｏｕｔｐｕｔ］　［Ｗｉｔｈ］｛ｒｅｃｏｍｐｉｌｅｌｅｎｃｒｙｐｔｉｏｎ１　ｂｏｏｋ’００３‘，’Ｄｅｌｐｈｉ控件开发指南’．￥１　００，＠ｔｏ—　Ｉ作者简介】吴彦国（１９７１一），男，河南郑州人，河南省轻工业职工大学计算机系教师，研究发向：数据库管理与开发。　６５　维普资讯 http://www.cqvip.com

ｔａｌ—ｐｒｉｃｅ　ｐｒｉｎｔ’总金额为’＋ｃｏｎｖｅｒｔ（ｖａｒｃｈａ　ｒ＇＠ｔｏｔａＪ—ｐｒｉｃｅ）　ｇｏ　通过执行ｉｎｓｅｒｔ—ｂｏｏｋ存储过程可以加入一笔新记录，并　查询出Ｂｏｏｋ表中所有书籍的总金额。　实例３：设有两个表为Ｐｒｏｄｕｃｔｓ、Ｏｒｄｅｒｓ，其表内容如下：　Ｐｒｏｄｕｃｔｓ　产品蛾号　客户名　客享Ｉ订金　请实现按编号为连接条件，将两个表连接生成一个新表　ｔｅｍｐｔａｂｌｅ，该表只含编号，产品名，客户名，订金，总金额（总金　额：订金・订数）。　在查询分析器输入如下命令建立存储过程：　Ｃｒｅａｔｅ　ｐｒｏｃ　ｔｅｍｐ　．ｓａｌｅ　ａ８　ｓｅｌｅｃｔ　Ｐｒｏｄｕｃｔｓ．产品编号，产品名称，客户名，客户订金，客户订　数・客户订金ａｓ总金额　ｉｎｔｏ　ｔｅｍｐｔａｂｌｅ　ｆｒｏｍ　Ｐｒｏｄｕｃｔｓ　ｉｎｎｅｒ　ｊｏｉｎ　ｏｒｄｅｒｓ　ｏｎ　Ｐｒｏｄｕｃｔｓ．产　品编号＝ｏｒｄｅｒｓ．产品编号　ｉｆ＠＠ｅｒｒｏｒ＝Ｏ　ｐｒｉｎｔ’Ｇｏｏｄ’　ｅｌｓｅ　ｇｏ　执行存储过程ｔｅｍｐ—ｓａｌｅ后即生成一个新表ｔｅｍｐｔａｂｌｅ。　最后，我们应注意存储过程的传回值。存储过程的传回值有　３种方式：以Ｒｅｔｕｒｎ传回整数；以Ｏｕｔｐｕｔ格式传回参数；　Ｒｅｃｏｒｄｓｅｔ方式。Ｏｕｔｐｕｔ和Ｒｅｔｕｒｎ都可在批次程式中用变量　接收．而Ｒｅｃｏｒｄｓｅｔ则传回到执行批次的客户端中。下面例子　介绍了Ｏｕｔｐｕｔ格式传回参数。　高校新生数据库中的表“某高校新生基本情况”结构与数　据如下：　在查询分析器输入如下命令建立存储过程ｓｔｕｄｅｎｔ：　ｕｓｅ高校新生　ｉｆｅｘｉｓｔｓ（ｓｅｌｅｃｔ　ｎａｍｅ　ｆｒｏｍ　ｓｙｓｏｂｊｅｃｔｓ　ｗｈｅｒｅ　ｎａｍｅ＝’ｓｔｕｄｅｎｔ’ａｎｄ　ｔｙｐｅ＝’Ｐ’）　ｄｒｏｐ　ｐｒｏｃｅｄｕｒｅ　ｓｔｕｄｅｎｔ　ｇ０　ｃｒｅａｔｅ　ｐｒｏｃｅｄｕｒｅ　ｓｔｕｄｅｎｔ　＠ｇｏｏｄｎａｍｅ　ｖａｒｃｈａｒ（２０），　＠ｍａｘａｇｅ　ｉｎｔ　ｏｕｔｐｕｔ，　＠ａｖｇａｇｅ　ｉｎｔ　ｏｕｔｐｕｔ　ａ８　ｓｅｌｅｃｔ＠ｍａｘａｇｅ＝ｍａｘ（年龄），＠ａｖｇａｇｅ＝ａｖｇ（年龄）ｆｒｏｍ某高校　新生基本情况ｗｈｅｒｅ性别＝＠ｇｏｏｄｎａｍｅ　ｇｏ　执行存储过程ｓｔｕｄｅｎｔ：　ｄｅｃｌａｒｅ＠ａａ　ｖａｒｃｈａｒ（２０），＠ｂｂ　ｉｎｔ，＠ｃｃ　ｉｎｔ　ｓｅｌｅｃｔ＠ａａ＝’女’　　・ｅｘｅｃｕｔｅ　ｓｔｕｄｅｎｔ＠ａａ，＠ｂｂ　ｏｕｔｐｕｔ．＠ｃｃ　ｏｕｔｐｕｔ　ｓｅｌｅｃｔ’学生性别：’＋＠ａ　ａ＇ｔ最大年龄：’＋ｃａｓｔ（＠ｂｂ　ａｓ　ｃｈａｒ（６）），’平　均年龄：’＋ｃａｓｔ（＠ｃｃ　ａｓ　ｃｈａ￣４））　执行存储过程ｓｔｕｄｅｎｔ后，将查询出女生的最大年龄、平均　年龄。其中，变量＠ｇｏｏｄｎａｍｅ获取参数＠ａａ的值“女”，输出变　量＠ｍａｘａｇｅ和＠ａｖｇａｇｅ将值依次输出给变量＠ｂｂ和＠ｃｃ。　由此我们看出，存储过程就是将常用的或很复杂的工作，　预先用ＳＱＬ语句写好并用一个指定的名称存储起来，那么以　后要叫数据库提供与已定义好的存储过程的功能相同的服务　时，只需调用存储过程，即可自动完成命令。它跟函数调用差不　多。　参考文献　【１】耿文兰．ＳＱＬ　Ｓｅｒｖｅｒ　２０００数据库管理与开发【Ｍ】．，ｇｑ－工业出版社，　２００６，１　０．　【２】蒋秀英．ＳＱＬ　Ｓｅｒｖｅｒ　２０００数据库与应用【Ｍ】．清华大学出版社，　２００６．２．　【３］赵增民．ＳＱＬ　Ｓｅｒｖｅｒ２０００实用教程【Ｍ】・电子．Ｙ－．ｔｌ：出版社．２００６，１．