oracle学习笔记_典型函数

**

–1.函数：根据员工号查询员工姓名 --IN可省略，是默认值

**

  CREATE or replace FUNCTION f_get_name(i_empno IN varchar2) 
  RETURN varchar2 IS
          i_ename emp.ename%TYPE;
  BEGIN
          SELECT ename INTO i_ename
          FROM emp
          WHERE empno = i_empno;
          return (i_ename);
  END;

–2.根据部门号查询部门名称，使用函数 查询人员姓名及部门名称

create or replace function f_get_name_by_deptno(t_deptno in varchar2)
return varchar2 is
v_dept_name dept.dname%type;
begin
select d.dname into v_dept_name
from dept d where d.deptno=t_deptno;
return v_dept_name;
select f_get_name_by_deptno('201') from emp

**

–3.传入一个员工编号，下属的人员姓名中间用,分隔

**

create or replace function f_lower_name(t_empno in number)
return varchar2 is
v_lower_name emp.ename%type;
begin 
select ename into v_lower_name
from emp