Oracle explicit cursor small example

1: The cursor instance displays the name and Department

DECLARE

Name VARCHAR2 (50 );

Department_name varchar (20); -- defines two variables to store the content in the employees table and the departments table.

CURSOR emp_cur IS -- defines the CURSOR emp_cur

SELECT name, department_name

FROM employees e, departments d

WHERE e. department_id = d. department_id; -- selects the names and departments of all employees.

BEGIN

OPEN emp_cur; -- OPEN the cursor

LOOP

FETCH emp_cur INTO name, department_name; -- each time a row of data is put INTO the variable, the cursor moves behind

Exit when emp_cur % NOTFOUND; -- EXIT the loop WHEN the cursor cannot obtain data

Dbms_output.put_line (name | 'in' | department_name); -- output name and Department

End loop;

CLOSE emp_cur;

END;

 

2: Example 2,

When the company went public, it decided to raise the salary for its employees. The company's employment time did not exceed in one year, and the price of Yuan was capped.

DECLARE

Hire_date DATE; -- defines two variables to store the content in the employee table.

E_id NUMBER;

CURSOR emp_cur IS -- defines the CURSOR emp_cur

SELECT id, hire_date

FROM employees; -- select the names and start time of all employees

BEGIN

OPEN emp_cur; -- OPEN the cursor

LOOP

FETCH emp_cur INTO e_id, hire_date; -- each time a row of data is stored in a variable, the cursor moves behind

Exit when emp_cur % NOTFOUND;

IF 100 * (2010-to_char (hire_date, 'yyyy') <1000 THEN -- determine the relationship between the duration and salary

UPDATE salary

SET salaryvalue = salaryvalue + 100 * (2010-to_char (hire_date, 'yyyy '))

WHERE employeeid = e_id;

ELSE

UPDATE salary

SET salaryvalue = salaryvalue + 1000;

WHERE employeeid = e_id;

End if;

End loop;

END;

3: Use cyclic cursors to simplify cursor reading

Syntax:

FOR <type> IN <cursor Name> LOOP

-- Operate data of each row

End loop;

Example:

DECLARE

TYPE employee_record is record -- defines a record type variable, which contains two variables: name and department_name

(

Name VARCHAR2 (50 ),

Department_name varchar (20)

);

CURSOR emp_cur IS

SELECT name, department_name

FROM employee e, departments d

WHERE e. department_id = d. department_id;

BEGIN

FOR employee_record IN emp_cur LOOP

Dbms_output.put_line

(Employee_record.name | 'in' | employee_record.department_name );

End loop;

END;