MySQL存储过程上海时时乐走势图官网

-- 显示所有数据库中所有存储过程的基本信息,如所属数据库、存储过程名、创建时间等
show procedure status;

-- 显示指定数据库中所有存储过程的基本信息,如 Demo 数据库
show procedure status where db='Demo';

6.同时返回参数和记录集的存储过程

-------------创建名为GetUserAccount的存储过程----------------

create Procedure GetUserAccountRe2
 @UserName nchar(20),
 @UserID int output
as
   if(@UserName>5)
      select @UserID=COUNT(*) from UserAccount where UserID>25
   else
      set @UserID=1000
      select * from UserAccount
      return @@rowcount
go

-------------执行上面的存储过程----------------

exec GetUserAccountRe2 '7',null
结果:返回执行 select * from UserAccount 这句代码的结果集,同时@userID为COOUT(*)即 =1,Retun Value=9。 

三、系统存储过程

系统存储过程是SQL Server系统自身提供的存储过程,可以作为命令执行各种操作。

系统存储过程主要用来从系统表中获取信息,使用系统存储过程完成数据库服务器的管理工作,为系统管理员提供帮助,为用户查看数据库对象提供方便,系统存储过程位于数据库服务器中。例如,sys.sp_changedbowner 就是一个系统存储过程。从物理意义上讲,系统存储过程存储在源数据库中,并以“sp_”开头。从逻辑意义上来讲,系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。可将GRANT、DENY 和REVOKE权限应用于系统存储过程。

上海时时乐走势图官网 1

图1

上海时时乐走势图官网 2

图2

注意:虽然SQL Server 2008中的系统存储过程被放在master数据库中,但是仍可以在其他数据库中对其进行调用,而且在调用时不必在存储过程名前加上数据库名。甚至当创建一个新数据库时,一些系统存储过程会在新数据库中被自动创建。

上海时时乐走势图官网 3

图3

上海时时乐走势图官网 4

图4

例如:sp_databases --列出当前系统中的所有数据库

上海时时乐走势图官网 5

图5

例如:sp_renamedb  --更改数据库的名称

上海时时乐走势图官网 6

图6

如图7,将原有的dataBase数据库更改为Hotel数据库。结果如图8.

上海时时乐走势图官网 7

图7

上海时时乐走势图官网 8

图8

不带参数,直接写数据库名称,如图9

上海时时乐走势图官网 9

图9

例如:sp_help --查看表中的所有信息

上海时时乐走势图官网 10

图10

注意:如图10,我们看到消息版中,告诉我们数据库“master”中不存在或对于此操作无效,发现我们要查询的表不在master数据库中。如图11:

上海时时乐走势图官网 11

图11

以上是常用的系统存储过程。


接下来,简单说一下扩展存储过程,如图12.通过扩展存储过程在D盘下创建myFile文件夹。

上海时时乐走势图官网 12

图12

结果如下:

上海时时乐走势图官网 13

图13



-- 删除存储过程
drop procedure sp_char_split_inser;
drop procedure if exists sp_char_split_inser;

5. 同时具有返回值、输入参数、输出参数的存储过程

-------------创建名为GetUserAccount的存储过程----------------

create Procedure GetUserAccountRe1
   @UserName nchar(20),
   @UserID int output
as
       if(@UserName>5)
        select @UserID=COUNT(*) from UserAccount where UserID>25
else
       set @UserID=1000
       return @@rowcount
go

-------------执行上面的存储过程----------------

exec GetUserAccountRe1 '7',null
结果:@userID为COOUT(*)即 =1,Retun Value=1。

                                                      存储过程的特点


接受输入参数并以输出参数的格式向调用过程或者批处理返回多个值。

包含用于在数据库中执行操作(包括调用其他过程)的编程语句。

向调用过程或者批处理返回状态值,以指明成功或者失败(以及失败的原因)。

-- 定义结束符为“$$”,mysql默认结束符为“;”
-- 意思是告诉mysql解释器,该段命令是否已经结束了,即标识一段命令起始和结束
delimiter $$

-- 创建存储过程
-- sp_char_split_inser:存储过程名称
-- strs:存储过程参数名称
-- in:表示该参数为输入参数;out:表示该参数为输出参数;inout:表示该参数为输入输出参数。不写时默认为in,即输入参数。
create procedure sp_char_split_inser(in strs text)
begin 
    declare i int default 0;
    declare leng int default 0;
    declare word char(1);

    -- 判断字符串是否为空或空字符串
    if(strs is not null && strs <> '') then 
        -- 获取字符串长度
        set leng = char_length(strs);
        -- 循环
        while i < leng do 
            -- 获取第一个字符
            set word=left(strs,1);
            if(word is not null && word <> '') then 
                -- 判断该条数据是否存在
                if not exists(select 1 from demo.charinfo where Hanzi=word limit 1) then 
                    -- 插入数据
                    insert into demo.charinfo(Hanzi) values(word);
                end if;
            end if;
            -- 截取除第一个字符之外的所有字符
            set strs=substring(strs,2);
            set i=i 1;
        end while;
    end if;
end;
-- 命令结束
$$
delimiter ;

什么是存储过程:

 存储过程可以说是一个记录集吧,它是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后再给这个代码块取一个名字,在用到这个功能的时候调用他就行了。

二、扩展存储过程

扩展存储过程允许使用编程语言(例如C)创建自己的外部例程。扩展存储过程是以在SQL Server环境外执行的动态连接(DLL文件)来实现的,可以加载到SQL Server实例运行的地址空间中执行,扩展存储过程可以用SQL Server扩展存储过程API编程,扩展存储过程以 前缀“xp_”来标识,对于用户来说,扩展存储过程和普通存储过程一样,可以用相同的方法来执行。

一、查看存储过程

2.没有输入输出的存储过程

-------------创建名为GetUserAccount的存储过程----------------

 create Procedure inUserAccount
 as
     insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9)
 go

-------------执行上面的存储过程----------------

 exec inUserAccount
 结果:相当于运行 insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9) 这行代码。

一般,操作最简洁明了的是找到创建好的存储过程,单击右键-->执行存储过程


上海时时乐走势图官网 14

图21

如图22.可以在红框内根据参数,输入想要查询的存储过程信息,点击确定即可。

上海时时乐走势图官网 15

图22

二、创建存储过程

存储过程的好处:

 1.由于数据库执行动作时,是先编译后执行的。然而存储过程是一个编译过的代码块,所以执行效率要比T-SQL语句高。

 2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句,所以也能降低网络的通信量,提高通信速率。

 3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库,从而确保数据的安全。

小结:总之存储过程是好东西,在做项目时属于必备利器,下面介绍存储过程的基本语法。

存储过程分三大类:用户定义的存储过程、扩展存储过程、系统存储过程。


-- 调用存储过程
set @s='测试文字';
call sp_char_split_inser(@s);
call sp_char_split_inser('测试一下');

存储过程的一些基本语法:

--------------创建存储过程-----------------

 CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
    [ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

 [ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

--------------调用存储过程-----------------

EXECUTE Procedure_name '' --存储过程如果有参数,后面加参数格式为:@参数名=value,也可直接为参数值value

--------------删除存储过程-----------------

drop procedure procedure_name    --在存储过程中能调用另外一个存储过程,而不能删除另外一个存储过程

存储过程的缺点:

1、数据库移植不方便,存储过程依赖与数据库管理系统,SQL Server存储过程中封装的操作代码不能直接移植到其他的数据库管理系统中。

2、不支持面向对象的设计,无法采用面向对象的方式将逻辑业务进行封装,甚至形成通用的可支持服务的业务逻辑框架。

3、代码可读性差,不易维护。不支持集群。


 

1. 只返回单一记录集的存储过程

-------------创建名为GetUserAccount的存储过程----------------

  create Procedure GetUserAccount
 as
     select * from UserAccount
 go

-------------执行上面的存储过程----------------

   exec GetUserAccount
  结果:相当于运行 select * from UserAccount 这行代码,结果为整个表的数据。

创建存储过程


创建不带参数的存储过程

CREATE PROC / PROCEDURE 存储过程名      //存储过程名称在架构中必须是唯一的。

AS

     SQL语句

GO

上海时时乐走势图官网 16

图15

创建带参数的存储过程

CREATE PROC / PROCEDURE 存储过程名

       @参数1  数据类型=默认值,

       ....... 

       @参数n  数据类型=默认值 

AS

    SQL语句

GO

上海时时乐走势图官网 17

图16

带参数的存储过程我们创建完成,刷新数据库,就可以看到我们创建好的存储过程。那么我们如何查询执行带参数的存储过程呢?

以上面的为例,第一种查询执行带参数的存储过程:如图一17

上海时时乐走势图官网 18

图17

第二种方式:如图--18

上海时时乐走势图官网 19

图18

第三种方式:--如图19

上海时时乐走势图官网 20

图19

这里我们需要注意的是:

上例结束时间我们设为默认default时,点击执行,消息版中报错。显示必须传递参数3。如果我们前面的参数是以@name=value的形式传递值的,那所有的参数都必须是这样的形式来传递。如图20

上海时时乐走势图官网 21

图20






 

创建存储过程的参数:

1.procedure_name :存储过程的名称,在前面加#为局部临时存储过程,加##为全局临时存储过程。

2.; number:是可选的整数,用来对同名的过程分组,以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如,名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符,则数字不应包含在标识符中,只应在 procedure_name 前后使用适当的定界符。 

3.@parameter: 存储过程的参数。可以有一个或多个。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。存储过程最多可以有 2.100 个参数。 使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身;相同的参数名称可以用在其它过程中。默认情况下,参数只能代替常量,而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息,请参见 EXECUTE。 

4.data_type:参数的数据类型。所有数据类型(包括 text、ntext 和 image)均可以用作存储过程的参数。不过,cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor,也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息,请参见数据类型。 说明 对于可以是 cursor 数据类型的输出参数,没有最大数目的限制。 

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

6.default: 参数的默认值。如果定义了默认值,不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字,那么默认值中可以包含通配符(%、_、[] 和 [^])。

7.OUTPUT :表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。 

8.RECOMPILE: 表明 SQL Server 不会缓存该过程的计划,该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时,请使用 RECOMPILE 选项。

9.ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明 在升级过程中,SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。 

10.FOR REPLICATION :指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选,且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。 

11.AS :指定过程要执行的操作。

12.sql_statement :过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。

小结:看过这些基本语法后,下面我就根据语法创建各式的存储过程。

                                                          设计存储过程


在创建存储过程之前,必须先设计存储过程。在设计存储过程中,需要了解存储过程的创建规则、存储过程内部名称以及加密过程等相关知识。

一、创建存储过程的规则

(1)CREATE PROCEDURE定义自身可以包括任意数量和类型的SQL语句,但图5中的语句除外。因为不能在存储过程的任何位置使用这些语句。

上海时时乐走势图官网 22

图14

(2)可以引用在同一存储过程中创建的对象,只要引用时已经创建了该对象即可。

(3)可以在存储过程内引用临时表。

(4)如果在存储过程内创建本地临时表,则临时表仅为该存储过程而存在;退出该存储过程后,临时表将消失。

(5)如果执行的存储过程将调用另一个存储过程,则被调用的存储过程可以访问由第一个存储过程创建的所有对象,包括临时表在内。

(6)如果执行对远程Microsoft SQL Server 2008 实例进行更改的远程存储过程,则不能回滚这些更改。远程存储过程不参与事务处理。

(7)存储过程中的参数的最大数目为2100.

(8)存储过程中的局部变量的最大数目仅受可用内存的限制。

(9)根据可用内存的不同,存储过程最大可达128MB。

二、限定存储过程内的名称

在存储过程内,如果用于语句(例如SELECT、INSERT)的对象名没有限定架构,则架构将默认为该存储过程的架构。在存储过程内,如果创建该存储过程的用户没有限定SELECT、INSERT、UPDATE或者DELETE语句中引用的表名或者视图名,则默认情况下,通过该存储过程对这些表进行的访问将受到该过程创建者的权限的限制。

三、加密存储过程的定义

如果要创建存储过程,并且希望确保其他用户无法查看该过程的定义,则可以使用WITHENCRYPTION子句。这样,过程定义将以不可读的形式存储。

注:存储过程一旦被加密,其定义将无法解密,任何人(包括该存储过程的所有者或者系统管理员)都将无法查看该存储过程的定义。


三、调用存储过程

7.返回多个记录集的存储过程

-------------创建名为GetUserAccount的存储过程----------------

 create Procedure GetUserAccountRe3
 as
   select * from UserAccount
  select * from UserAccount where UserID>5
go

-------------执行上面的存储过程----------------

 exec GetUserAccountRe3
 结果:返回两个结果集,一个为 select * from UserAccount,另一个为 select * from UserAccount where UserID>5 。

转载链接:http://www.cnblogs.com/knowledgesea/archive/2013/01/02/2841588.html

一、用户定义的存储过程

该种存储过程是指封装了可重用代码的模块或者例程。存储过程可以接受输入参数、向客户端返回表格或者标量结果和消息。调用数据定义语言(DDL)和数据操纵语言(DML)语句,然后返回输出参数。创建自定义存储过程时,存储过程名前加上“##”表示创建了一个全局的临时存储过程;存储过程名前加上“#”,表示创建的局部临时存储过程。局部临时存储过程只能在创建它的回话中使用,会话结束时,将被删除。这两种存储过程都存储在tempdb数据库中。

用户定义的存储过程有两种类型:T-SQL或者CLR.

T-SQL:存储过程是指保存的T-SQL语句集合,可以接受和返回用户提供的参数。例如,存储过程中可能包含根据客户端应用程序提供的信息在一个或者多个表中插入新行所需的语句。存储过程也可能从数据库向客户端应用程序返回数据。

CLR: 存储过程是指引用Microsoft.NET Framework公共语言的方法存储过程,可以接受和返回用户提供的参数,它们在.NET Framework程序集是作为类的公共静态方法实现的。

四、删除存储过程

4.有输入参数和输出参数的存储过程

-------------创建名为GetUserAccount的存储过程----------------

create Procedure GetUserAccountRe
@UserName nchar(20),
@UserID int output
as
       if(@UserName>5)
             select @UserID=COUNT(*) from UserAccount where UserID>25
      else
            set @UserID=1000
go

-------------执行上面的存储过程----------------

  exec GetUserAccountRe '7',null
  解释:@UserName为输入参数,@UserID为输出参数。 运行结果为@userID为COOUT(*)即 =1。

存储过程的优点:

1、存储过程加快系统运行速度,存储过程在创建时已经被编译,以后每次执行时不需要重新编译。

2、存储过程可以封装复杂的数据库操作,简化操作流程,例如对多个表的更新,删除等。

3、可实现模块化的程序设计,存储过程可以多次调用,提供统一的数据库访问接口,改进应用程序的可维护性。

4、存储过程可以增加代码的安全性,对于用户不能直接操作存储过程中引用的对象,SQL Server可以设定用户对指定存储过程的执行权限。用户只需提交存储过程名称就可以直接执行,避免了攻击者非法截取SQL代码获得用户数据的可能性。

5、存储过程可以降低网络流量,存储过程代码直接存储于数据库中,在客户端与服务器的通信过程中,不会产生大量的T-SQL代码流量。一个需要数百行SQL语句代码的操作可以通过一条执行过程代码来执行,而不需要在网络中发送数百行代码

 

3.有返回值的存储过程

-------------创建名为GetUserAccount的存储过程----------------

 create Procedure inUserAccountRe
 as
        insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(10,10,'2013-01-02',10)

 return @@rowcount
 go

-------------执行上面的存储过程----------------

 exec inUserAccountRe
 解释:这里的@@rowcount为执行存储过程影响的行数,执行的结果是不仅插入了一条数据,还返回了一个值即 return value =1  ,这个可以在程序中获取,稍后在c#调用存储过程中会有说到。

存储过程是一组预先编辑好的SQL语句组成,编译后存储在数据库中。

存储过程可包含程序流、逻辑及对数据库的查询。它们可以接受参数、输出参数、返回单个或者多个结果集及返回值。

 

本文由上海时时乐走势图发布于上海时时乐走势图官网,转载请注明出处:MySQL存储过程上海时时乐走势图官网

您可能还会对下面的文章感兴趣: