# PL/SQL经典练习

/*
=========================================================
PL/SQL编程
=========================================================
*/
--先把scott里面的表弄到test表空间里面来
CREATE TABLE EMP AS
SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPT AS
SELECT * FROM SCOTT.DEPT;
/*

*/
--（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;

/*

*/
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;
/*

||输入员工编号，工资，部门编号，
||如果部门代码是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;
/*

*/
--（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）创建输出参数为薪水集合的存储过程，调用并显示所有员工的薪水
CREATE OR REPLACE PROCEDURE get_sals(
cur_salary OUT SYS_REFCURSOR,
on_Flag OUT number,       --执行状态
os_Msg OUT VARCHAR2       --提示信息
)
AS
BEGIN
OPEN cur_salary FOR
SELECT empno,sal FROM employee;
on_Flag:=1;
os_Msg:='成功';
EXCEPTION
WHEN OTHERS THEN
on_Flag:=-1;
os_Msg:='其他错误，与管理员联系。';
END;
DECLARE
v_empno  employee.empno%type;
v_sal employee.sal%type;
emp_salary SYS_REFCURSOR;
on_Flag number(1);     --执行状态
os_Msg VARCHAR2(200);       --提示信息
BEGIN
get_sals(emp_salary,on_Flag,os_Msg);
IF on_flag=1 THEN
LOOP
FETCH emp_salary INTO v_empno, v_sal;
EXIT WHEN emp_salary%notfound;
DBMS_OUTPUT.PUT_LINE(v_empno||'的薪水是' ||v_sal);
END LOOP;
ELSE
dbms_output.put_line(os_Msg);
END IF;
IF emp_salary%ISOPEN THEN
CLOSE emp_salary;
END IF;
END;

|
11天前
|
SQL 存储 Oracle
Oracle的PL/SQL定义变量和常量：数据的稳定与灵动
【4月更文挑战第19天】在Oracle PL/SQL中，变量和常量扮演着数据存储的关键角色。变量是可变的“魔术盒”，用于存储程序运行时的动态数据，通过DECLARE定义，可在循环和条件判断中体现其灵活性。常量则是不可变的“固定牌”，一旦设定值便保持不变，用CONSTANT声明，提供程序稳定性和易维护性。通过 %TYPE、NOT NULL等特性，可以更高效地管理和控制变量与常量，提升代码质量。善用两者，能优化PL/SQL程序的结构和性能。
25 1
|
11天前
|
SQL Perl
PL/SQL编程基本概念
PL/SQL编程基本概念
18 0
|
11天前
|
SQL Perl
PL/SQL Developer 注册机+汉化包+用户指南
PL/SQL Developer 注册机+汉化包+用户指南
19 0
|
11天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
【4月更文挑战第19天】Oracle PL/SQL游标属性如同车辆的导航仪和仪表盘，提供丰富信息和控制。 %FOUND和%NOTFOUND指示数据读取状态，%ROWCOUNT记录处理行数，%ISOPEN显示游标状态。还有%BULK_ROWCOUNT和%BULK_EXCEPTIONS增强处理灵活性。通过实例展示了如何在数据处理中利用这些属性监控和控制流程，提高效率和准确性。掌握游标属性是提升数据处理能力的关键。
37 9
|
11天前
|
SQL Oracle 安全
Oracle的PL/SQL循环语句：数据的“旋转木马”与“无限之旅”
【4月更文挑战第19天】Oracle PL/SQL中的循环语句（LOOP、EXIT WHEN、FOR、WHILE）是处理数据的关键工具，用于批量操作、报表生成和复杂业务逻辑。LOOP提供无限循环，可通过EXIT WHEN设定退出条件；FOR循环适用于固定次数迭代，WHILE循环基于条件判断执行。有效使用循环能提高效率，但需注意避免无限循环和优化大数据处理性能。掌握循环语句，将使数据处理更加高效和便捷。
32 2
|
11天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL条件控制：数据的“红绿灯”与“分岔路”
【4月更文挑战第19天】在Oracle PL/SQL中，IF语句与CASE语句扮演着数据流程控制的关键角色。IF语句如红绿灯，依据条件决定程序执行路径；ELSE和ELSIF提供多分支逻辑。CASE语句则是分岔路，按表达式值选择执行路径。这些条件控制语句在数据验证、错误处理和业务逻辑中不可或缺，通过巧妙运用能实现高效程序逻辑，保障数据正确流转，支持企业业务发展。理解并熟练掌握这些语句的使用是成为合格数据管理员的重要一环。
23 0
|
11天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL表达式：数据的魔法公式
【4月更文挑战第19天】探索Oracle PL/SQL表达式，体验数据的魔法公式。表达式结合常量、变量、运算符和函数，用于数据运算与转换。算术运算符处理数值计算，比较运算符执行数据比较，内置函数如TO_CHAR、ROUND和SUBSTR提供多样化操作。条件表达式如CASE和NULLIF实现灵活逻辑判断。广泛应用于SQL查询和PL/SQL程序，助你驾驭数据，揭示其背后的规律与秘密，成为数据魔法师。
36 2
|
11天前
|
SQL Oracle 关系型数据库
Oracle系列十一：PL/SQL
Oracle系列十一：PL/SQL
10 0
|
11天前
|
SQL
SQL语句多表查询练习
SQL语句多表查询练习
13 0
|
11天前
|
SQL 缓存 数据库
PL/SQL的性能优化
PL/SQL的性能优化
44 0