oracle——SQL复习09

--创建无参函数
create or replace function fun_test return integer 
is v_tst integer;
begin
    select power(2,2) into v_tst from dual; 
    
    return v_tst;
end;


--调用无参函数
select fun_test from dual; 


--创建有参函数
create or replace function fun_test2(v_t2 in integer) --参数定义
 return integer --返回类型

is v_tst integer;
begin
    select power(v_t2,2) into v_tst from dual;   --函数体
    
    return v_tst;  --返回语句
end;


 
--调用有参函数
select fun_test2(9) from dual; 



/** 计算一个月有多少个工作日的函数 */

select to_char(sysdate,'D') from dual; 
select level from dual connect by level <= 7 ; 
select to_char(sysdate,'D') from dual; 
select to_char(sysdate+level-1,'D') from dual connect by level <= 7 ; 
select last_day( add_months(sysdate,-1) ) + 1 from dual;   
--获取当月的周几数
select to_char(last_day(add_months(sysdate, -1)) + 1 + level - 1, 'D')
  from dual
connect by level <=
           (select last_day(sysdate) - last_day(add_months(sysdate, -1))
              from dual);
--获取工作日
select count(1)
  from (select to_char(last_day(add_months(sysdate, -1)) + 1 + level - 1,
                       'D') wk
          from dual
        connect by level <= (select last_day(sysdate) -
                                    last_day(add_months(sysdate, -1))
                               from dual)) t
 where t.wk not in (1, 7);
 
--创建一个月有多少个工作日的函数
create or replace function fun_workdays(v_day_in in varchar2) --参数定义
 return number --返回类型 
is v_days number;
begin 
select count(1)  into v_days
  from (select to_char(last_day(add_months(to_date(v_day_in,'YYYYMMDD'), -1)) + 1 + level - 1,
                       'D') wk
          from dual
        connect by level <= (select last_day(to_date(v_day_in,'YYYYMMDD')) -
                                    last_day(add_months(to_date(v_day_in,'YYYYMMDD'), -1))
                               from dual)) t
 where t.wk not in (1, 7); 
    return v_days;  --返回语句
end;
  

--调用 工作日 函数 
select fun_workdays('20160502') from dual;