oracle复习笔记之PL/SQL程序所要了解的知识点(3)

declare
type emp_record is record(
v_empid employees.employee_id%type,
v_lastName employees.last_name%type,
v_sal employees.salary%type
);
v_emp_record emp_record;
cursor emp_sal_cursor is select employee_id,last_name,salary from employees where 
department_id = 80;
begin 
open emp_sal_cursor;
fetch emp_sal_cursor into v_emp_record;
while emp_sal_cursor%found loop
dbms_output.put_line('employee_id:'||v_emp_record.v_empid||', '||'last_name:'|| 
v_emp_record.v_lastName||', '||'salary:'||v_emp_record.v_sal);
fetch emp_sal_cursor into v_emp_record;
end loop;
close emp_sal_cursor;
end; 

declare
cursor emp_sal_cursor is select employee_id,last_name,salary from employees where 
department_id = 80;
begin 
for c in emp_sal_cursor loop
dbms_output.put_line('employee_id:'||c.employee_id||', '||'last_name:'||c.last_name||', 
'||'salary:'||c.salary);
end loop;
end;

declare 
  cursor emp_cursor is select employee_id,salary from employees;
  v_empid employees.employee_id%type;
  v_sal employees.salary%type;
  v_temp number(4,2);
begin 
  open emp_cursor;
  fetch emp_cursor into v_empid,v_sal;
  while emp_cursor%found loop
   if v_sal < 5000 then v_temp:=0.05;
   elsif v_sal < 10000 then v_temp:=0.03;
   elsif v_sal < 15000 then v_temp:=0.02;
   else v_temp:=0.01;
   end if;
  dbms_output.put_line(v_empid||','||v_sal);
  update employees
  set salary = salary * (1+v_temp)
  where employee_id = v_empid;
  fetch emp_cursor into v_empid,v_sal;
  end loop;
  close emp_cursor;
end;

declare 
  cursor emp_cursor is select employee_id,salary from employees;
  v_temp number(4,2); 
begin 
  for c in emp_cursor loop
   if c.salary <5000 then v_temp:=0.05;
   elsif c.salary <10000 then v_temp:=0.03;
   elsif c.salary <15000 then v_temp:=0.02;
   else v_temp:=0.01;
  end if;
  update employees
  set salary = salary * (1+v_temp)
  where employee_id = c.employee_id;
  end loop;
end;

begin 
  update employees
  set salary = salary + 10
  where employee_id = 1001;
   if sql%notfound then dbms_output.put_line('查无此人');
   end if;
end;

declare
  v_sal employees.salary%type;
begin 
  select salary into v_sal from employees 
  where employee_id > 100;
  dbms_output.put_line(v_sal); 
exception
  when too_many_rows then dbms_output.put_line('输出的行数过多');
  when others then dbms_output.put_line('出现其它的异常了');
end;