第07章OraclePLSQL语言基础_控制语句_过程_触发器

数据库操作与管理语言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基本结构-2declare说明部分（变量说明，光标申明，例外说明〕begin语句序列（DML语句〕…exception例外处理语句End;/PL/SQL字符集PL/SQL语言有效字符包括以下三类所有大写和小写英文字母0~9的阿拉伯数字操作符，包括(、)、+、-、*、/、、、!、=、@、%等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-声明一个变量并输出declarenamevarchar(10):=‘HelloWorld';begindbms_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‘提示信息’;要求用户输入在后面的代码中，可以通过地址引用&someVal接收这值--接收用户的输入setserveroutputon--要求用户输入一个串,如果不输入后面也可以直接引用，只是一个提示而已acceptageprompt'plzenteranumber:';declarennnumber;beginnn:=&age;dbms_output.put_line('你输入的信息是'||nn);end;从查询中结果中赋值：用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...thenelsif…thenendif;判断if正确则执行then，否则执行else(elsif为嵌套判断)注意elsif,里面少一下e.Casevarwhen…thenwhen…thenend有逻辑的从数值中做出选择Loopexitendloop循环控制，用判断语句执行exitLoopexitwhen…endloop同上，当when为真时执行exitwhile..loopendloop当while为真时循环for...in...loopendloop已知循环次数的循环条件语句IF-1IF语句是根据条件表达式的值决定执行相应的程序段。语法结构如下：IF条件表达式THEN执行语句...执行语句n[ELSIF条件表达式THEN执行语句...执行语句n...ELSE执行语句]ENDIF;其中ELSIF子句是可选项。注意是ELSIF而不是ELSEIF条件语句IF-2程序中说明一个整型变量Number，使用IF语句判断Number变量是正数、负数或0。SETServerOutPutON;DECLARENumberINTEGER:=-10;BeginIFNumber0THENdbms_output.put_line('负数');ELSIFNumber0THENdbms_output.put_line('正数');ELSEdbms_output.put_line('0');ENDIF;End;执行效果如右图所示：分支语句CASE-1分支语句是对指定的变量进行判断，从指定的列表中选择满足条件的行，并把该行的值作为CASE语句的结果返回。CASE语句的语法结构如下：CASE变量WHEN表达式1THEN值1WHEN表达式2THEN值2......WHEN表达式nTHEN值nELSE值n+1END;分支语句CASE-2声明一个整型变量varDAY和一个字符型变量Result。使用CASE语句判断varDAY是星期几。如果变量varDAY在1~7之间，则能够显示相应的星期信息，否则返回提示信息“数据越界”；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(caseidwhen2then'222'else'33'end)fromt6;SQL--上面的示例等于SQLselect(casewhenid=2then'2222'else'333'end)fromt6;循环语句LOOP..EXIT..END-1此语句的功能是重复执行循环体中的程序块，直到执行EXIT语句，则退出循环。LOOP...EXIT...END语句的语法结构如下LOOP程序块1IF条件表达式THENEXITENDIF程序块2ENDLOOP;LOOP..EXIT..ENDLOOP-2计算1~4累加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语句的语法结构如下：LOOP程序块1EXITWHEN条件表达式程序块2ENDLOOP;LOOP...EXITWHEN...ENDLOOP-2重新实现1~4累加SETServerOutPutON;DECLAREvarNumINTEGER:=1;varSumINTEGER:=0;BEGINLOOPvarSum