Mysql 存储过程的使用问题

pengcheng_1024 发布于 2014/05/22 10:52
阅读 158
收藏 0
/**
这是我的java拼接的sql,拼接好直接调用查询即可,我之前用mysql不是直接拼接的,就是判断一个参数是否有值,就写一个sql,
这样下拉,存储过程百分之80都是一样的语句,就是where后面不一样,感觉这样太烂了!!
**/
有那位给个好的解决方法啊??? 
StringBuffer query = new StringBuffer(" SELECT bdbr.`NO` AS '分部编号',bdbr.`NAME` AS '分部名称',SUM(info.TOTAL) AS '销售收入',SUM(info.COST) AS '成本', ");
query.append(" SUM(info.TOTAL-info.COST) AS '毛利',SUM(info.TOTAL-info.COST)/SUM(info.TOTAL) AS '毛利率',SUM(info.COST)/SUM(info.TOTAL) AS '成本率' ");
query.append(" FROM prod_material_obtain_branch AS info ");
query.append(" LEFT JOIN bd_branch AS bdbr ON bdbr.ID = info.REQUEST_BRANCH_ID WHERE `STATUS`!=5 ");
if (null != branchID && branchID.length() > 0) {
	query.append(" and info.REQUEST_BRANCH_ID IN(");
	query.append(branchID);
	query.append(") ");
}
if (null != start && start.length() > 0) {
	query.append(" AND info.NOTES_DATE>='");
	query.append(start);
	query.append("' ");
}
if (null != end && end.length() > 0) {
	query.append(" AND info.NOTES_DATE<=' ");
	query.append(end);
	query.append("' ");
}
        query.append(" GROUP BY info.REQUEST_BRANCH_ID order by SUM(info.TOTAL-info.COST) desc");
/** 数据源 **/

我存储过程是想拼接sql然后查询,可是我不知道怎么拼接sql!有参数传入后要判断是否有值,有值时将wehre加上相应的参数过滤。


下面是我的部分存储过程,不要说这个查询不需要存储过程,我是想表达这种效果怎么实现,因为下面的编写是有错误的。希望看懂的给点指导,感谢啦。

CREATE PROCEDURE h_rb.report_inventory_flow_copy(warehouseID int,productID varchar(50),startDate varchar(23),endDate varchar(23),type int,  IN supplierID varchar(10),  IN branchID varchar(10),IN statu varchar(10))
BEGIN
/*
   出入库记录报表
   warehouseID int, 仓库id
   productID varchar(50), 商品编码或名称
   startDate varchar(23), 开始时间
   endDate varchar(23), 结束时间
   type int,  是入库还是出库
   supplierID varchar(10),  供应id
   branchID varchar(10),  分部id
   statu varchar(10): 单据状态
*/
-- 基本单位对主要单位换算
DECLARE itemNo VARCHAR(50);
DECLARE MAIN2BASIC DOUBLE;
DECLARE itemName VARCHAR(50);
DECLARE unitName VARCHAR(50);
DECLARE norm VARCHAR(100);
DECLARE whereInfo VARCHAR(5000);

CREATE TEMPORARY TABLE IF NOT EXISTS flow-- 不存在则创建临时表  
(
  fTime VARCHAR(23) NULL, -- 日期
  suppler VARCHAR(50) NULL, -- 供应商
  branch VARCHAR(50) NULL, -- 分部
  fno VARCHAR(20) NULL, -- 单号
  ftype VARCHAR(50) NULL, -- 单据类型
  fname VARCHAR(50) NULL, -- 单据名称
  fmodify VARCHAR(20) NULL, -- 操作员
  amount DECIMAL(18, 4) NULL, -- 单据数量
  price DECIMAL(18, 4) NULL, -- 单据单价
  sumt DECIMAL(18, 4) NULL, -- 单据金额
  tAmount DECIMAL(18, 4) NULL, -- 结存量
  tPrice DECIMAL(18, 4) NULL, -- 结存单价
  tSum DECIMAL(18, 4) NULL -- 结存金额
);

TRUNCATE TABLE flow; -- 清空临时表


-- 判断参数是否有值,然后拼接
    if  ISNULL(productID) || LENGTH(trim(productID))<1  THEN
                SET whereInfo = CONCAT(' AND iifl.PRODUCT_ID = ', productID) ;
    ELSEIF supplierID!=0  THEN
                SET whereInfo = concat(whereInfo, ' AND AND iifl.SUPPLIER_ID = ', supplierID);
    ELSEIF branchID!=0  THEN
                SET whereInfo = concat(whereInfo, ' iifl.BRANCH_ID =  = ', branchID);
    ELSEIF supplierID!=0  THEN
                SET whereInfo = concat(whereInfo, ' AND AND iifl.SUPPLIER_ID = ', supplierID);
    END if;
 
  if ISNULL(whereInfo) || LENGTH(trim(whereInfo))<1   then
         INSERT INTO flow(fTime,fno,ftype,fname,fmodify,amount,price,sumt,tAmount,tPrice,tSum,suppler,branch)
         SELECT iifl.NOTES_DATE  AS fTime,
                        iifl.BILLS_NO AS fno,
                        CASE iifl.FLOW_TYPE
                                WHEN 0 THEN '入库'
                                WHEN 1 THEN '出库'
                                ELSE iifl.FLOW_TYPE
                        END AS ftype, sre.`NAME` AS fname,sus.NAME_CN AS fmodify,
                        iifl.BILLS_AMOUNT/bdpr.MAIN2BASIC  AS amount,iifl.BILLS_PRICE AS price,iifl.BILLS_AMOUNT/bdpr.MAIN2BASIC *iifl.BILLS_PRICE AS sumt,
                        iifl.STORAGE_AMOUNT/bdpr.MAIN2BASIC AS tAmount,iifl.STORAGE_PRICE  AS tPrice,iifl.STORAGE_AMOUNT/bdpr.MAIN2BASIC*iifl.STORAGE_PRICE  AS tSum,
                        bdsu.SUPPLY_NAME AS supplier,bdbr.`NAME` AS branch
         FROM ivt_inventory_flow AS iifl
         LEFT JOIN sys_user AS sus ON sus.ID = iifl.MODIFIER
         LEFT JOIN bd_product AS bdpr ON bdpr.`NO`=iifl.ITEM_NO
         LEFT JOIN sys_receipt AS sre ON sre.ID=iifl.BILLS_TYPE
         LEFT JOIN bd_branch AS bdbr ON bdbr.ID=iifl.BRANCH_ID
         LEFT JOIN bd_supplier AS bdsu ON bdsu.ID=iifl.SUPPLIER_ID
         WHERE   iifl.WAREHOUSE_ID = warehouseID AND  iifl.NOTES_DATE BETWEEN startDate AND endDate

         ORDER BY iifl.NOTES_DATE DESC;
    ELSE
        INSERT INTO flow(fTime,fno,ftype,fname,fmodify,amount,price,sumt,tAmount,tPrice,tSum,suppler,branch)
            SELECT iifl.NOTES_DATE  AS fTime,
                        iifl.BILLS_NO AS fno,
                        CASE iifl.FLOW_TYPE
                                WHEN 0 THEN '入库'
                                WHEN 1 THEN '出库'
                                ELSE iifl.FLOW_TYPE
                        END AS ftype,
                        sre.`NAME` AS fname,sus.NAME_CN AS fmodify,
                        iifl.BILLS_AMOUNT/bdpr.MAIN2BASIC  AS amount,iifl.BILLS_PRICE AS price,iifl.BILLS_AMOUNT/bdpr.MAIN2BASIC *iifl.BILLS_PRICE AS sumt,
                        iifl.STORAGE_AMOUNT/bdpr.MAIN2BASIC AS tAmount,iifl.STORAGE_PRICE  AS tPrice,iifl.STORAGE_AMOUNT/bdpr.MAIN2BASIC*iifl.STORAGE_PRICE  AS tSum,
                        bdsu.SUPPLY_NAME AS supplier,bdbr.`NAME` AS branch
         FROM ivt_inventory_flow AS iifl
         LEFT JOIN sys_user AS sus ON sus.ID = iifl.MODIFIER
         LEFT JOIN bd_product AS bdpr ON bdpr.`NO`=iifl.ITEM_NO
         LEFT JOIN sys_receipt AS sre ON sre.ID=iifl.BILLS_TYPE
         LEFT JOIN bd_branch AS bdbr ON bdbr.ID=iifl.BRANCH_ID
         LEFT JOIN bd_supplier AS bdsu ON bdsu.ID=iifl.SUPPLIER_ID
         WHERE   iifl.WAREHOUSE_ID = warehouseID AND  iifl.NOTES_DATE BETWEEN startDate AND endDate +@whereInfo

         ORDER BY iifl.NOTES_DATE DESC;

 end if;
 
   SELECT
    itemNo    AS '商品编号', itemName    AS '商品名称',    norm    AS '规格',
    unitName    AS '单位',    fTime    AS '日期',    suppler  AS '供应商',
    branch   AS '分部',    fno      AS '单号',    ftype    AS '单据类型',
    fname    AS '单据名称',    fmodify  AS '操作员',    amount   AS '单据数量',
    price    AS '单据单价',    sumt      AS '单据金额',    tAmount  AS '结存量',
    tPrice   AS '结存单价',    tSum     AS '结存金额' FROM flow ORDER BY tAmount DESC;

TRUNCATE TABLE flow; -- 清空临时表
END
加载中
0
南湖船老大
南湖船老大
不明白你在说啥,你这里说的和存储过程没看出啥联系
pengcheng_1024
pengcheng_1024
我有做了补充,帮忙看看呗
pengcheng_1024
pengcheng_1024
我存储过程是想拼接sql然后查询,可是我不知道怎么拼接sql!有参数传入后要判断是否有值,有值时将wehre加上相应的参数过滤。
返回顶部
顶部