存储过程
数据库中过程的概念如同编程语言中的函数或方法,把一段段SQL语句组成语句块并命名保存在数据库中,这样有诸多利处:
1.使用复杂的sql操作时候不需要再次编写sql直接调用过程即可
2.当从网络上操作数据库时传输调用存储过程指令而非sql语句,避免了数据表信息泄露。
--create proc proc_test
--[参数声明:声明输入输出参数]
--as
--[过程体:sql语句]
--go
--drop procedure proc_test1
--定义存储过程
create proc proc_sum
@a int,
@b int ,
@c int output
as
set @c=@a+@b;
go
--调用存储过程
begin
declare @i int;
declare @j int;
declare @k int;
set @i = 3;
set @j=4;
exec proc_sum @i,@j,@k out;
print(@k);
end;
--无输入无参数
--创建一个存储过程,查询学生表中所有信息,并打印学生总数,平均年龄
create procedure proc_test1
as
select * from tb_stus;
declare @num int;
select @num=count(*) from tb_stus;
print('num:'+convert(varchar,@num));
declare @age int;
select @age=avg(stu_age) from tb_stus;
print('avg age:'+convert(varchar,@age));
go
--执行
exec proc_test1;
--有输入参数无输出参数
create procedure proc_test2
@min int =0, --可以这样设置默认值
@max int =100
as
select * from tb_stus where stu_age between @min and @max;
go
exec proc_test2 10,20;
begin
declare @i int;
declare @j int;
set @i = 17;
set @j = 28;
exec proc_test2 @i,@j;
end;
--无输入参数有输出参数
--创建以个存储过程,统计男生和女生的数量,以输出参数返回
create procedure proc_test3
@num1 int out,
@num2 int output
as
select @num1=count(*) from tb_stus where stu_sex='男';
select @num2=count(*) from tb_stus where stu_sex='女';
go
--调用带有输出参数的存储过程,需要定义变量接值
begin
declare @m1 int;
declare @m2 int;
exec proc_test3 @m1 out ,@m2 out;
print ('男生人数为:'+convert(varchar,@m1));
print ('女生人数为:'+convert(varchar,@m2));
end;
--有输入参数和输出参数的存储过程
--创建一个存储过程,输入一个学生的学号和课程编号,查询学生的成绩
create procedure proc_test4
@snum char(5),
@cnum int,
@score int=0 out,
@snam varchar(20) out,
@cname varchar(50) out
as
select @snam=stu_name from tb_stus where stu_num=@snum;
select @cname=course_name from tb_courses where course_num =@cnum;
select @score = score from tb_grade where snum=@snum and cnum=@cnum;
go
begin
declare @score int;
declare @sn varchar(20);
declare @cn varchar(50);
exec proc_test4 '10010',100, @score out,@sn out,@cn out;
print ('sn='+@sn);
print ('cn='+@cn);
print ('score='+convert(varchar,@score));
end;
--存储过程练习
--1创建一个存储过程,根据输入的学号查询这个学生是否参加考试,如果参加考试则打印出
--学生姓名、考试科目名称、对应成绩,如果没有参加考试则提示此学生未参加考试
create procedure proc_test5
@snum char(5)
as
declare @num int;
declare @score int;
declare @sn varchar(20);
declare @cn varchar(50);
select @num=count(*) from tb_grade where snum=@snum;
if(@num > 0)
begin
--此学生参加了考试
print('list:')
select s.stu_name,c.course_name,g.score from tb_stus s,tb_courses c,tb_grade g
where s.stu_num=g.snum and g.cnum=c.course_num and g.snum=@snum;
end else
begin
print('no info')
end;
go
exec proc_test5 '10010';
--练习二 创建一个人存储过程
--输入参数:每页显示的条数pageSize(m) 页码pageNum(n)
--输出参数:总页数
--查询出对应页码的结果集
--分页:每页显示三条数据
--第一页 select top(3) * from tb_stus;
--第二页 select top(3) * from tb_stus where stu_num not in (select top(3*1) stu_num from tb_stus);
--第三页 select top(3) * from tb_stus where stu_num not in (select top(3*2) stu_num from tb_stus);
--第n页 select top(m) * from tb_stus where stu_num not in (select top(m*(n-1)) stu_num from tb_stus);
create procedure proc_stu_splitpage
@pageSize int ,
@pageNum int,
@pageCount int out
as
declare @recordCount int;
--查询到总记录数
select @recordCount = count(*) from tb_stus;
--计算总页数
set @pageCount = @recordCount/@pageSize;
if(@recordCount%@pageSize=0)
begin
set @pageCount = @recordCount/@pageSize;
end else
begin
set @pageCount = @recordCount/@pageSize+1;
end
select top(@pageSize) * from tb_stus where stu_num not in (select top(@pageSize*(@pageNum-1)) stu_num from tb_stus);
go
begin
declare @pc int;
exec proc_stu_splitpage 3 ,2,@pc out;
print('pageCount='+convert(varchar,@pc));
end;
常用系统存储过程
SQL Server系统自带一些很有用的存储过程,我们可以方便的调用
--列出当前数据库服务器上所有的数据库
execute sp_databases;
--列出数据库信息
execute sp_helpdb; --所有数据库
execute sp_helpdb 'db_test';--指定数据库
--更改数据库名称
--第一个参数:原数据库名称,第二个参数:新名称
execute sp_renamedb 'db_test','db_ahah';
--列出当前数据库中所有的表和视图信息
exec sp_tables;
--列出表的字段信息
exec sp_columns 'tb_stus';
--列出当前数据库中所有数据库对象信息
exec sp_help;
--列出指定数据表的详细信息
exec sp_help 'tb_stus';
--列出指定表的约束信息
exec sp_helpconstraint 'tb_stus';
--查看指定表的索引信息
exec sp_helpindex 'tb_stus';
--列出当前数据库下所有的存储过程
exec sp_stored_procedures;
--修改指定用账号的密码
exec sp_password 'oldpwd','newpwd','username';
exec xp_cmdshell 'cmd';
触发器
触发器是一种特殊的存储过程
对数据表进行insert/delete/update操作时可以自动调用触发器
create trigger tri_test on tb_stus for insert
as
print('im a trigger');
go
alter trigger tri_test on tb_stus for insert
as
print(convert(varchar,Getdate(),120)+'im a trigger');
go
create trigger tri_test2 on tb_stus for delete
as
select * from tb_stus;
go
insert into tb_stus values ('70019','laowang','男',33,'18071118229');
delete from tb_stus where stu_num ='70018';
分页存储过程
create procedure page_split
@tableName varchar(50), --表名
@pk varchar(30), --主键名
@condition varchar(100) = '', --筛选条件
@orderStr varchar(100)='', --排序方式
@columns varchar(100)='*', --查询的列名(默认全部列)
@pageSize int, --页面条目数
@pageNum int, --页码
@pageCount int out --总页面数
as
declare @sc int; --总条目数
declare @sql1 nvarchar(200);
--查询总记录数
set @sql1='select @sc=count(*) from '+@tableName;
--sp_executesql:执行指定的sql字符串语句,可以指定参数
exec sp_executesql @sql1,N'@sc int output',@sc output;
--计算总页数
if(@sc%@pageSize=0)begin
set @pageCount=@sc/@pageSize;
end else begin
set @pageCount=@sc/@pageSize+1;
end;
--进行分页
declare @sql2 nvarchar(100);
--拼接获取前n-1页的子集的语句
set @sql2 ='select top('+convert(varchar,@pageSize*(@pageNum-1))+') '+@pk+' from '+@tableName;
if(@condition!='')begin
set @sql2=@sql2+' where '+@condition;
end;
if(@orderStr!='')begin
set @sql2=@sql2+' order by '+@orderStr;
end;
--拼接查询语句
declare @sql3 nvarchar(200);
set @sql3='select top('+convert(varchar,@pageSize)+') '+@columns+' from '+@tablename+' where '+@pk+' not in('+@sql2+')';
if(@condition!='')begin
set @sql3=@sql3+' and '+@condition;
end;
if(@orderStr!='')begin
set @sql3=@sql3+' order by '+@orderStr;
end;
--打印sql
print(@sql3);
--执行sql
exec(@sql3);
--select top(@pageSize) * from tb_stus where stu_num not in
--(select top(@pageSize*(@pageNum-1))stu_num from tb_stus where @condition) and @condition order by @orderStr;
print('recordNum='+str(@sc));
print('pageNum='+str(@pageCount));
go
--调用测试
begin
use db_test;
declare @pc int;
--查找以员工编号为主键的员工表中 年龄大于21岁的员工以年龄升序
--每页2个条目的第4页
execute page_split 'emp','eno','age>21','age asc','*',2,4,@pc out;
print('总页数='+convert(varchar,@pc));
end;