Basic Oracle cursor and basic oracle cursor

Basic Oracle cursor and basic oracle cursor

I. cursor

The cursor is used to process the multi-row records retrieved from the database (using the SELECT statement ). With the cursor, the program can process and traverse the entire record set returned by a retrieval one by one.

To process SQL statements, Oracle allocates a region in the memory, which is the context zone. This area contains the number of rows that have been processed and the pointer to the analyzed statement. The entire area is the data row set returned by the query statement. A cursor refers to a context handle or pointer.

 

Ii. cursor classification:

1. Static cursor: a static cursor is the cursor that knows its SELECT statement during compilation. Static cursors are classified into two types: Implicit cursors and display cursors.

2. Dynamic Cursor: the query used by the user for the cursor cannot be determined until it is run. You can use the REF cursor and the cursor variable to meet this requirement. To use the referenced cursor, the cursor variable must be declared. There are two types of REF cursor: strong type REF cursor and weak type REF cursor.

 

 

Iii. Usage of display cursor:

The show CURSOR is used to process the SELECT statement that returns multiple rows of data. The CURSOR name is passed through CURSOR .... The IS statement IS explicitly assigned to the SELECT statement.

(1) Steps of use;

1) declared CURSOR: CURSOR cursor_name IS select_statement

2) OPEN cursor for query: OPEN cursor_name

3) Put the obtained results into PL/SQL variables;

FETCH cursor_name INTO list_of_variables;

FETCH cursor_name into pl/SQL _record;

4) Close the cursor. CLOSE cursor_name

　　Note: When declaring a cursor,Select_statement cannot contain the INTO clause. When a display cursor is used, the INTO clause is part of the FETCH statement.

Example: Display employee name and salary

-- Use LOOP to traverse the cursor DECLARE v_name emp. ename % TYPE; v_sal emp. sal % TYPE; CURSOR cus_emp is select ename, sal FROM emp; -- declare the cursor begin open cus_emp; -- OPEN the cursor loop fetch cus_emp INTO v_name, v_sal; -- extract the cursor exit when cus_emp % NOTFOUND; dbms_output.put_line ('dide' | cus_emp % ROWCOUNT | 'user: name: '| v_name | 'sal: '| v_sal); end loop; CLOSE cus_emp; -- CLOSE the cursor END;

Display the attributes of the cursor:

% FOUND: returns TRUE only if the DML statement affects one or more rows;

% NOTFOUND: returns TRUE if no row is affected.

% ROWCOUNT: returns the number of rows affected by the DML statement. If the number is not affected, 0 is returned;

% ISOPEN: indicates whether the cursor is opened. After the SQL statement is executed, Oracle automatically closes the SQL cursor. Therefore, the % isopen attribute of the implicit cursor is never false;

 

Another method:

-- Use for to simplify CURSOR traversal declare cursor cus_emp is select ename, sal FROM emp; begin for record_emp IN cus_emp LOOP dbms_output.put_line ('die' | cus_emp % ROWCOUNT | 'User: name: '| record_emp.ename | 'sal:' | record_emp.sal); end loop; END;

Record_emp is the record variable declared by plsql. the attribute of this variable is declared as % ROWTYPE, And the scope is within the FOR loop, that is, it cannot be accessed outside the FOR loop.

Cyclic cursor features:
(1) automatically terminates after all records are extracted from the cursor.
(2) extract and process each record in the cursor.
(3) If the % NOTFOUND attribute returns TRUE after the record is extracted, the loop is terminated. If no row is returned, the loop is not entered.

 

Cursor case:

-- Raise the salary for the employee, which is based on the employee's start time. The salary is increased by every year, and the 100th is capped by DECLARE v_date emp. hiredate % TYPE; v_empno emp. empno % TYPE; v_money NUMBER; CURSOR cur_emp is select empno, hiredate FROM emp; begin open cur_emp; loop fetch cur_emp INTO v_empno, v_date; exit when cur_emp % NOTFOUND; v_money: = 100 * (1990-to_char (v_date, 'yyyy'); IF v_money <1000 then update emp SET sal = sal + v_money WHERE empno = v_empno; else update emp SET sal = sal + 1000 WHERE empno = v_empno; end if; end loop; END;

 

Iv. Implicit cursor:

All implicit cursors are assumed to return only one record.
You do not need to declare, open, or close an implicit cursor. PL/SQL implicitly opens, processes, and closes the cursor. Multiple SQL statements implicit cursor SQL always refers to the results of the last SQL statement, mainly used in the update and delete statements.

Four attributes of an implicit cursor:

 

Attribute	Description
SQL % rowcount	The integer of the number of rows of the affected record (used to determine whether insertion, update, and modification are successful, must be before the comit; otherwise, the result after submission is 0 .)
SQL % found	Affected record true ()
SQL % notfound	The record is not affected. true
SQL % isopen	Whether to enable Boolean value is always false

For example:

DECLARE row_emp emp % ROWTYPE; begin select ename, sal INTO row_emp.ename, row_emp.sal FROM emp WHERE emp. empno = 7369; -- determine whether to check the data if (SQL % ROWCOUNT = 1) THEN dbms_output.put_line ('found '); END IF; -- another method is to determine IF (SQL % Found) THEN dbms_output.put_line ('found '); END IF; dbms_output.put_line ('ename:' | row_emp.ename | 'sal: '| row_emp.sal); END;

 

The cursor is automatically opened, related values are assigned to corresponding variables, and then closed. After execution, the PL/SQL variable rowemp. ename and rowemp. sal have a value.

V. Dynamic Cursor

A static cursor is a query statement that has been determined upon declaration. If you need to dynamically determine the query executed by the cursor at runtime, you need to use a dynamic cursor (REF cursor ).

Dynamic cursors are classified into two types: Strong cursors and weak cursors.

Steps for using dynamic cursors:

1. Declare the Dynamic Cursor type;

2. Open the cursor and specify the cursor query;

3. Extract the cursor.

4. Close the cursor.

Example:

　　Strong type cursor,The cursor declared using return is a strong type cursor. You can only bind rowtype returned by the cursor when binding a query.

-- Strong-TYPE Dynamic CURSOR: query the data in the emp table declare type ref_cur is ref cursor -- DECLARE the cursor type return emp % ROWTYPE; -- refcur_emp ref_cur with the returned value; -- The cursor type object v_emp emp % ROWTYPE; begin open refcur_emp FOR -- binds the cursor to a query statement. Because the statement declares a strong type, only emp can be bound. SELECT * FROM emp; loop fetch refcur_emp INTO v_emp; -- extract the cursor content exit when refcur_emp % NOTFOUND; dbms_output.put_line (refcur_emp % Rowcount | ', name:' | v_emp.ename |: '| v_emp.sal); end loop; CLOSE refcur_emp; END;

　　

　　Weak type cursor: it can be used to bind multiple query results.

Example:

-- Weak type cursor declare type refcur is ref cursor; -- undefined return type is weak type cursor rc refcur; v_name emp. ename % TYPE; v_deptname dept. dname % TYPE; begin open rc for select ename, dname FROM emp e, dept d WHERE e. deptno = d. deptno; -- bind the query loop fetch rc INTO v_name, v_deptname; exit when rc % NOTFOUND; dbms_output.put_line ('name: '| v_name | 'deptname:' | v_deptname ); end loop; CLOSE rc; END;

 

Example:

-- Bind the cursor declare type refcur is ref cursor; rc refcur; v_tablename VARCHAR2 (10): = '& tab'; v_id NUMBER; v_name VARCHAR2 (20) based on the input content ); begin if (v_tablename = 'E') then open rc for select e. empno, e. ename INTO v_id, v_name FROM emp e; dbms_output.put_line ('========== employee information ======= '); elsif (v_tablename = 'D') then open rc for select d. deptno, d. dname INTO v_id, v_name FROM dept d; dbms_output.put_line ('==== === Department Information ==============='); ELSE dbms_output.put_line ('input error, please input e or d! '); RETURN; end if; loop fetch rc INTO v_id, v_name; dbms_output.put_line (' # '| rc % Rowcount | 'id:' | v_id | 'name: '| v_name); exit when rc % NOTFOUND; end loop; END;