当前位置： 首页 > news >正文

数据库优化提速(一)之进销存库存管理—仙盟创梦IDE

news 2025/8/21 10:23:51

从存储过程到通用 SQL：进销存系统的数据操作优化

在进销存系统的开发与维护中，数据库查询语句的编写方式对系统的性能、兼容性和可维护性有着深远影响。本文将围绕给定的三段 SQL 代码展开，深入探讨将存储过程转换为通用 SQL 在进销存场景下的诸多好处，同时对字段进行中文转换以满足发表和隐私需求。

原始存储过程剖析

原始的 SQL 存储过程代码如下：

sql

$sql = "exec sp_executesql N'SELECT 库存ID, 库存编码, RTRIM(库存名称) AS 库存名称,  -- 去除中文字段尾部空格RTRIM(库存类别) AS 库存类别,RTRIM(库存款式) AS 库存款式,RTRIM(库存规格) AS 库存规格,ISNULL(门店成本, 库存成本) AS 库存成本, 期初数量, 增加数量, 减少数量, (期初数量 + 增加数量 - 减少数量) AS 总数量 FROM (SELECT 库存主表.库存ID AS 库存ID, 库存主表.库存编码 AS 库存编码, 库存主表.库存名称 AS 库存名称, 库存主表.库存类别 AS 库存类别, 库存主表.库存款式 AS 库存款式, 库存主表.库存规格 AS 库存规格, 库存主表.库存成本 AS 库存成本, (SELECT 库存成本 FROM 库存门店成本 WHERE 库存门店成本.库存ID = 库存主表.库存ID AND 库存门店成本.分店 = @仓库) AS 门店成本, SUM(CASE WHEN (库存凭证.出入库 = 1 OR 库存凭证.出入库 = -1) AND 库存凭证.生效日期 < @开始日期 THEN 库存凭证明细.数量 * 库存凭证.增减标识 ELSE 0 END) AS 期初数量, SUM(CASE WHEN 库存凭证.出入库 = 1 AND 库存凭证.生效日期 >= @开始日期 AND 库存凭证.生效日期 <= @结束日期 THEN 库存凭证明细.数量 * 库存凭证.增减标识 ELSE 0 END) AS 增加数量, SUM(CASE WHEN 库存凭证.出入库 = -1 AND 库存凭证.生效日期 >= @开始日期 AND 库存凭证.生效日期 <= @结束日期 THEN 库存凭证明细.数量 * 库存凭证.增减标识 * -1 ELSE 0 END) AS 减少数量 FROM 库存凭证, 库存凭证明细, 库存主表 WHERE 库存凭证.凭证ID = 库存凭证明细.主表ID AND 库存凭证明细.库存ID = 库存主表.库存ID AND 库存凭证.状态 = @状态 AND 库存凭证.仓库 LIKE @仓库 GROUP BY 库存主表.库存ID, 库存主表.库存编码, 库存主表.库存名称, 库存主表.库存类别, 库存主表.库存款式, 库存主表.库存规格, 库存主表.库存成本) AS 库存数据 ORDER BY 库存编码',N'@状态 int, @开始日期 datetime, @结束日期 datetime, @仓库 nvarchar(4)',@状态 =?, @开始日期 =?, @结束日期 =?, @仓库 =? ";

此存储过程通过 sp_executesql 执行动态 SQL，用于从进销存相关的多张表（库存主表、库存门店成本表、库存凭证表、库存凭证明细表）中获取特定时间段和仓库的库存数据，包括库存 ID、编码、名称、类别、款式、规格、成本以及不同时间段的数量变化情况。通过参数化查询，使得该存储过程在不同条件下具有一定的灵活性。

转换为通用 SQL 的过程及优势

  1. 消除特定数据库依赖，提升兼容性：许多数据库系统虽然支持存储过程，但语法和特性存在差异。将存储过程转换为通用 SQL，可以避免依赖特定数据库的存储过程执行机制，如 sp_executesql 是 SQL Server 特定的语法。转换后的通用 SQL 可以在更多类型的数据库系统中运行，无需针对不同数据库进行语法调整，大大提高了系统的兼容性和可移植性。

sql

// 构建带参数的SQL语句
$sql = " SELECT 库存ID, 库存编码, RTRIM(库存名称) AS 库存名称,RTRIM(库存类别) AS 库存类别,RTRIM(库存款式) AS 库存款式,RTRIM(库存规格) AS 库存规格,ISNULL(门店成本, 库存成本) AS 库存成本, 期初数量, 增加数量, 减少数量, (期初数量 + 增加数量 - 减少数量) AS 总数量 FROM (SELECT 库存主表.库存ID AS 库存ID, 库存主表.库存编码 AS 库存编码, 库存主表.库存名称 AS 库存名称, 库存主表.库存类别 AS 库存类别, 库存主表.库存款式 AS 库存款式, 库存主表.库存规格 AS 库存规格, 库存主表.库存成本 AS 库存成本, (SELECT 库存成本 FROM 库存门店成本 WHERE 库存门店成本.库存ID = 库存主表.库存ID AND 库存门店成本.分店 = @仓库) AS 门店成本, SUM(CASE WHEN (库存凭证.出入库 = 1 OR 库存凭证.出入库 = -1) AND 库存凭证.生效日期 < @开始日期 THEN 库存凭证明细.数量 * 库存凭证.增减标识 ELSE 0 END) AS 期初数量, SUM(CASE WHEN 库存凭证.出入库 = 1 AND 库存凭证.生效日期 >= @开始日期 AND 库存凭证.生效日期 <= @结束日期 THEN 库存凭证明细.数量 * 库存凭证.增减标识 ELSE 0 END) AS 增加数量, SUM(CASE WHEN 库存凭证.出入库 = -1 AND 库存凭证.生效日期 >= @开始日期 AND 库存凭证.生效日期 <= @结束日期 THEN 库存凭证明细.数量 * 库存凭证.增减标识 * -1 ELSE 0 END) AS 减少数量 FROM 库存凭证, 库存凭证明细, 库存主表 WHERE 库存凭证.凭证ID = 库存凭证明细.主表ID AND 库存凭证明细.库存ID = 库存主表.库存ID AND 库存凭证.状态 = @状态 AND 库存凭证.仓库 LIKE @仓库 " . // 添加商品ID筛选条件（如果提供了商品ID）(!empty($商品ID)? " AND 库存主表.库存ID = @商品ID " : "") . "GROUP BY 库存主表.库存ID, 库存主表.库存编码, 库存主表.库存名称, 库存主表.库存类别, 库存主表.库存款式, 库存主表.库存规格, 库存主表.库存成本) AS 库存数据 ORDER BY 库存编码',N'@状态 int, @开始日期 datetime, @结束日期 datetime, @仓库 nvarchar(4)" . (!empty($商品ID)? ", @商品ID int" : "") . "',@状态 = " . $状态 . ", @开始日期 = '" . $开始日期 . "', @结束日期 = '" . $结束日期 . "', @仓库 = N'" . $分店 . "'" . // 商品ID参数（如果提供）(!empty($商品ID)? ", @商品ID = " . $商品ID : "");

  1. 简化维护难度，提高代码可读性：通用 SQL 以更直观的方式展示数据查询逻辑，对于不熟悉存储过程复杂语法和结构的开发人员来说，更容易理解和维护。在上述转换后的代码中，SQL 语句的结构和逻辑一目了然，直接从多张表中获取数据并进行计算和筛选，开发人员可以快速定位和修改相关逻辑，减少维护成本。
  2. 便于代码审查和优化：通用 SQL 便于进行代码审查，因为其语法和结构相对统一。审查人员可以更清晰地分析查询性能，发现潜在的问题，如是否存在冗余连接、不合理的条件判断等。通过优化通用 SQL，可以提高查询效率，进而提升整个进销存系统的性能。

sql

// 基础查询SQL
$sql = "SELECT 库存ID, 库存编码, RTRIM(库存名称) AS 库存名称,RTRIM(库存类别) AS 库存类别,RTRIM(库存款式) AS 库存款式,RTRIM(库存规格) AS 库存规格,ISNULL(门店成本, 库存成本) AS 库存成本, 期初数量, 增加数量, 减少数量, (期初数量 + 增加数量 - 减少数量) AS 总数量 FROM (SELECT 库存主表.库存ID AS 库存ID, 库存主表.库存编码 AS 库存编码, 库存主表.库存名称 AS 库存名称, 库存主表.库存类别 AS 库存类别, 库存主表.库存款式 AS 库存款式, 库存主表.库存规格 AS 库存规格, 库存主表.库存成本 AS 库存成本, (SELECT 库存成本 FROM 库存门店成本 WHERE 库存门店成本.库存ID = 库存主表.库存ID AND 库存门店成本.分店 = '" . $分店 . "') AS 门店成本, SUM(CASE WHEN (库存凭证.出入库 = 1 OR 库存凭证.出入库 = -1) AND 库存凭证.生效日期 < '" . $开始日期 . "' THEN 库存凭证明细.数量 * 库存凭证.增减标识 ELSE 0 END) AS 期初数量, SUM(CASE WHEN 库存凭证.出入库 = 1 AND 库存凭证.生效日期 >= '" . $开始日期 . "' AND 库存凭证.生效日期 <= '" . $结束日期 . "' THEN 库存凭证明细.数量 * 库存凭证.增减标识 ELSE 0 END) AS 增加数量, SUM(CASE WHEN 库存凭证.出入库 = -1 AND 库存凭证.生效日期 >= '" . $开始日期 . "' AND 库存凭证.生效日期 <= '" . $结束日期 . "' THEN 库存凭证明细.数量 * 库存凭证.增减标识 * -1 ELSE 0 END) AS 减少数量 FROM 库存凭证, 库存凭证明细, 库存主表 WHERE 库存凭证.凭证ID = 库存凭证明细.主表ID AND 库存凭证明细.库存ID = 库存主表.库存ID AND 库存凭证.状态 = " . $状态 . " AND 库存凭证.仓库 LIKE '" . $分店 . "' ";// 添加商品ID筛选条件（如果提供了商品ID）
if (!empty($商品ID)) {$sql .= " AND 库存主表.库存ID = '" .$商品ID . "' ";
}// 完成SQL语句
$sql .= " GROUP BY 库存主表.库存ID, 库存主表.库存编码, 库存主表.库存名称, 库存主表.库存类别, 库存主表.库存款式, 库存主表.库存规格, 库存主表.库存成本) AS 库存数据 ORDER BY 库存编码";

结论

在进销存系统中，将存储过程转换为通用 SQL 具有显著的好处，不仅可以提升系统的兼容性，降低数据库迁移成本，还能简化代码维护难度，提高查询性能。开发人员在实际项目中应根据具体需求和场景，权衡存储过程和通用 SQL 的使用，以实现更高效、稳定的进销存系统。

阿雪技术观

在科技发展浪潮中，我们不妨积极投身技术共享。不满足于做受益者，更要主动担当贡献者。无论是分享代码、撰写技术博客，还是参与开源项目维护改进，每一个微小举动都可能蕴含推动技术进步的巨大能量。东方仙盟是汇聚力量的天地，我们携手在此探索硅基生命，为科技进步添砖加瓦。

Hey folks, in this wild tech - driven world, why not dive headfirst into the whole tech - sharing scene? Don't just be the one reaping all the benefits; step up and be a contributor too. Whether you're tossing out your code snippets, hammering out some tech blogs, or getting your hands dirty with maintaining and sprucing up open - source projects, every little thing you do might just end up being a massive force that pushes tech forward. And guess what? The Eastern FairyAlliance is this awesome place where we all come together. We're gonna team up and explore the whole silicon - based life thing, and in the process, we'll be fueling the growth of technology.

http://www.lryc.cn/news/626734.html

相关文章：

  • 开源模型应用落地-安全合规篇-深度合成隐式标识的技术实现（五）
  • 无人机感知系统详解
  • Tomcat 性能优化终极指南
  • C++ std::sort的应用总结
  • Vue2封装Axios
  • Google Chrome v139.0.7258.139 便携增强版
  • 嵌入式音频开发（3）- AudioService核心功能
  • 嵌入式开发学习———Linux环境下网络编程学习（四）
  • 04-认证授权服务开发指南
  • 读《精益数据分析》：规模化（Scale）—— 复制成功，进军新市场
  • Kafka如何保证消费确认与顺序消费？
  • Python爬虫实战：研究dark-fantasy，构建奇幻文学数据采集分析系统
  • GitHub宕机生存指南：从应急协作到高可用架构设计
  • BM25 vs TF-IDF：经典文本检索方法的对比
  • 《算法导论》第 34 章 - NP 完全性
  • RK Android14 新建分区恢复出厂设置分区数据不擦除及开机动画自定义(二)
  • 细说数仓中不同类型的维度
  • 哈希：字母异位词分组
  • Linux系统：C语言进程间通信信号(Signal)
  • 动态规划----6.单词拆分
  • Java 大视界 -- Java 大数据在智能医疗远程会诊数据管理与协同诊断优化中的应用（402）
  • C++---向下取整（＞＞）与向零取整（/）
  • WPF Alert弹框控件 - 完全使用指南
  • 【力扣 买卖股票的最佳时机 Java/Python】
  • 【Unity3D优化】平衡 Hide 与 Destroy：基于性能等级与 LRU 的 UI 管理策略与实践思考
  • 大数据毕业设计选题推荐-基于Hadoop的电信客服数据处理与分析系统-Spark-HDFS-Pandas
  • 计算机网络模型
  • 华为数通认证学习
  • CSS 定位的核心属性：position
  • SPSS数据文件的建立与管理