what is oracle cursor

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!