存储过程百科

《存储过程百科》由会员分享，可在线阅读，更多相关《存储过程百科（9页珍藏版）》请在装配图网上搜索。

1、存储过程百科存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集，经编译 后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象，任 何一个设计良好的数据库应用程序都应该用到存储过程。目录隐藏一、存储过程介绍二、使用存储过程有以下的优点：三、存储过程的种类：五、存储过程的常用格式：六、编写对数据库访问的存储过程：七、在 SQL Server 中执行存储过程：八、存储过程的缺点九、存储过程中临时表的创建问题 十一、Oracle中的存储过程：1. 创建过程2. 使用过程3. 开发过程4. 数据字典一、存

2、储过程介绍二、使用存储过程有以下的优点：三、存储过程的种类：五、存储过程的常用格式：六、编写对数据库访问的存储过程：七、在 SQL Server 中执行存储过程：八、存储过程的缺点九、存储过程中临时表的创建问题 十一、 Oracle 中的存储过程：1.创建过程 2.使用过程 3.开发过程 4.数据字典编辑本段一、存储过程介绍存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储 在数据库服务器中，应用程序使用时只要调用即可。在ORACLE中，若干个有联系的过程可以组合在一起构成程序包。 存储过程是利用 SQL Server 所提供的 Transact-SQL 语言所编写的程序。

3、Transact-SQL语言是 SQL Server 提供专为设计数据库应用程序的语言，它是应用程序和 SQL Server数据库间的主要程序式设计界面。它好比Oracle数据库系统中的 PL-SQL 和 Informix 的数据库系统结构中的 Informix- 4GL 语言。这类语 言主要提供以下功能，让用户可以设计出符合引用需求的程序：1）、变量说明2）、ANSI 兼容的 SQL命令（如 Select,Update.）3）、一般流程控制命令（ifelse、wh il e.）4）、内部函数编辑本段二、使用存储过程有以下的优点：*存储过程的能力大大增强了 SQL语言的功能和灵活性。存储过程可

4、以用流 控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的 运算。 * 可保证数据的安全性和完整性。 # 通过存储过程可以使 没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。 # 通过存储过程可以使相关的动作在一起发生，从而可以维护数据 库的完整性。 * 在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。 * 可以降低网络的通信量。 * 使体 现企业规则的运算程序放入数据库服务器中，以便： # 集中控制。 # 当企业规则发生变化

5、时在服务器中改变存储过程即可，无须修 改任何应用程序。企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用 程序工作量非常之大（修改、发行和安装应用程序）。如果把体现企业规则的运算放入存储过程中，则当企业规则发生变化时，只要修改存储 过程就可以了，应用程序无须任何变化。编辑本段三、存储过程的种类：1）、系统存储过程：以sp_开头，用来进行系统的各项设定.取得信息相关管理工作,如 sp_help 就是取得指定对象的相关信息 2）、扩展存储过程以XP_开头，用来调用操作系统提供的功能 execmaster.xp_cmdshell ping

6、10.8.16.13）、用户自定义的存储过程，这是我们所指的存储过程四、存储过程的书写格式： CREATE PROCEDURE 拥 有者存储过程名；程序编号（参数#1,参数#1024）WITHRECOMPILE | ENCRYPTION | RECOMPILE，ENCRYPTION FOR REPLICATION AS 程序行 其中存储过程名不能超过 128 个字。每个存储过程中最多设定 1024 个参数 （SQLServer 7.0以上版本），参数的使用方法如下：参数名 数据类型 VARYING =内定值 OUTPUT每个参数名前要有一个“”符号 ,每个存储过程的参数仅为该程序内部使用,参数

7、的类型除了 IMAGE外，其他 SQL Server 所支持的数据类型都可使用。=内定值相当于我们在建立数据库时设定一个字段的默认值，这里是为这个参数设定默认值。OUTPUT是用来指定该参数是既有输入又有输出值的，也就是在调用了 这个存储过程时，如果所指定的参数值是我们需要输入的参数，同时也需要在结果中输出的，则该项必须为OUTPUT，而如果只是做输出参数 用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT 这两 个语句 。例 子：CREATE PROCEDURE order_tot_amto_id int, p_tot int output AS SELECTp

8、_tot = sum(Unitprice*Quantity)FROM orderdetailsWHEREordered二o_idGO 例子说明：该例子是建立一个简单 的存储过程order_tot_amt,这个存储过程根据用户输入的定单ID号码(o_id), 由定单明细表(orderdetails)中计算该定单销售总额单价 (Unitprice)*数量(Quantity),这一金额通过p_tot这一参数输出给调用这一 存储过程的程序。编辑本段五、存储过程的常用格式：Create procedure procedue_name parameter data_typeoutput withrecom

9、pile|encryption assql_statement 解释： recompile|encryptionwithout put:表示此参数是可传回的 recomp il e:表示每次执行此存储过程时都重新编译一次encryption:所创建的存储过程的内容会被加密如： 表book的内容如下 编号书名 价格 001 C语言入门 $30容的存储过程实例1:查询表Book的内 select002 PowerBuilder 报表开发 $52create proc query_book as加入一笔记录到* from book go exec query_book 实例 2： 表 book, 并

10、查询此表中所有书籍的总金额 Create procinsert_bookparam1 char(10),param2 varchar(20),param3money,param4 money output with encryption 加密as insert book(编号,书名,价格)Values(param1,param2,param3) select param4=sum(价格)from book go 执行例子： declare total_price money控件开发指南，$100, tot al_priceexecinsert_book003,Delphiprint 总金额为

11、+convert（varchar, total_price） go 存储过程的 3 种传回值：1）、以 Return 传回整数2）、以 output 格式传回参数3）、Recordset传回值的区别： output 和 return 都可在批次程式中用变量接收，而 recordset 则传回到执行批次的客户端中。 实例 3：设有两个表为 Product,Order_, 其表内容如下： Product 产品编号 产品名称 客户订数 001 钢笔 30 002 毛笔50003 铅笔 100 Order南山区 $30002 罗湖区 $50产品编号 客户名 客户订金003 宝安区 $4 请实001现按

12、编号为连接条件 , 将两个表连接成一个临时表 , 该表只含编号 .产品名.客户 名.订金.总金额,总金额=订金*订数, 临时表放在存储过程中代码如下：Create proc temp_sale asselect a.产品编号,a.产品名称,b.客户名,b.客户订金,a.客户订数* b.客户订金as 总金额 into #temptable from Product a inner join Order_ b on a. 产品编号二b.产品编号if error=0 print Goodelse &n bsp; print Fail go编辑本段六、编写对数据库访问的存储过程：数据库存储过程的实质就

13、是部署在数据库端的一组定义代码以及SQL。将常 用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称 存储起来，那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用execute,即可自动完成命令。利用SQL的语言CREATE PROCEDURE可以编写对于数据库访问的存储过程，其语法如下： procedure_name ;number parameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE| ENCRYPTION|ASRECOMPILE, ENCRYPTIONsql_statement .nFOR REPLICA

14、TION 内的内容是可选项，而（）内的内容是必选项，例：若用户想建立一个删除表 tmp 中的记录的存储过程Select_delete 可写为：Create Proc select_del AsDelete tmp例：用户想查询tmp表中某年的数据的存储过程createproc select_query year int as select * from tmpwhere year=year在这里year是存储过程的参数例：该存储过程是从某结点n开始找到最上层的父亲结点，这种经常用到的过程可 以由存储过程来担当，在网页中重复使用达到共享。 空：表示该结点为顶层结点fjdid(父结点编号)结点n非

15、空：表示该结 点的父亲结点号 dwmc (单位名称) CREATE proc search_dwmc dwidold int,dwmcresult varchar(100) output asdeclare dwmcdeclare stop intdeclare result varchar(80)varchar(80) declare dwid int set nocount ondwmc=from jtdw whereselectid=dwidoldset stop=1 set dwmc=dwmc,dwid=convert(int,fjdid) setresult=rtrim(dwmc)(

16、stop=1) and (dwid0)if dwid=0beginset stop=0 set dwidold=dwidwhileselectid=dwidolddwmc=dwmc,dwid=convert(int,fjdid)from jtdw elsewhereif rowcount=0set dwmc=setresult=dwmc+resultif(dwid=0)or (rowcount=0)setstop=0elsecontinueendsetdwmcresult=rtrim(result) 使用 exec pro-name pram1 pram2编辑本段七、在 SQL Server

17、中执行存储过程：sql 语句执行的时候要先编译，然后执行。存储过程就是编译好了的一些 sql 语句。用的时候直接就可以用了。 在 SQL Server 的查询分析 器中 ， 输入 以下 代码 : order_tot_amt 1,tot_amt outputdeclare tot_amt intexecuteselect tot_amt 以上代码是执行 order_tot_amt 这一存储过程，以计算出定单编号为 1 的定单销售金额，我 们定义tot_amt为输出参数，用来承接我们所要的结果。存储过程具有以下特点：1.具有立即访问数据库的能力；2.是数据库服务器端的执行代码，在服务器执行操作时，

18、减少网络通讯，提高执行效率。3.保证数据库安全，自动完成提前设定的作业。编辑本段八、存储过程的缺点1：调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。 2：移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。 3：重新编译问题，因为后端代 码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译(不过也可以设置成运行时刻自动编译)。4： 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很

19、难、而且代价是空前的。维护起来更加麻烦！编辑本段九、存储过程中临时表的创建问题(针对 SQL2000/2OO5)可以创建本地和全局临时表。本地临时表仅在当前会话中可见；全局临时表在所有会话中都可见。 本地临时表的名称前面有一个编号符(#table_name),而全局临时表的名称前面有两个编号符 (#table_name)。 SQL 语句使用 CREATE TABLE语句中为 table_name 指定的名称引用临时表：CREATE TABLE#MyTempTable (cola INT PRIMARY KEY) INSERT INTO #MyTempTableVALUES (1) 如果本地临时

20、表由存储过程创建或由多个用户同时执行的应用 程序创建,则 SQL Server 必须能够区分由不同用户创建的表。为此, SQL Server 在内部为每个本地临时表的表名追加一个数字后缀。存储在tempdb 数据库的 sysobjects 表中的临时表,其全名由CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后 缀,为本地临时表指定的表名 table_name 不能超过 116 个字符。 除非使用 DROP TABLE 语句显式除去临时表,否则临时表将在退出其 作用域时由系统自动除去： 当存储过程完成时,将自动除去在存储过程中创建的本地临时表。由创建表的存储过程

21、执行的所有嵌套存储过 程都可以引用此表。但调用创建此表的存储过程的进程无法引用此表。 所有其它本地临时表在当前会话结束时自动除去。 全局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与表之间的关联只在单个 Transact-SQL 语句的生存周期内保持。换言之,当创建全局临时表的会话结束时,最后一条引用此表的Transact-SQL 语句完成后,将自动除去此表。十、特殊的存储过程-触发器 1.触发器的概念及作用触发器是一种特殊类型的存储过程,它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而 被执行的,而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸

22、如 Update、 Insert、 Delete 这些操作时, SQL Server 就 会自动执行触发器所定义的 SQL 语句,从而确保对数据的处理必须符合由这些 SQL 语句所定义的规则。 触发器的主要作用就是其能够实现 由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此 之外，触发器 还有其它许多不 同的功能： (1) 强化约束 (Enforce restriction) 触发器能够实现比 CHECK 语句更为复杂的约束。(2) 跟踪变化 Auditing changes 触发器可以侦测数据库内的操作，从而 不允许数据库中未经许可的指定更新和变化。 (3) 级联运行(Casca

23、ded operation)。触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器中包含有对另外一个表的数据操作(如删除，更新，插入)而该操作又导致该表上触发器被触发。(4)存储过程的调用(Stored procedureinvocation)。 为了响应数据库更新触，发器可以调用一个或多个存储过程， 甚至可以通过外部过程的调用而在DBMS(数据库管理系统)本身之外进行操作。 由此可见，触发器可以解决高级形式的业务规则或复杂 行为限制以及实现定制记录等一些方面的问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的处理。此外

24、一个表的同一类型(Insert、Update、Delete)的多个触发器能够对同一种数据操作采取多种不同的处理。 总体而言，触发器性 能通常比较低。当运行触发器时，系统处理的大部分时间花费在参照其它表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删 除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。 2.触发器的种类 SQL Server 2000支持两种类型的触发器：AFTER触发器和INSTEAD OF触发器。其中 AFTER 触发器即为 SQL Server 2000 版本以前所介绍的触发器。该类型 触发器要求只有执行某一操作(Insert

25、 Update Delete)之后，触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。 对于 AFTER 触发器，可以定义哪一个触发器被最先触发，哪一个被最后触发， 通常使用系统过程 sp_settriggerorder 来完成此任务。INSTEAD OF触发器表示并不执行其所定义的操作(Insert、Update、Delete)，而仅是执行触发器本身。既可在表上定义INSTEAD OF触发 器，也可以在视图上定义INSTEAD OF触发器，但对同一操作只能定义一个INSTEAD OF触发器。编辑本段一、Oracle中的存储过程：1. 创建过程与其它的数据库系统一样，Or

26、acle的存储过程是用PL/SQL语言编写的能完 成一定处理功能的存储在数据库字典中的程序。语法: (argment in|in( 注: 不用 declare 语可选的create or replace procedure procedure_name out type, argment in | out | in outtype is | as 类型.变量的说明句 ) Begin执行部分exception异常处理说明end; l这里的IN表示向存储过程传递参数，OUT表示从存储过程返回参数。而IN OUT表示传递参数和返回参数；l在存储过程内的变量类型只能指定变量类型；不能指定长度；l在AS

27、或IS后声明要用到的变量名称和变量类型及长度；l在AS或IS后声明变量不要加declare语句。2. 使用过程存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS、Oracle开 发工具或第三方开发工具来调用运行。Oracle使用EXECUTE语句来 实现 对 存储 过 程的 调 用 。语 法 ：EXECUTEprocedure_name( parameterl, parameter2)；3. 开发过程目前的几大数据库厂商提供的编写存储过程的工具都没有统一，虽然它们的 编写风格有些相似，但由于没有标准，所以各家的开发调试过程也不一样。下面编写PL/SQL存储过程、函数、包及触发器的步骤

28、如下：3.1编辑存储过程源码使用文字编辑处理软件编辑存储过程源码，要用类似WORD文字处理软件进行编辑时，要将源码存为文本格式。3.2对存储过程程序进行解释在SQLPLUS或用调试工具将存储过程程序进行解释；在SQL下调试，可用st ar t或get等Oracle命令来启动解释。如： SQLstart c:stat1.sql 如果使用调试工具，可直接编辑和点击相应的按钮即可生成存储过程。13.3 调试源码直到正确我们不能保证所写的存储过程达到一次就正确。所以这里的调试是每个程序员必须进行的工作之一。在 SQLPLUS 下来调试主要用的方法 是：1 使用SHOW ERROR命令来提示源码的错误位

29、置；2使用 USER_ERRORS 数据字典来查看各存储过程的错误位置。 3.4 授权 执行权给相关的用户或角色如果调试正确的存储过程没有进行授 权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。在SQLPLUS下可以用GRANT命令来进行存储过程的运行授权。 语法： GRANT system_privilege | role TO user | role | PUBLIC WITH ADMIN OPTION 或GRANT object_privilege | ALL column ON schema.object TO user | role |

30、 PUBLIC WITH GRANT OPTION 其中：user: 被授权的用户名ALTERsystem_privilege: 系统权限 role: 角色名 object_privilege: 所授予的权限名字，可以是DELETEEXECUTEINDEXINSERTREFERENCESSELECTUPDATEColumn: 列名schema:模式名object: 对象名4. 数据字典USER_SOURCE 用户的存储过程、函数的源代码字典 DBA_SOURCE 整个系 统所有用户的存储过程、函数的源代码字典 ALL_SOURCE当前用户能使用的存储过程（包括其她用户授权 ）、函数的源代码字典USER_ERRORS用户的存储过程、函数的源代码存在错误的信息字典