I need to retrieve these months into a collection and then display each month.I can use a varray to ensure that the absolute limit of 12 is respected, as shown in Listing 3.
Oracle Database automatically optimizes cursor FOR loops to execute similarly to BULK COLLECT, so you get most of the advantages of that approach.
Next, if you’re writing a “one-off” script, or a program that is run only occasionally and is not in the critical path of operations, you may want to choose the simplicity and readability of the cursor FOR loop over the incremental improvement in performance (and additional complexity of code) that BULK COLLECT offers.
By the way, if you are writing back-end code that serves up data to a stateless user interface, as is often the case with browser-based applications, you would never retrieve so many rows at once.
Instead, consider a “next page” paradigm, in which the page has a preset maximum number of rows and the query that retrieves the next set of rows includes a WHERE clause that specifies that range of rows.
If you need to retrieve a single row and you know that at most one row should be retrieved, you should use a SELECT INTO statement, as in the following: PROCEDURE process_employee ( id_in IN employees.employee_id%TYPE) IS l_last_name employees.last_name%TYPE; BEGIN SELECT e.last_name INTO l_last_name FROM employees e WHERE e.employee_id = process_employee.id_in; ...
END process_employee; The implicit SELECT INTO offers the most-efficient means of returning that single row of information to your PL/SQL program.Use an implicit SELECT INTO for single-row fetches.Developers often tell me that they write a cursor FOR loop to fetch a single row. Oracle Database does so much of the work for you, saving several lines of code and several minutes of typing.Listing 2 shows how to define a varray type and declare a variable based on this type.If you try to add a 13th month to this list, Oracle Database will raise an exception: Code Listing 2: Defining a varray type and declaring a variable DECLARE TYPE at_most_twelve_t IS VARRAY (12) OF VARCHAR2 (100); l_months at_most_twelve_t := at_most_twelve_t ('January' , 'February' , 'March' , 'April' , 'May' , 'June' , 'July' , 'August' , 'September' , 'October' , 'November' , 'December' ); Varrays offer a very nice mechanism when you need to retrieve multiple rows of data efficiently and the number of rows should never exceed a certain limit.Having said that, I can identify two circumstances in which using a cursor FOR loop would do little harm.