Tham khảo tài liệu 'oracle pl/sql by example- p10', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | LAB 422 LAB The BULK COLLECT Clause LAB OBJECTIVE After completing this lab you will be able to Use the BULK COLLECT clause The BULK COLLECT clause fetches the batches of results and brings them back from SQL to PL SQL. For example consider a cursor against the STUDENT table that returns the student s ID first name and last name. After this cursor is opened the rows are fetched one by one until all of them have been processed. Then this cursor is closed. These steps are illustrated in the following example FOR EXAMPLE DECLARE CURSOR student_cur IS SELECT student_id first_name last_name FROM student BEGIN FOR rec IN student_cur LOOP student_id first_name last_name END LOOP END Recall that the cursor FOR loop opens and closes the cursor and fetches cursor records implicitly. The same task of fetching records from the STUDENT table can be accomplished by employing the BULK COLLECT clause. The difference here is that the BULK COLLECT clause fetches all rows from the STUDENT table at once. Because BULK COLLECT fetches multiple rows these rows are stored in collection variables. Consider a modified version of the previous example in which the cursor processing is replaced by the BULK COLLECT clause Please purchase PDF Split-Merge on to remove this watermark. The BULK COLLECT Clause LAB 423 FOR EXAMPLE DECLARE -- Define collection type and variables to be used by the -- BULK COLLECT clause TYPE student_id_type IS TABLE OF TYPE TYPE first_name_type IS TABLE OF TYPE TYPE last_name_type IS TABLE OF TYPE student_id_tab student_id_type first_name_tab first_name_type last_name_tab last_name_type BEGIN -- Fetch all student data at once via BULK COLLECT clause SELECT student_id first_name last_name BULK COLLECT INTO student_id_tab first_name_tab last_name_tab FROM student FOR i IN .
