Use Cursor

Source: Internet
Author: User

Label:

declare : CURSOR cursor_name is

select_statement;

Open : Open cursor_name

If the query returns no rows when the cursor is opened, PL/SQL does not raise an exception. However, you can test the

Status of the cursor after a fetch using the Sql%rowcount cursor attribute.

fetch : Fetch cursor_name into [Variable1, variable2 ...] | Record_name; For example:

LOOP

FETCH emp_cursor into V_empno, v_ename;

EXIT when ...;

--Process The retrieved data

END LOOP;

close : Close cursor_name;

Explicit Cursor Attributes

%isopen Boolean (type)

%notfound Boolean (type) evaluates to TRUE if the most recent fetch does not return a row

%found Boolean (type) evaluates to TRUE if the most recent fetch returns a row

%rowcount number evaluates to the total number of rows returned so FAF

IF not Emp_cursor%isopen Then

OPEN Emp_cursor;

END IF;

Use the%rowcount cursor attribute to retrieve an exact number of rows.

Use the%notfound cursor attribute to determine when you exit the loop.

EXIT when emp_cursor%notfound ORemp_cursor%notfound is NULL;

For Record_name in cursor_name LOOP

Statement 1;

Statement 2;

END LOOP;

Record_name is the name of the implicitly declared record.

For Emp_record (without definition, auto-defined) in Emp_cursor LOOP

IF emp_record.department_id =

... statement 1

END LOOP;

Use subqueries to implement

For Emp_record in (SELECT last_name, department_id from Employees) LOOP

IF emp_record.department_id =

END LOOP; (You can also use subqueries, so you don't need to define cursors.)

Cursor with parameters
Cursor cursor_name
[(parameter_name datatype,...)]
is
select_statement;
Open cursor_name (Parameter_value,...);
Example:
declare 
CURSOR emp_cursor
(p_deptno number, P_job VARCHAR2) is--no need to specify parameter type size
SELECT employee_id, last_name
from Employees
WHERE department_id = P_deptno
and job_id = P_job;
BEGIN
OPEN emp_cursor ("Sa_rep");
...
CLOSE Emp_cursor;
OPEN emp_cursor (' It_prog ');
...
END;
for UPDATE in cursor
WHERE current of cursor; (cursor must be locked for UPDATE)

For example:
DECLARE
CURSOR Sal_cursor is
SELECT department_id,last_name,salary
From Employees
WHERE department_id = 60
For UPDATE [of salary] NOWAIT; of salary (Salary column name, optional)
BEGIN
For Emp_record in Sal_cursor
LOOP
IF Emp_record.salary <
UPDATE Employee
SET salary = Emp_record.salary * 1.1
WHERE Current of Sal_cursor; Indicates the meaning of the current line
END IF;
END LOOP;
END;

Blog

1. What is a cursor

To handle SQL statements, Oracle must allocate a memory area, which is the context area. The context area contains the information necessary to complete the processing, including the number of rows to be processed by the statement, a pointer to a representation of the statement being parsed, and the active set of the query (which is the collection of rows returned by the query).

A cursor is a handle (handle) or pointer to a context area. With cursors, the PL/SQL program can control the context area and what happens in the context area when the statement is processed.

2. An explicit cursor

Processing an explicit cursor includes four PL/SQL steps

1) Declaring cursors

2) Open a cursor for a query

3) Extract the result (fetch) into the PL/SQL variable

4) Close the cursor

There are two forms of a FETCH statement

1) Fetch cursor_name into list_of_variables;

2) Fetch cursor_name into Pl/sql_record;

Here cursor_name identifies the cursor that has been declared and opened, and List_of_variables is a list of the PL/SQL variables that have been declared (the variables are separated by commas), and Pl/sql_record is a PL/SQL record that has been declared.

Four properties of a cursor

1)%found a Boolean property. If the previous FETCH statement returns a row, it returns true, otherwise it returns false. If%found is set before the cursor is opened, ORA-1001 (invalid cursor) is returned.

2)%notfound behaves in the opposite way as above%found. If the previous FETCH statement returns a row, then%notfound returns false. %notfound returns true only if the current FETCH statement does not return any rows.

3)%isopen This Boolean property is used to determine whether the associated cursor is open. Returns true if it is turned on, otherwise false.

4)%rowcount This number property returns the number of rows returned by the cursor so far. If a reference is made when the relevant cursor is not open, a ORA-1001 error is returned.

parameterized cursors

3. An implicit cursor

It hurts to talk about it ... Asked me the day before the interview. Shows what the difference between a cursor and an implicit cursor is, and I just have an impression that the specific concept is not clear. I'll just say that the cursor is named, and the implicit cursor does not have a name.

Shows that the cursor is used to handle a SELECT statement that returns more than one row, as we have seen in the previous chapters. However, all SQL statements are executable inside the context area, so there is a cursor pointing to the context area. This cursor is called a SQL cursor.

Unlike an explicit cursor, a SQL cursor is not opened and closed by a program. PL/SQL implicitly opens the SQL cursor, processes the SQL statement in it, and then closes the cursor.

Implicit cursors are used to handle inserts, UPDATE, delete, and single-row select ... INTO statement. The open, fetch, and close commands are irrelevant because SQL cursors are turned on and off through the PL/SQL engine. However, cursor properties can be applied to SQL cursors.

An implicit cursor

No_data_found and%notfound

No_data_found exception is only select ... Into statement, which is triggered when the WHERE clause of the query does not find any rows. The%notfound property is set to True when the WHERE clause of an explicit cursor does not find a row. If the WHERE clause of the UPDATE and DELETE statements does not find any rows, Sql%notfound is set to true without triggering no_data_found.

4.SELECT for UPDATE cursors

In most cases, the processing done in the extraction loop modifies the rows retrieved by the cursor. PL/SQL provides a convenient syntax for such processing.

This method contains two parts--the FOR UPDATE clause in the Cursor Declaration section and the WHERE CURRENT OF clause in the UPDATE or DELETE statement

1) for UPDATE

The FOR UPDATE clause is part of the SELECT statement. It is the last clause of the statement, after the ORDER BY clause (if any).

The syntax is:

SELECT ... From ... For Update[of column_reference] [NOWAIT]

Typically, the select operation does not perform any lock settings on the row being processed, which allows other sessions connected to the database to change the data being selected. However, the result set is still consistent. When the activity set is determined, Oracle intercepts a snapshot of the table at the time the open is executed. Any changes that were committed before this time will be reflected in the active set. Any changes that are made after this time, even if they have been committed, are not reflected unless the cursor is reopened (which results in a re-evaluation of the result set). This is actually read consistency processing (read-consistency process). However, if the FOR UPDATE clause is used, a mutex (exclusive lock) is added to the corresponding row of the active set before open returns. These locks prevent other sessions from modifying the rows in the active set until the entire transaction is committed.

If another session has already added a lock to the rows in the active set, the Select for update operation waits for the other session to release the locks before proceeding with its own operation. This wait is a--select for update with no timeout limit pending indefinitely until the other session releases the lock. If you are dealing with this situation, you need to use the NOWAIT clause. If these rows are locked by another session, then Open will return immediately, triggering an Oracle error:

Ora-54:resource busy and acquire with NOWAIT specified

In this case, you may want to retry the open later or change the active set to fetch rows that are not locked.

2) WHERE Current OF

If you use the WHERE CURRENT OF clause to declare a cursor, you can use the WHERE CURRENT OF clause in the UPDATE and DELETE statements.

The syntax for this clause is:

WHERE Current OF cursor

Here the cursor is the name of the cursor declared with the FOR UPDATE clause. The WHERE current OF clause evaluates the row that was just retrieved by the cursor.

For update

Note that the UPDATE statement only updates the columns that are listed at the cursor declaration's for UPDATE clause. If no columns are listed, all columns can be updated.

3) Commit and extract operations

We can note that in the example above, commit is done after the fetch loop is complete, because commit releases all locks held by the session. Because the FOR UPDATE clause obtains a lock, commit releases the locks. When the lock is released, the cursor is invalid. All subsequent operations will return an Oracle error.

Ora-1002:fetch out of sequence

Error

Thus, if there is a commit statement in the Select for update fetch loop, any fetch operation after the commit statement will be invalid. Therefore, we do not recommend using a COMMIT statement inside a loop. This problem does not occur if the cursor is not defined as a select for UPDATE.

Of course, what if you want to update the rows that you just extracted from the cursor and use commit inside the fetch loop? Where current of is not available because cursors cannot be defined using the FOR UPDATE clause. However, you can use the primary key of the table in the WHERE clause of the update. As shown in the following example

This example basically simulates the WHERE CURRENT OF clause, but does not create a lock on the row of the active set.

5. Cursor variables

At this point all the explicit cursors we encounter are static cursors (static cursor)-the cursor is associated with an SQL statement, and this statement is already known when compiling the block. On the other hand, a cursor variable can be run again at a time associated with a different SQL statement.

A cursor variable is a reference type. The syntax for defining a cursor variable type is as follows:

Type type_name is REF CURSOR return Return_type

Here type_name is the name of the new reference type, Return_type is a record type that indicates the type of selection list that is ultimately returned by the cursor variable.

The return type of a cursor variable must be a record type. It can be explicitly declared as a user-defined record, or implicitly using%rowtype.

Restricted and unrestricted cursor variables

The cursors described earlier are limited-they are only declared as a specific return type. When you open the variable later, you must open it for a specific query so that the query's selection list matches the return type of the cursor, otherwise, a predefined error rowtype_mismatch is triggered.

The non-restricted cursor variable does not have to have a return clause, and when a non-restricted cursor variable is opened later, it can be opened for any query.

Examples of non-restricted cursors CREATE OR REPLACE PROCEDURE showcursorvariable/* Demonstrates the use of a cursor variable on the server      .  If p_table is ' classes ' and then information from the classes Table was inserted into temp_table. If p_table is ' rooms ' then information from rooms is inserted.      */(P_table in VARCHAR2) AS/* Define the cursor variable type */Type t_classesrooms is REF cursor; /* and the variable itself.      */V_cursorvar T_classesrooms; /* Variables to hold the output.    */V_department Classes.department%type;    V_course Classes.course%type;    V_roomid Rooms.room_id%type;  V_description Rooms.description%type;    BEGIN-Based on the input parameter, open the cursor variable.    IF p_table = ' classes ' then OPEN V_cursorvar for SELECT Department, course from classes;    elsif p_table = ' rooms ' then OPEN v_cursorvar for SELECT room_id, description from rooms; ELSE/* Wrong value Passed as input-raise an error */raise_application_error ( -20000, ' input must be ' ' classes ' or ' rooms ');      END IF;  /* Fetch loop. Note the EXIT when clause after the Fetch-with PL/SQL 2.3 We can use the cursor attributes with the cursor variables.        */LOOP IF p_table = ' classes ' then FETCH V_cursorvar into V_department, v_course;          EXIT when V_cursorvar%notfound;      INSERT into Temp_table (Num_col, Char_col) VALUES (V_course, v_department);        ELSE FETCH V_cursorvar into V_roomid, v_description;          EXIT when V_cursorvar%notfound;      INSERT into Temp_table (Num_col, Char_col) VALUES (V_roomid, SUBSTR (v_description, 1, 60));    END IF;      END LOOP; /* Close the cursor.      */CLOSE V_cursorvar;  COMMIT;  END showcursorvariable;   /

Use Cursor

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: