返回
前端
分类

必赢备用网址查看数据库所有的存储过程名,下面介绍存储过程的基本语法

日期: 2020-01-02 07:54 浏览次数 : 53

Mysql存储过程入门知识

什么是存储过程:

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

必赢备用网址 ,  简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。

 

存储过程的好处:

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

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

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

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

一:存储过程概述

 SQL Server中的存储过程是使用T_SQL编写的代码段。它的目的在于能够方便的从系统表中查询信息,或者完成与更新数据库表相关的管理任务和其他的系统管理任务.T_SQL语句是SQL Server数据库与应用程序之间的编程接口。在很多情况下,一些代码会被开发者重复编写多次,如果每次都编写相同功能的代码,不但繁琐,而且容易出错,而且由于SQL Server逐条的执行语句会降低系统的运行效率。

 简而言之,存储过程就是SQL Server为了实现特定任务,而将一些需要多次调用的固定操作语句编写成程序段,这些程序段存储在服务器上,有数据库服务器通过程序来调用。

存储过程的优点:

  1. 存储过程加快系统运行速度,存储过程只在创建时编译,以后每次执行时不需要重新编译。
  2. 存储过程可以封装复杂的数据库操作,简化操作流程,例如对多个表的更新,删除等。
  3. 可实现模块化的程序设计,存储过程可以多次调用,提供统一的数据库访问接口,改进应用程序的可维护性。
  4. 存储过程可以增加代码的安全性,对于用户不能直接操作存储过程中引用的对象,SQL  Server可以设定用户对指定存储过程的执行权限。
  5. 存储过程可以降低网络流量,存储过程代码直接存储于数据库中,在客户端与服务器的通信过程中,不会产生大量的T_SQL代码流量。

存储过程的缺点:

  1. 数据库移植不方便,存储过程依赖与数据库管理系统, SQL Server 存储过程中封装的操作代码不能直接移植到其他的数据库管理系统中。
  2. 不支持面向对象的设计,无法采用面向对象的方式将逻辑业务进行封装,甚至形成通用的可支持服务的业务逻辑框架.
  3. 代码可读性差,不易维护。不支持集群。
[sql] 
#1,查看数据库所有的存储过程名    
#--这个语句被用来移除一个存储程序。不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程  
#SELECT NAME FROM mysql.proc WHERE db='数据库名';   

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

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

 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系统自身提供的存储过程,可以作为命令执行各种操作。

  系统存储过程主要用来从系统表中获取信息,使用系统存储过程完成数据库服务器的管理工作,为系统管理员提供帮助,为用户查看数据库对象提供方便,系统存储过程位于数据库服务器中,并且以sp_开头,系统存储过程定义在系统定义和用户定义的数据库中,在调用时不必在存储过程前加数据库限定名。例如:sp_rename系统存储过程可以修改当前数据库中用户创建对象的名称,sp_helptext存储过程可以显示规则,默认值或视图的文本信息,SQL SERVER服务器中许多的管理工作都是通过执行系统存储过程来完成的,许多系统信息也可以通过执行系统存储过程来获得。

  系统存储过程创建并存放在与系统数据库master中,一些系统存储过程只能由系统管理员使用,而有些系统存储过程通过授权可以被其它用户所使用。

2.用户存储过程(自定义存储过程)

  自定义存储过程即用户使用T_SQL语句编写的、为了实现某一特定业务需求,在用户数据库中编写的T_SQL语句集合,自定义存储过程可以接受输入参数、向客户端返回结果和信息,返回输出参数等。创建自定义存储过程时,存储过程名前加上"##"表示创建了一个全局的临时存储过程;存储过程前面加上"#"时,表示创建的局部临时存储过程。局部临时存储过程只能在创建它的回话中使用,会话结束时,将被删除。这两种存储过程都存储在tempdb数据库中。

用户定义的存储过程分为两类:T_SQL 和CLR

T_SQL:存储过程是值保存的T_SQL语句集合,可以接受和返回用户提供的参数,存储过程也可能从数据库向客户端应用程序返回数据。

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

3.扩展存储过程

 扩展存储过程是以在SQL SERVER环境外执行的动态连接(DLL文件)来实现的,可以加载到SQL SERVER实例运行的地址空间中执行,扩展存储过程可以用SQL SERVER扩展存储过程API编程,扩展存储过程以前缀"xp_"来标识,对于用户来说,扩展存储过程和普通话存储过程一样,可以用相同的方法来执行。 

 

创建存储过程的参数:

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 语句。但有一些限制。

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

三:创建存储过程

工欲善其事,必先利其器,准备数据如下:

use sample_db;
--创建测试books表
create table books (
    book_id int identity(1,1) primary key,
    book_name varchar(20),
    book_price float,
    book_auth varchar(10)
);
--插入测试数据
insert into books (book_name,book_price,book_auth)
                    values
                        ('论语',25.6,'孔子'),
                        ('天龙八部',25.6,'金庸'),
                        ('雪山飞狐',32.7,'金庸'),
                        ('平凡的世界',35.8,'路遥'),
                        ('史记',54.8,'司马迁');
[sql] 
#2,列出所有的存储过程   
#SHOW  PROCEDURE  STATUS;  
#3,查看存储过程详细  
#SHOW CREATE PROCEDURE 数据库名.存储过程名;   

[sql] 
#4,存储过程的创建 由括号包围的参数列必须总是存在。如果没有参数,也该使用一个空参数列()。  
#每个参数默认都是一个IN参数。要指定为其它参数,可在参数名之前使用关键词 OUT或INOUT   
#CREATE PROCEDURE存储过程名 (参数列表); 使用BEGIN ... END复合语句来包含多个语句  
#   BEGIN  
#         SQL语句代码块  
#   END  

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

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

  create Procedure GetUserAccount
 as
     select * from UserAccount
 go

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

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

1.创建无参存储过程

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
    drop proc proc_get_student
go
create procedure getAllBooks
as
select * from books;
--调用,执行存储过程
exec getAllBooks;

 

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) 这行代码。

2.修改存储过程

alter procedure dbo.getAllBooks 
as
select book_auth from books;
[sql] 
#5,DECLARE语句(用来声明局部变量);  
#要给变量提供一个默认值,需包含一个DEFAULT子句。如果没有DEFAULT子句,初始值为NULL。局部变量的作用范围在它被声明的BEGIN ... END块内。  
#DECLARE var_name[,...] type [DEFAULT value]   

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#调用存储过程中会有说到。

3.删除存储过程

drop procedure getAllBooks;

 

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。

4.重命名存储过程

sp_rename getAllBooks,proc_get_allBooks;
[sql] 
#6,变量SET语句  
#SET var_name = expr [, var_name = expr]    
[sql] 
#7,SELECT ... INTO语句;这个SELECT语法把选定的列直接存储到变量,因此,只有单一的行可以被取回。  
#SELECT col_name[,...] INTO var_name[,...] table_expr  
[sql] 
#8,修改存储过程   
#ALTER PROCEDURE 存储过程名SQL语句代码块;    
[sql] 
#9,删除存储过程  
#DROP PROCEDURE  IF  EXISTS 存储过程名;   
#存储过程的调用;存储过程名称后面必须加括号,哪怕该存储过程没有参数传递。  
[sql] 
#10,CALL 存储过程名(参数列表);    
[sql] 
#11,MySQL 存储过程参数(in;)  
#跟 C 语言的函数参数的值传递类似, MySQL 存储过程内部可能会修改此参数,但对 in 类型参数的修改,对调用者(caller)来说是不可见的(not visible)。  
[sql] 
#12,MySQL 存储过程参数(out)  
#MySQL 存储过程 “out” 参数:从存储过程内部传值给调用者。在存储过程内部,该参数初始值为 null,无论调用者是否给存储过程参数设置值  
[sql] 
#13,MySQL 存储过程参数(inout)  
#MySQL 存储过程 inout 参数跟 out 类似,都可以从存储过程内部传值给调用者。不同的是:调用者还可以通过 inout 参数传递值给存储过程。   
#注:如果仅仅想把数据传给 MySQL 存储过程,那就使用“in” 类型参数;  
#    如果仅仅从 MySQL 存储过程返回值,那就使用“out” 类型参数;  
#    如果需要把数据传给 MySQL 存储过程,还要经过一些计算后再传回给我们,此时,要使用“inout” 类型参数。  

#demo1:创建一个简单的存储过程(获取用户总数),带输出参数。  
#--删除存储过程  
DROP PROCEDURE IF EXISTS proc_users_getCount  
#--创建存储过程  
CREATE PROCEDURE proc_users_getCount(OUT n INT)  
BEGIN  
     SELECT COUNT(*) FROM users ;  
END  
#--MYSQL调用存储过程  
CALL proc_users_getCount(@n);   

#demo2:创建一个简单的存储过程(根据用户id获取用户信息),带输入参数。  
#--删除存储过程  
DROP PROCEDURE IF EXISTS proc_users_findById;  
#--创建存储过程  
CREATE PROCEDURE proc_users_findById(IN n INT)  
BEGIN  
     SELECT * FROM users WHERE id=n;   
END  
#--定义变量  
SET @n=1;  
#--调用存储过程   
CALL proc_users_findById(@n);  

#Last:操作存储过程时应注意:  
#1.删除存储过程时只需要指定存储过程名即可,不带括号;  
#2.创建存储过程时,不管该存储过程有无参数,都需要带括号;  
#3.在使用SET定义变量时应遵循SET的语法规则;  
#SET @变量名=初始值;  
#4.在定义存储过程参数列表时,应注意参数名与数据库中字段名区别开来,否则将出现无法预期的结果.  

#END:java中调用存储过程util  
#   // 执行存储过程  
#   @Override  
#   public ResultSet execProc(final String procName, final Object[] params) {  
#       return (ResultSet) getHibernateTemplate().execute(  
#               new HibernateCallback() {  
#                   public Object doInHibernate(Session session)  
#                           throws HibernateException, SQLException {  
#                       CallableStatement cstmt = session.connection()  
#                               .prepareCall(procName);  
#                       if (params != null) {  
#                           for (int i = 0; i < params.length; i++) {  
#                               cstmt.setObject(i + 1, params[i]);  
#                           }  
#                       }  
#                       ResultSet rs = cstmt.getResultSet();  
#                       return rs;  
#                   }  
#               });  
#   }  

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。

5.创建带参数的存储过程

 存储过程的参数分为两种:输入参数和输出参数

输入参数:用于向存储过程传入值,类似java语言或则c中的值传递。

输出参数:用于调用存储过程后,参会结果,类似java语言的按引用传递。

值传递和引用传递区别:

  • 基本数据类型赋值属于值传递;引用类型之间赋值属于引用传递。
  • 值传递传递的是实实在在的变量值;引用传递传递的是对象的引用地址。
  • 值传递后,两个变量改变的是各自的值;引用传递后,两个引用改变的是同一个对象的状态

(1)带一个参数存储过程

if (exists (select * from sys.objects where name = 'searchBooks'))
    drop proc searchBooks
go
create proc searchBooks(@bookID int)
as
    --要求book_id列与输入参数相等
    select * from books where book_id=@bookID;
--执行searchBooks
exec searchBooks 1;

(2)带2个参数存储过程

if (exists (select * from sys.objects where name = 'searchBooks1'))
    drop proc searchBooks1
go
create proc searchBooks1(
    @bookID int,
    @bookAuth varchar(20)
)
as
    --要求book_id和book_Auth列与输入参数相等
    select * from books where book_id=@bookID and book_auth=@bookAuth;
exec searchBooks1 1,'金庸';

(3)创建有返回值的存储过程

if (exists (select * from sys.objects where name = 'getBookId'))
    drop proc getBookId
go
create proc getBookId(
    @bookAuth varchar(20),--输入参数,无默认值
    @bookId int output --输入/输出参数 无默认值
)
as
    select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字

(4)创建带通配符的存储过程

if (exists (select * from sys.objects where name = 'charBooks'))
    drop proc charBooks
go
create proc charBooks(
    @bookAuth varchar(20)='金%',
    @bookName varchar(20)='%'
)
as 
    select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程charBooks
exec  charBooks    '孔%','论%';

(5)加密存储过程

  with encryption子句对用户隐藏存储过程的文本.下例创建加密过程,使用 sp_helptext 系统存储过程获取关于加密过程的信息,然后尝试直接从 syscomments 表中获取关于该过程的信息.

if (object_id('books_encryption', 'P') is not null)
    drop proc books_encryption
go
create proc books_encryption 
with encryption
as 
    select * from books;
--执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"

(6).不缓存存储过程

--with  recompile不缓存
if (object_id('book_temp', 'P') is not null)
    drop proc book_temp
go
create proc book_temp
with recompile
as
    select * from books;
go

exec book_temp;
exec sp_helptext 'book_temp';

(7).创建带游标参数的存储过程

if (object_id('book_cursor', 'P') is not null)
    drop proc book_cursor
go
create proc book_cursor
    @bookCursor cursor varying output
as
    set @bookCursor=cursor forward_only static for
    select book_id,book_name,book_auth from books
    open @bookCursor;
go
--调用book_cursor存储过程
declare @cur cursor,
        @bookID int,
        @bookName varchar(20),
        @bookAuth varchar(20);
exec book_cursor @bookCursor=@cur output;
fetch next from @cur into @bookID,@bookName,@bookAuth;
while(@@FETCH_STATUS=0)
begin 
    fetch next from @cur into @bookID,@bookName,@bookAuth;
    print 'bookID:'+convert(varchar,@bookID)+' , bookName: '+ @bookName
            +' ,bookAuth: '+@bookAuth;
end
close @cur    --关闭游标
DEALLOCATE @cur; --释放游标

(8).创建分页存储过程

if (object_id('book_page', 'P') is not null)
    drop proc book_page
go
create proc book_page(
    @TableName varchar(50),            --表名
    @ReFieldsStr varchar(200) = '*',   --字段名(全部字段为*)
    @OrderString varchar(200),         --排序字段(必须!支持多字段不用加order by)
    @WhereString varchar(500) =N'',  --条件语句(不用加where)
    @PageSize int,                     --每页多少条记录
    @PageIndex int = 1 ,               --指定当前为第几页
    @TotalRecord int output            --返回总记录数
)
as
begin
     --处理开始点和结束点
    Declare @StartRecord int;
    Declare @EndRecord int; 
    Declare @TotalCountSql nvarchar(500); 
    Declare @SqlString nvarchar(2000);    
    set @StartRecord = (@PageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @PageSize - 1 
    SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句
    SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句
    --
    IF (@WhereString! = '' or @WhereString!=null)
        BEGIN
            SET @TotalCountSql=@TotalCountSql + '  where '+ @WhereString;
            SET @SqlString =@SqlString+ '  where '+ @WhereString;            
        END
    --第一次执行得到
    --IF(@TotalRecord is null)
    --   BEGIN
           EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
    --  END
    ----执行主语句
    set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    Exec(@SqlString)    
END
--调用分页存储过程book_page
exec book_page 'books','*','book_id','',3,1,0;

--
declare @totalCount int
exec book_page 'books','*','book_id','',3,1,@totalCount output; 
select @totalCount as totalCount;--总记录数。

 

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。 

 

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

[sql] #1,查看数据库所有的存储过程名 #--这个语句被用来移除一个存储程序。不能在一个存储过程中删除另一个存储过...