oracle——SQL复习03

-- 空值转换
select nvl(null,0) from dual;

-- 有值指向第一个,无值指向第二个
select nvl2('aaa',1,2) from dual; 
select nvl2(null,1,2) from dual;
 

--获取这个月末,上个月末的时间
select last_day(trunc(sysdate)) from dual; 
select last_day(add_months(trunc(sysdate),-1))  from dual; 

--获取当前月的总天数
select last_day(trunc(sysdate)) - last_day(add_months(trunc(sysdate), -1)) days
  from dual;


-- 当前月的工作日
select to_char(sysdate,'D') - 1  from dual; 

--获取当月的1号
select last_day(add_months(sysdate,-1)) + 1  from dual; 

--获取上月的1号
select last_day(add_months(sysdate,-2)) + 1  from dual; 


--利用connet by level 函数 获取 当月的周数
select to_char((last_day(add_months(sysdate, -1)) + 1) + level - 1,
                       'D') - 1 week
          from dual
        connect by level <= 30
        
        
--获取当前月的工作日
select count(1)
  from (select to_char((last_day(add_months(sysdate, -1)) + 1) + level - 1,
                       'D') - 1 week
          from dual
        connect by level <= (select last_day(trunc(sysdate)) -
                                    last_day(add_months(trunc(sysdate), -1)) days
                               from dual) ) t
 where t.week not in (0, 6) ;


-- 获取上个月的工作日
select count(1)
  from (select to_char((last_day(add_months(sysdate, -2)) + 1) + level - 1,
                       'D') - 1 week
          from dual
        connect by level <=  (select last_day(add_months(trunc(sysdate), -1)) -
                                    last_day(add_months(trunc(sysdate), -2)) days
                               from dual) ) t
 where t.week not in (0, 6) ;
 
 
----获取当前月的休息日
select count(1)
  from (select to_char((last_day(add_months(sysdate, -1)) + 1) + level - 1,
                       'D') - 1 week
          from dual
        connect by level <= (select last_day(trunc(sysdate)) -
                                    last_day(add_months(trunc(sysdate), -1)) days
                               from dual)) t
 where t.week in (0, 6);



--创建一个存储过程
create or replace procedure sql_week
as
  begin 
      select count(1)
        from (select to_char((last_day(add_months(sysdate, -1)) + 1) + level - 1,
                             'D') - 1 week
                from dual
              connect by level <= (select last_day(trunc(sysdate)) -
                                          last_day(add_months(trunc(sysdate), -1)) days
                                     from dual) ) t
       where t.week not in (0, 6)  
   end; 




SELECT to_date('20160315','YYYYMMDD') FROM DUAL ;




declare 
       v_week varchar2(20) ;
begin
  select count(1)
    into v_week
    from (select to_char((last_day(add_months(to_date('20160315','YYYY-MM-DD'), -1)) + 1) + level - 1,
                         'D') - 1 week
            from dual
          connect by level <= (select last_day(trunc(to_date('20160315','YYYY-MM-DD'))) -
                                      last_day(add_months(trunc(to_date('20160315','YYYY-MM-DD')), -1)) days
                                 from dual)) t
   where t.week not in (0, 6); 
   
   dbms_output.put_line( '当月的工作日是是是是:' || v_week);  
end;


                       
                                 
create or replace procedure sql_week_in(current_day in varchar2) as
  v_week int;
begin
  select count(1)
    into v_week
    from (select to_char((last_day(add_months(to_date(current_day, 'YYYY-MM-DD'), -1)) + 1) + level - 1,  'D') - 1 week
            from dual
          connect by level <=
                     (select last_day(trunc(to_date(current_day, 'YYYY-MM-DD'))) - last_day(add_months(trunc(to_date(current_day, 'YYYY-MM-DD')),   -1)) days
                        from dual)) t
   where t.week not in (0, 6);

  dbms_output.put_line('当月的工作日是:' || v_week);

end;


                                 
                                 
--打工存储过程的测试窗口的代码如下:                                 
begin
  -- Call the procedure
  --使用oracle的"测试"调试存储过程输入参数的时候,因为current_day 是varchar2类型了,
  --只能输入20160315,而不是带引号的'20160315'
  sql_week_in(current_day => :current_day);
end;
 HqkpTKTzAa
 
 
--参考网址: http://zhidao.baidu.com/link?url=ikzPdlScBvDDV2QRy1Bp-IoJ35XHgbJzgJlXgGXdbkiA8V8e722SBcss4UYl4RBXHqDDxxw4bCp-HqkpTKTzAa                         
                                 
                                 
命令窗口执行不了:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as scott@ORCL

SQL> exec sql_week_in '20160315'
begin sql_week_in '20160315'; end;
ORA-06550: 第 1 行, 第 19 列: 
PLS-00103: 出现符号 "20160315"在需要下列之一时：
 := . ( @ % ;
符号 ":=" 被替换为 "20160315" 后继续。

SQL> exec sql_week_in 20160315
begin sql_week_in 20160315; end;
ORA-06550: 第 1 行, 第 19 列: 
PLS-00103: 出现符号 "20160315"在需要下列之一时：
 := . ( @ % ;
符号 ":=" 被替换为 "20160315" 后继续。