[Reprint]oracle Cursor Concept explained

Source: Internet
Author: User
Tags cursor library oracle cursor rowcount

Original url:http://www.2cto.com/database/201203/122387.html

Oracle Cursor Concepts Explained
What is a cursor?      ① retrieves the result set from the table, and the mechanism from which each point points to a record for interaction.   The operations in the ② relational database are performed on the complete rowset. The rowset returned by the SELECT statement includes all rows that satisfy the conditions listed in the WHERE clause of the statement.      The complete rowset returned by the statement is called the result set.      Applications, especially interactive and online applications, treat a complete set of results as a unit that is not always valid. These applications require a mechanism to process a row or rows at a time.        The cursor is an extension of the result set that provides this mechanism. Cursors are implemented through a cursor library. Cursor Library is a software that is often implemented as part of a database system or data access API to manage the properties (result sets) of the data returned from the data source.        These properties include concurrency management, the location in the result set, the number of rows returned, and whether you can move forward and/or backward in the result set (scrollable).      The cursor tracks the position in the result set and allows multiple actions to be performed on the result set line by row, which may be returned to the original table or not back to the original table.      In other words, a cursor conceptually returns a result set from a database-based table.   Because it indicates the current position in the result set, the cursor is named as if the cursor on the computer screen indicates the current position.  2, what is the function of the cursor?  ① Specifies the location of a particular row in the result set.  ② retrieves a row or successive rows based on the current result set location.  ③ modifies the data in the row at the current position of the result set.         ④ defines different levels of sensitivity for data changes made by other users. The ⑤ can be

Programming

the way to access

Database

.  3, why avoid using cursors? ① the most thing to consider when creating cursors is, "is there a way to avoid using cursors?"   "Because cursors are inefficient, if the cursor operates on more than 10,000 rows of data, it should be rewritten, and if a cursor is used, try to avoid table joins in the cursor loop." 4,

Oracle

Type of cursor?  ① static cursor: A cursor that has a true (statically defined) result set. is divided into implicit and display cursors.  ⑴ implicit cursors: All DML statements are implicit cursors, and SQL statement information can be obtained through implicit cursor properties.  ⑵ Display cursors: The user displays the declared cursor, which specifies the result set. An explicit cursor is required when the query returns more than one row. &NBSP;②REF cursor: A temporary object that dynamically associates the result set.   5,oracle What are the states of cursors, and how do I use cursor properties? The state of the  ① cursor is represented by a property.  %found:fetch statement (get record) execution true or False.  %notfound: Whether the last record extracts true or False.  %isopen: Whether the cursor turns on true or False.  %rowcount: The number of rows currently fetched by the cursor.  ② uses 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 (' has been updated! ‘);    Else     dbms_output. Put_Line (' Update failed! ‘);    end  If;   End;   6, how do I use the display cursor? How do I traverse a circular cursor?  ① uses the display cursor   ⑴ to declare the cursor: partition the storage area, and note that the SELECT statement is not executed at this time.     cursor cursor name (parameter list)    [return value type]   is   Select statement;    ⑵ Open cursor: Executes a SELECT statement to obtain the result set stored in the cursor, at which point the cursor points to the result set header instead of the first record.     Open cursor name (parameter list);   ⑶ Get record: Move cursor takes one record     fetch  cursor name into  temporary record or attribute type variable;   ⑷ close cursor: Puts the cursor in the buffer pool. Resources are not completely freed. can be reopened.     close  cursor name;  ② traversal loop cursor  ⑴for loop cursor    loop Leng open cursor, automatically scrolls to get a record, and automatically creates temporary record type variables to store records. The cursor is automatically closed when finished processing.      for  variable name   in  cursor name        Loop        data processing statement;      End loop;   ⑵loop Loop cursor      ...     Loop      fatch  cursor name into  temporary record or attribute type variable;      Exit   when   cursor name%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 a 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: Traversing a cursor with a for loop,  /* Conn Scott/tiger */  Declare      cursor mycur is select * from EMP;   Begin      for VarA in mycur       loop     & nbsp;    dbms_output.put_line (mycur%rowcount| | '     ' | | vara.empno| | '     ' | | vara.ename| | '   ' | | Vara.sal);       end Loop;   End;  /  7, how do I update and delete records in the display cursor? The WHERE current of substring in the  ①update or DELETE statement specifically handles the most recent data that is taken out of the table to perform the update or delete operation.   To use this method, the for update substring must be used when declaring a cursor, and when a dialog uses a for update substring to open a cursor,   all data rows in the returned set will be in row-level (Row-level) exclusive locking. Other objects can only query these data rows, &NBsp Cannot make update, delete, or select ... For update operation.    in a multi-table query, use the OF clause to lock a particular table, and if the of clause is omitted, all rows of data selected in the table will be locked.    If these data rows have been locked by another session, Oracle will normally wait until the data row is unlocked.  ② using UPDATE or delete:  ⑴ declaration to update or delete a display cursor:    cursor cursor name is  SELECT statement    for UPDATE [of  Update column column name]; nbsp;  cursor cursor name is  SELECT statement    for delete [of  update column column name];  ⑵ Update or delete using the current record of the display cursor:    update  table name    set   UPDATE statement   where   current  of   cursor name;     delete  from  table name    where   current  of   cursor name;      Example 1: Update display cursor record   /*conn scott/tiger*/   Declare      cursor mycur is select Job FR OM emp for update;        Vjob Empa.job%type;        rsal Empa.sal%type;     Begin        Open mycur;      &nbsp 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 display cursor records    /*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 < then         delete  from empa  where  cursor  of   mycursor;        end  if;         end     Loop;     end;/ 8, what is a display cursor with parameters?  ① similar to procedures and functions, you can pass parameters to cursors and use them in queries. The   parameter defines only the data type, no size (all parameters in Oracle define only the data type and do not specify a size).    Unlike a procedure, a cursor can only accept values that are passed and cannot return a value.   can set a default value for the parameter, and the default value is used when no parameter value is passed to the cursor. The parameters defined in the   cursor are just a placeholder, and referencing the parameter elsewhere is not necessarily reliable.  ② using a display cursor with parameters  ⑴ declare a display cursor with parameters:    cursor cursor name   [(Parameter[,parameter],...)]     is   SELECT statement;;       Parameter form: 1, Parameter name    data type           2, parameter name     Data type   default  default           example:    /*conn Scott/tiger     Crate table  empa  Select * from Scott.emp;     * *     Declare       Cursor mycursor (psal  number   default  )   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;/      REF CURSOR   1, what is a REF CURSOR?   A temporary object that dynamically associates the result set. This is the dynamic decision to execute the query at run time. What is the function of   2,REF cursors?   implements the ability to pass result sets between programs, using REF CURSOR to implement bulk SQL, which improves SQL performance.   3, what is the difference between a static cursor and a REF CURSOR?  ① static cursors are statically defined and REF cursors are dynamic associations;  ② using REF cursors requires REF CURSOR variables. &NBSP;③REF cursors can be passed as parameters, while static cursors are not possible.   4, what is a REF CURSOR variable? The &NBSP;REF cursor variable is a variable that references the REF CURSOR type  , which points to the dynamically associated result set.   5, how do I use   ref cursors?  ① declares a REF cursor type, determines the REF cursor type, and  ⑴ a strongly typed REF CURSOR: Specifies that the type of the Retrun TYPE,REF cursor variable must be the same as the return type.    Syntax:type   REF CURSOR name    is   REF CURSOR return  result set returns record type;   ⑵ weakly typed REF CURSOR: Return Typ not specifiedE, which can match any type of cursor variable to get any result set.    Syntax:type   REF CURSOR name    is   REF CURSOR,    ② declaring REF CURSOR type variable;   Syntax: variable name   declared REF cursor type;    ③ open REF CURSOR, associate result set,   syntax:open   REF CURSOR type variable    for   query statement return Back to the result set;    ④ get record, operation record;   Syntax:fatch    REF CURSOR name into   temporary record type variable or attribute type variable list;    ⑤ closes the cursor, completely frees the resource,   syntax:close   REF CURSOR name,     Example: strongly-typed REF CURSOR  /*conn scott/tiger*/  Declare   Type Myrefcura is  REF CURSOR RETURN emp%rowtype;   Type myrefcurb is  REF CURSOR RETURN emp.ename%type;   vrefcura  Myrefcura;   vrefcurb  Myrefcurb;   vtempa  Vrefcura%rowtype;   vtempb  Vrefcurb.ename%type;    begin   open  vrefcura  for select  *  from   emp   Where   SAL > 2000;   Loops    fatch  Vrefcura into  Vtempa;    exit&nbsp when  Vrefcura%notfound;    Dbms_output. Put_Line (vrefcura%rowcount| | '   ' | | vtempa.eno| | '   ' | | Vtempa.ename | | '   ' | | vtempa.sal)   End Loop;   Close Vrefcura;     Dbms_output. Put_Line ('---------------------------------------------------------------------------------------------------- ---‘);     open  vrefcurb  for select  ename  from   emp   Where  SAL ; 2000;   Loops    fatch  vrefcurb into  VTEMPB;    exit  when  Vrefcurb%notfound;    Dbms_output. Put_Line (vrefcurb%rowcount| | '   ' | | VTEMPB)   End Loop;   Close Vrefcurb;     Dbms_output. Put_Line ('---------------------------------------------------------------------------------------------------- ---');       open  vrefcura  for select  *  from   emp   where  JOB = ' clerk ';   Loop &NBsp;  fatch  Vrefcura into  Vtempa;    exit  when  Vrefcura%notfound;    Dbms_output. Put_Line (vrefcura%rowcount| | '   ' | | vtempa.eno| | '   ' | | Vtempa.ename | | '   ' | | vtempa.sal)   End Loop;   Close Vrefcura;  End;     Example: weakly-typed REF cursor  /*conn scott/tiger*/ declare   type myrefcur  is  ref  cursor;   Vrefcur Myrefcur;   vtemp  Vrefcur%rowtype;  begin   Case (&n)    when  1 then Open vrefcur  for select   *   fro M EMP;    when  2 then Open vrefcur  for select   *   from dept;    Else     Open vrefcur  for select   eno,  ename  from emp Where JOB = ' Clerk ';   END case;   close  Vrefcur;  End;

[Reprint]oracle Cursor Concept explained

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.