create or replace procedure UPDATE_CLASS_ON_INP_RCPT is
CURSOR CURS1 IS
SELECT "PRICE_LIST"."ITEM_CLASS",
"PRICE_LIST"."ITEM_CODE",
"PRICE_LIST"."ITEM_SPEC",
"PRICE_LIST"."UNITS",
count(start_date),
CLASS_ON_INP_RCPT
FROM "PRICE_LIST"
group by item_class,item_code,item_spec,units,CLASS_ON_INP_RCPT;
v_ITEM_CLASS VARCHAR2 (1);
v_ITEM_CODE VARCHAR2 (10);
v_ITEM_SPEC VARCHAR2 (20);
v_UNITS VARCHAR2 (8);
v_CLASS_ON_INP_RCPT VARCHAR2(1);
v_count_date number (3);
begin
LOCK TABLE INP_BILL_DETAIL_TEST IN EXCLUSIVE MODE ;
OPEN CURS1 ;
FETCH CURS1 INTO v_ITEM_CLASS,v_ITEM_CODE,v_ITEM_SPEC,v_UNITS,v_count_date,v_CLASS_ON_INP_RCPT; WHILE (CURS1%found) loop
UPDATE "INP_BILL_DETAIL_TEST"
SET "CLASS_ON_INP_RCPT" = v_CLASS_ON_INP_RCPT
WHERE ( "INP_BILL_DETAIL_TEST"."ITEM_CLASS" = v_ITEM_CLASS ) AND
( "INP_BILL_DETAIL_TEST"."ITEM_CODE" = v_ITEM_CODE ) AND
( "INP_BILL_DETAIL_TEST"."ITEM_SPEC" = v_ITEM_SPEC ) AND
( "INP_BILL_DETAIL_TEST"."UNITS" = v_UNITS ) ;
end loop;
close CURS1 ;
commit;end;
附:PRICE_LIST大概有10000条数据,INP_BILL_DETAIL_TEST大概有200条数据。
在监控工具中显示EXECUTION=336523370,PARSE CALLS=2,BUFFER GETS=336523443,其他数值都较小,这是为何啊?
CURSOR CURS1 IS
SELECT "PRICE_LIST"."ITEM_CLASS",
"PRICE_LIST"."ITEM_CODE",
"PRICE_LIST"."ITEM_SPEC",
"PRICE_LIST"."UNITS",
count(start_date),
CLASS_ON_INP_RCPT
FROM "PRICE_LIST"
group by item_class,item_code,item_spec,units,CLASS_ON_INP_RCPT;
v_ITEM_CLASS VARCHAR2 (1);
v_ITEM_CODE VARCHAR2 (10);
v_ITEM_SPEC VARCHAR2 (20);
v_UNITS VARCHAR2 (8);
v_CLASS_ON_INP_RCPT VARCHAR2(1);
v_count_date number (3);
begin
LOCK TABLE INP_BILL_DETAIL_TEST IN EXCLUSIVE MODE ;
OPEN CURS1 ;
FETCH CURS1 INTO v_ITEM_CLASS,v_ITEM_CODE,v_ITEM_SPEC,v_UNITS,v_count_date,v_CLASS_ON_INP_RCPT; WHILE (CURS1%found) loop
UPDATE "INP_BILL_DETAIL_TEST"
SET "CLASS_ON_INP_RCPT" = v_CLASS_ON_INP_RCPT
WHERE ( "INP_BILL_DETAIL_TEST"."ITEM_CLASS" = v_ITEM_CLASS ) AND
( "INP_BILL_DETAIL_TEST"."ITEM_CODE" = v_ITEM_CODE ) AND
( "INP_BILL_DETAIL_TEST"."ITEM_SPEC" = v_ITEM_SPEC ) AND
( "INP_BILL_DETAIL_TEST"."UNITS" = v_UNITS ) ;
end loop;
close CURS1 ;
commit;end;
附:PRICE_LIST大概有10000条数据,INP_BILL_DETAIL_TEST大概有200条数据。
在监控工具中显示EXECUTION=336523370,PARSE CALLS=2,BUFFER GETS=336523443,其他数值都较小,这是为何啊?
这样写不行吗? UPDATE "INP_BILL_DETAIL_TEST"
SET "CLASS_ON_INP_RCPT" = (
SELECT
max( CLASS_ON_INP_RCPT )
FROM "PRICE_LIST"
WHERE ( "INP_BILL_DETAIL_TEST"."ITEM_CLASS" = "PRICE_LIST"."ITEM_CLASS" ) AND
( "INP_BILL_DETAIL_TEST"."ITEM_CODE" = PRICE_LIST"."ITEM_CODE" ) AND
( "INP_BILL_DETAIL_TEST"."ITEM_SPEC" = "PRICE_LIST"."ITEM_SPEC" ) AND
( "INP_BILL_DETAIL_TEST"."UNITS" = "PRICE_LIST"."UNITS")
)