SQLserver学习day09 存储过程

存储过程

存储过程是一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。可包含数据操作语句，变量，逻辑控制语句等。

优点：
①重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。
②减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。
③安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程
④执行速度快，因为存储过程在创建时已经被编译，每次执行不需要再编译，而SQL语句每次执行都需要编译。

存储过程的分类

• 系统存储过程（用来管理SQLserver和显示有关数据库和用户信息的存储过程，以sp_开头，存放在master系统数据库中）
• 扩展存储过程（未来版本将会删除，不建议使用）
• 自定义存储过程

存储过程的调用
EXECUTE 过程名 [参数] 或者 EXEC 过程名 [参数]

--调用系统存储过程
USE master
GO
EXECUTE sp_databases--列出当前系统的数据库的存储过程

系统存储过程有很多，可以查阅文档进行学习。

创建存储过程

创建不带参数的存储过程

语法
CREATE PROCEDURE(PROC) 存储过程名
AS
SQL语句
GO

USE E_Market--选择数据库
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='sp_userinfo')--判断是否存在这个存储过程
DROP PROC sp_userinfo--存在就删除
GO
CREATE PROC sp_userinfo--创建存储过程
AS
    SELECT UserName,Address,Phone,PayMoney FROM UserInfo
    INNER JOIN OrderInfo ON UserInfo.UserId=OrderInfo.UserId
GO

EXEC sp_userinfo--调用存储过程
GO

创建带输入参数的存储过程

语法
CREATE PROCEDURE ( PROC ) 存储过程名
@参数1 数据类型=默认值，
……………,
@参数n 数据类型=默认值
AS
SQL语句
GO

--创建存储过程，三个参数，最低工资，最高工资和用户姓名，查询指定的最低工资和最高工资之间的用户，未
--指定用户的话默认所有用户
IF EXISTS(SELECT * FROM sysobjects WHERE name='sp_demo')--判断是否存在
DROP PROC sp_demo
GO
CREATE PROCEDURE sp_demo--创建三个参数，用户默认值null
        @lowmoney money,
        @highmoney money,
        @user varchar(20)=NULL
AS
        IF(@user is NULL)
            BEGIN
                SELECT UserName,Address,Phone,PayMoney FROM UserInfo
                INNER JOIN OrderInfo ON UserInfo.UserId=OrderInfo.UserId
                WHERE PayMoney BETWEEN @lowmoney AND @highmoney
            END
        ELSE
            BEGIN
                SELECT UserName,Address,Phone,PayMoney FROM UserInfo
                INNER JOIN OrderInfo ON UserInfo.UserId=OrderInfo.UserId
                WHERE PayMoney BETWEEN @lowmoney AND @highmoney AND UserName=@user
            END
GO


--隐式调用,参数的顺序必须与创建存储过程时的参数顺序完全相同，没输入参数的使用默认值
EXEC sp_demo 1000,2000
GO
--显式调用
EXEC sp_demo @highmoney=1500,@lowmoney=1000

创建带输出参数的存储过程

--插入一行数据，返回插入的行数
IF EXISTS(SELECT * FROM sysobjects WHERE name='sp_test')
DROP PROC sp_test
GO
CREATE PROC sp_test
    @name varchar(20) OUTPUT,
    @age  int ,
    @email varchar(20),
    @address varchar(20),
    @phone varchar(20),
    @id int OUTPUT--输出的参数后加output
AS 
    INSERT INTO UserInfo VALUES(@name,@age,@email,@address,@phone)
    SET @id=@@IDENTITY--@@IDENTITY就是发生变化的行数，把他赋给变量id
GO
--存储过程的参数一定要和创建的时候一致
DECLARE @id INT
DECLARE @A varchar(20)
EXEC @A=sp_test '秦八',80,'5654@qq.com','河南省驻马店市','4156413',@id OUTPUT
PRINT @id

带返回值的存储过程

--return可以结束程序，直接返回
CREATE PROC sp_test
    @name varchar(20) OUTPUT,
    @age  int ,
    @email varchar(20),
    @address varchar(20),
    @phone varchar(20),
    @id int OUTPUT
AS 
    IF(@age>60 OR @age<18)--如果年龄不符合，返回-1
        RETURN -1
    INSERT INTO UserInfo VALUES(@name,@age,@email,@address,@phone)
    SET @id=@@IDENTITY
GO
--插入错误的信息
DECLARE @id INT
DECLARE @A varchar(20)
EXEC @A=sp_test '秦八',10,'5654@qq.com','河南省驻马店市','4156413',@id OUTPUT
IF(@A = -1)
PRINT '年龄错误'