Oracle经典练习题(很全面)

Oracle经典练习题一.创建一个简单的PL/SQL程序块.编写一个程序块,从emp表中显示名为“SMITH”的雇员的薪水和职位。declarev_empemp%rowtype;beginselect*intov_empfromempwhereename='SMITH';）（'员工的工作是：'∣∣∣∣'；他的薪水是：'||;end;.编写一个程序块，接受用户输入一个部门号，从dept表中显示该部门的名称与所在位置。方法一：（传统方法）declarepname%type;ploc%type;pdeptno%type;|beginPdePtno:=&请输入部门编号;selectdname,locintopname,plocfromdeptwheredeptno=pdeptno;（'部门名称：'∣∣pname0所在位置：'∣∣ploc）;exception-异常处理whenno_data_foundthen（'你输入的部门编号有误！！'）；whenothers/then（'其他异常'）;end;方法二：（使用％rowtype）declareerowdept%rowtype;beginselect*intoerowfromdeptwheredeptno=&请输入部门编号；ll'--'ll;@exceptionwhenno_data_foundthen（'你输入的部门号有误'）;whenothersthen（'其他异常'）;end;.编写一个程序块，利用％type属性，接受一个雇员号，从emp表中显示该雇员的整体薪水（即，薪水加佣金）。declarePempno%type;totalSal%type;beginpempno:=&请输入员工编号；selectsal+nvl（comm,0）intototalSalfromempwhereempno=pempno;（'该员工总共薪水'UtotalSal）;exceptionwhenno_data_found,then（'你输入的员工编号有误！！'）；whenothersthen（'其他异常'）;end;.编写一个程序块，利用％rowtype属性，接受一个雇员号，从emp表中显示该雇员的整体薪水（即，薪水加佣金）。declareerowemp%rowtype;begin；select*intoerowfromempwhereempno=&请输入员工编号；+nvl,0））;exceptionwhenno_data_foundthen（'你输入的员工编号有误！！'）；whenothersthen（'其他异常'）;end;.某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理：DesignationRaiseClerk 500Salesman 1000Analyst 1500Otherwise 2000[编写一个程序块,接受一个雇员名，从emp表中实现上述加薪处理。declareerowemp%rowtype;beginselect*intoerowfromempwhereename='&name'；if='Clerk'thenupdateempsetSal=Sal+500whereempno=;elsif='Salesman'then（updateempsetSal=Sal+1000whereempno=;elsif='Analyst'thenupdateempsetSal=SalT500whereempno=;elseupdateempsetsal=sal+2000whereempno=;endif;commit;exception%whenno_data_foundthen（'你输入的员工编号有误！！'）；whenothersthen（'其他异常'）;end;.编写一个程序块，将emp表中雇员名全部显示出来。declare>cursorcsisselectenamefromemp;beginforerowincsloop;endloop;end;.编写一个程序块，将emp表中前5人的名字显示出来。方式一:/declarecursorcsisselectt.*from（select,rownumrmfromempe）twherebetween1and6;beginforerowincsloop;endloop;end;方式二：--方式二declarecursorCSisselectenamefromemp;inumber:=1;beginforerowincsloop;@i:=i+1; --迭代exitwheni>5;--退出条件endloop;end;.编写一个程序块，接受一个雇员名，从emp表中显示该雇员的工作岗位与薪水，若输入的雇员名不存在，显示“该雇员不存在”信息。declarepjob%type;@totalsal%type;beginselectjob,salintopjob,totalsalfromempwhereename='&请输入员工姓名‘；(pjob||' '||totalsal);exceptionwhenno_data_foundthen('你输入的员工姓名有误！！')；end;.接受两个数相除并且显示结果，如果第二个数为0，则显示消息“除数不能为0”。declarenum1float;num2float;resfloat;my_exCePtiOnException;|beginnum1:二&被除数；num2:=&除数；res:=num1/num2;raisemy_exception;exceptionwhenmy_exceptionthen（res）;whenothersthen（'除数不能为0'）;end；二.声明和使用游标 游标:（集合），处理返回多行记录的问题--声明游标--语法：cursor游标名isDQL;--遍历游标/*.打开游标,open游标名;.从游标中提取一行的记录：fetch游标名into变量名，...；.使用循环，exitwhen游标名％notfound;.关闭游标，close游标名;.通过使用游标来显示dept表中的部门名称。…declarecursorcoisselectdnamefromdept;beginforVnameincoloop;endloop;end;-.使用For循环，接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和薪水。declarecursorc_empisselect*fromempwheredeptno=&请输入部门号；beginforerowinc_emploop||' ,||||' '||;endloop;@exceptionwhenno_data_foundthen（'输入的部门编号有误'）；end;.使用带参数的游标，实现第2题。declarecursorc_cs(c_deptnonumber)isselect*fromempwheredeptno=c_deptno；v_deptnonumber；(beginv_deptno:=&请输入部门编号；forerowinc_cs(v_deptno)loop||' '||||' '||;endloop;exceptionwhenno_data_foundthen('输入的部门编号有误')；<end;4.编写一个PL/SQL程序块，从emp表中对名字以“A”或"S”开始的所有雇员按他们基本薪水的10%给他们加薪。declarecursorc_empisselectenamefromemp;beginforerowinc_emploopiflike'A%'thenupdateempsetSaI=Sal+sal*whereename=;elsiflike'S%'thenupdateempsetSaI=Sal+sal*whereename=;endif;commit;endloop;end;5.emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000卢布，则取消加薪。declare"cursorc_empisselect*fromemp;beginforerowinc_emploopif*<5000thenupdateempsetsal=sal+sal*whereename=;endif;commit;endloop;<end;三存储过程——存储过程（dba声明，得授予dba权限）：封装了一组Sql语句，提前编译好，效率较高,存储在服务端--场景：网购：数据库发生什么改变一库存量T（Update）,订单增加（insert），钱（Update）,物流（insert）,日志（insert）---语法/*,〜create[orreplace]procedUre存储过程名称（参数名in|oUt类型, ）as|is--声明变量begin--过程化语言end;*/】1.创建一个过程，能向dept表中添加一个新记录.（in参数）createorreplaceprocedureinsert_dept（p_deptnoinnumber,p_dnameinVarChar2,p_locinVarChar2）isbegininsertintodeptValUeS（p_deptno,p_dname,p_loc）;end;--调用该存储过程declarebegininsert_dept（50,'DEVELOP','SHENGZ'）;end;.创建一个过程，从emp表中输入雇员的姓名，返回该雇员的薪水值。（out参数）%然后调用过程。createorreplaceprocedurefind_emp（p_nameinVarChar2,p_saloutnumber）ise_sal%type;beginselectsalintoe_salfromempwhereename=p_name;p_sal:=e_sal;exceptionWhenno_data_foundthenp_sal：=0；end；一调用存储过程declaremsalnumber(5);begin、find_emp('SCOTT',msal);(msal);end;.编写一个程序块，接受一个雇员号与一个百分数，从emp表中将该雇员的薪水增加输入的百分比。(利用过程，inout参数)createorreplaceprocedureaddSal(p_empnoinnumber,p_numinfloat)isbeginupdateempsetSal=Sal+sal*p_numwhereempno=p_empno;exceptionwhenno_data_foundthen('输入的员工编号有误')；；end;―访问存储过程declarebeginaddSal(7788,;commit;end;&存储函数---存储函数:封装了一组Sql语句，提前编译好，效率较高，存储在服务端---存储函数必须有一个返回值，存储函数可以用SeleCt语句中/**createorreplacefunction函数名(参数名in|out类型, )returntypeaS|iSbeginreturn值;end;*/.创建一个函数，它以部门号作为参数且返回那个部门的所有的雇员的整体薪水(其实就是该部门的平均工资)。然后调用此函数。/createorreplacefunctiongetAllSal(f_deptnoinnumber)returnnumberise_sal%type;beginselectavg(sal)intoe_salfromempwheredeptno=f_deptno;returne_sal;】exceptionwhenno_data_foundthenreturn0;end;一调用存储函数selectgetAllSal(20)fromdual;/.创建一个函数，它以部门号作为参数传递并且使用函数显示那个部门名称与位置。然后调用此函数。createorreplacefunctionShoWDnameAndLoC(f_deptnoinnumber)returndept%rowtypease_rowdept%rowtype;begin*select*intoe_rowfromdeptwheredeptno=f_deptno;returne_row;end;一访问存储函数declareerowdept%rowtype;begin]erow:=ShoWDnameAndLoC（20）;Il' '||;end;四触发器练习 触发器（监听器）:监听表中的数据是否发生了改变--增删改操作/*createorreplacetrigger触发器名…after|before在改变之前还是之后执行触发器insert|delete|update监听表的哪个操作on表对哪张表的监听触发器的级别:表级触发器，行级触发器表级触发器不能使