PL/SQL program cursor

1. Description of the cursor Syntax: cursor name [(Parameter Name Data Type [, parameter name data type]...)] is SELECT statement; 2. It is used to store multiple rows of data returned by a query. 3. Open the cursor: Open C1; (open the cursor to execute the query). 4. Take the value of a row of the cursor: fetch C1 into pjob; (take a row into the variable) 5. Close the cursor: Close C1; (close the cursor to release resources) 6. Note: the above pjob must be of the same type as the job column in the EMP table: Definition: pjob EMP. job % type; Example 1: Salary Based on the title of the employee, President 1000 yuan, Manager 800 yuan, other staff 400 yuan Java Program SQL> Update EMP set sal = (Case job when 'presiind' Then SAL + 1000 when 'manger 'Then SAL + 800 else Sal + 400end ); PL/SQL program declare -- Define the empno for the cursor query and the job cursor C1 is select empno, job from EMP; -- declare a variable. This variable is consistent with the job field type in the EMP table. emp_job EMP. job % type; -- declare a variable. This variable is of the same type as the empno field in the EMP table. emp_empno EMP. empno % type; -- start begin -- open the cursor open C1; -- loop start loop -- retrieve the cursor fetch C1 into emp_empno, emp_job; exit when C1 % notfound; -- determine whether the cursor exists. If yes, execute the following command. Otherwise, if emp_job = 'President 'Then update EMP set sal = Sal + 1000 where empno = emp_empno is not executed; elsif emp_job = 'manager' then update EMP set sal = Sal + 800 where empno = emp_empno; else update EMP set sal = Sal + 1000 where empno = emp_empno; end if; end loop; -- exit loop close C1; -- close the cursor end; -- end ID Example 2: Find the position declare emp_job EMP in 7369. job % type; begin select job into emp_job from EMP where empno = 7369; ----- into method, put the queried job name in emp_job dbms_output.put_line (emp_job); end; locate all jobs in EMP declare -- Define the cursor query job cursor C1 is select job from EMP; -- declare a variable. This variable is consistent with the job field type in the EMP table. emp_job EMP. job % type; -- start begin -- open the cursor open C1; -- loop start loop -- retrieve the cursor fetch C1 into emp_job; exit when C1 % notfound; -- determine whether the cursor exists. If yes, execute the following command; otherwise, do not execute dbms_output.put_line (emp_job); End loop; -- exit loop close C1; -- close cursor end; -- end ID Example 3: find all enamedeclarecursor C1 is select * from EMP; emp_row EMP % rowtype; beginopen C1; loopfetch C1 into emp_row; exit when C1 % notfound; terminate (emp_row.ename); End loop; close C1; end; example 4: declarecursor C1 (emp_deptno number) is select * from EMP where deptno = emp_deptno; emp_row EMP % rowtype; beginopen C1 (10); dbms_output.put_line ('employee info with Department Number 10 '); loop fetch C1 into emp_row; exit when C1 % notfound; dbms_output.put_line ('employee No. '| emp_row.empno | 'employee name' | emp_row.ename | 'employee payroll' | emp_row.sal | 'employee Department No. '| emp_row.deptno ); end loop; close C1; end; Result: employee information employee No. 7782 employee name Clark employee salary 2450 employee Department No. 10 employee No. 7839 employee name King employee salary 5000 employee Department No. 10 employee No. 7934 employee name Miller employee salary 1300 employee Department Number 10 example 5 with parameter: exercise SQL with parameter cursor> declare 2 cursor C1 (emp_deptno number) is select * from EMP where deptno = emp_deptno; 3 emp_row EMP % rowtype; 4 Begin 5 open C1 (10 ); 6 loop 7 fetch C1 into emp_row; 8 exit when C1 % notfound; 9 dbms_output.put_line ('employee name' | emp_row.ename | emp_row.job); 10 end loop; 11 close C1; 12 end; 13/Result: employee name clarkmanager employee name kingpresident employee name millerclerk
Note:
 
 
Before opening a cursor, it is best to determine whether the cursor is already open.
 
 
Judging by isopen, the format is as follows:
 
 
Cursor % isopen
 
 
If mycur % isopen
 
 
Then
 
 
NULL;
 
 
Else
 
 
Openmycur;
 
 
End if;