Oracle pl/SQL: batch extraction

Oracle pl/SQL: batch extraction all data can be extracted from the cursor result set at one time by using the fetch... bulk collect into statement. Www.2cto.com SQL code -- use fetch... bulk collect into to extract all data/* Use Substitution variables to enter a post, store the result set to PL/SQL set variables, and display the name and salary. */Declare cursor emp_cursor is select * from emp where lower (job) = lower ('& job'); -- defines the cursor type emp_table_type is table of emp % rowtype; emp_table emp_table_type; begin open emp_cursor; -- open the cursor fetch emp_cursor bulk collect into emp_table; -- Extract all data from the cursor result set at one time close emp_cursor; -- close the cursor for I in 1 .. emp_table.count loop dbms_output.put_line ('name: '| emp_table (I ). ename | ', salary: $' | emp_table (I ). sal); end loo Run the p; end; www.2cto.com command window: the input value is in job: clerk output. to limit the number of extracted rows, use the limit clause. When you use fetch... bulk collect into to extract all data, if the cursor result set contains a large amount of data and uses the VARRAY set variable to receive data, you may need to limit the number of rows extracted each time. SQL code/* Use Substitution variables to enter the number of rows extracted each time, and display the names and salaries of all employees. */Declare cursor emp_cursor is select * from emp; type emp_array_type is varray (5) of emp % rowtype; emp_array emp_array_type; -- defines the varray set variable begin open emp_cursor; loop fetch emp_cursor bulk collect into emp_array limit & rows; for I in 1 .. emp_array.count loop dbms_output.put_line ('name: '| emp_array (I ). ename | ', salary: $' | emp_array (I ). sal); end loop; exit when emp_cursor % notfound; end loop; close emp_cursor; -- close the cursor end; command window execution: input value in rows: 4 output: