sql存储过程实例详解,sql存储过程实例详解 学生表
sql 存储过程是怎么实现的 简单的例子和解释!
存储过程就是一组保存在数据库中的sql语句,在需要的时候可以调用
最简单的,比如
create procedure test as
delete from t_1; ---删除t_1表的所有记录
在sql server查询分析器执行时:
exec test; --执行过程test,删除了表t_1的所有记录
当然,没有人这样使用存储过程,存储过程可以接受参数,处理大量sql语句,并返回结果。
当在编写软件的过程中,碰到需要进行复杂的数据库操作时,可能需要大量的sql语句,这时候可以先在数据库中创建存储过程,将sql语句都写在存储过程里,可以视情况加入参数,也可以返回处理结果。编写软件时,在适当的地方引用并执行这个存储过程就好了,至于怎么引用,不同的软件开发语言有不同的语法。
存储过程是预编译的,这样可以提高执行效率,对于软件代码的维护也有好处
SQL存储过程实例
楼上的是ORACLE的存储过程语法,SQLSERVER的语法略有不同。
举个例子:
员工表(tb_employee)字段如下 emp_id 员工编号, emp_nm 员工姓名, dept_id 所属部门编号, salary 工资收入
编写存储过程查询某部门员工的工资总和。
那么分析可以知道,这个存储过程的输入参数就是部门编号
create procedure sp_sum_salary
@dept_id varchar(20)
as
--------直接以查询语句输出
select sum(salary)
from tb_employee
where dept_id = @dept_id
SQL 中存储过程怎么使用?
一、简单的储存过程:
1、创建一个存储过程
create procedure GetUsers()
begin
select * from user;
end;12345
2、调用存储过程
call GetUsers();12
3、删除存储过程
drop procedure if exists GetUsers;
二、带参数的存储过程
1、MySql 支持 IN (传递给存储过程) , OUT (从存储过程传出) 和 INOUT (对存储过程传入和传出) 类型的参数 , 存储过程的代码位于 BEGIN 和 END 语句内 , 它们是一系列 SQL 语句 , 用来检索值 , 然后保存到相应的变量 (通过指定INTO关键字) ;
2、下面的存储过程接受三个参数 , 分别用于获取用户表的最小 , 平均 , 最大分数 , 每个参数必须具有指定的类型 , 这里使用十进制值(decimal(8,2)) , 关键字 OUT 指出相应的参数用来从存储过程传出
create procedure GetScores(
out minScore decimal(8,2),
out avgScore decimal(8,2),
out maxScore decimal(8,2)
)
begin
select min(score) into minScore from user;
select avg(score) into avgScore from user;
select max(score) into maxScore from user;
end;1234567891011
3、调用此存储过程 , 必须指定3个变量名(所有 MySql 变量都必须以?@?开始) , 如下所示 :
call GetScores(@minScore, @avgScore, @maxScore);12
4、该调用并没有任何输出 , 只是把调用的结果赋给了调用时传入的变量?@minScore, @avgScore, @maxScore?, 然后即可调用显示该变量的值 :
select @minScore, @avgScore, @maxScore;
5、使用 IN 参数 , 输入一个用户 id , 返回该用户的名字 :
create procedure GetNameByID(
in userID int,
out userName varchar(200)
)
begin
select name from user
where id = userID
into userName;
end;12345678910
6、调用存储过程 :
call GetNameByID(1, @userName);
select @userName;123
参考资料
SQL存储过程使用介绍.csdn博客[引用时间2017-12-31]
SQL 中存储过程的一些用法 和解释
?--?修改存储过程PZ_JF_TimePeriodCardTimes
?ALTER?PROCEDURE?[dbo].[PZ_JF_TimePeriodCardTimes]
????@StaffID?int,???????????--?员工ID
????@BeginDate?datetime,????--?开始日期
????@EndDate?datetime,??????--?结束日期
????@TimeBegin?nvarchar(5),?--?时间段开始
????@TimeEnd?nvarchar(5),???--?时间段结束
????@CardTimes?int,??????????--?时间区间内的刷卡次数?
????@UserNo?nvarchar(100)?
AS
BEGIN????
????--?如果StaffID为0则将之设为null,StaffID应该是输入参数
????if?@StaffID=0?begin?set?@StaffID=null?end?
????--?自定义变量@UserID,初始值为0
????Declare?@UserID?int=0?
????--?将变量@UserID设为满足条件且EnableYN='Y'的那个userid的值
????select?@UserID=userid?from?Ghrs_User?where?
UserNo=@UserNo
?and?EnableYN='Y'
????--?自定义变量@TempAdd?,初始值为0
????Declare?@TempAdd?int=0
????--?如果结束时间小于开始时间设置?@TempAdd?的值为1
????if?@TimeEnd@TimeBegin?begin?set?@TempAdd=1?end?
???select?StaffNo,StaffName,convert(nvarchar(10),b.CDate?,121)from?Ghra_Staff,Ghrb_Calendar?b
????where?b.CDate?between?@BeginDate?and??@EndDate?--条件1:Ghrb_Calendar中的CDate的值在开始时间和结束时间之内
??????and?Ghra_Staff.StaffID=ISNULL(?@StaffID,Ghra_Staff.staffID)?--条件2:如果@StaffID是null此过滤条件无效,@StaffID不是null,
??????and?dbo.FUserStaffPriv(@UserID,staffid)='Y'??--?条件3:FUserStaffPriv(@UserID,staffid)的结果是'Y'????
??????and?(select?COUNT(1)from?Ghrb_CardRecord
?????????????where?StaffID?=?Ghra_Staff.StaffID
???????????????and?EnableYN='Y'
???????????????and?CardTime?between??convert(nvarchar(10),b.CDate?,121)+'?'+?@TimeBegin?
???????????????and?convert(nvarchar(10),Dateadd(Day,@TempAdd,b.CDate)?,121)+'?'+?@TimeEnd????
??????????)=?@CardTimes??--条件4:StaffID一致、EnableYN为'Y',CardTime在开始时间和结束时间之间
END
在SQL中存储过程分为哪五类
1、目录存储过程
例如:sp_columns返回当前环境中可查询的指定表或视图的列信息。
sp_tables返回当前环境下可查询的对象的列表(任何可出现在FROM子句中的对象)。
2、复制类存储过程
例如:sp_addarticle创建项目并将其添加到发布中。此存储过程在发布服务器的发布数据库上执行。
3、安全管理类存储过程
例如:sp_addrole在当前数据库创建新的Microsoft SQL Server角色。
sp_password添加或更改Microsoft SQL Server登录的密码。
4、分布式查询存储过程
例如:sp_foreignkeys返回引用主键的外键,这些主键在链接服务器中的表上。
sp_primarykeys返回指定远程表的主键列,每个键列占一行。
5、扩展存储过程
例如:xp_sendmail向指定的收件人发送邮件和查询结果集附件。
xp_startmail启动SQL邮件客户端会话。
存储过程的特点
1、存储过程在服务器端运行,执行速度快。
2、存储过程执行一次后,其执行驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行,提高了系统性能。
3、确保数据库的安全。使用存储过程可以完成所有的数据库操作。
4、降低网络负载,提高效率。
5、可以接受用户参数,亦可返回参数。
6、作为一种安全机制来充分利用:通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。