实验6+过程_函数和程序...

1、第7页共26页.-IH x|xl实验6过程、函数和程序包姓名：学号：专业：班级：同组人：无实验日期：2013/7/21【实验目的与要求】掌握过程的创建与调用掌握PL/SQL函数的编写与调用熟悉程序包的使用【实验内容与步骤】6.0 .实验准备工作：PL/SQL程序文件的编辑与执行1 .使用文档编辑器编辑以下文件，并保存为aa.sql:dlFEl arppp ran nor rap .p p ,person name enp .enanelttyipe;bryimsplfc t empno t 审ni ntu|JFrsan_no perion_njnfifruit vh|j询4* pnpnn) 7

2、7RR;exceptlonuh edit aaSQL 8AA1* /注：测试时，文件名请用全名（即包含路径，如：c:aa） 给出运行结果：+ Oracle SQL*PIuj6.1 .存储过程1. 最简单的存储过程编写与执行(1) 创建测试表drop table Exam_Table;create table Exam_Table(e_id nu mber(5),e_n ame varchar2(20),e_salary nu mber(8,2);(2) 创建存储过程create or replace procedure in sert_salary (v_id nu mber,v_ name

3、varchar2,v_salary nu mber) isbeginin sert into Exam_Table values (v_id,v_ name,v_salary);commit;dbms_output.put_line(数据插入成功);en d;/(3) 执行(调用)存储过程exec in sert_salary(6,g,2000);(4) 查询执行结果select * from Exam_Table;给出执行的最后结果:cre-ate tdbl? ex刁m tablet2 e_i exec insert_salari/(6, g* ,290);PL/SOL过程己咸助完成。SQL

4、 select * fron exam_table:E ID E NAIHEE SfiLAR?6 g20002. 参数的使用：in/out/in out 参数阅读以下程序，理解不同类型参数使用的不同，运行程序，给出运行结果。(1) 用两个参数：in ,out传入一个姓名，输出：某某人你好：create or replace procedure mp(v_i n varchar2,v_out out varchar2)isbeginv_out:=v_in你好；en d;declarev_n ame varchar2(10);beginmp(scott,v_ name);dbms_output.p

5、ut_li ne(v_ name); end;-输出：scott你好 给出运行结果：0 Oracle SQL*P1uj1回冈文件电）編辑 搜索 选项辺 粘助叫6 g2000ASQL create or replace procedure np(u_in uarchar2,u_out out warchar22 is-3 begin4 v_out：=v_i叫 | 你奸；吕 end;6 /过程已创患SQL de-clare2 uname uarchar2(1O);3 bgin4 mp(scott,v_nane);5 dims output.put line(v name);6 end;7 /PL/

6、SQL i程己成功完成。SQL(2) - i n out 类型参数create or replace procedure mp(n ame_ inin varchar2,n ame_out out varchar2,n ame_ in _out in out varchar2)isbegindbms_output.put_li ne(n ame_i n);name_out:=返回的参数 name_out是| name_in;name_in_out := name_in_out 是| namen | name_in_out;en d;给出运行结果：S(L create or replace pr

7、ocedure np(nane_in in uarchar2,2 nane_outout启尸 2.3 namo in out in out credit wr rrpldUi? piruedlure pJLeslfiidn gut udyt! uul nuivribvruul Udrth占2 .*丄打ul imnbvr ? is9 ibegi n4 iiiiame:-B5CDtt;& agei-26 ：6 火耳：l男幕J M11-8S0S；89 /过保己世眩E0L K1LV3 u1 qe* nuraberf 1D) *也 u_ sex uarEharZ(5);5 u sal minber( 1

8、 ii) *ptest (ii_n 罷 z. y_r)gp. u_5Mt, v_?a 1) jHdibnsniir piir puti nrcvnianr);9di|iOSHDutpLjt wpu inline (:IB dbns DulpLit.put Ilnefu sx);11 dbirts jouipuil a put_line (；12 岀油；13 friMUL过程已威朮完成*3练习：根据测试表完成下列程序的编写，并给出测试结果:(1)已知有如下表和相应的数据，请根据要求完成实验。create table departme nts(DEPARTMENT。NUMBER(4) primar

9、y key, DEPARTMENT_NAME V ARCHAR2(30), MANAGER_ID NUMBERLOCATION。NUMBER(4);insert into departments values(1,技术部,1,1); insert into departments values(2,人事部,2,2); insert into departments values(3,市场部,3,3); insert into departments values(4,财务咅B ,4,4);根据上表结构编写存储过程，实现以下功能，并给出测试结果。SQL create tddie depdrtme

10、n(2 DEFARTMEI1TNUI1BER(4) primary3 DEPARTMENT_Nfll1E UARCHAR2(3O),4 t1ANAGER_lD NUMBER(6),5 LOCATIONID NUHBER(U)6 -QL insert into departments, 技术部？ .1；已创建1行。SQL insert into depart me nts ulues(2 T 人事部QQ；已创建1行。SQL insert into departmentsJ市场部，叭7已刨健1行。SQL insert into departnents values财务部严山”已协健1行。1)根据指

11、定的部 门更新指定部门名(DEPARTMENT_NAME )的管理者(MANAGER_ID ) id。SQL234561create or replace procedure dep_pro(u_de|p_riaine in varcharZ pUdepid io number) LS beginupdate Hepartmentsset reanager_id=u_dep_Ldlwhere dep artmenitnaiiiie-uclep name ;Bndl;SQL deciare2 bpgi na dep_proCfj5； 斗 comnit;q pncl;o f卩SQL过程己咸功完淼D

12、EPfiRTI1ENT_ID DE PR KT HE NT JW HENfiNfiGERJP LDCfiTION ID1技术肖512夬事肖2 23市场咅334财务44f-ni %12）根据部门ID （DEPARTMENT。）删除指定部门Greate or replace procedure delete_dep(w_dep_id in number)15begindelete Fron departmentsuhert departnent_ld=u dep_i(i;conmit;9nd;过程已创建QL declare2 begin3 deLete-_dep(1);4 e-nd;5 fPL/S

13、OL过程己成功宪成。DEPARTMENTD DEPftRrMEHT NAblEMANAGERTD LOCftTIONID-血口立口宜口一事尊一人市财-2 3 kr一-一-一一一-一-J2213314h2）编写给雇员增加工资的存储过程 CHANGE_SALARY ，通过 IN 类型的参数传递要增加 工资的雇员编号和增加的工资额。 （操作数据库表为 EMP ）.1） 编写存储过程 CHANGE_SALARY ，给出程序代码：2） 调用存储过程：EXECUTECHANGE_SALARY(7788,80)6.2 函数1.最简单的函数：- 简单函数(1) 创建函数create function f(na

14、me in varchar2)return varchar2 isbeginreturn name; end;(2)调用函数declarev_n ame varchar2(10);beginv_n ame:=f(scott);dbms_output.put_li ne(v_ name);en d;给出运行结果：Function F(name in uajchap2)return2 is山 return;? end6 ；7 /麺数已创建。jQL declare2 v_nane uarchar?(10):3 begin1undine zfrtscott*);5 dbmsDutpu t ,put_l

15、ine(u_nanip);6 end;7 /过程已成功兄际2 稍微复杂的函数-编写函数create or replace fun cti on salarylevel(salary nu mber) retur n varchar2 isbeginif alary 1000 the nreturn 工资太低了，要加油了 ；elsif salary 3000 thenreturn 还可以,但是也要努力啊；elsif salary 5000 then return 这个还可以；else你的工资现在来说，是比较高了 ；end if;en d;-调用函数declarev_salarylevel var

16、char2(50); beginv_salarylevel := salarylevel(IOOO); dbms_output.put_li ne(v_salarylevel); en d;给出运行结果：uarchar2QL create or PGplacG Function salartleuel(salar number) return2 is3 begin埠if1 BOO then5 return 1工资太低了，要加油了；6 elsif salary3O0O then1 return 还可岌，但是也要努力啊飞8 elsif salar dclare2 u_salaryleuel uar

17、char2(50);3 begin4 usalaryleuel := salaryleuel(10B0;5 dbns output ,p(jt_Line(u_salari|leuel);6 &nd:pl/sql过程己成功気成。3 函数练习：(1).定义一个函数接收三个参数，算出最大值。而后调用该函数，给出测试结果。给出求三个数最大值函数getMax( nu ml, nu m2, num3)程序源码：第13页共26页SQL create or replace function t_nax(n1 nunbern2 nunber,n3 number) return nunber2 is3 beqin

18、4 if nln2 then5 if nln3then6 return nl;7 Rise8 return n3;9 end if;10 else11 If nzn3 then12 return r)z；131*ireturn r)3;15 end ir;16 end iF;17 end;1S /屉数己刨建.SOL set serueroutput on：SQL declare2 u hhx number;3 begin4 uniax： =t_nai! (6,2,3);F dbnE_Dutput.put_line(三个中最大拘是11 u_max;6 end;7 /二个中最大対是血尸过程已威励完

19、咸*-调用测试：declarev_max nu mber(10,2);beginv_max:= getMax (10.2,34.4,34.6); dbms_output.put_li ne(v_max);en d;给出运行结果：UHuidrh2 v nunberCIQ?);U U_KiSX：=t_nrlX1H.75 obms out卩ut；6（2）.根据房屋的相关数据（长，宽），编写函数find_area ，根据公式求出其建筑面积和 使用面积，并写入到数据库表中，完成后，使用 Select语句查询表中数据，以确认程序 编写的正确性。1）创建表：建立房屋表 House（房屋名称，长，宽，建筑面积

20、，使用面积）；给出相应代码：SQL ereop roplace function Finti_jrej(u_1 in (lotdu_w in floatJreturn fLot2 is_3 uare Float;h beqin5 u_arpj!u_1*j_w;6 rcturr v_apca;1 tmil：8 /國数三创建。SI|L set rueroutput on;S0L declare2 uirea Float;3 begin4 u_area: HnoareaiS ,3J;5 dbns uiitput-put_line2.蒐为3的向枳为? Jlu.area)；6 and;7 /*为乙觅为3

21、旳直积为：Cpi /shi过程己成功完咸*2) 插入三行记录测试，插入值时忽略建筑面积和使用面积1 号机房,20,5.52 号机房,25,5.5卧室,200,5.5给出相应代码：SQL? create table House2 (3 housenane varcitiar?C19) 94 liouseLengtti FLoat,5 liouseviidth FloatF6 housedirea Float,1 usearea float6 )；表已创建。$QLHL icibrrt intv Huhfery tSQi 1(siL lriEPirt Into Hoiis0【nUEBn3J*nou占

22、el刨ngtn,n(ij电uidtn)p ,25j;己创連1行口SQL connlt;売交冗廉。3) 利用find_area函数，更新建筑面积和使用面积字段-建筑面积 =长*宽+ 2.5-使用面积=长*宽给出相应代码：SQL update house2 set housearpa=Find_area (house Leng th ,housewidith)+2.5,3 pj( ho Length ,hou?iiidth)即：-已更新?行。QL4) 在select查询表中数据。给出运行结果：SQL select * fron house;HOUSEHAME HOUSELEMGTH HOUSEVI

23、DTIi HOUSEAREA USEAREA房房SQL265.5112.5110255.5140137,5255.514C137.56.3 .包的声明和使用阅读以下程序，理解包的声明和使用。包的声明和使用 iDROP TABLE Exam_Emps;CREATE TABLE Exam_Emps(id nu mber(5) primary key,n ame varchar2(30), commissi on _pct nu mber(3,2 );insert into Exam_Emps values (1,张一 ,0.13);insert into Exam_Emps values (2,张

24、二,0.23);insert into Exam_Emps values (3,张三,0.33);insert into Exam_Emps values (4,张四,0.43); commit;select * from Exam_Emps;-创建包头CREATE OR REPLACE PACKAGE comm_packageISg_comm NUMBER := 0.10;PROCEDURE reset_comm (p_comm IN NUMBER);END comm_package;/-创建包体CREATE OR REPLACE PACKAGE BODY comm_packageIS 在包

25、体中定义的局部函数 /*如果输入的参数 p_comm 大于 Exam_Emps 表中最大的 commission_pct 字段 ,则函数返回 FALSE, 否则函数返回 TRUE*/FUNCTION validate_comm (p_comm IN NUMBER)RETURN BOOLEANIS v_max_comm NUMBER;BEGINSELECT MAX(commission_pct) INTOv_max_commFROM Exam_Emps;IF p_comm v_max_comm THENRETURN FALSE ;ELSERETURN TRUE ;END IF;END valid

26、ate_comm; 在包体中定义的局部函数 :结束 完成在包体中声明的过程 PROCEDURE reset_comm (p_comm IN NUMBER)ISBEGINIF validate_comm(p_comm) THEN g_comm:=p_comm;ELSERAISE_APPLICATION_ERROR(-20210, 不合理的表达式 );END IF;END reset_comm; 完成在包体中声明的过程 :结束 END comm_package;-测试包EXECUTE comm_package.reset_comm(1);EXECUTE comm_package.reset_com

27、m(.33); 给出运行结果：SQL CREATE TABLE Exan_Emp(2 idprimary kept9 (1-ame uar c：har2(30),4 connissionpct number(3,2 )袤己创建乜insert into ExanFnps values (1, f0-13);己创建1行。S()L insert into Exan_Enps values (2, 0-23);己创建1行。S()L insert into EKan_Enps values (3,张三川-朗卄 已刨建1行S(|L insert into EKam Enps values (叫 马檢四川丄

28、町； 已创建1行。SQL commit;提交冗成。SQL select * Fton ExanEnps;ID HAHECOHMISSIONPCT二二四 f/KLJNLJK 12 3 43 3 3 3 12 3ft- begincomm_package.reset_comm(0.15);dbms_output.put_li ne(g_comm = | comm_package.g_comm ); en d;/给出运行结果：0 end;rL/SQL过程己戒功宪成包的声明和使用 2drop table Exam_Emp;create table Exam_Emp(id nu mber (5),n a

29、me varchar2(30), salary nu mber(8,2);insert into Exam_Emp values(1,张一 ,3000);insert into Exam_Emp values(2,张二,3400);insert into Exam_Emp values(3,张三,5600);commit;S(L create tabid Exam_EmpC2 id number C5),3 nan(? varchar2(30),U? a Lar nufnber(Q,2)5)；表已创建。SQL5 insert nto Exam E叩 ualues(1 , 1 ,9 0H0)己创

30、肄1行。SQL insert into Eam_Enp alues(2,1 张二冷却阳)；己创莓1行亠SQL insert into Exam_Emp ualues(3,三，弘BP);己创建1行。SQL connilt；create or replace package tax_pkg asfun ctio n tax(v_value in nu mber) return nu mber; end tax_pkg;/SQL create or replace package tax_pkg as2 function taK(v value in number) 广turn nunber;3 e

31、nd taxpkg;4 J程序包已创建。create or replace package body tax_pkgas包体中的函数执行部分 fun cti on tax(v_value in nu mber) retur n nu mberisbeginif v_value 1000 the nretur n (v_value * 0);elsif v_value 5000 the nreturn (v_value * 0.10);elsif v_value create or replace park占go bod taxpkg3Functionnumber ) returm number

32、Uis5 begin6 if u_value 诃帕 then7 return (u valije * 0);8 el5if v_ualue 5000 then9 return (v_value * 0.10);10 elsif u_ualue select salary,tax_pkgfron Exan_Emp;SALARV TflXPKG-TnX(SALflRV)34005600300 的0 84 0trni 、6.4作业与思考练习题以为实验中用到的表除作特殊说明外，均为scott模式下的表，如Emp表,连接到scott模式。1. 创建一个存储过程 show_emp()，以员工号为参数，输出

33、该员工的工资。执行测试结果应如下：SJQlfeA cxcc 31ww_ienipCZHIia7 i*5刖员工工号为POSOL过程己难功龙以.(1)请给出程序源码：create or replace procedure show_emp(v_emp no in nu mber) isv_sal nu mber;beginselect sal into v_salfrom empwhere emp no=v_emp no;dbms_output.put_line(v_empno的工资是：|v_sal); en d;/请给出测试结果：SQL exec sriou_emp(7U99);99的工资是：1

34、600PL/EQL过程已成功完成。SQL|2. 创建一个存储过程，以员工号为参数，修改该员工的工资：若该员工属于 则工资增加150;若属于20号部门，则工资增加 200;若属于30号部门, 250;若属于其他部门，则工资增加300.请在实验时，10号部门, 则工资增加(1)请给出程序源码：SQL create or replace procedure show enp(u_enpno in number)2 is3 veal number;U v_d?ptno number;5 newsal number;6 begin7 select sal,deptno intd UsjITu_doptn

35、o8 fron enp9 where empno=u empna;10 if u_deptn(j=lQ then11 neu salz=v 531+150;12 eislF v_Jeptno-2 0 men13 new_sal:=v_sal+2O0;1ii pl si F u_1pp1rioafl then15 n?w_5al：cu_sa1+258;16 el5G11newsal:=u_sal+300;18 end if;15 update cup20 set sal=new sal21 where ?mpno=u empno;22 commit；23 dbnsoutput .put_line

36、(u_enpno 1原工资是I |u_sal); 岀 diii(2)完成后，请执行存储过程，并查询表中数据确认是否完成规定要求。SQL exec show_erap(74?9); 汕仞的厚工资是：1&00 新的工贲是：1850PL/SQL过程己成功完成。SQL |3. 创建一个存储过程maxSal()，以一个整数为参数，输出工资最高的前几个(以参数值为标准)员工的信息。执行测试结果应如下：5* maifS41 ；7VBH 5GOTT 3BHHOHD JUUUJOHEE 2?7S(1)请给出程序源码：create or replace procedure hig_sal( num in nu m

37、ber) iscursor c1 is select * from emp order by sal desc;v_emp c1%rowtype;beginope n c1;loopfetch cl into v_emp;dbms_output.put_li ne(v_emp.emp no|v_emp.e name |v_emp.sal); exit whe n c1%rowco unt=num;end loop;en d;请给出测试结果：SQL exec hlg_sal(U)；2313 name7657 yinan7839 KING &0UU7788 SCOTT 3RR0PL/SQL过程己成

38、功完成。4. 创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。 执行测试结果应如下：cxc cXcc tH736-9SMITH3&755i-|HARD1MnRTIM12597S-4-4TURNKRAUAMVJAME95079 flflJAMES过性巴甌牺兀厩(1)请给出程序源码:reate or replace procedure inf(nu m1_sal in nu mber, nu m2_sal in nu mber) is cursor c1 is select * from empwhere sal betwee n nu m1_sal and nu m2_

39、salorder by sal desc;v_emp c1%rowtype;beginope n c1;loopfetch cl into v_emp;dbms_output.put_li ne(v_emp.emp no|v_emp.e name |v_emp.sal);exit whe n c1% notfound;end loop;end;请给出测试结果：SQL exe匚;7844 TURNER 150Q7934 HILLER 13007521 UARD 1250?65U MARIIN 12507876 ADANS 11007900 JAMES 9507969 SMITH 800?969

40、SMITH 800PL/SQL过程己成功完成fQL |5. 编写一个存储过程 checkSal()，用以检查所指定雇员，传入员工工号作为参数，检查 该员工的薪水是否在有效范围内。不同职位的薪水范围为：Desig nati on RaiseClerk 1500-2500Salesma n 2501-3500An alyst 3501-4500 Others 4501 and above.如果薪水在此范围内，则显示消息Salary is OK，否则，更新薪水为该范围内的最低值。请给出程序源码：Create or replace function sal_level( no emp.emp no%t

41、ype)retur n charasv_job emp.job%type;v_sal emp.sal%type;v_mesg char(50);beginselect job,salinto v_job,v_salfrom emp where emp no=no;if v_job= CLERK thenif v_sal=1500 and v_sal=2501 thenv_mesg:= salary is ok;elsev_sal:=2501;v_mesg:= have updated your salary to to_char(v_sal); end if;elsif v_job= ANAL

42、 YST thenif v_sal=3501 and v_sal=4501 thenv_mesg:= salary is okelsev_sal:=4501;v_mesg:= have updated your salary toto_char(v_sal); end if;end if;update empset sal=v_salwhere emp no=no;commit;return v_mesg;en d;/请给出程序运行结果：U2 set 5sl=u_salUS where enpna=nD;片埠 conni5 return v_mesg;end:U7US /函数己创建。S0L I

43、sqLSQL23set seruergutput on;declareu_mesg cliar(50):uenpno emp.empnoXtyp;beginu_enpno:=6enpro;u_mesg:=al_leucl(u_pmpno): dbns_output _put_line(u_mesg); end;/empno 的值：71(995; v_enpn叮：-ftempnO;S x v em()no; = 74?9 ;updated vour salary toZOIPL/SQL过程已咸功完成。6. 编写一个 PL/SQL 函数 count_by_Salary ，接受传入参数 Salary

44、_min 和 Salary_max , 据此统计并返回薪水值在Salary_min和Salary_max之间的员工人数。请给出程序源码：create or replace function coun t_by_Salary(Salary_ min in nu mber,Salary_max in nu mber)retur n nu mberiscoun t_sal nu mber := 0;beginselect coun t(emp no)into coun t_salfrom empwhere sal = Salary_m in andsal begin2 dbm5_autput .put_iine( 2C资衽