定义
用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表示错误发生的位置。你可以根据自己的需要修改和扩展这个示例。