oracle-day03

 --1,列出所有“CLERK”（办事员）的姓名及其部门名称。
 select a.ename,a.job from emp a
 join dept b on a.deptno=b.deptno
 where a.job='CLERK'
 
 --2,列出薪金高于公司平均薪金的所有员工。
 select * from emp
 where sal>(select avg(sal) from emp)
 
 --3,截取字符串'afafaa@fffff@aaaaa'
 select substr(
    'afafaa@fffff@aaaaa',
    instr('afafaa@fffff@aaaaa','@',1,1)+1,
    instr('afafaa@fffff@aaaaa','@',1,2)-(instr('afafaa@fffff@aaaaa','@',1,1)+1)
 )from dual
 
 --4,列出员工表中每月入职的人数
   select to_char(hiredate,'yyyy-mm'),count(*) 
   from emp 
   group by to_char(hiredate,'yyyy-mm')
   order by to_char(hiredate,'yyyy-mm');
   
 -- 5查询每个部门中工资最高的人
 select * from emp where (deptno,sal) in 
 (select deptno,max(sal) from emp group by deptno)

----------------------------------------------------------------------

-- plsql 匿名块
-- 接收用户输入的员工编号，查询并输出该员工的姓名和雇佣日期，处理用户输入的员工编号不存在的异常
declare --声明部分
 -- %type取字段数据类型
 
 -- t_empno number(4); --声明变量
 t_empno emp.empno%type;
 -- t_ename varchar2(20);
 t_ename emp.ename%type;
 --t_hiredate date;
 t_hiredate emp.hiredate%type;
 
begin -- 执行部分
  t_empno := &empno; --:=变量赋值 ，&接受用户输入，empno输入框名称
  -- into 把查询结果赋值给两个变量
  select ename,hiredate into t_ename,t_hiredate from emp where empno=t_empno;
  dbms_output.put_line('姓名:'||t_ename||'雇佣日期:'||to_char(t_hiredate,'yyyy-mm-dd'));
  exception -- 异常处理
    when no_data_found then 
       dbms_output.put_line('你输入的工号'||t_empno||'不存在');
end;

-- 常量使用
declare
t_pi constant number(8,7):=3.1415926; 
t_r number(8,2);
t_area number(10,2);

begin
  t_r:=&r;
  t_area:=t_pi*t_r*t_r;
  dbms_output.put_line('半径为:'||t_r||'    面积为'||t_area);
end;

-- %rowtype 去一行数据的类型
declare
t_emprow emp%rowtype;
begin
  select * into t_emprow from emp where empno=7369;
  dbms_output.put_line('姓名'||t_emprow.ename||'   工资'||t_emprow.sal);
  end;
  
-- record 自定义一部分类型
declare
  type emp_record is record( --定义record类型
    t_empno emp.empno%type,
    t_emame emp.ename%type,
    t_sal emp.sal%type,
    t_comm emp.comm%type
  );
-- 声明变量属于定义好类型
  t_emp_record emp_record;
begin
    select empno,ename,sal,comm into t_emp_record from emp where empno=7369;
    dbms_output.put_line('姓名:'||t_emp_record.t_emame||'   工资:'||t_emp_record.t_sal
    ||'   奖金'||nvl(t_emp_record.t_comm,0));
  end;

--table 定义具有行和列的存放 数据的集合
declare

  type dept_table is table of dept%rowtype index by binary_integer;
  t_dept_table dept_table;
begin
    select * into t_dept_table(0) from dept where deptno=10;
    select * into t_dept_table(1) from dept where deptno=20;
    dbms_output.put_line('部门名称'||t_dept_table(0).dname||'  位置'||t_dept_table(0).loc);
    dbms_output.put_line('部门名称'||t_dept_table(1).dname||'  位置'||t_dept_table(1).loc);
  end;
  
-- 快速表备份
create table emp_bak1031 as select * from emp;
-- 备份空表
create table emp_bak1030 as select * from emp where 1=2;
-- 向表中批量插入数据
insert into emp_bak1030
select * from emp where sal<1500;

----------------------------------------------------

-- if elsif elsif ..end if
declare
t_empno emp_bak1031.empno%type;
t_comm emp_bak1031.comm%type;
begin
  t_empno:=&empno;
  select comm into t_comm from emp_bak1031 where empno=t_empno;
  dbms_output.put_line('原来奖金'||t_comm);
  
  if t_comm is null then 
    update emp_bak1031 set comm=sal*0.1 where empno=t_empno;
   elsif t_comm <1000 then 
     update emp_bak1031 set comm=1000 where empno =t_empno;
   else
      update emp_bak1031 set comm=1.1*comm where empno =t_empno;
   end if;
   commit;--提交事物
end;

-- case when then when then--end case
-- 第一个条件满足就退出
declare
t_sal emp.sal%type;
begin 
  select sal into t_sal from emp where empno=&empno;
  case when t_sal<1000 then
    dbms_output.put_line('C');
    when t_sal<2000 then 
       dbms_output.put_line('B');
    when t_sal>=2000 then 
       dbms_output.put_line('A');
  end case;
end;