SQL Server与MySQL存储过程学习记录之一

by shinichi_wtn 2009-12-15 13:39

SQL语句执行的时候要先编译,然后执行。存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。

SQL Server的存储过程在设计BNU校园百科的时候就有使用,因为存储过程不仅能提高SQL语言的执行效率,同时参数化的调用比每次都写一条SQL语句来得方便。

【SQL Server存储过程的建立】

在企业管理器新建一个StoreProcedure会自动生成相应导航代码,方便用户编写函数。


-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:   <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

我们现在设计一个带参数的存储过程,比如我们经常需要使用这样的一类查询:包含keyword的所有结果集,这个时候为该查询实现一个存储过程如下

CREATE PROCEDURE [dbo].[p_SearchByKeyWord]
@keyword nvarchar(200)
AS
BEGIN
SELECT * From [myTable] where [myWordColumn] like
'%'+@keyword+'%'
END
GO

在SQL Server中调用存储过程的方法是使用EXEC命令,比如我们要查包含“北京师范大学”的所有记录,我们运行如下T-SQL命令

EXEC p_SearchByKeyWord @keyword = '北京师范大学'

就能得到结果集如下:如果有多个参数,需要用逗号隔开分别传入,即

EXEC myProcedureName @parameter1='×××',@parameter2='×××',......

如果想要修改已经存在的存储过程,只要把CREATE换成ALTER即可,比如我们限定查询数量为100条记录,修改已经建立的p_SearchByKeyWord

ALTER PROCEDURE [dbo].[p_SearchByKeyWord]
@keyword nvarchar(200)
AS
BEGIN
SELECT TOP 100 * From [myTable] where [myWordColumn] like '%'+@keyword+'%'

END
GO

删除存储过程就更简单了,直接使用DROP命令,比如

DROP PROCEDURE [dbo].[p_SearchByKeyWord]

【MySQL存储过程的建立】

MySQL存储过车的创建、修改、删除语法与SQL Server类似,但是参数定义和调用方式都不一样,所以需要花时间看官方的手册!

MYSQL没有提供微软企业管理那样的强大前端,当然就没有创建存储过程的向导,但是一旦熟悉了存储过程建立的方法,就觉得自己手写要方便得多。即使有很多第三方的MySQL前端,如Navicat提供了向导式的存储过程建立,但是为了了解底层的语法细节,手动编写每一步是很重要的。下面来看MySQL的存储过程建立方式。

官方说明有这样一段

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
   
proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type

characteristic:
    LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
    Valid SQL procedure statement

很好理解,比如我们要创建一个简单的不带参数的存储过程p_Test,只需如下极短的代码

CREATE p_Test()
BEGIN
Select * FROM [myTable] LIMIT 100;
END;

其中BEGINEND块是存储过程的核心,这个与SQL Server相同。另外,需要注意的是,不管有没有参数,存储过程名称后面需要有一个括号,这个与SQL Server是不相同的(有参数和没有参数均不需要任何括号),所以不要忘了。

现在来创建一个带有参数的查询,记住,所有参数都要包含在刚才所说的括号中。比如我们需要统计在给定两个时间点的所有访问记录数,于是每次调用需要传入起始时间和结束时间,于是需要指定两个参数,如下 _starttime 和 _endtime ,这两个变量之前的 IN 用来修饰参数,表示是输入变量,也可以忽略(默认即输入),也可以显示指定为OUT(输出变量),INOUT(既作为输入,也作为输出,在某些复杂的程序中可能会遇到)

CREATE PROCEDURE p_GetVisitCountByTime(
IN _starttime datetime,
IN _endtime datetime
)
BEGIN
SELECT COUNT(VISIT_ID) AS total FROM myTable
             WHERE VISIT_TIME>=_starttime and
             VISIT_TIME<_endtime and;
END;

创建好之后就能调用了,注意MySQL调用存储过程的方法和SQL Server是不一样的,需要使用语法CALL,然后参数全部包含在括号里面,比如

CALL p_GetVisitCountByTime('2009-12-01 00:00:00','2009-12-02 00:00:00');

这句话查询在09年12月1号到12月2号的总访问人数,清晰明了。

最后说明一些要注意的地方,也是SQL Server与MySQL差异的地方,在编写存储过程的时候,SQL Server并不需要在每个块结束后使用分号,但是MySQL需要在每个块结束后使用分号标识,比如

CREATE p_Test()
BEGIN
Select * FROM [myTable] LIMIT 100;
END
--这里没有分号

那么会提示编译错误,同样,如果是这样

CREATE p_Test()
BEGIN
Select * FROM [myTable] LIMIT 100--这里没有分号
END;

那么也会提示编译错误,所以在MySQL里一定不要忘了分号,当然SQL Server则不需要分号!

更多关于存储过程里循环、条件、定义内部变量等高级的应用和使用上的差异会在学习记录之二再谈,其实也就是SQL Server和MySQL的编程语法!

总结一下,由于存储过程是数据库的一个重要部分,因此不同的数据库都有起实现方式,但是思想是一致的,在学习不同数据库相应操作的时候,应该进行类比学习,这样在做特定应用的时候就能更加顺手!我也是现在经常遇到基于MySQL的项目才开始关注MySQL的各种细节!

可以继续阅读学习记录之二

(仅用于Gavatar)

  Country flag

biuquote
  • Comment
  • Preview
Loading

About

shinichi_wtnI'm Shinichi_wtn

Software Engineering Manager at Microsoft

[More...]


Month List