存储过程

本随笔不是本人自创  仅作为笔记学习

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

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

[ FOR REPLICATION ]

创建存储过程的参数：

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

在SQL中存储过程的一般语法是什么？

一般分为十种情况，每种语法各不相同：

 1、 创建语法

1
2
3
4
5
6
7
create proc | procedure pro_name
   [{@参数数据类型} [=默认值] [output],
    {@参数数据类型} [=默认值] [output],
    ....
   ]
as
   SQL_statements

2、 创建不带参数存储过程

1
2
3
4
5
6
7
8
9
10
--创建存储过程
if (exists (select * from sys.objects where name = 'proc_get_student'))
    drop proc proc_get_student搜索
go
create proc proc_get_student
as
    select * from student;
 
--调用、执行存储过程
exec proc_get_student;

3、 修改存储过程

1
2
3
4
--修改存储过程
alter proc proc_get_student
as
select * from student;

4、 带参存储过程

1
2
3
4
5
6
7
8
9
10
--带参存储过程
if (object_id('proc_find_stu', 'P') is not null)
    drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
    select * from student where id between @startId and @endId
go
 
exec proc_find_stu 2, 4;

5、 带通配符参数存储过程

1
2
3
4
5
6
7
8
9
10
11
--带通配符参数存储过程
if (object_id('proc_findStudentByName', 'P') is not null)
    drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
    select * from student where name like @name and name like @nextName;
go
 
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';

6、 带输出参数存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
if (object_id('proc_getStudentRecord', 'P') is not null)
    drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
    @id int, --默认输入参数
    @name varchar(20) out, --输出参数
    @age varchar(20) output--输入输出参数
)
as
    select @name = name, @age = age  from student where id = @id and sex = @age;
go
 
-- 
declare @id int,
        @name varchar(20),
        @temp varchar(20);
set @id = 7; 
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;

7、 不缓存存储过程

1
2
3
4
5
6
7
8
9
10
11
--WITH RECOMPILE 不缓存
if (object_id('proc_temp', 'P') is not null)
    drop proc proc_temp
go
create proc proc_temp
with recompile
as
    select * from student;
go
 
exec proc_temp;

8、 加密存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
--加密WITH ENCRYPTION 
if (object_id('proc_temp_encryption', 'P') is not null)
    drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
    select * from student;
go
 
exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';

9、 带游标参数存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
if (object_id('proc_cursor', 'P') is not null)
    drop proc proc_cursor
go
create proc proc_cursor
    @cur cursor varying output
as
    set @cur = cursor forward_only static for
    select id, name, age from student;
    open @cur;
go
--调用
declare @exec_cur cursor;
declare @id int,
        @name varchar(20),
        @age int;
exec proc_cursor @cur = @exec_cur output;--调用存储过程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
    fetch next from @exec_cur into @id, @name, @age;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--删除游标

10、 分页存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
---存储过程、row_number完成分页
if (object_id('pro_page', 'P') is not null)
    drop proc proc_cursor
go
create proc pro_page
    @startIndex int,
    @endIndex int
as
    select count(*) from product
;    
    select * from (
        select row_number() over(order by pid) as rowId, * from product 
    ) temp
    where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分页存储过程
if (object_id('pro_page', 'P') is not null)
    drop proc pro_stu
go
create procedure pro_stu(
    @pageIndex int,
    @pageSize int
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize +1
    set @endRow = @startRow + @pageSize -1
    select * from (
        select *, row_number() over (order by id asc) as number from student 
    ) t
    where t.number between @startRow and @endRow;
 
exec pro_stu 2, 2;
 
 
 

 


其他回答

sql server存储过程语法 存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。     定义总是很抽象。存储过程其实就是能完成一定操作的一组SQL语句，只不过这组语句是放在数据库中的(这里我们只谈SQL Server)。如果我们通过创建存储过程以及在ASP中调用存储过程，就可以避免将SQL语句同ASP代码混杂在一起。这样做的好处至少有三个：     第一、大大提高效率。存储过程本身的执行速度非常快，而且，调用存储过程可以大大减少同数据库的交互次数。     第二、提高安全性。假如将SQL语句混合在ASP代码中，一旦代码失密，同时也就意味着库结构失密。     第三、有利于SQL语句的重用。
    在ASP中，一般通过command对象调用存储过程，根据不同情况，本文也介绍其它调用方法。为了方便说明，根据存储过程的输入输出，作以下简单分类：     1. 只返回单一记录集的存储过程     假设有以下存储过程(本文的目的不在于讲述T-SQL语法，所以存储过程只给出代码，不作说明)：
    /*SP1*/     CREATE PROCEDURE dbo.getUserList     as     set nocount on     begin        select * from dbo.[userinfo]     end     go
    以上存储过程取得userinfo表中的所有记录，返回一个记录集。通过command对象调用该存储过程的ASP代码如下:
    '**通过Command对象调用存储过程**     DIM MyComm,MyRst     Set MyComm = Server.CreateObject("ADODB.Command")     MyComm.ActiveConnection = MyConStr          'MyConStr是数据库连接字串     MyComm.CommandText      = "getUserList"     '指定存储过程名     MyComm.CommandType      = 4                 '表明这是一个存储过程     MyComm.Prepared         = true              '要求将SQL命令先行编译     Set MyRst = MyComm.Execute     Set MyComm = Nothing
    存储过程取得的记录集赋给MyRst，接下来，可以对MyRst进行操作。     在以上代码中，CommandType属性表明请求的类型，取值及说明如下：       -1   表明CommandText参数的类型无法确定
     1    表明CommandText是一般的命令类型       2    表明CommandText参数是一个存在的表名称       4    表明CommandText参数是一个存储过程的名称
    还可以通过Connection对象或Recordset对象调用存储过程，方法分别如下：
    '**通过Connection对象调用存储过程**     DIM MyConn,MyRst     Set MyConn = Server.CreateObject("ADODB.Connection")     MyConn.open MyConStr                            'MyConStr是数据库连接字串     Set MyRst  = MyConn.Execute("getUserList",0,4)  '最后一个参断含义同CommandType     Set MyConn = Nothing
    '**通过Recordset对象调用存储过程**     DIM MyRst     Set MyRst = Server.CreateObject("ADODB.Recordset")     MyRst.open "getUserList",MyConStr,0,1,4     'MyConStr是数据库连接字串,最后一个参断含义与CommandType相同
2. 没有输入输出的存储过程     请看以下存储过程：
    /*SP2*/     CREATE PROCEDURE dbo.delUserAll     as     set nocount on     begin        delete from dbo.[userinfo]     end     go
    该存储过程删去userinfo表中的所有记录，没有任何输入及输出，调用方法与上面讲过的基本相同，只是不用取得记录集：
    '**通过Command对象调用存储过程**     DIM MyComm     Set MyComm = Server.CreateObject("ADODB.Command")     MyComm.ActiveConnection = MyConStr          'MyConStr是数据库连接字串     MyComm.CommandText      = "delUserAll"      '指定存储过程名     MyComm.CommandType      = 4                 '表明这是一个存储过程     MyComm.Prepared         = true              '要求将SQL命令先行编译     MyComm.Execute                              '此处不必再取得记录集
    Set MyComm = Nothing
    当然也可通过Connection对象或Recordset对象调用此类存储过程，不过建立Recordset对象是为了取得记录集，在没有返回记录集的情况下，还是利用Command对象吧。
3. 有返回值的存储过程     在进行类似SP2的操作时，应充分利用SQL Server强大的事务处理功能，以维护数据的一致性。并且，我们可能需要存储过程返回执行情况，为此，将SP2修改如下：
    /*SP3*/     CREATE PROCEDURE dbo.delUserAll     as     set nocount on     begin        BEGIN TRANSACTION        delete from dbo.[userinfo]        IF @@error=0           begin              COMMIT TRANSACTION              return 1           end        ELSE           begin              ROLLBACK TRANSACTION              return 0           end               return     end     go
    以上存储过程，在delete顺利执行时，返回1，否则返回0，并进行回滚操作。为了在ASP中取得返回值，需要利用Parameters集合来声明参数：
    '**调用带有返回值的存储过程并取得返回值**     DIM MyComm,MyPara     Set MyComm = Server.CreateObject("ADODB.Command")     MyComm.ActiveConnection = MyConStr          'MyConStr是数据库连接字串     MyComm.CommandText      = "delUserAll"      '指定存储过程名     MyComm.CommandType      = 4                 '表明这是一个存储过程     MyComm.Prepared         = true              '要求将SQL命令先行编译     '声明返回值     Set Mypara = MyComm.CreateParameter("RETURN",2,4)
    MyComm.Parameters.Append MyPara     MyComm.Execute     '取得返回值     DIM retValue     retValue = MyComm(0)    '或retValue = MyComm.Parameters(0)     Set MyComm = Nothing
    在MyComm.CreateParameter("RETURN",2,4)中，各参数的含义如下：     第一个参数("RETURE")为参数名。参数名可以任意设定，但一般应与存储过程中声明的参数名相同。此处是返回值，我习惯上设为"RETURE"；     第二个参数(2)，表明该参数的数据类型，具体的类型代码请参阅ADO参考，以下给出常用的类型代码：     adBigInt: 20 ;     adBinary : 128 ;     adBoolean: 11 ;     adChar: 129 ;     adDBTimeStamp: 135 ;     adEmpty: 0 ;     adInteger: 3 ;     adSmallInt: 2 ;     adTinyInt: 16 ;     adVarChar: 200 ;     对于返回值，只能取整形，且-1到-99为保留值；     第三个参数(4)，表明参数的性质，此处4表明这是一个返回值。此参数取值的说明如下：     0 : 类型无法确定； 1: 输入参数；2: 输入参数；3：输入或输出参数；4: 返回值
    以上给出的ASP代码，应该说是完整的代码，也即最复杂的代码，其实
    Set Mypara = MyComm.CreateParameter("RETURN",2,4)     MyComm.Parameters.Append MyPara
    可以简化为
    MyComm.Parameters.Append MyComm.CreateParameter("RETURN",2,4)
    甚至还可以继续简化，稍后会做说明。     对于带参数的存储过程，只能使用Command对象调用(也有资料说可通过Connection对象或Recordset对象调用，但我没有试成过)。
4. 有输入参数和输出参数的存储过程     返回值其实是一种特殊的输出参数。在大多数情况下，我们用到的是同时有输入及输出参数的存储过程，比如我们想取得用户信息表中，某ID用户的用户名，这时候，有一个输入参数----用户ID，和一个输出参数----用户名。实现这一功能的存储过程如下：
    /*SP4*/     CREATE PROCEDURE dbo.getUserName        @UserID int,        @UserName varchar(40) output     as     set nocount on     begin        if @UserID is null return        select @UserName=username  

 

 
 

 

 
 
1 2 3
 

 

 


















 
 
posted @ 2018-01-29 14:19  超重语言  阅读(201)  评论(0)  编辑  收藏  举报