Oracle cursor use full solution

Oracle cursor use full solution

This <Oracle cursor use full solution> document covers almost all aspects of Oracle cursor use, all passed the test

-- Declare the CURSOR; CURSOR cursor_name IS select_statement

-- For Loop cursor
-- (1) define a cursor
-- (2) define the cursor variable
-- (3) use the cursor for a for Loop
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 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 cursor
-- You must enable and disable it explicitly when using it.

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
-- Extract a row of data to c_row
Fetch c_job into c_row;
-- Determine whether to extract the value. Exit if the value is not obtained.
-- The value c_job % notfound is false.
-- The value c_job % notfound is not obtained. 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 the cursor
Close c_job;
End;

-- 1: execute any update operation. Use the attributes % found, % notfound, % rowcount, and % isopen of the implicit cursor SQL statement to observe the execution of the update statement.
Begin
Update emp set ENAME = 'eark' 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 valid row'); -- determines 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 ('hahaha ');
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 writable 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 writable rows ');
End;



-- 2. The cursor and loop are used to display the names of all departments.
-- Cursor Declaration
Declare
Cursor csr_dept
Is
-- Select statement
Select DNAME
From Depth;
-- Specifies the row pointer. This statement should be a variable of the same type as the csr_dept row.
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. Use a cursor and a while loop to display the geographical location of all departments (use the % found attribute)
Declare
-- Cursor Declaration
Cursor csr_TestWhile
Is
-- Select statement
Select LOC
From Depth;
-- Specify the row pointer
Row_loc csr_TestWhile % rowtype;
Begin
-- Open the cursor
Open csr_TestWhile;
-- Feed data to the first line
Fetch csr_TestWhile into row_loc;
-- Test whether data exists and execute a loop
While csr_TestWhile % found loop
Dbms_output.put_line ('department location: '| row_loc.LOC );
-- Feed data to the next row
Fetch csr_TestWhile into row_loc;
End loop;
Close csr_TestWhile;
End;
Select * from emp

 

-- 4. Receive the Department number entered by the user. Print all information of all employees of the Department using the for loop and cursor (use the cyclic cursor)
-- CURSOR cursor_name [(parameter [, parameter],...)] IS select_statement;
-- Syntax for defining parameters: 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 (20) loop
Dbms_output.put_line ('employee ID: '| r_emp.EMPNO | 'employee name:' | r_emp.ENAME | 'salary: '| r_emp.SAL );
End loop;
End;
Select * from emp
-- 5: pass a type of work to the cursor to display all information of all employees of this type of work (using the parameter cursor)
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 ('cler') loop
Dbms_output.put_line ('employee ID '| r_job.EMPNO | ''| 'employee name' | r_job.ENAME );
End loop;
End;
SELECT * FROM EMP

-- 6: use an update cursor to add commission to employees: (use if to create an emp1 table that is the same as the emp table and 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: compile a pl/SQL program block. All employees whose names start with 'A' or 'S' are paid according to their basic salary (sal) 10% to give them a raise (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 | '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: compile a PL/SQL block to increase the Commission (comm) by 500 for all salesman.
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: compile a PL/SQL program block to improve the qualification of the two oldest employees as managers (the longer the working hours, the older the qualifications)
-- (Tip: You can define a variable as a counter to control the cursor to extract only two pieces of data. You can also find the two most qualified employees 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 Time:' | r_testComput.HIREDATE );
-- Speedometer minus 1
Top_two: = top_two-1;
FETCH crs_testComput INTO r_testComput;
End loop;
Close crs_testComput;
End;

-- 10: compile a PL/SQL program block to raise salaries for all employees based on 20% of their basic salary (sal,
-- If the salary increases by more than 300, cancel the salary increase (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 | ': salary increase failed. '|' Salary: '| r_UpdateSal.SAL );
Else
SalInfo: = r_UpdateSal.SAL + salAdd;
Dbms_output.put_line (r_UpdateSal.ENAME | ': salary increase succeeded.' | 'salary changed to: '| salInfo );
End if;
Update emp1 set SAL = salInfo where current of crs_UpadateSal;
End loop;
End;

-- 11: how many years, months, and days each employee has worked
-- Approximation
-- CEIL (n) function: obtains the smallest integer greater than or equal to the value n.
-- FLOOR (n) function: returns the largest integer less than or equal to the value n.
-- Truc usage http://publish.it168.com/2005/1028/20051028034101.shtml
Declare
Cursor
Crs_WorkDay
Is
Select ENAME, HIREDATE, trunc (months_between (sysdate, hiredate)/12) as spandyears,
Trunc (mod (months_between (sysdate, hiredate), 12) AS months,
Trunc (mod (sysdate-hiredate, 365), 12) as days
From emp1;
R_WorkDay crs_WorkDay % rowtype;
Begin
For r_WorkDay in crs_WorkDay loop
Dbms_output.put_line (r_WorkDay.ENAME | 'working now' | r_WorkDay.SPANDYEARS | 'year, 0' | r_WorkDay.months | 'month, 0' | r_WorkDay.days | 'day ');
End loop;
End;

-- 12: Enter the Department number and execute the statement according to the following salary increase ratio (Use CASE to create an emp1 table, modify the data in the emp1 table), and output the data before and after the update.
-- Deptno raise (%)
-- 10 5%
-- 20 10%
-- 30 15%
-- 40 20%
-- The salary increase ratio is based on the 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: Judge the salary of each employee. If the employee's salary is higher than the average salary of his/her department, the employee's salary will be reduced by 50 yuan, and the salary before and after the update and the employee's name will be output, department ID.
-- AVG ([distinct | all] expr) over (analytic_clause)
--- Function:
-- Calculate the average value of the group according to the rules in analytic_clause.
-- Analysis Function Syntax:
-- FUNCTION_NAME (<argument>, <argument> ...)
-- OVER
-- (<Partition-Clause> <Order-by-Clause> <wing Clause>)
-- PARTITION clause
-- Partition by expression (Group). If the partition clause is omitted, all result sets are considered 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;