Less_14(2) PLSQL实例

1PL/SQL实例1./*用PL/SQL将EMP表中职员为'SMITH'的工资增加100元，并提交给数据库*/declarev_enamevarchar2(10):='SMITH';--char(10)isnotright.--v_enamechar(10):='SMITH';beginupdateempsetsal=sal+100whereename=v_ename;commit;end;/--selectsalfromempwhereename='SMITH';2./*若MARTIN的佣金不足1500元，则将其工资提升为1500元*/declarev_enamevarchar2(10):='MARTIN';v_commemp.comm%type;begincreatetablename1(enamevarchar2(10),mgrnamevarchar2(10));selectcommintov_commfromempwhereename=v_ename;ifv_comm1500thenupdateempsetsal=1500whereename=v_ename;endif;--;cannotbeignored!!!--commit;end;/3.查询职业为CLERK,且比他的上司受雇时间早的职工的名字及其上司的名字，并将结果存入新建的表name中，用两种方法来解答：(1)不用光标FOR循环；(2)用光标FOR循环createtablename(enamevarchar2(10),mgrnamevarchar2(10));1)/*declare2--Ibinary_integer:=0;v1_enameemp.ename%type;v2_enameemp.ename%type;beginforrecin(selecte1.enamea,e2.enamebfromempe1,empe2where(e1.job='CLERK')and(e1.mgr=e2.empno)and(e1.hiredatee2.hiredate))loop--I=I+1;insertintonamevalues(a,b);endloop;end;*//*declare--Ibinary_integer:=0;v_namename%rowtypev1_enameemp.ename%type;v2_enameemp.ename%type;beginforrecin(selecte1.ename,e2.enameintov_namefromempe1,empe2where(e1.job='CLERK')and(e1.mgr=e2.empno)and(e1.hiredatee2.hiredate))loop--I=I+1;insertintonamevalues(v_name);endloop;end;*/1)declarecursorc1isselectename,mgr,hiredatefromempwhereJOB='CLERK';edateDATE;mgrnameemp.ename%TYPE;emp_recC1%ROWTYPE;beginOPENc1;FETCHC1INTOemp_rec;WHILEC1%FOUNDLOOPSELECTENAME,HIREDATEINTOmgrname,edateFROMempWHEREempno=emp_rec.mgr;IFedateemp_rec.hiredatethenINSERTINTOnameVALUES(emp_rec.ename,mgrname);ENDIF;FETCHC1INTOemp_rec;ENDLOOP;3CLOSEc1;END;2)declarecursorc1isselectename,mgr,hiredatefromempwhereJOB='CLERK'orderbyhiredate;cursorc2isselectename,empno,hiredatefromemp;beginforr1inc1loopforr2inc2loopif(r1.mgr=r2.empno)and(r1.hiredater2.hiredate)theninsertintonamevalues(r1.ename,r2.ename);endif;endloop;endloop;end;/4./*上题中，设职为CLERK的职员不能是公司中最先受聘的员工，若发生这种情况，将该职员提升为经理(MANAGER)*/declarecursorc1isselectename,mgr,hiredatefromempwhereJOB='CLERK'orderbyhiredate;cursorc2isselectename,empno,hiredatefromemp;v_pridateemp.hiredate%type;beginselectmin(hiredate)intov_pridatefromemp;forr1inc1loopforr2inc2loopif(r1.mgr=r2.empno)and(r1.hiredater2.hiredate)and(r1.hiredate=v_pridate)thenupdateempsetjob='manager'where(r1.mgr=r2.empno)and(r1.hiredater2.hiredate)and(r1.hiredate=v_pridate);endif;endloop;endloop;end;/5/*在EMP表上编写一个触发器，审计任何对sal和comm列的修改（将每次修改此两列的数据库用户，修改时间、修改的用户和改前与该后此两列的值存入新建的一个审计表中），4然后测试其正常。createtableaudit2(timeDATE,usernamevarchar2(10),befor_salnumber(7,2),befor_commnumber(7,2),after_salnumber(7,2),after_commnumber(7,2));createorreplacetriggeremp_checkbeforeupdateofsal,commonempforeachrowbegininsertintoaudit2values(sysdate,user,:old.sal,:old.comm,:new.sal,:new.comm);end;--updateempsetsal=2000whereempno=7654;6./*编写一个存储过程，给职工加工资，过程有两个参数，参数1为要加薪的部门号，参数2为要加薪幅度，部门号为0表示要给所有部门的职工都加。*/createorreplaceprocedureraise_salary(deptno_idemp.deptno%type,increasereal)isbeginif(deptno_id=0)thenupdateempsetsal=sal+increase;elseupdateempsetsal=sal+increasewheredeptno=deptno_id;endif;end;////executeraise_salary(20,100)executeraise_salary(0,100)7./*将上题中存储过程放到一个包里去*/--***********packagespecification*********createorreplacepackageT_packageas5procedureraise_salary(deptno_idemp.deptno%type,increasereal);endT_package;--************packagebody********createorreplacepackagebodyT_packageasprocedureraise_salary(deptno_idemp.deptno%type,increasereal)isbeginif(deptno_id=0)thenupdateempsetsal=sal+increase;elseupdateempsetsal=sal+increasewheredeptno=deptno_id;endif;endraise_salary;endT_package;/test************executeT_package.raise_salary(0,2000);***********//test**********/declarebeginT_package.raise_salary(0,2000);end;