SQL Server数据库基础：存储过程介绍、用脚本“创建/修改/删除/调用”存储过程。

一、存储过程介绍。

    1.1 什么是存储过程？

        存储过程是一种为了完成特定功能的一个或一组SQL语句集合。经编译后存储在服务器端的数据库中，
        可以利用存储过程来加速SQL语句的执行效率。

    1.2 存储过程的分类。

        分为“系统存储过程”和“自定义存储过程”。

        系统存储过程： 

            存在于master数据库中，其他数据库可以直接调用，不必在前面加上数据库名。
            在创建其他新的数据库时，这些系统存储过程便会在新数据库中自动创建。

        自定义存储过程：

            为完成特定功能由用户自己创建的存储过程。
            存储过程可以传参，也可以不传参。
            存储过程可以有返回值，也可以没有返回值。
            存储过程可以只有一个操作，也可以有多个操作。

    1.3 存储过程的优点。

        1.3.1 提高应用程序的通用性和可移植性。
              允许多次调用，不必每次都去编写SQL语句，方便维护人员随时修改。

        1.3.2 可以有效管理数据库权限，提高数据库安全性。

        1.3.3 提高执行SQL语句的效率。

        1.3.4 减轻服务器的负担。

    1.4 存储过程的缺点。

        1.4.1 存储过程占用数据库物理空间。

        1.4.2 存储过程需要人员定期维护。



二、用脚本“创建/修改/删除/调用”存储过程。

    假如有下面2张表，以它们举例说明：


        产品信息表（ProductInfo）：
            标识列       Id                int 
            产品编号     ProductNo         varchar(50)
            产品名称     ProductName       nvarchar(100)
            产品类型编号  ProductTypeId     int 
            产品价格     ProductPrice      decimal(18, 2)
            产品数量     ProductCount      int 
            产品备注     ProductRemark     nvarchar(150) 

        产品类型表（ProductTypeInfo）：
            产品类型编号  ProductTypeId    int 
            产品类型名称  ProductTypeName  nvarchar(50)  



        use ProductManagementDB
        go 


        -- 先创建好主表，指定主键
        create table ProductTypeInfo
        (
            ProductTypeId int identity(1, 1) primary key not null,
            ProductTypeName nvarchar(50) not null
        )
        go 


        -- 再创建好从表，指定外键
        create table ProductInfo
        (
            Id int identity(10001, 1) primary key not null,    -- 标识种子，自增量
            ProductNo varchar(50) unique not null,              -- 指定unique约束
            ProductName nvarchar(100) not null,
            ProductTypeId int not null foreign key references ProductTypeInfo(ProductTypeId),   -- 指定ProductTypeInfo表中的ProductTypeId为外键
            ProductPrice decimal(18, 2) default(0.00) check(ProductPrice > 9.99 and ProductPrice < 499.99) not null,  -- 指定default约束和check约束
            ProductCount int default(0) not null,
            ProductRemark nvarchar(150) null
        )
        go



    2.1 创建存储过程。
        
        关键字：
            "create" 和 "procedure / proc"。

        语法：
            create proc/procedure 存储过程名称 参数列表
            as
                begin
                    T-SQL语句
                end
            go


        2.1.1 创建一个无参的存储过程。

            例如：

                create proc proc_productInfo
                as
                    begin

                        select 
                            pi.Id,
                            pi.ProductNo,
                            pi.ProductName,
                            pi.ProductTypeId,
                            pti.ProductTypeName,
                            pi.ProductPrice,
                            pi.ProductCount
                        from
                            ProductInfo pi
                        inner join
                            ProductTypeInfo pti
                        on
                            pi.ProductTypeId = pti.ProductTypeId

                    end
                go 


        2.1.2 创建一个有参的存储过程。

            存储过程内部:
                定义局部变量，关键字"declare"。
                设置单个变量，关键字"set"。
                设置多个变量，关键字"select"。
           
                declare @变量1 数据类型
                declare @变量1 数据类型, @变量2 数据类型, @变量3 数据类型......
                set @变量1 = 值1
                select @变量1 = 值1, @变量2 = 值2, @变量3 = 值3......


            例如：
                declare @DefaultProductNo varchar(50)            
                declare @DefaultProductName nvarchar(100)
                declare @DefaultProductTypeId int, @DefaultProductPrice decimal(18, 2), @DefaultProductCount int, @DefaultProductRemark nvarchar(150)

                set DefaultProductNo = 'isbn123456'
                set DefaultProductName = '毛泽东选集'
                select @DefaultProductTypeId = 12, @DefaultProductPrice = 59.99, @DefaultProductCount = 288, @DefaultProductRemark = '一代伟人的气势磅礴作品！'
            

            例如：

                create proc proc_addProductInfo
                    @ProductNo varchar(50),            
                    @ProductName nvarchar(100),
                    @ProductTypeId int,  
                    @ProductPrice decimal(18, 2), 
                    @ProductCount int,
                    @ProductRemark nvarchar(150)
                as
                    begin

                        insert into ProductInfo(ProductNo, ProductName, ProductTypeId, ProductPrice, ProductCount, ProductRemark)
                        values(@ProductNo, @ProductName, @ProductTypeId, @ProductPrice, @ProductCount, @ProductRemark);

                        delete from ProductInfo where Id = 10025;

                        select 
                            * 
                        from 
                            ProductInfo

                    end
                go



    2.2 调用存储过程。

        关键字：
            "execute / exec"。

        语法：
            exec 存储过程名 参数列表

        例如：

            2.2.1 调用一个无参的存储过程。

                exec proc_productInfo

            2.2.2 调用一个有参的存储过程。

                exec proc_addProductInfo 'isbn123456', '毛泽东选集', 12, 59.99, 288, '一代伟人的气势磅礴作品！'



    2.3 修改存储过程。

        关键字：
            "alter" 和 "procedure / proc"。

        语法：
            alter proc/procedure 存储过程名称 参数列表
            as
                begin
                    T-SQL语句
                end
            go


        例如：

            alter proc proc_productInfo
            as
                begin

                    select 
                        pi.*,
                        pti.ProductTypeName
                    from
                        ProductInfo pi
                    inner join
                        ProductTypeInfo pti
                    on
                        pi.ProductTypeId = pti.ProductTypeId

                end
            go 

    

    2.4 删除存储过程。

        关键字：
            "drop" 和 "procedure / proc"。

        语法：
            drop proc/procedure 存储过程名称 
            go

        例如：

            drop proc proc_productInfo
            go