Oracle PL/SQL的练习题

7i80m1j30n

--建立员工表employee  p97 员工编号empno 员工名称ename 工资sal 工作job  部门编号deptno
--建立部门表dept   注解 deptno 部门名称 name
--练习一
DECLARE
v_tax_rate NUMBER(2);
v_sal_d NUMBER(5);
v_tax NUMBER(10,2);
v_sal NUMBER(10,2);
c_sal_start CONSTANT NUMBER(10):=3500;
BEGIN
SELECT sal
INTO v_sal
FROM employee
WHERE ename='KING';
v_tax:=0;
IF v_sal>=3500 THEN
v_sal:=v_sal-c_sal_start;
CASE
WHEN v_sal<=1500 THEN
v_tax_rate:=3;
v_sal_d:=0;
WHEN v_sal<=4500 THEN
v_tax_rate:=10;
v_sal_d:=105;   
WHEN v_sal<=9000 THEN
v_tax_rate:=20;
v_sal_d:=555;
ELSE
v_tax_rate:=25;
v_sal_d:=1005;
END CASE;
v_tax:=v_sal*v_tax_rate/100-v_sal_d;
END IF;
dbms_output.put_line(v_tax);
END;

DECLARE
v_bonus NUMBER(10,2);
v_hiredate employee.hiredate%TYPE;
BEGIN
SELECT hiredate
INTO v_hiredate
FROM employee
WHERE ename='SCOTT';
IF sysdate-v_hiredate>=365*6 THEN
v_bonus:=2000;
ELSE
v_bonus:=1500;
END IF;
UPDATE employee SET comm=v_bonus
WHERE ename='SCOTT';
END;

DECLARE
v_grade  NUMBER(2);
v_dname dept.dname%TYPE;
v_sal employee.sal%TYPE;
BEGIN
SELECT sal,dname
INTO v_sal,v_dname
FROM employee e INNER join dept d
ON  e.deptno=d.deptno
WHERE ename='SCOTT';
CASE
WHEN v_sal<=3200 AND v_sal>=700 THEN
v_grade:=1;
WHEN v_sal<=4400 THEN
v_grade:=2;
WHEN v_sal<=5000 THEN
v_grade:=3;
WHEN v_sal<=7000 THEN
v_grade:=4;
WHEN v_sal<=9999 THEN
v_grade:=5;
ELSE
v_grade:=0;
END CASE;
IF v_grade=0 THEN
dbms_output.put_line('SCOTT所在部门：'||v_dname||' 薪水：'||v_sal||' 工资无级别');
ELSE
dbms_output.put_line('SCOTT所在部门：'||v_dname||' 薪水：'||v_sal||' 工资级别:'||v_grade);      
END IF;
END;
--为员工SCOTT增加工资，每次增加100元，直到10000元停止。
DECLARE
v_hight employee.sal%TYPE:=10000;
v_sal employee.sal%TYPE;
BEGIN
SELECT sal
INTO v_sal
FROM employee
WHERE ename='SCOTT';
LOOP
EXIT WHEN v_sal+100>v_hight;   
UPDATE employee
SET sal=sal+100
WHERE ename='SCOTT' ;
v_sal:=v_sal+100;
END LOOP;
END;
SELECT * FROM employee;

--练习二
--自己实现

--练习三
DECLARE
v_sal employee.sal%TYPE;
v_deptno employee.deptno%TYPE;
e_comm_is_null EXCEPTION; --定义异常类型变量
BEGIN
SELECT sal,deptno INTO v_sal,v_deptno
FROM employee
WHERE empno=7788;
IF  v_deptno=20 THEN
IF v_sal<10000 THEN
UPDATE employee
SET sal=10000
WHERE empno=7788;
ELSE
RAISE e_comm_is_null;
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('雇员不存在！错误为:'||SQLCODE||SQLERRM);
WHEN e_comm_is_null THEN
dbms_output.put_line('工资不低于10000元');
WHEN others THEN
dbms_output.put_line('出现其他异常');
END;
--练习四
DECLARE
v_tax_rate NUMBER(2);
v_sal_d NUMBER(5);
v_tax NUMBER(10,2);
v_sal NUMBER(10,2);
v_sum NUMBER(10,2):=0;
c_sal_start CONSTANT NUMBER(10):=3500;
CURSOR emp_cursor IS
SELECT sal
FROM employee;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_sal;
EXIT WHEN emp_cursor%NOTFOUND;   
v_tax:=0;
IF v_sal>=3500 THEN
v_sal:=v_sal-c_sal_start;
CASE
WHEN v_sal<=1500 THEN
v_tax_rate:=3;
v_sal_d:=0;
WHEN v_sal<=4500 THEN
v_tax_rate:=10;
v_sal_d:=105;   
WHEN v_sal<=9000 THEN
v_tax_rate:=20;
v_sal_d:=555;
ELSE
v_tax_rate:=25;
v_sal_d:=1005;
END CASE;
v_tax:=v_sal*v_tax_rate/100-v_sal_d;
END IF;
v_sum:=v_sal+v_tax;
END LOOP;
CLOSE emp_cursor;
dbms_output.put_line(v_sum);
END;

DECLARE
v_bonus NUMBER(10,2);
CURSOR emp_cursor IS
SELECT hiredate
FROM employee
FOR UPDATE;
BEGIN
FOR cur1 IN emp_cursor LOOP
IF sysdate-cur1.hiredate>=365*6 THEN
v_bonus:=2000;
ELSE
v_bonus:=1500;
END IF;
UPDATE employee SET comm=v_bonus
WHERE CURRENT OF emp_cursor;
END LOOP;
END;

DECLARE
v_grade  NUMBER(2);
CURSOR emp_cursor IS
SELECT ename,sal,dname
FROM employee e INNER join dept d
ON  e.deptno=d.deptno
WHERE dname='SALES';
BEGIN
FOR cur1 IN emp_cursor LOOP   
CASE
WHEN cur1.sal<=3200 AND cur1.sal>=700 THEN
v_grade:=1;
WHEN cur1.sal<=4400 THEN
v_grade:=2;
WHEN cur1.sal<=5000 THEN
v_grade:=3;
WHEN cur1.sal<=7000 THEN
v_grade:=4;
WHEN cur1.sal<=9999 THEN
v_grade:=5;
ELSE
v_grade:=0;
END CASE;
IF v_grade=0 THEN
dbms_output.put_line(cur1.ename||'所在部门：'||cur1.dname||' 薪水：'||cur1.sal||' 工资无级别');
ELSE
dbms_output.put_line(cur1.ename||'所在部门：'||cur1.dname||' 薪水：'||cur1.sal||' 工资级别:'||v_grade);      
END IF;
END LOOP;
END;
--练习五
CREATE OR REPLACE PROCEDURE fire_employee(
eno employee.empno%type,                  --输入参数,雇员编号
on_Flag OUT number,       --执行状态
os_Msg OUT VARCHAR2       --提示信息
)
IS
e1 EXCEPTION; --定义异常类型变量
BEGIN
DELETE
FROM employee
WHERE empno=eno;
IF SQL%NOTFOUND THEN
RAISE e1;
ELSE
on_Flag:=1;
os_Msg:='成功';
END IF;
EXCEPTION
WHEN e1 THEN
on_Flag:=-1;
os_Msg:='该雇员不存在。';
WHEN OTHERS THEN
on_Flag:=SQLCODE;
os_Msg:=SQLERRM;
END;
DECLARE
v_no employee.empno%TYPE;
on_Flag number(1);     --执行状态
os_Msg VARCHAR2(200);       --提示信息  
BEGIN
v_no:=7788;
fire_employee(v_no,on_flag,os_Msg);
dbms_output.put_line(on_flag);
dbms_output.put_line(os_Msg);
END;

--调用get_sals存储过程,显示员工薪水

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;