Summary of Oracle database cursor usage methods

An Oracle cursor is divided into a display cursor and an implicit cursor.

Display cursor (Explicit Cursor): A cursor defined in a PL/SQL program, used for querying, is called a display cursor.
Implicit cursors (implicit Cursor): A cursor that is automatically allocated by an Oracle system when a UPDATE/DELETE statement is defined in a non-Pl/sql program and is used in Pl/sql.

One. Display Cursors

1. Use steps
(1) Definition (2) open (3) use (4) to close
2. Use Demo
First, create the test table student, as follows:

The code is as follows:

CREATE TABLE "STUDENT" (
"Stuname" VARCHAR2 (BYTE),
"Stuno" VARCHAR2 (4 BYTE),
"Age" number,
"GENDER" VARCHAR2 (2 CHAR)
)

(1). Using a while loop to process a cursor

Create or replace PROCEDURE PROC_STU1 as


BEGIN


--Display cursor usage, using while loop


Declare


--1. Defines a cursor, named Cur_stu


Cursor Cur_stu is


Select Stuno,stuname from student order by Stuno;


--Define variables to hold the data that the cursor takes out


V_stuno varchar (4);


V_stuname varchar (20);


Begin


--2. Open Cursor Cur_stu


Open cur_stu;


--3. Store the current row of the cursor in a variable


Fetch cur_stu into v_stuno,v_stuname;


While Cur_stu%found--the cursor refers to a data row, the loop continues


Loop


--Print results


Dbms_output. Put_Line (v_stuno| | ' -> ' | | V_stuname);


--Continue to remove the current row that the cursor refers to and place it in the variable


Fetch cur_stu into v_stuno,v_stuname;


End Loop;


Close Cur_stu; --4. Close cursor


End


End PROC_STU1;


(2). Use IF. else instead of while loop processing cursors


Create or replace PROCEDURE PROC_STU2 as


BEGIN


--Display cursor usage, using if judgment


Declare


--1. Defines a cursor, named Cur_stu


Cursor Cur_stu is


Select Stuno,stuname from student order by Stuno;


--Define variables to hold the data that the cursor takes out


V_stuno varchar (4);


V_stuname varchar (20);


Begin


--2. Open Cursor Cur_stu


Open cur_stu;


--3. Store the current row of the cursor in a variable


Fetch cur_stu into v_stuno,v_stuname;


Loop


If Cur_stu%found then--if the cursor cur_stu refers to a row of data


--Print results


Dbms_output. Put_Line (v_stuno| | ' -> ' | | V_stuname);


--Continue to remove the current row that the cursor refers to and place it in the variable


Fetch cur_stu into v_stuno,v_stuname;


Else


Exit


End If;


End Loop;


Close Cur_stu; --4. Close cursor


End


End PROC_STU2;


(3). Using a For loop to process a cursor


Create or replace PROCEDURE PROC_STU3 as


BEGIN


--Display cursor usage, using for loop


Declare


--Defines a cursor, named Cur_stu


Cursor Cur_stu is


Select Stuno,stuname from student order by Stuno;


Begin


For Stu in Cur_stu


Loop


Dbms_output. Put_Line (stu.stuno| | ' -> ' | | Stu.stuname);


--loop do hidden check%notfound


End Loop;


--Auto Close cursor


End


End PROC_STU3;


(4). Common use of exit when processing cursors


Create or replace


PROCEDURE Proc_stu1_1 as


BEGIN


--Display cursor usage, using exit when loop


Declare


--1. Defines a cursor, named Cur_stu


Cursor Cur_stu is


Select Stuno,stuname from student order by Stuno;


--Define variables to hold the data that the cursor takes out


V_stuno varchar (4);


V_stuname varchar (20);


Begin


--2. Open Cursor Cur_stu


Open cur_stu;


Loop


--3. Store the current row of the cursor in a variable


Fetch cur_stu into v_stuno,v_stuname;


Exit when Cur_stu%notfound; --the cursor refers to a data row, then the loop continues


--Print results


Dbms_output. Put_Line (v_stuno| | ' -> ' | | V_stuname);


End Loop;


Close Cur_stu; --4. Close cursor


End


End Proc_stu1_1;

Two. Implicit cursors

1. Use Demo
Create or replace PROCEDURE Proc_stu4 as
BEGIN
--An implicit cursor uses
Update student set stuname= ' Zhang Yanquang ' where stuno= ' 1104 ';
--Inserts a new record if the update does not match
If Sql%notfound Then
INSERT into student (Stuno,stuname,age,gender)
VALUES (' 1104 ', ' Zhang Yanquang ', 18, ' Male ');
End If;
End Proc_stu4;
2. Notes
All SQL statements are executable within the context area because there is a cursor pointing to the context area, and this cursor is
SQL cursors, unlike reality cursors, where SQL cursors do not need to be turned on and off in Pl/sql, but rather in the execution of update,
Delete is automatically turned on and off.
In the example above, the Sql%notfound cursor property is used to determine the execution of the UPDATE statement to determine whether a new record needs to be inserted.

Add:

--declaration cursor; CURSOR cursor_name is select_statement

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


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