项目中的一个问题--返回游标结果集

几句题外话：

现在越来越觉得数据库的重要作用，程序只是实现这样或那样的业务过程，没有数据库的支撑，程序再怎么写都是鸡肋，特别是大型互联网的应用，数据库绝对是一个重要的方面。

需求背景描述：
在真实的项目中，有这样一个需求：现在有三张数据库表，一张商品信息表productInfo，一张商品定价表productPricing，一张商品整体定价表prodoctUnityPricing。这三张表的表结构如下（注：为了叙述的方便，在此省略了很多字段）：

表一：商品信息表productInfo

 

PRODUCTID PRODUCTNAME PRODUCTPRICE PRODUCEADDRESS PRODUCTTYPE

GD010010001	LG手机	1000.00	深圳XX电子	10001
GD020020002	佳能相机	2000.00	福州XX电子	10001
GD030040005	Lenovo ThinkPad	5500.00	联想中国	10002

该表存储的是商品的相关信息。

表二：商品定价表productPricing

ID USERID PRODUCTID PRODUCTTYPE PRODUCPRICING

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

 

ID USERID PRODUCTTYPE UNITYPRICING

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