/*
=========================================================
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所在的部门是:'
||http://www.||
',薪水是:'
||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(http://www.);
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)创建输出参数为薪水集合的存储过程,调用并显示所有员工的薪水