An explanation of Oracle cursor usage

Transferred from: http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html

Cursor cursor_name is select_statement

--For loop cursors
--(1) Defining cursors
--(2) Defining a cursor variable
--(3) using the 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_rowIn 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‘;
 --Defining a Cursor variable
C_row C_job%rowtype;
Begin
 Open c_job;
Loop
 --Extract a row of data to C_row
 Fetch C_jobinto C_row;
 --Interpret whether to extract the value, exit without taking a value
 --Fetch value C_job%notfound is False
 --No value C_job%notfound is true
 ExitWhen C_job%notfound;
Dbms_output.put_line (c_row.empno||‘-‘|| C_row.ename||‘-‘|| C_row.job||‘-‘|| C_row.sal);
 End Loop;
 --Close Cursors
 Close C_job;
End

--1: Perform an update operation arbitrarily, using the implicit cursor SQL property%found,%notfound,%rowcount,%isopen to observe the UPDATE statement execution.
 Begin
 Update empSet ename=‘Aleark‘WHERE EMPNO=7469;
 If SQL%isopenThen
Dbms_output.put_line (‘Openging‘);
 Else
Dbms_output.put_line (‘Closing‘);
 EndIf
 If SQL%foundThen
Dbms_output.put_line (‘The cursor points to a valid row‘);--Determines whether the cursor points to a valid row
 Else
Dbms_output.put_line (‘Sorry‘);
 EndIf
 If SQL%notfoundThen
Dbms_output.put_line (‘Also Sorry‘);
 Else
Dbms_output.put_line (‘Haha‘);
 EndIf
Dbms_output.put_line (SQL%ROWCOUNT);
exception
 When No_data_foundThen
Dbms_output.put_line (‘Sorry No Data‘);
 When Too_many_rowsThen
Dbms_output.put_line (‘Too Many rows‘);
 End
Declare
Empnumber EMP. EMPNO%TYPE;
EmpName EMP. Ename%TYPE;
 Begin
 If SQL%isopenThen
Dbms_output.put_line (‘Cursor is opinging‘);
 Else
Dbms_output.put_line (‘Cursor is Close‘);
 EndIf
 If SQL%notfoundThen
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 to 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 declarations DECLARE CURSOR csr_dept is--select statement select Dname from Depth; --Specify the row pointer, which should be the same variable as the csr_dept row type row_dept csr_dept%rowtype;begin--for loop for row_dept in Csr_dept loop dbms_output.put _line (' Department Name: ' | | Row_dept. DNAME); End loop;end;--3, using cursors and while loops to display the geographic location of all departments (with the%found property) declare--cursor declaration cursor Csr_testwhile is--select statement select LOC from Depth; --Specifies the row pointer row_loc csr_testwhile%rowtype;begin--Opens the cursor open csr_testwhile; --Feed the data to the first line fetch csr_testwhile into Row_loc; --Test for data and perform loop while Csr_testwhile%found Loop dbms_output.put_line (' Department location: ' | | Row_loc. LOC); --Feed the next line of data fetch csr_testwhile into Row_loc; End Loop; Close csr_testwhile;end; SELECT * FROM EMP--4, receives the user input department number, uses a for loop and a cursor to print all the information for all employees in this department (using circular cursors)--cursor cursor_name[(Parameter[,parameter],... )] is select_statement;--the syntax for defining parameters is as follows: Parameter_name [in] data_type[{:=| DEFAULT} value] Declare CURSOR c_dept (p_deptno number) is a 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 job to the cursor, showing all the information for all employees of this 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: Use an update cursor to add a commission to an employee: (using the If implementation, create a emp1 table like the EMP table, modify the EMP1 table), and output 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 for all employees whose name begins with ' A ' or ' S ' to give them a raise (modify the EMP1 table) by 10% of their base salary (SAL) 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 for all salesman (comm) 500declare 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 increase the 2 most qualified staff as manager (the longer the work time, the older the qualification)--(Hint: You can define a variable as a counter control cursor to extract only two data It is also possible to locate the oldest two people in the employee in the cursor when declaring the cursor. ) 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 hours: ' | | R_testcomput.hiredate); --the speed reducer is reduced by one top_two:=top_two-1; FETCH crs_testcomput into R_testcomput; End Loop; Close crs_testcomput;end; --10: Write a PL/SQL program block to raise the salary for all employees at 20% of their basic salary (SAL)--if the increase in salary is greater than 300, cancel the raise (Modify the EMP1 table and output the data before and after the update) DECLARE CURSOR CRS _upadatesal is a 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| | ': Salary increase success. ' | | ' Salary changed to: ' | | Salinfo); End If; Update EMP1 set Sal=salinfo where current of crs_upadatesal; End Loop;end; --11: How many years and months to zero how many days each employee worked out-approximate--ceil (n) function: takes the smallest integer greater than or equal to the value n--floor (n) function: Takes the maximum integer value less than or equal to the number N,--truc Publish.it168.com/2005/1028/20051028034101.shtmldeclare cursor Crs_workday is select Ename,hiredate, trunc (months_ Between (Sysdate, HireDate)/() as Spandyears, Trunc (mod (Months_between (sysdate), HireDate)) as months, trunc (mod (mo D (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 Working ' | | r_workday.spandyears| | ' Year, 0 ' | | r_workday.months| | ' Month, 0 ' | | R_workday.days| | ' Days '); End Loop;end; --12: Enter the department number, according to the following increase rate (in case implementation, create a EMP1 table, modify the data of the EMP1 table), and output the data before and after the update-Deptno raise (%)--10 5%--20 10%--30 15%--40 20%- -The rate of increase is based on the existing SAL standard--case expr when comparison_expr then return_expr--[, when comparison_expr then return_expr] ... [ELSE else_expr] Enddeclare 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 if r_casetest.depno=10 then salinfo:=r_casetest.sal*1.05; When R_casetest.depno=20 and then salinfo:=r_casetest.sal*1.1; When r_casetest.depno=30 and then salinfo:=r_casetest.sal*1.15; When r_casetest.depno=40 and then salinfo:=r_casetest.sal*1.2; End case; Update EMP1 set Sal=salinfo where current of crs_casetest; End LOOP;END;--13: The salary of each employee is judged, if the employee's salary is higher than the average salary of his department, then his salary will be reduced by 50 yuan, output the salary before and after the update, employee name, department number. --avg ([Distinct|all] expr) over (analytic_clause)---role:----------to group the average according to the rules in Analytic_clause. --Analytic function syntax:--function_name (&LT;ARGUMENT&GT;,&LT;ARGUMENT&GT; ...)--over--(&LT partition-clause><order-by-clause><windowing clause>)--partition clause-partitioning by expression (that is, grouping), if a partition clause is omitted, Then the entire result set is treated as a single group SELECT * from Emp1declare the CURSOR crs_testavg is select Empno,ename,job,sal,depno,avg (SAL) over (partiti On 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;

Detailed Oracle Cursor usage