2021-08-11

游标

--隐式游标：常用属性使用，隐式游标固定门sql
declare
begin
  update employees e set e.salary = e.salary +&新加工资 where e.employee_id = &员工编号;
  --影响到数据行返回true,
  if sql%found then
    dbms_output.put_line('成功更新'||sql%rowcount||'行数据');
  end if;
  commit;
  if sql%isopen then
    dbms_output.put_line('游标打开');
  else
   dbms_output.put_line('游标关闭'); 
  end if;
  end;
  
  
 select e.* from employees e where e.employee_id = 105;
 --使用隐式游标和for循环遍历表的所有记录
 declare
 
 begin
   for student_row in (select *from student)--查询结果放到隐式游标，使用for循环遍历游标的每一条数据
     loop
       dbms_output.put_line('编号：'||student_row.id||'，姓名：'||student_row.name||'，生日：'||student_row.birthday||'，创建时间：'||student_row.create_time);
       
       
     end loop;
   end;
   
   
   -- 使用显示游标遍历表所有记录
declare 
   student_row student%rowtype;
   --使用关键字cursor 声明游标student_cursor病初始化
   cursor student_cursor is select * from student order by id; 
begin
  --打开游标
  open student_cursor;
  loop
    --逐行读取数据赋值给变量student_row
    fetch student_cursor into student_row;
    --游标中没有退出循环
    exit when student_cursor%notfound;
    dbms_output.put_line('编号：'||student_row.id||'，姓名：'||student_row.name||'，生日：'||student_row.birthday||'，创建时间：'||student_row.create_time);
    
  end loop;
  --关闭游标
  
  close student_cursor;
  end;
--带参数的显式游标使用，根据部门编号查询员工信息
declare
  emp_row employees%rowtype;
  --声明带参数的游标，多个参数逗号隔开
  cursor emp_cursor(dept_no employees.department_id%type) is select e.* from employees e where e.department_id=dept_no;
begin
  --打开游标，传递对应的参数值
  open emp_cursor(&部门编号);
  loop
    --逐行读取游标数据赋值给变量emp_row
    fetch emp_cursor into emp_row;
    --游标中没有数据退出循环
    exit when emp_cursor%notfound;
    dbms_output.put_line('编号：'||emp_row.employee_id);
  
  end loop;
  --关闭游标
  close emp_cursor;
  
  end;
  
  
  --带参数的显示游标使用，给指定的部门中的所有员工加薪
  
declare
   emp_row employees%rowtype;
   cursor emp_cursor(dep_no employees.department_id%type) is select e.* from employees e where e.department_id=dep_no for update;

begin
  open emp_cursor(&部门编号);
  loop
    fetch emp_cursor into emp_row;
    exit when emp_cursor%notfound;
    update employees e set e.salary=emp_row.salary+100 where current of emp_cursor;
  end loop;
  close emp_cursor; 
  end;


--循环游标=显式游标+for循环
declare
   emp_row employees%rowtype;
   cursor emp_cursor is select * from employees;
begin
  for emp_row in emp_cursor
  loop
    dbms_output.put_line('编号'||emp_row.employee_id||'薪水'||emp_row.salary);
  end loop;
   end;
  
--创建ref游标
declare
--声明ref游标类型emp_ref_cursor 指定ref游标返回类型是emp表的行类型return emp%rowtype
    type emp_ref_cursor is ref cursor;
    --使用enp_ref_cuesor 类型声明游标变量enp_cursor
    emp_cursor emp_ref_cursor;
    emp_row employees%rowtype;

begin
  --打开游标并初始化值
  open emp_cursor for select e.* from employees e;
  loop
    fetch emp_cursor into emp_row;
    exit when emp_cursor%notfound;
     dbms_output.put_line('编号'||emp_row.employee_id||'薪水'||emp_row.salary);
  end loop;
  close emp_cursor;
  end;


 ---使用ref游标执行动态sql
 declare
--声明ref游标类型emp_ref_cursor，执行动态sql游标不能有返回值
    type emp_ref_cursor is ref cursor;
    --使用emp_ref_cursor 类型声明游标变量emp_cursor
    emp_cursor emp_ref_cursor;
    emp_row employees%rowtype;


begin
  --打开游标病初始化值，使用动态sql赋值，动态sql用单引号括起来，参数占位符，using赋值
  open emp_cursor for ' select e.* from employees e where e.department_id=:1' using &部门编号;
  loop
      fetch emp_cursor into emp_row;
      exit when emp_cursor%notfound;
      dbms_output.put_line('编号'||emp_row.employee_id||'薪水'||emp_row.salary);
  end loop;
  
  close emp_cursor;
  end;
  
  --使用显示游标完成，给各个部门最低工资员工加薪1000
  --使用sql打印日期2021-01-01到2021-12-31s所有星期天的日期（yyyy-mm-dd）
  --往表里student表插入10万条数据，id从1-100000，年龄随机生成，20-30之间。