在学习JDBC的时候,CallableStatement
对象出现了一个数据库存储过程,对这一概念不是很了解。所以就查阅相关资料,总结一下
什么是存储过程?
根据百度百科的解释,存储过程是Store Procedure,是在大型数据库系统中,一组为了完成特定功能的SQL语句集,它存储在数据库中,经过一次编译后再次调用就不需要编译了。
笔者认为可以把它认为是一个语言的方法,它也有存储过程名、存储过程参数、返回值。
我们通过指定存储过程的名字并给出参数(如果带的话)来执行它。
存储过程的设计规则
首先,我们已经知道了,存储过程是一系列sql语句的集合。我们可以通过存储过程来创建其他数据库对象。同时也可以在存储过程中创建本地临时表,或者引用本地临时表。如果在存储过程内来创建本地临时表的话,临时表仅为存储过程而存在,退出该存储过程后,临时表将消失。
存储过程还可以调用另一个存储过程,被调用的存储过程可以访问访问
存储过程中的参数的最大数目为 2100。
存储过程中的局部变量的最大数目仅受可用内存的限制。
根据可用内存的不同,存储过程最大可达 128 MB
实现存储过程
CREATE PROCEDURE Procedure_Name
--Procedure_Name为存储过程名(不能以阿拉伯数字开头),在一个数据库中触发器名是唯一的。名字的长度不能超过个字。PROCEDURE可以简写为PROC。
@Param1 Datatype,@Param2 Datatype
--@Param1和@Param2为存储过程的参数,Datatype为参数类型,多个参数用逗号隔开,最多允许个参数。
AS --存储过程要执行的操作
BEGIN
--BEGIN跟END组成一个代码块,可以写也可以不写,如果存储过程中执行的SQL语句比较复杂,用BEGIN和END会让代码更加整齐,更容易理解。
END
GO --GO就代表结操作完毕
exec Procedure_Name [参数名] --调用存储过程Procedure_Name。
drop procedure Procedure_Name --删除存储过程Procedure_Name,不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程
show procedure status --显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等
show create procedure Procedure_Name --显示存储过程Procedure_Name的详细信息
exec sp_helptext Procedure_Name --显示你这个Procedure_Name这个对象创建文本
更加详细的内容我们看下面:
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
详细说明参数
- procedure_name:存储过程的名称,前面加#为局部存储过程,加##全局存储过程。
- number:可选的参数,用来对同名的过程分组,以便用一条
DROP PROCEDURE
语句即可将同组的过程一起去除。
如:名为orders的应用程序使用的过程可以名为orderproc;1
和 orderproc;2
.使用DROP PROCEDURE orderproc
语句将去除整个组。
- @parameter:存储过程的参数。可以有一个或者多个。用户必须在执行过程中提供每个所声明参数的值(除非定义了该参数的默认值)。存储过程最多有2100个参数。
对于参数命名的规则:要用@符号作为第一个字符,参数名必须符合标识符的规则。
- data_type:参数的数据类型。所有数据类型(包括text、ntext、image)均可以用作存储过程的参数。不过cursor数据类型只能用于OUTPU参数。如果指定数据类型为cursor,同事也必须指定VARYING和OUTPUT关键字。
5.VARYING:指定作为输出参数支持的结果集(由存储过程动态构造,内容可以变化)。仅适用于游标cursor参数
- default:参数的默认值。如果定义了默认值,不必指定该参数的值就可以执行过程。默认值必须为常量或者是NULL。如果过程将该参数使用LIKE关键字,那么默认值中可以包含通配符(%、_ 、[]和[^])。
- OUTPUT:表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可以将信息返回给调用过程。Text、ntext和image参数可以用作OUTPUT参数。
- RECOMPILE:表明SQL Server不会缓存该过程的计划,该过程将在运行时重新编译,在使用非典型值或者临时值而不希望缓存在内存中执行计划时,请使用RECOMPILE 选项
- ENCRYPTION:表示SQL Server 加密syscomments表中包含CREATE PROCEDURE语句文本的条目。使用ENCRYPTION可以防止将过程作为SQL Server赋值的一部分发布。说明在升级过程中,Sql Server利用存储在syscomments中的加密注释来重新创建加密过程。
- FOR REPLICATION:指定不能在订阅服务器上执行为复制创建的存储过程。使用FOR REPLICATION选项创建的存储过程可用作存储过程筛选。
- AS:指定过程要执行的操作
- sql_statement:过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制。
实例操作
现有一个Student表。
下面是无参存储过程:
选出Student表中所有的信息:
create proc StuProc
as //此处as可省略不写
begin//begin和end是一对,不可以只写一个
select S#,Sname,Sage,Ssex from student
end
go
有参数的存储过程:
create proc StuProc
@sname varchar(100)
as
begin
select S#,Sname,Sage,Ssex from student where sname = @sname
end
go
exec StuProc '赵雷' //执行语句
上面是在外部给变量赋值,也可以直接在内部设置默认值
create proc StuProc
@sname varchar(100) = '赵雷'
as
begin
select S#,Sname,Ssex from student where sname = @sname
end
go
exec StuProc
也可以把变量的内容输出,使用output
create proc StuProc
@sname varchar(100),
@IsRight int output//传出参数
as
if exists(select s#,Sname,Sage,Ssex from student wheere sname = @sname)
set @IsRight = 1
else
set @IsRight = 0
go
declare @IsRight int
exec StuProc '赵雷',@IsRight output
select @IsRight
几个问题
问:存储过程在实际项目中用的多吗?
答:凡事都有利有弊,存储过程也是一样。在商业数据库应用中,例如金融、企业、政府等等,存储过程的使用非常广泛,有多方面的原因,例如:存储过程一旦调试完成通过后就能稳定运行,这与各个业务在一段时间内是相对稳定和确定是匹配的;存储过程大大地减少了业务系统与数据库的交互,一定程度降低了业务系统与数据库的耦合,例如即使业务系统与应用系统不在同一城市,对性能的影响也可控(100条SQL语句交互一次,即使延时由同城1ms增加到异地50ms,也只是增加49ms,如果交互100次,则增加4900ms)。在互联网行业,存储过程很少使用,一个重要的原因是MySQL的广泛使用,而MySQL的存储过程的功能很弱(跟商业数据库相比);另外也跟互联网行业变化快有一定的关系。
问:存储过程到底有什么用?
答:优点是大数据量的情况下提高计算效率,缺点是存储过程与系统代码分离,有时可能随手一动存储过程,造成与代码的不一致,不好进行版本控制。
参考资料
什么时候用存储过程---存储过程的好处
详细全面解析sql存储过程