Oracle经典练习题(很全面)

Oracle经典练习题一.创建一个简单的PL/SQL程序块1.编写一个程序块,从emp表中显示名为“SMITH”的雇员的薪水和职位。declarev_empemp%rowtype;beginselect*intov_empfromempwhereename='SMITH';dbms_output.put_line('员工的工作是：'||v_emp.job||'；他的薪水是：'||v_emp.sal);end;2.编写一个程序块，接受用户输入一个部门号，从dept表中显示该部门的名称与所在位置。方法一：（传统方法）declarepnamedept.dname%type;plocdept.loc%type;pdeptnodept.deptno%type;beginpdeptno:=&请输入部门编号;selectdname,locintopname,plocfromdeptwheredeptno=pdeptno;dbms_output.put_line('部门名称:'||pname||'所在位置:'||ploc);exception–异常处理whenno_data_foundthendbms_output.put_line('你输入的部门编号有误!!');whenothersthendbms_output.put_line('其他异常');end;方法二：（使用%rowtype）declareerowdept%rowtype;beginselect*intoerowfromdeptwheredeptno=&请输入部门编号;dbms_output.put_line(erow.dname||'--'||erow.loc);exceptionwhenno_data_foundthendbms_output.put_line('你输入的部门号有误!!!');whenothersthendbms_output.put_line('其他异常');end;3.编写一个程序块，利用%type属性，接受一个雇员号，从emp表中显示该雇员的整体薪水(即，薪水加佣金)。declarepempnoemp.empno%type;totalSalemp.sal%type;beginpempno:=&请输入员工编号;selectsal+nvl(comm,0)intototalSalfromempwhereempno=pempno;dbms_output.put_line('该员工总共薪水'||totalSal);exceptionwhenno_data_foundthendbms_output.put_line('你输入的员工编号有误!!');whenothersthendbms_output.put_line('其他异常');end;4.编写一个程序块，利用%rowtype属性，接受一个雇员号，从emp表中显示该雇员的整体薪水(即，薪水加佣金)。declareerowemp%rowtype;beginselect*intoerowfromempwhereempno=&请输入员工编号;dbms_output.put_line(erow.sal+nvl(erow.comm,0));exceptionwhenno_data_foundthendbms_output.put_line('你输入的员工编号有误!!');whenothersthendbms_output.put_line('其他异常');end;5.某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理：DesignationRaise-----------------------Clerk500Salesman1000Analyst1500Otherwise2000编写一个程序块,接受一个雇员名，从emp表中实现上述加薪处理。declareerowemp%rowtype;beginselect*intoerowfromempwhereename='&name';iferow.job='Clerk'thenupdateempsetsal=sal+500whereempno=erow.empno;elsiferow.job='Salesman'thenupdateempsetsal=sal+1000whereempno=erow.empno;elsiferow.job='Analyst'thenupdateempsetsal=sal-1500whereempno=erow.empno;elseupdateempsetsal=sal+2000whereempno=erow.empno;endif;commit;exceptionwhenno_data_foundthendbms_output.put_line('你输入的员工编号有误!!');whenothersthendbms_output.put_line('其他异常');end;6.编写一个程序块，将emp表中雇员名全部显示出来。declarecursorcsisselectenamefromemp;beginforerowincsloopdbms_output.put_line(erow.ename);endloop;end;7.编写一个程序块，将emp表中前5人的名字显示出来。方式一:declarecursorcsisselectt.*from(selecte.ename,rownumrmfromempe)twheret.rmbetween1and6;beginforerowincsloopdbms_output.put_line(erow.ename);endloop;end;方式二:--方式二declarecursorcsisselectenamefromemp;inumber:=1;beginforerowincsloopdbms_output.put_line(erow.ename);i:=i+1;--迭代exitwheni5;--退出条件endloop;end;8.编写一个程序块，接受一个雇员名，从emp表中显示该雇员的工作岗位与薪水，若输入的雇员名不存在，显示“该雇员不存在”信息。declarepjobemp.job%type;totalsalemp.sal%type;beginselectjob,salintopjob,totalsalfromempwhereename='&请输入员工姓名';dbms_output.put_line(pjob||'----'||totalsal);exceptionwhenno_data_foundthendbms_output.put_line('你输入的员工姓名有误!!');end;9.接受两个数相除并且显示结果，如果第二个数为0，则显示消息“除数不能为0”。declarenum1float;num2float;resfloat;my_exceptionException;beginnum1:=&被除数;num2:=&除数;res:=num1/num2;raisemy_exception;exceptionwhenmy_exceptionthendbms_output.put_line(res);whenothersthendbms_output.put_line('除数不能为0');end;二.声明和使用游标------游标:(集合)，处理返回多行记录的问题--声明游标--语法：cursor游标名isDQL;--遍历游标/*1.打开游标,open游标名;2.从游标中提取一行的记录：fetch游标名into变量名,...;3.使用循环，exitwhen游标名%notfound;4.关闭游标，close游标名;1.通过使用游标来显示dept表中的部门名称。declarecursorcoisselectdnamefromdept;beginforvnameincoloopdbms_output.put_line(vname.dname);endloop;end;2.使用For循环，接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和薪水。declarecursorc_empisselect*fromempwheredeptno=&请输入部门号;beginforerowinc_emploopdbms_output.put_line(erow.ename||''||erow.job||''||erow.sal);endloop;exceptionwhenno_data_foundthendbms_output.put_line('输入的部门编号有误');end;3.使用带参数的游标，实现第2题。declarecursorc_cs(c_deptnonumber)isselect*fromempwheredeptno=c_deptno;v_deptnonumber;beginv_deptno:=&请输入部门编号;forerowinc_cs(v_deptno)loopdbms_output.put_line(erow.ename||''||erow.job||''||erow.sal);endloop;exceptionwhenno_data_foundthendbms_output.put_line('输入的部门编号有误');end;4.编写一个PL/SQL程序块，从emp表中对名字以“A”或“S”开始的所有雇员按他们基本薪水的10%给他们加薪。declarecursorc_empisselectenamefromemp;beginforerowinc_emploopiferow.enamelike'A%'thenupdateempsetsal=sal+sal*0.1whereename=erow.ename;elsiferow.enamelike'S%'thenupdateempsetsal=sal+sal*0.1whereename=erow.ename;endif;commit;endloop;end;5.emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000卢布，则取消加薪。declarecursorc_empisselect*fromemp;beginforerowinc_emploopiferow.sal*1.15000thenupdateempsetsal=sal+sal*0.1whereename=erow.ename;endif;commit;endloop;end;三存储过程-----存储过程(dba声明,得授予dba权限):封装了一组sql语句，提前编译好，效率较高,存储在服务端--场景：网购：数据库发生什么改变--库存量-1(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;2.创建一个过程，从emp表中输入雇员的姓名，返回该雇员的薪水值。（out参数）然后调用过程。createorreplaceprocedurefind_emp(p_nameinvarchar2,p_saloutnumber)ise_salemp.sal%type;begin