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_insT1asselect * from test1方法2:找到存储过程文件进行修改
在数据库下可编程性中存储过程里找到需要修改的存储过程
修改之后运行一下该过程,修改完成。
不缓存存储过程
create proc p_tempwith recompile --设置为不缓存asselect * from student;
加密存储过程
create proc proc_temp_encryptionwith encryptionasselect * from student;goexec proc_temp_encryption;exec sp_helptext p_set7; --未加密可直接查看源语句exec sp_helptext proc_temp_encryption --加密后不可以直接查看源语句
带通配符参数存储过程
create proc p_findStNa(@id varchar(20)='%p%')asdelete table test1 where id like @idexec 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 --输入输出参数)asselect @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_cursorgocreate proc proc_cursor --声明一个存储过程@cur cursor varying output --声明游标变量forward_only变量为@curasset @cur = cursor forward_only static forselect 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) --存放每一条beginfetch next from @exec_cur into @id, @name, @age;print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);endclose @exec_cur;deallocate @exec_cur;--删除游标游标参考文档https://www.cnblogs.com/myboke/p/3738912.html
还有一些其他的存储过程,只不过大多数都是在上面这些基础上进行编写,所以就不多写了。