SQL database Cursors

This document contains almost every aspect of Oracle cursor use, and all passes the test

--declaring cursors; cursor cursor_name is select_statement

--for Loop Cursors
--(1) Defining cursors
--(2) Defining cursor variables
--(3) Use this cursor with a for loop
Declare
--type definition
Cursor C_job
Is
Select Empno,ename,job,sal
From EMP
where job= ' MANAGER ';
--Define a cursor variable v_cinfo c_emp%rowtype, which is a row of data type 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 opened and closed 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
--Fetch a row of data to C_row
Fetch c_job into C_row;
--to interpret if the value is extracted and exit without taking a value
--Take the value C_job%notfound is False
--No 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, using the implicit cursor SQL property%found,%notfound,%rowcount,%isopen to observe the execution of the UPDATE statement.
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 points to a valid row ');---determine if the cursor is pointing 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 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 Declaration
Declare
Cursor Csr_dept
Is
--select statements
Select Dname
From Depth;
--Specify the row pointer, which should be a variable that specifies the same type as the Csr_dept row
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, 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 statements
Select LOC
From Depth;
--Specify the 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 the data to the next line
Fetch csr_testwhile into Row_loc;
End Loop;
Close Csr_testwhile;
End
SELECT * FROM emp



       
--4, receives the user input department number, uses for loops and cursors, prints out all the information of 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
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: Pass a job to the cursor, showing all the information for 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: 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 and increase commission for 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 increase the 2 most qualified staff as manager (the longer the work time, the older the qualification)
--(Hint: You can define a variable as the counter control cursor to extract only two data, or you can declare the cursor at the time of the oldest two people in the employee to be found in 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);
--speed reducer minus 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 that raises 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
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 to work each employee to zero how many months zero how many days output out
--Approximate
--ceil (n) function: takes 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 usage 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 (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 Working ' | | r_workday.spandyears| | ' Year, 0 ' | | r_workday.months| | ' Month, 0 ' | | r_workday.days| | ' Days ');
End Loop;
End
  
--12: Enter the department number, execute according to the following increase rate (in case, 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] 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: 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)
---effect:
--in accordance with the rules in Analytic_clause to find the grouping average.
--Analytic function syntax:
--function_name (<argument>,<argument> ...)
--over
--(<partition-clause><order-by-clause><windowing clause>)
--partition clause
--partitioning by expression (that is, grouping), if a partition clause is omitted, then all result sets are treated as 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

SQL database Cursors