sqlserver存储过程案例教程

定义

用sql写业务逻辑，系统变了，只要数据库没变，功能还是可以生效

常用存储过程（系统自带）

--查看数据库
exec sp_databases;
-- 查看表
exec sp_tables;

-- 查看列
exec sp_columns studentInfo;
exec sp_helpIndex student; --查看索引
exec sp_helpConstraint student;  --约束
exec sp_stored_procedures; -- 查看存储过程

exec sp_helptext 'sp_stored_procedures';--查看存储过程创建定义语句
exec sp_rename student,stuInfo;--修改表索引，列名称
exec sp_renamedb myTempDb,myDB;--更改数据库名
exec sp_defaultdb 'master','mydb'; --更改登录名的默认数据库
exec sp_helpdb;  --数据库帮助，
exec sp_helpdb master;

大部分业务功能的实现都需要自定义存储过程，以下是一些例子

无参数存储过程

select * from sys.objects where type='p';
-- 判断是否存在
if exists (select * from sys.databases where name='mydata1') print 'yes' else print 'no';
use mydata1;
go
-- 创建不带参数的存储过程
if exists (select * from sys.objects where name='proc_get_student')
drop proc proc_get_student
go
create proc proc_get_student
as 
select * from studentInfo;

--调用、执行存储过程
exec proc_get_student;

-- 查看是否有存储过程
exec sp_stored_procedures;

--修改存储过程
alter proc proc_get_student
as 
select * from studentInfo;

-- 带参存储过程，各个参数类型表示如下
--AF = 聚合函数 (CLR)
		--C = CHECK 约束
		--D = DEFAULT（约束或独立）
		--F = FOREIGN KEY 约束
		--FN = SQL 标量函数
		--FS = 程序集 (CLR) 标量函数
		--FT = 程序集 (CLR) 表值函数
		--IF = SQL 内联表值函数
		--IT = 内部表
		--P = SQL 存储过程
		--PC = 程序集 (CLR) 存储过程
		--PG = 计划指南
		--PK = PRIMARY KEY 约束
		--R = 规则（旧式，独立）
		--RF = 复制筛选过程
		--S = 系统基表
		--SN = 同义词
		--SQ = 服务队列
		--TA = 程序集 (CLR) DML 触发器
		--TF = SQL 表值函数
		--TR = SQL DML 触发器
		--U = 表（用户定义类型）
		--UQ = UNIQUE 约束
		--V = 视图
		--X = 扩展存储过程

-- 创建带参存储过程
if(object_id('proc_find_stu','p')is not null)
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int,@endId int)
	as 
exec sp_tables;
select * from stuInfo where stuId between @startId and @endId
go
exec proc_find_stu 0,111;

-- 带通配符参数存储过程
if object_id('proc_findstudentbyname','p') is not null
drop proc proc_findstudentbyname
go
create proc proc_findstudentbyname(@name varchar(20) ='%j',
@nextname varchar(20) ='%')
as 
select * from stuInfo where stuInfo.stuName like @name and stuName like 
@nextname;
go
-- 传空参啥输出结果都没
exec proc_findstudentbyname;
exec proc_findstudentbyname '%1%','1%';

select * from stuInfo;

带参数存储过程

if object_id('proc_getstudentrecord','p') is not null
drop proc proc_getstudentrecord
go
create proc proc_getstudentrecord(
@id int,
@name varchar(20) out, --输出参数
@age varchar(20) output --输入输出参数

)
as 
select @name=stuName,@age=age from stuInfo where stuId=@id and age=@age;

go

--
declare @id int,
@name varchar(20),
@temp varchar(20)
set @id=7;
set @temp=1;

exec proc_getstudentrecord @id,@name out,@temp output;
select @name,@temp;
print @name+'#'+@temp

不缓存存储过程

if object_id('proc_temp','p') is not null
drop proc proc_temp
go
create proc proc_temp 
with recompile
as 
select * from stuInfo
go

exec proc_temp;

加密存储过程

if object_id('proc_temp encription','p') is not null
drop proc proc_temp_encription
go
create proc proc_temp_encription 
with encryption
as
select * from stuInfo

exec proc_temp_encription;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encription';

分页存储过程

---存储过程、row_number完成分页
if (object_id('pro_page', 'P') is not null)
    drop proc proc_cursor
go
create proc pro_page
    @startIndex int,
    @endIndex int
as
    select count(*) from student
;    
    select * from (
        select row_number() over(order by id) as rowId, * from student 
    ) temp
    where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分页存储过程
if (object_id('pro_page', 'P') is not null)
    drop proc pro_stu
go
create procedure pro_stu(
    @pageIndex int,
    @pageSize int
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize +1
    set @endRow = @startRow + @pageSize -1
    select * from (
        select *, row_number() over (order by id asc) as number from student 
    ) t
    where t.number between @startRow and @endRow;

exec pro_stu 2, 2;

游标存储过程

if (object_id('proc_cursor', 'P') is not null)
    drop proc proc_cursor
go
create proc proc_cursor
    @cur cursor varying output
as
    set @cur = cursor forward_only static for
    select id, name, age from student;
    open @cur;
go
--调用
declare @exec_cur cursor;
declare @id int,
        @name varchar(20),
        @age int;
exec proc_cursor @cur = @exec_cur output;--调用存储过程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
    fetch next from @exec_cur into @id, @name, @age;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--删除游标

用户自定义错误信息

raiserror('is errror',16,1);
select * from sys.messages;

--使用sysmesssages中定义的信息
-- 16错误级别 19 到 25 之间的严重级别只能由 sysadmin 固定服务器角色成员使用。若要使用 19 到 25 之间的严重级别，必须选择 WITH LOG 选项
raiserror(33003,16,1);
raiserror(33006,16,1);

--msg_id是错误消息的ID，msg_str是自定义错误消息的文本。severity是错误的严重程度，取值范围为125，其中1表示最轻微，25表示最严重。state是错误状态，取值范围为0255，用于指示错误发生的位置。argument是错误消息中包含的参数，可以是整数、字符或日期等类型。option是其他选项，如WITH LOG、WITH NOWAIT等，用于指定如何处理错误消息。
--在代码中，RAISERROR(33003,16,1)表示生成一个自定义错误消息，该消息的ID为33003，严重程度为16，状态为1。由于msg_str参数未指定，所以错误消息的文本将从SQL Server的错误消息目录中获取。该错误消息的严重程度为16，表示一个一般错误，可以被捕获和处理，但仍需要注意。状态为1表示错误发生的位置。你可以根据自己的需要修改和扩展这个示例。