2024.07.12oracle函数练习

--1.创建一个函数，以员工号为参数，返回该员工的工资：
create or replace function f1 (
    eno in emp.empno%type  
) 
return number
is
    vsal emp.sal%type; 
begin
    execute immediate 'SELECT sal FROM emp WHERE empno = :1' into vsal using eno;
    return vsal;
exception
    when no_data_found then
        return null;
    when others then
        dbms_output.put_line('Error: ' || sqlerrm);
        return null;
end;

declare
  v number;
begin
  v:=f1(7749);
  dbms_output.put_line(v);
  dbms_output.put_line(f1(7749));
end;
--2.创建一个函数，以部门号为参数，返回该部门的平均工资：
create or replace function f1 (
    deptno in emp.deptno%type  
) 
return number
is
    dn number; 
begin
    execute immediate 'SELECT avg(sal) FROM emp WHERE deptno = :1' into dn using deptno;
    return dn;
exception
    when no_data_found then
        return null;
    when others then
        dbms_output.put_line('Error: ' || sqlerrm);
        return null;
end;

declare
  v number;
begin
  v:=f1(10);
  dbms_output.put_line(v);
end;
--3.创建一个函数，以员工号为参数，返回该员工所部门的平均工资：

create or replace function f1 (
    eno in emp.empno%type  
) 
return number
is
    vsal number; 
begin
    execute immediate 'SELECT avg(sal) FROM emp WHERE deptno = (select deptno from emp where empno=:1)' into vsal using eno;
    return vsal;
exception
    when no_data_found then
        return null;
    when others then
        dbms_output.put_line('Error: ' || sqlerrm);
        return null;
end;

declare
  v number;
begin
  v:=round(f1(7749));
  dbms_output.put_line(v);
end;

--4.创建一个函数，求一个数的阶乘：
create or replace function f1 (n in number) 
return number
is
    temp number := 1;   
begin
    if n < 0 then
        return null;
    elsif n = 0 or n = 1 then
        return 1;
    else
        for i in 1..n loop
            temp := temp * i;
        end loop;
        return temp;
    end if;
exception
    when others then
        dbms_output.put_line('Error: ' || sqlerrm);
        return null;
end;

declare
  v number;
begin
  v:=round(f1(5));
  dbms_output.put_line(v);
end;

--5.创建函数，输入一个员工名字，判断该名字在员工表中是否存在。存在返回 1，不存在返回 0：
create or replace function f1 (en in varchar2) 
return number
is
    v_exists number;  
begin
    begin
        select case when exists (
            select 1
            from emp
            where ename = en
        ) then 1 else 0 end
        into v_exists
        from dual;
    exception
        when others then
            dbms_output.put_line('Error: ' || sqlerrm);
            return null;
    end;
   
    return v_exists;
end;

declare
  v VARCHAR2(20);
begin
  v:=f1('SMITH');
  dbms_output.put_line(v);
end;
--6.创建函数，给定员工编号，返回一句话：“该员工是：xxx，岗位是：xxx，薪资是：xx元。”若该编号对应的员工不存在，则返回：
--“所查工号不存在，请确认输入内容！”：
create or replace function f1 (eno in number) 
return varchar2
is
    en   varchar2(50);  
    jb   varchar2(50);
    sl   number;       
    result varchar2(200); 
begin
    begin
        execute immediate 'select ename, job, sal from emp where empno = :1' 
        into en, jb, sl 
        using eno;
        result := '该员工是：' || en || '，岗位是：' || jb || '，薪资是：' || sl || '元。';
    exception
        when no_data_found then
            result := '所查工号不存在，请确认输入内容！';
        when others then
            result := '错误: ' || sqlerrm;
    end;
    return result;
end;
   
declare
    v_result varchar2(200);
begin
    v_result := f1('7749');  
    dbms_output.put_line(v_result);

    v_result := f1('9999'); 
    dbms_output.put_line(v_result);
end;

--7.模拟length函数，编写一个lengthpro函数，在返回字符串的长度时，如果字符串里包含大写字母、小写字母和数字需要返回提示信息，
--输出“长度是xxx，里面包含大写字母|小写字母|数字（三者有几个显示几个）”：

--6.创建函数，给定员工编号，返回一句话：“该员工是：xxx，岗位是：xxx，薪资是：xx元。”
--若该编号对应的员工不存在，则返回：“所查工号不存在，请确认输入内容！”：
create or replace function f6(eno number)
return varchar2
is

  cnt number;
  str varchar2(500);
  ena emp.ename%type;
  ejob emp.job%type;
  esal emp.sal%type;
begin
  select count(*) into cnt from emp where empno = eno;
  if cnt > 0 then
    select ename,job,sal into ena,ejob,esal from emp where empno = eno;
    str := '该员工是：'||ena||'，岗位是：'||ejob||'，薪资是：'||esal||'元。';
  else
    str := '所查工号不存在，请确认输入内容！';
  end if;
  return str;
exception
  when others then
    dbms_output.put_line(sqlerrm); 
end;

select f6(7369) from dual;

--7.模拟length函数，编写一个lengthpro函数，在返回字符串的长度时，
--如果字符串里包含大写字母、小写字母和数字需要返回提示信息，输出“长度是xxx，
--里面包含大写字母|小写字母|数字（三者有几个显示几个）”：
create or replace function f1(str varchar2)
return varchar2
is
  res varchar2(500);
  n number := 1;
  low number := 0;
  up number := 0;
  numb number := 0;
  s varchar2(500) ;
begin
  loop
    exit when n>length(str);
    s := substr(str,n,1);
    if ascii(s) between 65 and 90 then
      up := up+1;
    elsif ascii(s) between 97 and 122 then
      low := low+1;
    elsif ascii(s) between 48 and 57 then
      numb := numb+1;
    end if;
    n := n+1;
  end loop;
  res := '长度是'||to_char(n-1)||',里面包含大写字母：'||up||'个，小写字母：'||low||'个，数字：'||numb||'个。';
  return res;
exception
  when others then
    dbms_output.put_line(sqlerrm); 
end;

declare
  s varchar2(500);
begin
  s:=f1('qwertyuiopabcABC123');
  dbms_output.put_line(s);
end;