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

数据库操作与管理语言OracleSQL

OraclePL/SQL语言基础/过程/触发器数据库操作与管理语言OracleSQL

Oracle本章目标PL/SQL语言基本结构PL/SQL变量和常量的声明PL/SQL程序的执行部分了解PL/SQL常用函数存储过程触发器自定义函数游标本章目标PL/SQL语言基本结构PL/SQL简介PL/SQL(ProceduralLanguage/SQL，过程语言/SQL)它是结合Oracle过程语言和结构化查询语言的一种扩展语言PL/SQL支持多种数据类型，可以使用条件语句和循环语句等控制结构PL/SQL可用于创建存储过程、触发器和程序包，也可以用来处理业务规则、数据库事件或给SQL命令的执行添加程序逻辑PL/SQL简介PL/SQL(ProceduralLangPL/SQL的优点支持SQL支持面向对象编程(OOP)更好的性能可移植性与SQL集成安全性PL/SQL的优点支持SQLPL/SQL的基本结构-1PL/SQL语言是程序化程序设计语言。块(Block)是PL/SQL程序中最基本的结构，所有PL/SQL程序都是由块组成。PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成：DECLARE标记声明部分变量的声明，必须要在begin前面声明一些变量、常量、用户定义的数据类型及游标namevarchar(30);--声明时不设置值namevarchar(30):=‘Jack’;--声明带有默认值%type;--直接引用一个表的数据类型BEGIN标记主程序体部分开始主程序体，在这里可以加入各种合法语句EXCEPTION标记异常处理部分开始异常处理程序，当程序中出现错误时执行这一部分END标记主程序体结束部分PL/SQL的基本结构-1PL/SQL语言是程序化程序设计语PL/SQL基本结构-2declare

说明部分（变量说明，光标申明，例外说明〕begin语句序列（DML语句〕…exception例外处理语句End;/PL/SQL基本结构-2declarePL/SQL字符集PL/SQL语言有效字符包括以下三类所有大写和小写英文字母0~9的阿拉伯数字操作符，包括(、)、+、-、*、/、<、>、!、=、@、%等PL/SQL标识符标识符的最大长度为30个字符，不区分大小写，但建议在标识符中适当使用大小写，以增加程序的可读性。PL/SQL字符集PL/SQL语言有效字符包括以下三类PL/SQL的运算符PL/SQL语言的运算符算术运算符加(+)、减(-)、乘(*)、除(/)、乘方(**)和连接(||)关系运算符=、<>(或!=)、<、>、>=、<=、BETWEEN...AND...、IN、LIKE、ISNULL逻辑运算符逻辑与(AND)、逻辑或(OR)、逻辑非(NOT)PL/SQL的运算符PL/SQL语言的运算符PL/SQL常量和变量在PL/SQL程序运行时，需要定义一些变量来存放一些数据。常量和变量在使用前必须声明，可以使用DECLARE对变量进行声明，语法如下：DECLARE <变量名><变量类型>:=默认值; ….在DECLARE块中可以同时声明多个常量和变量。声明普通常量或变量是需要说明以下信息：常量或变量的名称常量或变量的数据类型PL/SQL常量和变量在PL/SQL程序运行时，需要定义一些变量说明说明变量（char,varchar2,date,number,boolean,long)记录变量分量的引用：emp_rec.ename:='ADAMS';说明变量名、数据类型和长度后用分号结束说明语句。引用型变量，即my_name的类型与emp表中ename列的类型一样记录型变量集变量说明说明变量（char,varchar2,dat声明常量声明常量的基本格式如下：<常量名>constant<数据类型>:=<值>;:=为赋值语句关键字constant表示声明的是常量。常量一旦定义，在以后的使用中其值不再改变。一些固定的大小为了防止有人改变，最好定义成常量。例如Pass_ScoreconstantINTEGER:=60;声明常量声明常量的基本格式如下：声明变量声明变量的基本格式如下：<变量名><数据类型>[(宽度):=<初始值>];变量声明是没有关键字，但要指定数据类型，宽度和初始值可以定义也可以不定义。例如AddressVARCHAR2(30):=‘地址未知’;PL/SQL对一个未初始化的变量，将被默认赋值为NULL例如AddressVARCHAR2(30);声明变量声明变量的基本格式如下：PLSQL案例-1：--打开输出setseveroutputon-声明一个变量并输出declare namevarchar(10):=‘HelloWorld';begindbms_output.put_line(name);end;PLSQL案例-1：--打开输出案例-2：例如SETSERVEROUTPUTON;DECLARE

Pass_ScoreconstantINTEGER:=60;AddressVARCHAR2(30):='北京海淀区';BEGIN DBMS_OUTPUT.PUT_LINE(Pass_Score); DBMS_OUTPUT.PUT_LINE(Address);END;使用SETSERVEROUTPUTON命令设置环境变量SERVEROUTPUT为打开状态，从而使PL/SQL程序能够在SQL*Plus中输出结果使用函数DBMS_OUTPUT.PUT_LINE()可以输出参数的值案例-2：例如PL/SQL程序的执行部分PL/SQL程序的执行部分包括赋值语句流程控制语句SQL语句游标语句PL/SQL程序的执行部分PL/SQL程序的执行部分包括使用赋值语句可以在声明变量时或处理变量时设置初始值。也可以在程序的执行部分对变量进行赋值。SETServerOutPutON;DECLARE TrainNameVARCHAR2(30);BEGIN TrainName:=‘OracleAdministration’;--赋值一个新值 Dbms_output.put_line(TrainName);END;运行结果如下图所示使用赋值语句可以在声明变量时或处理变量时设置初始值。也可以在接收用户的输入赋值：在declare之前，可以通过acceptsomeVarprompt‘提示信息’;要求用户输入在后面的代码中，可以通过地址引用&someVal接收这值--接收用户的输入setserveroutputon--要求用户输入一个串,如果不输入后面也可以直接引用，只是一个提示而已

acceptageprompt'plzenteranumber:'; declare nnnumber; begin

nn:=&age; dbms_output.put_line('你输入的信息是'||nn); end;接收用户的输入赋值：在declare之前，可以通过从查询中结果中赋值：用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;从查询中结果中赋值：用into关键字可以将查询结果的值，设置流程控制语句流程控制语句是所有过程性程序语言的关键PL/SQL的主要控制语句如下：if...thenelsif…thenendif;判断if正确则执行then，否则执行else(elsif为嵌套判断)注意elsif,里面少一下e.Casevarwhen…thenwhen…thenend有逻辑的从数值中做出选择Loopexitendloop循环控制，用判断语句执行exitLoopexitwhen…endloop同上，当when为真时执行exitwhile..loopendloop当while为真时循环for...in...loopendloop已知循环次数的循环流程控制语句流程控制语句是所有过程性程序语言的关键条件语句IF-1IF语句是根据条件表达式的值决定执行相应的程序段。语法结构如下：IF<条件表达式>THEN<执行语句>...<执行语句n>[ELSIF<条件表达式>THEN<执行语句>...<执行语句n>...ELSE<执行语句>]ENDIF;其中ELSIF子句是可选项。注意是ELSIF而不是ELSEIF条件语句IF-1IF语句是根据条件表达式的值决定执行相应的程条件语句IF-2程序中说明一个整型变量Number，使用IF语句判断Number变量是正数、负数或0。SETServerOutPutON;DECLARENumberINTEGER:=-10;Begin

IFNumber<0THENdbms_output.put_line('负数');

ELSIFNumber>0THENdbms_output.put_line('正数');

ELSEdbms_output.put_line('0');

ENDIF;End;执行效果如右图所示：条件语句IF-2程序中说明一个整型变量Number，使用IF分支语句CASE-1分支语句是对指定的变量进行判断，从指定的列表中选择满足条件的行，并把该行的值作为CASE语句的结果返回。CASE语句的语法结构如下：CASE<变量>WHEN<表达式1>THEN值1WHEN<表达式2>THEN值2......WHEN<表达式n>THEN值nELSE值n+1END;分支语句CASE-1分支语句是对指定的变量进行判断，从指定的分支语句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;分支语句CASE-2声明一个整型变量varDAY和一个字符型案例-1：使用简单的casewhenelseend语句：declareiint:=0;begini:=(casewhen1=1then111else222end);dbms_output.put_line(i);end;案例-1：使用简单的casewhenelseend语句在查询时使用简单的Case：在查询中使用case语句：SQL>select(caseidwhen2then'222'else'33'end)fromt6;SQL>--上面的示例等于SQL>select(casewhenid=2then'2222'else'333'end)fromt6;在查询时使用简单的Case：在查询中使用case语句：SQL循环语句LOOP..EXIT..END-1此语句的功能是重复执行循环体中的程序块，直到执行EXIT语句，则退出循环。LOOP...EXIT...END语句的语法结构如下LOOP<程序块1>IF<条件表达式>THENEXITENDIF<程序块2>ENDLOOP;循环语句LOOP..EXIT..END-1此语句的功能是重复LOOP..EXIT..ENDLOOP-2计算1~4累加SETServerOutPutON;DECLAREvarNumINTEGER:=1;varSumINTEGER:=0;BEGIN

LOOPvarSum:=varSum+varNum;dbms_output.put_line(varNum);IFvarNum=4THEN

EXIT;ENDIF;dbms_output.put_line('+');varNum:=varNum+1;

ENDLOOP;dbms_output.put_line('=');dbms_output.put_line(varSum);END;LOOP..EXIT..ENDLOOP-2计算1~4累加LOOP...EXITWHEN...ENDLOOP-1此循环语句的功能是重复执行循环体中的程序块，直到满足EXITWHEN后面的判断语句，则退出循环。LOOP...EXITWHEN...END语句的语法结构如下：LOOP<程序块1>EXITWHEN<条件表达式><程序块2>ENDLOOP;LOOP...EXITWHEN...ENDLOOP-1此LOOP...EXITWHEN...ENDLOOP-2重新实现1~4累加SETServerOutPutON;DECLAREvarNumINTEGER:=1;varSumINTEGER:=0;BEGIN

LOOPvarSum:=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;LOOP...EXITWHEN...ENDLOOP-2重WHILE..LOOP..ENDLOOP-1此语句的功能是当WHILE后面的语句条件成立时，重复执行循环体中的程序块。WHILE...LOOP...ENDLOOP语句语法结构如下：WHILE<条件表达式>LOOP<程序块>ENDLOOP;WHILE..LOOP..ENDLOOP-1此语句的功能是WHILE..LOOP..ENDLOOP-2再次实现1~4累加SETServerOutPutON;DECLAREvarNumINTEGER:=1;varSumINTEGER:=0;BEGINWHILEvarNum<=4LOOPvarSum:=varSum+varNum;dbms_output.put_line(varNum);IFvarNum<4THENdbms_output.put_line('+');ENDIF;varNum:=varNum+1;

ENDLOOP;dbms_output.put_line('=');dbms_output.put_line(varSum);END;WHILE..LOOP..ENDLOOP-2再次实现1~4FOR..IN..LOOP..ENDLOOP-1此语句定义一个循环变量，并指定循环变量的初始值和终止值。每循环一次循环变量自动加1.FOR...IN...LOOP...ENDLOOP语句的语法如下FOR<循环变量>IN<初始值>..<终止值>LOOP<程序块>ENDLOOP;FOR..IN..LOOP..ENDLOOP-1此语句定义FOR..IN..LOOP..ENDLOOP-2再次实现1~4累加SETServerOutPutON;DECLAREvarNumINTEGER:=1;varSumINTEGER:=0;BEGINFORvarNumIN1..4LOOPvarSum:=varSum+varNum;dbms_output.put_line(varNum);IFvarNum<4THENdbms_output.put_line('+');ENDIF;ENDLOOP;dbms_output.put_line('=');dbms_output.put_line(varSum);END;FOR..IN..LOOP..ENDLOOP-2再次实现1FOR..IN..LOOP–3:--forinloop使用变量setserveroutputondeclareiinteger:=1;jinteger:=10;xinteger:=0;beginforxini..jloop//i和j都是变量dbms_output.put_line(x);endloop;end;FOR..IN..LOOP–3:--forinloo异常处理PL/SQL程序在运行过程中，可能会出现错误或异常现象例如：无法建立到Oracle的连接或用0做除数。好的程序应该对可能发生的异常情况进行处理，异常处理代码在EXCEPTION块中实现可以使用WHEN语句来定义异常。WHEN语句的使用方法如下：EXCEPTIONWHEN<异常情况名>THEN<异常处理代码>WHEN<异常情况名>THEN<异常处理代码>...WHENOTHERSTHEN<异常处理代码>异常处理PL/SQL程序在运行过程中，可能会出现错误或异常现预定义异常种类异常说明ACCESS_INTO_NULL在未初始化对象时出现CASE_NOT_FOUNF在CASE语句中的选项与用户输入的数据不匹配时出现COLLECTION_IS_NULL在给尚未初始化的表或数组赋值时出现CURSOR_ALREADY_OPEN用户试图重新打开已经打开的游标时出现。在重新打开游标前必须先将其关闭DUP_VAL_ON_INDEX用户试图将重复的值存储在使用唯一索引的数据库列中时出现INVALID_CURSOR在执行非法游标运算(如fetch一个尚未打开的游标)时出现INVALID_NUMBER将字符串转换为数字时出现LOGIN_DENIED输入的用户名或密码无效时出现NO_DATA_FOUND在表中不存在请求的行时出现SOTRAGE_ERROR在内存损坏或PL/SQL耗尽内存时出现TOO_MANY_ROWS在执行SELECTINTO语句后返回多行时出现VALUE_ERROR在产生大小限制错误时出现。如，变量中的列值超出变量的大小ZERO_DIVIDE以零做除数时出现预定义异常种类异常说明ACCESS_INTO_NULL在未初示例1：向一个NUMBER类型的变量赋值字符串时，导致异常的发生SETServerOutPutON;DECLAREvarNumNUMBER;BEGINvarNum:='abc';EXCEPTION

WHENVALUE_ERRORTHENdbms_output.put_line('VALUE_ERROR');END;示例1：向一个NUMBER类型的变量赋值字符串时，导致异常的示例2：使用SQLCODE,SQLERRM输出错误信息：setserveroutputon;declarejinteger:=0;beginj:='Jack';--如果出错，直接去异常处执行，以下行不会输出dbms_output.put_line('Valueissetted');exceptionwhenothersthen--在异常中默认使用sqlerrm输出信息dbms_output.put_line(SQLCODE||'Othererrors'||sqlerrm);end;示例2：使用SQLCODE,SQLERRM输出错误信息：用户自定义异常：当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用RAISE语句来触发。当引发一个异常错误时，控制就转向到EXCEPTION块异常错误部分，执行错误处理代码。对于这类异常情况的处理，步骤如下：在PL/SQL块的定义部分定义异常情况：<异常名称>exception;RAISE<异常名称>；在PL/SQL块的异常情况处理部分对异常情况做出相应的处理。用户自定义异常：当与一个异常错误相关的错误出现时，就会隐含触自定义异常示例：setserveroutputon;declare

myExceptionEXCEPTION;--定义异常beginupdatepersonsetid='900'whereid='901';ifSQL%NOTFOUNDthen

RAISEmyException;endif;EXCEPTIONwhenmyExceptionthendbms_output.put_line(SQLCODE||'Error'||SQLERRM);end;自定义异常示例：setserveroutputon;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过程:RAIRAISE_APPLICATION_ERRROR示例：RAISE_APPLICATION_ERRROR示例：setserveroutputon;declaremyExceptionEXCEPTION;beginupdatepersonsetid='900'whereid='901';ifSQL%NOTFOUNDthen--调用系统的异常处理，抛出异常

RAISE_APPLICATION_ERROR(-20001,'cannotfoundanyrow!');endif;EXCEPTIONwhenmyExceptionthendbms_output.put_line('usererrors');whenothersthendbms_output.put_line('Others'||SQLCODE||''||SQLERRM);end;RAISE_APPLICATION_ERRROR示例：RAI用异常在PLSQL块中管理事务setserveroutputon;begininsertintopersonvalues('P007','Tom1');insertintopersonvalues('P006','Tom4');commit;--提交exceptionwhenothersthenrollback;--回滚dbms_output.put_line(SQLCODE||SQLERRM);end;用异常在PLSQL块中管理事务setserveroutpu小结PL/SQL语言基本结构变量和常量的声明流程控制语句小结PL/SQL语言基本结构存储过程\函数\触发器\游标存储过程\函数\触发器\游标存储过程存储过程存储过程指存储在数据库中供所有用户程序调用的子程序叫存储过程。创建存储过程用CREATEPROCEDURE命令建立存储过程和存储函数语法： create[orreplace]PROCEDURE过程名(参数列表) ASPLSQL子程序体存储过程中可以接收的参数类型： in类型为输入类型的参数 out类型为输出类型的参数。存储过程指存储在数据库中供所有用户程序调用的子程序叫存储过程存储过程调用setserveroutputonbeginraisesalary(7369);end;/setserveroutputonexecraisesalary(7369);方法一：方法二：存储过程调用setserveroutputonsets不接收参数的过程createorreplaceprocedureP1isbegindbms_output.put_line('Currentdateis:'||to_char(sysdate,'yyyy-mm-dd'));end;--调用方法1setserveroutputon;execP1();--调用方法2setserveroutputon;beginp1();end;不接收参数的过程createorreplaceproc接收输入类型的参数：--声明接收参数的只声明类型，不声明大小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;接收输出类型的参数：--select*fromall_在过程中使用游标createorreplaceprocedurepro5ascursorc1isselect*fromperson;v_pperson%rowType;beginopenc1;loopfetchc1intov_p;exitwhenc1%notfound;dbms_output.put_line(v_p.id||v_);endloop;ifc1%isopenthenclosec1;endif;end;--调用setserveroutputon;execpro5;在过程中使用游标createorreplaceproc函数函数存储函数函数（Function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。建立存储函数的语法：CREATE[ORREPLACE]FUNCTION函数名(参数列表)RETURN函数值类型ASPLSQL子程序体；存储函数函数（Function）为一命名的存储程序，可带参数函数示例：createorreplacefunctionf1returnvarchar2asbeginreturn'hello';end;--调用selectf1()fromdual;函数示例：createorreplacefunctio函数的调用declare v_salnumber;begin v_sal:=queryEmpSalary(7934); dbms_output.put_line('salaryis:'||v_sal);end;或是直接使用select调用

begin dbms_output.put_line('salaryis:'||queryEmpSalary(7934));end;函数的调用declarebegin过程和函数中的in和out一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。In类型的参数，只可以接收值，不能再给in类型的参数设置新的值。但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个。过程和函数中的in和out一般来讲，过程和函数的区别在于函数什么时候用存储过程/存储函数？原则：如果只有一个返回值，用存储函数；否则，就用存储过程。什么时候用存储过程/存储函数？原则：函数示例2：用函数去掉字段中多个重复的空格：createorreplacefunctionmtrim(strvarchar2)returnvarchar2asv_strvarchar2(500):='';beginselectregexp_replace(str,'(\s)\1','')intov_strfromdual;returnv_str;end;--测试selectmtrim('HA')fromdual;函数示例2：用函数去掉字段中多个重复的空格：触发器/Trigger触发器/Trigger触发器数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。触发器的类型语句级触发器在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。行级触发器（FOREACHROW）触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量,识别值的状态。触发器数据库触发器是一个与表相关联的、存储的PL/SQL程序创建触发器CREATE[orREPLACE]TRIGGER触发器名{BEFORE|AFTER}{DELETE|INSERT|UPDATE[OF列名]}ON表名[FOREACHROW[WHEN(条件)]]PLSQL块创建触发器CREATE[orREPLACE]T触发语句与伪记录变量的值触发语句:old:newInsert将要插入的数据Update更新以前该行的值更新后的值delete删除以前该行的值触发语句与伪记录变量的值触发语句:old:newInsert示例：限制写入以J开始的串：--书写一个最基本的beforetriggercreateorreplacetriggert1beforeinsertonpersonforeachrowbegindbms_output.put_line('Hello'||:NEW.id||:NEW.name);

if:NEW.namelike'J%'thenRAISE_APPLICATION_ERROR(-20000,'限制写入以J开始的串');--在触发器中，不能写commit或是rollbackendif;end;setserveroutputon;insertintopersonvalues('X790','Mack');示例：限制写入以J开始的串：--书写一个最基本的beforeForeachrow的含义：添加foreachrow后，触发器，将变为行级触发器：如：createorreplacetriggertrigger1beforeupdateonpersonforeachrowdeclarebegindbms_output.put_line('Hello');end;如果person表中有四行，则会显示4个hello.如果没有foreachrow则只会显示一个hello.只有在行级的触发器中，才可以使用:new,:old。Foreachrow的含义：添加foreachrow示例2：限制修改的年龄大于以前的年龄createorreplacetriggert2beforeupdateont6foreachrowbeginif:NEW.age<:OLD.agethenRAISE_APPLICATION_ERROR(-20000,'年龄不能越来越小');endif;end;--写入小值就是出错updatet6setage=143;select*fromt6;示例2：限制修改的年龄大于以前的年龄示例：同时使用insert,update,delete类型的触发器：--在同一个表上，使用insert,update,delete触发器createorreplacetriggertrigger1beforeinsertorupdateordeleteonpersondeclarebegindbms_output.put_line('hello');end;--以下都会引发上面的触发器setserveroutputon;insertintopersonvalues(1,'Jack');setserveroutputon;updatepersonsetname='Tom';setserveroutputon;deletefromperson;示例：同时使用insert,update,delete类型的示例：判断是何种类型的操作：--同时声明insert,update,delete判断是何种操作createorreplacetriggertrigger1beforeinsertorupdateordeleteonpersonforeachrowdeclarebeginifINSERTINGthendbms_output.put_line('这是插入数据');elsifDELETINGthendbms_output.put_line('这是删除数据');elsedbms_output.put_line('这是修改数据');endif;end;示例：判断是何种类型的操作：示例：触发器_根据条件决定是否执行plsql块createorreplacetriggertrigger1beforeinsertonpersonforeachrowwhen(NEW.id=8)--这儿是这样引用newdeclarebegin--这儿前面必须要添加:new才可以引用dbms_output.put_line('这真是的是等于'||:new.id||:);end;示例：触发器_根据条件决定是否执行plsql块示例：作用在某个列上的createorreplacetriggertrigger1--声明将这个触发器作用到某个列上beforeupdateofnameonpersonforeachrowDeclareBegindbms_output.put_line('之前的值'||:||'现在的值:'||:);end;示例：作用在某个列上的示例：在触发器调用过程：createorreplacetriggertg1beforeinsertonpersonforeachrowdeclarev_agenumber:=0;begindbms_output.put_line('Trigger..');

P1(v_age);--直接调用过程即可dbms_output.put_line('ageis:'||v_age);end;示例：在触发器调用过程：触发器小总结触发器可用于数据确认

实施复杂的安全性检查做审计，跟踪表上所做的数据操作等数据的备份和同步查询触发器、过程及函数Select*fromuser_triggers;Select*fromuser_source;触发器小总结触发器可用于游标游标游标：游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。游标分为三种类型：隐式Cursor显式CursorRefCursor（动态Cursor）。游标：游标是SQL的一个内存工作区，由系统或用户以变量的形式隐式游标：对于Select…INTO…语句，一次只能从数据库中获取到一条数据，对于这种类型的DMLSql语句，就是隐式Cursor。例如：Select/Update/Insert/Delete操作。作用：可以通过隐式Cusor的属性来了解操作的状态和结果，从而达到流程的控制。隐式Cursor是系统自动打开和关闭Cursor。Cursor的属性包含：SQL%ROWCOUNT整型代表DML语句成功执行的数据行数。SQL%FOUND布尔型值为TRUE代表插入、删除、更新或单行查询操作成功。SQL%NOTFOUND布尔型与SQL%FOUND属性返回值相反。SQL%ISOPEN布尔型DML执行过程中为真，结束后为假。隐式游标：对于Select…INTO…语句，一次只能从数据隐式游标示例1：用隐式的游标判断是否修改数据成功：setserveroutputon;beginupdatepersonsetid='P003'whereid='P010';ifSQL%FOUNDthendbms_output.put_line('updatesuccessfull');commit;elsedbms_output.put_line('updatefaulied');endif;end;隐式游标示例1：用隐式的游标判断是否修改数据成功：隐式游标示例2：判断影响的行数：setserveroutputon;declarerowCountinteger;beginupdatepersonsetname='Jack';rowCount:=SQL%ROWCOUNT;--输出本次共影响几行dbms_output.put_line('Effectedrowsis:'||rowCount);end;隐式游标示例2：判断影响的行数：显式Cursor：对于从数据库中提取多行数据，就需要使用显式Cursor。显式Cursor的属性包含：%ROWCOUNT整型获得FETCH语句返回的数据行数。%FOUND布尔型最近的FETCH语句返回一行数据则为真，否则为假。%NOTFOUND布尔型与%FOUND属性返回值相反。%ISOPEN布尔型游标已经打开时值为真，否则为假。对于显式游标的运用分为四个步骤：定义游标---Cursor[CursorName]IS;必须要使用is.打开游标---Open[CursorName];操作数据---Fetch[Cursorname]intosomeVar;判断游标是否获取到行值exitwhenc1%notfound;关闭游标---Close[CursorName],这个Step绝对不可以遗漏。显式Cursor：对于从数据库中提取多行数据，就需要使用显显式Cursor用法-1：遍历数据：setserveroutputon;declarersperson%rowType;cursorc1isselect*fromperson;beginopenc1;loop

fetchc1intors;--每一次将值写到rs记录集中exitwhenc1%notfound;--判断是否获取到了数据dbms_output.put_line('行号：'||c1%rowcount||'编号'||rs.id||'姓名'||);endloop;ifc1%isopenthen–关闭游标closec1;endif;end;显式Cursor用法-1：遍历数据：显式Cursor用法-2setserveroutputon;declarecursorc1isselectid,namefromperson;--声明两个变量v_idperson.id%type;v_%type;beginopenc1;loop

--分别对应写入两个值,注意与select语句的对应关系fetchc1intov_id,v_name;exitwhenc1%notfound;DBMS_OUTPUT.put_line('Nameis:'||v_name);endloop;ifc1%isopenthenclosec1;endif;end;显式Cursor用法-2setserveroutputo显式Cursor用法-3:setserveroutputon;declarecursorc1isselect*fromperson;begin

--使用for..in直接打开的游标,直接使用rs变量即可forrsinc1loopdbms_output.put_line(rs.id||''||);endloop;ifc1%isopenthenclosec1;endif;end;显式Cursor用法-3:setserveroutput综合示例：12执行select语句并把查询的结果写c13取一条记录处理4判断是否取到数据按员工的工种长工资,总裁1000元，经理长800元其，他人员长400元综合示例：12执行select语句并把查询的结果写c13取一RefCursor（动态游标）：与隐式Cursor,显式Cursor的区别：RefCursor是可以通过在运行期间传递参数来获取数据结果集。而另外两种Cursor，是静态的，在编译期间就决定数据结果集。Refcursor的使用:Type[Cursortypename]isrefcursor;Define动态的Sql语句Opencursor操作数据---Fetch[Cursorname]CloseCursorRefCursor（动态游标）：与隐式Cursor,显式动态游标示例：declaretypecursor_typeisrefcursor;myCursorcursor_type;v_sqlvarchar2(500);v_personperson%rowType;beginv_sql:='select*frompersonwherenamelike''%a%''';

openmyCursorforv_sql;loopfetchmyCursorintov_person;exitwhenmycursor%notfound;dbms_output.put_line(v_person.id||v_);endloop;exception--异常处理块，确保关闭游标whenothersthenclosemyCursor;ifmyCursor%isopenthenclosemyCursor;endif;end;动态游标示例：declare带参数的游标：语法：声明参数，不可以声明大小cursorc2(jobcvarchar2)isselectename,salfromempwherejob=jobc;执行语句:Openc2(‘clerk’);带参数的游标：语法：声明参数，不可以声明大小执行语句:带参数的游标示例：setserveroutputon;declarecursorc1(p_idvarchar2)isselect*frompersonwhereid=p_id;begin

--注意下面的参数，或是使用openc1('P001');forrsinc1('P002')loopdbms_output.put_line(rs.id||);endloop;end;带参数的游标示例：setserveroutputon;游标综合示例：用游标删除用户指定的时间创建的表：游标综合示例：用游标删除用户指定的时间创建的表：Pl/sql块断点调试：断点调试需要用户具备以下两个权限：debugconnectsessiondebuganyprocedure(或其他对象)Pl/sql块断点调试：断点调试需要用户具备以下两个权限：总结：过程触发器函数游标总结：过程第07章OraclePLSQL语言基础_控制语句_过程_触发器课件数据库操作与管理语言OracleSQL

OraclePL/SQL语言基础/过程/触发器数据库操作与管理语言OracleSQL

Oracle本章目标PL/SQL语言基本结构PL/SQL变量和常量的声明PL/SQL程序的执行部分了解PL/SQL常用函数存储过程触发器自定义函数游标本章目标PL/SQL语言基本结构PL/SQL简介PL/SQL(ProceduralLanguage/SQL，过程语言/SQL)它是结合Oracle过程语言和结构化查询语言的一种扩展语言PL/SQL支持多种数据类型，可以使用条件语句和循环语句等控制结构PL/SQL可用于创建存储过程、触发器和程序包，也可以用来处理业务规则、数据库事件或给SQL命令的执行添加程序逻辑PL/SQL简介PL/SQL(ProceduralLangPL/SQL的优点支持SQL支持面向对象编程(OOP)更好的性能可移植性与SQL集成安全性PL/SQL的优点支持SQLPL/SQL的基本结构-1PL/SQL语言是程序化程序设计语言。块(Block)是PL/SQL程序中最基本的结构，所有PL/SQL程序都是由块组成。PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成：DECLARE标记声明部分变量的声明，必须要在begin前面声明一些变量、常量、用户定义的数据类型及游标namevarchar(30);--声明时不设置值namevarchar(30):=‘Jack’;--声明带有默认值%type;--直接引用一个表的数据类型BEGIN标记主程序体部分开始主程序体，在这里可以加入各种合法语句EXCEPTION标记异常处理部分开始异常处理程序，当程序中出现错误时执行这一部分END标记主程序体结束部分PL/SQL的基本结构-1PL/SQL语言是程序化程序设计语PL/SQL基本结构-2declare

说明部分（变量说明，光标申明，例外说明〕begin语句序列（DML语句〕…exception例外处理语句End;/PL/SQL基本结构-2declarePL/SQL字符集PL/SQL语言有效字符包括以下三类所有大写和小写英文字母0~9的阿拉伯数字操作符，包括(、)、+、-、*、/、<、>、!、=、@、%等PL/SQL标识符标识符的最大长度为30个字符，不区分大小写，但建议在标识符中适当使用大小写，以增加程序的可读性。PL/SQL字符集PL/SQL语言有效字符包括以下三类PL/SQL的运算符PL/SQL语言的运算符算术运算符加(+)、减(-)、乘(*)、除(/)、乘方(**)和连接(||)关系运算符=、<>(或!=)、<、>、>=、<=、BETWEEN...AND...、IN、LIKE、ISNULL逻辑运算符逻辑与(AND)、逻辑或(OR)、逻辑非(NOT)PL/SQL的运算符PL/SQL语言的运算符PL/SQL常量和变量在PL/SQL程序运行时，需要定义一些变量来存放一些数据。常量和变量在使用前必须声明，可以使用DECLARE对变量进行声明，语法如下：DECLARE <变量名><变量类型>:=默认值; ….在DECLARE块中可以同时声明多个常量和变量。声明普通常量或变量是需要说明以下信息：常量或变量的名称常量或变量的数据类型PL/SQL常量和变量在PL/SQL程序运行时，需要定义一些变量说明说明变量（char,varchar2,date,number,boolean,long)记录变量分量的引用：emp_rec.ename:='ADAMS';说明变量名、数据类型和长度后用分号结束说明语句。引用型变量，即my_name的类型与emp表中ename列的类型一样记录型变量集变量说明说明变量（char,varchar2,dat声明常量声明常量的基本格式如下：<常量名>constant<数据类型>:=<值>;:=为赋值语句关键字constant表示声明的是常量。常量一旦定义，在以后的使用中其值不再改变。一些固定的大小为了防止有人改变，最好定义成常量。例如Pass_ScoreconstantINTEGER:=60;声明常量声明常量的基本格式如下：声明变量声明变量的基本格式如下：<变量名><数据类型>[(宽度):=<初始值>];变量声明是没有关键字，但要指定数据类型，宽度和初始值可以定义也可以不定义。例如AddressVARCHAR2(30):=‘地址未知’;PL/SQL对一个未初始化的变量，将被默认赋值为NULL例如AddressVARCHAR2(30);声明变量声明变量的基本格式如下：PLSQL案例-1：--打开输出setseveroutputon-声明一个变量并输出declare namevarchar(10):=‘HelloWorld';begindbms_output.put_line(name);end;PLSQL案例-1：--打开输出案例-2：例如SETSERVEROUTPUTON;DECLARE

Pass_ScoreconstantINTEGER:=60;AddressVARCHAR2(30):='北京海淀区';BEGIN DBMS_OUTPUT.PUT_LINE(Pass_Score); DBMS_OUTPUT.PUT_LINE(Address);END;使用SETSERVEROUTPUTON命令设置环境变量SERVEROUTPUT为打开状态，从而使PL/SQL程序能够在SQL*Plus中输出结果使用函数DBMS_OUTPUT.PUT_LINE()可以输出参数的值案例-2：例如PL/SQL程序的执行部分PL/SQL程序的执行部分包括赋值语句流程控制语句SQL语句游标语句PL/SQL程序的执行部分PL/SQL程序的执行部分包括使用赋值语句可以在声明变量时或处理变量时设置初始值。也可以在程序的执行部分对变量进行赋值。SETServerOutPutON;DECLARE TrainNameVARCHAR2(30);BEGIN TrainName:=‘OracleAdministration’;--赋值一个新值 Dbms_output.put_line(TrainName);END;运行结果如下图所示使用赋值语句可以在声明变量时或处理变量时设置初始值。也可以在接收用户的输入赋值：在declare之前，可以通过acceptsomeVarprompt‘提示信息’;要求用户输入在后面的代码中，可以通过地址引用&someVal接收这值--接收用户的输入setserveroutputon--要求用户输入一个串,如果不输入后面也可以直接引用，只是一个提示而已

acceptageprompt'plzenteranumber:'; declare nnnumber; begin

nn:=&age; dbms_output.put_line('你输入的信息是'||nn); end;接收用户的输入赋值：在declare之前，可以通过从查询中结果中赋值：用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;从查询中结果中赋值：用into关键字可以将查询结果的值，设置流程控制语句流程控制语句是所有过程性程序语言的关键PL/SQL的主要控制语句如下：if...thenelsif…thenendif;判断if正确则执行then，否则执行else(elsif为嵌套判断)注意elsif,里面少一下e.Casevarwhen…thenwhen…thenend有逻辑的从数值中做出选择Loopexitendloop循环控制，用判断语句执行exitLoopexitwhen…endloop同上，当when为真时执行exitwhile..loopendloop当while为真时循环for...in...loopendloop已知循环次数的循环流程控制语句流程控制语句是所有过程性程序语言的关键条件语句IF-1IF语句是根据条件表达式的值决定执行相应的程序段。语法结构如下：IF<条件表达式>THEN<执行语句>...<执行语句n>[ELSIF<条件表达式>THEN<执行语句>...<执行语句n>...ELSE<执行语句>]ENDIF;其中ELSIF子句是可选项。注意是ELSIF而不是ELSEIF条件语句IF-1IF语句是根据条件表达式的值决定执行相应的程条件语句IF-2程序中说明一个整型变量Number，使用IF语句判断Number变量是正数、负数或0。SETServerOutPutON;DECLARENumberINTEGER:=-10;Begin

IFNumber<0THENdbms_output.put_line('负数');

ELSIFNumber>0THENdbms_output.put_line('正数');

ELSEdbms_output.put_line('0');

ENDIF;End;执行效果如右图所示：条件语句IF-2程序中说明一个整型变量Number，使用IF分支语句CASE-1分支语句是对指定的变量进行判断，从指定的列表中选择满足条件的行，并把该行的值作为CASE语句的结果返回。CASE语句的语法结构如下：CASE<变量>WHEN<表达式1>THEN值1WHEN<表达式2>THEN值2......WHEN<表达式n>THEN值nELSE值n+1END;分支语句CASE-1分支语句是对指定的变量进行判断，从指定的分支语句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;分支语句CASE-2声明一个整型变量varDAY和一个字符型案例-1：使用简单的casewhenelseend语句：declareiint:=0;begini:=(casewhen