SqlServer中的存储进程和游标操作ITeye - 娱乐之横扫全球

SqlServer中的存储进程和游标操作ITeye

2019年03月24日08时54分12秒 | 作者: 青寒 | 标签: 进程,存储,游标 | 浏览: 792

开篇叙述-
首要介绍一下什么是存储进程:
  存储进程便是将常用的或很杂乱的作业,预先用SQL句子写好并用一个指定的称号存储起来,而且这样的句子是放在数据库中的,
  还能够依据条件履行不同SQL句子, 那么今后要叫数据库供给与已界说好的存储进程的功用相同的效劳时,只需调用execute,即可主动完结指令。
-注意事项SQL2008 R2
*修正存储进程和从头创立存储进程的差异:
  假如需求更改存储进程中的句子或参数,能够删去并从头创立该存储进程,也能够经过一个进程更改该存储进程。
  删去并从头创立存储进程时,与该存储进程相关的一切权限都将丢掉。更改存储进程时,将更改进程或参数界说,
  但为该存储进程界说的权限将保存,而且不会影响任何相关的存储进程或触发器。还能够修正存储进程以加密其定
  义或使该进程在每次履行时都得到从头编译。
*修正存储进程犯错:
  更改存储进程的称号或界说可能会导致一切相关目标在履行时失利,假如这些相关目标未进行更新以反映对该存储进程所做的更改。
 
1、SQLserver存储进程的语法

Create PROC [ EDURE ] procedure_name  [  number ]  [ { @parameter data_type }
  [ VARYING ] [ = default ] [ OUTPUT ]  ] [ ,...n ] 
  [ WITH
  { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] 
  [ FOR REPLICATION ] 
  AS sql_statement [ ...n ]

简略一点便是:Create PROCEDURE  procedure_name  [参数个数]  [有参数/没有参数  ]  AS  进程体  GO
存储进程的调用:execute procedure  [参数]

2、-检查存储进程-
若要检查存储进程的界说
sys.sql_modules (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL)
sp_helptext (Transact-SQL)
检查有关存储进程的信息
sys.objects (Transact-SQL)
sys.procedures (Transact-SQL)
sys.parameters (Transact-SQL)
sys.numbered_procedures (Transact-SQL)
sys.numbered_procedure_parameters (Transact-SQL)
sp_help (Transact-SQL)
检查存储进程的依靠联系
sys.sql_expression_dependencies (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)
检查有关扩展存储进程的信息
sp_helpextendedproc (Transact-SQL) 

3、存储进程示例:
示例1:简略数据查询
create proc query_book
  as
  select * from book
go
调用存储进程
exec query_book
示例2:存储进程参数传递
create procedure dbo.Get_operator
  @Czy_code varchar(10),

  @Czy_password varchar(10)
as
declare Czy_name varchar(10)
declare Czy_in_station varchar(10)
 
  begin transaction
  select @Czy_name = name,@Czy_in_station = in_station
  from dbo.User where code = @Czy_code and password =@Czy_password
  commit
  select @Czy_code,@Czy_name,@Czy_in_station

GO
示例3:分页存储进程
create proc commonPagination
  @columns varchar(500),  要显现的列名,用逗号离隔
  @tableName varchar(100), 要查询的表名
  @orderColumnName varchar(100), 排序的列名
  @order varchar(50),  排序的办法,升序为asc,降序为 desc
  @where varchar(100), where 条件,假如不带查询条件,请用 1=1
  @pageIndex int, 当时页索引
  @pageSize int,  页巨细(每页显现的记载条数)
  @pageCount int output 总页数,输出参数
as
  begin
  declare @sqlRecordCount nvarchar(1000) 得到总记载条数的句子
  declare @sqlSelect nvarchar(1000) 查询句子
  set @sqlRecordCount=Nselect @recordCount=count(*) from
  +@tableName + where + @where
  declare @recordCount int 保存总记载条数的变量
  exec sp_executesql @sqlRecordCount,N@recordCount int output,@recordCount output
 
  动态 sql 传参
  if( @recordCount % @pageSize = 0) 假如总记载条数能够被页巨细整除
  set @pageCount = @recordCount / @pageSize 总页数就等于总记载条数除以页巨细
  else 假如总记载条数不能被页巨细整除
  set @pageCount = @recordCount / @pageSize + 1 总页数就等于总记载条数除以页巨细加1
  set @sqlSelect =
  Nselect +@columns+ from (
  select row_number() over (order by
  +@orderColumnName+ +@order
  +) as tempid,* from
  +@tableName+ where + @where
  +) as tempTableName where tempid between
  +str((@pageIndex - 1)*@pageSize + 1 )
  + and +str( @pageIndex * @pageSize)
  exec (@sqlSelect) 履行动态Sql
  end
go
以下是调用示例
use pubs
  go
declare @pageCount int
exec commonPagination  job_id,job_desc,jobs,job_id, asc,1=1,2,2,@pageCount output
select 总页数为: + str(@pageCount)

4、 存储进程输入输出演示
  存储进程的3种传回值(便利正在看这个比如的朋友不必再去检查语法内容): 
  1.以Return传回整数 
  2.以output格局传回参数 
  3.Recordset 
  传回值的差异:  output和return都可在批次程式顶用变量接纳,而recordset则传回到履行批次的客户端中。

  实例1:只回来单一记载集的存储进程。

  要求:查询表bankMoney的内容的存储进程
  create procedure sp_query_bankMoney  as
  select * from bankMoney
  go
  exec sp_query_bankMoney
  注*  在运用进程中只需求把中的SQL句子替换为存储进程名,就能够了很便利吧!

  实例2(向存储进程中传递参数):

  参加一笔记载到表bankMoney,并查询此表中userID= Zhangsan的一切存款的总金额。 
  Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output  with encryption -加密  as 
  insert bankMoney (id,userID,sex,Money) Values(@param1,@param2,@param3, @param4)
  select @param5=sum(Money) from bankMoney where userID=Zhangsan  go
  在SQL Server查询分析器中履行该存储进程的办法是:  declare @total_price int
  exec insert_bank 004,Zhangsan,男,100,@total_price output  print 总余额为+convert(varchar,@total_price)  go

  实例3:运用带有杂乱 Select 句子的简略进程

  下面的存储进程从四个表的联接中回来一切作者(供给了名字)、出书的书本以及出书社。该存储进程不运用任何参数。  USE pubs
  IF EXISTS (Select name FROM sysobjects  Where name = au_info_all AND type = P)  Drop PROCEDURE au_info_all  GO
  Create PROCEDURE au_info_all  AS
  Select au_lname, au_fname, title, pub_name  FROM authors a INNER JOIN titleauthor ta  ON a.au_id = ta.au_id INNER JOIN titles t  ON t.title_id = ta.title_id INNER JOIN publishers p  ON t.pub_id = p.pub_id  GO
  au_info_all 存储进程能够经过以下办法履行:  EXECUTE au_info_all  or
  EXEC au_info_all
  假如该进程是批处理中的第一条句子,则可运用:  au_info_all 

  实例4:运用带有参数的简略进程

  Create PROCEDURE au_info  @lastname varchar(40),  @firstname varchar(20)  AS
  Select au_lname, au_fname, title, pub_name  FROM authors a INNER JOIN titleauthor ta  ON a.au_id = ta.au_id INNER JOIN titles t  ON t.title_id = ta.title_id INNER JOIN publishers p  ON t.pub_id = p.pub_id  Where  au_fname = @firstname  AND au_lname = @lastname  GO
  au_info 存储进程能够经过以下办法履行:  EXECUTE au_info Dull, Ann  or
  EXECUTE au_info @lastname = Dull, @firstname = Ann  or
  EXECUTE au_info @firstname = Ann, @lastname = Dull  or
  EXEC au_info Dull, Ann  or
  EXEC au_info @lastname = Dull, @firstname = Ann  or
  EXEC au_info @firstname = Ann, @lastname = Dull  假如该进程是批处理中的第一条句子,则可运用:  au_info Dull, Ann  or
  au_info @lastname = Dull, @firstname = Ann  or
  au_info @firstname = Ann, @lastname = Dull

  实例5:运用带有通配符参数的简略进程

  Create PROCEDURE au_info2  @lastname varchar(30) = D%,  @firstname varchar(18) = %  AS
  Select au_lname, au_fname, title, pub_name  FROM authors a INNER JOIN titleauthor ta  ON a.au_id = ta.au_id INNER JOIN titles t  ON t.title_id = ta.title_id INNER JOIN publishers p  ON t.pub_id = p.pub_id  Where au_fname LIKE @firstname  AND au_lname LIKE @lastname  GO
  au_info2 存储进程能够用多种组合履行。下面只列出了部分组合:  EXECUTE au_info2  or
  EXECUTE au_info2 Wh%  or
  EXECUTE au_info2 @firstname = A%  or
  EXECUTE au_info2 [CK]ars[OE]n  or
  EXECUTE au_info2 Hunter, Sheryl  or
  EXECUTE au_info2 H%, S%
5、存储进程中游标的运用
(1)、需求游标的数据操作

  当select句子的成果中包括多个元组时,运用游标能够逐一存取这些元组 
  活动集:select句子回来的元组的调集 
  当时行:活动会集当时处理的那一行。游标便是指向当时行的指针。 
(2)、游标分类

  翻滚游标:游标的方位能够来回移动,可在活动会集取恣意元组。 
  非翻滚游标:只能在活动会集次序地取下一个元组。 
  更新游标:数据库对游标指向的当时行加锁,当程序读下一行数据时,本行数据解锁,下一行数据加锁。 
(3)、界说与运用游标的句子

  declare : 
  declare  游标名[scroll]  cursor  for select句子[for update [of列表名]] 界说一个游标,使之对应一个select句子 
  for update任选项,表明该游标可用于对当时行的修正与删去 
  open 
  翻开一个游标,履行游标对应的查询,成果调集为该游标的活动集 
  open  游标名 
  fetch 
  在活动会集将游标移到特定的行,并取出该行数据放到相应的变量中 
  fetch [next | prior | first | last | current | relative n | absolute m] 游标名into  [变量表]
  close 
  封闭游标,开释活动集及其所占资源。需求再运用该游标时,履行open句子 
  close  游标名 
  deallocate 
  删去游标,今后不能再对该游标履行open句子 
  deallocate 游标名 
  @@FETCH_STATUS 
  回来被FETCH 句子履行的最终游标的状况. 
  0 fetch句子成功 
  -1 fetch句子失利 
  -2 被提取的行不存在
(4)、游标实例
  例:查询电子商务系学生信息,性别为女输出为female,不然输出为male? 
  declare c1 cursor for select sno,sname,ssex from student where sdept=ec 
  declare @sno char(10),@sname char(10),@ssex char(2) 
  Open c1 
  Fetch c1 into @sno,@sname,@ssex 
  While @@fetch_status0 
  Begin 
  if @ssex=女 
  begin  set @ssex=female end 
  else
  begin set @ssex=male end 
  Select @sno,@sname ,@ssex 
  Fetch c1 into @sno,@sname,@ssex 
  end

(5)修正存储进程
例: ALTER PROC [dbo].[dnt_UserRecoveryByUserName]  @username  NVARCHAR(50) AS 
BEGIN 
  DECLARE  @uid INT;  DECLARE  @tid INT;  DECLARE  @replies INT;
  DECLARE  @temp varchar(50); 
  SET @uid  = (SELECT TOP(1) uid FROM dnt_users WHERE username = @username);
  SET @tid  = 0;  SET @replies  = 0; 
  UPDATE dnt_users SET accessmasks = 0 WHERE uid = @uid;
  UPDATE dnt_userforum SET groupid = 5 WHERE groupid = 4 AND uid = @uid;  UPDATE dnt_posts SET invisible = 0 WHERE invisible = -1 AND posterid = @uid; 
  界说一游标
  DECLARE Ctemp CURSOR FOR SELECT tid FROM dnt_topics WHERE posterid = @uid FOR READ only FOR UPDATE 
  OPEN Ctemp 
  FETCH next FROM Ctemp INTO @tid;  WHILE (@@fetch_status = 0)  BEGIN
  SET @replies = (SELECT COUNT(1) FROM dnt_posts WHERE tid = @tid AND layer
  UPDATE dnt_topics SET replies = @replies WHERE posterid = @uid AND tid = @tid;
  FETCH next FROM Ctemp INTO @tid;  END
  CLOSE Ctemp;
  DEALLOCATE Ctemp;
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表娱乐之横扫全球立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章