面試準備之SQL 6 —— 存儲過程

2019-11-03 08:34:21

字體：大中小

來源：轉載

供稿：網友

1.什么是存儲過程?

存儲過程是一次編譯可多次運行（存儲過程存放在服務器中），預編譯好的集合，運行速度快。

2.常用系統存儲過程

代碼
-- Purpose: 常用系統存儲過程使用

EXEC sp_databases --列出當前系統中的數據庫

EXEC sp_renamedb 'test','test1'--改變數據庫名稱(單用戶訪問)

USE stuDB
GO

EXEC sp_tables --當前數據庫中查詢的對象的列表

EXEC sp_columns stuInfo --返回某個表列的信息

EXEC sp_help stuInfo --查看表stuInfo的信息

EXEC sp_helpconstraint stuInfo --查看表stuInfo的約束

EXEC sp_helpindex stuMarks --查看表stuMarks的索引

EXEC sp_helptext 'view_stuInfo_stuMarks' --查看視圖的語句文本

EXEC sp_stored_PRocedures --返回當前數據庫中的存儲過程列表

use master
go
exec xp_cmdshell 'mkdir D:/bank',no_output--創建文件夾

3.自定義存儲過程

（1.）不帶參數的存儲過程

代碼
use studb
go
if exists(select * from sysobjects where name='proc_stu')
drop proc proc_stu
go

create procedure proc_stu
AS
    DECLARE @writtenAvg float,@labAvg float --筆試和機試平均分變量
    SELECT @writtenAvg=AVG(writtenExam), @labAvg=AVG(labExam) FROM stuMarks
    print '筆試平均分：'+convert(varchar(5),@writtenAvg)
    print '機試平均分：'+convert(varchar(5),@labAvg)
    IF (@writtenAvg>70 AND @labAvg>70)
       print '本班考試成績：優秀'
    ELSE
       print '本班考試成績：較差'
    print '--------------------------------------------------'
    print '           參加本次考試沒有通過的學員：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<60 OR labExam<60
GO

exec proc_stu --執行存儲過程
（2.）帶輸入參數的存儲過程

代碼
USE stuDB
GO
/*---檢測是否存在：存儲過程存放在系統表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
DROP PROCEDURE proc_stu
GO
/*---創建存儲過程----*/
CREATE PROCEDURE proc_stu
@writtenPass int,
@labPass int-- 可以添加默認值這樣執行可以是這樣的 exec proc_stu 不用指定參數了
AS
    print '筆試及格線：'+convert(varchar(5),@writtenPass)
    print '機試及格線：'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           參加本次考試沒有通過的學員：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<@writtenPass OR labExam<@labPass
GO

exec proc_stu 60,55
exec proc_stu 60,@labPass=55
exec proc_stu @writtenPass=60,@labPass=55
exec proc_stu @writtenPass=60,55--這一行會報錯：必須傳遞參數 2，并以 '@name = value' 的形式傳遞后續的參數。
-- 一旦使用了 '@name = value' 形式之后，所有后續的參數就必須以 '@name = value' 的形式傳遞

（3.）帶輸出參數的存儲過程

代碼
USE stuDB
GO
/*---檢測是否存在：存儲過程存放在系統表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
DROP PROCEDURE proc_stu
GO
/*---創建存儲過程----*/
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT, --OUTPUT關鍵字，否則視為輸入參數
@writtenPass int=60, --默認參數放后
@labPass int=60       --默認參數放后
AS
    print '筆試及格線：'+convert(varchar(5),@writtenPass)
       + '   機試及格線：'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           參加本次考試沒有通過的學員：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<@writtenPass OR labExam<@labPass
    /*--統計并返回沒有通過考試的學員人數--*/
    SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
      WHERE writtenExam<@writtenPass OR labExam<@labPass

GO

/*---調用存儲過程----*/
DECLARE @sum int
EXEC proc_stu @sum OUTPUT ,64 --機試及格線采用默認值：筆試及格線64分，機試及格線60分。
print '--------------------------------------------------'
IF @sum>=3
print '未通過人數：'+convert(varchar(5),@sum)+ '人,超過60%,及格分數線還應下調'
ELSE
print '未通過人數：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分數線適中'
GO