Cursor
Cursors are used to process multiple rows of records retrieved from the database (using SELECT statements). Using cursors, programs can process and traverse the entire recordset returned once retrieved one by one.
To process the SQL statement, Oracle will allocate a region in memory, which is the context area. This section contains the number of processed rows, pointers to the analyzed statement, and the entire section is the data row set returned by the query statement. The cursor refers to the handle or pointer of the context area.
Two. Classification of cursors:
1、Static cursor: the static cursor is the cursor that knows its SELECT statement at compile time. There are two types of static cursors, implicit cursors and display cursors.
2、Dynamic cursor: The query that the user uses for the cursor can not be determined until it runs, and can be met with the REF cursor and cursor variables. In order to use reference cursors, you must declare cursor variables. There are two types of REF cursors: strongly typed REF cursors and weak type REF cursors.
Three. Show the usage of cursor:
The cursor is used to process SELECT statements that return multiple rows of data, and the cursor name is passed through CURSOR. The.IS statement is displayed to the SELECT statement.
（(1) use steps;
1）Declare cursor:CURSOR cursor_name IS select_statement
2）Open cursors for queries:OPEN cursor_name
3）The result is put in the PL/SQL variable.
FETCH cursor_name INTO list_of_variables;
FETCH cursor_name INTO PL/SQL_record;
4）Close the cursor.CLOSE cursor_name
Note: when vernier is declared,select_statementCan not contain the INTO clause. When displaying cursors, the INTO clause is part of the FETCH statement.
Example: show employee’s name and salary
--Traversing cursors using LOOPDECLARE
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
CURSOR cus_emp IS
SELECT ename,sal FROM emp; --declare cursorBEGIN
OPEN cus_emp; --open LOOP
FETCH cus_emp INTO v_name,v_sal; --Extract cursorEXIT WHEN cus_emp%NOTFOUND;
dbms_output.put_line('The first'||cus_emp%ROWCOUNT||'User: name:'||v_name||' sal:'||v_sal);
END LOOP;
CLOSE cus_emp; --Close the cursorEND;
%FOUND:Only when the DML statement affects one row or more rows, it returns TRUE.
%NOTFOUND：No row is affected, then TRUE is returned.
%ROWCOUNT：Returns the number of rows affected by the DML statement, and returns 0 if there is no effect.
%ISOPEN：Returns whether the cursor is open, and after executing SQL, Oracle automatically closes the SQL cursor, so the% isopen property of the implicit cursor is never false;
Another way:
--Using for to simplify cursor traversalDECLARE
CURSOR cus_emp IS
SELECT ename,sal FROM emp;
BEGIN
FOR record_emp IN cus_emp
LOOP
dbms_output.put_line('The first'||cus_emp%ROWCOUNT||'User: name:'||record_emp.ename||' sal:'||record_emp.sal);
END LOOP;
END;
--Raise salary for employees, increase salary according to staff entry time, add 1001000 capping per year.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;
Grammar:
CURSOR cursor_name[(parameter[,parameter],…)] IS select_statement;
The syntax for defining parameters is as follows: Parameter_name [IN] data_type[{: =|DEFAULT} value]
Example: Receive the user's input department number, use for loop and cursor, print out all the information of all employees in this department (using circular cursor)
DECLARE
CURSOR C_DEPT(P_DEPTNO NUMBER) IS
SELECT * FROM EMP WHERE EMP.DEPTNO = P_DEPTNO;
R_EMP EMP%ROWTYPE;
BEGIN
FOR R_EMP IN C_DEPT(30) LOOP
DBMS_OUTPUT.PUT_LINE('Employee number:' || R_EMP.EMPNO || 'Employee name:' || R_EMP.ENAME ||
'Wages:' || R_EMP.SAL);
END LOOP;
END;
Four. Implicit cursors:
All implicit cursors are assumed to return only one record.
When using implicit cursors, users do not need to declare, open and close. PL/SQL implicitly opens, processes, and then turns off the cursor. Implicit cursor SQL for multiple SQL statements always refers to the result of the last SQL statement, mainly used in update and delete statementsUp.
Four properties of implicit cursors:
| attribute | Explain |
| SQL%rowcount | The number of rows in the affected record integer (used to determine whether the insert, update, modification is successful, must be before comit, otherwise the submitted result is 0.) |
| SQL%found | It affects the record true (). |
| SQL%notfound | No impact on recording true |
| SQL%isopen | Whether to open 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;
--Deciding whether to find dataif(SQL%ROWCOUNT=1) THEN
dbms_output.put_line('Eureka');
END IF;
--Another way of judgingIF(SQL%Found) THEN
dbms_output.put_line('Eureka');
END IF;
dbms_output.put_line('ename:'||row_emp.ename||' sal:'||row_emp.sal);
END;
Five: dynamic cursor
A static cursor is a query statement that is declared to have been determined. If the user needs to dynamically determine the query to be executed by the cursor at run time, he needs to use a dynamic cursor (REF cursor).
Dynamic cursors can be classified into two categories:Strongly typed cursors andWeak type cursors.
Dynamic cursor usage steps:
1、Declare dynamic cursor type;
2、Open cursor and specify cursor query;
3、Extract cursors.
4、Close the cursor.
Example:
Strongly typed cursorsA vernier using return declaration is a strongly typed cursor. Binding queries on cursors can only bind the type rowtype returned by cursors.
--Strong type dynamic cursor, querying data in EMP table.DECLARE
TYPE ref_cur IS REF CURSOR --Declare cursor typeRETURN emp%ROWTYPE; --Strong type dynamic cursors with return valuesRefcur_emp ref_cur;--Cursor type objectV_emp EMP%ROWTYPE;
BEGIN
OPEN refcur_emp FOR --Bind the cursor to a query statement, because the declaration is strongly typed, so it can only bind EMP.SELECT * FROM emp;
LOOP
FETCH refcur_emp INTO v_emp; --Extract cursor contentEXIT WHEN refcur_emp%NOTFOUND;
dbms_output.put_line(refcur_emp%Rowcount||'、name:'||v_emp.ename||' sal:'||v_emp.sal);
END LOOP;
CLOSE refcur_emp;
END;
--Weak type cursorDECLARE
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; --Binding 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;
--Binding cursors based on input contentDECLARE
TYPE refcur IS REF CURSOR;
rc refcur;
v_tablename VARCHAR2(10) := '&tab';
v_id NUMBER;
v_name VARCHAR2(20);
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('=========Departmental 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;
Six, the difference between dynamic cursor and static cursor:
1、Static cursors are static definitions, and REF cursors are dynamic associations.
2、Using REF cursors requires REF cursor variables.
3、REF Cursors can be passed as parameters, while static cursors are impossible.