SQL SERVER 存儲(chǔ)過(guò)程

Coder編程 2022-02-10

展開(kāi)全文

一、存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程是預(yù)編譯的SQL語(yǔ)句的集合，這些語(yǔ)句存儲(chǔ)在一個(gè)名稱(chēng)下并作為一個(gè)單元處理。存儲(chǔ)過(guò)程代替了傳統(tǒng)的逐條執(zhí)行sql語(yǔ)句的方式。一個(gè)存儲(chǔ)過(guò)程中可包含查詢(xún)、插入、更新、刪除等操作的一系列sql語(yǔ)句。當(dāng)這個(gè)存儲(chǔ)過(guò)程被調(diào)用執(zhí)行時(shí)，這些操作也會(huì)同時(shí)執(zhí)行

存儲(chǔ)過(guò)程與其他編程語(yǔ)言的過(guò)程類(lèi)似，它可以接受輸入?yún)?shù)，并以輸出參數(shù)的格式向調(diào)用過(guò)程或批處理返回多個(gè)值；包含用于在數(shù)據(jù)庫(kù)中執(zhí)行操作（包括調(diào)用其他過(guò)程的）的編程語(yǔ)句；向調(diào)用過(guò)程或批處理返回狀態(tài)值，以指明成功或失?。ㄒ约笆〉脑颍?。

創(chuàng)建存儲(chǔ)過(guò)程

create proc [EDURE] procedure_name [:number]
  [{@parameter data_type}
      [VARYING] [=default] [OUTPUT]
  ] [...n]
AS sql_statement

參數(shù)	描述
create procedure	關(guān)鍵字，也可以寫(xiě)成create proc
procedure_name	創(chuàng)建的存儲(chǔ)過(guò)程名字
number	對(duì)存儲(chǔ)過(guò)程進(jìn)行分組
@parameter	存儲(chǔ)過(guò)程參數(shù)，存儲(chǔ)過(guò)程可以聲明一個(gè)或多個(gè)參數(shù)
data_type	參數(shù)的數(shù)據(jù)類(lèi)型，所有數(shù)據(jù)類(lèi)型（包括text，ntext和image）均可以用作存儲(chǔ)過(guò)程的參數(shù)，但cursor數(shù)據(jù)類(lèi)型只能用于OUTPUT參數(shù)
VARYING	可選項(xiàng)，指定作為輸出參數(shù)支持的結(jié)果集（由存儲(chǔ)過(guò)程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化），該關(guān)鍵字僅適用于游標(biāo)參數(shù)
default	可選項(xiàng)，表示為參數(shù)設(shè)置默認(rèn)值
OUTPUT	可選項(xiàng)，表明參數(shù)是返回參數(shù)，可以將參數(shù)值返回給調(diào)用的過(guò)程
n	表示可以定義多個(gè)參數(shù)
AS	指定存儲(chǔ)過(guò)程要執(zhí)行的操作
sql_statement	存儲(chǔ)過(guò)程中的過(guò)程體

--存儲(chǔ)過(guò)程查詢(xún)所有數(shù)據(jù)
--begin...end 類(lèi)似編程語(yǔ)言中的{}
create proc stu1
as
begin
  select * from student;
end
go
 
exec stu1
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲(chǔ)過(guò)程根據(jù)條件用戶(hù)名查詢(xún)用戶(hù)信息
create proc stu2
@sname varchar(50)   --聲明全局變量
as
begin
  select * from student s where s.stuName=@sname;
end
go
 
exec stu2 '王男'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲(chǔ)過(guò)程內(nèi)部設(shè)定用戶(hù)名查詢(xún)用戶(hù)信息
create proc stu3
@sname varchar(50)='王男'
as 
begin
  select * from student s where s.stuName=@sname; 
end
go
 
exec stu3 
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲(chǔ)過(guò)程根據(jù)用戶(hù)名查詢(xún)是否存在這個(gè)用戶(hù)信息
create proc stu4
@sname varchar(50),
@result varchar(8) output --輸出參數(shù)
as
begin
   if (select COUNT(1) from student s where s.stuName=@sname)>0
   --if exists (select COUNT(1) from student s where s.stuName=@sname)
   set 
    @result='存在!'
   else
   set 
    @result='不存在!'
end
go
 
 
declare @result varchar(8)
exec stu4 '王男1',@result output
print @result
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲(chǔ)過(guò)程內(nèi)部設(shè)定局部變量用戶(hù)名來(lái)查詢(xún)用戶(hù)信息
create proc stu5
as
declare @sname varchar(50)  --局部變量聲明
set @sname='楊冪'
begin
  select * from student s where s.stuName=@sname
end
go
 
exec stu5
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲(chǔ)過(guò)程根據(jù)條件用戶(hù)學(xué)號(hào)查詢(xún)用戶(hù)名
create proc stu6
@stuNo varchar(50)
as
declare @sname varchar(50)
set @sname=(select s.stuName from student s where s.stuNo=@stuNo)
select @sname
go
 
exec stu6 '01'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲(chǔ)過(guò)程插入用戶(hù)信息
create proc stu7
@stuNo varchar(50),
@stuName varchar(50),
@stuAge datetime,
@stuSex varchar(5)
as 
begin
  insert into student 
    (stuNo,stuName,stuAge,stuSex)
  values 
    (@stuNo,@stuName,@stuAge,@stuSex) 
end
go
 
exec stu7 '07','王莽','2000-9-9 9:9:9','女'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲(chǔ)過(guò)程根據(jù)用戶(hù)名來(lái)刪除對(duì)應(yīng)的用戶(hù)信息
--@@rowcount返回操作條數(shù)
--return返回信息，終止下面的操作
create proc stu8
@stuName varchar(50)
as
begin
  delete from student where stuName=@stuName
  return @@rowcount
end
go
 
declare @result varchar(50)
exec @result=stu8 '王莽'
select @result as '刪除條數(shù)'
--print @result
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲(chǔ)過(guò)程根據(jù)用戶(hù)學(xué)號(hào)來(lái)查詢(xún)他的平均分
create proc stu9
@stuNo varchar(50),
@avg int output
as 
begin
  set @avg=(select AVG(courseScore) from course where stuNo=@stuNo)
  --等同
  --select @avg=AVG(courseScore) from course where stuNo=@stuNo
end
go
 
declare @avg int
exec stu9 '02',@avg output
print @avg
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲(chǔ)過(guò)程根據(jù)用戶(hù)學(xué)號(hào)來(lái)聯(lián)合查詢(xún)用戶(hù)信息和課程信息
create proc stu10
@stuNo varchar(50)
as
  select c.stuNo,s.stuName,s.stuAge,s.stuSex,c.courseName,c.courseScore from student s join course c on s.stuNo=c.stuNo where s.stuNo=@stuNo
go
 
exec stu10 '02'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲(chǔ)過(guò)程判斷學(xué)號(hào)是否存在，不存在，插入用戶(hù)信息，返回消息；存在，返回信息
create proc stu11
@stuNo varchar(50),
@stuName varchar(50),
@stuAge datetime,
@stuSex varchar(5),
@result varchar(50) output
as
  if exists (select * from student where stuNo=@stuNo)
    begin
      set @result='對(duì)不起，學(xué)號(hào)已存在!'
    end    
  else
    begin
      insert into student 
        (stuNo,stuName,stuAge,stuSex)
      values 
        (@stuNo,@stuName,@stuAge,@stuSex)
      set @result='恭喜你，用戶(hù)信息插入成功!'   
    end
go
 
declare @result varchar(50)
exec stu11 '06','王忠磊','1980-8-8 8:9:0','男',@result output
print @result
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲(chǔ)過(guò)程查詢(xún)當(dāng)前用戶(hù)的平均成績(jī)與總的平均成績(jī)之間的關(guān)系
create proc stu12
@stuNo varchar(50)
as
declare @curAvg decimal(18,2)
declare @totalAvg decimal(18,2)
if exists(select * from course where stuNo=@stuNo)
  begin
    set @totalAvg=(select AVG(courseScore) from course)
    select @curAvg=AVG(courseScore) from course where stuNo=@stuNo
    print ('總的平均分:'+convert(varchar(18),@totalAvg))
    print ('該生的平均分:'+convert(varchar(18),@curAvg))
    if @curAvg>@totalAvg
      print '高于平均水平!'
    else 
      print '低于平均水平!'  
  end
else
  print '該生對(duì)應(yīng)的分?jǐn)?shù)信息不存在,請(qǐng)重新查詢(xún)!'
go
 
exec stu12 '03'
go

sqlserver存儲(chǔ)過(guò)程學(xué)習(xí)(通俗易懂)_英雄主義-CSDN博客_sqlserver 存儲(chǔ)過(guò)程

管理存儲(chǔ)過(guò)程
執(zhí)行存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程創(chuàng)建完成后，可以通過(guò)execute執(zhí)行，簡(jiǎn)寫(xiě)為exec

語(yǔ)法格式

[{exec|execute}]
  {
    [@return_status=]
    {module_name[;number]|@modlue_name_var}
    [[@parameter=]{value
                    |@variable[OUTPUT]
                    |[DEFAULT]
                    }
    ]
    [...n]
    [WITH RECOMPILE]
  }

參數(shù)說(shuō)明

參數(shù)	描述
@return_status	可選的整型變量，存儲(chǔ)模塊的返回狀態(tài)。這個(gè)變量execute語(yǔ)句前，必須在批處理、存儲(chǔ)過(guò)程或函數(shù)中聲明過(guò)
module_name	是要調(diào)用的存儲(chǔ)過(guò)程或標(biāo)量值用戶(hù)定義函數(shù)的完全限定或者不完全限定的名稱(chēng)。模塊名稱(chēng)必須符合標(biāo)識(shí)符規(guī)則。無(wú)論服務(wù)器的排序規(guī)則如何，擴(kuò)展存儲(chǔ)過(guò)程的名稱(chēng)總是區(qū)分大小寫(xiě)
number	是可選整數(shù)，用于對(duì)同名的過(guò)程分組。該參數(shù)不能用于擴(kuò)展存儲(chǔ)過(guò)程
@module_name_var	是局部定義的變量名，代表模塊名稱(chēng)
@parameter	module_name的參數(shù)，與在模塊中定義的相同，參數(shù)名稱(chēng)前必須加上“@”符號(hào)
value	傳遞給模塊或傳遞命令的參數(shù)值，如果參數(shù)名稱(chēng)沒(méi)有指定，參數(shù)值必須以在模塊中定義的順序提供
@variable	是用來(lái)存儲(chǔ)參數(shù)或返回參數(shù)變量
OUTPUT	指定模塊或命令字符串返回一個(gè)參數(shù)，該模塊或命令字符串中的匹配參數(shù)也必須使用關(guān)鍵字OUTPUT創(chuàng)建。使用游標(biāo)變量作為參數(shù)時(shí)使用該關(guān)鍵字
DEFAULT	根據(jù)模塊的定義，提供參數(shù)的默認(rèn)值。當(dāng)模塊需要的參數(shù)值沒(méi)有定義默認(rèn)值并且缺少參數(shù)或指定了DEFAULT關(guān)鍵字，會(huì)出現(xiàn)錯(cuò)誤
WITH RECOMPILE	指定模塊后，強(qiáng)制編譯、使用和放棄新計(jì)劃。如果該模塊存在現(xiàn)有查詢(xún)計(jì)劃，則該計(jì)劃將保留在緩存中

查看存儲(chǔ)過(guò)程
使用sys.sql_modules查看存儲(chǔ)過(guò)程的定義

select * from sys.sql_modules

使用OBJECT_DEFINITION查看存儲(chǔ)過(guò)程的定義

-- object_id 要查看的存儲(chǔ)過(guò)程id
select OBJECT_DEFINITION(object_id)

使用sp_helptext查看存儲(chǔ)過(guò)程的定義

sp_helptext 'proc_student'

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶(hù)發(fā)布，不代表本站觀(guān)點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買(mǎi)等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自： Coder編程 > 《待分類(lèi)》

舉報(bào)/認(rèn)領(lǐng)