Question on Mysql procedures

Question

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 simple block i wrote using oracle plsql and it works fine. Can you guide me if similar can be re-written in mysql(mainly AWS aurora mysql)? or any alternate way to achieve the same functionality(cursor bulk fetch and type declaration) 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');
Insert into employees values(1, 'g');
Insert into employees values(1, 'h');
Insert into employees values(1, 'i');
```

```
`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;
/`
```

Answer

Hi there.

Something like this might get you started.

```
DECLARE var$ID DECIMAL (10, 0);
DECLARE var$NAME VARCHAR (4000);
DECLARE done INT DEFAULT FALSE;
DECLARE v_list CURSOR FOR 
   SELECT * FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done := TRUE;
OPEN v_list;
read_label:
LOOP
   FETCH v_list INTO var$ID, var$NAME;
   IF done THEN
      LEAVE read_label;
   END IF;
END LOOP;
CLOSE v_list;
```

I hope this helps (if so, please accept the answer).

Question on Mysql procedures

Relevant content