oracle 笔记 VI 之游标 (CURSOR)

 游标(CURSOR),很重要

游标:用于处理多行记录的事务
游标是一个指向上下文的句柄(handle)或指针,简单说，游标就是一个指针

1 处理显式游标
  显式游标处理需 4个 PL/SQL 步骤,显示游标主要用于处理查询语句
  (1) 定义游标
  格式:  CURSOR cursor_name [(partment[,parameter]...)] IS select_statement;
   定义的游标不能有 INTO 子句
  (2) 打开游标
   OPEN cursor_name[...];
  PL/SQL 程序不能用 OPEN 语句重复打开一个游标
  (3)提取游标数据
   FETCH cursor_name INTO {variable_list | record_variable};
  (4) 关闭游标
   CLOSE cursor_name;

 

  例 1 查询前 10 名员工的信息  
   declare
    --定义游标
    cursor c_cursor is select last_name,salary  from employees where rownum < 11 order by salary;
    v_name employees.last_name%type;
    V_sal employees.salary%type;
 
    begin
      --打开游标
      open c_cursor;
      -- 提取游标数据
      fetch c_cursor into v_name,v_sal;

       while c_cursor %found loop
             dbms_output.put_line(v_name || ':' || v_sal);
             fetch c_cursor into v_name,v_sal;
        end loop;
  
       --关闭游标
       close c_cursor;
   end;

 

----------------------------------
 练习: 输入部门号 dep_id,查询该部门的平均工资 : avg_sal,员工工资为 salary
       若 salary < avg_sal - 500 工资涨 500
       若 avg_sal - 500 <= salary < avg_sal + 500 工资涨 300
       若 avg_sal + 500 <= salary 工资涨 100;

declare
  --存放平均工资
  avg_sal employees.salary%type;
  -- 存放每个员工的工资
  sal employees.salary%type;
  --存放输入的department_id
  dep_id employees.department_id%type := &dep_id;
  --存放员工 employee_id 的记录
   emp_id employees.employee_id%type;

 
   --定义游标
   cursor emp_cursor is select employee_id,salary  from employees where department_id = dep_id;
begin
   select avg(salary) into avg_sal  from employees where department_id = dep_id;

   open emp_cursor;
   fetch emp_cursor into emp_id,sal;
  
   --循环操作
   while emp_cursor%found loop
         dbms_output.put_line(emp_id || ', ' || sal);
  if sal < avg_sal -500 then
          sal := sal + 500;
         elsif sal < avg_sal - 500 then
          sal := sal +300;
         else
          sal := sal +100;
         end if;
         update employees set salary = sal where employee_id =emp_id;
 fetch emp_cursor into emp_id,sal;

   end loop;

   close emp_cursor;
end

 

游标的for循环:

declare
   avg_sal employees.salary%type;

   sal employees.salary%type;

   dep_id employees.department_id%type := &dep_id;

   cursor emp_cursor(v_dep_id number default 30) is select employee_id,salary
  from employees where department_id = v_dep_id;
begin

   select avg(salary) into avg_sal from employees where department_id = dep_id;

 --游标的 for 循环免除如下操作:1.打开游标 2.关闭游标 3.提取游标数据 4.定义存放游标每一条记录的数据结构(通常为 record)
 for emp_record in emp_cursor(v_dep_id => dep_id) loop
    --dbms_output.put_line(emp_record.employee_id || ',' || emp_record.salary);
    sal := emp_record.salary;

  --确定涨后的工资是多少
  if sal < avg_sal - 500 then
    sal := sal + 500;
  elsif  sal <avg_sal + 500 then
    sal := sal +300;
  else
    sal := sal +100;
  end if;

  --更新操作
  update employees set salary = sal where employee_id = emp_record.employee_id;
end loop;

 

2 处理隐式游标,主要用于处理修改、删除语句
由系统隐含创建的游标称为隐式游标，隐式游标的名字为 SQL

3 游标修改和删除
指在游标定位下，修改或删除表中指定的数据行