plsql命名块练习（3）

1. 定义一个函数，查询某个员工的年收入
2. 记录类型作为返回类型，根据指定的部门号返回其对应的部门信息
   函数创建：
3. 定义函数，部门编码作为输入参数，查询出该部门的员工总数。
4. 定义包规范，分别定义存储过程完成部门信息的添加和删除操作，定义函数实现根据传递的部门号返回部门信息。
5. 定义一个包，创建一个过程，显示雇员表中10部门的员工信息。要求用静态游标
6. 只有在每个月的10号才允许办理职员的入职或离职手续，其它时间不允许操作
7. 每天12点以后，不允许修改雇员的工资和奖金
8. 每一位雇员都要根据收入缴所得税，假设所得税的上缴原则为：2000以下上缴3%，2000—5000上缴8%，5000以上上缴10%。现在要求建立一张新的数据表，可以记录出雇员的编号、姓名、工资、奖金和上缴所得税数据，并且在每次修改雇员表中sal和comm字段后可以自动更新记录。
9. 写一个函数 输入一个员工名字，判断该名字在员工表中是否存在。存在返回 1，不存在返回 0
10. 写一个函数，传入员工编号，返回所在部门名称

--1.定义一个函数，查询某个员工的年收入
create or replace function f1(f_en emp.ename%type) return number
is 
years number;
begin
  select nvl2(comm,sal+comm,sal) into years from emp where ename=f_en;
  return 12*years;
end;

begin
  dbms_output.put_line(f1('KING'));
end;

--2.定义一个函数，记录类型作为返回类型，根据指定的部门号返回其对应的部门信息
create or replace function f1(f_deptno dept.deptno%type) return dept%rowtype
is
  rec dept%rowtype;
begin
  select * into rec from dept where deptno=f_deptno;
  return rec;
end;

declare
v_dn dept.deptno%type:='&a';
begin
dbms_output.put_line(f1(v_dn).deptno||' '||f1(v_dn).dname||' '||f1(v_dn).loc);
end;

--3. 定义函数，部门编码作为输入参数，查询出该部门的员工总数。
create or replace function f1(f_dn emp.deptno%type) return number
is 
c number;
begin
  select count(empno) into c from emp where deptno=f_dn;
  return c;
end;

begin
  dbms_output.put_line(f1(10));
end;

--6. 只有在每个月的10号才允许办理职员的入职或离职手续，其它时间不允许操作
create or replace trigger t1 before insert or delete on emp 
declare
  riqi varchar2(15);
begin
  riqi:=to_char(sysdate,'dd');
  if riqi!=10 then 
    raise_application_error(-20101, '非10号不能办理入职和离职');
  end if;
end;
insert into emp(empno,ename,sal) values(2622,'liu',3000);

--7. 每天12点以后，不允许修改雇员的工资和奖金
create or replace trigger t1 before update of sal,comm on emp for each row
declare
 shijian varchar2(10);
begin
  shijian:=to_char(sysdate, 'HH24');
  if to_number(shijian)>12 then --将varchar2类型转换为
    raise_application_error(-20100, '每天12点以后，不允许修改雇员的工资和奖金');
  end if;
end;

update emp set sal=40 where empno=2612;

-- 8. 每一位雇员都要根据收入缴所得税，假设所得税的上缴原则为：2000以下上缴3%，2000—5000上缴8%，
--    5000以上上缴10%。现在要求建立一张新的数据表，可以记录出雇员的编号、姓名、工资、奖金和上缴所
--    得税数据，并且在每次修改雇员表中sal和comm字段后可以自动更新记录。
create table c1(
empno number(4),
ename varchar2(10),
sal number,
comm number,
tax number
);
create or replace trigger t1 after insert or update of sal,comm on emp
declare
  cursor cur is select * from emp;
  rec cur%rowtype;
  tax number;
  ico emp.sal%type;
begin
  open cur;
  loop
  fetch cur into rec;
  exit when cur%notfound;
    ico:=rec.sal+nvl(rec.comm,0);
    if ico<2000 then
      tax:=ico*0.03;
    elsif  ico between 2000 and 5000 then
      tax:=ico*0.08;
    elsif  ico>5000 then
      tax:=ico*0.1;
    end if;
    insert into c1 values(rec.empno,rec.ename,rec.sal,rec.comm,tax);
  end loop;
end;
update emp set comm=100 where empno=2612;
select * from c1;

--9. 写一个函数 输入一个员工名字，判断该名字在员工表中是否存在。存在返回 1，不存在返回 0
create or replace function f2(em emp.ename%type) return number
is
num number(2);
n number(2);
begin
  select count(*) into num from emp where ename=em;
  if num>0 then n:=1;
  elsif num=0 then n:=0;
  end if;
  return n;
end;

begin
dbms_output.put_line(f2('wan'));
end;

--10. 写一个函数，传入员工编号，返回所在部门名称
create or replace function f1(emn emp.empno%type) return varchar
is
  dm dept.dname%type;
  dn dept.deptno%type;
begin
  select deptno into dn from emp where empno=emn;
  select dname into dm from dept where deptno=dn;
  return dm;
  exception
   when no_data_found then 
    dbms_output.put_line('无数据');
end;
begin
  dbms_output.put_line(f1(1111));
end;