Detailed description of Oracle CURSOR instance, oraclecursor

Detailed description of Oracle CURSOR instance, oraclecursor
CURSORI,Cursor Overview

Cursor is a data buffer provided by the database system in the memory. It stores the execution results of SQL statements.

Each cursor has a name. You can use SQL statements to obtain records from the cursor one by one and assign them to variables for further processing.

Function: used to locate rows in the result set and traverse the result set.

Cursor generation time: When a dml SQL statement is executed;

Cursor for storing: result set

Is there a name for the cursor? Yes, it is named by an SQL statement or a user.

How to operate a cursor: extract records from the cursor one by one using the FETCH statement, and assign the records to the variables for further processing.

You can also open several cursors: multiple, and the specific quantity is determined by the database initialization parameter OPEN_CURSOR.

II, Cursor type

Cursors include static cursors (implicit and explicit) and REF cursors (+ cursors)

1. Cursor attributes

% Found SQL statement affects one or more rows to true;

% Notfound SQL statement does not affect any row to true (commonly used, exit if it is not found to be T)

% ROWCOUNT number of rows affected by SQL statements;

% Whether the ISOPEN cursor is enabled, always false.

2. Static cursor

A static cursor is a cursor that is already defined (static) in the result set.

Static cursor points: Implicit cursor and display cursor.

2.1 implicit cursor

In PL/SQL, the implicit cursor is automatically created, declared, opened, and closed when the dml SQL (Insert/Delete/Update/Select) Statement is executed. Its name is SQL (note: all implicit cursors are called "SQL ");

Example:

Use the attributes of the cursor.

BEGIN

UPDATE emp SET sal = 5000 WHERE empno = 7369;

If SQL % FOUND THEN

DBMS_OUTPUT.PUT_LINE ('table updated ');

End if;

END;

Tip:

In Java, if you add, delete, and modify a table, n is returned, indicating how many rows in the database are affected.

We use n> 0 or n = 0 to determine whether the SQL code is successfully executed.

This n is the SQL % ROWCOUNT attribute.

2.2 display cursor

The display cursor is used to return multiple rows of queries when processing the SELECT statement;

The display cursor is not used during addition, deletion, modification, and deletion;

The display cursor must be manually declared, opened, extracted, and closed.

Example:

LShow cursor operation

DECLARE

-- Declared cursor: divides the storage area. Note that the Select statement is not executed at this time.

CURSOR c_name IS

SELECT sal FROM e-mapreduce WHERE e-mapreduce = 7369;

My_sal emp. sal % TYPE;

BEGIN

-- Open the cursor: Execute the select statement to obtain the result set and store it in the cursor. The cursor points to the result set header instead of the first record.

OPEN c_name;

-- Extract record: move the cursor to retrieve a record

FETCH c_name INTO my_sal;

DBMS_OUTPUT.PUT_LINE (my_sal );

-- Close the cursor: place the cursor in the buffer pool. The resource is not completely released and can be reopened.

CLOSE c_name;

END;

LDisplay cursor with Parameters

DECLARE

CURSOR c_name (dno NUMBER) IS

SELECT * FROM emp WHERE deptno = dno;

My_a emp % ROWTYPE;

BEGIN

OPEN c_name (10); -- OPEN the cursor and pass the value

LOOP

FETCH c_name INTO my_a;

Exit loop c_name % NOTFOUND;

DBMS_OUTPUT.PUT_LINE ('name: '| my_a.ename );

End loop;

CLOSE c_name;

END;

LFor Loop cursor

Purpose: simplify the cursor processing code (simplify opening, extracting, and disabling ).

Basic Syntax:

FOR r_index IN cursor_name LOOP

......

End loop;

Example:

For Loop cursor operation

DECLARE

CURSOR c_name IS

SELECT * FROM emp;

BEGIN

FOR I IN c_name LOOP

DBMS_OUTPUT.PUT_LINE (I. ename | ''| I. job );

End loop;

END;

Note: Like a foreach statement, I is of the row type (% rowtype) by default and is automatically enabled, extracted, and disabled.

LFor Loop cursor with Parameters

DECLARE

CURSOR c_name (dno number) IS

SELECT * FROM emp WHERE deptno = dno;

BEGIN

FOR I IN c_name (30) LOOP

DBMS_OUTPUT.PUT_LINE (I. ename | ''| I. job );

End loop;

END;

LNesting of cursors

A fixed-name cursor is a nested cursor in the cursor.

Example:

Lists the personnel information of all departments.

The results are as follows:

Department No.: 10 department name :****

No.: ** name :**

No.: ** name :**

......

Department No.: 20 Department name :****

No.: ** name :**

No.: ** name :**

......

Analysis:

1. Create a department cursor and list the department information first;

2. Create an employee table cursor to read the Department Information cyclically.

Code:

DECLARE

CURSOR mydc IS

SELECT * FROM dept;

Mydr dept % ROWTYPE; -- declare the dept Department table object

CURSOR myec (dno NUMBER) IS

SELECT * FROM emp WHERE empno = dno;

Myer emp % ROWTYPE; -- declare the emp employee table object

BEGIN

-- Open the Department result set cursor first

OPEN mydc;

LOOP

FETCH mydc INTO mydr;

Exit loop mydc % NOTFOUND; -- EXIT the LOOP after extraction

DBMS_OUTPUT.PUT_LINE ('department No.: '| mydr. deptno | 'department name:' | mydr. dname );

-- Then open the employee result set cursor and enter the Department number

OPEN myec (mydr. deptno );

LOOP

FETCH myec INTO myer;

Exit loop myec % NOTFOUND;

DBMS_OUTPUT.PUT_LINE ('No.:' | myer. empno | 'name: '| myer. ename );

End loop;

CLOSE myec;

End loop;

CLOSE mydc;

END;

-- Simplify the use of the For Loop

DECLARE

CURSOR mydc IS

SELECT * FROM dept;

CURSOR myec (dno number) IS

SELECT * FROM emp WHERE empno = dno;

BEGIN

-- Traverses the Department result set cursor

FOR I INT mydc LOOP

DBMS_OUTPUT.PUT_LINE ('department No. '| I. deptno | 'department name:' | I. dname );

-- Traverses the employee result set cursor and transmits the Department number

FOR j INT myec (I. detpno) LOOP

DBMS_OUTPUT.PUT_LINE ('number' | j. empno | 'name: '| j. ename );

End loop;

End loop;

END;

3. REF cursor

A ref cursor is also called a dynamic cursor, which is generated when a dynamic SQL statement is executed.

The REF cursor is also called the cursor type, and the cursor variable is the cursor of this type.

3.1 create REF cursor + cursor variable

Basic Syntax:

-- Create a REF cursor

TYPE ref_cursor_name IS REF CURSOR

[RETURN type];

-- Declare REF cursor variable

Cursor variable ref_cursor_name;

Example:

Create REF cursor types

TYPE ref_name is ref cursor; -- defines the weak cursor type. Its name IS ref_name.

C_name ref_name;

3.2 REF cursor category

Strong type: RETURN type

Weak type: RETURN type is not included.

Relatively speaking, weak types are more flexible.

Example:

Declare strong type REF cursor + cursor variable

DECLARE

TYPE c_type IS REF CURSOR

RETURN emp % ROWTYPE;

C_name c_type;

3.3 open the cursor variable

OPEN cursor_name FOR query result set;

Example:

Use REF cursor + cursor variable to display data

DECLARE

TYPE my_t IS REF CURSOR

RETURN emp % ROWTYPE; -- declare a cursor type

My_t; -- defines a cursor variable of the REF type.

Myr emp % ROWTYPE;

BEGIN

OPEN MCM

SELECT * FROM emp;

LOOP

FETCH gem-into myr;

Exit when PC3 % NOTFOUND;

DBMS_OUTPUT.PUT_LINE (myr. ename );

End loop;

Close acc;

END;

4. Advantages and limitations of scalar Variables

4.1 powerful cursor variables allow you to simplify data processing;

4.2 advantages of cursor variables:

A. the result set can be extracted from different select statements;

B. It can be passed as a process parameter;

C. All attributes of a cursor can be referenced;

D. assign values;

4.3 restrictions on cursor variables:

A. the cursor variable cannot be declared in the package;

B. The for update clause cannot be used with the cursor variable;

C. Comparison operators cannot be used.

III, Dynamic statement concatenation: n

Set a cursor variable to execute dynamic SQL statements.

Common knowledge points of database administrators and designers.

Basic Syntax:

OPEN cursor name FOR 'select... >: 1... ';

USEING variable name;

Example 1:

OPEN cursor_name

'Select * FROM emp WHERE sal>: 1 order by sal DESC'

USEING p_sal;

Explanation:

-- Set the placeholder: sal>: 1 Dynamic concatenation (equivalent? Placeholder)

-- Fill placeholder: place the value of the variable p_sal at the ": 1" position in the SELECT statement.

-- If there are multiple Dynamic joining points, set: 2,: 3 ,......, At the same time, variables after using are separated by commas.

Example 2:

DECLARE

R_emp emp % ROWTYPE;

-- Define REF cursor and cursor variable

TYPE c_type is ref cursor;

Cur c_type;

P_salary NUMBER;

BEGIN

P_salary: = 2500;

-- Open the cursor

OPEN cur

'Select * from emp where sal>: 1 order by sal desc'

-- Fill placeholder

USING p_salary;

DBMS_OUTPUT.PUT_LINE ('salary is higher than '| p_salary |' employees :');

LOOP

-- Extract data from the cursor to the variable r_emp

FETCH cur INTO r_emp;

Exit when cur % NOTFOUND;

DBMS_OUTPUT.PUT_LINE ('No.:' | r_emp.empno | 'name: '| r_emp.ename | 'salary:' | r_emp.sal );

End loop;

-- Close the cursor

CLOSE cur;

END;