Cursors1 a cursor is a vehicle for extracting data from an Oracle database. Cursors determine the columns and objects
Using Explicit Cursors Versus Implicit Cursors
Download 392.79 Kb.
|
- Navigate this page:
- 2.1. Whats the Difference
- 2.3. Cursor Parameters
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.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;
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;
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;
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;
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;
As 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;
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;
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. Directory: 2015 2015 -> 587 Return function, r i(X) r i(0) r i(1) r i(2) r i(3) 1 0 2 4 6 Thermal Station, I 2 0 1 5 6 3 0 3 5 6 10 2015 -> Fall 2013 Spring 2014 Program Data: Standard 1 Exhibit 4d 2015 -> Police Militarization 2015 -> Medical Radiography Program 2015 -> 2015 ihbb championships: hs history Bowl Round 4 – Prelims First Quarter 2015 -> Desk review yemen 2015 -> Wrtp/big step · 3841 W. Wisconsin Ave., Milwaukee, wi 53208 2015 -> 1. naclo 2015 – North American Computational Linguistics Olympiad 2015 -> Rao bulletin 1 December 2015 html edition this bulletin contains the following articles 2015 -> Volume Ⅴ (2015) On The 2015 China Victory Day Parade Download 392.79 Kb. Share with your friends:
|