oracle——SQL复习12

TBNoO

于 2016-04-14 18:11:16 发布

阅读量370

点赞数

分类专栏： Oracle数据库开发

本文链接：https://blog.csdn.net/badyflf/article/details/51153865

版权

Oracle数据库开发专栏收录该内容

48 篇文章 2 订阅

订阅专栏

-- 获取日薪
select t.sal, t.hiredate, last_day(t.hiredate) - add_months(last_day(t.hiredate),-1) days  from emp t;
-- 获取工作日
select level from dual connect by level <= 30  
select to_char(sysdate ,'d') from dual ;
select to_char(add_months(last_day(sysdate),-1)+1   +level-1 ,'d')  wk_day from dual connect by level <= 30   ;

 
select count(*)
  from (select to_char(add_months(last_day(sysdate), -1) + 1 + level - 1,
                       'd') wk_day
          from dual
        connect by level <= 30) t
 where t.wk_day not in (7, 1);

select last_day(sysdate) - add_months(last_day(sysdate), -1)  ,last_day(sysdate) - add_months(last_day(sysdate), -1) from dual; 


 
select count(*)
  from (select to_char(add_months(last_day(sysdate), -1) + 1 + level - 1,
                       'd') wk_day
          from dual
        connect by level <= ( select last_day(sysdate) - add_months(last_day(sysdate), -1) from dual)
        ) t
 where t.wk_day not in (7, 1);


--创建工作日函数
create or replace function ft_wk_day  return integer 
is v_tst integer;
begin 
    select count(*) into v_tst
  from (select to_char(add_months(last_day(sysdate), -1) + 1 + level - 1,
                       'd') wk_day
          from dual
        connect by level <= ( select last_day(sysdate) - add_months(last_day(sysdate), -1) from dual)
        ) t
 where t.wk_day not in (7, 1);
    return v_tst;
end;
  
select  ft_wk_day() from dual ;



--创建有参函数

CREATE OR REPLACE FUNCTION ft_wk_day2(v_date IN DATE) --参数定义
 RETURN INTEGER --返回类型 
 IS
  V_TST INTEGER;
BEGIN 
  SELECT COUNT(*)
    INTO V_TST
    FROM (SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(v_date), -1) + 1 + LEVEL - 1, 'D') WK_DAY
            FROM DUAL
          CONNECT BY LEVEL <= ( select last_day(v_date) - add_months(last_day(v_date), -1) from dual) 
         ) T
   WHERE T.WK_DAY NOT IN (7, 1); 
  RETURN V_TST; --返回语句
END;
 

 


declare 
v_tst date ; 
v_days integer; 
begin 
select t.hiredate into v_tst from emp t where t.ename = 'ALLEN'; 
select ft_wk_day2( v_tst ) into v_days from dual; 
dbms_output.put_line( 'v_tst:' || to_char(v_tst,'YYYYMMDD')   || '    =******=   v_days: ' || v_days) ;
end;





CREATE OR REPLACE FUNCTION FT_EMP_WDAY(V_ENAME IN VARCHAR2) --参数定义
 RETURN INTEGER --返回类型 
 IS
 V_TST DATE ; 
 V_DAYS INTEGER; 
BEGIN 
 SELECT T.HIREDATE INTO V_TST FROM EMP T WHERE T.ENAME = V_ENAME ; 
 SELECT FT_WK_DAY2( V_TST ) INTO V_DAYS FROM DUAL; 
 --DBMS_OUTPUT.PUT_LINE( 'V_TST:' || TO_CHAR(V_TST,'YYYYMMDD')   || '    =******=   V_DAYS: ' || V_DAYS) ;
 RETURN V_DAYS;
END; 
 

SELECT T.ENAME,T.SAL,T.HIREDATE,ft_emp_wday(T.ENAME) FROM EMP T  


--根据名字获取该员工  入职当月的日薪
select t.ename,
       t.sal, 
       round((t.sal + t.comm) / ft_emp_wday('ALLEN'), 2) day_sal,
        ft_emp_wday('ALLEN') wk_day 
  from emp t
 where t.ename = 'ALLEN';
 

select t.ename,
       t.sal,
       round((nvl(t.sal,0) + nvl(t.comm,0)) / ft_emp_wday('SMITH'), 2) day_sal,
       ft_emp_wday('SMITH') wk_day 
  from emp t
 where t.ename = 'SMITH';