Batch processing using mysql
Posted by:
Sud s
Date: June 11, 2023 07:09AM
Hello Experts,
As batch processing is critical for performance when we deal with large data volume processing, so we normally avoid row by row processing and opt for batch execution. And in oracle we use bulk collect for that. I am not getting similar functionality for "Type" and "bulk collect" statement as its in oracle also the exception handling part.
Below is the sample block i created using oracle plsql and it works fine. I am new to mysql. Can you guide me if similar can be written in mysql(mainly AWS aurora mysql)? or any alternate way to achieve the same functionality and exception handling?
Create table employees(employee_id NUMBER(10,0), first_name varchar2(4000));
Insert into employees values (1, 'a');
Insert into employees values(1, 'b');
Insert into employees values(1, 'c');
Insert into employees values(1, 'd');
Insert into employees values(1, 'e');
Insert into employees values(1, 'f');
SET SERVEROUTPUT ON
DECLARE
CURSOR c1 IS
SELECT first_name FROM employees
ORDER BY first_name;
TYPE fName IS TABLE OF VARCHAR2 (4000);
v_first_name fname;
e exception;
BEGIN
OPEN c1;
LOOP
FETCH c1 bulk collect INTO v_first_name limit 2;
EXIT WHEN c1%NOTFOUND;
FOR idx IN v_first_name.FIRST.. v_first_name.LAST
LOOP
DBMS_OUTPUT.PUT_LINE (idx||' '||v_first_name(idx) );
--Raise e;
END LOOP;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
raise_application_error (-20001, substr(sqlerrm, 100));
END;
/
Subject
Written By
Posted
Batch processing using mysql
June 11, 2023 07:09AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.