第十四章 函数

[sql]代码库

第十四章 函数
in 只读
out 只写
in out 可读写
 函数就是一个有返回值的过程。
定义一个函数：此函数可以根据雇员的编号查询出雇员的年薪
CREATE OR REPLACE FUNCTION myfun(eno emp.empno%TYPE) 
RETURN NUMBER 
AS
 rsal NUMBER ;
BEGIN
 SELECT (sal+nvl(comm,0))*12 INTO rsal FROM emp WHERE empno=eno ;
RETURN rsal ;
END ;
/
直接写 SQL 语句，调用此函数：
SELECT myfun(7369) FROM dual ;
v_dfd := EXEC myfun(7369);

练习
1: 使用游标 和 loop 循环来显示所有部门的名称
2： 使用游标 和 loop 循环来显示所有部门的的地理位置（用%found 属性）
3:接收用户输入的部门编号，用 for 循环和游标，打印出此部门的所有雇员的所有信息
4：向游标传递一个工种，显示此工种的所有雇员的所有信息
5：用更新游标来为雇员加佣金：
6:编写一个 PL/SQL 程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水的
10%给他们加薪
7：编写一个 PL/SQL 程序块，对所有的 salesman 增加佣金 500
8：编写一个 PL/SQL 程序块，以提升 2 个资格最老的职员为高级职员（工作时间越长，资格越老）
9：编写一个 PL/SQL 程序块，对所有雇员按他们的基本薪水的 20%为他们加薪，如果增加
的薪水大于 300 就取消加薪
 
写一个函数 输入一个员工名字，判断该名字在员工表中是否存在。存在返回 1，不存在返回 0
create or replace function empfun(en emp.ename%type) return number as is_exist number; begin
select count(*) into is_exist from emp where ename=upper(en); return is_exist; end;
/
 
1.写一个函数，传入员工编号，返回所在部门名称
create or replace function myfun(eno emp.empno%type) return varchar as
 name varchar(30); begin
 select d.dname into name  from emp e,dept d where e.deptno = d.deptno and e.empno = eno;  return name; end;
/
 
2.写一个函数，传入时间，返回入职时间比这个时间早的所有员工的平均工资
 
create or replace function getAvgSal(hdate emp.hiredate%type) return number as  esal number; 
begin
 select avg(sal) into esal from emp where hdate>emp.hiredate;  return esal; end;
/
删除一张表重复记录（ID 是自增唯一，重复记录：其他字段都是一样）
非常经典的一道面试题（可能存在很多数据，要求性能比较高） aa
1  louis 20
2  louis 20
3  jimmy 30
4  louis 20
------------------------------------------------------------------
delete from aa where id not in(select min(id) from aa group by name,age);
------------------------------------------------------------------- 
delete test where id in  (select distinct t2.id    from test t1,test t2
  where t1.id<t2.id and t1.name=t2.name and t1.age=t2.age); 
-------------------------------------------------------------------------
 delete test where id in  (select distinct t1.id    from test t1,test t2
  where t1.id<t2.id and t1.name=t2.name and t1.age=t2.age);
--------------------------------------------- create table tmp as select distinct name ,age from aa; truncate table aa; insert into aa select * from tmp; drop table tmp;
----------------------------------------------- create table tmp as select * from aa where 1=2;
DECLARE
 CURSOR mycur IS select * from aa order by id;
 
a1 aa%rowtype; cou number; begin
 for a1 in mycur loop
  
  select count(*) into cou from tmp     where name=a1.name and age=a1.age; if cou=0 then 
  insert into tmp values(a1.id,a1.name,a1.age);
  else
   delete from aa where id = a1.id;
  end if;  end loop; end;
--------------------------------------------------------------
DECLARE
 CURSOR mycur IS select * from aa order by id;
 
a1 aa%rowtype; cou number; begin
 for a1 in mycur loop
  
  select count(*) into cou from tmp     where name=a1.name and age=a1.age;
  
  if cou=0 then     insert into tmp values(a1.id,a1.name,a1.age);   end if;  end loop;
 
 delete from aa;
 insert into aa select * from tmp; end;