PLSQL Day3

--7.键盘输入1-10之间的任意一个数字，输出这个数字的阶乘： [3!=1*2*3] [5!=1*2*3*4*5]
declare
  n number := &输入一个数字;
  s number := 1;
begin
  if n between 1 and 10
  then for i in 1..n loop 
          s := i*s;
       end loop;
  dbms_output.put_line(s);
  else
    dbms_output.put_line('请输入1-10之间的数');
  end if;
end;

--8.键盘输入一个deptno，将全部门的人的信息打印出来：
declare 
  no number := &部门号;
begin
  for e in (select * from scott.emp where deptno = no) loop
    dbms_output.put_line(e.empno||' '||e.ename||' '||e.job
    ||' '||e.mgr||' '||to_char(e.hiredate,'yyyy-MM-dd')||
    ' '||e.sal||' '||e.comm||' '||e.deptno);
    end loop;
end;

--9.键盘输入一个empno，将同部门的人的信息全部打印：

declare 
  no number := &员工号;
begin
  for e in (select * from scott.emp where deptno = (select deptno from emp where empno = no)) loop
    dbms_output.put_line(e.empno||' '||e.ename||' '||e.job
    ||' '||e.mgr||' '||to_char(e.hiredate,'yyyy-MM-dd')||
    ' '||e.sal||' '||e.comm||' '||e.deptno);
    end loop;
end;

--10.输出九九乘法表：
begin
  for m in 1..9 loop
    for n in 1..m loop
      dbms_output.put(n||'*'||m||'='||m*n||' ');
    end loop;   
    dbms_output.put_line(' ');
  end loop;
end;

--11.键盘输入用户模糊名字，查询符合条件的用户：

declare
  n varchar2(10) := '&模糊名字';
  a varchar2(50);
  b varchar2(50);
begin
  for i in 1..length(n) loop    
    a := '%'||substr(n,i,1); 
    b := b||a; 
  end loop;
  b := b||'%';
  for e in (select * from scott.emp where ename like b) loop
      dbms_output.put_line(e.empno||' '||e.ename||' '||e.job
    ||' '||e.mgr||' '||to_char(e.hiredate,'yyyy-MM-dd')||
    ' '||e.sal||' '||e.comm||' '||e.deptno);
    end loop;
end;

--12.三种循环打印emp表中的员工和入职日期：
-- for
begin
    for e in (select * from emp) loop
      dbms_output.put_line(e.ename||' '|| to_char(e.hiredate,'yyyy-MM-dd'));
    end loop;
end;
-- while
declare
  n number := 1;
  e_name varchar2(50);
  e_date varchar2(50);
  r number;
begin
  select count(*) into r from emp;
  while n <= r loop
      select t.ename,to_char(t.hiredate,'yyyy-MM-dd') 
      into e_name,e_date 
      from (select e.*,rownum r from emp e) t
      where t.r = n;
      dbms_output.put_line(e_name||' '|| e_date);
      n := n + 1;
  end loop;
end;
-- loop
declare
  n number := 1;
  e_name varchar2(50);
  e_date varchar2(50);
  r number;
begin
  select count(*) into r from emp;
  loop
      select t.ename,to_char(t.hiredate,'yyyy-MM-dd') 
      into e_name,e_date 
      from (select e.*,rownum r from emp e) t
      where t.r = n;
      dbms_output.put_line(e_name||' '|| e_date);
      n := n + 1;
      exit when n > r;
  end loop;
end;

-- 13.编写一个PL/SQL程序，用于接受用户输入的数字，
-- 将该数左右反转，然后显示反转后的数：（不使用反转函数）
declare
   n number := &输入一个数字;
   s varchar2(50);
begin
  for i in 1..length(n) loop
    s := s||substr(n,-i,1); -- substr 也可以直接切字符串
  end loop;
  dbms_output.put_line(s);
end;

-- 14.编写一个PL/SQL程序，用于接受用户输入的字符串，
-- 将该字符串左右反转，然后显示反转后的字符串：（不使用反转函数）
declare
   n varchar2(50) := '&输入一个字符串';
   s varchar2(50);
begin
  for i in 1..length(n) loop
    s := s||substr(n,-i,1);
  end loop;
  dbms_output.put_line(s);
end;

/*
create table nba(
  team varchar2(20),
  year number(4)
);
insert into nba values('活塞',1990);
insert into nba values('公牛',1991);
insert into nba values('公牛',1992);
insert into nba values('公牛',1993);
insert into nba values('火箭',1994);
insert into nba values('火箭',1995);
insert into nba values('公牛',1996);
insert into nba values('公牛',1997);
insert into nba values('公牛',1998);
insert into nba values('马刺',1999);
insert into nba values('湖人',2000);
insert into nba values('湖人',2001);
insert into nba values('湖人',2002); 
insert into nba values('马刺',2003);
insert into nba values('活塞',2004);
insert into nba values('马刺',2005);
insert into nba values('热火',2006);
insert into nba values('马刺',2007);
insert into nba values('凯尔特人',2008);
insert into nba values('湖人',2009);
insert into nba values('湖人',2010);  */

/*
请写出一条 SQL 语句，查询出在此期间连续获得冠军的有哪些，其连续的年份的起止时间是多少，结果如下：

TEAM BEGIN END

公牛   1991   1993

火箭   1994   1995

公牛   1996   1998

湖人   2000   2002

湖人   2009   2010

*/

select * from nba;

-- 最优解
select team,min(t1y) begin ,max(t2y) end  from
(select t1.team,t1.year t1y,t2.year t2y from nba t1 , nba t2 
where t1.team = t2.team and t1.year+1 = t2.year)
group by team,(t1y-rownum)

--
with nb as(
select nba.team, nba.year,row_number()over( partition by team order by year) r,
year-row_number()over( partition by team order by year) q
 from nba)
select team,min(year) begin,max(year) end from nb
  group by team ,q having min(year)!=max(year) order by min(year);

select team,min(year) yearv,max(year)+1 yeari from(
select e.*,e.year-rownum aa from (select nba.team,nba.year,lead(nba.team) 
over(order by year)cc from nba) e
where e.team =e.cc) e1
group by team,aa 

-- 1.定义游标：列出每个员工的姓名、
-- 部门名称并编程显示工资正序第10个到第20个记录：

declare
  cursor e_cursor is
  select e.ename,d.dname from  emp e
  join dept d on d.deptno = e.deptno
   order by e.sal
  ;
  
  e e_cursor%rowtype;
begin
  -- loop
  open e_cursor;
    loop
      fetch e_cursor into e;
      exit when e_cursor%notfound;
      if e_cursor%rowcount between 10 and 20 then
         dbms_output.put_line(e.ename||' '||e.dname);
      end if;
    end loop;
  close e_cursor;
end;

declare
  cursor e_cursor is
  select e.ename,d.dname 
  from emp e join dept d
  on d.deptno = e.deptno
  order by sal;
  e e_cursor%rowtype;
begin
  open e_cursor;
  -- while
  --在循环体外进行一次fetch操作，作为第一次循环的条件
  fetch e_cursor into e;
  while e_cursor%found loop
    if e_cursor%rowcount between 10 and 20 
       then dbms_output.put_line(e.ename||' '||e.dname);
    end if;
    fetch e_cursor into e;
  end loop;   
  close e_cursor;
end;

declare
  cursor e_cursor is
  select e.ename,d.dname
  from emp e
  join dept d
  on e.deptno = d.deptno
  order by sal;
  
  e e_cursor%rowtype;
begin
  -- for
  for e in e_cursor loop
    if e_cursor%rowcount between 10 and 20 
       then dbms_output.put_line(e.ename||' '||e.dname);
    end if;
  end loop;
end;

-- 2.定义游标：从员工表中显示工资大于3000的记录，
-- 只要姓名、工资和部门编号。编程显示其中的奇数记录：
declare
  cursor e_cursor is 
  (select * from emp where sal >= 3000);
begin
  for x in e_cursor loop
    if mod(e_cursor%rowcount,2)=1
    then dbms_output.put_line(x.ename||' '||x.sal||' '||x.deptno);
    end if;
  end loop;
end;