oracle——SQL复习06

select * from emp ;


------------------------------------无参数显式游标-------------------------------
declare
  sname varchar2(20); --声明变量
  cursor student_cursor is
    select sname from t_student; --1.声明游标
begin
  open student_cursor; --2.打开游标
  fetch student_cursor
    into sname; --让游标指针往下移动
  while student_cursor%found --判断游标指针是否指向某行记录
   loop
    --遍历
    dbms_output.put_line('学生姓名: ' || sname);
    fetch student_cursor  --3.读取游标
      into sname;
  end loop;
     /*dbms_output.put_line('循环外面的学生姓名: ' || sname);*/
  close student_cursor;  --4.关闭游标
end;


--不借助第三方工具，怎么查看SQL的执行计划
   EXPLAIN   PLAN
      SET STATEMENT_ID='QUERY1'
      FOR
     select sname from t_student; 
   SELECT    operation, options, object_name, object_type, ID, parent_id
       FROM plan_table
      WHERE STATEMENT_ID = 'QUERY1'
   ORDER BY ID
   


--获取周几数
select to_char(sysdate ,'D') from dual; 
--level

select to_char(sysdate + level - 1 ,'D') from dual connect by level <= 30  ;
--获取1号
select last_day( add_months(sysdate,-1) ) + 1 from dual;  

--按30天计算
select to_char(last_day(add_months(sysdate, -1)) + 1 + level - 1, 'D')
  from dual
connect by level <= 30;


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


--按30天计算
select count(1)  cnt
  from (select to_char(last_day(add_months(sysdate, -1)) + 1 + level - 1, 'D') week
          from dual
        connect by level <= (select last_day(sysdate) -
                                    last_day(add_months(sysdate, -1))
                               from dual)) t
 where t.week not in (1, 7);