存储过程 旧城等待, 2021-11-04 22:12 454阅读 0赞 1. 存储过程的类型: (1) 用户自定义存储过程 自定义存储过程即用户使用T\_SQL语句编写的、为了实现某一特定业务需求,在用户数据库中编写的T\_SQL语句集合,自定义存储过程可以接受输入参数、向客户端返回结果和信息,返回输出参数等。创建自定义存储过程时,存储过程名前加上"\#\#"表示创建了一个全局的临时存储过程;存储过程前面加上"\#"时,表示创建的局部临时存储过程。局部临时存储过程只能在创建它的回话中使用,会话结束时,将被删除。这两种存储过程都存储在tempdb数据库中。 用户定义的存储过程分为两类:T\_SQL 和CLR T\_SQL:存储过程是值保存的T\_SQL语句集合,可以接受和返回用户提供的参数,存储过程也可能从数据库向客户端应用程序返回数据。 CLR存储过程是指引用Microsoft.NET Framework公共语言的方法存储过程,可以接受和返回用户提供的参数,它们在.NET Framework程序集是作为类的公共静态方法实现的。 (2) 扩展存储过程 扩展存储过程是以在SQL SERVER环境外执行的动态连接(DLL文件)来实现的,可以加载到SQL SERVER实例运行的地址空间中执行,扩展存储过程可以用SQL SERVER扩展存储过程API编程,扩展存储过程以前缀"xp\_"来标识,对于用户来说,扩展存储过程和普通话存储过程一样,可以用相同的方法来执行。 (3) 系统存储过程 系统存储过程是 SQL Server系统自身提供的存储过程,可以作为命令执行各种操作。 系统存储过程主要用来从系统表中获取信息,使用系统存储过程完成数据库服务器的管理工作,为系统管理员提供帮助,为用户查看数据库对象提供方便,系统存储过程位于数据库服务器中,并且以sp\_开头,系统存储过程定义在系统定义和用户定义的数据库中,在调用时不必在存储过程前加数据库限定名。例如:sp\_rename系统存储过程可以修改当前数据库中用户创建对象的名称,sp\_helptext存储过程可以显示规则,默认值或视图的文本信息,SQL SERVER服务器中许多的管理工作都是通过执行系统存储过程来完成的,许多系统信息也可以通过执行系统存储过程来获得。 系统存储过程创建并存放在与系统数据库master中,一些系统存储过程只能由系统管理员使用,而有些系统存储过程通过授权可以被其它用户所使用。 2.Sql存储过程 (1)创建存储过程 使用 create Procedure语句来创建存储过程,存储过程名称在该语句之后,as关键字标示存储过程主体的开始,存储过程有多个sql语句组成,例如下面的语句创建一个名为usp\_getAllEmployees的存储过程,用于从employeeDepartment表中检索数据 Create Procedure usp\_getAllEmployees As Select LastName,FirstName,JobTitle,Department From employeeDepartment 要执行一个存储过程,可以使用execute语句: Execute usp\_getAllEmployees 存储过程的参数 存储过成人能够通过参数与调用程序通讯。参数定义应当出现在存储过程名称的后面,as的前面,当程序执行存储过程时,可通过存储过程的参数向该存储过程传递值,也可以output参数将值返回至调用程序 1、指定参数的名称和数据类型 参数名称应当以@开始,以后的字符可以是遵守对象标识符的任意字符,并以@@开头,因为这是用于内置函数的标识符号,例如下面创建的usp\_GetProduct存储过程包含@standardCost和@listPrice两个参数,参数的数据类型均是money Create produce usp\_GetProduct @standardCost money,@listPrice money As Select name, standardCost, listPrice from product Where standardCost>@ standardCost and listPrice> @listPrice 执行存储过程时,既可以通过显式的方式指定参数名称并分配适当的值,也可以直接分配参数值,如果使用了显式方式,则按任意顺序提供参数,如果未指定参数名称,则必须按参数在存储过程定义的时候的顺序来提供。 Excute usp\_GetProduct @ listPrice=100,@standardCost=10 Excute usp\_GetProduct 10 ,100 2、为参数指定默认值 在参数定义中可以为可选参数指定一个,默认值,执行该存储过程时,如果未指定其他值,则使用默认值 Create produce usp\_GetProduct @standardCost money=0,@listPrice money As Select name, standardCost, listPrice from product Where standardCost>@ standardCost and listPrice> @listPrice 执行该存储过程,可以只为@listPrice指定参数 Excute usp\_GetProduct @ listPrice=100 由于具有默认参数通常是可选参数,所以建议将他们放置在参数列表的末尾以便于调用。 对于字符参数,在参数传递时可以指定通配符 3、指定输出参数 默认情况下,所有的参数均为输出参数,要指定输出参数,必须在参数定义中使用output关键字。当存储过程退出时,它将向调用程序返回输出参数的当前值。,例如,下面创建的存储过程定义了一个输出参数@productCount,用于返回ListPrice大于指定产品的数量 Create produce usp\_GetProduct @productCount int output, @listPrice money As set @productCount= (select count(id) from product Where listPrice> @listPrice) 1. 修改存储过程 如果需要修改存储过程中的语句或者参数,可以删除并重新创建该存储过程,也可以使用alter producedure语句更改该存储过程。删除并重新建时,与该存储过程关联的所有权限将丢失,更改时,将更改过程或者参数定义,但为该存储过程定义的权限将保留,将不会影响任何相关的存储过程或触发器 Alrter produce usp\_GetProduct @standardCost money=0,@listPrice money As Select name, standardCost, listPrice from product Where standardCost>@ standardCost and listPrice> @listPrice 1. 存储过程的重新编译 1、指定在下次执行时重新编译 可以使用sp\_recompile系统存储过程指定在下次执行存储过程或触发器进行重新编译 2、从sql server 2005开始,引入了对存储过程执行语句级重新编译的功能,也就是说在重新编译存储过程时,值编译导致重新编译的语句,而不编译整个存储过程。 要使用此功能,应当在语句中包含recomple查询提示,recomeple指示数据库引擎在执行查询后,丢弃为其生成的查询计划,从而在下次执行时强制编译查询计划,如果未指定recompile。数据库将缓存查询计划并从新使用它们 3、每次执行时重新编译村重过程 在创建存储过程时指定with recompile选项,强制在执行存储过程时对其进行重新编译,指定该选项时,数据库引擎将部位该存储过程缓存执行计划,而是在每次执行时都重新编译 4.创建带游标参数的存储过程 if (object\_id('book\_cursor', 'P') is not null) drop proc book\_cursor go create proc book\_cursor @bookCursor cursor varying output as set @bookCursor=cursor forward\_only static for select book\_id,book\_name,book\_auth from books open @bookCursor; go \--调用book\_cursor存储过程 declare @cur cursor, @bookID int, @bookName varchar(20), @bookAuth varchar(20); exec book\_cursor @bookCursor=@cur output; fetch next from @cur into @bookID,@bookName,@bookAuth; while(@@FETCH\_STATUS=0) begin fetch next from @cur into @bookID,@bookName,@bookAuth; print 'bookID:'+convert(varchar,@bookID)+' , bookName: '+ @bookName +' ,bookAuth: '+@bookAuth; end close @cur --关闭游标 DEALLOCATE @cur; --释放游标 5.创建分页存储过程 if (object\_id('book\_page', 'P') is not null) drop proc book\_page go create proc book\_page( @TableName varchar(50), --表名 @ReFieldsStr varchar(200) = '\*', --字段名(全部字段为\*) @OrderString varchar(200), --排序字段(必须!支持多字段不用加order by) @WhereString varchar(500) =N'', --条件语句(不用加where) @PageSize int, --每页多少条记录 @PageIndex int = 1 , --指定当前为第几页 @TotalRecord int output --返回总记录数 ) as begin --处理开始点和结束点 Declare @StartRecord int; Declare @EndRecord int; Declare @TotalCountSql nvarchar(500); Declare @SqlString nvarchar(2000); set @StartRecord = (@PageIndex-1)\*@PageSize + 1 set @EndRecord = @StartRecord + @PageSize - 1 SET @TotalCountSql= N'select @TotalRecord = count(\*) from ' + @TableName;--总记录数语句 SET @SqlString = N'(select row\_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句 -- IF (@WhereString! = '' or @WhereString!=null) BEGIN SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString; SET @SqlString =@SqlString+ ' where '+ @WhereString; END --第一次执行得到 --IF(@TotalRecord is null) -- BEGIN EXEC sp\_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数 -- END ----执行主语句 set @SqlString ='select \* from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord)); Exec(@SqlString) END \--调用分页存储过程book\_page exec book\_page 'books','\*','book\_id','',3,1,0; \-- declare @totalCount int exec book\_page 'books','\*','book\_id','',3,1,@totalCount output; select @totalCount as totalCount;--总记录数。 转载于:https://www.cnblogs.com/mingqi-420/p/10664908.html
相关 存储过程 什么是存储过程? 存储过程是由T-sql语句组成,通过这个语句实现一个功能并且给这个语句起个名字,待用到此功能的时候调用这个名字即可。 存储过程的好处: 1.数据库 ╰半夏微凉°/ 2022年07月13日 10:50/ 0 赞/ 179 阅读
相关 存储过程 MySQL的存储过程(procedure),也叫存储程序。它是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。可以通过存储过程的名称对其进行调用。 存储过程跟触发 浅浅的花香味﹌/ 2022年05月30日 02:09/ 0 赞/ 209 阅读
相关 存储过程一 存储过程介绍 存储过程是由流控制和SQL语句书写的过程,这个过程经编译和优化 后存储在数据库服务器中,使用时只要调用即可。在ORACLE中,若干个 有联系的过程可以组合在一起构成程序 川长思鸟来/ 2022年05月26日 03:13/ 0 赞/ 238 阅读
相关 存储过程 存储过程 文前话: 内心一场金戈铁马 -------------------- 定义: 1.存储过程(Stored Procedure)是在大型数 ﹏ヽ暗。殇╰゛Y/ 2022年05月09日 00:24/ 0 赞/ 221 阅读
相关 存储过程 dbms\_output.put\_line : 用于打印信息 create or replace PROCEDURE ALLJOB AS 用于创建存储过程 ,名称 港控/mmm°/ 2022年04月23日 05:20/ 0 赞/ 236 阅读
相关 存储过程 1、建立存储过程完成图书管理系统中的借书功能。 功能要求: l 借书时要求输入借阅流水号,借书证号,图书编号。(即该存储 淩亂°似流年/ 2022年04月22日 06:16/ 0 赞/ 340 阅读
相关 存储过程 Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以 ゞ 浴缸里的玫瑰/ 2022年01月22日 03:29/ 0 赞/ 265 阅读
相关 存储过程 1. 存储过程的类型: (1) 用户自定义存储过程 自定义存储过程即用户使用T\_SQL语句编写的、为了实现某一特定业务需求,在用户数据库 旧城等待,/ 2021年11月04日 22:12/ 0 赞/ 455 阅读
相关 存储过程 存储过程在我们后端开发很常用,我们经常在开发过程中需要写存储过程,其实存储过程没啥难的,就是一个固定的格式,最难的在于存储过程中我们要写的逻辑,业务方面的东西,这些就需要我们对 柔光的暖阳◎/ 2021年11月01日 23:20/ 0 赞/ 406 阅读
还没有评论,来说两句吧...