sql server 存储过程的学习

存储过程学习笔记

存储过程就是一条或者多条sql语句的集合,为了实现特定任务，而将一些需要多次调用的固定操作语句编写成程序段，这些程序段存储在服务器上，有数据库服务器通过程序来调用。
T_SQL:存储过程是值保存的T_SQL语句集合，可以接受和返回用户提供的参数，存储过程也可能从数据库向客户端应用程序返回数据。
存储过程的优点就不多说了开始学习吧！

　　创建表以及测试数据

use db_test
create table student(userid varchar(10),username varchar(20),identityd varchar(20),classId varchar(20))

insert into student values('20180312','小红','5131002121121212','6024201803'),
('20180313','小明','121354564654','6024201803'),
('20180314','小孙','6545487','6024201803'),
('20180315','小何','78921548','6024201803')
create table xfClass(classid varchar(10),classname varchar(20),rem varchar(20))
insert into xfClass values('6024201803','三班','学费比较高'),
('6024201802','二班','学费有点高')
select * from xfClass
create table test1(id varchar(10),name varchar(20),rem varchar(20)) --以前的测试表部分过程在这个表中完成

创建无参存储过程

--查看存储过程p_InsT1是否存在
if (exists (select * from sys.objects where name = 'p_InsT1'))
    drop proc p_InsT1
go　　

--上面这句判断比较占用空间，所以后面创建存储过程我就不写了这句判断了
--创建一个存储过程p_InsT1
create proc p_seluser
as
select * from student a left join xfclass b on a.classId=b.classid ;
go

--执行sp_name存储过程

exec p_seluser

创建带参数的存储过程

create proc p_seluser_name(
@sName  varchar(20)
)
as
select * from student a left join xfclass b on a.classId=b.classid where username=@sName; --这段sql 应该可以优化，以后进行详细了解后在分享
go
--执行sp_name存储过程
exec p_seluser_name '小红'

 

存储过程的修改

方法1：直接修改，直接将新的过程将以前的过程进行覆盖。

alert proc p_insT1

as

select * from test1

方法2：找到存储过程文件进行修改

在数据库下可编程性中存储过程里找到需要修改的存储过程

修改之后运行一下该过程，修改完成。

　　上面用的都是常用存储过程，下面了是不常用存储过程

不缓存存储过程

create proc p_temp

with recompile --设置为不缓存

as

select * from student;

加密存储过程

create proc proc_temp_encryption

with encryption

as

select * from student;

go

 

exec proc_temp_encryption;

exec sp_helptext p_set7; --未加密可直接查看源语句

exec sp_helptext proc_temp_encryption　　--加密后不可以直接查看源语句

　　带通配符参数存储过程

create proc p_findStNa(@id varchar(20)='%p%')

as

delete table test1 where id like @id

exec proc_p_findStNa　//如果不加参数则直接使用定义的参数‘%p%’

exec proc_p_findStNa 'pp'; //如果不加参数则直接使用定义的参数

　　带输出参数存储过程

查询出来的结果不以表格进行输出declare @id varchar(20),@name varchar(20),@rem varchar(20) 　
set @id='2'
select @id=id,@name =name,@rem=rem from test1 where id=@id
--select @id, @name,@rem;
print @id+''+ @name+''+@rem;

通过函数对参数进行赋值

create proc p_getName(

@id varchar(20),--默认输入参数

@name varchar(20) out, --输出参数

@age varchar(20) output --输入输出参数

)

as

select @name = name ,@age =age from test1 where id=@id and sex=@age;

 

--运行

declare @id varchar(20),

@name varchar(20),

@temp varchar(20);

set @id='7';

set @temp='15';

--运行存储过程

exec p_getName @id,@name out,@tem output;

--直接输出存储过程中的返回值

select @name,@temp;

　　上面的两段语句存在很大问题，他们的输出只有最后一条数据，因为在给变量赋值的时候只赋一次，当查询结果出来后数据指针往往指向最后一条，所以只输出最后一条。如要全部输出则　　需要用到游标

9、带游标参数存储过程

if (object_id('proc_cursor', 'P') is not null)

drop proc proc_cursor

go

create proc proc_cursor　－－声明一个存储过程

@cur cursor varying output --声明游标变量forward_only变量为@cur

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;--删除游标

游标参考文档https://www.cnblogs.com/myboke/p/3738912.html

还有一些其他的存储过程，只不过大多数都是在上面这些基础上进行编写，所以就不多写了。

https://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html#undefined