Oracle--Cursors

In order to handle the information obtained by the SQL statements, Oracle must allocate a region called the context to handle the above information. A cursor is a handle or pointer to a context, and PL/SQL can control the information that is obtained from the context area through a cursor.

1. Cursors are used differently for different SQL statements

SQL statement Cursors

Non-query-statement implicit cursors

The result is a single-line query statement that implicitly or displays a cursor

The result is that multiple rows of query statements display the

2. Working with display cursors

declare        --Defining cursor Types          cursor c_cursor is select user_name,age from t_user;         --defines two variables         v_username t_ user.user_name%type;        v_age             t_user.age%type; begin         --Opening Cursors         open c_cursor;         --extracts the cursor data, which is the data row in the result set, and puts it into the specified output variable         fetch c _cursor into v_username,v_age;        --here can understand c_ The cursor is select user_name,age from t_user        --, which is C below. _cursor can be used Select user_name,age froM t_user instead of         --when the SELECT statement gets the value, it loop         while c_cursor%found loop                 dbms_output.put_line (' Perform your own operation ');                 fetch c_cursor into v_ username,v_age;        end loop;         --Closing Cursors         close c_cursor;         end;

3. Cursor Properties

%found Boolean property, returns True when the last read record was successful

%notfound is the opposite of%found.

%isopen Boolean property that returns True when the cursor is open

%rowcount Numeric property, returns the number of records that have been read from the cursor

For example: Pay less than 4000 employees to increase wages 800

declare        v_empno emp.empno%type;         v_salary emp.salary%type;        cursor  c_cursor is select empNo,salary from emp;begin         open c_cursor;        loop             fetch c_cursor into v_empNo,v_salary;             --Stop     When the cursor does not find data          exit when c_cursor%notfound;             if v_salary <=4000 then                 update emp                  set salary=salary+800                 where empNo=v_empNo;              end if;         end loop;          close c_ cursor;  end;

4. Cursor FOR loop

The PL/SQL language provides a cursor for loop statement that automatically executes the open, fetch, and close statements of the cursor. When the loop is entered, the FOR Loop statement automatically opens the cursor and extracts the first row of cursor data, and when the program finishes processing the current extracted data into the next loop, the cursor for Loop statement automatically extracts the next row of data for processing, ending the loop after extracting all the data rows in the result set, and automatically closing the cursor.

DECLARE CURSOR c_cursor is select empno,empname,empsalary from Emp;begin-implicitly open cursor for v_salary in C_c         Ursor Loop-implicitly executes the FETCH statement dbms_output.put_line (' Perform your own operation ');        --implicit monitoring c_cursor%notfound statement end Loop; --implicitly closes the cursor end;

5. Implicit cursor handling

Display cursors are primarily used to handle query statements, especially if the query results in multiple records. For non-query statements, such as modifications, deletions, and so on, the Oracle system automatically sets cursors for these operations and creates their workspaces, which are called implicit cursors by the system implicitly, and the implicit cursors are named SQL, which is defined by the Oracle system. The operation of an implicit cursor, such as definition, open, value, and shutdown, is done automatically by the Oracle system, and the user can only complete the corresponding operation through the relevant properties of the implicit cursor.

Practical examples

Delete all employees in a department in the EMP table, and if there are no employees in the department, delete the Department in the Dept table.

Declare v_deptno emp.deptno%type;begin delete from emp where deptNo = V_deptno; --If the where statement is not true, that is, the V_deptno field does not have an employee if Sql%notfound then--delete the department number in the Department table delete from dept where D        Eptno = V_deptno; End If;end;

The difference between 6.no_data_found and%notfound

Select ... into statement will trigger No_data_found;

%notfound is triggered when a WHERE clause of a display cursor is not found;

Sql%notfound is triggered when the WHERE clause of the UPDATE or DELETE statement is not found.

7. Cursor variables

A cursor variable is a pointer to the current row of data in a multi-row query result set.

The 1> cursor variable is dynamic and the cursor is static.

The 2> cursor can only be connected to the specified query, which is fixed to the memory processing area of a query, and the cursor variable is dynamic and can be connected to a different query statement, which can point to the memory processing area of a different query statement (only one query statement is connected at a time. and requires the return type of these query statements to be compatible).

When you define a cursor variable, there are two kinds of strongly typed definitions and weak type definitions. A strongly typed definition must specify the return value type of the cursor variable, and the weak type definition does not describe the return type.

For example, create two strongly typed, one weakly typed cursor variable

--Define a cursor variable, the return type is table t_dept type C_depttype is REF CURSOR return t_dept%rowtype; --Define a cursor variable, the return type is the T_dept table field type C_DEPTTYPE2 is REF CURSOR return t_dept.deptname%type;--defines a cursor variable, no return type is C_depttype3 is REF CURSOR;

Examples of strongly typed cursor variables

Declare        type emp_rec is record (         empId emp.empNo%type;         Empname emp.empname%type;        empjob emp.empjob%type         );         --defines the cursor variable, return type emp_rec        type emprefcurtype is ref  cursor return emp_rec;        --defines a cursor variable of type Emprefcurtype          emp_refcur empRefcurType;         --defining variables, type emp_rec        emp_job emp_rec; begin        open emp_refcur for         select empNo,empname,empjob from emp;        fetch emp_refcur  into emp_job;        while emp_refcur%found loop                 dbms_ Output.put_line (' Perform your own operation ');                     fetch emp_refcur into emp_job;         end loop;        end;

Example of a weakly typed cursor variable

declare        type c_refcur if ref cursor;         refcur c_refcur;         type sample_reftype is record (        v_id  Number (2);         v_description varchar2 (+)          );        sample sample_reftype; begin        open refcur for         select deptNo,deptName from dept;         fetch refcur into sample;        while  refcur%found loop                dbms_output.put_line (' Perform your own operation '));                  fetch  refcur into sample;          end loop;          close refcur;                    end;

Oracle--Cursors