第07章：Oracle-PL／SQL语言基础-控制语句-过程-触发器-函数-游标.ppt

第07章：Oracle-PL／SQL语言基础-控制语句-过程-触发器-函数-游标.ppt

《第07章：Oracle-PL／SQL语言基础-控制语句-过程-触发器-函数-游标.ppt》由会员分享，可在线阅读，更多相关《第07章：Oracle-PL／SQL语言基础-控制语句-过程-触发器-函数-游标.ppt（90页珍藏版）》请在冰点文库上搜索。

数据库操作与管理语言OracleSQLOraclePL/SQL语言基础/过程/触发器,本章目标,PL/SQL语言基本结构PL/SQL变量和常量的声明PL/SQL程序的执行部分了解PL/SQL常用函数存储过程触发器自定义函数游标,PL/SQL简介,PL/SQL（ProceduralLanguage/SQL，过程语言/SQL）它是结合Oracle过程语言和结构化查询语言的一种扩展语言PL/SQL支持多种数据类型，可以使用条件语句和循环语句等控制结构PL/SQL可用于创建存储过程、触发器和程序包，也可以用来处理业务规则、数据库事件或给SQL命令的执行添加程序逻辑,PL/SQL的优点,支持SQL支持面向对象编程（OOP）更好的性能可移植性与SQL集成安全性,PL/SQL的基本结构-1,PL/SQL语言是程序化程序设计语言。

块（Block）是PL/SQL程序中最基本的结构，所有PL/SQL程序都是由块组成。

PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成：

DECLARE标记声明部分变量的声明，必须要在begin前面声明一些变量、常量、用户定义的数据类型及游标namevarchar（30）;-声明时不设置值namevarchar（30）:

=Jack;-声明带有默认值namepreson.name%type;-直接引用一个表的数据类型BEGIN标记主程序体部分开始主程序体，在这里可以加入各种合法语句EXCEPTION标记异常处理部分开始异常处理程序，当程序中出现错误时执行这一部分END标记主程序体结束部分,PL/SQL基本结构-2,declare说明部分（变量说明，光标申明，例外说明begin语句序列（DML语句exception例外处理语句End;/,PL/SQL字符集,PL/SQL语言有效字符包括以下三类所有大写和小写英文字母09的阿拉伯数字操作符，包括（、）、+、-、*、/、!

、=、%等PL/SQL标识符标识符的最大长度为30个字符，不区分大小写，但建议在标识符中适当使用大小写，以增加程序的可读性。

PL/SQL的运算符,PL/SQL语言的运算符算术运算符加（+）、减（-）、乘（*）、除（/）、乘方（*）和连接（|）关系运算符=、（或!

=）、=、=、BETWEEN.AND.、IN、LIKE、ISNULL逻辑运算符逻辑与（AND）、逻辑或（OR）、逻辑非（NOT）,PL/SQL常量和变量,在PL/SQL程序运行时，需要定义一些变量来存放一些数据。

常量和变量在使用前必须声明，可以使用DECLARE对变量进行声明，语法如下：

DECLARE:

=默认值;.在DECLARE块中可以同时声明多个常量和变量。

声明普通常量或变量是需要说明以下信息：

常量或变量的名称常量或变量的数据类型,变量说明,说明变量（char,varchar2,date,number,boolean,long）记录变量分量的引用：

emp_rec.ename:

=ADAMS;,说明变量名、数据类型和长度后用分号结束说明语句。

引用型变量，即my_name的类型与emp表中ename列的类型一样,记录型变量集,声明常量,声明常量的基本格式如下：

constant:

=;:

=为赋值语句关键字constant表示声明的是常量。

常量一旦定义，在以后的使用中其值不再改变。

一些固定的大小为了防止有人改变，最好定义成常量。

例如Pass_ScoreconstantINTEGER:

=60;,声明变量,声明变量的基本格式如下：

（宽度）:

=;变量声明是没有关键字，但要指定数据类型，宽度和初始值可以定义也可以不定义。

例如AddressVARCHAR2（30）:

=地址未知;PL/SQL对一个未初始化的变量，将被默认赋值为NULL例如AddressVARCHAR2（30）;,PLSQL案例-1：

-打开输出setseveroutputon-声明一个变量并输出declarenamevarchar（10）:

=HelloWorld;begindbms_output.put_line（name）;end;,案例-2：

例如SETSERVEROUTPUTON;DECLAREPass_ScoreconstantINTEGER:

=60;AddressVARCHAR2（30）:

=北京海淀区;BEGINDBMS_OUTPUT.PUT_LINE（Pass_Score）;DBMS_OUTPUT.PUT_LINE（Address）;END;使用SETSERVEROUTPUTON命令设置环境变量SERVEROUTPUT为打开状态，从而使PL/SQL程序能够在SQL*Plus中输出结果使用函数DBMS_OUTPUT.PUT_LINE（）可以输出参数的值,PL/SQL程序的执行部分,PL/SQL程序的执行部分包括赋值语句流程控制语句SQL语句游标语句,使用赋值语句,可以在声明变量时或处理变量时设置初始值。

也可以在程序的执行部分对变量进行赋值。

SETServerOutPutON;DECLARETrainNameVARCHAR2（30）;BEGINTrainName:

=OracleAdministration;-赋值一个新值Dbms_output.put_line（TrainName）;END;运行结果如下图所示,接收用户的输入赋值：

在declare之前，可以通过acceptsomeVarprompt提示信息;要求用户输入在后面的代码中，可以通过地址引用,从查询中结果中赋值：

用into关键字可以将查询结果的值，设置给变量：

setserveroutputondeclareidvarchar（30）;nmvarchar（30）;beginselectid,nameintoid,nmfrompersonwhereid=P001;dbms_output.put_line（编号|id|名称|nm）;-以下是异常处理-可选exceptionwhenNO_DATA_FOUNDthendbms_output.put_line（没有你要查询的数据）;whenothersthendbms_output.put_line（其他错误）;end;,流程控制语句,流程控制语句是所有过程性程序语言的关键PL/SQL的主要控制语句如下：

if.thenelsifthenendif;判断if正确则执行then，否则执行else（elsif为嵌套判断）注意elsif,里面少一下e.Casevarwhenthenwhenthenend有逻辑的从数值中做出选择Loopexitendloop循环控制，用判断语句执行exitLoopexitwhenendloop同上，当when为真时执行exitwhile.loopendloop当while为真时循环for.in.loopendloop已知循环次数的循环,条件语句IF-1,IF语句是根据条件表达式的值决定执行相应的程序段。

语法结构如下：

IFTHEN.ELSIFTHEN.ELSEENDIF;其中ELSIF子句是可选项。

注意是ELSIF而不是ELSEIF,条件语句IF-2,程序中说明一个整型变量Number，使用IF语句判断Number变量是正数、负数或0。

SETServerOutPutON;DECLARENumberINTEGER:

=-10;BeginIFNumber0THENdbms_output.put_line（正数）;ELSEdbms_output.put_line（0）;ENDIF;End;执行效果如右图所示：

分支语句CASE-1,分支语句是对指定的变量进行判断，从指定的列表中选择满足条件的行，并把该行的值作为CASE语句的结果返回。

CASE语句的语法结构如下：

CASEWHENTHEN值1WHENTHEN值2.WHENTHEN值nELSE值n+1END;,分支语句CASE-2,声明一个整型变量varDAY和一个字符型变量Result。

使用CASE语句判断varDAY是星期几。

如果变量varDAY在17之间，则能够显示相应的星期信息，否则返回提示信息“数据越界”；SETServerOutPutON;DECLAREvarDAYINTEGER:

=3;ResultVARCHAR2（20）;BEGINResult:

=CasevarDAYWHEN1THEN星期一WHEN2THEN星期二WHEN3THEN星期三WHEN4THEN星期四WHEN5THEN星期五WHEN6THEN星期六WHEN7THEN星期日ELSE数据越界END;dbms_output.put_line（Result）;END;,案例-1：

使用简单的casewhenelseend语句：

declareiint:

=0;begini:

=（casewhen1=1then111else222end）;dbms_output.put_line（i）;end;,在查询时使用简单的Case：

在查询中使用case语句：

SQLselect（caseidwhen2then222else33end）fromt6;SQL-上面的示例等于SQLselect（casewhenid=2then2222else333end）fromt6;,循环语句LOOP.EXIT.END-1,此语句的功能是重复执行循环体中的程序块，直到执行EXIT语句，则退出循环。

LOOP.EXIT.END语句的语法结构如下LOOPIFTHENEXITENDIFENDLOOP;,LOOP.EXIT.ENDLOOP-2,计算14累加SETServerOutPutON;DECLAREvarNumINTEGER:

=1;varSumINTEGER:

=0;BEGINLOOPvarSum:

=varSum+varNum;dbms_output.put_line（varNum）;IFvarNum=4THENEXIT;ENDIF;dbms_output.put_line（+）;varNum:

=varNum+1;ENDLOOP;dbms_output.put_line（=）;dbms_output.put_line（varSum）;END;,LOOP.EXITWHEN.ENDLOOP-1,此循环语句的功能是重复执行循环体中的程序块，直到满足EXITWHEN后面的判断语句，则退出循环。

LOOP.EXITWHEN.END语句的语法结构如下：

LOOPEXITWHENENDLOOP;,LOOP.EXITWHEN.ENDLOOP-2,重新实现14累加SETServerOutPutON;DECLAREvarNumINTEGER:

=1;varSumINTEGER:

=0;BEGINLOOPvarSum:

=varSum+varNum;dbms_output.put_line（varNum）;EXITWHENvarNum=4;dbms_output.put_line（+）;varNum:

=varNum+1;ENDLOOP;dbms_output.put_line（=）;dbms_output.put_line（varSum）;END;,WHILE.LOOP.ENDLOOP-1,此语句的功能是当WHILE后面的语句条件成立时，重复执行循环体中的程序块。

WHILE.LOOP.ENDLOOP语句语法结构如下：

WHILELOOPENDLOOP;,WHILE.LOOP.ENDLOOP-2,再次实现14累加SETServerOutPutON;DECLAREvarNumINTEGER:

=1;varSumINTEGER:

=0;BEGINWHILEvarNum=4LOOPvarSum:

=varSum+varNum;dbms_output.put_line（varNum）;IFvarNum4THENdbms_output.put_line（+）;ENDIF;varNum:

=varNum+1;ENDLOOP;dbms_output.put_line（=）;dbms_output.put_line（varSum）;END;,FOR.IN.LOOP.ENDLOOP-1,此语句定义一个循环变量，并指定循环变量的初始值和终止值。

每循环一次循环变量自动加1.FOR.IN.LOOP.ENDLOOP语句的语法如下FORIN.LOOPENDLOOP;,FOR.IN.LOOP.ENDLOOP-2,再次实现14累加SETServerOutPutON;DECLAREvarNumINTEGER:

=1;varSumINTEGER:

=0;BEGINFORvarNumIN1.4LOOPvarSum:

=varSum+varNum;dbms_output.put_line（varNum）;IFvarNum4THENdbms_output.put_line（+）;ENDIF;ENDLOOP;dbms_output.put_line（=）;dbms_output.put_line（varSum）;END;,FOR.IN.LOOP3:

-forinloop使用变量setserveroutputondeclareiinteger:

=1;jinteger:

=10;xinteger:

=0;beginforxini.jloop/i和j都是变量dbms_output.put_line（x）;endloop;end;,异常处理,PL/SQL程序在运行过程中，可能会出现错误或异常现象例如：

无法建立到Oracle的连接或用0做除数。

好的程序应该对可能发生的异常情况进行处理，异常处理代码在EXCEPTION块中实现可以使用WHEN语句来定义异常。

WHEN语句的使用方法如下：

EXCEPTIONWHENTHENWHENTHEN.WHENOTHERSTHEN,预定义异常种类,示例1：

向一个NUMBER类型的变量赋值字符串时，导致异常的发生SETServerOutPutON;DECLAREvarNumNUMBER;BEGINvarNum:

=abc;EXCEPTIONWHENVALUE_ERRORTHENdbms_output.put_line（VALUE_ERROR）;END;,示例2：

使用SQLCODE,SQLERRM输出错误信息：

setserveroutputon;declarejinteger:

=0;beginj:

=Jack;-如果出错，直接去异常处执行，以下行不会输出dbms_output.put_line（Valueissetted）;exceptionwhenothersthen-在异常中默认使用sqlerrm输出信息dbms_output.put_line（SQLCODE|Othererrors|sqlerrm）;end;,用户自定义异常：

当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。

用户定义的异常错误是通过显式使用RAISE语句来触发。

当引发一个异常错误时，控制就转向到EXCEPTION块异常错误部分，执行错误处理代码。

对于这类异常情况的处理，步骤如下：

在PL/SQL块的定义部分定义异常情况：

exception;RAISE；在PL/SQL块的异常情况处理部分对异常情况做出相应的处理。

自定义异常示例：

setserveroutputon;declaremyExceptionEXCEPTION;-定义异常beginupdatepersonsetid=900whereid=901;ifSQL%NOTFOUNDthenRAISEmyException;endif;EXCEPTIONwhenmyExceptionthendbms_output.put_line（SQLCODE|Error|SQLERRM）;end;,RAISE_APPLICATION_ERROR过程:

RAISE_APPLICATION_ERROR过程可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。

语法如下：

RAISE_APPLICATION_ERROR（error_number,error_message,keep_errors）;这里的error_number是从20,000到20,999之间的参数。

error_message是相应的提示信息（2048字节）.keep_errors为可选，如果keep_errors=TRUE,则新错误将被添加到已经引发的错误列表中。

如果keep_errors=FALSE（缺省）,则新错误将替换当前的错误列表。

RAISE_APPLICATION_ERROR示例：

RAISE_APPLICATION_ERRROR示例：

setserveroutputon;declaremyExceptionEXCEPTION;beginupdatepersonsetid=900whereid=901;ifSQL%NOTFOUNDthen-调用系统的异常处理，抛出异常RAISE_APPLICATION_ERROR（-20001,cannotfoundanyrow!

）;endif;EXCEPTIONwhenmyExceptionthendbms_output.put_line（usererrors）;whenothersthendbms_output.put_line（Others|SQLCODE|SQLERRM）;end;,用异常在PLSQL块中管理事务,setserveroutputon;begininsertintopersonvalues（P007,Tom1）;insertintopersonvalues（P006,Tom4）;commit;-提交exceptionwhenothersthenrollback;-回滚dbms_output.put_line（SQLCODE|SQLERRM）;end;,小结,PL/SQL语言基本结构变量和常量的声明流程控制语句,存储过程函数触发器游标,存储过程,存储过程,指存储在数据库中供所有用户程序调用的子程序叫存储过程。

创建存储过程用CREATEPROCEDURE命令建立存储过程和存储函数语法：

createorreplacePROCEDURE过程名（参数列表）ASPLSQL子程序体存储过程中可以接收的参数类型：

in类型为输入类型的参数out类型为输出类型的参数。

存储过程调用,setserveroutputonbeginraisesalary（7369）;end;/,setserveroutputonexecraisesalary（7369）;,方法一：

方法二：

不接收参数的过程,createorreplaceprocedureP1isbegindbms_output.put_line（Currentdateis:

|to_char（sysdate,yyyy-mm-dd）;end;-调用方法1setserveroutputon;execP1（）;-调用方法2setserveroutputon;beginp1（）;end;,接收输入类型的参数：

-声明接收参数的只声明类型，不声明大小createorreplaceprocedurepro2（p_idinvarchar2,p_nameinvarchar2）asbegininsertintopersonvalues（p_id,p_name）;end;-调用setserveroutputon;execpro2（P100,Marray）;,接收输出类型的参数：

-select*fromall_objectswhereowner=HR;createorreplaceprocedurepro4（pidinvarchar,pnminvarchar,rcountoutnumber）asrcnumber:

=0;begin-先写入数据insertintopersonvalues（pid,pnm）;commit;selectcount

（1）intorcfromperson;rcount:

=rc;end;-测试调用setserveroutputon;declarercnumber:

=0;begin-第三个参数为返回值的参数pro4（P210,Jack,rc）;dbms_output.put_line（rc）;end;,在过程中使用游标,createorreplaceprocedurepro5ascursorc1isselect*fromperson;v_pperson%rowType;beginopenc1;loopfetchc1intov_p;exitwhenc1%notfound;dbms_output.put_line（v_p.id|v_p.name）;endloop;ifc1%isopenthenclosec1;endif;end;-调用setserveroutputon;execpro5;,函数,存储函数,函数（Function）为一命名的存储程序，可带参数，并返回一计算值。

函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。

函数说明要指定函数名、结果值的类型，以及参数类型等。

建立存储函数的语法：

CREATEORREPLACEFUNCTION函数名（参数列表）RETURN函数值类型ASPLSQL子程序体；,函数示例：

createorreplacefunctionf1returnvarchar2asbeginreturnhello;end;-调用selectf1（）fromdual;,函数的调用,declarev_salnumber;beginv_sal:

=queryEmpSalary（7934）;dbms_output.put_line（salaryis:

|v_sal）;end;或是直接使用select调用,begindbms_output.put_line（salaryis:

|queryEmpSalary（7934）;end;,过程和函数中的in和out,一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。

In类型的参数，只可以接收值，不能再给in类型的参数设置新的值。

但过程和函数都可以通过out指定一个或多个输出参数。

我们可以利用out参数，在过程和函数中实现返回多个。

什么时候用存储过程/存储函数？

原则：

如果只有一个返回值，用存储函数；否则，就用存储过程。

函数示例2：

用函数去掉字段中多个重复的空格：

createorreplacefunctionmtrim（strvarchar2）returnvarchar2asv_strvarchar2（500）:

=;beginselectregexp_replace（str,（s）1,）intov_strfromdual;returnv_str;end;-测试selectmtrim（HA）fromdual;,触发器/Trigger,触发器,数据库触发器是一个与表相关联的、存储的PL/SQL程序。

每当一个特定的数据操作语句（Insert,update,d