Oracle cursor for updating in oracle
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.Suppose, for example, that I have a table (training_months) of the months in a year in which I am available to provide training on the PL/SQL language.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.But there’s a problem with using a cursor FOR loop for a single-row fetch: the resulting code is very misleading. However, from a best practices standpoint, writing code that is transparent in purpose and easy to read and understand is most important.I further recommend that you encapsulate your SELECT INTO statements into their own functions whose sole purpose is to retrieve this one row of information, as shown in Listing 1.
*/ INSERT INTO training_months VALUES (' March'); INSERT INTO training_months VALUES (' April'); INSERT INTO training_months VALUES (' May'); INSERT INTO training_months VALUES (' June'); INSERT INTO training_months VALUES (' September'); INSERT INTO training_months VALUES (' October'); INSERT INTO training_months VALUES (' November'); COMMIT; END; / DECLARE TYPE at_most_twelve_t IS VARRAY (12) OF VARCHAR2 (100); l_months at_most_twelve_t; BEGIN SELECT month_name BULK COLLECT INTO l_months FROM training_months; FOR indx IN 1 .. COUNT LOOP DBMS_OUTPUT.put_line (l_months (indx)); END LOOP; END; / However, there’s one concern with this varray approach: what if you know the maximum number of elements that can appear in the varray and that maximum is 1,000,000?
Having said that, I can identify two circumstances in which using a cursor FOR loop would do little harm.
First, because Oracle Database automatically optimizes cursor FOR loops to execute similarly to BULK COLLECT, as long as the body of your loop does not push changes back to the database, your “read-only” cursor FOR loop will almost always suffice.
Let’s take a closer look at these five cursor FOR loop recommendations.
Never use a cursor FOR loop if the loop body executes non-query DML (INSERT, UPDATE, DELETE, MERGE).
END process_employee; The implicit SELECT INTO offers the most-efficient means of returning that single row of information to your PL/SQL program.