SQL存储过程使用介绍

在数据库编程过程中经常会用到存储过程 , 相比 SQL 语句 , 存储过程更方便 , 快速 , 安全 ; 先将存储过程的相关介绍和使用方法总结如下 

1. 存储过程的概念
存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次编译 , 
用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执行它 , 存储过程是数据库中的一个重要对象 ; 
存储过程中可以包含 逻辑控制语句 和 数据操纵语句 , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 ;

2. 存储过程的优缺点
优点 :
由于应用程序随着时间推移会不断更改 , 增删功能 , SQL 语句会变得更复杂 , 存储过程为封装此类代码提供了一个替换位置 ;
由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中 , 所以存储过程运行要比单个的 SQL 语句块要快 ;
由于在调用时只需用提供存储过程名和必要的参数信息 , 所以在一定程度上也可以减少网络流量 , 简单网络负担 ;
可维护性高 , 更新存储过程通常比更改 , 测试以及重新部署程序集需要较少的时间和精力 ;
代码精简一致 , 一个存储过程可以用于应用程序代码的不同位置 ;
增强安全性 : 
通过向用户授予对存储过程 (而不是基于表) 的访问权限 , 它们可以提供对特定数据的访问 ;
提高代码安全 , 防止 SQL注入 (但未彻底解决 , 例如将数据操作语言 DML 附加到输入参数) ;
SQLParameter 类指定存储过程参数的数据类型 , 作为深层次防御性策略的一部分 , 可以验证用户提供的值类型 (但也不是万无一失 , 还是应该传递至数据库前得到附加验证) ;
缺点 :
如果更改范围大到需要对输入存储过程的参数进行更改 , 或者要更改由其返回的数据 , 则仍需要更新程序集中的代码以添加参数 , 等等 ;
可移植性差 , 由于存储过程将应用程序绑定到 Server , 因此使用存储过程封装业务逻辑将限制应用程序的可移植性 ; 如果应用程序的可移植性在您的环境中非常重要 , 则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择 ;

3. 编写简单存储过程

在这个示例中，我们将在db2中创建一个名为demo2018的存储过程。
该存储过程的输入参数in_name和in_uuid，表示用户的姓名和身份证号。
该存储过程的作用是根据身份证号来新建用户或者更改用户信息，代码如下：

create or replace procedure demo2018 (
  in in_name varchar(50),
  in in_uuid varchar(18)
)
begin
  declare v_result varchar(50) default null;
  select name into v_result from t_tusers where uuid=in_uuid;
  if (v_result is not null) then
    update t_tusers set name=in_name where uuid=in_uuid;
  else
    insert into t_tusers values(in_name,in_uuid);
  end if;
end

-- 创建完该存储过程后，我们来新建一个USERS表并调用存储过程：

drop table t_tusers;
create table t_tusers (
  name varchar(50),
  uuid varchar(18)
);
insert into t_tusers values ('张三','123456789123456789');
insert into t_tusers values ('李四','123456789987654321');

-- 调用存储过程
call demo2018('王五','123456789987654321');

我们新建了两个用户，并调用了存储过程demo2018，此时我们执行SELECT操作：

select * from t_tusers;

-- 结果如下：

name                                               uuid         
-------------------------------------------------- ------------------ 
张三                                             123456789123456789 
王五                                             123456789987654321 

根据 ID 获取货品的价格 , 并根据参数判断是否折扣 :

该存储过程传入三个参数 , 货品 ID , 是否折扣以及返回的价格 , 在存储过程内部 , 定义两个局部变量 tmpPrice 和 prodDiscRate , 把查询出来的结果赋给临时变量 , 再判断是否折扣 , 最后把局部变量的值赋给输出参数 

create procedure GetPriceByID(
    in prodID int,
    in isDisc boolean,
    out prodPrice decimal(8,2)
) 
begin
    declare tmpPrice decimal(8,2);
    declare prodDiscRate decimal(8,2);
    set prodDiscRate = 0.88;

    select price from products
    where id = prodID
    into tmpPrice;

    if isDisc then
        select tmpPrice*prodDiscRate into tmpPrice;
    end if;

    select tmpPrice into prodPrice;
end;