• 存储过程

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

    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
     

     

     


















     
     
    总结很重要哦 方法得当，坚持会有奇迹哦
  • 相关阅读:
    JSP实验四
    servlet应用
    SWPU电子邮件页面
    Java Web基础
    软件工程课程总结
    Beta-星期五
    个人作业-Alpha项目测试
    第三次作业
    学期总结-西北师范大学-18计师班助教牛百泉
    Java学习计划
  • 原文地址：https://www.cnblogs.com/sunyuhuan/p/8377094.html
Copyright © 2020-2023  润新知