游标和异常处理 oracle

1、游标和异常处理游标的概念游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是是用于临时存存储从数据库库中提取的数数据块。在某些情况下，需需要把数据从从存放在磁盘盘的表中调到到计算机内存存中进行处理理，最后将处处理结果显示示出来或最终终写回数据库库。这样数据据处理的速度度才会提高，否否则频繁的磁磁盘数据交换换会降低效率率。游标有两种类型型：显式游标标和隐式游标标。在前述程序中用用到的SELLECT.INTOO.查询询语句，一次次只能从数据据库中提取一一行数据，对对于这种形式式的查询和DDML操作，系系统都会使用用一个隐式游游标。但是如如果要提取多多行数据，就就要由程序员员定

2、义一个显显式游标，并并通过与游标标有关的语句句进行处理。显式游标对应一一个返回结果果为多行多列列的SELEECT语句。游标一旦打开，数数据就从数据据库中传送到到游标变量中中，然后应用用程序再从游游标变量中分分解出需要的的数据，并进进行处理。隐式游标如前所述，DMML操作和单单行SELEECT语句会会使用隐式游游标，它们是是：* 插入操操作：INSSERT。* 更新操操作：UPDDATE。* 删除操操作：DELLETE。* 单行查查询操作：SSELECTT . INTO .。当系统使用一个个隐式游标时时，可以通过过隐式游标的的属性来了解解操作的状态态和结果，进进而控制程序序的流程。隐隐式游标可以

3、以使用名字SSQL来访问问，但要注意意，通过SQQL游标名总总是只能访问问前一个DMML操作或单单行SELEECT操作的的游标属性。所所以通常在刚刚刚执行完操操作之后，立立即使用SQQL游标名来来访问属性。游游标的属性有有四种，如下下表所示。范例：使用隐式式游标的属性性，判断对雇雇员工资的修修改是否成功功。SET SSERVERROUTPUUT ON BEGINN UPDDATE eemp SEET sall=sal+100 WWHERE empnoo=12344; IF SQL%FFOUND THEN DBBMS_OUUTPUT.PUT_LLINE(成功修改雇雇员工资！); COOMMIT;

4、 ELSSEDBMS_OUUTPUT.PUT_LLINE(修改雇员工工资失败！); ENDD IF; END;说明：本例例中，通过SSQL%FOOUND属性性判断修改是是否成功，并并给出相应信信息。显式游标游标的定义和操操作游标的使用用分成以下44个步骤。1声明游游标在DECLLEAR部分分按以下格式式声明游标：CURSOOR 游标名名(参数1 数据类型型，参数2 数据类型型.) IS SSELECTT语句;参数是可选选部分，所定定义的参数可可以出现在SSELECTT语句的WHEERE子句中中。如果定义义了参数，则则必须在打开开游标时传递递相应的实际际参数。SELECT语语句是对表或或视图的查

5、询询语句，甚至至也可以是联联合查询。可可以带WHEERE条件、ORDDER BYY或GROUPP BY等子子句，但不能能使用INTTO子句。在在SELECCT语句中可可以使用在定定义游标之前前定义的变量量。2打开游游标在可执行部部分，按以下下格式打开游游标：OPEN 游标名(实际参数数1，实际参参数2.);打开游标时时，SELEECT语句的的查询结果就就被传送到了了游标工作区区。3提取数数据在可执行部部分，按以下下格式将游标标工作区中的的数据取到变变量中。提取取操作必须在在打开游标之之后进行。FETCHH 游标名 INTO 变量名1，变量名名2.;或FETCHH 游标名 INTO 记录变量;游

6、标打开后后有一个指针针指向数据区区，FETCCH语句一次次返回指针所所指的一行数数据，要返回回多行需重复复执行，可以以使用循环语语句来实现。控控制循环可以以通过判断游游标的属性来来进行。下面对这两种格格式进行说明明：第一种格式式中的变量名名是用来从游游标中接收数数据的变量，需需要事先定义义。变量的个个数和类型应应与SELEECT语句中中的字段变量量的个数和类类型一致。第二种格式式一次将一行行数据取到记记录变量中，需需要使用%RROWTYPPE事先定义义记录变量，这这种形式使用用起来比较方方便，不必分分别定义和使使用多个变量量。定义记录变变量的方法如如下：变量名 表表名|游标名名%ROWTTYP

7、E；其中的表必必须存在，游游标名也必须须先定义。4关闭游标CLOSEE 游标名;显式游标打打开后，必须须显式地关闭闭。游标一旦旦关闭，游标标占用的资源源就被释放，游游标变成无效效，必须重新新打开才能使使用。范例：以下是使使用显式游标标的一个简单单练习。用游游标提取emmp表中77888雇员的名称称和职务。(yb1.ssql)SET SSERVERROUTPUUT ONDECLAARE v_eename VARCHHAR2(110); v_jjob VAARCHARR2(10); CURRSOR eemp_cuursor IS SSELECTT enamme,jobb FROM emp WWHE

8、RE empnoo=77888;BEGINN OPENN emp_cursoor; FETTCH emmp_currsor IINTO v_enaame,v_job; DDBMS_OOUTPUTT.PUT_LINE(v_enaame|,|vv_job); CCLOSE emp_ccursorr;END;说明：该程序通通过定义游标标emp_ccursorr，提取并显显示雇员77788的名称称和职务。作为对以上上例子的改进进，在以下训训练中采用了了记录变量。范例：用游标提提取emp表中77888雇员的姓名名、职务和工工资。(ybb2.sqll)SET SSERVERROUTPUUT ONDECLA

9、ARE CURRSOR eemp_cuursor IS SSELECTT enamme,jobb,sal FROM emp WWHERE empnoo=77888; empp_recoord emmp_currsor%RROWTYPPE;BEGINNOPEN emmp_currsor; FEETCH eemp_cuursor INTO emp_rrecordd;DBMS_OUTPUUT.PUTT_LINEE(emp_recorrd.enaame|,| emp_rrecordd.job|,| to_char(emp_rrecordd.sal); CLOOSE emmp_currsor;END;说

10、明：实例中使使用记录变量量来接收数据据，记录变量量由游标变量量定义，需要要出现在游标标定义之后。注意：可通过以以下形式获得得记录变量的的内容：记录变量名名.字段名。范例： 显示工工资最高的前前3名雇员的名名称和工资。（yb3.sql）SET SSERVERROUTPUUT ONDECLAARE v_eename VARCHHAR2(110); v_sal NNUMBERR(5); CURRSOR eemp_cuursor IS SSELECTT enamme,sall FROMM emp ORDERR BY ssal DEESC;BEGINN OPEEN empp_curssor; FORR

11、I INN 1.33 LOOPP FEETCH eemp_cuursor INTO v_enaame,v_sal;DBMS_OUUTPUT.PUT_LLINE(vv_enamme|,|v_sal); ENDD LOOPP; CLOOSE emmp_currsor;END;说明：该程序在在游标定义中中使用了ORRDER BBY子句进行行排序，并使使用循环语句句来提取多行行数据。游标循环范例：使用特殊殊的FOR循环形形式显示全部部雇员的编号号和名称。(yb4.ssql)SET SERRVEROUUTPUT ONDECLAREE CURSSOR emmp_currsor IIS SELEECT em

12、mpno, enamee FROMM emp;BEGINFOR emmp_reccord IIN empp_curssor LOOOP DBBMS_OUUTPUT.PUT_LLINE(EEmp_reecord.empnoo| Emmp_reccord.eename);END LOOOP;END;说明：可以看到到该循环形式式非常简单，隐隐含了记录变变量的定义、游游标的打开、提提取和关闭过过程。Empp_recoord为隐含含定义的记录录变量，循环环的执行次数数与游标取得得的数据的行行数相一致。范例：另一种形形式的游标循循环。（ybb5.sqll）SET SERRVEROUUTPUT ON BEG

13、IN FOR ree IN (SELECCT enaame FRROM EMMP) LLOOP DBMS_OUTPUUT.PUTT_LINEE(re.eename); END LOOOP;END;说明：该种形式式更为简单，省省略了游标的的定义，游标标的SELEECT查询语语句在循环中中直接出现。显式游标属性虽然可以使用用前面的形式式获得游标数数据，但是在在游标定义以以后使用它的的一些属性来来进行结构控控制是一种更更为灵活的方方法。显式游游标的属性如如下表所示。 可按照以下形式式取得游标的的属性：游标名%属属性要判断游标标emp_ccursorr是否处于打打开状态，可可以使用属性性emp_ccu

14、rsorr%ISOPPEN。如果果游标已经打打开，则返回回值为“真”，否则为“假”。具体可参参照以下的训训练。范例： 使用游游标的属性练练习。(ybb6.sqll)SET SERRVEROUUTPUT ONDECLAREE v_enaame VAARCHARR2(10); CURSOOR empp_curssor ISS SELECCT enaame FRROM emmp;BEGIN OPEN eemp_cuursor; IF empp_curssor%ISSOPEN THENLOOP FETCCH empp_curssor INNTO v_enamee; EXITT WHENN emp_cu

15、rsoor%NOTTFOUNDD; DBMSS_OUTPPUT.PUUT_LINNE(to_char(emp_ccursorr%ROWCCOUNT)|-|v_eename); END LLOOP; ELSE DBMS_OUTPUUT.PUTT_LINEE(用户信信息：游标没没有打开！); END IFF; CLOSE emp_cursoor;END;说明：本例使用用emp_ccursorr%ISOPPEN判断游游标是否打开开；使用emmp_currsor%RROWCOUUNT获得到到目前为止FFETCH语语句返回的数数据行数并输输出；使用循循环来获取数数据，在循环环体中使用FFETCH语语句；

16、使用eemp_cuursor%NOTFOOUND判断断FETCHH语句是否成成功执行，当当FETCHH语句失败时时说明数据已已经取完，退退出循环。练习:去掉OPPEN emmp_currsor;语语句，重新执执行以上程序序。游标参数的传递递(了解)范例：带参数的的游标。（yyb7.sqql）SET SSERVERROUTPUUT ONDECLAARE v_empnoo NUMBBER(5); v_enamee VARCCHAR2(10); CCURSORR emp_ccursorr(p_depttno NUUMBER, p_jobb VARCCHAR2) IS SSELECTTempnoo,

17、enaame FRROM emmp WWHEREdeptnno = pp_depttno ANND jobb = p_job;BEGIN OPPEN emmp_currsor(110, CCLERK); LLOOP FETCCH empp_curssor INNTO v_empnoo,v_enname; EEXIT WWHEN eemp_cuursor%NOTFOOUND; DBMS_OUTPUUT.PUTT_LINEE(v_emmpno|,|v_enaame); ENDD LOOPP; EEND;说明：游标emmp_currsor定义义了两个参数数：p_deeptno代代表部门编号号，p_j

18、oob代表职务务。语句OPPEN emmp_currsor(110, CCLERK)传递了两两个参数值给给游标，即部部门为10、职务为为CLERKK，所以游标标查询的内容容是部门100的职务为CLLERK的雇雇员。循环部部分用于显示示查询的内容容。练习:修改Oppen语句的的参数：部门门号为20、职务为为ANALYYST，并重重新执行。也可以通过变量量向游标传递递参数，但变变量需要先于于游标定义，并并在游标打开开之前赋值。对对以上例子重重新改动如下下：范例： 通过过变量传递参参数给游标。(yb8.sql)SET SSERVERROUTPUUT ONDECLAARE v_eempno NUMBE

19、ER(5); v_eename VARCHHAR2(110); v_ddeptnoo NUMBBER(5);v_job VVARCHAAR2(100); CCURSORR emp_cursoor IS SSELECTT empnno, enname FFROM eemp WWHEREdeptnno = vv_depttno ANND jobb = v_job;BEGINN v_ddeptnoo:=10; v_jjob:=CLERKK; OPEEN empp_curssor; LOOOP FEETCH eemp_cuursor INTO v_emppno,v_enamee; EXXIT WHHE

20、N emmp_currsor%NNOTFOUUND;DBMS_OUUTPUT.PUT_LLINE(vv_empnno|,|v_enamee); ENDD LOOPP;END;说明：该程程序与前一程程序实现相同同的功能。利用游标删除和和修改数据的的时候要注意意：UPDATE 表名 SEET WHEREE CURRRENT OOF 游标名名;DELETE 表名 WHHERE CCURRENNT OF 游标名;范例：定义游标标emp_ccur。通过过使用游标，根根据职务调整整雇员的工资资(yb9.sql)SET SERRVEROUUTPUT ONDECLAREE v_jobb emp.job%TTY

21、PE; CURSOOR empp_cur IS SELLECT jjob FROOM empp FOR UUPDATEE;BEGIN OPEN emp_ccur; LOOP FETTCH emmp_curr INTOO v_joob; EXIIT WHEEN empp_cur%NOTFOOUND; CASSE WWHEN vv_job=CLERRK THHEN updaate emmp sett sal=sal+550 wheere cuurrentt of eemp_cuur; WHENN v_joob=SAALESMAAN ORR v_joob=ANNALYSTT THEEN updaat

22、e emmp sett sal=sal+440 wheere cuurrentt of eemp_cuur; EELSE updaate emmp sett sal=sal+110 wheere cuurrentt of eemp_cuur;END CASSE; END LLOOP;COMMIT;END;/范例：用游标FFor循环，实现打印某一职务（输入一职务）的雇员的雇员编号和雇员姓名。（yb10.sql）可参考yb4.sqlDECLAREEv_job emp.jjob%TYYPE; CURSSOR emmp_currsor IIS SELEECT emmpno, enamee FROMM

23、emp WHEREE job=v_jobb;BEGINv_job:=&v_job;FOR emmp_reccord IIN empp_curssor LOOOP DBBMS_OUUTPUT.PUT_LLINE(EEmp_reecord.empnoo| Emmp_reccord.eename);END LOOOP;END;异常处理错误处理错误处理部部分位于程序序的可执行部部分之后，是是由WHENN语句引导的的多个分支构构成的。错误误处理的语法法如下：EXCEPPTIONWHEN 错误1ORR 错误2 TTHEN语句序列11；WHEN 错误3ORR 错误4 TTHEN语句序列22；WHEN OTT

24、HERS语句序列n；END; 其中：错误是在标标准包中由系系统预定义的的标准错误，或或是由用户在在程序的说明明部分自定义义的错误，参参见下一节系系统预定义的的错误类型。语句序列就就是不同分支支的错误处理理部分。凡是出现在WHHEN后面的的错误都是可可以捕捉到的的错误，其他他未被捕捉到到的错误，将将在WHENN OTHEERS部分进进行统一处理理，OTHEERS必须是EXCCEPTIOON部分的最最后一个错误误处理分支。如如要在该分支支中进一步判判断错误种类类，可以通过过使用预定义义函数SQLLCODE( )和SQLERRRM( )来获得系统统错误号和错错误信息。如果在程序序的子块中发发生了错误

25、，但但子块没有错错误处理部分分，则错误会会传递到主程程序中。下面是由于于查询编号错错误而引起系系统预定义异异常的例子。范例：查询编号号为12344的雇员名字字。SET SERRVEROUUTPUT ONDECLAREEv_name VARCHHAR2(110);BEGIN SELEECTenamee INTOOv_namme FROMMemp WHERREempnoo = 12234;DBMS_OUUTPUT.PUT_LLINE(该雇员名字字为：| v_naame);EXCEPTIION WHEN NO_DAATA_FOOUND TTHEN DBMMS_OUTTPUT.PPUT_LIINE(编

26、编号错误，没没有找到相应应雇员！); WHEN OTHERRS THEEN DBMMS_OUTTPUT.PPUT_LIINE(发发生其他错误误！);END;说明：在以上查查询中，因为为编号为12234的雇员员不存在，所所以将发生类类型为“NOO_DATAA_FOUNDD”的异常。“NNO_DATTA_FOUUND”是系系统预定义的的错误类型，EXCEPTION部分下的WHEN语句将捕捉到该异常，并执行相应代码部分。在本例中，输出用户自定义的错误信息“编号错误，没有找到相应雇员!”。如果发生其他类型的错误，将执行OTHERS条件下的代码部分，显示“发生其他错误!”范例：由程序代代码显示系统统错误

27、。SET SERRVEROUUTPUT ONDECLAREEv_temp NUMBEER(5):=1;BEGINv_temp:=v_teemp/0;EXCEPTIION WHEN OTHERRS THEENDBMS_OUUTPUT.PUT_LLINE(发生系统错错误！); DBMMS_OUTTPUT.PPUT_LIINE(错错误代码：| SQQLCODEE( ); DBMMS_OUTTPUT.PPUT_LIINE(错错误信息： |SQQLERRMM( );END;说明：程序运行行中发生除零零错误，由WWHEN OOTHERSS捕捉到，执执行用户自己己的输出语句句显示错误信信息，然后正正常结束。

28、在在错误处理部部分使用了预预定义函数SSQLCODDE( )和和SQLERRRM( )来进一步获获得错误的代代码和种类信信息。预定义错误（了了解）Oracle的的系统错误很很多，但只有有一部分常见见错误在标准准包中予以定定义。定义的的错误可以在在EXCEPPTION部部分通过标准准的错误名来来进行判断，并并进行异常处处理。常见的的系统预定义义异常如下表所示。比如，如果程序序向表的主键键列插入重复复值，则将发发生DUP_VAL_OON_INDDEX错误。如果一个系系统错误没有有在标准包中中定义，则需需要在说明部部分定义，语语法如下：错误名 EEXCEPTTION;定义后使用用PRAGMMA EX

29、CCEPTIOON_INIIT来将一个个定义的错误误同一个特别别的Oraccle错误代代码相关联，就就可以同系统统预定义的错错误一样使用用了。语法如如下：PRAGMMA EXCCEPTIOON_INIIT(错误名名，- 错误代码码)；范例：定义新的的系统错误类类型。SET SSERVERROUTPUUT ONDECLAAREV_ENAAME VAARCHARR2(10);NULL_INSERRT_ERRROR EXXCEPTIION;PRAGMMA EXCCEPTIOON_INIIT(NULLL_INSSERT_EERROR,-14000);BEGINNINSERRT INTTO EMPP(E

30、MPNNO) VAALUES(NULL);EXCEPTIIONWHEN NUULL_INNSERT_ERRORR THENN DBMMS_OUTTPUT.PPUT_LIINE(无无法插入NUULL值！); WHENN OTHEERS TTHEN DBMMS_OUTTPUT.PPUT_LIINE(发发生其他系统统错误！);END;执行结果为为：无法插入NNULL值！PL/SQQL 过程已已成功完成。说明：NNULL_IINSERTT_ERROOR是自定义义异常，同系系统错误14400相关联联。自定义异常程序设计者者可以利用引引发异常的机机制来进行程程序设计，自自己定义异常常类型。可以以在声明部分

31、分定义新的异异常类型，定定义的语法是是：错误名 EEXCEPTTION;用户定义的的错误不能由由系统来触发发，必须由程程序显式地触触发，触发的的语法是：RAISEE 错误名；RAISE也可可以用来引发发模拟系统错错误，比如，RAISE ZERO_DIVIDE将引发模拟的除零错误。使用RAIISE_APPPLICAATION_ERRORR函数也可以以引发异常。该该函数要传递递两个参数，第第一个是用户户自定义的错错误编号，第第二个参数是是用户自定义义的错误信息息。使用该函函数引发的异异常的编号应应该在20 000和20 9999之间选择择。自定义异常常处理错误的的方式同前。范例：插入新雇雇员，限定

32、插插入雇员的编编号在700008000之间间。SET SERRVEROUUTPUT ONDECLAREEnew_no NUMBEER(10);new_exccp1 EXXCEPTIION;new_exccp2 EXXCEPTIION;BEGINnew_no:=67899;INSERT INTOemp(eempno,enamee) VALUEES(neww_no, 小郑); IF neew_no8000 THEN RAIISE neew_exccp2; END IIF; COMMIIT;EXCEPTIIONWHEN neew_exccp1 TTHENROLLBACCK;DBMS_OUUTPUT.

33、PUT_LLINE(雇员编号小小于70000的下限！); WHEN new_eexcp2 THENN ROLLLBACKK; DBMMS_OUTTPUT.PPUT_LIINE(雇雇员编号超过过8000的上上限！);END;执行结果为：雇员编号小于于7000的的下限！PL/SQLL 过程已成成功完成。说明：在此例中中，自定义了了两个异常：new_eexcp1和和new_eexcp2，分分别代表编号号小于70000和编号大大于80000的错误。在在程序中通过过判断编号大大小，产生对对应的异常，并并在异常处理理部分回退插插入操作，然然后显示相应应的错误信息息。范例：使用RAAISE_AAPPLICC

34、ATIONN_ERROOR函数引发发系统异常。SET SERRVEROUUTPUT ONDECLAREENew_no NUMBEER(10);BEGIN New_nno:=67789; INSERTT INTOOemp(eempno,enamee) VALUEES(neww_no, JAMEES);IF new_no8000 THEN ROLLLBACKK; RAIISE_APPPLICAATION_ERRORR (-200002, 编号大于于8000的下下限！); END IIF;END;执行结果为：DECLAARE*ERRORR 位于第 1 行:ORA-220001: 编号小于于7000的

35、下下限！ORA-006512: 在line 9说明：在在本训练中，使使用RAISSE_APPPLICATTION_EERROR引引发自定义异异常，并以系系统错误的方方式进行显示示。错误编号号为200001和200022。注意：同上上一个训练比比较，此种方方法不需要事事先定义异常常，可直接引引发。 可以参考考下面的程序序片断将出错错信息记录到到表中，其中中，erroors为记录录错误信息的的表，SQLLCODE为为发生异常的的错误编号，SQLERRM为发生异常的错误信息。DECLAREE v_errror_coode NUUMBER; v_errror_meessagee VAARCHARR2(

36、2555);BEGIN.EXCEPTIION.WHEN OTTHERS THEN v_eerror_code := SQQLCODEE ; v_eerror_messaage := SQLEERRM ; INSSERT IINTO eerrorss VALLUES(vv_erroor_codde, v_errorr_messsage);END;练习：修改雇雇员的工资，通通过引发异常常控制修改范范围在60006000之间间。存储过程、函数数和包认识存储过程和和函数存储过程和函数数也是一种PPL/SQLL块，是存入入数据库的PPL/SQLL块。但存储储过程和函数数不同于已经经介绍过的PPL/SQL

37、L程序，我们们通常把PLL/SQL程程序称为无名名块，而存储储过程和函数数是以命名的的方式存储于于数据库中的的。和PL/SQL程序序相比，存储储过程有很多多优点，具体体归纳如下：存储过程和函数数以命名的数数据库对象形形式存储于数数据库当中。存存储在数据库库中的优点是是很明显的，因因为代码不保保存在本地，用用户可以在任任何客户机上上登录到数据据库，并调用用或修改代码码。存储过程和函数数可由数据库库提供安全保保证，要想使使用存储过程程和函数，需需要有存储过过程和函数的的所有者的授授权，只有被被授权的用户户或创建者本本身才能执行行存储过程或或调用函数。存储过程和函数数的信息是写写入数据字典典的，所以

38、存存储过程可以以看作是一个个公用模块，用用户编写的PPL/SQLL程序或其他他存储过程都都可以调用它它(但存储过程程和函数不能能调用PL/SQL程序序)。一个重复复使用的功能能，可以设计计成为存储过过程，比如：显示一张工工资统计表，可可以设计成为为存储过程；一个经常调调用的计算，可可以设计成为为存储函数；根据雇员编编号返回雇员员的姓名，可可以设计成存存储函数。像其他高级语言言的过程和函函数一样，可可以传递参数数给存储过程程或函数，参参数的传递也也有多种方式式。存储过程程可以有返回回值，也可以以没有返回值值，存储过程程的返回值必必须通过参数数带回；函数数有一定的数数据类型，像像其他的标准准函数一

39、样，我我们可以通过过对函数名的的调用返回函函数值。存储过程和函数数需要进行编编译，以排除除语法错误，只只有编译通过过才能调用。创建和删除存储储过程创建存储过过程，需要有有CREATTE PROOCEDURRE或CREATTE ANYY PROCCEDUREE的系统权限限。该权限可可由系统管理理员授予。创创建一个存储储过程的基本本语句如下：CREATTE ORR REPLLACE PROCEEDURE 存储过程名名(参数IN|OUT|IIN OUTT 数据类类型.)AS|IIS说明部分分BEGINN可执行部分分EXCEEPTIONN 错误处理理部分END 过程名;其中：可选关键字字OR REEP

40、LACEE 表示如果果存储过程已已经存在，则则用新的存储储过程覆盖，通通常用于存储储过程的重建建。参数部分用用于定义多个个参数(如果果没有参数，就就可以省略)。参数有三三种形式：IIN、OUT和IN OUUT。如果没没有指明参数数的形式，则则默认为INN。关键字ASS也可以写成成IS，后跟过过程的说明部部分，可以在在此定义过程程的局部变量量。编写存储过过程可以使用用任何文本编编辑器或直接接在SQL*Plus环环境下进行，编编写好的存储储过程必须要要在SQL*Plus环环境下进行编编译，生成编编译代码，原原代码和编译译代码在编译译过程中都会会被存入数据据库。编译成成功的存储过过程就可以在在Ora

41、clle环境下进进行调用了。一个存储过程在在不需要时可可以删除。删删除存储过程程的人是过程程的创建者或或者拥有DRROP ANNY PROOCEDURRE系统权限限的人。删除除存储过程的的语法如下：DROP PROCEEDURE 存储过程名名；如果要重新新编译一个存存储过程，则则只能是过程程的创建者或或者拥有ALLTER AANY PRROCEDUURE系统权权限的人。语语法如下：ALTERR PROCCEDUREE 存储过程程名 COMMPILE；执行(或调用)存储过程的的人是过程的的创建者或是是拥有EXEECUTE ANY PPROCEDDURE系统统权限的人或或是被拥有者者授予EXEEC

42、UTE权权限的人。执执行的方法如如下：方法1：EXECUUTE 模式式名.存储过程名名(参数.);方法2：BEGINN模式名.存存储过程名(参数.);END;传递的参数必须须与定义的参参数类型、个个数和顺序一一致(如果参数定定义了默认值值，则调用时时可以省略参参数)。参数可以以是变量、常常量或表达式式，用法参见见下一节。如果是调用用本账户下的的存储过程，则则模式名可以以省略。要调调用其他账户户编写的存储储过程，则模模式名必须要要添加。以下是一个个生成和调用用简单存储过过程的训练。注注意要事先授授予创建存储储过程的权限限。范例：创建一个个显示雇员总总人数的存储储过程。步骤1：登登录SCOTTT账

43、户。步骤2：在在SQL*PPlus输入入区中，输入入以下存储过过程：CREATE OR REEPLACEE PROCCEDUREE EMP_COUNTTASV_TOTALL NUMBBER(100);BEGIN SELECTT COUNNT(*) INTO V_TOTTAL FRROM EMMP; DBMS_OOUTPUTT.PUT_LINE(雇员总人人数为：|V_TOOTAL);END;步骤3：/执行行是进行编译译。如果存在错错误，就会显显示:警告: 创创建的过程带带有编译错误误。如果存在错错误，对脚本本进行修改，直直到没有错误误产生。如果要想查查看编译的错错误：show errrors p

44、roceedure emp_ccount;如果编译结结果正确，将将显示：过程已创建建。步骤4：调调用存储过程程，在输入区区中输入以下下语句并执行行：EXECUTEE EMP_COUNTT;显示结果为为：雇员总人数数为：14PL/SQQL 过程已已成功完成。说明：在该训练练中，V_TTOTAL变变量是存储过过程定义的局局部变量，用用于接收查询询到的雇员总总人数。注意：在SQLL*Pluss中输入存储储过程，按“执行”按钮是进行行编译，不是是执行存储过过程。如果在存存储过程中引引用了其他用用户的对象，比比如表，则必必须有其他用用户授予的对对象访问权限限。一个存储储过程一旦编编译成功，就就可以由其他

45、他用户或程序序来引用。但但存储过程或或函数的所有有者必须授予予其他用户执执行该过程的的权限。存储过程没没有参数，在在调用时，直直接写过程名名即可。注意：用EXEECUTE调调用存储过程程只有两种情情况：一种是存储过程程不带参数一种是存储过程程不带输出参参数，带输入入参数不是变变量带入是直直接带入范例：在PL/SQL程序序中调用存储储过程。步骤1：登登录SCOTTT账户。步骤2：授授权STUDDENT账户户使用该存储储过程，即在在SQL*PPlus输入入区中，输入入以下的命令令：GRANTT EXECCUTE OON EMPP_COUNNT TO STUDEENT授权成功。步骤3：登登录STUD

46、DENT账户户，在SQLL*Pluss输入区中输输入以下程序序：SET SSERVERROUTPUUT ONBEGINNSCOTTT.EMP_COUNTT;END;步骤4：执行以以上程序，结结果为：雇员总人数数为：14PL/SQQL 过程已已成功完成。 说明：在本例中中，存储过程程是由SCOOTT账户创创建的，STTUDEN账账户获得SCCOTT账户户的授权后，才才能调用该存存储过程。 注意：在程序序中调用存储储过程，使用用了第二种语语法。范例：编写显示示雇员信息的的存储过程EEMP_LIIST，并引引用EMP_COUNTT存储过程。步骤1：在在SQL*PPlus输入入区中输入并并编译以下存存

47、储过程：CREATTE OR REPLAACE PRROCEDUURE EMMP_LISSTAS CURRSOR eemp_cuursor IS SELLECT eempno,enamee FROMM emp;BEGINNFOR Empp_recoord INN emp_cursoor LOOOP DBMS_OUTPUUT.PUTT_LINEE(Emp_recorrd.emppno|EEmp_reecord.enamee); END LOOP; EMP_COUNTT;END;执行结果：过程已创建建。步骤2：调用存存储过程，在在输入区中输输入以下语句句并执行：EXECUTEE EMP_LIST说

48、明：以上的EEMP_LIIST存储过过程中定义并并使用了游标标，用来循环环显示所有雇雇员的信息。然然后调用已经经成功编译的的存储过程EEMP_COOUNT，用用来附加显示示雇员总人数数。通过EXXECUTEE命令来执行行EMP_LLIST存储储过程。练习：编写显示示部门信息的的存储过程DDEPT_LLIST，要要求统计出部部门个数。参数传递参数的作用用是向存储过过程传递数据据，或从存储储过程获得返返回结果。正正确的使用参参数可以大大大增加存储过过程的灵活性性和通用性。参数的类型型有三种，如如表8-1所所示。参数的定义形式式和作用如下下：参数名 IIN 数据类类型 DEFFAULT 值；定义一个

49、输输入参数变量量，用于传递递参数给存储储过程。在调调用存储过程程时，主程序序的实际参数数可以是常量量、有值变量量或表达式等等。DEFAAULT 关关键字为可选选项，用来设设定参数的默默认值。如果果在调用存储储过程时不指指明参数，则则参数变量取取默认值。在在存储过程中中，输入变量量接收主程序序传递的值，但但不能对其进进行赋值。参数名 OOUT 数据据类型；定义一个输输出参数变量量，用于从存存储过程获取取数据，即变变量从存储过过程中返回值值给主程序。在调用存储储过程时，主主程序的实际际参数只能是是一个变量，而而不能是常量量或表达式。在在存储过程中中，参数变量量只能被赋值值而不能将其其用于赋值，在在

50、存储过程中中必须给输出出变量至少赋赋值一次。参数名 IIN OUTT 数据类型型 DEFAAULT 值值；定义一个输输入、输出参参数变量，兼兼有以上两者者的功能。在在调用存储过过程时，主程程序的实际参参数只能是一一个变量，而而不能是常量量或表达式。DEFAULT 关键字为可选项，用来设定参数的默认值。在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。注意：如果省略IIN、OUT或IN OUUT，则默认认模式是INN。参数的定义义只能给出类类型，无需定定义长度。范例：编写给雇雇员增加工资资的存储过程程CHANGGE_SALLARY

51、，通通过IN类型的参参数传递要增增加工资的雇雇员编号和增增加的工资额额。步骤1：登登录SCOTTT账户。步骤2：在在SQL*PPlus输入入区中输入以以下存储过程程并执行：CREATTE OR REPLAACE PRROCEDUURE CHHANGE_SALARRY(P_EEMPNO IN NUUMBER DEFAUULT 77788,P_RAISEE NUMBBER DEEFAULTT 10)AS V_ENNAME VVARCHAAR2(100);V_SAL NNUMBERR(5);BEGINN SELEECT ENNAME,SSAL INNTO V_ENAMEE,V_SAAL FROOM

52、EMPP WHERRE EMPPNO=P_EMPNOO; UPDAATE EMMP SETT SAL=SAL+PP_RAISSE WHEERE EMMPNO=PP_EMPNNO; DBMSS_OUTPPUT.PUUT_LINNE(雇员员|V_ENAMEE|的工工资被改为|TO_CHAR(V_SALL+P_RAAISE); COMMMIT;EXCEPPTION WHENN OTHEERS THHEN DBMSS_OUTPPUT.PUUT_LINNE(发生生错误，修改改失败！); ROLLLBACK;END;执行结果为：过程已创建建。步骤3：调调用存储过程程，在输入区区中输入以下下语句并执行行：E

53、XECUTEE CHANNGE_SAALARY(7788,80)显示结果为为：雇员SCOOTT的工资资被改为30080 说明：从执执行结果可以以看到，雇员员SCOTTT的工资已由由原来的30000改为30800。参数的值由调用用者传递，传传递的参数的的个数、类型型和顺序应该该和定义的一一致。如果顺顺序不一致，可可以采用以下下调用方法。如如上例，执行行语句可以改改为：EXECUTTE CHAANGE_SSALARYY(P_RAAISE=80,P_EMPNOO=77888);可以看出出传递参数的的顺序发生了了变化，并且且明确指出了了参数名和要要传递的值，=运算符左侧是参数名，右侧是参数表达式，这种

54、赋值方法的意义较清楚。练习：创建插入入雇员的存储储过程INSSERT_EEMP，并将将雇员编号等等作为参数。在设计存储储过程的时候候，也可以为为参数设定默默认值，这样样调用者就可可以不传递或或少传递参数数了。练习：调用存储储过程CHAANGE_SSALARYY，不传递参参数，使用默默认参数值。在SQL*Plus输输入区中输入入以下命令并并执行：EXECUTEE CHANNGE_SAALARY显示结果为为：雇员SCOOTT的工资资被改为30090 说明：在存储过过程的调用中中没有传递参参数，而是采采用了默认值值7788和10，即默认认雇员号为77788，增增加的工资为为10。范例：使用OUUT类

55、型的参参数返回存储储过程的结果果。步骤1：登登录SCOTTT账户。步骤2：在在SQL*PPlus输入入区中输入并并编译以下存存储过程：CREATTE OR REPLAACE PRROCEDUURE EMMP_COUUNT(P_TOTALL OUT NUMBEER)ASBEGINNSELECCT COUUNT(*) INTOO P_TOOTAL FFROM EEMP;END;执行结果为：过程已创建建。步骤3：输输入以下程序序并执行：DECLAAREV_EMPPCOUNTT NUMBBER;BEGINNEMP_CCOUNT(V_EMPPCOUNTT);DBMS_OUTPUUT.PUTT_LINEE

56、(雇员总总人数为：|V_EEMPCOUUNT);END;显示结果为：雇员总人数数为：14PL/SQQL 过程已已成功完成。说明：在存储储过程中定义义了OUT类类型的参数PP_TOTAAL，在主程程序调用该存存储过程时，传传递了参数VV_EMPCCOUNT。在在存储过程中中的SELEECT.INTO.语句中中对P_TOOTAL进行行赋值，赋值值结果由V_EMPCOOUNT变量量带回给主程程序并显示。以上程序要覆盖盖同名的EMMP_COUUNT存储过过程，如果不不使用OR REPLAACE选项，就就会出现以下下错误：ERRORR 位于第 1 行:ORA-000955: 名称已由由现有对象使使用。练

57、习：创建存储储过程，使用用OUT类型参参数获得雇员员经理名。创建和删除存储储函数创建函数数，需要有CCREATEE PROCCEDUREE或CREATTE ANYY PROCCEDUREE的系统权限限。该权限可可由系统管理理员授予。创创建存储函数数的语法和创创建存储过程程的类似，即即CREATTE ORR REPLLACE FUNCTTION 函函数名(参数IN 数据类型型.) RETURRN 数据类类型AS|IIS说明部分分BEGIN可执行部分RETURN (表达式)EXCEPTTION 错误处理部部分END 函数数名;其中，参数数是可选的，但只能是IN类型(IN关键字可以省略)。在定义部分

58、分的RETUURN 数据据类型，用来来表示函数的的数据类型，也也就是返回值值的类型，此此部分不可省省略。在可执行部分的的RETURRN(表达式式)，用来生成成函数的返回回值，其表达达式的类型应应该和定义部部分说明的函函数返回值的的数据类型一一致。在函数数的执行部分分可以有多个个RETURRN语句，但但只有一个RRETURNN语句会被执执行，一旦执执行了RETTURN语句句，则函数结结束并返回调调用环境。一个存储函函数在不需要要时可以删除除，但删除的的人应是函数数的创建者或或者是拥有DDROP AANY PRROCEDUURE系统权权限的人。其其语法如下：DROP FUNCTTION 函函数名；

59、重新编译一个存存储函数时，编编译的人应是是函数的创建建者或者拥有有ALTERR ANY PROCEEDURE系系统权限的人人。重新编译译一个存储函函数的语法如如下：ALTERR PROCCEDUREE 函数名 COMPIILE；函数的调用用者应是函数数的创建者或或拥有EXEECUTE ANY PPROCEDDURE系统统权限的人，或或是被函数的的拥有者授予予了函数执行行权限的账户户。函数的引引用和存储过过程不同，函函数要出现在在程序体中，可可以参加表达达式的运算或或单独出现在在表达式中，其其形式如下：变量名:=函数名(.)范例：创建一个个通过雇员编编号返回雇员员名称的函数数GET_EEMP_N

60、AAME。步骤1：登登录SCOTTT账户。步骤2：在在SQL*PPlus输入入区中输入以以下存储函数数并编译：CREATTE OR REPLAACE FUUNCTIOON GETT_EMP_NAME(P_EMPPNO NUUMBER DEFAUULT 77788)RETURRN VARRCHAR22AS V_ENNAME VVARCHAAR2(100);BEGINN SELEECT ENNAME IINTO VV_ENAMME FROOM EMPP WHERRE EMPPNO=P_EMPNOO;RETURN(V_ENAAME);EXCEPTIION WHEEN NO_DATA_FOUNDD T