(完整word版)Oracle---PL-SQL经典练习题

1、Oracle作业题-.创建一个简单的 PL/SQLPL/SQL 程序块使用不同的程序块组件工作使用编程结构编写 PL/SQLPL/SQL 程序块处理 PL/SQLPL/SQL 程序块中的错误1 1 编写一个程序块，从 empemp 表中显示名为SMITHSMITH ”的雇员的薪水和职位。declarev_emp emp%rowtype;begi nselect * into v emp from emp where en ame=SMITH;dbms_output.put_li ne(员工的工作是：|v_emp.job|；他的薪水是：|v emp.sal）;en d;2 2 编写一个程序块，接

2、受用户输入一个部门号，从 deptdept 表中显示该部门的名称与所在位置。方法一：（传统方法）declarev_loc deptcp.d name%type;v dn ame deptcp.d name%type;v_dept no deptcp.dept no%type;beginv deptno:- &部门编口、.号;select loc , dnameinto v_loc , v_dname from deptcpwhere dept no=v_dept nodbms output.put li ne（员工所在地是：|v loc |；部门名称是：|v dname );excep

3、ti onwhen no_data_foundthen dbms_output.put_line(您输入的部门编号不存在，请从新输入，谢谢);end;方法二：(使用%rowtype%rowtype )declarev_dept dept%rowtype;beginselect * into v dept from dept where deptno=&部门号;dbms output.put li ne(v dept.d name|卜-|v dept.loc);en d;3 3 编写一 水（即，薪;declare个程序块，利用 %type%type 属性， 水加佣金）。（* *期末考试试

4、题* *接受）-个雇员号，从empemp 表中显示该雇员的整体新v_salemp. sal %type ;beginselectsal +comm into v_salfromemp whereempno =&雇员号;dbms output. put line（v sal ）;end ;4 4 编写一个程序块，利用rowtyperowtype 属性，接受一个雇员号，从empemp 表中显示该雇员的整体薪水（即，薪水加佣金）。方式一：（错误程序）（让学生思考错在哪里？）declarev_emp empcp %rowtype;beginselect* into v_empfrom empc

5、pwhereempno - &雇员编号；dbms output.put li ne（整体薪水是llv emp.sal+v m );en d;declarev_emp emp%rowtype ;beginselect* in tov empfrom empwhereempno =&雇员号;dbms output. put line( v emp . sal +v emp . comm);end ;5 5 某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理:DesigDesig natinati ononRaiseRaiseClerkClerk500500SalesmaSal

6、esma n n10001000AnAn alystalyst15001500OtherwiseOtherwise20002000编写一个程序块，接受一个雇员名，从 empemp 表中实现上述加薪处理。（* *期末考试试题* *）declarev_emp emp%rowtype ;beginselect * intov emp from emp where ename =&name;if v_emp . job =CLERKthenupdate emp set sal =sal +500 where empno =v_emp . empno ; elsif v_emp . job =S

7、ALESMANthenupdate emp set sal =sal +1000 where empno =v emp . empno ;elsifv emp . job =ANALYSTthenupdate emp set sal =sal +1500 where empno =v emp . empno ;elseupdate emp set sal =sal +2000 where empno =v_emp . empno ;end ifcommitend ;6 6 编写一个程序块，将empemp 表中雇员名全部显示出来。declarecursorv_cursoris select* f

8、rom emp;begi nforv emp inv cursorloopdbms_output.put_line(v_emp . ename );end loopend ;7 7 编写一个程序块，将 empemp 表中前 5 5 人的名字显示出来。declarecursorv_cursoris select* fromemp;v cou ntn umber:=1;beginfor v emp inv cursorloopdbms_output.put_line(v_emp .ename );v count :=v count +1exit whe nv_cou nt 5;end loopen

9、d ;8 8 编写一个程序块，接受一个雇员名，从 empemp 表中显示该雇员的工作岗位与薪水, 的雇员名不存在，显示“该雇员不存在”信息。（* *期末考试试题* *）declarev_emp emp%rowtype ;my excepti onExcepti on;beginselect * into v_emp from emp where ename =&name raise my excepti on;excepti onwhe n no _data_fou ndthe ndbms_outputput_li ne（该雇员不存在 r ）;whe n others the n若输入

10、dbms output. put line( v emp . job | -| v emp . sal );end ;9 9 接受两个数相除并且显示结果，如果第二个数为0 0,则显示消息“除数不能为未讲) )。DeclareDeclareV_1V_1 numbernumber :=&:=& 被除数; ;V_2V_2 numbernumber :=&:=& 除数；V_resultV_result nunu mber;mber;BeginBeginV_resultV_result : :=v_1/v_2;=v_1/v_2;Dbms_output.put_liDbms

11、_output.put_li ne(v_result);ne(v_result);ExceptiExcepti ononWhenWhen Zero-divideZero-divide thethe n nDbms_output.put_line(Dbms_output.put_line(除数不能为 0 0!;WhenWhen othersothers thethe n nDbms_output.put_line(Dbms_output.put_line(未知错误；End;End;二. .声明和使用游标使用游标属性使用游标 ForFor 循环工作声明带参数的游标(使用 FORFOR UPDATE

12、UPDATE OFOF 和 CURRENTCURRENT OFOF 子句工作)1.1.通过使用游标来显示deptdept 表中的部门名称。declarecursor v_cursor is select * from dept ; beginfor v_dept in v_cursorloopdbms_output . put_line ( v_dept . dname );end loop ;end ;2.2.使用 ForFor 循环，接受一个部门号，从 empemp 表中显示该部门的所有雇员的姓名，工作和0 0”（课堂薪水。declarecursorv_cursoris select *

13、from emp wheredept no =&部门号;beginforv_empinv_cursoroopdbms_output.put_line(v_emp . ename | -|v_emp job | -| v_emp . sal );end loop ;end ;3.3.使用带参数的游标，实现第 2 2 题。declarecursorv_cursor(p_deptnonumber ) is select * from emp wheredept no =p dept no ;v_dept non umber(2);beginv deptno:=& 部门号；for v_

14、emp in v_cursor (v_deptno )loopdbms_output . put_line( v_emp . ename | -| v_emp . job | -| v_emp . sal );end loop ;end ;4.4. 编写一个 PL/SQLPL/SQL 程序块，从 empemp 表中对名字以“ A A”或“ S S”开始的所有雇员按他们 基本薪水的 10%10%给他们加薪。declarecursor v_cursor is select * from emp where ename likeA% or enamelikeS%;beginforv emp in v

15、 cursorloopif v_emp . ename like A% thenupdateempset sal =sal+ sal * 0.1whereempno =v emp.empno ;elsifv_emp . en ame likeS% the nupdate empset sal =sal+ sal *0.1 whereempno =v emp .empno ;end if ;commit ;end loop ;end ;5.5.empemp 表中对所有雇员按他们基本薪水的10%10%给他们加薪，如果所增加后的薪水大于50005000 卢布，则取消加薪。declarecursorv

16、_cursorisselect* fromemp;beginfor v_emp in v_cursor loopif v emp . sal *1.1 5000 thenupdate emp set sal = sal * 1.1where empno = v_emp . empno ;end if ;commit ;end loop ;end ;，创建 PL/SQLPL/SQL 记录和 PL/SQLPL/SQL 表创建过程创建函数3 3 创建一个过程，能向deptdept 表中添加一个新记录. .(inin 参数)create or replace procedureinsert_dept(

17、dept_no in number , dept_namein varchar2 , dept_loc invarchar2 )isbegininsert into dept values (dept no , dept name, dept loc);end ;调用该存储过程：begininsert dept（ 50 ,技术部，武汉）;end；4 4. .创建一个过程，从 empemp 表中带入雇员的姓名，返回该雇员的薪水值。（outout 参数）然后调用过程。create or replace procedurefind_emp3 （emp_name in varchar2 , emp_s

18、al out number ）v_salnumber ( 5);beginselect sal into v_sal from emp where ename = emp_name ; emp sal := v sal ;excepti onwhe n no data fou ndthe nemp_sal := 0; end ;调用：declarev_salnumber ( 5);beginfin d_emp3(ALLEN , v_sal );dbms output.put line( v sal );end ;5 5 编写一个程序块，接受一个雇员号与一个百分数，从 empemp 表中将该雇员

19、的薪水增加输入的百分比（* *课堂没讲）。（利用过程，inin outout 参数）create or replace procedureupdate emp set sal =sal +sal * parse ntwhere emp no =emp noend ;调用：beginupdate sal( 7499 ,0.5);end ;6.6. 创建一个函数，它以部门号作为参数且返回那个部门的所有的所有雇员的整体薪水。 然后调用此函数。7.7. 创建一个函数，它以部门号作为参数传递并且使用函数显示那个部门名称与位置。然后调用此函数。create or replacefun cti onfind

20、_dept( dept_nonumber )returndept %rowtypeisv deptdept %rowtype ;beginselect* into v_dept from dept where dept no =dept_ no ;returnv dept ;end ;调用函数:declareupdate_sal(emp_ no in n umber , parse ntin float )beginv_dept dept %rowtype ;beginv dept := fi nd dept(30);dbms output. put line( v dept . dname

21、| -| v dept . loc );end ;四，创建程序包创建程序件创建触发器1 1 创建在 deptdept 表中插入和删除一个记录的数据包，它且有一个函数( (返回插入或删除的部门名称) )和两个过程。然后调用包。create or replace package pack_1is一procedurefind_emp(emp_no in number , emp_name out varchar2 );procedure find_emp1( emp_namein varchar2, emp_noout number );functionfind dname( dept nonumb

22、er )retur nvarchar2 ;end pack_1 ;createor replacepackagebody pack_1isfun cti onfind dn ame(dept nonumber )returnvarchar2isv_dn amevarchar2(20);beginselectdn ame intov dn ame from dept where dept no =dept no ;retru nv_dn ameend ;end pack_1调用包：declarev_dname varchar2 ( 20);beginv dname := pack 1 . find dname( 50);dbms output. put line( v dname );end ;