Vernier

[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.

[general structure]

declare

cursor is sql statement

begin

open name;

fetch a variable with the name into;

exit when condition;

close name

end;

[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.