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