what is oracle cursor
- 青灯夜游Original
- 2022-02-23 11:35:0413358browse
In Oracle, a cursor is a mechanism that uses the keyword CURSOR to define a set of data sets queried by Oracle. The queried data set can be stored in memory, and then the cursor points to one of the data sets. Record, through the loop cursor to achieve the purpose of looping the data set.
The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.
In Oracle, cursors are a mechanism by which a name can be assigned to a SELECT statement and the information in that SQL statement can be processed.
Oracle cursor defines a set of data sets queried by Oracle through the keyword CURSOR. Similar to an array, the queried data set is stored in the memory, and then the cursor points to one of the records, and the cursor is looped Achieve the purpose of looping the data set.
What is the function of cursor?
①Specify the position of a specific row in the result set.
②Retrieve one row or several consecutive rows based on the current result set position.
③ Modify the data in the row at the current position of the result set.
④ Define different sensitivity levels for data changes made by other users.
⑤The database can be accessed programmatically.
Type of Oracle cursor?
1. Static cursor: A cursor whose result set has been confirmed (statically defined). Divided into implicit and explicit cursors
Implicit cursor: All DML statements are implicit cursors, and SQL statement information can be obtained through the implicit cursor attribute.
Display cursor: The user displays the declared cursor, that is, the specified result set. When a query returns more than one row of results, an explicit cursor is required.
2. REF cursor: a temporary object that dynamically associates the result set.
What are the statuses of Oracle cursors and how to use cursor attributes?
①The status of the cursor is represented by attributes.
%Found: The execution status of Fetch statement (getting records) is True or False.
%NotFound: Whether the last record is extracted as True or False.
%ISOpen: Whether the cursor is open True or False.
%RowCount: The number of rows currently extracted by the cursor.
②Use the properties of the cursor.
Example:
/* conn scott/tiger */
Begin
Update emp Set SAL = SAL + 0.1 Where JOB = 'CLERK';
If SQL%Found Then
DBMS_OUTPUT.PUT_LINE('已经更新！');
Else
DBMS_OUTPUT.PUT_LINE('更新失败！');
End If；
End；How to use the display cursor? How to iterate over a loop cursor?
1. Use the display cursor
Declare the cursor: divide the storage area. Note that the Select statement is not executed at this time.
CURSOR 游标名( 参数 列表) [返回值类型] IS Select 语句；
Open the cursor: execute the Select statement, obtain the result set and store it in the cursor. At this time, the cursor points to the head of the result set, not the first record.
Open 游标名( 参数 列表);
Get records: Move the cursor to get a record
Fetch 游标名InTo 临时记录或属性类型变量；
Close the cursor: The cursor is placed in the buffer pool and the resources are not completely released. Can be reopened.
Close 游标名；
2. Traversing the loop cursor
For loop cursor
The loop cursor is implicitly opened The cursor automatically scrolls to obtain a record and automatically creates a temporary record type variable to store the record. The cursor is automatically closed after processing.
For 变量名 In 游标名
Loop
数据处理语句；
End Loop；Loop cursor
。。。
Loop
Fatch 游标名InTo 临时记录或属性类型变量；
Exit When 游标名%NotFound；
End Loop；
。。。Example 1:
/* conn scott/tiger */
Declare
Cursor myCur is select empno,ename,sal from emp;
vna varchar2(10);
vno number(4);
vsal number(7,2);
Begin
open myCur;
fetch myCur into vno,vna,vsal;
dbms_output.put_line(vno||' '||vna||' '||vsal);
close myCur;
End;
/Example 2: Use loop to traverse the cursor.
/* conn scott/tiger */
Declare
Cursor myCur is select ename,job,sal,empno from emp;
varE myCur%rowType;
Begin
if myCur%isopen = false then
open myCur;
dbms_output.put_line('Opening...');
end if;
loop
fetch myCur into varE;
exit when myCur%notfound;
dbms_output.put_line(myCur%rowCount||' '||vare.empno||' '||vare.ename||' '||vare.sal);
end loop;
if myCur%isopen then
Close myCur;
dbms_output.put_line('Closing...');
end if;
End;
/Example 3: Using a For loop to traverse the cursor,
/* conn scott/tiger */
Declare
Cursor myCur is select * from emp;
Begin
for varA in myCur
loop
dbms_output.put_line(myCur%rowCount||' '||varA.empno||' '||varA.ename||' '||varA.sal);
end loop;
End;
/How to update and delete records in the displayed cursor?
①The WHERE CURRENT OF substring in the UPDATE or DELETE statement specifically handles the most recent data retrieved from the table to be performed UPDATE or DELETE operations.
To use this method, you must use the FOR UPDATE substring when declaring the cursor. When the dialog uses the FOR UPDATE substring to open a cursor,
All data rows in the return set will be in the row Level (ROW-LEVEL) exclusive locking, other objects can only query these data rows,
cannot perform UPDATE, DELETE or SELECT...FOR UPDATE operations.
In a multi-table query, use the OF clause to lock a specific table. If the OF clause is ignored, the selected data rows in all tables will be locked.
If these data rows have been locked by other sessions, then under normal circumstances ORACLE will wait until the data rows are unlocked.
② Use update or delete:
⑴ Declare to update or delete the display cursor:
Cursor 游标名IS SELECT 语句 For Update [ Of 更新列列名]； Cursor 游标名IS SELECT 语句 For Delete [ Of 更新列列名]；
⑵ Use the display cursor to update or delete the current record:
Update 表名 SET 更新语句 Where Current Of 游标名； Delete From 表名 Where Current Of 游标名；
Example 1: Update the display cursor record
/*conn scott/tiger*/
Declare
Cursor myCur is select job from emp for update;
vjob empa.job%type;
rsal empa.sal%type;
Begin
open myCur;
loop
fetch myCur into vjob;
exit when myCur%notFound;
case (vjob)
when 'ANALYST' then rsal := 0.1;
when 'CLERK' then rsal := 0.2;
when 'MANAGER' then rsal := 0.3;
else
rsal := 0.5;
end case;
update emp set sal = sal + rsal where current of myCur;
end loop;
End;
/Example 2: Delete the display cursor record
/*conn scott/tiger
Crate table empa Select * from scott.emp;
*/
Declare
Cursor MyCursor Select JOB From empa For Update;
vSal emp.Sal%TYPE;
Begin
Loop
Fetch MyCursor InTo vSal;
Exit When MyCursor%NotFound;
If vSal < 800 Then
Delete From empa Where Cursor Of MyCursor;
End If;
End Loop;
End;/What is a display cursor with parameters?
1. Similar to procedures and functions, parameters can be passed to the cursor and used in queries.
The parameters only define the data type, not the size (all formal parameters in Oracle only define the data type, not the size).
与过程不同的是，游标只能接受传递的值，而不能返回值。
可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。
游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。
2、使用带参数的显示游标
声明带参数的显示游标：
CURSOR 游标名 [(parameter[,parameter],...)] IS Select语句;
参数形式：
1，参数名 数据类型
2，参数名 数据类型 DEFAULT 默认值
例子：
/*conn scott/tiger
Crate table empa Select * from scott.emp;
*/
Declare
Cursor MyCursor(pSal Number Default 800) Select JOB From empa Where SAL > pSal ;
varA MyCursor%ROWTYPE;
Begin
Loop
Fetch MyCursor InTo varA;
Exit When MyCursor%NotFound;
DBMS_OUTPUT.PUT_LINE(MyCursor%RowCount||' '||varA.empno||' '||varA.ename||' '||varA.sal);
End Loop;
End;/推荐教程：《Oracle教程》
The above is the detailed content of what is oracle cursor. For more information, please follow other related articles on the PHP Chinese website!