请高手优化

wdswcy 2008-08-23 12:58:03
有一表,记录某一部门的免费金额,如:
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表有大量的数据,查询时速度很慢,有没有办法优化一下啊

...全文
194 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
wdswcy 2008-08-23
  • 打赏
  • 举报
回复
谢谢oracledbalgtu ,肯定给你分的,你的函授比我的写的好,我明天在生产环境上试一下

各位还有什么好方法啊,呵呵
oracledbalgtu 2008-08-23
  • 打赏
  • 举报
回复

在一个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




[Quote=引用 11 楼 wdswcy 的回复:]
期待高手
[/Quote]
wdswcy 2008-08-23
  • 打赏
  • 举报
回复
期待高手
wdswcy 2008-08-23
  • 打赏
  • 举报
回复
http://topic.csdn.net/u/20080823/14/ac6d66c5-1cc7-4a73-9321-65d322d0a5f3.html
mantisXF 2008-08-23
  • 打赏
  • 举报
回复
直接把源表数据和字段信息、生成的数据格式贴出来,看用SQL可不可以实现。
wdswcy 2008-08-23
  • 打赏
  • 举报
回复
放进去那个都不对,又没有别的办法啊,如不用函数,用一条SQL写出来
oracledbalgtu 2008-08-23
  • 打赏
  • 举报
回复
那就把能放进去的先试试。

[Quote=引用 6 楼 wdswcy 的回复:]
SQL code
看错了,应该是传递的参数。

把 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,这样就查不出来了他上级部门的记录了
[/Quote]
wdswcy 2008-08-23
  • 打赏
  • 举报
回复

看错了,应该是传递的参数。

把 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,这样就查不出来了他上级部门的记录了

wdswcy 2008-08-23
  • 打赏
  • 举报
回复
代码是可以执行的,我都做了查询,只是速度慢
oracledbalgtu 2008-08-23
  • 打赏
  • 举报
回复
看错了,应该是传递的参数。

把 NVL(GZ.QZD_JE, 0.00) > 0
AND ZSXM_DM = AV_ZSXM
AND ZSPM_DM = AV_ZSPM
的限制放到最里层查询去试试。

[Quote=引用 3 楼 oracledbalgtu 的回复:]
给的代码本身就无法执行,因为你的g.*中根本不包含AV_ZSXM和AV_ZSPM
那下面的关联:ZSXM_DM = AV_ZSXM AND ZSPM_DM = AV_ZSPM就不会成立。

引用楼主 wdswcy 的帖子:
有一表,记录某一部门的免费金额,如:
1 165 02 1110 5000 Y 250000000
2 300 02 1110 200 Y 250010700
3 301 02 1110 100 Y 250010703
这个免费金额又是根据部门递归向上查询的,如某一部门没有记录,要用他上级部门的金额

部门表如下:
部…
[/Quote]
oracledbalgtu 2008-08-23
  • 打赏
  • 举报
回复
给的代码本身就无法执行,因为你的g.*中根本不包含AV_ZSXM和AV_ZSPM
那下面的关联:ZSXM_DM = AV_ZSXM AND ZSPM_DM = AV_ZSPM就不会成立。

[Quote=引用楼主 wdswcy 的帖子:]
有一表,记录某一部门的免费金额,如:
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 25001…
[/Quote]
wdswcy 2008-08-23
  • 打赏
  • 举报
回复
高手人呢
wdswcy 2008-08-23
  • 打赏
  • 举报
回复

有一表,记录某一部门的免费金额,
表名: 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


3,492

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧