Sqlserver存储过程

系列文章目录

---

一、准备工作

-- 创建 Students 表
CREATE TABLE Students (StudentID INT PRIMARY KEY IDENTITY,Name NVARCHAR(50),Gender NCHAR(10),BirthDate DATE,Major NVARCHAR(50)
);-- 创建 Scores 表
CREATE TABLE ScoresPlus (ScoreID INT PRIMARY KEY IDENTITY,StudentID INT,Course NVARCHAR(50),Score INT,FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);
-- 插入学生数据
INSERT INTO Students (Name, Gender, BirthDate, Major)
VALUES 
('张三', '男', '2000-01-01', '计算机科学'),
('李四', '女', '2001-05-15', '软件工程'),
('王五', '男', '1999-08-20', '人工智能');-- 插入成绩数据
INSERT INTO ScoresPlus (StudentID, Course, Score)
VALUES 
(1, '数学', 85),
(1, '英语', 90),
(2, '数学', 78),
(2, '英语', 88),
(3, '数学', 92),
(3, '英语', 89);

二、编写存储过程

1、根据学生ID查询学生信息

CREATE PROCEDURE GetStudentById@StudentID INT
AS
BEGINSELECT * FROM Students WHERE StudentID = @StudentID;
END;使用：
EXEC GetStudentById @StudentID = 1;

2、根据学生ID查询所有成绩

-- 查询学生成绩的存储过程
CREATE PROCEDURE GetScoresByStudentId@StudentID INT
AS
BEGINSELECT s.Course, s.ScoreFROM Scores sWHERE s.StudentID = @StudentID;
END;使用：
EXEC GetScoresByStudentId @StudentID = 1;

3、插入学生的存储过程

CREATE PROCEDURE InsertStudent@Name NVARCHAR(50),@Gender NCHAR(10),@BirthDate DATE,@Major NVARCHAR(50)
AS
BEGININSERT INTO Students (Name, Gender, BirthDate, Major)VALUES (@Name, @Gender, @BirthDate, @Major);
END;使用：
EXEC InsertStudent @Name = '赵六',@Gender = '男',@BirthDate = '2002-03-10',@Major = '网络工程';

4、删除学生信息

-- 删除学生信息的存储过程
CREATE PROCEDURE DeleteStudent@StudentID INT
AS
BEGINIF EXISTS (SELECT 1 FROM Students WHERE StudentID = @StudentID)BEGINDELETE FROM Students WHERE StudentID = @StudentID;PRINT '学生已删除。';ENDELSEBEGINPRINT '学生不存在，无法删除。';END
END;使用：
EXEC DeleteStudent @StudentID = 1;

二、每周定期refresh index & sp

目标：
获取所有表名（TableName）
获取每个表的所有索引（IndexName）
获取所有存储过程名称（SP Name）
对这些对象进行维护操作：
对表的索引进行 REBUILD 或 REORGANIZE
对存储过程进行 sp_recompile

-- 获取所有表名和对应的索引名
SELECT t.name AS TableName,i.name AS IndexName
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
WHERE i.type > 0 -- 排除堆表
ORDER BY t.name, i.name;

-- 获取所有存储过程名称
SELECT name AS SPName
FROM sys.procedures
WHERE type = 'P' -- 存储过程
ORDER BY name;

CREATE PROCEDURE RefreshIndexesAndSPs
AS
BEGINSET NOCOUNT ON;DECLARE @TableName NVARCHAR(128)DECLARE @IndexName NVARCHAR(128)DECLARE @SQL NVARCHAR(MAX)-- 获取所有表和索引DECLARE cur CURSOR FORSELECT t.name AS TableName,i.name AS IndexNameFROM sys.tables tINNER JOIN sys.indexes i ON t.object_id = i.object_idWHERE i.type > 0 -- 排除堆表OPEN curFETCH NEXT FROM cur INTO @TableName, @IndexNameWHILE @@FETCH_STATUS = 0BEGIN-- 执行 REBUILD 索引SET @SQL = N'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD;'EXEC sp_executesql @SQL-- 可选：根据碎片率判断是否使用 REORGANIZE-- 这里为了简化，直接使用 REBUILDFETCH NEXT FROM cur INTO @TableName, @IndexNameENDCLOSE curDEALLOCATE cur-- 重新编译所有存储过程DECLARE @SPName NVARCHAR(128)DECLARE cur_sp CURSOR FORSELECT name FROM sys.procedures WHERE type = 'P'OPEN cur_spFETCH NEXT FROM cur_sp INTO @SPNameWHILE @@FETCH_STATUS = 0BEGINSET @SQL = N'EXEC sp_recompile ''' + @SPName + ''''EXEC sp_executesql @SQLFETCH NEXT FROM cur_sp INTO @SPNameENDCLOSE cur_spDEALLOCATE cur_spPRINT '索引刷新和存储过程重新编译完成。'
END使用：
EXEC RefreshIndexesAndSPs;

注意：
REBUILD 会锁表，建议在低峰期执行
事务控制	建议加 BEGIN TRANSACTION 和 COMMIT，避免失败
权限要求	需要 ALTER 权限对表和存储过程
可选优化	根据 sys.dm_db_index_physical_stats 的 avg_fragmentation_percent 决定是 REBUILD 还是 REORGANIZE