PLSQL练习介绍.ppt

PL/SQL练习 赵明 中南大学软件学院，2010秋 练习一 编写一个触发器，当任何时候某个部门从DEPT中删除时，该触发器将从EMP表中删除该部门的所有雇员。 create or replace trigger tri_dept_emp after delete on dept for each row begin delete from emp where deptno=:old.deptno; end; / 练习二 某招生系统，要求计算考生的总分，并根据每门专业课的最低分数线和总分的最低分数线自动将考生归类为录取考生或落选考生。 SID 考生编号 SName 考生姓名 Status 标志位 TotalScore 总分 EnglishScore 英语成绩 PoliticsScore 政治成绩 Major1Score 专业课1成绩 Major2Score 专业课2成绩 Major3Score 专业课3成绩 create or replace procedure handle_score(es number, ps number, m1s number, m2s number, m3s number, ts number) is cursor c_score is select * from score for update nowait; begin for c in c_score loop update score set totalscore=c.englishscore+c.politicsscore+c.major1score+c.major2score+c.major3score where current of c_score; if c.totalscore= ts and c.englishescore=es and c.politicsscore=ps and c.major1score=m1s and c.major2score=m2s and c.major3score=m3s then update score set c.status = PASS where current of c_score; else update score set c.status = FAIL where current of c_score; end if; end loop; end; / 练习三 编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为： Designation Range Clerk 1500-2500 Salesman 2501-3500 Analyst 3501-4500 Others 4501 and above. 如果薪水在此范围内，则显示消息Salary is OK，否则更新薪水为该范围内的最小值。 create or replace procedure Proc_Chk_Emp_Sal(Emp_No IN emp.emp_id%TYPE) as Vc_Job emp.job%TYPE; N_Sal emp.emp_salary%TYPE; begin select job, emp_salary into Vc_Job, N_Sal from emp where emp_id=Emp_No; if Upper(Vc_Job) = CLERK then if N_Sal =2500 and N_Sal =1500 then DBMS_OUTPUT.PUT_LINE(Salary is OK!); else update emp set emp_salary=1500 where emp_id=Emp_No; end if; elsif Upper(Vc_Job) = SALESMAN then if N_Sal =3500 and N_Sal =2501 then DBMS_OUTPUT.PUT_LINE(Salary is OK!); else update emp set emp_salary=2501 where emp_id=Emp_No; end if; elsif Upper(Vc_Job) = ANALYST then if N_Sal =4500 and N_Sal =3501 then DBMS_OUTPUT.P