Friday, August 31, 2018

BULK COLLECT & FORALL vs. CURSOR & FOR-LOOP



CREATE OR REPLACE PROCEDURE CURSOR_FOR_OPEN_ORA
 IS
 l_ename VARCHAR2(30);
 l_empno VARCHAR2(30);
 l_sal VARCHAR2(19);
 CURSOR cur IS SELECT ename, empno , sal  FROM emp;
 BEGIN
 dbms_output.put_line('Before CURSOR OPEN: ' || systimestamp);
 OPEN cur;
 dbms_output.put_line('Before LOOP: ' || systimestamp);
 LOOP
 FETCH cur INTO l_ename, l_empno, l_sal;
 IF cur%NOTFOUND THEN
 EXIT;
 END IF;
 INSERT INTO emp1(ename,empno,sal) values (l_ename, l_empno, l_sal);
 END LOOP;
 CLOSE cur;
 dbms_output.put_line('After CURSOR CLOSE: ' || systimestamp);
 COMMIT;
 END;
/

 Procedure created.

SQL> exec CURSOR_FOR_OPEN_ORA();
Before CURSOR OPEN: 31-AUG-18 03.19.22.033000000 PM +05:30
Before LOOP: 31-AUG-18 03.19.22.033000000 PM +05:30
After CURSOR CLOSE: 31-AUG-18 03.19.22.033000000 PM +05:30

PL/SQL procedure successfully completed.

No comments:

Post a Comment