`

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 #### ...

    pl/sql基础教程

    而PL/SQL则是在SQL基础上添加了流程控制、异常处理、子程序等面向过程的编程特性,使其更适用于开发复杂的业务逻辑。 **§1.2.1 什么是PL/SQL?** PL/SQL是一种结构化编程语言,它允许开发者编写一系列的SQL语句,...

    pl/sql基础知识ppt

    pl/sql基础知识ppt

    PL/SQL编程基础知识

    ### PL/SQL编程基础知识 #### 一、PL/SQL简介 PL/SQL(Procedural Language for SQL)是一种过程化语言,它结合了SQL的数据操纵功能和过程化语言的控制结构,使用户能够灵活地控制数据操作流程。作为Oracle数据库...

    PL/SQL doc 文件

    本文将讲述 PL/SQL 基础语法、结构和组件、以及如何设计并执行一个 PL/SQL 程序。 PL/SQL 的优点包括: * 高性能的基于事务处理的语言,能运行在任何 ORACLE 环境中,支持所有数据处理命令。 * 支持所有 SQL 数据...

    Oracle Database 12c PL/SQL开发指南 实例源代码

    1. **PL/SQL基础**:PL/SQL的基础语法,如变量声明、数据类型、流程控制语句(如IF-THEN-ELSIF,FOR循环,WHILE循环)、异常处理(BEGIN-EXCEPTION-END结构)等。 2. **函数与过程**:如何定义和调用用户自定义的...

    Oracle PL/SQL专家指南-高级PL/SQL解决方案的设计与开发

    1. **PL/SQL基础**:涵盖PL/SQL的基本语法,包括变量声明、常量定义、条件语句(IF-THEN-ELSIF-ELSE)、循环结构(WHILE, FOR)、异常处理(BEGIN-EXCEPTION-END)以及子程序(PROCEDURE和FUNCTION)的创建与调用。...

    PL/SQL基础一之pl/sql块

    本篇文章主要探讨PL/SQL的基础知识,包括其结构、变量、条件判断、循环以及异常处理。 PL/SQL块是PL/SQL的基本组成单元,它由声明部分、执行部分和异常处理部分组成。声明部分用于定义变量和常量;执行部分包含SQL...

    pl/sql 学习资料

    1. **PL/SQL基础知识**: - PL/SQL结构:了解块的概念,包括声明部分、执行部分和异常处理部分。 - 数据类型:熟悉PL/SQL内置的数据类型,如NUMBER、VARCHAR2、DATE等。 - 变量和常量:声明和使用变量,以及定义...

    精通pl/sql

    1. **PL/SQL基础**:介绍PL/SQL的基本语法,包括变量声明、常量定义、条件判断语句(IF-THEN-ELSIF-ELSE)、循环结构(WHILE、FOR)、异常处理(BEGIN-EXCEPTION-END)等,以及如何编写存储过程、函数和触发器。...

    Pl/Sql程序设计

    1、PL/SQL简介 2、PL/SQL基础 3、记录和表 4、在PL/SQL中使用SQL 5、内置SQL函数 6、游标 7、过程和函数 ...

    oracle pl/sql从入门到精通 配套源代码

    首先,让我们探讨Oracle PL/SQL的基础知识。PL/SQL是Oracle数据库的内置编程语言,由块构成,包括声明部分、执行部分和异常处理部分。块可以是匿名块(即直接在SQL*Plus或其他工具中编写的一次性执行的代码),也...

    pl/sql精通

    1. PL/SQL基础:PL/SQL是Oracle特有的结构化查询语言,由三部分组成:声明部分(DECLARATION)、执行部分(EXECUTION)和异常处理部分(EXCEPTION)。声明部分用于定义变量、游标和过程;执行部分包含SQL语句和控制...

Global site tag (gtag.js) - Google Analytics