数据库的创建与管理存储过程

JY数据库创建与管理存储过程

USE JY
GO

1.说明：

--存储过程优点
/*
1.提高系统运行速度(存储过程只在创建时编译，以后每次执行不用重新编译)
2.提高系统开发速度(存储过程通过封装复杂的数据库操作简化开发过程)
3.增强系统可维护性(存储过程可以实现模块化的程序设计，提供统一的数据库访问接口，提高可维护性)
4.提高系统安全性(用户不能直接操作存储过程中引用的对象(有权限))
5.降低网络流量(也就是降低网络通信量)
  (存储过程直接存储在数据库中，在客户端和服务器通信时不会产生大量代码流量)
*/

--自定义存储过程
/*
1.在存储过程名称前加##表示创建一个全局临时存储过程
2.在存储过程名称前加#表示创建一个局部临时存储过程
3.用户自定义存储过程在tempdb数据库中
*/

--扩展存储过程
/*
1.EXEC xp_logininfo 返回账户的信息(包括类型、特权级别、登录名等)
2.EXEC xp_loginconfig 返回SQL在Windows上运行时的登录安全配置
*/

2.操作

• 创建和执行不带参数的存储过程

1. 语法格式
   –创建和执行不带参数的存储过程
   CREATE PROCEDURE procedure_name
   [WITH ENCRYPTION] –对存储过程进行加密
   [WITH RECOMPILE] –对存储过程重新编译
   AS
      sql_statement

2. 执行存储过程语句
   EXEC | EXECUTE procedure_name

--示例：
CREATE PROCEDURE p_book
AS
    SELECT *
    FROM book
    WHERE book_publisher = '人民邮电出版社'
GO

EXEC p_book  --执行存储过程，查看返回结果
GO

/*
--存储过程和视图的比较
1.存储过程可以包括几乎所有的T-SQL语句，而视图只能包括SELECT语句
2.存储过程可以接受参数并向调用它的过程返回多个值或者状态值或者结果集.
  视图不能接受参数，只能返回结果集
*/

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

–2.语法格式
–创建带输入参数的存储过程
CREATE PROCEDURE procedure_name
@parameter_name –存储过程中的参数
data_type[ = default] –指定参数的数据类型(default参数的默认值)
[WITH

--示例
CREATE PROCEDURE p_newbook
@name nvarchar(60)
AS
    SELECT *
    FROM book
    WHERE book_publisher = @name
GO

EXEC p_newbook '人民邮电出版社'
GO
EXEC p_newbook @name = '清华大学出版社'
GO

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

  –3.语法格式
  –创建带输出参数的存储过程
  CREATE PROCEDURE procedure_name
  @parameter_name data_type[ = default ] OUTPUT
  [WITH

--示例
/*有输入和输出参数时，要先定义这两个局部变量，并为输入参数赋值*/
CREATE PROCEDURE p_book2
@name nvarchar(20), @book_times smallint OUTPUT
AS
    SET @book_times = (
        SELECT interview_times
        FROM book
        WHERE book_name = @name
    )
    PRINT @book_times
GO

DECLARE @name nvarchar(20), @book_times smallint
SET @name = '九阳神功'
EXEC p_book2 @name, @book_times OUTPUT
SELECT @book_times
GO

• 管理存储过程

--1>查看存储过程信息
EXEC sp_helptext procedure_name

--2>修改存储过程语法结构
ALTER PROCEDURE procedure_name
[WITH <ENCRYPTION | RECOMPILE>]
AS
    sql_statement

--3>重命名与删除存储过程
EXEC sp_rename procedure_name  --重命名

DROP PROCEDURE procedure_name  --删除