PL/SQL基础

 

 

 

/*-----------------------------------------------------------------------------------------------------------------------------------
  --------------------------------------------------------------------------
  \\ 过程名称 ：ERR_LOG
  \\ 功能描述 ：自治事务，独立于当前事务的处理逻辑，记录错误日志
  \\-------------------------------------------------------------------------*/

create or replace procedure err_log(prm_infono in varchar2,
                                    prm_info   varchar2) as
begin
  insert into log_err
    (infono, info, infodate, err)
  values
    (prm_infono, prm_info, sysdate, '');
  commit;
end;
/*-----------------------------------------------------------------------------------------------------------------------------------
  --------------------------------------------------------------------------
  \\ 函数名称 ：SELECTEMP
  \\ 功能描述 ：实现特定部门下员工信息（员工编号、员工姓名、部门名称，按入职日期升序排列，以游标变量形式返回查询结果）；
  \\-------------------------------------------------------------------------*/
create or replace procedure selectemp(prm_deptno in number,
                                      prm_infono out varchar2,
                                      prm_info   out varchar2) as
  v_empno emp.empno%type;
  v_ename emp.ename%type;
  v_dname dept.dname%type;

  cursor my_cursor is
    select a.empno, a.ename, b.dname
      from emp a, dept b
     where a.deptno = b.deptno
       and a.deptno = prm_deptno
     order by a.hiredate asc;
begin
  open my_cursor;
  loop
    fetch my_cursor
      into v_empno, v_ename, v_dname;
    exit when my_cursor%notfound;
    dbms_output.put_line(v_empno || ' ' || v_ename || ' ' || v_dname);
  end loop;
  close my_cursor;
exception
  when others then
    --error
    prm_infono := -1;
    prm_info   := '查询失败' || SQLERRM;
    dbms_output.put_line(prm_infono || prm_info);
    err_log(prm_infono, prm_info);
end;
/
  --test selectemp
declare
  prm_infono varchar2(100);
  prm_info   varchar2(100);
begin
  selectemp(12, prm_infono, prm_info);
end;

/*-----------------------------------------------------------------------------------------------------------------------------------
  --------------------------------------------------------------------------
  \\ 函数名称 ：INSERTEMP
  \\ 功能描述 ：实现员工信息新增功能，要求检验部门信息的合理性，如输入部门编号，判断部门是否存在；
  \\ 
  \\-------------------------------------------------------------------------*/

create or replace PROCEDURE INSERTEMP(PRM_EMPNO    IN EMP.EMPNO%TYPE,
                                      PRM_ENAME    IN EMP.ENAME%TYPE,
                                      PRM_JOB      IN EMP.JOB%TYPE,
                                      PRM_MGR      IN EMP.MGR%TYPE,
                                      PRM_HIREDATE IN EMP.HIREDATE%TYPE,
                                      PRM_SAL      IN EMP.SAL%TYPE,
                                      PRM_COMM     IN EMP.COMM%TYPE,
                                      PRM_DEPTNO   IN EMP.DEPTNO%TYPE,
                                      PRM_INFONO   OUT VARCHAR2,
                                      PRM_INFO     OUT VARCHAR2) aS
  ECOUNT     NUMBER; --行数
  V_EMPNO    EMP.EMPNO%TYPE := PRM_EMPNO; --员工编号
  V_ENAME    EMP.ENAME%TYPE := PRM_ENAME; --员工姓名
  V_JOB      EMP.JOB%TYPE := PRM_JOB; --员工岗位
  V_MGR      EMP.MGR%TYPE := PRM_MGR; --主管编号
  V_HIREDATE EMP.HIREDATE%TYPE := PRM_HIREDATE; --入职时间
  V_SAL      EMP.SAL%TYPE := PRM_SAL; --工资
  V_COMM     EMP.COMM%TYPE := PRM_COMM; --通讯费
  V_DEPTNO   EMP.DEPTNO%TYPE := PRM_DEPTNO; --部门编号

BEGIN
  --根据所填写的部门编号查找部门信
  SELECT COUNT(1) INTO ECOUNT FROM DEPT WHERE DEPT.DEPTNO = V_DEPTNO;
  IF ECOUNT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('部门编号不存在');
    PRM_INFONO := -1;
    PRM_INFO   := '部门不存在：' || SQLERRM;
    ERR_LOG(PRM_INFONO, PRM_INFO);
  END IF;
  IF ECOUNT = 1 THEN
    INSERT INTO EMP
      (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES
      (V_EMPNO, V_ENAME, V_JOB, V_MGR, V_HIREDATE, V_SAL, V_COMM, V_DEPTNO);
    IF SQL%ROWCOUNT = 1 THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('员工' || PRM_EMPNO || '的数据已经插入');
    ELSE
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('插入员工信息失败');
    END IF;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    PRM_INFONO := -102;
    PRM_INFO   := '新增员工信息失败：' || SQLERRM;
    ERR_LOG(PRM_INFONO, PRM_INFO);
END;

--test INSERTEMP
declare
  prm_infono varchar2(100);
  prm_info   varchar2(100);
begin
  INSERTEMP(110, 'wang', 'soft', 3, '', 322, 32, 10, prm_infono, prm_info);
end;

  /*-----------------------------------------------------------------------------------------------------------------------------------
  --------------------------------------------------------------------------
  \\ 函数名称 ：UPDATETEMP
  \\ 功能描述 ：实现依据员工编号，修改工资、通讯费及主管编号的修改，
  \\   A、依据岗位不同按如下比例调整员工工资：
  \\     PRESIDENT 增加10%
  \\     MANAGER 增加20%
  \\     其余      增加 15%
  \\  B、岗位为SALESMAN的员工，可调整通讯费；
  \\  C、岗位为PRESIDENT的员工，不允许调整主管编号。
  \\-------------------------------------------------------------------------*/

create or replace procedure updateemp(PRM_EMPNO  IN NUMBER,
                                      PRM_MGR    IN NUMBER,
                                      PRM_COMM   IN NUMBER,
                                      PRM_INFONO OUT VARCHAR2,
                                      PRM_INFO   OUT VARCHAR2) as
begin
  update emp
     set sal = case
                 when emp.job = 'president' then
                  sal * 1.1
                 when emp.job = 'manager' then
                  sal * 1.2
                 else
                  sal * 1.15
               end,
         comm = case
                  when emp.job = 'salesman' and PRM_COMM is not null then
                   PRM_COMM
                  else
                   comm
                end,
         mgr = case
                 when emp.job <> 'PRESIDENT' then
                  PRM_MGR
                 else
                  mgr
               end
   where emp.empno = PRM_EMPNO;

  if SQL%rowcount = 1 then
    commit;
    dbms_output.put_line('员工' || PRM_EMPNO || '的数据已更新');
  else
    rollback;
    dbms_output.put_line('更新的员工不存在');
    PRM_INFONO := -1;
    PRM_INFO   := '更新的员工不存在' || sqlerrm;
    err_log(PRM_INFONO, PRM_INFO);
  end if;

EXCEPTION
  WHEN OTHERS THEN
    PRM_INFONO := -103;
    PRM_INFO   := '更新员工信息失败：' || SQLERRM;
end;

--test updateemp
declare
  prm_infono varchar2(100);
  prm_info   varchar2(100);
begin
  updateemp(110, 88, 100, prm_infono, prm_info);
end;

/*-----------------------------------------------------------------------------------------------------------------------------------
  --------------------------------------------------------------------------
  \\ 函数名称 ：DELETETEMP
  \\ 功能描述 ：实现依据员工编号，删除员工信息的功能
  \\-------------------------------------------------------------------------*/

create or replace PROCEDURE DELETEEMP(PRM_EMPNO  IN NUMBER,
                                      PRM_INFONO OUT VARCHAR2,
                                      PRM_INFO   OUT VARCHAR2) as
begin
  delete from emp where emp.empno = PRM_EMPNO;
  if sql%rowcount = 1 then
    commit;
    DBMS_OUTPUT.PUT_LINE('员工' || PRM_EMPNO || '的数据已经删除');
  else
    rollback;
    DBMS_OUTPUT.PUT_LINE('删除的员工不存在');
    PRM_INFONO := -1;
    PRM_INFO   := '删除的员工不存在' || SQLERRM;
    ERR_LOG(PRM_INFONO, PRM_INFO);
  end if;
EXCEPTION
  WHEN OTHERS THEN
    PRM_INFONO := -104;
    PRM_INFO   := '删除员工信息失败：' || SQLERRM;
END;
  
  --test DELETEEMP
declare
  prm_infono varchar2(100);
  prm_info   varchar2(100);
begin
  DELETEEMP(110, prm_infono, prm_info);
end;
--匿名块实例
begin
  for i in 100 .. 1000 loop
    dbms_output.put_line(i);
  end loop;
end;
--给部门信息表添加主键
alter table dept add primary key(deptno);
--null和nvl区别
--NULL指的是空值，或者非法值。
--NVL (expr1, expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1
--查询每个部门收入前三的所有员工信息
select * from (select rank() over(partition by deptno order by sal+nvl(comm,0) desc) no,e.* from emp e)
where no<=3
--员工通讯费为空的员工信息 is = ''
select * from emp where comm is null;
--统计平均工资高于3000的部门 having
select deptno ,avg(sal) from emp group by deptno having avg(sal)>4000;
--统计1981年1月-9月入职多少员工
select count(1) from emp where hiredate between to_date('198101','yyyymm') and to_date('198109','yyyymm');
--统计姓名第二个字母为m的员工
select * from emp where ename like '_M%';
--统计员工人数高于各部门平均人数的部门
select deptno, count(ename)
  from emp
 group by deptno
having count(ename) > (select avg(empcount)
                         from (select deptno, count(ename) empcount
                                 from emp
                                group by deptno));

--统计员某员工所有上级的信息，类似机构树
SELECT * FROM EMP 
WHERE EMPNO <> '7369'
START WITH EMPNO = 7369
CONNECT BY PRIOR MGR = EMPNO;

--向下统计员工各下级员工
SELECT * FROM EMP
WHERE EMPNO <> 7566
  START WITH EMPNO = 7566
CONNECT BY MGR = prior EMPNO;