【java学习】数据库的存储过程总结

在学习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 ]

详细说明参数

1. procedure_name:存储过程的名称，前面加#为局部存储过程，加##全局存储过程。
2. number：可选的参数，用来对同名的过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起去除。

如：名为orders的应用程序使用的过程可以名为orderproc;1 和 orderproc;2.使用DROP PROCEDURE orderproc语句将去除整个组。

1. @parameter：存储过程的参数。可以有一个或者多个。用户必须在执行过程中提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多有2100个参数。

对于参数命名的规则：要用@符号作为第一个字符，参数名必须符合标识符的规则。

1. data_type:参数的数据类型。所有数据类型（包括text、ntext、image）均可以用作存储过程的参数。不过cursor数据类型只能用于OUTPU参数。如果指定数据类型为cursor，同事也必须指定VARYING和OUTPUT关键字。

5.VARYING:指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标cursor参数

1. default：参数的默认值。如果定义了默认值，不必指定该参数的值就可以执行过程。默认值必须为常量或者是NULL。如果过程将该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_ 、[]和[^])。
2. OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可以将信息返回给调用过程。Text、ntext和image参数可以用作OUTPUT参数。
3. RECOMPILE:表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译，在使用非典型值或者临时值而不希望缓存在内存中执行计划时，请使用RECOMPILE 选项
4. ENCRYPTION：表示SQL Server 加密syscomments表中包含CREATE PROCEDURE语句文本的条目。使用ENCRYPTION可以防止将过程作为SQL Server赋值的一部分发布。说明在升级过程中，Sql Server利用存储在syscomments中的加密注释来重新创建加密过程。
5. FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。使用FOR REPLICATION选项创建的存储过程可用作存储过程筛选。
6. AS:指定过程要执行的操作
7. 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存储过程