程序博客网 > php mysql 连接池

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

来源：互联网 发布：php mysql 连接池 编辑：程序博客网 时间：2024/05/21 19:38
 

几句题外话：

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

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

表一：商品信息表productInfo

 

PRODUCTIDPRODUCTNAMEPRODUCTPRICEPRODUCEADDRESSPRODUCTTYPEGD010010001LG手机1000.00深圳XX电子10001GD020020002佳能相机2000.00福州XX电子10001GD030040005Lenovo ThinkPad5500.00联想中国10002

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

表二：商品定价表productPricing

IDUSERIDPRODUCTIDPRODUCTTYPEPRODUCPRICING10001GD010010001100011000.1120002GD010010001100011577.0030001GD020020002100012000.2240001GD030040005100025520.00

该表存储的是商品的定价信息，即将商品信息表productInfo的商品重新修订价格之后，会将修改的价格信息存储在该表中。

表三：商品整体定价表prodoctUnityPricing

 

IDUSERIDPRODUCTTYPEUNITYPRICING10001100011.00

该表存储的是某类商品的整体定价，例如将商品类型productType=10001的商品整体调整价格，使价格统一上浮100元，这种整体价格调整信息将存储在该表中。

业务需求描述：

要处理的问题是从这三张表中获取商品信息，显然，如果只是仅仅查询出商品信息，从商品信息表productInfo查询就够了，但不同的用户他所看到的商品的价格是不一样的。

因此，就出现了下面的几种情况：

1.当商品没有定价（即商品定价表productPricing没有该用户对应的定价记录），并且也没有商品整体定价（即商品整体定价表prodoctUnityPricing没有该用户对应的定价记录），则直接查询商品信息表productInfo中的数据；

2.当商品有定价，但商品没有整体定价，则查询商品信息表productInfo表的数据，但价格是商品定价表productPricing对应的价格，其中当部分商品有定价，部分商品没有定价，则有定价的显示定价价格，无定价的显示商品信息表中的价格；

3当商品有整体定价，但商品没有商品定价，则要将商品信息表的价格都加上整体定价上浮的钱数之后才是最终显示给用户的价格；

4.当商品有定价，并且商品也有整体定价，则优先显示定价的价格。

业务过程如上，该功能也已经上线很久了，但线上的版本使用的是sql查询，sql语句也比较复杂，最近做了一个使用存储过程的版本。

给大家参考的同时也盼望大家看看有没有什么问题呢？

下面是存储过程代码，使用package和package bodies结构：

Sql代码
  1. create or replace package package_productprice is  
  2.   
  3.   -- Author  : hnylj   
  4.   
  5.   type resultList is ref cursor;   
  6.   
  7.   procedure processProductPrice(p_userid      in varchar2,   
  8.                                 p_productType in varchar2,   
  9.                                 p_pageIndex   in number,   
  10.                                 p_pageEnd     in number,   
  11.                                 productList   out resultList);   
  12.   
  13. end package_productprice;  
Sql代码
  1. create or replace package body package_productprice is  
  2.   procedure processProductPrice(p_userid      in varchar2,   
  3.                                 p_productType in varchar2,   
  4.                                 p_pageIndex   in number,   
  5.                                 p_pageEnd     in number,   
  6.                                 productList   out resultList) is  
  7.        
  8.     --商品整体定价(上浮的钱数)   
  9.     v_productUnityPrice number(8, 2) := 0.00;   
  10.     --判断是否有整体定价   
  11.     v_count number(1) := 0;   
  12.      
  13.   begin  
  14.     --查询是否有商品的整体定价   
  15.     select count(*)   
  16.       into v_count   
  17.       from PRODOCTUNITYPRICING a   
  18.      where a.userid = p_userid   
  19.        and a.producttype = p_productType;   
  20.      
  21.     --如果不存在整体定价   
  22.     if v_count = 0 then  
  23.       --查询商品表和商品定价表   
  24.       open productList for  
  25.         SELECT *   
  26.           FROM (SELECT AA.*, ROWNUM RN   
  27.                   FROM (select t.*, p.productPricing   
  28.                           from (select a.productid productId,   
  29.                                        a.productname productName,   
  30.                                        decode(a.productprice,   
  31.                                               null,   
  32.                                               0.00,   
  33.                                               a.productprice) productPrice,   
  34.                                        a.producttype productType   
  35.                                   from productinfo a   
  36.                                  where a.producttype = p_productType) t,   
  37.                                (select b.productid productId,   
  38.                                        decode(b.PRODUCPRICING,   
  39.                                               null,   
  40.                                               0.00,   
  41.                                               b.PRODUCPRICING) productPricing   
  42.                                   from productpricing b   
  43.                                  where b.producttype = p_productType   
  44.                                    and b.userid = p_userid) p   
  45.                          where t.productId = p.productId(+)   
  46.                          order by t.productPrice) AA   
  47.                  WHERE ROWNUM <= p_pageEnd)   
  48.          WHERE RN >= p_pageIndex;   
  49.     end if;   
  50.        
  51.     --如果存在整体定价   
  52.     if v_count > 0 then  
  53.       --查询出整体定价上浮的钱数存入v_productUnityPrice变量  
  54.       select decode(a.unitypricing, null, 0, a.unitypricing)   
  55.         into v_productUnityPrice   
  56.         from PRODOCTUNITYPRICING a   
  57.        where a.userid = p_userid   
  58.          and a.producttype = p_productType;   
  59.          
  60.       --查询商品表和商品定价表   
  61.       open productList for  
  62.         SELECT *   
  63.           FROM (SELECT AA.*, ROWNUM RN   
  64.                   FROM (select t.*, decode(p.productPricing, null, t.productPrice+v_productUnityPrice, p.productPricing) productPricing   
  65.                           from (select a.productid productId,   
  66.                                        a.productname productName,   
  67.                                        decode(a.productprice,   
  68.                                               null,   
  69.                                               0.00,   
  70.                                               a.productprice) productPrice,   
  71.                                        a.producttype productType   
  72.                                   from productinfo a   
  73.                                  where a.producttype = p_productType) t,   
  74.                                (select b.productid productId,   
  75.                                        decode(b.PRODUCPRICING,   
  76.                                               null,   
  77.                                               0.00,   
  78.                                               b.PRODUCPRICING) productPricing   
  79.                                   from productpricing b   
  80.                                  where b.producttype = p_productType   
  81.                                    and b.userid = p_userid) p   
  82.                          where t.productId = p.productId(+)   
  83.                          order by t.productPrice) AA   
  84.                  WHERE ROWNUM <= p_pageEnd)   
  85.          WHERE RN >= p_pageIndex;   
  86.       --循环游标开始   
  87.     end if;   
  88.   end processProductPrice;   
  89. end package_productprice;  

存储过程代码如上，使用的动态游标ref cursor。

 

下面是一段java调用该存储过程的测试代码：

Java代码
  1. package com.javaeye.hnylj.test;   
  2.   
  3. import java.sql.CallableStatement;   
  4. import java.sql.Connection;   
  5. import java.sql.DriverManager;   
  6. import java.sql.ResultSet;   
  7. import java.sql.SQLException;   
  8. import java.util.ArrayList;   
  9. import java.util.List;   
  10.   
  11. import com.javaeye.hnylj.model.ProductInfo;   
  12.   
  13. /**  
  14.  * 测试存储过程  
  15.  *   
  16.  * @since Jun 20, 2010  
  17.  */  
  18. public class ProceduresTest {   
  19.   
  20.     private Connection conn = null;   
  21.     private CallableStatement cstmt = null;   
  22.     private ResultSet rs = null;   
  23.     private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";   
  24.     private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:workdb";   
  25.     private static final String USERNAME = "framework";   
  26.     private static final String PASSWORD = "framework";   
  27.        
  28.     private List<ProductInfo> list;   
  29.   
  30.     /**  
  31.      * 数据库连接  
  32.      *   
  33.      * @return Connection  
  34.      */  
  35.     public synchronized Connection getConnection() {   
  36.         try {   
  37.             Class.forName(DRIVER);   
  38.             conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);   
  39.         } catch (ClassNotFoundException e) {   
  40.             e.printStackTrace();   
  41.             return null;   
  42.         } catch (SQLException e) {   
  43.             e.printStackTrace();   
  44.             return null;   
  45.         }   
  46.         return conn;   
  47.     }   
  48.   
  49.     /**  
  50.      * 调用存储过程得到游标数据集  
  51.      *   
  52.      * @return  
  53.      */  
  54.     public List<ProductInfo> queryList() {   
  55.         list = new ArrayList<ProductInfo>();   
  56.         try {   
  57.             if (this.getConnection() != null) {   
  58.                 conn = this.getConnection();   
  59.                 cstmt = conn.prepareCall("{call package_productprice.processProductPrice(?,?,?,?,?)}");   
  60.                 cstmt.setString(1, "0001");   
  61.                 cstmt.setString(2, "10001");   
  62.                 cstmt.setInt(3, 1);   
  63.                 cstmt.setInt(4, 10);   
  64.                 cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.CURSOR);   
  65.                 cstmt.execute();   
  66.                 rs = (ResultSet)cstmt.getObject(5);   
  67.   
  68.                 while (rs.next()) {   
  69.                     ProductInfo productInfo = new ProductInfo();   
  70.                     productInfo.setProductId(rs.getString(1));   
  71.                     productInfo.setProductName(rs.getString(2));   
  72.                     productInfo.setProductPrice(rs.getDouble(3));   
  73.                     productInfo.setProductType(rs.getString(4));   
  74.                     productInfo.setProductPricing(rs.getDouble(5));   
  75.                     list.add(productInfo);   
  76.                 }   
  77.             }   
  78.         } catch (SQLException e) {   
  79.             e.printStackTrace();   
  80.         } finally {   
  81.             try {   
  82.                 if (null != rs) {   
  83.                     rs.close();   
  84.                 }   
  85.                 if (null != cstmt) {   
  86.                     cstmt.close();   
  87.                 }   
  88.                 if (null != conn) {   
  89.                     conn.close();   
  90.                 }   
  91.             } catch (SQLException e) {   
  92.                 e.printStackTrace();   
  93.             }   
  94.         }   
  95.         return list;   
  96.     }   
  97.        
  98.     /**  
  99.      * main方法测试  
  100.      *   
  101.      * @param args  
  102.      * @throws Exception  
  103.      */  
  104.     public static void main(String[] args) throws Exception {   
  105.         ProceduresTest test = new ProceduresTest();   
  106.         List<ProductInfo> productList = test.queryList();   
  107.         for (ProductInfo productInfo : productList) {   
  108.             System.out.println(productInfo.getProductId());   
  109.             System.out.println(productInfo.getProductName());   
  110.             System.out.println(productInfo.getProductPrice());   
  111.             System.out.println(productInfo.getProductPricing());   
  112.             System.out.println(productInfo.getProductType());   
  113.         }   
  114.     }   
  115. }  

另外还需要一个model类ProductInfo，该类的代码只要productId、productName、productPrice、productPricing、productType及相应的getter和setter方法。

php mysql 连接池 php mysql 连接池
原创粉丝点击
热门IT博客
d3.js 弧线d3.js 弧线
java 开发云平台java 开发云平台
外卖软件代理加盟
放生 范逸臣 知乎
js 作用域调用 this
淘宝菲慕最出名模特
橙树网络
2016淘宝最新刷单软件
周立功编程器
问题解答软件
淘宝店铺如何实名认证
java购物车生成订单
软件建模工具
怎么租用阿里云服务器
暗黑3 mac 下载
大连理工大学软件学院
长歌正太捏脸数据
网络自动校时软件
手机淘宝怎么修改差评
阔知学堂 pc
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 室内排水沟篦子 细缝式排水沟 路面排水沟 不锈钢线型排水沟 树脂排水沟盖板 明沟排水 游泳池排水沟 修排水沟 排水沟疏通 排水沟坡度 树脂线性排水沟 边沟和排水沟的区别 v型排水沟 排水沟多少钱一米 排水沟现一米鲤鱼 排水沟施工图 缝隙式线性排水沟 洗车店排水沟设计 道路排水沟做法 线性排水沟做法 排水沟类型 排水沟定额 u型线性排水沟 花岗岩排水沟盖板 雨水方沟 截水沟和排水沟的区别 排水沟详图 水泥排水沟盖板 室外排水沟图集 排水沟沉降缝 线性缝隙式排水沟 排水沟规范 树脂混凝土成品排水沟 排水集气法 排水管45度计算法图解 排水法为什么短进长出 排水泵站 排水泵房 洗衣机排水泵多少钱 洗衣机排水泵 滚筒洗衣机排水泵清理

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里