2024.07.16 oracle函数练习

--8.创建函数，将emp表中工资低于所在部门平均工资的职工工资加上500，并返回修改了工资的总人数：
create or replace function f1 return number is
    v_count number;
begin
    update emp
    set sal = sal + 500
    where sal < (select avg(sal) 
                 from emp e2 
                 where e2.deptno = emp.deptno);
    select count(*)
    into v_count
    from emp
    where sal > (select avg(sal)
                 from emp e2
                 where e2.deptno = emp.deptno
                 group by deptno)
          and sal <= (select avg(sal)
                      from emp e2
                      where e2.deptno = emp.deptno
                      group by deptno) + 500;
    return v_count;
end;
 declare
    v_updated_count number;
begin
    v_updated_count := f1;
    dbms_output.put_line(v_updated_count);
end;

--9.编写函数nvl3(tab_name,column1,column2)，遍历某表当某表中任意列一和列二的值相等时，回传列一的值，否则回传列二的值：
--当某表中任意列一和列二的值相等时，回传列一的值，否则回传列二的值：
create or replace type tabs is table of varchar2(300);
create or replace function nvl3(tab_name varchar2,column1 varchar2,column2 varchar2) 
return tabs 
is
  str varchar2(300);
  col1 tabs;
  col2 tabs;
  x number := 0;
begin
  str:='select '||column1||','||column2||' from '||tab_name;
  execute immediate str bulk collect into col1,col2; 
  for i in 1..col1.last loop
    for j in 1..col2.last loop
      if col1(i) = col2(j) then
         x := x+1;
      end if;
    end loop;
  end loop;
  if x > 0 then
    return col1;
  elsif x = 0 then
    return col2;
  end if;
exception
  when others then
    dbms_output.put_line(sqlerrm); 
end;
declare
  s tabs;
begin
  s:=nvl3('emp','empno','mgr');
  for i in 1..s.last loop
    dbms_output.put_line(s(i));
  end loop;
end; 

--10.创建一个函数，它接收调用函数中传递过来的两个实参值：date1、date2，计算两个日期内有多少个工作日（
--假设只在周六周天休息，不算国家法定节假日）：
create or replace function cw(
    date1  date,
    date2  date
) return number 
is
    start_date date;
    end_date date;
    wc number := 0;
    current_date date;
begin
    if date1 > date2 then
        start_date := date2;
        end_date := date1;
    else
        start_date := date1;
        end_date := date2;
    end if;
    current_date := start_date;
    while current_date <= end_date loop
        if to_char(current_date,'DY') not in ('SAT', 'SUN') then
            wc := wc + 1;
        end if;
        current_date := current_date + 1;
    end loop;
    return wc;
end;
declare
    v_workdays number;
begin
    v_workdays := cw(to_date('2024-07-01', 'YYYY-MM-DD'), to_date('2024-07-15', 'YYYY-MM-DD'));
    dbms_output.put_line('Number of workdays: '|| v_workdays);
end;
  --11.编写一个函数，传入一个身份证号，计算此人的周岁年龄，并返回此人的性别。输出‘此人xx周岁，性别x。’：
create or replace function idcard (
    i in number
) return varchar2
is
    birth_date date;
    current_date date := sysdate;
    age number;
    sex varchar2(10);
    gender_char char(1);
begin
    birth_date := to_date(substr(i, 7, 8), 'YYYYMMDD');
    age := extract(year from current_date) - extract(year from birth_date);
    if (to_date(extract(year from current_date) || to_char(birth_date, 'MMDD'), 'YYYYMMDD') > current_date) then
        age := age - 1;
    end if;
    gender_char := substr(i, 17, 1);
    if to_number(gender_char) mod 2 = 1 then
        sex := 'male';
    else
        sex := 'female';
    end if;
    return '此人' || age || '周岁，性别' || sex || '。';
end;

declare
    v_result varchar2(100);
begin
    v_result := idcard('123456199001015678'); -- 示例身份证号
    dbms_output.put_line(v_result);
end;

--12.创建一个函数，传入一个字符串，提取字符串中的数字，如果没有数字则返回 -1：
create or replace function st (
    s in varchar2
) return number
is
    exnum varchar2(4000) := ''; 
    ch char(1);
begin
    for i in 1 .. length(s) loop
        ch := substr(s, i, 1);
        if ch between '0' and '9' then
            exnum := exnum || ch;
        end if;
    end loop;
    if exnum is not null then
        return to_number(exnum);
    else
        return -1;
    end if;
end;

 declare
    v_result number;
begin
    v_result := st('abc123def456'); 
    dbms_output.put_line(v_result); 

    v_result := st('qwe');
    dbms_output.put_line(v_result); 
end;
--13.编写一个函数，可以实现输入其中带‘,’的字符串‘1,2,3,a,b,c’，把用‘,’分隔的内容分割成多行显示：
create or replace function f1 (
    newline in varchar2
) return varchar2
is
begin
    return replace(newline, ',', chr(10));
end;

declare 
s varchar(400);
begin
  s:=f1('1,2,3,a,b,c,qwertyuiop,123456+');
  dbms_output.put_line(s);
end;

       
--14.函数说明：字符串按照指定分隔符拆分，截取第几个部位：
/*
参数1：v_str 要分割的字符串，例：‘1-36-47-599-3-0’
参数2：v_cut 分隔符，例：‘-’
参数3：v_index 取第几个元素，例：‘3’
*/

create or replace function f1 (
    v_str    in varchar2,
    v_cut    in varchar2,
    v_index  in number
) return varchar2
is
    v_result varchar2(4000);
    v_temp   varchar2(4000);
    v_count  number := 0;
    v_start  number := 1;
    v_pos    number;
begin
    v_result := null;
    v_temp := v_str;
    while length(v_temp) > 0 loop
        v_pos := instr(v_temp, v_cut);
        if v_pos > 0 then
            v_result := substr(v_temp, 1, v_pos - 1);
            v_temp := substr(v_temp, v_pos + length(v_cut));
        else
            v_result := v_temp;
            v_temp := '';
        end if;
        v_count := v_count + 1;
        if v_count = v_index then
            return v_result;
        end if;
    end loop;
    return null;
end;

--15.编写一函数，完成下面的功能：
/*
1.输入姓名，课程名，成绩。该过程完成对SC表的插入或修改操作；
2.若插入成功，返回成功信息；
3.若该选课信息已经存在，则修改其成绩为输入的成绩；
4.若遇系统错误，返回相关错误信息。
*/