2.2. Using Explicit Cursors Versus Implicit CursorsA topic that has inspired much debate over the years is the choice between explicit and implicit cursorsor put another way, the "OPEN, FETCH, CLOSE" versus the "SELECT INTO" debate. In this section, I will completely set aside the debate about performance because Oracle has done a lot of work in recent releases to render the point moot. Instead, I'll focus on the effects on the database and discuss the differing PL/SQL usage of both cursor types including the fact they don't always match up in the database's shared pool. 2.2.1. What's the Difference?In PL/SQL, an implicit cursor is one that is defined as it is being executed. Here's an example: DECLARE v_date DATE; BEGIN SELECT order_date INTO v_date FROM orders WHERE order_number = 100; END; As the code was executing, it created a cursor to select the order_date for order 100. Thus, the cursor was implicitly defined when the code executed. An explicit cursor is one that is defined before it actually gets executed. Here's a simple example: DECLARE CURSOR curs_get_od IS SELECT order_date FROM orders WHERE order_number = 100; v_date DATE; BEGIN OPEN curs_get_od; FETCH curs_get_od INTO v_date; CLOSE curs_get_od; END; The implicit version was much easier to write and required a lot less typing, so the initial reaction may be to go with that choice. However, explicit cursors have other benefits that make the extra typing worthwhile within PL/SQL code, as described in the next two sections. 2.2.2. Cursor AttributesA key benefit of explicit cursors is the attributes they provide to facilitate logical programming. Consider the following example. Here, we want to look for an order and do something if it is found. The first procedure using implicit cursors has to rely on exception handling to determine whether a record was found or not. CREATE OR REPLACE PROCEDURE demo AS v_date DATE; v_its_there BOOLEAN := TRUE; BEGIN BEGIN SELECT order_date INTO v_date FROM orders WHERE order_number = 1; EXCEPTION WHEN no_data_found THEN v_its_there := FALSE; WHEN OTHERS THEN RAISE; END; IF NOT v_its_there THEN do_something; END IF; END; The following code, now using explicit cursors, is easier to follow because the availability of the cursor 's %NOTFOUND attribute makes it obvious what is being checked. There is also no need to embed extra PL/SQL blocks (BEGIN-END) just to handle logic. CREATE OR REPLACE PROCEDURE demo AS CURSOR curs_get_date IS SELECT order_date FROM orders WHERE order_number = 1; v_date DATE; BEGIN OPEN curs_get_date; FETCH curs_get_date INTO v_date; IF curs_get_date%NOTFOUND THEN do_something; END IF; CLOSE curs_get_date; END; Oracle supports the following cursor attributes:
You probably are aware that some of these attributes are available for implicit cursors, as well. However, they lend themselves better to programming logic with explicit cursors, especially when you are using multiple cursors as shown in this brief example: IF curs_get_order%ROWCOUNT = 1 THEN IF curs_get_details%FOUND THEN process_order_detail; 2.2.3. Cursor ParametersAs I mentioned earlier in this chapter, you can further promote cursor reuse in PL/SQL by parameterizing your cursors. Here's my simple order_date procedure with a parameterized cursor: DECLARE CURSOR curs_get_od ( cp_on NUMBER ) IS SELECT order_date FROM orders WHERE order_number = cp_on; v_date DATE; BEGIN OPEN curs_get_od(100); FETCH curs_get_od INTO v_date; CLOSE curs_get_od; END; If later on in the program I wanted to get order 200, 300, and 500, I could simply reopen the cursor. Doing so promotes cursor reuse within the PL/SQL program itself as well as in the shared pool. 2.2.4. Mixing but not MatchingExplicit and implicit cursors do not match up in the shared pool. What do I mean by that? I'll explain with an example. DECLARE CURSOR get_region IS SELECT region_id FROM orders WHERE region_id = 2; v_region NUMBER; BEGIN OPEN get_region; FETCH get_region INTO v_region; CLOSE get_region; SELECT region_id INTO v_region FROM orders WHERE region_id = 2; END; How many shared pool cursors does this produce? The answer is two. SQL_TEXT PARSE_CALLS EXECUTIONS ------------------------------ ----------- ---------- SELECT REGION_ID FROM ORDERS W 1 1 HERE REGION_ID = 2 SELECT REGION_ID FROM ORDERS W 1 1 HERE REGION_ID = 2 Even though these cursors look identical and share the same address in the shared pool, they differ just enough (perhaps the INTO clause?) for Oracle to store two separate ones. The moral is that you can't assume that explicit and implicit cursors will match up in the shared pool. It's best to stick with one way or the other. |