[introduction] we can use the cursor to locate our mouse. If we want to find a specific row in the table, we can use the cursor to locate it.
cursor is sql statement
fetch a variable with the name into;
exit when condition;
[example 1] use the cursor to output the names of students belonging to the school of computer science.
declare cursor st_cur is select sname from student where deptno=(select deptno from sdept where dname like '%Computer%'); x student.sname%type; begin open st_cur; loop fetch st_cur into x; exit when st_cur%notfound; dbms_output.put_line(x); end loop; close st_cur; end;
[example 2] use the cursor to output the name and student number of the students in the computer school, as well as the number of the head office.
declare cursor st_cur is select sno,sname from student where deptno=(select deptno from sdept where dname like '%Computer%'); type rec is record( --Can also is table y student.sno%type, --y student.sno%type; x student.sname%type --x student.sname%type; ); m rec; begin open st_cur; loop fetch st_cur into m; -- fetch st_cur into y,x; exit when st_cur%notfound; dbms_output.put_line(m.y||m.x); -- dbms_output.put_line(y||x); end loop; dbms_output.put_line(st_cur%rowcount); --Total output lines close st_cur; exception when no_data_found then dbms_output.put_line('no values'); end;
[introduction] therefore, it can be seen from the above that the operation of querying information is better to use a cursor than to directly use a for loop to traverse. What are the advantages of cursors? Answer: it can flexibly insert and modify the value of a specific row. If it meets the requirements, it will submit the modification. If it does not meet the requirements, it will roll back without modification.
[example 3] use the cursor to operate the emp table to increase the salary of employees. The president is 30 /, the experience is 20 /, the others are 10 /, and the final total salary is limited to 5W. If it is exceeded, cancel.
declare cursor emp_c is select job from emp for update of sal; v_job emp.job%type; v_sal emp.sal%type; begin open emp_c; loop fetch emp_c into v_job; exit when emp_c%notfound; if v_job='president' then update emp set sal=sal*1.3 where current of emp_c; elsif v_job='manager' then update emp set sal=sal*1.2 where current of emp_c; else update emp set sal=sal*1.1 where current of emp_c; end if; end loop; select sum(sal) into v_sal from emp; if v_sal<50000 then commit; --Submission dbms_output.put_line('ok'); else rollback; --RollBACK dbms_output.put_line('error'); end if; close emp_c; end;
[supplement] implicit cursors are used when select into\update\delete is used.