DECLARE
CURSOR c_orders
IS
SELECT customer_id, product_id, business_date,
SUM (price * quantity) sales_amount
FROM orders
GROUP BY customer_id, product_id, business_date;
TYPE t_orders IS TABLE OF c_orders%TYPE;
v_orders t_orders;
e_bulk_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (e_bulk_exception, -24381);
BEGIN
OPEN c_orders;
LOOP
BEGIN
FETCH c_orders
BULK COLLECT INTO t_orders LIMIT 50000;
EXIT WHEN t_orders.COUNT = 0;
for all i in 1..t_orders.count SAVE EXCEPTIONS
INSERT INTO daily_sales(customer_id, product_id, business_date,
sales_amount
)
VALUES (t_orders(i).customer_id, t_orders(i).product_id, t_orders(i).business_date,
t_orders(i).sales
);
COMMIT;
EXCEPTION
WHEN e_bulk_exception
THEN
DBMS_OUTPUT.put_line ( 'BULKCOEECT COUNT'
|| SQL%BULK_EXCEPTIONS.COUNT
);
FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line
( 'BULKCOEECT message '
|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
|| SQL%BULK_EXCEPTIONS (i).ERROR_CODE
);
END LOOP;
END;
END LOOP;
CLOSE c_orders;
COMMIT;
END;
CURSOR c_orders
IS
SELECT customer_id, product_id, business_date,
SUM (price * quantity) sales_amount
FROM orders
GROUP BY customer_id, product_id, business_date;
TYPE t_orders IS TABLE OF c_orders%TYPE;
v_orders t_orders;
e_bulk_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (e_bulk_exception, -24381);
BEGIN
OPEN c_orders;
LOOP
BEGIN
FETCH c_orders
BULK COLLECT INTO t_orders LIMIT 50000;
EXIT WHEN t_orders.COUNT = 0;
for all i in 1..t_orders.count SAVE EXCEPTIONS
INSERT INTO daily_sales(customer_id, product_id, business_date,
sales_amount
)
VALUES (t_orders(i).customer_id, t_orders(i).product_id, t_orders(i).business_date,
t_orders(i).sales
);
COMMIT;
EXCEPTION
WHEN e_bulk_exception
THEN
DBMS_OUTPUT.put_line ( 'BULKCOEECT COUNT'
|| SQL%BULK_EXCEPTIONS.COUNT
);
FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line
( 'BULKCOEECT message '
|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
|| SQL%BULK_EXCEPTIONS (i).ERROR_CODE
);
END LOOP;
END;
END LOOP;
CLOSE c_orders;
COMMIT;
END;
No comments:
Post a Comment