Oracle Learning Notes (10)

Cursor (cursor) Concept Introduction
is a result set (used when a query or other operation returns multiple results)
Define a cursor
Cursor C1 is a select ename from EMP;

To take a value from the cursor
Open cursor:
--open C1; (Open cursor execution query)
To close the cursor:
--close C1; (Close cursor release resource)
To take the value of one row of cursors:
--fetch C1 into Pename; (take one row to the variable)

The role of Fetch:
--Returns the record to which the current pointer is pointing
--point the pointer to the next record

Use the cursor to query the employee's name and salary and print
Declare
--Define a cursor
Cursor Cemp is a select ename,sal from EMP;
--Define the corresponding variable for the cursor
Pename Emp.ename%type;
Psal Emp.sal%type;
Begin
--Open cursor
Open cemp;

Loop
--Take a record
Fetch cemp into pename,psal;

--thinking: 1. What time does the loop exit? 2, fetch does not necessarily take records
--exit when the record is not taken;

Exit when Cemp%notfound;

--Print
Dbms_output.put_line (pename| | ' The salary is ' | | PSAL);

End Loop;
--Close cursor
Close Cemp;
End

1. Cursor Properties:
%found take the value is found
%notfound can't get it.

Example: Raise a salary for an employee (pay up according to position)
President Rose 1000
Manager Rose 800
Other employees Rose 400
The job in the change table is Empjob,

-The salary before the pay rise
Select Ename,job,sal from EMP;

Set Serveroutput on
Declare
--Define which employees the cursor represents to pay up
--alter table ' SCOTT ' EMP ' rename column ' JOB ' to Empjob
--Define a cursor
Cursor Cemp is a select empno,empjob from EMP;
--Define the corresponding variable for the cursor
Pempno Emp.empno%type;
Pjob Emp.empjob%type;
Begin
Rollback
--Open cursor
Open cemp;

Loop
--Take a record
Fetch cemp into pempno,pjob;
Exit when Cemp%notfound;

--judging the position of the employee
If Pjob = ' president ' then update EMP set sal=sal+1000 where Empno=pempno;
elsif pjob= ' MANAGER ' then update emp set sal=sal+800 where Empno=pempno;
Else update emp set sal=sal+400 where Empno=pempno;
End If;
End Loop;
--Close cursor
Close Cemp;

--for Oracle, the default transaction isolation level is read Committee
--Acid of the transaction (Atomicity/consistency/isolation/persistence)
Commit
Dbms_output. Put_Line (' Wage increase completed ');
End

1, the properties of the cursor;
%found (take value to true)
%notfound (value True is not taken)
%isopen (determines whether the cursor is open because the number of cursors opened in the default one session of the Oracle database is limited by 300)
%rowcount (the number of rows affected, that is, the number of rows that have been fetched, not the total number in the collection).

Case 1:%isopen Usage

Set Serveroutput on
Declare
--Define a cursor
Cursor Cemp is a select empno,empjob from EMP;
--Define the corresponding variable for the cursor
Pempno Emp.empno%type;
Pjob Emp.empjob%type;
Begin
--Open cursor
Open cemp;

If Cemp%isopen Then
Dbms_output. Put_Line (' cursor already open ');
Else
Dbms_output. Put_Line (' Cursor not open ');

End If;
--Close cursor
Close Cemp;
End

Case 2:%rowcount Usage

Set Serveroutput on
Declare
--Define a cursor
Cursor Cemp is a select empno,empjob from EMP;
--Define the corresponding variable for the cursor
Pempno Emp.empno%type;
Pjob Emp.empjob%type;
Begin
--Open cursor
Open cemp;
Loop
--Take out a record
Fetch cemp into pempno,pjob;
Exit when Cemp%notfound;

--Print the value of the rowcount
Dbms_output. Put_Line (' RowCount ' | | Cemp%rowcount);

End Loop;
--Close cursor
Close Cemp;
End

2, the cursor limit, by default, the Orcle database allows only 300 cursors to be opened in the same session
--Switch to Administrator
Conn sys/1234 as Sysdba
--View parameters
Show parameter cursor

To modify the limit of the number of cursors
Alter system set OPEN_CURSORS=400 Scope=both;

Scope has a value of three
Both (the latter two simultaneous changes), Memory,spfile (the database needs to be restarted)

3. Cursors with parameters
--Query the names of employees in a department
Set Serveroutput on
Declare
---to define cursors with parameters
Cursor Cemp (DNO number) is a select ename from emp where Deptno=dno;
Pename Emp.ename%type;

Begin
--Open cursor
Open Cemp (10);
Loop
--Take out a record
Fetch cemp into pename;
Exit when Cemp%notfound;

Dbms_output. Put_Line (Pename);

End Loop;
--Close cursor
Close Cemp;
End

The cursor is a parameter used in place of a collection (array) in SQL. **********

Oracle Learning Notes (10)