--建立员工表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 --提示信息) ASBEGIN 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;?