mxx_1111
- 浏览: 59088 次
- 性别:
- 来自: 济南
-
最新评论
PL/SQL经典练习
/*
=========================================================
PL/SQL编程
=========================================================
*/
--先把scott里面的表弄到test表空间里面来
CREATE TABLE EMP AS
SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPT AS
SELECT * FROM SCOTT.DEPT;
/*
上机1
*/
--（1）计算King所交税金
DECLARE
V_SHUIJIN NUMBER; --应交税金
V_SAL SCOTT.EMP.SAL%TYPE; --工资
C_QIZHENDIAN CONSTANT NUMBER :=3500;
BEGIN
SELECT SAL INTO V_SAL FROM SCOTT.EMP WHERE ENAME='KING';
IF (V_SAL-C_QIZHENDIAN)<=1500 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.03-0;
ELSIF (V_SAL-C_QIZHENDIAN)>1500 AND (V_SAL-C_QIZHENDIAN)<=4500 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.1-105;
ELSIF (V_SAL-C_QIZHENDIAN)>4500 AND (V_SAL-C_QIZHENDIAN)<=9000 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.2-555;
ELSIF (V_SAL-C_QIZHENDIAN)>9000 AND (V_SAL-C_QIZHENDIAN)<=35000 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.25-1005;
ELSIF (V_SAL-C_QIZHENDIAN)>35000 AND (V_SAL-C_QIZHENDIAN)<=55000 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.3-2755;
ELSIF (V_SAL-C_QIZHENDIAN)>55000 AND (V_SAL-C_QIZHENDIAN)<=80000 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.35-5505;
ELSIF (V_SAL-C_QIZHENDIAN)>80000 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.45-13505;
END IF;
DBMS_OUTPUT.PUT_LINE('KING所交的税金是：'||V_SHUIJIN);
END;
--(2)根据员工scott入职的时间修改发放奖金列，大于等于6年的奖金为2000，小于6年的奖金是1500
DECLARE
V_SCOTT_HIREDATE EMP.HIREDATE%TYPE; --SCOTT的入职时间
V_COMM NUMBER; --奖金
BEGIN
SELECT HIREDATE INTO V_SCOTT_HIREDATE FROM EMP WHERE ENAME='SCOTT';
IF (SYSDATE-V_SCOTT_HIREDATE)>=365*6 THEN
V_COMM:=2000;
ELSE
V_COMM:=1500;
END IF;
--开始修改
UPDATE EMP SET COMM=V_COMM WHERE ENAME='SCOTT';
IF SQL%ROWCOUNT>0 THEN
DBMS_OUTPUT.PUT_LINE('修改成功！');
ELSE
DBMS_OUTPUT.PUT_LINE('修改失败！');
END IF;
END;
--(3)查询scott相应的工资级别并显示所在部门名称，薪水，和所在的级别
DECLARE
V_SCOTT_SAL EMP.SAL%TYPE; --scott的工资
V_JIBIE NUMBER; --级别
V_DEPTNAME DEPT.DNAME%TYPE; --部门名称
BEGIN
SELECT SAL,DNAME INTO V_SCOTT_SAL,V_DEPTNAME FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
WHERE ENAME='SCOTT';
IF V_SCOTT_SAL>700 AND V_SCOTT_SAL<=3200 THEN
V_JIBIE:=1; --第一级别
ELSIF V_SCOTT_SAL>3200 AND V_SCOTT_SAL<=4400 THEN
V_JIBIE:=2; --第二级别
ELSIF V_SCOTT_SAL>4400 AND V_SCOTT_SAL<=5000 THEN
V_JIBIE:=3; --第三级别
ELSIF V_SCOTT_SAL>5000 AND V_SCOTT_SAL<=7000 THEN
V_JIBIE:=4; --第四级别
ELSIF V_SCOTT_SAL>7000 AND V_SCOTT_SAL<=10000 THEN
V_JIBIE:=5; --第五级别
END IF;
DBMS_OUTPUT.PUT_LINE('SCOTT所在的部门是：'||V_DEPTNAME||',薪水是：'||V_SCOTT_SAL||',所在的级别是：第'||V_JIBIE||'级别');
END;
--(4)位员工scott增加工资，每次增加100，直到增加到10000为止
DECLARE
V_SCOTT_SAL EMP.SAL%TYPE; --SCOTT的工资
BEGIN
SELECT SAL INTO V_SCOTT_SAL FROM EMP WHERE ENAME='SCOTT';
LOOP
--增加工资
V_SCOTT_SAL:=V_SCOTT_SAL+100;
EXIT WHEN V_SCOTT_SAL>=10000;
END LOOP;
--修改scott的工资
UPDATE EMP SET SAL=V_SCOTT_SAL WHERE ENAME='SCOTT';
IF SQL%ROWCOUNT>0 THEN
DBMS_OUTPUT.PUT_LINE('增加成功！');
ELSE
DBMS_OUTPUT.PUT_LINE('增加失败！');
END IF;
END;
/*
上机2 预定义异常
公司通过emp表维护职员记录，用以接收职员编号并检索职员姓名，
*/
DECLARE
V_ENAME VARCHAR2(4);
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=&EMPNO;
DBMS_OUTPUT.PUT_LINE('已找到'||V_ENAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('对不起，没有该职员！');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('职员名称太长！');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出现其他的异常！');
END;
/*
上机3自定义异常、
||输入员工编号，工资，部门编号，
||如果部门代码是10，且工资低于10000，更新员工的工资是10000
||如果部门代码是10，工资高于10000，显示消息“工资不低于10000”
||如果部门代码不是10则不显示
*/
DECLARE
V_EMPNO EMP.EMPNO%TYPE; --员工编号
V_DEPTNO EMP.DEPTNO%TYPE; --部门编号
V_SAL EMP.SAL%TYPE; --工资
V_ENAME EMP.ENAME%TYPE; --姓名
V_V_EMPNO EMP.EMPNO%TYPE; --输入员工编号
V_V_DEPTNO EMP.DEPTNO%TYPE; --输入部门编号
V_V_SAL EMP.SAL%TYPE; --输入工资
E_ERROR_DEPTNO EXCEPTION; --自定义异常(部门编号不是10)
E_ERROR_EMPNO EXCEPTION; --自定义异常(找不到该员工)
V_COUNT NUMBER; --声明一个记录数
BEGIN
--输入员工编号
V_EMPNO:=&V_V_EMPNO;
--输入工资
V_SAL:=&V_VSAL;
--输入部门编号
V_DEPTNO:=&V_V_DEPTNO;
IF V_DEPTNO=10 THEN
--在进行二次判断（输入员工编号）
IF V_SAL<10000 THEN
--判断输入的员工编号是否存在，不存在的话报异常，存在的话继续
SELECT COUNT(*) INTO V_COUNT FROM EMP WHERE EMPNO=V_DEPTNO;
IF V_COUNT!=1 THEN
RAISE E_ERROR_EMPNO; --报异常
ELSE
--更新工资为1000
UPDATE EMP SET SAL=10000 WHERE EMPNO=V_EMPNO;
IF SQL%ROWCOUNT>0 THEN
DBMS_OUTPUT.PUT_LINE('更新成功!!!');
ELSE
DBMS_OUTPUT.PUT_LINE('更新失败!!!');
END IF;
END IF;
ELSIF V_SAL>10000 THEN
DBMS_OUTPUT.PUT_LINE('工资不低于10000!!');
END IF;
ELSE
RAISE E_ERROR_DEPTNO;
END IF;
EXCEPTION
WHEN E_ERROR_DEPTNO THEN
DBMS_OUTPUT.PUT_LINE('部门代码不是10！！');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出现其他异常，请自行解决！！！');
END;
------------------------------------------------------
SELECT * FROM EMP;
DECLARE
V_NAME EMP.ENAME%TYPE;
E_ERROR EXCEPTION;
V_COUNT NUMBER; --记录数
BEGIN
SELECT COUNT(*) INTO V_COUNT FROM EMP WHERE EMPNO=7901;
IF (V_COUNT=1) THEN
DBMS_OUTPUT.PUT_LINE(V_NAME);
ELSE
RAISE E_ERROR;
END IF;
EXCEPTION
WHEN E_ERROR THEN
DBMS_OUTPUT.PUT_LINE('没有记录！');
/*when no_data_found then
DBMS_OUTPUT.PUT_LINE('找不到！');*/
END;
-------------------------------------------------------------
DECLARE
V_NAME VARCHAR2(10);
E_ERROR EXCEPTION;
BEGIN
IF V_NAME IS NULL THEN
RAISE E_ERROR;
ELSE
DBMS_OUTPUT.PUT_LINE(V_NAME);
END IF;
EXCEPTION
WHEN E_ERROR THEN
DBMS_OUTPUT.PUT_LINE('没有记录！');
END;
/*
//上机4使用游标
*/
--（1）计算公司应交税金的总额
DECLARE
V_SHUIJIN NUMBER; --应交税金
V_SAL SCOTT.EMP.SAL%TYPE; --工资
V_SUM NUMBER(10):=0; --总税金
C_QIZHENDIAN CONSTANT NUMBER :=3500;
CURSOR CURSOR_SAL IS
SELECT SAL FROM EMP; --所有的员工的工资
BEGIN
OPEN CURSOR_SAL;
LOOP
FETCH CURSOR_SAL INTO V_SAL;--把所有的工资放在V_SAL里面
EXIT WHEN CURSOR_SAL%NOTFOUND;
IF (V_SAL-C_QIZHENDIAN)<=1500 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.03-0;
ELSIF (V_SAL-C_QIZHENDIAN)>1500 AND (V_SAL-C_QIZHENDIAN)<=4500 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.1-105;
ELSIF (V_SAL-C_QIZHENDIAN)>4500 AND (V_SAL-C_QIZHENDIAN)<=9000 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.2-555;
ELSIF (V_SAL-C_QIZHENDIAN)>9000 AND (V_SAL-C_QIZHENDIAN)<=35000 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.25-1005;
ELSIF (V_SAL-C_QIZHENDIAN)>35000 AND (V_SAL-C_QIZHENDIAN)<=55000 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.3-2755;
ELSIF (V_SAL-C_QIZHENDIAN)>55000 AND (V_SAL-C_QIZHENDIAN)<=80000 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.35-5505;
ELSIF (V_SAL-C_QIZHENDIAN)>80000 THEN
V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.45-13505;
END IF;
V_SUM:=V_SUM+V_SHUIJIN;
END LOOP;
CLOSE CURSOR_SAL; --关闭游标
DBMS_OUTPUT.PUT_LINE(V_SUM);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出现异常！');
END;
--（2）根据员工入职时间修改所有员工发放奖金，大于6年的+2000，小于的1500+
DECLARE
V_COMM EMP.COMM%TYPE; --奖金
CURSOR CURSOR_EMP_COMM IS
SELECT HIREDATE FROM EMP FOR UPDATE;
BEGIN
FOR CUR1 IN CURSOR_EMP_COMM LOOP
IF (SYSDATE-CUR1.HIREDATE)>=365*6 THEN
V_COMM:=2000;
ELSE
V_COMM:=1500;
END IF;
--开始修改
UPDATE EMP SET COMM=V_COMM WHERE CURRENT OF CURSOR_EMP_COMM;
IF SQL%ROWCOUNT>0 THEN
DBMS_OUTPUT.PUT_LINE('更新成功!!!');
ELSE
DBMS_OUTPUT.PUT_LINE('更新失败!!!');
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出现异常！');
END;
--（3）显示员工姓名，所在部门名称，薪水，所在级别
DECLARE
C_DNAME CONSTANT VARCHAR2(20):='SALES'; --销售部门
V_JIBIE NUMBER; --级别
CURSOR CURSOR_EMP IS
SELECT ENAME,DNAME,SAL FROM EMP E
JOIN DEPT D ON E.DEPTNO=D.DEPTNO
WHERE DNAME=C_DNAME;
BEGIN
FOR C1 IN CURSOR_EMP LOOP
IF C1.SAL>700 AND C1.SAL<=3200 THEN
V_JIBIE:=1; --第一级别
ELSIF C1.SAL>3200 AND C1.SAL<=4400 THEN
V_JIBIE:=2; --第二级别
ELSIF C1.SAL>4400 AND C1.SAL<=5000 THEN
V_JIBIE:=3; --第三级别
ELSIF C1.SAL>5000 AND C1.SAL<=7000 THEN
V_JIBIE:=4; --第四级别
ELSIF C1.SAL>7000 AND C1.SAL<=10000 THEN
V_JIBIE:=5; --第五级别
ELSE
V_JIBIE:=0; --没有级别
END IF;
DBMS_OUTPUT.put_line(C1.ENAME||'在'||C1.DNAME||'部门，'||'薪水是'||C1.SAL||'在第'||V_JIBIE||'级别');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出现异常！');
END;
/*
上机5 存储过程
*/
--（1）根据输入的员工编号，删除相应的员工
CREATE OR REPLACE PROCEDURE DEL_EMPNAME
(
ENO EMP.EMPNO%TYPE, --输入员工的编号
ON_FLAG OUT NUMBER, --执行状态，-1失败，1成功 0异常
ON_MSG OUT VARCHAR --提示信息
)
IS
E_ERROR EXCEPTION; --异常信息
BEGIN
DELETE FROM EMP WHERE EMPNO=ENO;
IF SQL%NOTFOUND THEN
RAISE E_ERROR;
ELSE
ON_FLAG:=1; --执行成功
ON_MSG:='删除成功！';
END IF;
EXCEPTION
WHEN E_ERROR THEN
ON_FLAG:=0; --执行成功
ON_MSG:='删除失败！';
WHEN OTHERS THEN
ON_FLAG:=0;
ON_MSG:='出现异常！';
END;
DROP PROCEDURE DEL_EMPNAME;
--调用存储过程
DECLARE
V_EMPNO NUMBER;
ENO NUMBER(5);
ON_FLAG NUMBER(1);
ON_MSG VARCHAR(20);
BEGIN
ENO:=&EMPNO; --输入编号
DEL_EMPNAME(ENO,ON_FLAG,ON_MSG);
DBMS_OUTPUT.PUT_LINE(ON_FLAG);
DBMS_OUTPUT.PUT_LINE(ON_MSG);
END;
--（2）创建输出参数为薪水集合的存储过程，调用并显示所有员工的薪水
分享到：
发表评论
相关推荐
从给定的文件信息中，我们可以提炼出一系列与Oracle PL/SQL相关的知识点，涉及变量声明、数据查询、条件语句、异常处理以及数据库更新等核心概念。以下是对这些知识点的详细解析： ### 1. 变量声明与赋值 在PL/SQL...
从给定文件的标题、描述、标签以及部分内容中，可以...实际文档可能包含更深入的内容、更多示例和练习，以及更加详尽的解释和说明，用以帮助开发者理解PL/SQL的各个方面的知识，并指导他们在Oracle环境中进行有效开发。
PL/SQL是Oracle数据库系统中的过程式语言，它结合了SQL的数据操作能力与结构化编程语句，使得开发者能够创建复杂的数据处理逻辑和业务规则。本资料“PL/SQL入门到精通书的源代码”是一份针对初学者到高级用户的实践...
本书是一本逐步分解的，详尽的pl／sql编程教程，使用真实场景的试验、范例和练习来介绍读者所需的pl／sql编程技能，涵盖oracle 11g的最新特性。作者的写作手法源自于在哥伦比亚大学教授pl／sql编程技术的经验，深度...
在这个"pl/sql小练习"项目中，你将有机会深入理解并实践PL/SQL的用法。 1. PL/SQL基础： PL/SQL是由PL（过程语言）和SQL组成，它允许你在单一的环境中编写存储过程、函数、触发器等。PL/SQL的结构通常包括声明部分...
《精通PL/SQL》这本书是针对Oracle数据库系统中PL/SQL编程语言的深度解析与实践指南，主要面向希望提升在Oracle环境下使用PL/SQL技能的专业人士。PL/SQL，全称为Procedural Language/Structured Query Language，是...
PL/SQL是Oracle数据库中的一个核心组件，全称为Procedural Language/Structured Query Language，即过程化结构化查询语言。它是SQL的扩展，允许开发者在SQL的基础上添加过程化编程元素，如循环、条件判断和子程序等...
《PL/SQL精通》这本书是针对Oracle数据库管理系统中PL/SQL编程语言的深入学习指南。...书中的实例和练习将帮助读者深入理解并熟练运用PL/SQL，从而在实际工作中更有效地处理数据和构建高效的应用。
通过阅读《Oracle PL/SQL》这本书，法语读者可以系统地学习到上述内容，并通过实例和练习提升自己的编程技能。这本书将帮助读者深入理解Oracle数据库的编程精髓，提升数据库管理与应用开发的能力。
Oracle PL/SQL是一种强大的编程语言，它结合了SQL的数据库操作能力和Procedural Language的编程结构，用于在Oracle数据库环境中创建复杂的应用...通过不断练习和深入理解，你将成为一名精通Oracle PL/SQL的专业开发者。
在学习PL/SQL的过程中，建议先从基础语法入手，理解如何声明和使用变量，逐步掌握流程控制和异常处理，然后深入学习存储过程、函数和触发器的编写，最后结合实际数据库操作，练习编写复杂的PL/SQL程序。"proc"可能是...
PL/SQL 的函数与存储过程练习 PL/SQL 的函数与存储过程练习
这篇博客“Oracle PL/SQL的练习题”可能包含一系列针对初学者和进阶者的编程挑战，旨在提升对PL/SQL语言的理解和应用能力。虽然没有具体的描述，但我们可以推测这些练习可能涵盖以下几个方面： 1. **变量声明与赋值...
通过本书，读者不仅能够获得丰富的理论知识，还能通过大量的实例和练习来加深对PL/SQL的理解。无论你是初学者还是有经验的开发者，都能从中受益，提升在Oracle环境下的编程技能。 在阅读《Oracle PL/SQL by Example...
### Oracle PL/SQL测试题目与知识点解析 #### 一、选择题知识点解析 **1. Oracle数据库中为新创建的表分配的初始空间通常为多大？** - **知识点解析:** Oracle数据库为新创建的表分配的空间单位是“区”（Extent...
Oracle数据库是全球广泛使用的大型关系型数据库管理系统之一，SQL（结构化查询语言）是与所有关系数据库进行交互的标准语言，而PL/SQL是Oracle数据库特有的编程语言，它扩展了SQL的功能，使得数据库管理、开发和维护...
这本书的源代码提供了丰富的示例和练习，帮助读者理解和掌握PL/SQL编程技巧。 PL/SQL是Oracle数据库的内置编程语言，它是SQL的扩展，允许开发者编写复杂的事务处理逻辑、创建自定义函数和存储过程，以及实现数据库...
Oracle数据库是世界上最广泛使用的数据库管理系统之一，SQL（结构化查询语言）是用于管理和操作数据库的标准语言，而PL/SQL则是Oracle公司为Oracle数据库设计的一种过程式编程语言，它结合了SQL的功能并增加了流程...