SQL Server 存储过程基础样例

存储过程

数据库中过程的概念如同编程语言中的函数或方法，把一段段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;