存储过程,Server基础之存储过程
分类:美高梅-数据

一、存款和储蓄进程概述

  SQL Server中的存款和储蓄进程是使用T_SQL编写的代码段。它的意在能够有助于的从系统表中查询消息,也许完结与立异数据库表相关的田间管理职务和任何的系统管理职务.T_SQL语句是SQL Server数据库与应用程序之间的编制程序接口。在十分多景观下,一些代码会被开荒者重新编写多次,就算老是都编制同样效果的代码,不但繁琐,何况轻巧失误,并且由于SQL Server逐个的执行语句会收缩系统的周转效用。

  简来说之,存款和储蓄进程正是SQL Server为了完成特定职务,而将一部分亟待频仍调用的固化操作语句,或者某个事情过于繁琐,要求编写制定大批量逻辑和查询,严重影响程序性能,编写成程序段,那么些程序段存款和储蓄在服务器上,有数据库服务器通进度序来调用。

 

  一句话来说,存储进度便是一条可能多条sql语句的聚合,可身为批管理公事,可是其效劳不只有限于批管理。本篇首要介绍变量的行使,存款和储蓄进度和存款和储蓄函数的成立,调用,查看,修改以及去除操作。

SQL Server基础之存款和储蓄进程

 阅读目录

  • 一:存储进度概述
  • 二:存储进程分类
  • 三:创设存款和储蓄进程
  • 1.创办无参存储进程
  • 2.修改存款和储蓄进程
  • 3.剔除存款和储蓄进程
  • 4.重命名存款和储蓄进程
  • 5.创制带参数的蕴藏进度

  简单的话,存款和储蓄进程便是一条或许多条sql语句的聚众,可视为批处理文件,可是其作用不唯有限于批处理。本篇首要介绍变量的选用,存款和储蓄过程和仓库储存函数的始建,调用,查看,修改以及去除操作。

回来顶上部分

 

二、存款和储蓄进度的帮助和益处

  1. 积累进程加速系统运转速度,存款和储蓄进度只在创建时编写翻译,以往每趟实践时无需再一次编译。
  2. 仓库储存进度能够打包复杂的数据库操作,简化操作流程,比方对七个表的更新,删除等。
  3. 可完毕模块化的主次设计,存款和储蓄进度能够每每调用,提供联合的数据库访谈接口,创新应用程序的可维护性。
  4. 积累进程能够追加代码的安全性,对于顾客不可能直接操作存款和储蓄进程中引用的目的,SQL  Server能够设定客户对点名存款和储蓄进度的实行权限。
  5. 存款和储蓄进程能够下跌互连网流量,存款和储蓄进程代码直接存款和储蓄于数据库中,在顾客端与服务器的通讯进度中,不会发出一大波的T_SQL代码流量。

一:存款和储蓄进程概述

 SQL Server中的存款和储蓄进度是使用T_SQL编写的代码段。它的意在能够方便的从系统表中查询音讯,恐怕完毕与立异数据库表相关的田间管理职务和任何的系统管理职分.T_SQL语句是SQL Server数据库与应用程序之间的编制程序接口。在点不清场地下,一些代码会被开采者重新编写多次,假诺老是都编写制定同样效果的代码,不但繁琐,并且轻巧失误,何况由于SQL Server逐一的实施语句会减少系统的周转作用。

 简单的说,存款和储蓄进程正是SQL Server为了完成特定使命,而将有些内需反复调用的固定操作语句编写成程序段,这么些程序段存款和储蓄在服务器上,有数据库服务器通进度序来调用。

仓库储存进程的帮助和益处:

  1. 积累过程加速系统运转速度,存款和储蓄进程只在创设时编写翻译,以往每趟实践时无需再次编写翻译。
  2. 仓库储存进度可以打包复杂的数据库操作,简化操作流程,举个例子对八个表的更新,删除等。
  3. 可完成模块化的主次设计,存款和储蓄进程可以再三调用,提供统一的数据库访谈接口,立异应用程序的可维护性。
  4. 储存进度能够追加代码的安全性,对于客商不能够直接操作存款和储蓄进程中援引的对象,SQL  Server能够设定顾客对点名存款和储蓄进度的实行权限。
  5. 存款和储蓄进度能够下落互联网流量,存款和储蓄进程代码直接存款和储蓄于数据库中,在客商端与服务器的通讯过程中,不会发出大批量的T_SQL代码流量。

仓库储存进程的毛病:

  1. 数据库移植不方便人民群众,存储进度信赖与数据库管理类别, SQL Server 存款和储蓄进程中封装的操作代码无法直接移植到别的的数据库管理种类中。
  2. 不扶助面向对象的统一筹算,不可能利用面向对象的格局将逻辑业务开展包装,乃至形成通用的可协理服务的政工逻辑框架.
  3. 代码可读性差,不易维护。不支持集群。

一:存款和储蓄进程概述

 SQL Server中的存储进程是使用T_SQL编写的代码段。它的意在能够有助于的从系统表中查询新闻,或然达成与更新数据库表相关的治本职务和其余的系统管理职务.T_SQL语句是SQL Server数据库与应用程序之间的编制程序接口。在无数状态下,一些代码会被开采者重新编写数次,假如每便都编写制定同样成效的代码,不但繁琐,并且便于出错,而且由于SQL Server逐个的实行语句会收缩系统的运作效用。

 简单的说,存款和储蓄进程正是SQL Server为了达成特定任务,而将部分需求频仍调用的定势操作语句编写成程序段,那么些程序段存款和储蓄在服务器上,有数据库服务器通进程序来调用。

积攒进度的亮点:

  1. 储存进度加速系统运作速度,存款和储蓄进度只在成立时编写翻译,未来每回实行时无需重新编写翻译。
  2. 储存进度能够打包复杂的数据库操作,简化操作流程,举个例子对八个表的换代,删除等。
  3. 可落成模块化的次序设计,存款和储蓄进度能够频仍调用,提供联合的数据库访谈接口,立异应用程序的可维护性。
  4. 存款和储蓄进度可以扩大代码的安全性,对于客户不能够一向操作存款和储蓄进度中引用的靶子,SQL  Server能够设定顾客对点名存款和储蓄进度的实施权限。
  5. 仓储进度能够减少网络流量,存款和储蓄进程代码直接存款和储蓄于数据库中,在客商端与服务器的通讯进程中,不会爆发大量的T_SQL代码流量。

积累进程的毛病:

  1. 数据库移植不便利,存款和储蓄进度信赖与数据库管理类别, SQL Server 存储过程中封装的操作代码不能直接移植到别的的数据库管理种类中。
  2. 不扶助面向对象的宏图,不可能选择面向对象的方法将逻辑业务扩充包装,以致产生通用的可协理服务的专业逻辑框架.
  3. 代码可读性差,不易维护。不援救集群。

回来顶上部分

实践6:存款和储蓄进度

 

二:存款和储蓄进度分类

1.种类存款和储蓄进程

  系统存款和储蓄过程是 SQL Server系统自个儿提供的蕴藏进度,可以视作命令实行各个操作。

  系统存款和储蓄进程主要用来从系统表中获取新闻,使用系统存款和储蓄进程做到数据库服务器的处总管业,为系统管理员提供协助,为客商查看数据库对象提供方便,系统存款和储蓄进程位于数据库服务器中,并且以sp_初阶,系统存款和储蓄进程定义在系统定义和客户定义的数据库中,在调用时不必在储存进程前加多少库限定名。比如:sp_rename系统存款和储蓄进程能够修改当前数据库中顾客创立对象的称号,sp_helptext存储进程能够显得准绳,默许值或视图的文本音信,SQL SESportageVE昂科拉服务器中众多的管理专门的学业都是因而实践系统存款和储蓄进度来成功的,大多系统新闻也得以经超过实际践系统存款和储蓄进程来博取。

  系统存款和储蓄进程创制并寄放在与系统数据库master中,一些类别存款和储蓄进程只可以由系统管理员使用,而略带系统存款和储蓄过程通过授权能够被别的客商所使用。

2.客户存款和储蓄进程(自定义存款和储蓄进程)

  自定义存款和储蓄进程即客商使用T_SQL语句编写的、为了落实某一特定业务须求,在客户数据库中编辑的T_SQL语句集合,自定义存款和储蓄进程可以承受输入参数、向客商端重返结果和音讯,再次回到输出参数等。创造自定义存款和储蓄进程时,存款和储蓄进程名前加上"##"表示创设了二个大局的权且存款和储蓄进程;存款和储蓄进度前边加上"#"时,表示成立的有些有的时候存款和储蓄进程。局地一时存款和储蓄过程只好在开创它的回复中选取,会话截止时,将被删除。那三种存储进度都存款和储蓄在tempdb数据库中。

顾客定义的存放进程分成两类:T_SQL 和CLR

T_SQL:仓库储存进程是值保存的T_SQL语句集合,能够承受和再次来到客商提供的参数,存款和储蓄进度也说不定从数据库向客户端应用程序重返数据。

CL奥迪Q3存款和储蓄进程是指导用Microsoft.NET Framework公共语言的主意囤积进度,还可以和再次来到客商提供的参数,它们在.NET Framework程序集是作为类的公物静态方法达成的。

3.扩大存款和储蓄进程

 扩大存款和储蓄进度是以在SQL SEPRADOVESportage意况外执行的动态连接(DLL文件)来贯彻的,能够加载到SQL SE翼虎VE途乐实例运营的地方空间中推行,增加存款和储蓄进程能够用SQL SE猎豹CS6VE奥迪Q3扩张存款和储蓄进程API编制程序,扩张存储进程从前缀"xp_"来标记,对于客商来说,扩张存款和储蓄进度和国语存款和储蓄过程同样,能够用一样的点子来实行。 

二:存款和储蓄进程分类

1.种类存款和储蓄过程

  系统存款和储蓄进程是 SQL Server系统自个儿提供的积累进程,能够当做命令实施各类操作。

  系统存款和储蓄进程首要用以从系统表中获取新闻,使用系统存款和储蓄进程完毕数据库服务器的管理专门的学业,为系统管理员提供增加援救,为客商查看数据库对象提供方便,系统存款和储蓄进度位于数据库服务器中,并且以sp_始发,系统存储进度定义在系统定义和顾客定义的数据库中,在调用时不要在存款和储蓄进度前扩张少库限定名。举个例子:sp_rename系统存款和储蓄进度能够修改当前数据库中顾客成立对象的名目,sp_helptext存款和储蓄进程能够体现准绳,暗中同意值或视图的文件音讯,SQL SEEnclaveVE奥迪Q7服务器中有的是的管监护人业都以经过施行系统存储进程来成功的,多数系统消息也足以透过实施系统存款和储蓄进程来收获。

  系统存款和储蓄进度创立并贮存在与系统数据库master中,一些种类存款和储蓄进度只可以由系统管理员使用,而有一点系统存款和储蓄进程通过授权能够被别的顾客所利用。

2.客商存款和储蓄进程(自定义存款和储蓄进程)

  自定义存款和储蓄进度即顾客使用T_SQL语句编写的、为了促成某一特定业务供给,在客商数据库中编辑的T_SQL语句集结,自定义存款和储蓄进度能够承受输入参数、向顾客端重临结果和新闻,重临输出参数等。创设自定义存款和储蓄进度时,存储进程名前加上"##"表示成立了贰个大局的权且存款和储蓄进度;存款和储蓄进度前边加上"#"时,表示成立的片段不常存款和储蓄进程。局地临时存储进度只可以在创制它的回应中央银行使,会话甘休时,将被去除。那二种存款和储蓄进程都存款和储蓄在tempdb数据库中。

客户定义的囤积进度分成两类:T_SQL 和CLR

T_SQL:储存进度是值保存的T_SQL语句集结,尚可和重回客商提供的参数,存款和储蓄进度也可能从数据库向顾客端应用程序重返数据。

CLCRUISER存款和储蓄进程是引导用Microsoft.NET Framework公共语言的法子囤积进度,可以承受和重回顾客提供的参数,它们在.NET Framework程序集是作为类的公家静态方法达成的。

3.恢宏存款和储蓄进程

 扩充存款和储蓄进度是以在SQL SE科雷傲VEHighlander意况外实践的动态连接(DLL文件)来促成的,能够加载到SQL SE凯雷德VECR-V实例运转的地点空间中推行,扩张存款和储蓄进度能够用SQL SE兰德猎豹CS6VEOdyssey扩大存款和储蓄进程API编制程序,增加存款和储蓄进度以前缀"xp_"来标记,对于客商来讲,扩大存款和储蓄进度和汉语存款和储蓄进度同样,能够用一样的不二秘技来推行。 

重临最上端

仓库储存进度实验满含3个实验项目,个中2个必修实验项目,1个选修实验项目,均为设计型实验项目,选作个中的一个。

三、存储进度分类

1.连串存款和储蓄进程

  系统存款和储蓄进程是 SQL Server系统本人提供的储存进度,能够当作命令实行各类操作。

  系统存款和储蓄进度主要用来从系统表中获取新闻,使用系统存款和储蓄进度做到数据库服务器的管理专门的学业,为系统一管理理员提供赞助,为客户查看数据库对象提供方便,系统存款和储蓄进程位于数据库服务器中,何况以sp_发端,系统存款和储蓄进度定义在系统定义和客商定义的数据库中,在调用时不要在蕴藏进程前扩充少库限定名。例如:sp_rename系统存款和储蓄进程能够修改当前数据库中客户创设对象的称谓,sp_helptext存款和储蓄进度能够体现准绳,暗中同意值或视图的文件音信,SQL SELacrosseVE奥迪Q7服务器中非常多的处监护人业都以经超过实际施系统存款和储蓄进程来达成的,大多系统音讯也得以因此实行系统存款和储蓄进度来获得。

  系统存款和储蓄过程创立并存放在与系统数据库master中,一些类别存款和储蓄进程只好由系统管理员使用,而略带系统存款和储蓄进程通过授权能够被其余顾客所运用。

2.客商存款和储蓄进度(自定义存款和储蓄进程)

  自定义存款和储蓄进度即客户使用T_SQL语句编写的、为了贯彻某一特定业务须求,在客商数据库中编辑的T_SQL语句集结,自定义存款和储蓄进程可以承受输入参数、向客商端重临结果和音讯,再次回到输出参数等。创造自定义存储进程时,存款和储蓄进度名前加上"##"表示创制了二个大局的临时存款和储蓄进度;存储进度前边加上"#"时,表示制造的片段有时存款和储蓄进度。局地有时存款和储蓄进度只好在开立它的答疑中央银行使,会话结束时,将被去除。那三种存款和储蓄进程都存款和储蓄在tempdb数据库中。

客商定义的囤积进度分成两类:T_SQL 和CLR

T_SQL:积累进度是值保存的T_SQL语句集结,能够承受和再次来到顾客提供的参数,存款和储蓄进度也恐怕从数据库向客户端应用程序再次来到数据。

CLENCORE存款和储蓄进程是指援引Microsoft.NET Framework公共语言的办法囤积进度,还可以和重临顾客提供的参数,它们在.NET Framework程序集是作为类的共用静态方法实现的。

3.扩充存款和储蓄进度

 扩充存款和储蓄进度是以在SQL SERVE景逸SUV景况外实践的动态连接(DLL文件)来实现的,能够加载到SQL SE翼虎VESportage实例运转的地方空间中实践,扩张存款和储蓄进度能够用SQL SEENVISIONVELacrosse扩展存款和储蓄进度API编制程序,扩张存款和储蓄进程之前缀"xp_"来标志,对于客商来讲,增添存储进程和中文存款和储蓄进程一样,能够用平等的不二秘诀来奉行。 

 

三:创立存款和储蓄进程

工欲善其事,必先利其器,准备数据如下:

use sample_db;
--创建测试books表
create table books (
    book_id int identity(1,1) primary key,
    book_name varchar(20),
    book_price float,
    book_auth varchar(10)
);
--插入测试数据
insert into books (book_name,book_price,book_auth)
                    values
                        ('论语',25.6,'孔子'),
                        ('天龙八部',25.6,'金庸'),
                        ('雪山飞狐',32.7,'金庸'),
                        ('平凡的世界',35.8,'路遥'),
                        ('史记',54.8,'司马迁');

三:创制存储进度

工欲善其事,必先利其器,希图数据如下:

美高梅4858官方网站 1

use sample_db;
--创建测试books表
create table books (
    book_id int identity(1,1) primary key,
    book_name varchar(20),
    book_price float,
    book_auth varchar(10)
);
--插入测试数据
insert into books (book_name,book_price,book_auth)
                    values
                        ('论语',25.6,'孔子'),
                        ('天龙八部',25.6,'金庸'),
                        ('雪山飞狐',32.7,'金庸'),
                        ('平凡的世界',35.8,'路遥'),
                        ('史记',54.8,'司马迁');

美高梅4858官方网站 2

重返顶上部分

实验6.1 积累进度实验

四、存款和储蓄进度实际上援用

1. 创造无参存款和储蓄进程

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
    drop proc proc_get_student
go
create procedure getAllBooks
as
select * from books;
--调用,执行存储过程
exec getAllBooks;

 

2. 修改存款和储蓄进度

alter procedure dbo.getAllBooks 
as
select book_auth from books;

 

3. 剔除存款和储蓄进程

drop procedure getAllBooks;

 

4. 重命名存款和储蓄进程

sp_rename getAllBooks,proc_get_allBooks;

 

5. 创制带参数的寄放过程,那也是积存进度最广大的款型

积攒进度的参数分为三种:输入参数和出口参数

输入参数:用于向存款和储蓄进度传入值,类似java语言或则c中的值传递。

出口参数:用以调用存款和储蓄进度后,参加会议结果,类似java语言的按引用传递。

值传递和援用传递差别:

  • 着力数据类型赋值属于值传递;援引类型之间赋值属于引用传递。
  • 值传递传递的是逼真的变量值;引用传递传递的是指标的引用地址。
  • 值传递后,八个变量改造的是分别的值;援用传递后,三个援引改换的是同三个指标的场合

5.1)带贰个参数存款和储蓄进程

if (exists (select * from sys.objects where name = 'searchBooks'))
    drop proc searchBooks
go
create proc searchBooks(@bookID int)
as
    --要求book_id列与输入参数相等
    select * from books where book_id=@bookID;
--执行searchBooks
exec searchBooks 1;

 

5.2)带2个参数存款和储蓄进程**

if (exists (select * from sys.objects where name = 'searchBooks1'))
    drop proc searchBooks1
go
create proc searchBooks1(
    @bookID int,
    @bookAuth varchar(20)
)
as
    --要求book_id和book_Auth列与输入参数相等
    select * from books where book_id=@bookID and book_auth=@bookAuth;
exec searchBooks1 1,'金庸';

 

5.3)开创有再次来到值的积攒进程**

if (exists (select * from sys.objects where name = 'getBookId'))
    drop proc getBookId
go
create proc getBookId(
    @bookAuth varchar(20),--输入参数,无默认值
    @bookId int output --输入/输出参数 无默认值
)
as
    select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字

 

5.4)创设带通配符的仓库储存进度**

if (exists (select * from sys.objects where name = 'charBooks'))
    drop proc charBooks
go
create proc charBooks(
    @bookAuth varchar(20)='金%',
    @bookName varchar(20)='%'
)
as 
    select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程charBooks
exec  charBooks    '孔%','论%';

 

5.5)加密囤积进程**

with encryption子句对客户遮盖存款和储蓄进度的文本.下例成立加密经过,使用 sp_helptext 系统存款和储蓄进度获得有关加密进度的音讯,然后尝试直接从 syscomments 表中获得有关该进程的音讯。

if (object_id('books_encryption', 'P') is not null)
    drop proc books_encryption
go
create proc books_encryption 
with encryption
as 
    select * from books;
--执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"

 

5.6)不缓存存款和储蓄进度**

--with  recompile不缓存
if (object_id('book_temp', 'P') is not null)
    drop proc book_temp
go
create proc book_temp
with recompile
as
    select * from books;
go

exec book_temp;
exec sp_helptext 'book_temp';

 

5.7)创造带游标参数的存款和储蓄进度**

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.8)创制分页存储进程**

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;--总记录数。

 

 

 

PS:招待扫描下方二维码,插足QQ群

美高梅4858官方网站 3

 

作者:Jacky

来源:

扬言:本文版权归作者和新浪共有,应接转发,但未经小编同意必需保留此段申明,且在篇章页面明显地点给出原作连接,不然保留追究法律义务的职分。

 

1.开立无参存款和储蓄进度

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
    drop proc proc_get_student
go
create procedure getAllBooks
as
select * from books;
--调用,执行存储过程
exec getAllBooks;

1.创制无参存款和储蓄进程

美高梅4858官方网站 4

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
    drop proc proc_get_student
go
create procedure getAllBooks
as
select * from books;
--调用,执行存储过程
exec getAllBooks;

美高梅4858官方网站 5

回去顶上部分

(1)实验指标

2.修改存款和储蓄进程

alter procedure dbo.getAllBooks 
as
select book_auth from books;

2.更改存款和储蓄进程

alter procedure dbo.getAllBooks 
as
select book_auth from books;

再次来到最上端

垄断数据库PL/SQL编制程序语言,以及数据仓库储存款和储蓄进度的设计和使用办法

3.刨除存款和储蓄进度

drop procedure getAllBooks;

3.剔除存款和储蓄进度

drop procedure getAllBooks;

回到顶上部分

 

4.重命名存款和储蓄进程

sp_rename getAllBooks,proc_get_allBooks;

4.重命名存款和储蓄进程

sp_rename getAllBooks,proc_get_allBooks;

回到顶上部分

(2)实验内容和供给

5.创建带参数的存储进程

 存款和储蓄进程的参数分为三种:输入参数和出口参数

输入参数:用于向存储进度传入值,类似java语言或则c中的值传递。

出口参数:用于调用存款和储蓄进度后,参加会议结果,类似java语言的按援引传递。

值传递和引用传递差异:

  • 主干数据类型赋值属于值传递;援引类型之间赋值属于援用传递。
  • 值传递传递的是无可争论的变量值;援用传递传递的是指标的引用地址。
  • 值传递后,五个变量退换的是各自的值;援引传递后,七个引用退换的是同四个目的的处境

(1)带叁个参数存款和储蓄进程

if (exists (select * from sys.objects where name = 'searchBooks'))
    drop proc searchBooks
go
create proc searchBooks(@bookID int)
as
    --要求book_id列与输入参数相等
    select * from books where book_id=@bookID;
--执行searchBooks
exec searchBooks 1;

(2)带2个参数存款和储蓄进度

if (exists (select * from sys.objects where name = 'searchBooks1'))
    drop proc searchBooks1
go
create proc searchBooks1(
    @bookID int,
    @bookAuth varchar(20)
)
as
    --要求book_id和book_Auth列与输入参数相等
    select * from books where book_id=@bookID and book_auth=@bookAuth;
exec searchBooks1 1,'金庸';

(3)创立有重返值的存款和储蓄进程

if (exists (select * from sys.objects where name = 'getBookId'))
    drop proc getBookId
go
create proc getBookId(
    @bookAuth varchar(20),--输入参数,无默认值
    @bookId int output --输入/输出参数 无默认值
)
as
    select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字

(4)创制带通配符的存放进程

if (exists (select * from sys.objects where name = 'charBooks'))
    drop proc charBooks
go
create proc charBooks(
    @bookAuth varchar(20)='金%',
    @bookName varchar(20)='%'
)
as 
    select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程charBooks
exec  charBooks    '孔%','论%';

(5)加密仓库储存进程

  with encryption子句对客户遮蔽存款和储蓄过程的文本.下例成立加密进度,使用 sp_helptext 系统存储进程拿到有关加密经过的新闻,然后尝试直接从 syscomments 表中获取有关该进程的音讯.

if (object_id('books_encryption', 'P') is not null)
    drop proc books_encryption
go
create proc books_encryption 
with encryption
as 
    select * from books;
--执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"

(6).不缓存存款和储蓄进程

--with  recompile不缓存
if (object_id('book_temp', 'P') is not null)
    drop proc book_temp
go
create proc book_temp
with recompile
as
    select * from books;
go

exec book_temp;
exec sp_helptext 'book_temp';

(7).创制带游标参数的积攒进程

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; --释放游标

(8).创制分页存储进度

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;--总记录数。

5.创建带参数的储存进度

 存款和储蓄进度的参数分为二种:输入参数和输出参数

输入参数:用于向存储进程传入值,类似java语言或则c中的值传递。

输出参数:用来调用存款和储蓄进度后,参加会议结果,类似java语言的按援用传递。

值传递和援用传递分化:

  • 宗旨数据类型赋值属于值传递;援引类型之间赋值属于援用传递。
  • 值传递传递的是确实的变量值;引用传递传递的是目的的援引地址。
  • 值传递后,多少个变量改造的是独家的值;引用传递后,三个援用改动的是同三个对象的状态

(1)带二个参数存款和储蓄进程

美高梅4858官方网站 6

if (exists (select * from sys.objects where name = 'searchBooks'))
    drop proc searchBooks
go
create proc searchBooks(@bookID int)
as
    --要求book_id列与输入参数相等
    select * from books where book_id=@bookID;
--执行searchBooks
exec searchBooks 1;

美高梅4858官方网站 7

(2)带2个参数存款和储蓄进程

美高梅4858官方网站 8

if (exists (select * from sys.objects where name = 'searchBooks1'))
    drop proc searchBooks1
go
create proc searchBooks1(
    @bookID int,
    @bookAuth varchar(20)
)
as
    --要求book_id和book_Auth列与输入参数相等
    select * from books where book_id=@bookID and book_auth=@bookAuth;
exec searchBooks1 1,'金庸';

美高梅4858官方网站 9

(3)创设有再次回到值的积存进度

美高梅4858官方网站 10

if (exists (select * from sys.objects where name = 'getBookId'))
    drop proc getBookId
go
create proc getBookId(
    @bookAuth varchar(20),--输入参数,无默认值
    @bookId int output --输入/输出参数 无默认值
)
as
    select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字

美高梅4858官方网站 11

(4)创造带通配符的储存进度

美高梅4858官方网站 12

if (exists (select * from sys.objects where name = 'charBooks'))
    drop proc charBooks
go
create proc charBooks(
    @bookAuth varchar(20)='金%',
    @bookName varchar(20)='%'
)
as 
    select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程charBooks
exec  charBooks    '孔%','论%';

美高梅4858官方网站 13

(5)加密囤积进程

  with encryption子句对顾客隐敝存款和储蓄进度的文本.下例缔造加密进度,使用 sp_helptext 系统存款和储蓄进度获得有关加密经过的消息,然后尝试直接从 syscomments 表中收获有关该进程的新闻.

美高梅4858官方网站 14

if (object_id('books_encryption', 'P') is not null)
    drop proc books_encryption
go
create proc books_encryption 
with encryption
as 
    select * from books;
--执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"

美高梅4858官方网站 15

(6).不缓存存储进程

美高梅4858官方网站 16

--with  recompile不缓存
if (object_id('book_temp', 'P') is not null)
    drop proc book_temp
go
create proc book_temp
with recompile
as
    select * from books;
go

exec book_temp;
exec sp_helptext 'book_temp';

美高梅4858官方网站 17

(7).创造带游标参数的仓储进度

美高梅4858官方网站 18

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; --释放游标

美高梅4858官方网站 19

(8).创造分页存款和储蓄进度

美高梅4858官方网站 20

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;--总记录数。

美高梅4858官方网站 21

作者:阿赫瓦里

出处:

本文以读书、钻探和享受为主,版权归作者和新浪共有,接待转发,假使文中有不妥大概失实的地点还望大神您不吝提出。借使感到本文对你拥有支持不及【推荐】一下啊!如若您有更加好的建议,不及留言一齐探究,共同进步!另外,我们也能够支撑一下自家苹果, 再一次感激您耐心的读完本篇小说。

仓库储存进程定义、存款和储蓄过程运转,存款和储蓄进度更名,存款和储蓄进程删除,存款和储蓄进程的参数字传送递。了解PL/SQL编制程序语言和编制程序标准,标准设计存储过程。

 

存款和储蓄进程概述:

SQL Server中的存款和储蓄进度是使用T_SQL编写的代码段。它的目的在于能够有助于的从系统表中查询消息,恐怕完毕与立异数据库表相关的军管任务和其余的系统管理职责.T_SQL语句是SQL Server数据库与应用程序之间的编制程序接口。在重重情景下,一些代码会被开拓者重新编写数十二回,倘诺老是都编写制定一样效果的代码,不但繁琐,并且轻易失误,何况由于SQL Server逐个的执行语句会减弱系统的运维效能。

 

积存进程的优点:

  1. 增长速度系统运作速度,存款和储蓄只在成立时编写翻译,以往每一回实施时无需重新编写翻译。

  2. 能够打包复杂的数据库操作,简化操作流程,比如对多个表的立异,删除等。

  3. 可实现模块化的顺序设计,存款和储蓄进度可一再调用,提供统一的数据库访谈口。

  4. 能够扩大代码的安全性,对于客户不能够一贯操作存款和储蓄进度中援用的目的,SQL Server可以设定客商对点名存款和储蓄进度的实行权限。

  5. 存款和储蓄进程能够减低网络流量。

仓库储存进程的劣势:

  1. 移植不平价,存款和储蓄进程信赖与数据库管理种类,SQL Server存款和储蓄进程中封装的操作代码不能够一向移植到任何的数据库管理体系中。

  2. 不协助面向对象的宏图,不可能选取面向对象的章程将逻辑业务进行李包裹装。

  3. 代码可读性差,不易维护。不支持集群。

积攒进度分类:

1. 系统存款和储蓄进程:系统存款和储蓄进程是 SQL Server系统本身提供的囤积进度,能够视作命令实施各类操作。系统存款和储蓄进程首要用于从系统表中获取音讯,使用系统存款和储蓄进程一呵而就数据库服务器的管理专业,为系统管理员提供增派,为顾客查看数据库对象提供方便,系统存储进度位于数据库服务器中,而且以sp_千帆竞发,系统存款和储蓄进程定义在系统定义和顾客定义的数据库中,在调用时不用在仓库储存进程前增加少库限定名。

2. 客户存款和储蓄进度(自定义存款和储蓄进程):自定义存款和储蓄进度即顾客使用T_SQL语句编写的、为了完结某一特定业务须要,在顾客数据库中编辑的T_SQL语句集合,自定义存储进程能够承受输入参数、向顾客端重返结果和新闻,再次回到输出参数等。客商定义的贮存过程分成两类:T_SQL 和CLR:T_SQL:存款和储蓄进度是值保存的T_SQL语句集结,能够承受和重回顾客提供的参数,存款和储蓄进度也恐怕从数据库向客户端应用程序重临数据。CL宝马7系存储进度是指援引Microsoft.NET Framework公共语言的格局囤积进程,尚可和再次回到顾客提供的参数,它们在.NET Framework程序集是作为类的公物静态方法达成的。

3. 扩展存款和储蓄进度:推而广之存款和储蓄进度是以在SQL SE智跑VE汉兰达情形外实践的动态连接(DLL文件)来贯彻的,能够加载到SQL SE途锐VEENCORE实例运维的地方空间中实践,扩张存款和储蓄进程能够用SQL SE奇骏VEOdyssey扩充存款和储蓄进程API编制程序,扩充存款和储蓄进程此前缀”xp_”来标识,对于客商来说,增加存款和储蓄进度和国语存款和储蓄进程同样,能够用同一的措施来执行。

 

(3)实验入眼和困难

尝试入眼:存款和储蓄进程定义和周转。

实验难题:存款和储蓄进程的参数传递方法。

 

(4)实验进度

实验情形:

操作系统:windows 10 操作系统

         完结软件:SQL Server 二零一二

尝试进程:

始建数据库

use sample_db;

create table books (         --成立测量试验books表

         book_id int identity(1,1) primary key,

         book_name varchar(20),

         book_price float,

         book_auth varchar(10)

);

insert into books (book_name,book_price,book_auth)

values                                            --插入测量试验数据

         ('盗墓笔记',25.6,'南派大伯'),

         ('回到西晋当王爷',35.6,'月当'),

         ('雪山飞狐',32.7,'金庸'),

         ('鹿鼎记',32.7,'金庸'),

         ('甄缳传',32.7,'流潋紫'),

         ('鬼吹灯',45.8,'天下霸唱'),

         ('藏海华',34.8,'南派五叔');

 

 

1. 始建无参数的积攒进度:

1.1创制无参存款和储蓄进程

if (exists (select * from sys.objects where name = 'getAllBooks'))

         drop proc proc_get_student

go

create procedure getAllBooks

as

select * from books;        --调用,推行存款和储蓄进程

exec getAllBooks;

 

1.2.修改存储进度

alter procedure dbo.getAllBooks

as

select book_auth from books;

 

1.3.刨除存款和储蓄进程

drop procedure getAllBooks;

 

1.4.重命名存款和储蓄进程

sp_rename getAllBooks,proc_get_allBooks;

 

2. 创设带参数的存款和储蓄进程

积攒进度的参数分为三种:输入参数和出口参数

输入参数:用于向存款和储蓄进程传入值,类似java语言或则c中的值传递。

出口参数:用于调用存款和储蓄进程后,参加会议结果,类似java语言的按援用传递。

2.1带三个参数存款和储蓄进程

if (exists (select * from sys.objects where name = 'searchBooks'))

         drop proc searchBooks

go

create proc searchBooks(@bookID int)

as

select * from books where book_id=@bookID;      --要求book_id列与输入参数相等

exec searchBooks 1;        --执行searchBooks

 

2.2带2个参数存款和储蓄进度

if (exists (select * from sys.objects where name = 'searchBooks1'))

         drop proc searchBooks1

go

create proc searchBooks1(

         @bookID int,

         @bookAuth varchar(20)

)

as

 --要求book_id和book_Auth列与输入参数相等

         select * from books where book_id=@bookID and book_auth=@bookAuth;

exec searchBooks1 1,'南派四叔';

 

2.3 始建有重临值的蕴藏进程

if (exists (select * from sys.objects where name = 'getBookId'))

         drop proc getBookId

go

create proc getBookId(

         @bookAuth varchar(20),                  --输入参数,无默许值

         @bookId int output                           --输入/输出参数 无私下认可值

)

as

         select @bookId=book_id from books where book_auth=@bookAuth

                                              --施行getBookId这么些带重返值的仓储进程

declare @id int                --声飞鹤个变量用来收取实行存款和储蓄进度后的重回值

exec getBookId '南派四叔',@id output

select @id as bookId;       --as是给重回的列值起三个名字

 

2.4 创设带通配符的储存进程

if (exists (select * from sys.objects where name = 'charBooks'))

         drop proc charBooks

go

create proc charBooks(

         @bookAuth varchar(20)='天下%',

         @bookName varchar(20)='%'

)

as

 select * from books where book_auth like @bookAuth and book_name like @bookName;

exec charBooks '大爷%','盗墓%';            --施行存款和储蓄进度charBooks

 

3. 加密囤积进度

--with encryption子句对客户隐敝存款和储蓄进程的文本.下例创造加密经过,使用 sp_helptext 系统存款和储蓄进度获得有关加密经过的新闻,然后尝试直接从 syscomments 表中拿走有关该进程的消息。

if (object_id('books_encryption', 'P') is not null)

         drop proc books_encryption

go

create proc books_encryption

with encryption

as

         select * from books;

exec books_encryption;            --实施此进度books_encryption

exec sp_helptext 'books_encryption';

--调整台会显示"对象 'books_encryption' 的公文已加密。

 

4.不缓存囤积过程

if (object_id('book_temp', 'P') is not null)      

         drop proc book_temp

go

create proc book_temp

with recompile         --with recompile不缓存

as

         select * from books;

go

exec book_temp;

exec sp_helptext 'book_temp';

 

5. 创设带游标参数的仓储过程

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

declare @cur cursor,       --调用book_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;         --释放游标

 

6.创制分页存款和储蓄进度

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

exec book_page 'books','*','book_id','',3,1,0;         --调用分页存储进度book_美高梅4858官方网站 ,page

declare @totalCount int

exec book_page 'books','*','book_id','',3,1,@totalCount output;

select @totalCount as totalCount;          --总记录数。

 

(5)实验总结:

经过此番试验,学会了选拔SQL Server中的基本存款和储蓄进程,精晓存储进度时是使用T_SQL编写的代码段。它的意在能够有助于的从系统表中查询音讯,完结与更新数据库表相关的田间管理职分和别的的系统管理职务。此番实验相比难,所以仿效了英特网的上学资料,希望由此持续地学习,真正调整数据仓库储存款和储蓄进度。

 

(6)附录

参照他事他说加以考察资料:

尝试相关代码位于压缩文件中

本文由美高梅网站是多少发布于美高梅-数据,转载请注明出处:存储过程,Server基础之存储过程

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文