oracle存百分比,计算库存百分比 - Oracle开发 - ITPUB论坛－中国专业的IT技术社区...

/*本过程要实现的功能:对于同一产品代号(prod_no)且同一产品状态(prod_status)的产品在每个仓库(finance)中所占比率累计和为100%*/

/*

测试用表在drpimp下:fp_test

FINANCE     VARCHAR2(4)       ---仓库代号

PROD_NO     VARCHAR2(11)

PROD_STATUS VARCHAR2(1)       ---产品状态

PERCENT         NUMBER(10,2)  ---百分比

WHOUSE_NM   VARCHAR2(20)

*/

CREATE OR REPLACE PROCEDURE prod_percent

IS

BEGIN

DECLARE

CURSOR c1 IS

SELECT * FROM fp_test

WHERE percent<>100 --过滤掉百分比为100%的记录

ORDER BY prod_no,

prod_status,

finance;

s_pt number(10,2):=0;

BEGIN

FOR a1 IN c1 LOOP

DECLARE

row_no varchar2(100);

CURSOR c2 IS

SELECT * FROM fp_test

WHERE trim(prod_no)=trim(a1.prod_no)

AND trim(prod_status)=trim(a1.prod_status)

ORDER BY prod_no,

prod_status,

finance;

BEGIN

FOR a2 IN c2 LOOP

SELECT COUNT(*) INTO row_no

FROM fp_test

WHERE trim(prod_no)=trim(a2.prod_no)

AND trim(prod_status)=trim(a2.prod_status);

IF trim(c2%ROWCOUNT)=trim(row_no) THEN

UPDATE fp_test

SET percent=100-s_pt

WHERE trim(prod_no)=trim(a2.prod_no)

AND trim(prod_status)=trim(a2.prod_status)

AND trim(finance)=trim(a2.finance);

ELSE

s_pt:=s_pt+a2.percent;

END IF;

END LOOP;

END;

END LOOP;

COMMIT;

END;

END;

最终执行结果不正确,请高手指教