几句题外话:
现在越来越觉得数据库的重要作用,程序只是实现这样或那样的业务过程,没有数据库的支撑,程序再怎么写都是鸡肋,特别是大型互联网的应用,数据库绝对是一个重要的方面。
需求背景描述:
在真实的项目中,有这样一个需求:现在有三张数据库表,一张商品信息表productInfo,一张商品定价表productPricing,一张商品整体定价表prodoctUnityPricing。这三张表的表结构如下(注:为了叙述的方便,在此省略了很多字段):
表一:商品信息表productInfo
GD010010001 | LG手机 | 1000.00 | 深圳XX电子 | 10001 |
GD020020002 | 佳能相机 | 2000.00 | 福州XX电子 | 10001 |
GD030040005 | Lenovo ThinkPad | 5500.00 | 联想中国 | 10002 |
该表存储的是商品的相关信息。
表二:商品定价表productPricing
1 | 0001 | GD010010001 | 10001 | 1000.11 |
2 | 0002 | GD010010001 | 10001 | 1577.00 |
3 | 0001 | GD020020002 | 10001 | 2000.22 |
4 | 0001 | GD030040005 | 10002 | 5520.00 |
该表存储的是商品的定价信息,即将商品信息表productInfo的商品重新修订价格之后,会将修改的价格信息存储在该表中。
表三:商品整体定价表prodoctUnityPricing
1 | 0001 | 10001 | 1.00 |
该表存储的是某类商品的整体定价,例如将商品类型productType=10001的商品整体调整价格,使价格统一上浮100元,这种整体价格调整信息将存储在该表中。
业务需求描述:
要处理的问题是从这三张表中获取商品信息,显然,如果只是仅仅查询出商品信息,从商品信息表productInfo查询就够了,但不同的用户他所看到的商品的价格是不一样的。
因此,就出现了下面的几种情况:
1.当商品没有定价(即商品定价表productPricing没有该用户对应的定价记录),并且也没有商品整体定价(即商品整体定价表prodoctUnityPricing没有该用户对应的定价记录),则直接查询商品信息表productInfo中的数据;
2.当商品有定价,但商品没有整体定价,则查询商品信息表productInfo表的数据,但价格是商品定价表productPricing对应的价格,其中当部分商品有定价,部分商品没有定价,则有定价的显示定价价格,无定价的显示商品信息表中的价格;
3当商品有整体定价,但商品没有商品定价,则要将商品信息表的价格都加上整体定价上浮的钱数之后才是最终显示给用户的价格;
4.当商品有定价,并且商品也有整体定价,则优先显示定价的价格。
业务过程如上,该功能也已经上线很久了,但线上的版本使用的是sql查询,sql语句也比较复杂,最近做了一个使用存储过程的版本。
给大家参考的同时也盼望大家看看有没有什么问题呢?
下面是存储过程代码,使用package和package bodies结构:
create or replace package package_productprice is -- Author : hnylj type resultList is ref cursor; procedure processProductPrice(p_userid in varchar2, p_productType in varchar2, p_pageIndex in number, p_pageEnd in number, productList out resultList); end package_productprice;
create or replace package body package_productprice is procedure processProductPrice(p_userid in varchar2, p_productType in varchar2, p_pageIndex in number, p_pageEnd in number, productList out resultList) is --商品整体定价(上浮的钱数) v_productUnityPrice number(8, 2) := 0.00; --判断是否有整体定价 v_count number(1) := 0; begin --查询是否有商品的整体定价 select count(*) into v_count from PRODOCTUNITYPRICING a where a.userid = p_userid and a.producttype = p_productType; --如果不存在整体定价 if v_count = 0 then --查询商品表和商品定价表 open productList for SELECT * FROM (SELECT AA.*, ROWNUM RN FROM (select t.*, p.productPricing from (select a.productid productId, a.productname productName, decode(a.productprice, null, 0.00, a.productprice) productPrice, a.producttype productType from productinfo a where a.producttype = p_productType) t, (select b.productid productId, decode(b.PRODUCPRICING, null, 0.00, b.PRODUCPRICING) productPricing from productpricing b where b.producttype = p_productType and b.userid = p_userid) p where t.productId = p.productId(+) order by t.productPrice) AA WHERE ROWNUM <= p_pageEnd) WHERE RN >= p_pageIndex; end if; --如果存在整体定价 if v_count > 0 then --查询出整体定价上浮的钱数存入v_productUnityPrice变量 select decode(a.unitypricing, null, 0, a.unitypricing) into v_productUnityPrice from PRODOCTUNITYPRICING a where a.userid = p_userid and a.producttype = p_productType; --查询商品表和商品定价表 open productList for SELECT * FROM (SELECT AA.*, ROWNUM RN FROM (select t.*, decode(p.productPricing, null, t.productPrice+v_productUnityPrice, p.productPricing) productPricing from (select a.productid productId, a.productname productName, decode(a.productprice, null, 0.00, a.productprice) productPrice, a.producttype productType from productinfo a where a.producttype = p_productType) t, (select b.productid productId, decode(b.PRODUCPRICING, null, 0.00, b.PRODUCPRICING) productPricing from productpricing b where b.producttype = p_productType and b.userid = p_userid) p where t.productId = p.productId(+) order by t.productPrice) AA WHERE ROWNUM <= p_pageEnd) WHERE RN >= p_pageIndex; --循环游标开始 end if; end processProductPrice; end package_productprice;
存储过程代码如上,使用的动态游标ref cursor。
下面是一段java调用该存储过程的测试代码:
package com.javaeye.hnylj.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.javaeye.hnylj.model.ProductInfo; /** * 测试存储过程 * * @since Jun 20, 2010 */ public class ProceduresTest { private Connection conn = null; private CallableStatement cstmt = null; private ResultSet rs = null; private static final String DRIVER = "oracle.jdbc.driver.OracleDriver"; private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:workdb"; private static final String USERNAME = "framework"; private static final String PASSWORD = "framework"; private List<ProductInfo> list; /** * 数据库连接 * * @return Connection */ public synchronized Connection getConnection() { try { Class.forName(DRIVER); conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (ClassNotFoundException e) { e.printStackTrace(); return null; } catch (SQLException e) { e.printStackTrace(); return null; } return conn; } /** * 调用存储过程得到游标数据集 * * @return */ public List<ProductInfo> queryList() { list = new ArrayList<ProductInfo>(); try { if (this.getConnection() != null) { conn = this.getConnection(); cstmt = conn.prepareCall("{call package_productprice.processProductPrice(?,?,?,?,?)}"); cstmt.setString(1, "0001"); cstmt.setString(2, "10001"); cstmt.setInt(3, 1); cstmt.setInt(4, 10); cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.CURSOR); cstmt.execute(); rs = (ResultSet)cstmt.getObject(5); while (rs.next()) { ProductInfo productInfo = new ProductInfo(); productInfo.setProductId(rs.getString(1)); productInfo.setProductName(rs.getString(2)); productInfo.setProductPrice(rs.getDouble(3)); productInfo.setProductType(rs.getString(4)); productInfo.setProductPricing(rs.getDouble(5)); list.add(productInfo); } } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (null != rs) { rs.close(); } if (null != cstmt) { cstmt.close(); } if (null != conn) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } return list; } /** * main方法测试 * * @param args * @throws Exception */ public static void main(String[] args) throws Exception { ProceduresTest test = new ProceduresTest(); List<ProductInfo> productList = test.queryList(); for (ProductInfo productInfo : productList) { System.out.println(productInfo.getProductId()); System.out.println(productInfo.getProductName()); System.out.println(productInfo.getProductPrice()); System.out.println(productInfo.getProductPricing()); System.out.println(productInfo.getProductType()); } } }
另外还需要一个model类ProductInfo,该类的代码只要productId、productName、productPrice、productPricing、productType及相应的getter和setter方法。
一个Java技术交流群,一起交流,共同进步,扣扣群号:513086638
相关推荐
打开一个记录集,标识号为5,后面操作这个记录集,也要提供这个标识号 -------------------------------------------------------- 关闭记录集 void closeRecordset(int idx = -1); 例: db.closeRecordset(5); 关闭...
10. 下列哪个子句实现对一个结果集进行分组和汇总?( D ) A.HAVING B. ORDER BY C. WHERE D. GROUP BY 11. 查询一个表的总记录数,可以采用_________统计函数。( C ) A. AVG(*) B. SUM(*) C. COUNT(*) D.MAX...
一个“.java”原文件中是否可以包括多个类(不是内部类)? 53.掌握内部类和接口的概念 54.StringTokenizer类的使用 55.数据结构,如何遍历List中的元素? 如果要按照键值保存或者访问数据,使用什么数据结构? ...
“建议框”是一个示例应用程序,涵盖了两种不同的方法来对Google App Engine数据存储区查询中的结果集进行分页。 第一种方法(“唯一”)为提交的每个建议手动创建唯一值,并在分页时使用该值进行查询。 第二种...
类似的,遍历一个长结果集是用位置作为游标,而不是用LIMIT/OFFSET,所有页的数据均被索引,访问一样快。基于进入系统的时间,写操作具有幂等性(不管操作多少次结果都不变的性质,比如取绝对值的函数就具有幂等性)和...
Sqlite 一款轻型的数据库,是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C库中 W3C 万维网联盟,创建于1994年,是Web技术领域最具权威和影响力的国际中立性技术标准机构。主要的工作是发展 Web 规范,...
模块9 Java数据库编程 《Java程序设计案例教程》教学课件09Java数据库编程全文共26页,当前为第1页。 学习目标 01 掌握在Java应用程序中用...游动查询 (1)resultSetType的取值决定结果集的游标能否上下移动。取值如
游标是对查询出来的结果集作为一个单元来有效的处理,可以对结果集的当前行做修改。 select执行顺序? 答:from where group by having select order by Collection和Collections的区别? 答:Collection是集合...
5.9.1 从函数返回一个值 5.10 实现递归 5.10.1 名称空间 5.11 进一步学习 5.12 下一章 第6章 面向对象的PHP 6.1 理解面向对象的概念 6.1.1 类和对象 6.1.2 多态性 6.1.3 继承 6.2 在PHP中创建类、属性和...
注重实战,通过实际中的案例为读者讲解使用MongoDB时遇到的各种问题,并给出了解决方案。本书旨在帮助云计算初学者迅速掌握MongoDB数据库,提升读者在云计算实践中的应用和开发能力。同时本书极强的系统性和大量翔实...
5.9.1 从函数返回一个值 5.10 实现递归 5.10.1 名称空间 5.11 进一步学习 5.12 下一章 第6章 面向对象的PHP 6.1 理解面向对象的概念 6.1.1 类和对象 6.1.2 多态性 6.1.3 继承 6.2 在PHP中创建类、属性和...
注重实战,通过实际中的案例为读者讲解使用MongoDB时遇到的各种问题,并给出了解决方案。本书旨在帮助云计算初学者迅速掌握MongoDB数据库,提升读者在云计算实践中的应用和开发能力。同时本书极强的系统性和大量翔实...
5.9.1 从函数返回一个值 5.10 实现递归 5.10.1 名称空间 5.11 进一步学习 5.12 下一章 第6章 面向对象的PHP 6.1 理解面向对象的概念 6.1.1 类和对象 6.1.2 多态性 6.1.3 继承 6.2 在PHP中创建类、属性和操作 6.2.1...
21、当一个对象被当作参数传递到一个方法后,此方法可改变这个对象的属性,并可返回变化后的结果,那么这里到底是值传递还是引用传递? 10 22、我们在web 应用开发过程中经常遇到输出某种编码的字符,如iso8859-1等,...
21、当一个对象被当作参数传递到一个方法后,此方法可改变这个对象的属性,并可返回变化后的结果,那么这里到底是值传递还是引用传递? 10 22、我们在web 应用开发过程中经常遇到输出某种编码的字符,如iso8859-1等,...
2.4 要包含在项目中的 C 文件..............................................................................35 2.5 emWin 的配置 ..............................................................................