Oracle作业题
一.创建一个简单的PL/SQL程序块使用不同的程序块组件工作使用编程结构编写PL/SQL程序块处理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表中显示该部门的名称与所在位置。方法一：（传统方法）declarev_locdeptcp.dname%type;v_dnamedeptcp.dname%type;v_deptnodeptcp.deptno%type;beginv_deptno:=&部门编号;selectloc,dnameintov_loc,v_dnamefromdeptcpwheredeptno=v_deptno;dbms_output.put_line('员工所在地是：'||v_loc||'；部门名称是：'||v_dname);exceptionwhenno_data_foundthendbms_output.put_line('您输入的部门编号不存在，请从新输入，谢谢');end;方法二：（使用%rowtype）declarev_deptdept%rowtype;beginselect*intov_deptfromdeptwheredeptno=&部门号;dbms_output.put_line(v_dept.dname||'--'||v_dept.loc);end;3.编写一个程序块，利用%type属性，接受一个雇员号，从emp表中显示该雇员的整体薪水(即，薪水加佣金)。（*期末考试试题*）declarev_salemp.sal%type;beginselectsal+commintov_salfromempwhereempno=&雇员号;end;dbms_output.put_line(v_sal);4.编写一个程序块，利用%rowtype属性，接受一个雇员号，从emp表中显示该雇员的整体薪水(即，薪水加佣金)。方式一：（错误程序）（让学生思考错在哪里？）declarev_empempcp%rowtype;beginselect*intov_empfromempcpwhereempno=&雇员编号;dbms_output.put_line('整体薪水是：'||v_emp.sal+v_emp.comm);end;declarebeginv_empemp%rowtype;select*intov_empfromempwhereempno=&雇员号;dbms_output.put_line(v_emp.sal+v_emp.comm);end;5.某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理：DesignationRaise-----------------------Clerk500Salesman1000Analyst1500Otherwise2000编写一个程序块,接受一个雇员名，从emp表中实现上述加薪处理。（*期末考试试题*）declarebeginv_empemp%rowtype;select*intov_empfromempwhereename='&name';ifv_emp.job='CLERK'thenupdateempsetsal=sal+500whereempno=v_emp.empno;updateempsetsal=sal+1000whereempno=v_emp.empno;updateempsetsal=sal+1500whereempno=v_emp.empno;updateempsetsal=sal+2000whereempno=v_emp.empno;elsifv_emp.job='SALESMAN'thenelsifv_emp.job='ANALYST'thenelseendif;end;commit;6.编写一个程序块，将emp表中雇员名全部显示出来。declarecursorv_cursorisselect*fromemp;beginforv_empinv_cursorloopdbms_output.put_line(v_emp.ename);endloop;end;7.编写一个程序块，将emp表中前5人的名字显示出来。declarecursorv_cursorisselect*fromemp;v_countnumber:=1;beginforv_empinv_cursorloopdbms_output.put_line(v_emp.ename);v_count:=v_count+1;exitwhenv_count>5;endloop;end;8.编写一个程序块，接受一个雇员名，从emp表中显示该雇员的工作岗位与薪水，若输入的雇员名不存在，显示“该雇员不存在”信息。（*期末考试试题*）declarev_empemp%rowtype;my_exceptionException;beginselect*intov_empfromempwhereename='&name';raisemy_exception;exceptionwhenno_data_foundthendbms_output.put_line('该雇员不存在!');whenothersthenend;dbms_output.put_line(v_emp.job||'---'||v_emp.sal);9.接受两个数相除并且显示结果，如果第二个数为0，则显示消息“除数不能为0”(课堂未讲)。declarev_dividendfloat;v_divisorfloat;v_resultfloat;my_exceptionException;beginv_dividend:=&被除数;v_divisor:=&除数;v_result:=v_dividend/v_divisor;raisemy_exception;exceptionwhenmy_exceptionthenwhenothersthenend;dbms_output.put_line(v_result);dbms_output.put_line('除数不能为0');二.声明和使用游标使用游标属性使用游标For循环工作声明带参数的游标（使用FORUPDATEOF和CURRENTOF子句工作）1.通过使用游标来显示dept表中的部门名称。declarecursorv_cursorisselect*fromdept;beginforv_deptinv_cursorloopdbms_output.put_line(v_dept.dname);endloop;end;2.使用For循环，接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和薪水。declarecursorv_cursorisselect*fromempwheredeptno=&部门号;beginforv_empinv_cursorloopdbms_output.put_line(v_emp.ename||'--'||v_emp.job||'--'endloop;||v_emp.sal);end;3.使用带参数的游标，实现第2题。declarecursorv_cursor(p_deptnonumber)isselect*fromempwheredeptno=p_deptno;v_deptnonumber(2);beginv_deptno:=&部门号;forv_empinv_cursor(v_deptno)loopdbms_output.put_line(v_emp.ename||'--'||v_emp.job||'--'||v_emp.sal);end;endloop;4.编写一个PL/SQL程序块，从emp表中对名字以“A”或“S”开始的所有雇员按他们基本薪水的10%给他们加薪。declarecursorv_cursorisselect*fromemp;beginforv_empinv_cursorloopifv_emp.enamelike'A%'thenupdateempsetsal=sal+sal*0.1whereempno=v_emp.empno;elsifv_emp.enamelike'S%'thenupdateempsetsal=sal+sal*0.1whereempno=v_emp.empno;endif;commit;endloop;end;5.emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000卢布，则取消加薪。declarecursorv_cursorisselect*fromemp;beginforv_empinv_cursorloopifv_emp.sal*1.1<5000thenupdateempsetsal=sal*1.1whereempno=v_emp.empno;endif;commit;endloop;end;三，创建PL/SQL记录和PL/SQL表创建过程创建函数3.创建一个过程，能向dept表中添加一个新记录.（in参数）createorreplaceprocedureinsert_dept(dept_noinnumber,dept_nameinvarchar2,dept_locinvarchar2)isbegininsertintodeptvalues(dept_no,dept_name,dept_loc);end;调用该存储过程：begininsert_dept(50,'技术部','武汉');end;4.创建一个过程，从emp表中带入雇员的姓名，返回该雇员的薪水值。（out参数）然后调用过程。createorreplaceprocedurefind_emp3(emp_nameinvarchar2,emp_saloutnumber)isv_salnumber(5);beginselectsalintov_salfromempwhereename=emp_name;exceptionemp_sal:=v_sal;whenno_data_foundthenemp_sal:=0;end;调用：declarebeginv_salnumber(5);find_emp3('ALLEN',v_sal);end;dbms_output.put_line(v_sal);5.编写一个程序块，接受一个雇员号与一个百分数，从emp表中将该雇员的薪水增加输入的百分比（*课堂没讲）。（利用过程，inout参数）createorreplaceprocedureupdate_sal(emp_noinnumber,parsentinfloat)isbeginupdateempsetsal=sal+sal*parsentwhereempno=emp_no;end;调用：beginend;update_sal(7499,0.5);6.创建一个函数，它以部门号作为参数且返回那个部门的所有的所有雇员的整体薪水。然后调用此函数。7.创建一个函数，它以部门号作为参数传递并且使用函数显示那个部门名称与位置。然后调用此函数。createorreplacefunctionfind_dept(dept_nonumber)returndept%rowtypeisv_deptdept%rowtype;beginselect*intov_deptfromdeptwheredeptno=dept_no;returnv_dept;end;调用函数：declarev_deptdept%rowtype;beginv_dept:=find_dept(30);dbms_output.put_line(v_dept.dname||'---'||v_dept.loc);end;四，创建程序包创建程序件创建触发器1.创建在dept表中插入和删除一个记录的数据包，它且有一个函数(返回插入或删除的部门名称)和两个过程。然后调用包。createorreplacepackagepack_1isprocedurefind_emp(emp_noinnumber,emp_nameoutvarchar2);procedurefind_emp1(emp_nameinvarchar2,emp_nooutnumber);functionfind_dname(dept_nonumber)returnvarchar2;endpack_1;createorreplacepackagebodypack_1isfunctionfind_dname(dept_nonumber)returnvarchar2isv_dnamevarchar2(20);beginselectdnameintov_dnamefromdeptwheredeptno=dept_no;retrunv_dname;end;endpack_1;调用包：declarev_dnamevarchar2(20);beginv_dname:=pack_1.find_dname(50);dbms_output.put_line(v_dname);3.使用单独过程打开游标变量，将dept表中的记录显示出来。只创建程序包，无需主体。4.创建一个行级别触发器，将从emp表中删除的记录输入到ret_emp表中。createorreplacetriggerdelete_empafterdeleteonempforeachrowbegininsertintoret_empvalues(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);end;5.创建一个行级别触发器，停止用户删除雇员名为\"SMITH\"的记录。createorreplacetriggerdelete_smithbeforedeleteonempforeachrowwhen(old.ename='SMITH')beginraise_application_error(-20001,'不能删除该条信息!');end;end;6.创建一个语句级别触发器，不允许用户在\"Sundays\"使用emp表。createorreplacetriggert_control_empbeforeinsertorupdateordeleteonempbeginifto_char(sysdate,'DY','nls_date_language=AMERICAN')in('SUN')thenraise_application_error(-20001,'不允许在星期天操作emp表');endif;end;
