请高手优化

Question

有一表，记录某一部门的免费金额，如：
1 165 02 1110 5000 Y 250000000
2 300 02 1110 200 Y 250010700
3 301 02 1110 100 Y 250010703
这个免费金额又是根据部门递归向上查询的，如某一部门没有记录，要用他上级部门的金额部门表如下：
 部门代码                    上级部门
25001070004 Y 250010700
25001070005 Y 250010700
25001070006 Y 250010700
25001070007 Y 250010700
25001070008 Y 250010700现我写了一函数，能查出某部门的金额，但速度有点慢，请各位高手帮忙优化一下create or replace function w_get_qzd(av_gljg IN t_Dj_nsrxx.Gljg_Dm%TYPE, -- 机关
                                  av_zsxm  IN t_hd_yjszsx.zsxm_dm%TYPE, -- 条件1
                                  av_zspm  IN t_hd_yjszsx.zspm_dm%TYPE -- 条件2
                                  ) return number is
  qzd number;
begin
    select qzd_je
    into qzd
    from
    (
          select g.*,
                row_number()over(partition by zsxm_dm,zspm_dm order by swjg_dm desc )r
          from
          (
              select nvl(gz.qzd_je,0.00) qzd_je,
                     gz.zsxm_dm,
                     gz.zspm_dm,
                     jg.swjg_dm,
                     jg.sjswjg_dm
              from t_dm_gy_swjg jg
              left join t_cs_sb_qzdgz gz on (jg.swjg_dm=gz.gljg_dm)
          ) g
          START WITH swjg_dm =av_gljg
          CONNECT BY prior SJSWJG_DM =   swjg_DM
    ) qz
    where qzd_je>0
        and r<=1
        and zsxm_dm=av_zsxm   
        and zspm_dm=av_zspm   ;
  return qzd;
exception
    WHEN OTHERS THEN
    qzd := 0;
    RETURN(qzd);
end  w_get_qzd;调用方法
select a.*,
    w_get_qzd(a.bm,a.jj,a.bb) ss
from a
where a.je<w_get_qzd(a.bm,a.jj,a.bb)a表有大量的数据，查询时速度很慢，有没有办法优化一下啊

Answer 1

有一表，记录某一部门的免费金额，
表名： t_cs_sb_qzdgz 如： 
1 165 02 1110 5000 Y 250000000 
2 300 02 1110 200 Y 250010700 
3 301 02 1110 100 Y 250010703 
这个免费金额又是根据部门递归向上查询的，如某一部门没有记录，要用他上级部门的金额 部门表如下：表名： t_dm_gy_swjg 
部门代码                    上级部门 
25001070004 Y 250010700 
25001070005 Y 250010700 
25001070006 Y 250010700 
25001070007 Y 250010700 
25001070008 Y 250010700 

Answer 2

给的代码本身就无法执行，因为你的g.*中根本不包含AV_ZSXM和AV_ZSPM
那下面的关联：ZSXM_DM = AV_ZSXM AND ZSPM_DM = AV_ZSPM就不会成立。

Answer 3

看错了，应该是传递的参数。把  NVL(GZ.QZD_JE, 0.00) > 0
   AND ZSXM_DM = AV_ZSXM
   AND ZSPM_DM = AV_ZSPM
的限制放到最里层查询去试试。

Answer 4

看错了，应该是传递的参数。 把  NVL(GZ.QZD_JE, 0.00) > 0 
  AND ZSXM_DM = AV_ZSXM 
  AND ZSPM_DM = AV_ZSPM 
的限制放到最里层查询去试试。
  AND ZSXM_DM = AV_ZSXM 
  AND ZSPM_DM = AV_ZSPM 
这两个条件不能放到最里层的，因为t_cs_sb_qzdgz 表中不存在某部门时，g.ZSXM_DM是null，这样就查不出来了他上级部门的记录了

Answer 5

放进去那个都不对，又没有别的办法啊，如不用函数，用一条SQL写出来

Answer 6

直接把源表数据和字段信息、生成的数据格式贴出来，看用SQL可不可以实现。

Answer 7

http://topic.csdn.net/u/20080823/14/ac6d66c5-1cc7-4a73-9321-65d322d0a5f3.html

Answer 8

在一个sql中完成太复杂了；添加一个函数，简化代码，当自己没有免费点的时候用来取父部门的免费点。
DROP TABLE a;
DROP TABLE bm;
DROP TABLE kc;
CREATE TABLE a (depid VARCHAR2(10),free INT,product VARCHAR2(10));
--部门      免费点     产品;
INSERT INTO A VALUES ('01', 500, '01');
INSERT INTO A VALUES ('01', 300, '02');
INSERT INTO A VALUES ('0101', 100, '01');
INSERT INTO A VALUES ('0102', 50, '02');
--注：此表中，如果某部门不存在，要用上级部门的记录，是递归向上查询的;
--部门表 bm;
CREATE TABLE bm(deptid VARCHAR2(10),parent_id VARCHAR2(10));
--编号       上级部门;
INSERT INTO BM VALUES ('01', '00');
INSERT INTO BM VALUES ('0101', '01');
INSERT INTO BM VALUES ('0102', '01');
INSERT INTO BM VALUES ('0103', '01');
--库存表 kc ;
CREATE TABLE kc(ID VARCHAR2(10),deptid VARCHAR2(10),product VARCHAR2(10),price INT);
--id        部门      产品ID   价格;
INSERT INTO KC VALUES ('0001', '0101', '01', 5000);
INSERT INTO KC VALUES ('0002', '0102', '02', 60);
INSERT INTO KC VALUES ('0003', '0103', '02', 40);
INSERT INTO KC VALUES ('0004', '0103', '01', 520);
INSERT INTO KC VALUES ('0005', '0103', '01', 110);
INSERT INTO KC VALUES ('0006', '01', '01', 120);
INSERT INTO KC VALUES ('0007', '01', '02', 700);
COMMIT;CREATE OR REPLACE FUNCTION GET_FREE(P_DEPTID VARCHAR2, P_PRODUCT VARCHAR2)
  RETURN INTEGER IS
  V_RETURN INTEGER;
BEGIN
  WITH M AS(
    SELECT X.LL, A.PRODUCT, A.DEPID, A.FREE
      FROM A,
           (SELECT BM.*, LEVEL LL
              FROM BM
             START WITH DEPTID = P_DEPTID
            CONNECT BY PRIOR PARENT_ID = DEPTID) X
     WHERE A.DEPID = X.DEPTID
       AND A.PRODUCT = P_PRODUCT)
      SELECT FREE
        INTO V_RETURN
        FROM M
       WHERE LL = (SELECT MIN(LL) FROM M);  RETURN(V_RETURN);
END GET_FREE;
/
SELECT KC.*
  FROM KC,
       (SELECT BM.PARENT_ID, BM.DEPTID, A.FREE, A.PRODUCT
          FROM A, BM
         WHERE A.DEPID(+) = BM.DEPTID) A
 WHERE KC.PRICE > DECODE(FREE, NULL, GET_FREE(KC.DEPTID, KC.PRODUCT), FREE)
   AND KC.DEPTID = A.DEPTID
   AND KC.PRODUCT = DECODE(A.PRODUCT, NULL, KC.PRODUCT, A.PRODUCT)
/*   AND kc.deptid='0103'
   AND kc.product='01'*/
   ;输出:
1    0001    0101    01    5000
2    0002    0102    02    60
3    0004    0103    01    520
4    0007    01    02    700

Answer 9

谢谢oracledbalgtu ，肯定给你分的，你的函授比我的写的好，我明天在生产环境上试一下各位还有什么好方法啊，呵呵