存储过程

存储过程 百科名片 存储过程（Stored Procedure）是一组为了完成特定功能的 SQL语句集，经编译后存储在 数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储 过程。 目录[隐藏] 一、存储过程介绍 二、使用存储过程有以下的优点： 三、存储过程的种类： 五、存储过程的常用格式： 六、编写对数据库访问的存储过程： 七、在 SQL Server中执行存储过程： 九、存储过程中临时表的创建问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 十一、Oracle中的存储过程： 一、存储过程介绍 二、使用存储过程有以下的优点： 三、存储过程的种类： 五、存储过程的常用格式： 六、编写对数据库访问的存储过程： 七、在 SQL Server中执行存储过程： 九、存储过程中临时表的创建问题 十一、Oracle中的存储过程： [编辑本段] 一、存储过程介绍 存储过程是由流控制和 SQL语句 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 写的过程，这个过程经编译和优化后存储在 数据库服务器中，应用程序使用时只要调用即可。在 ORACLE中，若干个有联系的 过程可以组合在一起构成程序包。 存储过程是利用 SQL Server 所提供的 Transact-SQL语言所编写的程序。Tran sact-SQL语言是 SQL Server 提供专为设计数据库应用程序的语言，它是应用程序 和 SQL Server 数据库间的主要程序式设计界面。它好比 Oracle数据库系统中的 PL -SQL和 Informix的数据库系统结构中的 Informix- 4GL语言。这类语言主要提供以 下功能，让用户可以设计出符合引用需求的程序： 1)、变量说明 PDF created with pdfFactory Pro trial version www.pdffactory.com 2)、ANSI兼容的 SQL命令(如 Select,Update….) 3)、一般流程控制命令(if…else…、while….) 4)、内部函数 [编辑本段] 二、使用存储过程有以下的优点： * 存储过程的能力大大增强了 SQL语言的功能和灵活性。存储过程可以用流控 制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的 运算。 * 可保证数据的安全性和完整性。 # 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证 数据的安全。 # 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。 * 在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行 方案。这种已经编译好的过程可极大地改善 SQL语句的性能。由于执行 SQL语句的 大部分工作已经完成，所以存储过程能以极快的速度执行。 * 可以降低网络的通信量。 * 使体现企业 规则 编码规则下载淘宝规则下载天猫规则下载麻将竞赛规则pdf麻将竞赛规则pdf 的运算程序放入数据库服务器中，以便： # 集中控制。 # 当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程 序。企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中， 则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装 应用程序）。如果把体现企业规则的运算放入存储过程中，则当企业规则发生变化时， 只要修改存储过程就可以了，应用程序无须任何变化。 [编辑本段] 三、存储过程的种类： 1)、系统存储过程：以 sp_开头 ,用来进行系统的各项设定 .取得信息.相关管理工 作 , 如 sp_help 就是取得指定对象的相关信息 2)、扩展存储过程以 XP_开头,用来调用操作系统提供的功能 exec master..xp_cmdshell 'ping 10.8.16.1' 3)、用户自定义的存储过程 ,这是我们所指的存储过程四、存储过程的书写格式： CREATE PROCEDURE [拥有者.]存储过程名 [;程序编号 ] [(参数#1,…参数#1024)] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ] PDF created with pdfFactory Pro trial version www.pdffactory.com [FOR REPLICATION] AS 程序行 其中存储过程名不能超过 128 个字。每个存储过程中最多设定 1024个参数 (SQL Server 7.0以上版本),参数的使用 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 如下： @参数名 数据类型 [VARYING] [=内定值 ] [OUTPUT] 每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用 ,参 数的类型除了 IMAGE外，其他 SQL Server 所支持的数据类型都可使用。 [=内定值 ]相当于我们在建立数据库时设定一个字段的默认值，这里是为这个参数 设定默认值。 [OUTPUT]是用来指定该参数是既有输入又有输出值的，也就是在调用 了这个存储过程时，如果所指定的参数值是我们需要输入的参数，同时也需要在结果 中输出的，则该项必须为 OUTPUT，而如果只是做输出参数用，可以用 CURSOR， 同时在使用该参数时，必须指定 VARYING和 OUTPUT这两个语句。 例子： CREATE PROCEDURE order_tot_amt @o_id int, @p_tot int output AS SELECT @p_tot = sum(Unitprice*Quantity) FROM orderdetails WHERE ordered=@o_id GO 例子说明： 该例子是建立一个简单的存储过程 order_tot_amt,这个存储过程根据用户输入的 定单 ID号码(@o_id),由定单明细表 (orderdetails)中计算该定单销售总额 [单价(Unitp rice)*数量(Quantity)],这一金额通过@p_tot 这一参数输出给调用这一存储过程的程 序。 [编辑本段] 五、存储过程的常用格式： Create procedure procedue_name [@parameter data_type][output] [with]{recompile|encryption} as sql_statement 解释： output：表示此参数是可传回的 with {recompile|encryption} recompile:表示每次执行此存储过程时都重新编译一次 PDF created with pdfFactory Pro trial version www.pdffactory.com encryption:所创建的存储过程的内容会被加密 如： 表 book的内容如下 编号 书名 价格 001 C 语言入门 $30 002 PowerBuilder 报表开发 $52 实例 1:查询表 Book的内容的存储过程 create proc query_book as select * from book go exec query_book 实例 2： 加入一笔记录到表 book,并查询此表中所有书籍的总金额 Create proc insert_book @param1 char(10),@param2 varchar(20),@param3 money,@param4 mone y output with encryption ---------加密 as insert book(编号 ,书名 ,价格) Values(@param1,@param2,@param3) select @param4=sum(价格) from book go 执行例子： declare @total_price money exec insert_book '003','Delphi 控件开发指南 ',$100,@total_price print '总金额为 '+convert(varchar,@total_price) go 存储过程的 3种传回值： 1)、以 Return传回整数 2)、以 output格式传回参数 3)、Recordset 传回值的区别： output和 return都可在批次程式中用变量接收，而 recordset则传回到执行批次 的客户端中。 实例 3： 设有两个表为 Product,Order_,其表内容如下： Product 产品编号 产品名称 客户订数 PDF created with pdfFactory Pro trial version www.pdffactory.com 001 钢笔 30 002 毛笔 50 003 铅笔 100 Order_ 产品编号 客户名 客户订金 001 南山区 $30 002 罗湖区 $50 003 宝安区 $4 请实现按编号为连接条件 ,将两个表连接成一个临时表 ,该表只含编号.产品名.客 户名 .订金 .总金额 , 总金额=订金*订数,临时表放在存储过程中 代码如下： Create proc temp_sale as select a.产品编号 ,a.产品名称,b.客户名,b.客户订金 ,a.客户订数* b.客户订金 a s总金额 into #temptable from Product a inner join Order_ b on a.产品编号=b.产 品编号 if @@error=0 print 'Good' else &n bsp; print 'Fail' go [编辑本段] 六、编写对数据库访问的存储过程： 数据库存储过程的实质就是部署在数据库端的一组定义代码以及 SQL。将常用的 或很复杂的工作，预先用 SQL语句写好并用一个指定的名称存储起来，那么以后要 叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用 execute，即可 自动完成命令。 利用 SQL的语言可以编写对于数据库访问的存储过程，其语法如下： CREATE PROC[EDURE] procedure_name [;number] [ {@parameter data_type} ][VARYING] [= default] [OUTPUT] ] [,...n] [WITH { PDF created with pdfFactory Pro trial version www.pdffactory.com RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [FOR REPLICATION] AS sql_statement [...n] [ ]内的内容是可选项，而（）内的内容是必选项， 例：若用户想建立一个删除表 tmp 中的记录的存储过程 Select_delete可写为： Create Proc select_del As Delete tmp 例：用户想查询 tmp 表中某年的数据的存储过程 create proc select_query @year int as select * from tmp where year=@year 在这里@year 是存储过程的参数 例：该存储过程是从某结点 n开始找到最上层的父亲结点，这种经常用到的过程 可以由存储过程来担当，在网页中重复使用达到共享。 空：表示该结点为顶层结点 fjdid(父结点编号) 结点 n 非空：表示该结点的父亲结点号 dwmc（单位名称） CREATE proc search_dwmc @dwidold int,@dwmcresult varchar(100) outp ut as declare @stop int declare @result varchar(80) declare @dwmc varchar(80) declare @dwid int set nocount on set @stop=1 set @dwmc="" select @dwmc=dwmc,@dwid=convert(int,fjdid) from jtdw where id=@dwido ld set @result=rtrim(@dwmc) if @dwid=0 set @stop=0 while (@stop=1) and (@dwid<>0) PDF created with pdfFactory Pro trial version www.pdffactory.com begin set @dwidold=@dwid select @dwmc=dwmc,@dwid=convert(int,fjdid) from jtdw where id=@dwido ld if @@rowcount=0 set @dwmc="" else set @result=@dwmc+@result if (@dwid=0) or (@@rowcount=0) set @stop=0 else continue end set @dwmcresult=rtrim(@result) 使用 exec pro-name [pram1 pram2.....] [编辑本段] 七、在 SQL Server中执行存储过程： sql 语句执行的时候要先编译，然后执行。存储过程就是编译好了的一些 sql 语 句。用的时候直接就可以用了。 在 SQL Server 的查询分析器中，输入以下代码 : declare @tot_amt int execute order_tot_amt 1,@tot_amt output select @tot_amt 以上代码是执行 order_tot_amt这一存储过程，以计算出定单编号为 1的定单销 售金额，我们定义@tot_amt为输出参数，用来承接我们所要的结果。 存储过程具有以下特点： 1.具有立即访问数据库的能力； 2.是数据库服务器端的执行代码，在服务器执行操作时，减少网络通讯，提高执 行效率。 3.保证数据库安全，自动完成提前设定的作业。八、存储过程的缺点 1：调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。 2：移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目， 基本不存在移植问题。 3：重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发 生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动 编译）。 PDF created with pdfFactory Pro trial version www.pdffactory.com 4： 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用 户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想 维护该系统可以说是很难很难、而且代价是空前的。维护起来更加麻烦！ [编辑本段] 九、存储过程中临时表的创建问题 （针对 SQL2000/2OO5） 可以创建本地和全局临时表。本地临时表仅在当前会话中可见；全局临时表在所 有会话中都可见。 本地临时表的名称前面有一个编号符 (#table_name)，而全局临时表的名称前面 有两个编号符 (##table_name)。 SQL 语句使用 CREATE TABLE 语句中为 table_name 指定的名称引用临时 表： CREATE TABLE #MyTempTable (cola INT PRIMARY KEY) INSERT INTO #MyTempTable VALUES (1) 如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建，则 SQ L Server 必须能够区分由不同用户创建的表。为此，SQL Server 在内部为每个本 地临时表的表名追加一个数字后缀。存储在 tempdb 数据库的 sysobjects 表中的临 时表，其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。 为了允许追加后缀，为本地临时表指定的表名 table_name 不能超过 116 个字符。 除非使用 DROP TABLE 语句显式除去临时表，否则临时表将在退出其作用域 时由系统自动除去： 当存储过程完成时，将自动除去在存储过程中创建的本地临时表。由创建表的存 储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程的进程 无法引用此表。 所有其它本地临时表在当前会话结束时自动除去。 全局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与 表之间的关联只在单个 Transact-SQL 语句的生存周期内保持。换言之，当创建全 局临时表的会话结束时，最后一条引用此表的 Transact-SQL 语句完成后，将自动 除去此表。 十、特殊的存储过程-触发器 1.触发器的概念及作用 触发器是一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。触发 器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接 调用。当对某一表进行诸如 Update、 Insert、 Delete 这些操作时，SQL Server 就会自动执行触发器所定义的 SQL 语句，从而确保对数据的处理必须符合由这些 S QL 语句所定义的规则。 PDF created with pdfFactory Pro trial version www.pdffactory.com 触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整 性和数据的一致性。除此之外，触发器还有其它许多不同的功能： (1) 强化约束(En force restriction) 触发器能够实现比 CHECK 语句更为复杂的约束。 (2) 跟踪变化 Auditing changes 触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变 化。 (3) 级联运行(Cascaded operation)。 触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例 如，某个表上的触发器中包含有对另外一个表的数据操作(如删除，更新，插入)而该 操作又导致该表上触发器被触发。 (4) 存储过程的调用(Stored procedure invocation)。 为了响应数据库更新触，发器可以调用一个或多个存储过程，甚至可以通过外部 过程的调用而在 DBMS( 数据库管理系统)本身之外进行操作。 由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记 录等一些方面的问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异， 并根据这种差异执行一定的处理。此外一个表的同一类型(Insert、 Update、 Delet e)的多个触发器能够对同一种数据操作采取多种不同的处理。 总体而言，触发器性能通常比较低。当运行触发器时，系统处理的大部分时间花 费在参照其它表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删 除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费 的时间长短。 2.触发器的种类 SQL Server 2000 支持两种类型的触发器：AFTER 触发器和 INSTEAD OF 触发器。其中 AFTER 触发器即为 SQL Server 2000 版本以前所介绍的触发器。该 类型触发器要求只有执行某一操作(Insert Update Delete) 之后，触发器才被触发， 且只能在表上定义。可以为针对表的同一操作定义多个触发器。对于 AFTER 触发器， 可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程 sp_settr iggerorder 来完成此任务。 INSTEAD OF 触发器表示并不执行其所定义的操作(Insert、 Update、 Delete)， 而仅是执行触发器本身。既可在表上定义 INSTEAD OF 触发器，也可以在视图上定 义 INSTEAD OF 触发器，但对同一操作只能定义一个 INSTEAD OF 触发器。 [编辑本段] 十一、Oracle中的存储过程： 1.创建过程 PDF created with pdfFactory Pro trial version www.pdffactory.com 与其它的数据库系统一样，Oracle的存储过程是用 PL/SQL语言编写的能完成 一定处理功能的存储在数据库字典中的程序。 语法: create [or replace] procedure procedure_name [ (argment [ { in| in out }] type, argment [ { in | out | in out } ] type { is | as } <类型.变量的说明> ( 注 : 不用 declare 语句 ) Begin <执行部分> exception <可选的异常处理说明> end; l 这里的 IN 表示向存储过程传递参数，OUT表示从存储过程返回参数。而 IN OUT 表示传递参数和返回参数； l 在存储过程内的变量类型只能指定变量类型；不能指定长度； l 在 AS或 IS 后声明要用到的变量名称和变量类型及长度； l 在 AS或 IS 后声明变量不要加 declare 语句。 2.使用过程 存储过程建立完成后，只要通过授权，用户就可以在 SQLPLUS 、Oracle 开 发工具或第三方开发工具来调用运行。Oracle 使用 EXECUTE 语句来实现对存储过 程的调用。 语法： EXEC[UTE] procedure_name( parameter1, parameter2…); 3.开发过程 目前的几大数据库厂商提供的编写存储过程的工具都没有统一，虽然它们的编 写风格有些相似，但由于没有 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 ，所以各家的开发调试过程也不一样。下面编写 P L/SQL存储过程、函数、包及触发器的步骤如下： 3.1 编辑存储过程源码使用文字编辑处理软件编辑存储过程源码，要用类似 WO RD 文字处理软件进行编辑时，要将源码存为文本格式。 3.2 对存储过程程序进行解释在 SQLPLUS或用调试工具将 存储过程程序进行 解释； 在 SQL>下调试，可用 start 或 get 等 Oracle 命令来启动解释。如： SQL>start c:\stat1.sql 如果使用调试工具，可直接编辑和点击相应的按钮即可生成存储过程。 [1] PDF created with pdfFactory Pro trial version www.pdffactory.com 3.3 调试源码直到正确我们不能保证所写的存储过程达到一次就正确。所以这里 的调试是每个程序员必须进行的工作之一。在 SQLPLUS下来调试主要用的方法是： 1．使用 SHOW ERROR 命令来提示源码的错误位置； 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 | PUBLIC WITH GRANT OPTION 其中： system_privilege: 系统权限 role: 角色名 user: 被授权的用户名 object_privilege: 所授予的权限名字，可以是 ALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE Column: 列名 schema: 模式名 object: 对象名 4.数据字典 USER_SOURCE 用户的存储过程、函数的源代码字典 DBA_SOURCE 整个系统所有用户的存储过程、函数的源代码字典 ALL_SOURCE 当前用户能使用的存储过程(包括其她用户授权)、函数的源代码 字典 USER_ERRORS 用户的存储过程、函数的源代码存在错误的信息字典 PDF created with pdfFactory Pro trial version www.pdffactory.com