An explanation of how Oracle cursors are used

1, what is a cursor?

① retrieves a result set from a table from which the mechanism for interacting with each record is pointed.

Operations in the ② relational database are performed on a complete rowset.
The rowset returned by the SELECT statement includes all rows that meet the criteria listed in the WHERE clause of the statement. Returning the complete rowset by this statement is called the result set.
Applications, especially interactive and online applications, do not always work with a complete set of results as a unit.
These applications require a mechanism to process one row or successive lines at a time. Cursors are an extension of the result set that provides this mechanism.

Cursors are implemented through the cursor library. Cursor Library is software that is often implemented as part of a database system or data access API,
The property (result set) that is used to manage the data returned from the data source. These properties include concurrency management, location in the result set, number of rows returned,
And whether you can move forward and/or backward in the result set (scrollable).

The cursor tracks the location in the result set and allows multiple operations to be performed row by line on the result set, which may be returned to the original table or not to the original table.
In other words, the cursor returns the result set conceptually from a database based table.
Because it indicates the current position in the result set, the cursor derives its name as if the cursor on the computer screen indicates the current position.

2, what is the role of cursors?

① Specifies the location of a specific 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 sensitivity levels for data changes made by other users.
⑤ can access the database programmatically.

3, why avoid using cursors?

① when creating a cursor, the most thing to consider is, "is there a way to avoid using cursors?" ”
Because the cursor is inefficient, if the cursor operation has more than 10,000 rows of data, it should be overwritten;
If you use a cursor, you should try to avoid the operation of table joins in the cursor loop.

Type of 4,oracle cursor?

① static cursor: A cursor that has a true (statically defined) result set. is divided into implicit and display cursors.
⑴ implicit cursor: All DML statements are implicit cursors, and SQL statement information can be obtained through implicit cursor properties.
⑵ Display cursor: The user displays a declared cursor, that is, the result set is specified. When a query returns more than one row, an explicit cursor is required.
②REF cursor: A temporary object that dynamically associates a result set.

What are the states of the 5,oracle cursor, and how do you use the cursor properties?

code is as follows

copy code

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 is open true or False.  %rowcount: The number of rows currently fetched by the cursor. The  ② uses the properties of the cursor.   Example:/* Conn Scott/tiger */   Begin    Update emp set  sal = sal + 0.1  Where J OB = ' 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 a display cursor? How do I traverse a loop cursor?

① using Display Cursors


⑴ declaration cursor: Dividing the storage area, note that the SELECT statement is not executed at this time.


CURSOR cursor name (argument list) [return value type] is Select statement;


⑵ Open cursor: Executes the SELECT statement, obtains the result set to be stored in the cursor, at which point the cursor points to the result set header instead of the first record.


Open cursor name (argument list);


⑶ FETCH RECORD: Move cursor fetch a record


Fetch cursor name into temporary record or attribute type variable;


⑷ closes the cursor: puts the cursor in the buffer pool and does not fully release the resource. can be reopened.


The close cursor name;


② traversal loop cursor


⑴for Loop cursor


Loop Leng open cursors, automatically scroll to get a record, and automatically create temporary record type variables to store records. Automatically closes the cursor when the process is finished.


For variable name in cursor name


Loop


Data processing statements;


End Loop;


⑵loop Loop cursor


。。。


Loop


Fatch cursor into a temporary record or an attribute type variable;


Exit when cursor name%notfound;


End Loop;


。。。


Example 1:

code is as follows	copy code
/* "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;< br>      dbms_output.put_line (vno| | '     ' | | vna| | '     ' | | Vsal);      Close mycur;   END;  /

Example 2: Use the loop to traverse the cursor.

The code is as follows

Copy Code

/* 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 a cursor,

The code is as follows	Copy Code
/* Conn Scott/tiger * * Declare Cursor Mycur is a select * from EMP; Begin For VarA in Mycur Loop Dbms_output.put_line (mycur%rowcount| | ' '||    vara.empno| | ' '||  vara.ename| | ' '|| Vara.sal); End Loop; End; /

7, how to update and delete the record in the display cursor?

The WHERE current of substring in the

 ①update or DELETE statement deals specifically with the most recent data that is fetched from the table to perform an update or delete operation.
  To use this method, you must use a for update substring 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,
  cannot make update, delete, or select ... For update operation.
   in a multiple-table query, use the OF clause to lock a particular table, and if the of clause is omitted, the rows of data selected in all tables are locked.
   If these data rows are already locked by another session, Oracle will normally wait until the data row is unlocked.
 ② use Update or delete:
 ⑴ declare update or delete a display cursor:
   Cursor cursor name is  SELECT statement    for Update [OF&N Bsp Update column column name];
   Cursor cursor name is  SELECT statement    for Delete [of  Update column column name];
 ⑵ update with the current record of the display cursor or delete:
   update  table name    set   UPDATE statement   where   current  of & nbsp cursor name;
   delete  from  table name    where   current  of   cursor name;
& nbsp
   Example 1: Update display cursor records

The code is as follows

Copy Code

/*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 a 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 < Then Delete from Empa Where Cursor of MyCursor; End If; End Loop; end;/

8, what is a display cursor with parameters?

① are similar to procedures and functions, you can pass parameters to cursors and use them in queries.
The parameter defines only the data type, not the size (all the parameters in Oracle define only the data type, not the size).
Unlike a procedure, a cursor can only accept values that are passed, not values.
You can set a default value for the parameter and use the default value when no parameter values are passed to the cursor.
The parameter defined in the cursor is only a placeholder, and it is not necessarily reliable to reference the parameter elsewhere.
② using a display cursor with parameters
⑴ declares 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 value

Example:

The code is as follows	Copy Code
/*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;/

Here are some examples of Oracle cursors you can refer to for your classmates.

--declaration cursor; CURSOR cursor_name is select_statement

The code is as follows

Copy Code

--for Loop cursor --(1) Defining cursors --(2) Defining cursor variables --(3) using a For loop to use this cursor Declare --type definition Cursor C_job Is Select Empno,ename,job,sal From EMP where job= ' MANAGER '; --Defines a cursor variable v_cinfo c_emp%rowtype, which is a row of data types in the cursor c_emp C_row C_job%rowtype; Begin For C_row in C_job loop Dbms_output.put_line (c_row.empno| | ' -'|| c_row.ename| | ' -'|| c_row.job| | ' -'|| C_row.sal); End Loop; End --fetch Cursors --must be explicitly turned on and off when used Declare --type definition Cursor C_job Is Select Empno,ename,job,sal From EMP where job= ' MANAGER '; --Define a cursor variable C_row C_job%rowtype; Begin Open c_job; Loop --Extracts one row of data to C_row Fetch c_job into C_row; --whether the interpretation is extracted to the value, the value is not taken out --Take the value C_job%notfound is False --Cannot get value C_job%notfound is true Exit when C_job%notfound; Dbms_output.put_line (c_row.empno| | ' -'|| c_row.ename| | ' -'|| c_row.job| | ' -'|| C_row.sal); End Loop; --Close cursor Close C_job; End --1: Performs an update operation arbitrarily,%found,%notfound,%rowcount,%isopen the performance of the UPDATE statement with the implicit cursor SQL properties. Begin Update emp Set ename= ' Aleark ' WHERE empno=7469; If Sql%isopen Then Dbms_output.put_line (' openging '); Else Dbms_output.put_line (' closing '); End If; If Sql%found Then Dbms_output.put_line (' cursor pointing to a valid row ');--determining whether the cursor points to a valid row Else Dbms_output.put_line (' Sorry '); End If; If Sql%notfound Then Dbms_output.put_line (' Also Sorry '); Else Dbms_output.put_line (' Haha '); End If; Dbms_output.put_line (Sql%rowcount); exception When No_data_found Then Dbms_output.put_line (' Sorry No data '); When Too_many_rows Then Dbms_output.put_line (' Too Many rows '); End Declare Empnumber EMP. Empno%type; EmpName EMP. Ename%type; Begin If Sql%isopen Then Dbms_output.put_line (' Cursor is opinging '); Else Dbms_output.put_line (' Cursor is close '); End If; If Sql%notfound Then Dbms_output.put_line (' No Value '); Else Dbms_output.put_line (Empnumber); End If; Dbms_output.put_line (Sql%rowcount); Dbms_output.put_line ('-------------'); Select Empno,ename into Empnumber,empname from EMP where empno=7499; Dbms_output.put_line (Sql%rowcount); If Sql%isopen Then Dbms_output.put_line (' Cursor is opinging '); Else Dbms_output.put_line (' Cursor is Closing '); End If; If Sql%notfound Then Dbms_output.put_line (' No Value '); Else Dbms_output.put_line (Empnumber); End If; exception When No_data_found Then Dbms_output.put_line (' No Value '); When Too_many_rows Then Dbms_output.put_line (' Too many rows '); End --2, using cursors and loop loops to display the names of all departments --Cursor Declaration Declare Cursor Csr_dept Is --select statement Select Dname From Depth; --Specifies the row pointer, which should be the same variable as the specified and Csr_dept row type Row_dept Csr_dept%rowtype; Begin --for Cycle For row_dept in Csr_dept loop Dbms_output.put_line (' Department Name: ' | | Row_dept. DNAME); End Loop; End --3, use a cursor and a while loop to show the geography of all departments (with%found attribute) Declare --Cursor Declaration Cursor Csr_testwhile Is --select statement Select LOC From Depth; --Specify row pointer Row_loc Csr_testwhile%rowtype; Begin --Open cursor Open csr_testwhile; --feed data to the first line Fetch csr_testwhile into Row_loc; --Test for data and perform loops While Csr_testwhile%found loop Dbms_output.put_line (' Department location: ' | | Row_loc. LOC); --feed data to the next line Fetch csr_testwhile into Row_loc; End Loop; Close Csr_testwhile; End SELECT * FROM emp --4, receives the department number entered by the user, prints all information about all employees in this department with a for loop and a cursor (using a circular cursor) --cursor cursor_name[(Parameter[,parameter],...)] is select_statement; --The syntax for defining the parameters is as follows: Parameter_name [in] data_type[{:=| DEFAULT} value] Declare CURSOR C_dept (p_deptno number) Is SELECT * from EMP where emp.depno=p_deptno; R_emp Emp%rowtype; Begin For r_emp in C_dept loop Dbms_output.put_line (' Employee number: ' | | R_emp. empno| | ' Employee Name: ' | | R_emp. ename| | ' Salary: ' | | R_emp. SAL); End Loop; End SELECT * FROM emp --5: Passes a type of work to the cursor showing all information about all employees of the job (using parameter cursors) Declare Cursor C_job (P_job nvarchar2) Is SELECT * from EMP where job=p_job; R_job Emp%rowtype; Begin For r_job in C_job (' clerk ') loop Dbms_output.put_line (' Employee Number ' | | R_job. empno| | ' '||' Employee Name ' | | R_job. ENAME); End Loop; End SELECT * from EMP --6: Add a commission to an employee with an update cursor: (with an If implementation, create a EMP1 table like the EMP table, modify the EMP1 table), and export the data before and after the update --http://zheng12tian.iteye.com/blog/815770 CREATE TABLE EMP1 as SELECT * from EMP; Declare Cursor Csr_update Is SELECT * from EMP1 for update of SAL; Empinfo Csr_update%rowtype; Saleinfo EMP1. Sal%type; Begin For Empinfo in Csr_update loop IF empinfo.sal<1500 THEN saleinfo:=empinfo.sal*1.2; elsif empinfo.sal<2000 THEN saleinfo:=empinfo.sal*1.5; elsif empinfo.sal<3000 THEN saleinfo:=empinfo.sal*2; End IF; UPDATE emp1 SET sal=saleinfo WHERE Current of csr_update; End LOOP; End; --7: Write a pl/sql program block to give them a raise for all employees whose first name starts with ' A ' or ' S ' (SAL) 10% (Modify the EMP1 table) Declare Cursor Csr_addsal Is SELECT * from EMP1 where ename like ' a% ' OR ename like ' s% ' for update of SAL; R_addsal Csr_addsal%rowtype; Saleinfo EMP1. Sal%type; Begin For r_addsal in Csr_addsal loop Dbms_output.put_line (r_addsal.ename| | ' The original salary: ' | | R_addsal.sal); saleinfo:=r_addsal.sal*1.1; UPDATE emp1 SET sal=saleinfo WHERE Current of csr_addsal; End Loop; End --8: Write a pl/sql program block, increase commission on all salesman (COMM) 500 Declare Cursor Csr_addcomm (P_job nvarchar2) Is SELECT * from EMP1 where job=p_job for UPDATE of COMM; R_addcomm Emp1%rowtype; Comminfo Emp1.comm%type; Begin For R_addcomm in Csr_addcomm (' salesman ') loop comminfo:=r_addcomm.comm+500; UPDATE EMP1 SET Comm=comminfo where current of Csr_addcomm; End LOOP; End; --9: Write a PL/SQL program block to upgrade 2 oldest employees to manager (the longer the work time, the older the qualification) -(Hint: A variable can be defined as a counter control cursor to extract only two data, or the oldest two of employees can be identified to the cursor when the cursor is declared.) ） Declare Cursor Crs_testcomput Is SELECT * from EMP1 ORDER by hiredate ASC; --Counter Top_two number:=2; R_testcomput Crs_testcomput%rowtype; Begin Open crs_testcomput; FETCH crs_testcomput into R_testcomput; While Top_two>0 loop Dbms_output.put_line (' Employee Name: ' | | r_testcomput.ename| | ' working time: ' | | R_testcomput.hiredate); --A speed reducer Top_two:=top_two-1; FETCH crs_testcomput into R_testcomput; End Loop; Close Crs_testcomput; End --10: Write a pl/sql program block that gives them a raise for all employees at 20% of their basic salary (SAL), --Cancel the raise if the increase is greater than 300 (modify the EMP1 table and export the data before and after the update) Declare Cursor Crs_upadatesal Is SELECT * from EMP1 for update of SAL; R_updatesal Crs_upadatesal%rowtype; Saladd Emp1.sal%type; Salinfo Emp1.sal%type; Begin For r_updatesal in Crs_upadatesal loop Saladd:= r_updatesal.sal*0.2; If saladd>300 Then Salinfo:=r_updatesal.sal; Dbms_output.put_line (r_updatesal.ename| | '): The pay rise failed. '||' Salary maintained in: ' | | R_updatesal.sal); Else Salinfo:=r_updatesal.sal+saladd; Dbms_output.put_line (r_updatesal.ename| | '): The pay rise was successful. ' | | ' Salary into: ' | | Salinfo); End If; Update EMP1 set Sal=salinfo where current of crs_upadatesal; End Loop; End --11: How many years zero each employee work how many months zero how many days output out --Approximate --ceil (n) function: Take the smallest integer greater than or equal to the value n --floor (n) function: takes the largest integer less than or equal to the value n The use of--truc http://publish.it168.com/2005/1028/20051028034101.shtml Declare Cursor Crs_workday Is Select Ename,hiredate, Trunc (Months_between (sysdate, HireDate)/a) as Spandyears, Trunc (mod (Months_between (sysdate, HireDate),) as months, Trunc (mod (sysdate-hiredate, 365)) as days From EMP1; R_workday Crs_workday%rowtype; Begin For R_workday in Crs_workday loop Dbms_output.put_line (r_workday.ename| | ' Already worked on ' | | r_workday.spandyears| | ' Year, 0 ' | | r_workday.months| | ' Month, 0 ' | | r_workday.days| | ' Days '); End Loop; End --12: Input department number, execute according to the following salary ratio (with case implementation, create a EMP1 table, modify the EMP1 table data), and output the data before and after the update --Deptno Raise (%) --10 5% --20 10% --30 15% --40 20% --The salary ratio is based on existing Sal. --case expr when comparison_expr THEN return_expr --[, when comparison_expr THEN return_expr] ... [ELSE else_expr] End Declare Cursor Crs_casetest Is SELECT * from EMP1 for update of SAL; R_casetest Crs_casetest%rowtype; Salinfo Emp1.sal%type; Begin For r_casetest in Crs_casetest loop Case When r_casetest.depno=10 THEN salinfo:=r_casetest.sal*1.05; When r_casetest.depno=20 THEN salinfo:=r_casetest.sal*1.1; When r_casetest.depno=30 THEN salinfo:=r_casetest.sal*1.15; When r_casetest.depno=40 THEN salinfo:=r_casetest.sal*1.2; End case; Update EMP1 set Sal=salinfo where current of crs_casetest; End Loop; End --13: To judge the salary of each employee, if the employee's salary is higher than the average salary of his department, then the salary is reduced by 50 yuan, the salary before and after the update, the employee's name and department number. --avg ([Distinct|all] expr) over (analytic_clause) ---function: --group averages are calculated according to the rules in Analytic_clause. --Analytic function syntax: --function_name (<ARGUMENT>,<ARGUMENT>.) --over --(<partition-clause><order-by-clause><windowing clause>) --partition clause --By expression partitioning (that is, grouping), if the partition clause is omitted, the entire result set is considered a single group SELECT * FROM EMP1 DECLARE CURSOR Crs_testavg Is Select Empno,ename,job,sal,depno,avg (SAL) over (PARTITION by Depno) as Dep_avg From EMP1 for update of SAL; R_testavg Crs_testavg%rowtype; Salinfo Emp1.sal%type; Begin For R_testavg in Crs_testavg loop If R_testavg.sal>r_testavg.dep_avg Then salinfo:=r_testavg.sal-50; End If; Update EMP1 set Sal=salinfo where current of crs_testavg; End Loop; End