SQL SERVER 存盤程序

一、存盤程序

存盤程序是預編譯的SQL陳述句的集合，這些陳述句存盤在一個名稱下并作為一個單元處理，存盤程序代替了傳統的逐條執行sql陳述句的方式，一個存盤程序中可包含查詢、插入、更新、洗掉等操作的一系列sql陳述句，當這個存盤程序被呼叫執行時，這些操作也會同時執行

存盤程序與其他編程語言的程序類似，它可以接受輸入引數，并以輸出引數的格式向呼叫程序或批處理回傳多個值；包含用于在資料庫中執行操作（包括呼叫其他程序的）的編程陳述句；向呼叫程序或批處理回傳狀態值，以指明成功或失敗（以及失敗的原因），

1. 創建存盤程序

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

--存盤程序查詢所有資料
--begin...end 類似編程語言中的{}
create proc stu1
as
begin
  select * from student;
end
go
 
exec stu1
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存盤程序根據條件用戶名查詢用戶資訊
create proc stu2
@sname varchar(50)   --宣告全域變數
as
begin
  select * from student s where s.stuName=@sname;
end
go
 
exec stu2 '王男'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存盤程序內部設定用戶名查詢用戶資訊
create proc stu3
@sname varchar(50)='王男'
as 
begin
  select * from student s where s.stuName=@sname; 
end
go
 
exec stu3 
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存盤程序根據用戶名查詢是否存在這個用戶資訊
create proc stu4
@sname varchar(50),
@result varchar(8) output --輸出引數
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
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存盤程序內部設定區域變數用戶名來查詢用戶資訊
create proc stu5
as
declare @sname varchar(50)  --區域變數宣告
set @sname='楊冪'
begin
  select * from student s where s.stuName=@sname
end
go
 
exec stu5
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存盤程序根據條件用戶學號查詢用戶名
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
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存盤程序插入用戶資訊
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
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存盤程序根據用戶名來洗掉對應的用戶資訊
--@@rowcount回傳操作條數
--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 '洗掉條數'
--print @result
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存盤程序根據用戶學號來查詢他的平均分
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
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存盤程序根據用戶學號來聯合查詢用戶資訊和課程資訊
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
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存盤程序判斷學號是否存在，不存在，插入用戶資訊，回傳訊息；存在，回傳資訊
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='對不起，學號已存在!'
    end    
  else
    begin
      insert into student 
        (stuNo,stuName,stuAge,stuSex)
      values 
        (@stuNo,@stuName,@stuAge,@stuSex)
      set @result='恭喜你，用戶資訊插入成功!'   
    end
go
 
declare @result varchar(50)
exec stu11 '06','王忠磊','1980-8-8 8:9:0','男',@result output
print @result
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存盤程序查詢當前用戶的平均成績與總的平均成績之間的關系
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 '該生對應的分數資訊不存在,請重新查詢!'
go
 
exec stu12 '03'
go

sqlserver存盤程序學習(通俗易懂)_英雄主義-CSDN博客_sqlserver 存盤程序

1. 管理存盤程序

2. 執行存盤程序

存盤程序創建完成后，可以通過execute執行，簡寫為exec

• 語法格式

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

• 引數說明

1. 查看存盤程序

2. 使用sys.sql_modules查看存盤程序的定義

select * from sys.sql_modules

1. 使用OBJECT_DEFINITION查看存盤程序的定義

-- object_id 要查看的存盤程序id
select OBJECT_DEFINITION(object_id) 

1. 使用sp_helptext查看存盤程序的定義

sp_helptext 'proc_student'

標籤：SQL Server

上一篇：Redis-第十章節-鏈表

下一篇：SQL SERVER 存盤程序