`

PL/SQL基础

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;


          
 
分享到:
评论

相关推荐

    PL/SQL基础编程,实例自写

    ### PL/SQL基础编程知识点详解 #### 一、PL/SQL概述 PL/SQL,全称为Procedural Language for SQL,是Oracle数据库特有的高级程序设计语言。它结合了SQL的数据处理能力和传统编程语言的控制结构,使开发者能够在...

    pl/sql最新中文手册

    1. **PL/SQL基础**:手册可能会从基础开始,介绍PL/SQL的基本结构,包括声明变量、常量、游标、记录类型等。还会讲解如何编写存储过程、函数和触发器。 2. **控制流程语句**:这包括条件判断(IF-THEN-ELSIF-ELSE)...

    oracle 9i pl/sql程序设计笔记

    #### PL/SQL基础知识概览 **标题与描述**:本文档围绕“Oracle 9i PL/SQL程序设计笔记”这一核心主题,深入探讨了PL/SQL语言的基础知识及其在Oracle 9i数据库环境中的应用。 **关键词**:Oracle 9i、PL/SQL #### ...

    tornado-6.4.1-cp38-abi3-musllinux_1_2_i686.whl

    tornado-6.4.1-cp38-abi3-musllinux_1_2_i686.whl

    tornado-6.1-cp36-cp36m-manylinux2014_aarch64.whl

    tornado-6.1-cp36-cp36m-manylinux2014_aarch64.whl

    基于java的ssm停车位短租系统程序答辩PPT.pptx

    基于java的ssm停车位短租系统程序答辩PPT.pptx

    tornado-6.4b1-cp38-abi3-musllinux_1_1_x86_64.whl

    tornado-6.4b1-cp38-abi3-musllinux_1_1_x86_64.whl

    基于java的招生管理系统答辩PPT.pptx

    基于java的招生管理系统答辩PPT.pptx

    课设毕设基于SpringBoot+Vue的医学电子技术线上翻转课堂系统源码可运行.zip

    本压缩包资源说明,你现在往下拉可以看到压缩包内容目录 我是批量上传的基于SpringBoot+Vue的项目,所以描述都一样;有源码有数据库脚本,系统都是测试过可运行的,看文件名即可区分项目~ |Java|SpringBoot|Vue|前后端分离| 开发语言:Java 框架:SpringBoot,Vue JDK版本:JDK1.8 数据库:MySQL 5.7+(推荐5.7,8.0也可以) 数据库工具:Navicat 开发软件: idea/eclipse(推荐idea) Maven包:Maven3.3.9+ 系统环境:Windows/Mac

    基于java的农机电招平台答辩PPT.pptx

    基于java的农机电招平台答辩PPT.pptx

    jdk23 甲骨文官方安装包

    jdk23 甲骨文官方安装包

    基于java的机场网上订票系统答辩PPT.pptx

    基于java的机场网上订票系统答辩PPT.pptx

    【java毕业设计】小学家校互联平台源码(springboot+vue+mysql+说明文档).zip

    项目经过测试均可完美运行! 环境说明: 开发语言:java jdk:jdk1.8 数据库:mysql 5.7+ 数据库工具:Navicat11+ 管理工具:maven 开发工具:idea/eclipse

    基于java的网上书店销售管理系统答辩PPT.pptx

    基于java的网上书店销售管理系统答辩PPT.pptx

    tornado-6.3.3-cp38-abi3-win32.whl

    tornado-6.3.3-cp38-abi3-win32.whl

    基于 Jsp+Sqlserver 实现的超市信息管理系统

    【作品名称】:基于 Jsp+Sqlserver 实现的超市信息管理系统 【适用人群】:适用于希望学习不同技术领域的小白或进阶学习者。可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。 【项目介绍】: 系统功能: (1)系统分两种身份:管理员和员工,选择不同的身份进入不同的功能操作界面! (2)商品信息管理:管理员可以添加和维护商品信息,员工只能对商品信息进行查询 (3)员工信息管理:管理员登陆系统后可以可以添加和维护超市员工(收银员)的信息 (4)商品进货管理:管理员登陆系统后可以添加商品进货信息,可以对商品进货信息进行查询和统计,添加商品进进货退货信息,对商品进货退货信息进行查询和统计 (5)商品销售管理:员工(收银员)登陆系统后可以对商品进行销售,可以按时间查询自己的销售业绩;管理员登陆系统后可以按照时间等条件对销售信息进行查询,可以根据小票号登记顾客退货信息,查询顾客退货信息,可以查看员 【资源声明】:本资源作为“参考资料”而不是“定制需求”,代码只能作为参考,不能完全复制照搬。需要有一定的基础看懂代码,自行调试代码并解决报错,能自行添加功能修改代码。

    tornado-6.3.2-cp38-abi3-musllinux_1_1_i686.whl

    tornado-6.3.2-cp38-abi3-musllinux_1_1_i686.whl

    基于java的热带水果商城答辩PPT.pptx

    基于java的热带水果商城答辩PPT.pptx

    基于java Swing和面向对象思想实现中国象棋可联机版本源码分享

    java awt、Swing实现中国象棋可联机版本采用面向对象思想 采用面向对象的思路,实现中国象棋可联机版本,适合初学者,以及对面向对象有更深层次理解的开发者或者同学。 使用原生的java awt、Swing进行窗口式开发 将素材文件夹放在D:\Game路径下 两个工程直接导入Eclipse,即可运行, ps:一个工程运行两次也可以,需要注意端口号,代码默认如果连接的端口号是3003,则监听3004端口,相反同理。联机前需要确保两台计算机同时处于局域网或外网

Global site tag (gtag.js) - Google Analytics