Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use cursors in the Oracle database. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

What is a cursor?

① retrieves the result set from the table and points to the mechanism by which one record at a time interacts.

Operations in an ② relational database are performed on a complete rowset.

The set of rows returned by the SELECT statement includes all rows that meet the conditions listed in the statement's WHERE clause. The complete rowset set returned by this statement is called the result set.

It is not always effective for applications, especially interactive and online applications, to treat the complete result set as a unit.

These applications need a mechanism to process one or more consecutive lines at a time. Cursors are an extension of the result set that provides this mechanism.

Cursors are implemented through a cursor library. Cursor libraries are software that is often implemented as part of a database system or data access API to manage the properties (result sets) of data returned from a data source. These properties include concurrency management, location in the result set, the number of rows returned, and whether it can be moved forward and / or backward in the result set (scrollable).

Cursors track the location of the result set and allow multiple operations on the result set row by row, either to return to the original table or not to the original table in the process.

In other words, cursors conceptually return the result set based on the tables of the database.

Because it indicates the current location in the result set, just as the cursor on the computer screen indicates the current location, the cursor gets its name.

What is the purpose of cursors?

① specifies the location of a specific row in the result set.

② retrieves one or more consecutive rows based on the current result set location.

③ modifies the data in the row at the current position of the result set.

④ defines different levels of sensitivity to data changes made by other users.

⑤ can access the database programmatically.

Introduction

This section explains cursors in Oracle in detail. The source of the example in this section is the emp table dept under the scott user in Oracle:

I. Vernier:

1. Concept:

The essence of a cursor is a result set resultset, which is mainly used to temporarily store blocks extracted from the database.

Second, the classification of cursors

1. Explicit cursor:

Defined by the user, the required operations: define cursors, open cursors, extract data, close cursors, mainly used for the processing of query statements.

Attribute:% FOUND% NOTFOUND% ISOPEN% ROWCOUNT

Example: print employee information for emp table

DECLARE CURSOR emp_cursor IS SELECT empno,ename,job FROM emp; v_empno emp.empno%TYPE; v_name emp.ename%TYPE; v_job emp.job%TYPE;BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO vposts empnograd vsignnameGrampen; DBMS_OUTPUT.PUT_LINE ('employee number is:' | v_empno | | 'name is' | | v_name | | 'position:' | | v_job); EXIT WHEN emp_cursor%NOTFOUND; END LOOP; CLOSE emp_cursor;END

Here we strictly follow the writing rules for displaying cursors: DECLARE emp_cursor defines cursors OPEN emp_cursor opens cursors FETCH emp_cursor INTO... Extract data CLOSE emp_cursor closes the cursor, because the extracted data belongs to multiple lines, so it can be printed through the loop loop.

Example2: check if the cursor is open. If it is opened, it shows the number of extracted rows.

DECLARE CURSOR emp_cursor IS SELECT empno,ename,job FROM emp; v_empno emp.empno%TYPE; v_name emp.ename%TYPE; v_job emp.job%TYPE;BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO vposts empnograd vsignnamejob.EXIT WHEN emp_cursor%NOTFOUND; END LOOP; IF emp_cursor%ISOPEN THEN DBMS_OUTPUT.PUT_LINE ('cursor opened'); DBMS_OUTPUT.PUT_LINE ('read' | emp_cursor%ROWCOUNT | | 'line') ELSE DBMS_OUTPUT.PUT_LINE ('cursor not opened'); END IF; CLOSE emp_cursor;END

The% ISOPEN attribute determines whether the cursor is open, and the% ROWCOUNT determines the number of rows.

2, implicit cursor: defined by the system and create a work area for it, and implicit definition open extraction off, implicit cursor name is' SQL', attribute and display cursor the same, mainly used for single-line select statements or dml operations to deal with. Example: another user enters the employee number to modify the employee's salary. If it is successful, print out the success mark.

In order not to change the original table as much as possible, create a new table with the same emp_new as the original table:

CREATE TABLE emp_newASSELECT * FROM emp;BEGIN UPDATE emp_new SET sal = sal+500 WHERE empno=&empno; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE ('successful modification'); COMMIT; ELSE DBMS_OUTPUT.PUT_LINE ('modification failed'); ROLLBACK; END IF;END

Note here that the operation to be done will be submitted by commit after adding, deleting and modifying. If the operation fails, rollback will roll back the operation.

3. Parameter cursor:

Cursors with parameters are added when defining cursors, which can cooperate with the cursor for loop to quickly find the required data. Let's talk about the cursor for loop first.

A. Cursor FOR loop:

Implied implementation of the open extraction off data, the code is much simpler. Expression:

FOR table_record IN table_cursor LOOP

STATEMENT

END LOOP

Example: print out employee information using cursor For loops:

DECLARECURSOR emp_cursor IS SELECT empno,ename,job FROM emp;BEGIN FOR emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE ('employee number:' | emp_record.empno | | 'employee name' | | emp_record.ename | | 'employee position' | | emp_record.job); END LOOP;END

Here, the cursor FOR loop omits the naming and assignment of variables to the fetched data, and if you print it all, you don't have to write loop conditions, so the code is much simpler.

If you want to make the code more concise, you can remove the declaration of the cursor and introduce the subquery, as follows.

BEGIN FOR emp_record IN (SELECT empno,ename,job FROM emp) LOOP DBMS_OUTPUT.PUT_LINE ('employee number:' | emp_record.empno | | 'employee name' | | emp_record.ename | | 'employee position' | | emp_record.job); END LOOP;END

The code is more concise and the result is the same. It's a bit like implicit cursors, but implicit cursors are mainly used for single-line select and dml statements, so pay attention to the difference between them.

Let's continue with an example of a parameter cursor:

Example: enter the department number to print employee information:

DECLARECURSOR emp_cursor (dno NUMBER) IS SELECT empno,ename,job FROM emp WHERE deptno=dno;BEGIN FOR emp_record IN emp_cursor (& dno) LOOP DBMS_OUTPUT.PUT_LINE ('employee number' | | emp_record.empno | | 'name' | | emp_record.ename | | 'position' | | emp_record.job); END LOOP;END

Since there are parameters, then there must be a declaration of the cursor, in combination with the cursor FOR loop to quickly find the required data.

Matters needing attention when using cursors to modify data

1. When using cursors to modify data, in order to prevent others from modifying the data when they operate the data, oracle provides a for update clause to lock it.

At the same time, when you use update or delete, you must use the where current of+name_cursor statement and remember to submit at the end. If it is a cascading operation, you can use for update of to lock related tables.

Example1: an increase of 1000 for employees whose positions are PRESIDENT and 500for those with MANAGER

CREATE TABLE emp_newASSELECT * FROM emp;DECLARECURSOR empnew_cursor IS SELECT ename,job FROM emp_new FOR UPDATE;BEGIN FOR empnew_record IN empnew_cursor LOOP DBMS_OUTPUT.PUT_LINE ('name' | | empnew_record.ename | | 'position' | | empnew_record.job); IF empnew_record.job='PRESIDENT' THEN UPDATE emp_new SET sal=sal+1000 WHERE CURRENT OF empnew_cursor; ELSIF empnew_record.job='MANAGER' THEN UPDATE emp_new SET sal=sal+500 WHERE CURRENT OF empnew_cursor; END IF; END LOOP; COMMIT;END

On how to use cursors in the Oracle database to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.