Use of Oracle cursors

1. What is a cursor?

A: A cursor is a data buffer opened by the system to the user, which holds the execution result of the SQL statement.

Each cursor has a name that the user can use to extract data from the cursor and assign it to the variable.

2. Cursor classification

A: Cursors are divided into implicit cursors (cursor properties), display cursors (cursor tricks), and REF cursors (cursor variables);

3. How does an implicit cursor work?

A: When using DML, an implicit cursor is automatically created, automatically declared, automatically opened, automatically closed, and named SQL.

The properties are:%found,%notfound,%rowcount,%isopen.

　　　　

4. How do I use an explicit cursor?

A: An explicit cursor declares a partial definition query in a PL/SQL block that can return one or more rows of records.

Using an explicit cursor is divided into four steps:

(1), declaring cursors

Declare

Cursor MyCursor is

Select Ename,sal from emp where deptno=10;

V_name Emp.ename*type;

V_sal Emp.sal%type;

Begin

(2), open cursor

Open mycursor;

(3), using cursors

Loop

Fetch mycursor into v_name,v_sal;

Exit when Mycursor%notfound;

Dbms_output.put_line (v_name| | ' ' | | V_sal);

End Loop;

(4), close the cursor

Close MyCursor;

End

5. Use an explicit cursor with parameters.

DECLARE CURSOR emp_cursor (DNO number) is

Select Ename,sal from emp

where Deptno=dno

Emp_record Emp_cursor%rowtype;

Begin

IF not Emp_cursor%isopen Then

Open Emp_cursor (20);

END IF;

Loop

Fetch emp_cursor into Emp_record

Exit when Emp_cursor%notfound

Dbms_output.put_line (emp_record.ename| | ' ' | | Emp_record.sal);

End Loop;

Close emp_cursor;

End

6. Circular cursors (do not open and close cursors but need to declare a cursor variable of the same type as the cursor)

DECLARE CURSOR emp_cursor (DNO number) is

Select Ename,sal from emp

where Deptno=dno;

Emp_record Emp_cursor%rowtype;

Begin

For Emp_record in Emp_cursor (Ten) loop

Dbms_output.putline (v_name| | ' ' | | V_sal);

End Loop;

End

7. Updating rows with Cursors

Declare

Cursor Emp_cursor is

Select Ename,sal,deptno from emp for update;--locks the EMP table, does not commit after execution, or rolls back will lock the current table

Emp_record Emp_cursor%rowtype;

Begin

If not Emp_cursor%isopen then

Open emp_cursor;

End If;

Loop

Fetch emp_cursor into Emp_record;

Exit when Emp_cursor%notfound;

If Emp_record.deptno =

Update EMP Set sal=sal+100 where CURRENT of emp_cursor;

End If;

Dbms_output.put_line (emp_record.sal);

End Loop;

Close emp_cursor;

End

8. REF CURSOR (cursor variable)

Declare

Type my_type is REF CURSOR;

CV My_type;

--CV Sys_refcursor;

V_lastname Employee.ename%type;

Query_2 varchar2 (+): = ' select * FROM dept ';

V_emp Emp%rowtype;

V_dept Dept%rowtype;

Begin

Open CV for

Select ename from emp

where job= ' MANAGER '

Order by ename;

Loop

FETCH CV into v_lastname;

Exit when Cv%notfound;

Dbms_output.put_line (V_lastname);

End Loop;

Dbms_output.put_line ('----------------');

Open CV for query_2;

Loop

FETCH CV into v_dept;

Exit when Cv%notfound;

Dbms_output.put_line (V_dept.dname);

End Loop;

Close CV;

End

Use of Oracle cursors