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;   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  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
