一、MySQL数据库新特性之存储过程入门教程
在MYSQL中终于引入了存储过程这一新特性这将大大增强MYSQL的数据库处理能力在本文中将指导读者快速掌握MYSQL的存储过程的基本知识带领用户入门
存储过程介绍
存储过程是一组为了完成特定功能的SQL语句集经编译后存储在数据库中用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它存储过程可由应用程序通过一个调用来执行而且允许用户声明变量同时存储过程可以接收和输出参数返回执行存储过程的状态值也可以嵌套调用
存储过程的优点
作为存储过程有以下这些优点
()减少网络通信量调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别可是如果存储过程包含上百行SQL语句那么其性能绝对比一条一条的调用SQL语句要高得多
()执行速度更快存储过程创建的时候数据库已经对其进行了一次解析和优化其次存储过程一旦执行在内存中就会保留一份这个存储过程这样下次再执行同样的存储过程时可以从内存中直接中读取
()更强的安全性存储过程是通过向用户授予权限(而不是基于表)它们可以提供对特定数据的访问提高代码安全比如防止 SQL注入
()业务逻辑可以封装存储过程中这样不仅容易维护而且执行效率也高
当然存储过程也有一些缺点比如
可移植性方面当从一种数据库迁移到另外一种数据库时不少的存储过程的编写要进行部分修改
存储过程需要花费一定的学习时间去学习比如学习其语法等
在MYSQL中推荐使用MYSQL Query Browswer()这个工具去进行存储过程的开发和管理下面分步骤来学习MYSQL中的存储过程
定义存储过程的结束符
在存储过程中通常要输入很多SQL语句而SQL语句中每个语句以分号来结束因此要告诉存储过程什么位置是意味着整个存储过程结束所以我们在编写存储过程前先定义分隔符我们这里定义//为分隔符我们使用DELIMITER//这样的语法就可以定义结束符了当然你可以自己定义其他喜欢的符号
如何创建存储过程
下面先看下一个简单的例子代码如下
DELIMITER//CREATEPROCEDURE`p `()LANGUAGE SQLDETERMINISTICSQL SECURITY DEFINERMENT A procedure BEGINSELECT Hello World!;END//
下面讲解下存储过程的组成部分
)首先在定义好终结符后使用CREATE PROCEDURE+存储过程名的方法创建存储过程 LANGUAGE选项指定了使用的语言这里默认是使用SQL
)DETERMINISTIC关键词的作用是当确定每次的存储过程的输入和输出都是相同的内容时可以使用该关键词否则默认为NOT DETERMINISTIC
) SQL SECURITY关键词是表示调用时检查用户的权限当值为INVOKER时表示是用户调用该存储过程时检查默认为DEFINER即创建存储过程时检查
) MENT部分是存储过程的注释说明部分
lishixinzhi/Article/program/MySQL/201404/30557二、如何创建SQL存储过程
CREATE proc ProcBlacker_ADD--定义存储过程
@UserID int,--用户输入的参数1
@strName varchar(20),--用户输入的参数2
@strMobile varchar(20),--用户输入的参数3
@strRemark nvarchar(200)--用户输入的参数4,最后一个不需要加,
as
declare@ID int--定义变量
declare@Account varchar(50)--同上
declare@Content varchar(50)--同上
if not exists(select* from tblBlackList(nolock)--如果不存在则执行下面的语句.
where fdcMobile=@strMobile)
begin
insert into tblBlackList(fdcMobile,fdcName,fdtTm,fdcMemo)
values(@strMobile,@strName,getdate(),@strRemark)--getdate()获取当前系统的时间
set@ID=@@identity--返回一行记录
select@Account=Account from dbo.UserInfo where UserID=@UserID
set@Content='添加黑名单('+'操作者:'+@Account+','+'用户名:'+@strName+'手机:'+@strMobile+'备注:'+@strRemark+')'--设置变量的值
exec ProcRecordLog@Account,'添加',@Content---执行储过程ProcRecordLog
return@ID
end
GO
这个存储后面调用了ProcRecordLog存储过程
三、SQL 存储过程建立和使用方法
Sql Server的存储过程是一个被命名的存储在服务器上的Transacation-Sql语句集合,是封装重复性工作的一种方法,它支持用户声明的变量、条件执行和其他强大的编程功能。存储过程相对于其他的数据库访问方法有以下的优点：（1）重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。（2）提高性能。存储过程在创建的时候就进行了编译，将来使用的时候不用再重新编译。一般的SQL语句每执行一次就需要编译一次，所以使用存储过程提高了效率。（3）减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。（4）安全性。参数化的存储过程可以防止SQL注入式的攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。存储过程一共分为了三类：用户定义的存储过程、扩展存储过程以及系统存储过程。其中，用户定义的存储过程又分为Transaction-SQL和CLR两种类型。 Transaction-SQL存储过程是指保存的Transaction-SQL语句集合，可以接受和返回用户提供的参数。 CLR存储过程是指对.Net Framework公共语言运行时(CLR)方法的引用，可以接受和返回用户提供的参数。他们在.Net Framework程序集中是作为类的公共静态方法实现的。（本文就不作介绍了）创建存储过程的语句如下：Code
CREATE{ PROC| PROCEDURE} [schema_name.] procedure_name [; number ]
[{@parameter [ type_schema_name. ] data_type}
[ VARYING ] [= default ] [ [ OUT [ PUT ]
] [,n ]
[ WITH<procedure_option> [,n ]
[ FOR REPLICATION ]
AS{<sql_statement> [;][ n ]|<method_specifier>}
[;]
<procedure_option>::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE_AS_Clause ]
<sql_statement>::=
{ [ BEGIN ] statements [ END ]}
<method_specifier>::=
EXTERNAL NAME assembly_name.class_name.method_name [schema_name]:代表的是存储过程所属的架构的名称例如： Create Schema yangyang8848
Go
Create Proc yangyang8848.AllGoods
As Select* From Master_Goods
Go执行：Exec AllGoods发生错误。执行：Exec yangyang8848.AllGoods正确执行。 [；Number]:用于对同名过程进行分组的可选整数。使用一个 DROP PROCEDURE语句可将这些分组过程一起删除。例如： Create Proc S1;1
AS
Select* From Master_Goods
Go
Create Proc S1;2
As
Select* From Master_Location
Go创建完毕了两个存储过程。它们在同一个组S1里，如果执行Exec S1则存储过程默认执行 Exec S1;1。如果我们想得到所有据点信息则需要执行Exec S1;2。当我们要删除存储过程的时候，只能执行Drop Exec S1则该组内所有的存储过程被删除。 [@ parameter]:存储过程中的参数，除非将参数定义的时候有默认值或者将参数设置为等于另一个参数，否则用户必须在调用存储过程的时候为参数赋值。存储过程最多有2100个参数。例如： Create Proc yangyang8848.OneGoods
@GoodsCode varchar(10)
As
Select* From Master_Goods Where GoodsCode=@GoodsCode
Go调用的代码： Declare@Code varchar(10)
Set@Code='0004'
Exec yangyang8848.OneGoods@Code在参数的后边加入Output表明该参数为输出参数。 Create Proc yangyang8848.OneGoods
@GoodsCode2 varchar(10) output,@GoodsCode varchar(10)='0011'
As
Select* From Master_Goods Where GoodsCode=@GoodsCode
Set@GoodsCode2='0005'
Go调用方法：
Declare@VV2 varchar(10)
Exec yangyang8848.OneGoods@Code out注意：如果存储过程的两个参数一个有默认值一个没有，那么我们要把有默认值得放在后边，不然会出问题哦~~细心的朋友，可能看到上边的语句有一些不同，比如，存储过程用的是output，而调用语句用的是out。我要告诉您，两者是一样的。 [RECOMPILE]：指示数据库引擎不缓存该过程的计划，该过程在运行时编译。如果指定了 FOR REPLICATION，则不能使用此选项。对于 CLR存储过程，不能指定 RECOMPILE。这个说一个非常好用的函数 OBJECT_ID：返回架构范围内对象的数据库对象标识号。例如：我们创建存储过程时，可以如下写代码 If Object_ID('yangyang8848.OneGoods') Is Not Null
Drop Proc yangyang8848.OneGoods
Go Create Proc yangyang8848.OneGoods
@GoodsCode2 varchar(10) out,@GoodsCode varchar(10)='0011'
As
Select* From Master_Goods Where GoodsCode=@GoodsCode
Set@GoodsCode2='0005'
Go针对于上边的这个存储过程，我们调用以下SQL查询 Select definition From sys.sql_modules
Where object_id= Object_ID('yangyang8848.OneGoods');我们是可以查到结果的。可是如果我们对该存储过程加入[ ENCRYPTION ]那么你将无法看到任何结果 If Object_ID('yangyang8848.OneGoods') Is Not Null
Drop Proc yangyang8848.OneGoods
Go Create Proc yangyang8848.OneGoods
@GoodsCode2 varchar(10) out,@GoodsCode varchar(10)='0011' With Encryption
As
Select* From Master_Goods Where GoodsCode=@GoodsCode
Set@GoodsCode2='0005'
Go</SPAN>然后我们查询 sys.sql_modules目录视图，将返回给你Null。</p>然后我们执行以下SQL: Exec sp_helptext'yangyang8848.OneGoods'你将得到以下结果：The text for object'yangyang8848.OneGoods' is encrypted.说到这里你应该明白了，参数[ ENCRYPTION ]：是一种加密的功能，将 CREATE PROCEDURE语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 2005的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是，可通过 DAC端口访问系统表的特权用户或直接访问数据库文件的特权用户可使用此文本。此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。前两天写了一篇关于游标的介绍文章，下边写一个例子，将游标与存储过程一起使用上： If Object_ID('dbo.GetMasterGoods') Is Not Null
Drop Proc dbo.GetMasterGoods
Go Create Proc GetMasterGoods
@MyCursor Cursor Varying Output
With Encryption
As
Set@MyCursor= Cursor
For
Select GoodsCode,GoodsName From Master_Goods
Open@MyCursor
Go--下边建立另外一个存储过程，用于遍历游标输出结果 Create Proc GetAllGoodsIDAndName
As Declare@GoodsCode varchar(18)
Declare@GoodsName nvarchar(20)
Declare@MasterGoodsCursor Cursor
Exec GetMasterGoods@MasterGoodsCursor out
Fetch Next From@MasterGoodsCursor
InTo@GoodsCode,@GoodsName
While(@@Fetch_Status= 0)
Begin
Begin
Print@GoodsCode+':'+@GoodsName
End
Fetch Next From@MasterGoodsCursor
InTo@GoodsCode,@GoodsName
End
Close@MasterGoodsCursor
Deallocate@MasterGoodsCursor
Go最后执行Exec GetAllGoodsIDAndName结果为以下内容 0003:品0003
0004:品0004
0005:123123
0006:品0006
0007:品0007
0008:品0008
0009:品0009
0010:品0010
0011:品0011
0012:品0012
0013:品0013
0014:品0014