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 dualconnect 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;