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;

存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。 存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息,所以在一定程度上也可以减少网络流量、简单网络负担。 1、 存储过程的优点 A、 存储过程允许标准组件式编程 存储过程创建后可以在程序中被多次调用执行,而不必重新编写该存储过程SQL语句。而且数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。 B、 存储过程能够实现较快的执行速度 如果某一操作包含大量的T-SQL语句代码,分别被多次执行,那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化,所以速度就要慢一些。 C、 存储过程减轻网络流量 对于同一个针对数据库对象的操作,如果这一操作所涉及到的T-SQL语句被组织成一存储过程,那么当在客户机上调用该存储过程时,网络中传递的只是该调用语句,否则将会是多条SQL语句。从而减轻了网络流量,降低了网络负载。 D、 存储过程可被作为一种安全机制来充分利用 系统管理员可以对执行的某一个存储过程进行权限限制,从而能够实现对某些数据访问的限制,避免非授权用户对数据的访问,保证数据的安全。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值