存储过程基本了解

文章目录

介绍
存储过程示例
- 1. 目的
- 2. 输入参数
- 3. 输出参数
- 4. 执行逻辑
- 5. 返回值
- 6. 示例用法
- 7. 注意事项
存储过程的关键字有哪些
简单实操

介绍

存储过程是一组预编译的SQL语句，以及流程控制语句，封装在数据库服务器中并可以被重复调用。它们可以接收参数、执行逻辑和返回结果。存储过程通常用于实现复杂的业务逻辑和数据操作，提供了以下几个主要优势：

代码重用和模块化： 存储过程可以将复杂的业务逻辑封装成一个可重复调用的单元。这样可以提高代码的重用性和维护性，避免了重复编写相同的代码逻辑。
性能优化： 存储过程在数据库服务器上进行预编译，并可被缓存，从而提供更高的性能。通过减少网络开销和减少解析时间，存储过程可以显著提升查询和事务处理的性能。
安全性和权限控制： 存储过程可以通过授权机制实现对数据库对象的访问控制。数据库管理员可以授予或撤销用户对存储过程的执行权限，从而保护敏感数据和确保数据的安全性。
减少数据传输量： 存储过程可以在数据库服务器上执行大量的数据处理和计算，只将结果返回给客户端，减少了数据传输的量，提高了网络传输效率。
事务处理和数据一致性： 存储过程可以包含多个SQL语句，并可以在一个事务中执行。这样可以确保逻辑上相关的操作要么全部成功要么全部失败，从而保持数据的一致性。
简化客户端代码： 存储过程可以将复杂的业务逻辑移至数据库服务器端，减少了客户端的代码量，使客户端更加简洁和易于维护。

存储过程示例

1. 目的

获取特定客户的信息，包括客户姓名、电话号码和地址。

2. 输入参数

@CustomerID (INT)：客户ID，用于指定要查询的客户。

3. 输出参数

@CustomerName (VARCHAR)：客户姓名。
@PhoneNumber (VARCHAR)：客户电话号码。
@Address (VARCHAR)：客户地址。

4. 执行逻辑

CREATE PROCEDURE GetCustomerInfo
    @CustomerID INT
AS
BEGIN
    SELECT 
        CustomerName, 
        PhoneNumber, 
        Address
    FROM Customers
    WHERE CustomerID = @CustomerID;
END;

5. 返回值

无

6. 示例用法

EXEC GetCustomerInfo @CustomerID = 123;

7. 注意事项

调用该存储过程时，需传入有效的客户ID。
请注意存储过程中涉及到的表和字段名称，确保与实际数据库结构匹配。

下面是一个示例的复杂存储过程代码，该存储过程用于计算订单总金额并更新订单状态：

CREATE PROCEDURE CalculateOrderTotalAndSetStatus
    @OrderID INT
AS
BEGIN
    DECLARE @TotalAmount DECIMAL(10, 2);
    DECLARE @ItemCount INT;
    DECLARE @OrderStatus VARCHAR(20);

    -- 计算订单总金额
    SELECT @TotalAmount = SUM(UnitPrice * Quantity)
    FROM OrderDetails
    WHERE OrderID = @OrderID;

    -- 获取订单中商品数量
    SELECT @ItemCount = COUNT(*)
    FROM OrderDetails
    WHERE OrderID = @OrderID;

    -- 根据订单总金额和商品数量设置订单状态
    IF @TotalAmount > 1000
        SET @OrderStatus = 'High Value';
    ELSE
        SET @OrderStatus = 'Normal';

    -- 更新订单信息表中的订单状态和总金额
    UPDATE Orders
    SET TotalAmount = @TotalAmount,
        ItemCount = @ItemCount,
        Status = @OrderStatus
    WHERE OrderID = @OrderID;

    PRINT 'Order total amount calculated and status updated successfully.';
END;

在上面的示例中，存储过程CalculateOrderTotalAndSetStatus接收一个订单ID作为输入参数，然后执行以下操作：

计算订单的总金额；
获取订单中商品的数量；
根据总金额设置订单状态为“High Value”或“Normal”；
更新订单信息表中的总金额、商品数量和状态。

存储过程的关键字有哪些

CREATE PROCEDURE：用于创建存储过程。
ALTER PROCEDURE：用于修改现有存储过程的定义。
DROP PROCEDURE：用于删除存储过程。
EXEC 或 EXECUTE：用于执行存储过程。
WITH ENCRYPTION：用于加密存储过程的源代码，以保护存储过程的逻辑。
WITH RECOMPILE：用于指示数据库引擎在每次执行存储过程时重新编译存储过程。
AS：用于指定存储过程的主体部分。
BEGIN 和 END：用于定义存储过程的代码块。
DECLARE：用于声明变量或游标。
SET：用于给变量赋值。
SELECT：用于从表中检索数据。
UPDATE、INSERT、DELETE：用于更新、插入和删除数据。
IF、ELSEIF、ELSE：用于条件控制。
WHILE、BEGIN…END WHILE：用于循环控制。
RETURN：用于从存储过程中返回值。
OUTPUT：用于输出参数。
INOUT：用于输入输出参数。

简单实操

在存储过程中可以包含多个select语句，显示姓名中含有”张“字职工信息及其所在的仓库信息，

create procedure pro_sql5
as
begin
   select * from 职工 where 姓名 like '%张%'
   select * from 仓库 where 仓库号 in(select 仓库号 from 职工 where 姓名 like '%张%')
end

go
execute pro_sql5

带有输入参数的存储过程找出三个数字中的最大数：

create proc proc_sql6
@num1 int,
@num2 int,
@num3 int
as
begin
   declare @max int
   if @num1>@num2
      set @max = @num1
   else set @max = @num2

   if @num3 > @max
      set @max = @num3

   print '3个数中最大的数字是:' + cast(@max as varchar(20))
end

求阶乘之和如6! + 5! + 4! + 3! + 2! + 1

execute proc_sql7 6

带有输入参数的数据查询功能的存储过程

create proc proc_sql8
  @mingz int,
  @maxgz int
as
begin
   select * from 职工 where 工资>@mingz and 工资<@maxgz
end

带输入和输出参数的存储过程：显示指定仓库号的职工信息和该仓库号的最大工资和最小工资

create proc proc_sql9
  @cangkuhao varchar(50),
  @maxgz int output,
  @mingz int output
as
begin
  select * from 职工 where 仓库号=@cangkuhao
  select @maxgz=MAX(工资) from 职工 where 仓库号=@cangkuhao
  select @mingz=MIN(工资) from 职工 where 仓库号=@cangkuhao
end

带有登录判断功能的存储过程

create proc proc_sql10  
 @hyuer varchar(50),  
 @hypwd varchar(50)  
as  
begin  
  if @hyuer = 'hystu1'  
     begin  
         if @hypwd = '1111'  
            print '用户名和密码输入正确'  
         else   
            print '密码输入错误'  
     end  
  else if @hyuer = 'hystu2'  
     begin  
          if @hypwd = '2222'  
            print '用户名和密码输入正确'  
         else   
            print '密码输入错误'  
     end  
  else if @hyuer = 'hystu3'  
     begin  
           if @hypwd = '3333'  
            print '用户名和密码输入正确'  
         else   
            print '密码输入错误'  
     end  
  else   
      print '您输入的用户名不正确，请重新输入'  
end

带有判断条件的插入功能的存储过程

reate proc proc_sq111  
 @zghao varchar(30),  
 @ckhao varchar(30),  
 @sname varchar(50),  
 @sex varchar(10),  
 @gz int  
as  
begin  
  if Exists(select * from 职工 where 职工号=@zghao)  
     print '该职工已经存在，请重新输入'  
  else   
     begin  
        if Exists(select * from 仓库 where 仓库号=@ckhao)  
           begin  
              insert into 职工(职工号, 仓库号, 姓名, 性别, 工资)   
                           values(@zghao, @ckhao, @sname, @sex, @gz)  
           end  
        else  
           print '您输入的仓库号不存在，请重新输入'  
     end  
end