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

数据库操作与管理语言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语言有效字符包括以下三类所有大写和小写英文字母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 声明一个变量并输出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 thenelsif thenendif 判断if正确则执行then 否则执行else elsif为嵌套判断 注意elsif 里面少一下e Casevarwhen thenwhen thenend有逻辑的从数值中做出选择Loopexitendloop循环控制 用判断语句执行exitLoopexitwhen endloop同上 当when为真时执行exitwhile loopendloop当while为真时循环for in loopendloop已知循环次数的循环 条件语句IF 1 IF语句是根据条件表达式的值决定执行相应的程序段 语法结构如下 IFTHEN ELSIFTHEN ELSE ENDIF 其中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在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语句 SQL select caseidwhen2then 222 else 33 end fromt6 SQL 上面的示例等于SQL select casewhenid 2then 2222 else 333 end fromt6 循环语句LOOP EXIT END 1 此语句的功能是重复执行循环体中的程序块 直到执行EXIT语句 则退出循环 LOOP EXIT END语句的语法结构如下LOOPIFTHENEXITENDIFENDLOOP 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语句的语法结构如下 LOOPEXITWHENENDLOOP LOOP EXITWHEN ENDLOOP 2 重新实现1 4累加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 再次实现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 FOR IN LOOP ENDLOOP 1 此语句定义一个循环变量 并指定循环变量的初始值和终止值 每循环一次循环变量自动加1 FOR IN LOOP ENDLOOP语句的语法如下FORIN LOOPENDLOOP 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 3 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 900 whereid 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 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 用异常在PLSQL块中管理事务 setserveroutputon begininsertintopersonvalues P007 Tom1 insertintopersonvalues P006 Tom4 commit 提交exceptionwhenothersthenrollback 回滚dbms output put line SQLCODE SQLERRM end 小结 PL SQL语言基本结构变量和常量的声明流程控制语句 存储过程 函数 触发器 游标 存储过程 存储过程 指存储在数据库中供所有用户程序调用的子程序叫存储过程 创建存储过程用CREATEPROCEDURE命令建立存储过程和存储函数语法 create orreplace PROCEDURE过程名 参数列表 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子句 用于返回函数值 函数说明要指定函数名 结果值的类型 以及参数类型等 建立存储函数的语法 CREATE ORREPLACE FUNCTION函数名 参数列表 RETURN函数值类型ASPLSQL子程序体 函数示例 createorreplacefunctionf1returnvarchar2asbeginreturn hello 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 delete 在指定的表上发出时 Oracle自动地执行触发器中定义的语句序列 触发器的类型语句级触发器在指定的操作语句操作之前或之后执行一次 不管这条语句影响了多少行 行级触发器 FOREACHROW 触发语句作用的每一条记录都被触发 在行级触发器中使用old和new伪记录变量 识别值的状态 创建触发器 CREATE orREPLACE TRIGGER触发器名 BEFORE AFTER DELETE INSERT UPDATE OF列名 ON表名 FOREACHROW WHEN 条件 PLSQL块 触发语句与伪记录变量的值 示例 限制写入以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 Foreachrow的含义 添加foreachrow后 触发器 将变为行级触发器 如 createorreplacetriggertrigger1beforeupdateonpersonforeachrowdeclarebegindbms output put line Hello end 如果person表中有四行 则会显示4个hello 如果没有foreachrow则只会显示一个hello 只有在行级的触发器中 才可以使用 new old 示例2 限制修改的年龄大于以前的年龄createorreplacetriggert2beforeupdateont6foreachrowbeginif NEW age OLD agethenRAISE APPLICATION ERROR 20000 年龄不能越来越小 endif end 写入小值就是出错updatet6setage 143 select fromt6 示例 同时使用insert update delete类型的触发器 在同一个表上 使用insert update delete触发器createorreplacetriggertrigger1beforeinsertorupdateordeleteonpersondeclarebegindbms output put line hello end 以下都会引发上面的触发器setserveroutputon insertintopersonvalues 1 Jack setserveroutputon updatepersonsetname Tom setserveroutputon deletefromperson 示例 判断是何种类型的操作 同时声明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 new name end 示例 作用在某个列上的createorreplacetriggertrigger1 声明将这个触发器作用到某个列上beforeupdateofnameonpersonforeachrowDeclareBegindbms output put line 之前的值 old name 现在的值 new name 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 隐式游标 对于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执行过程中为真 结束后为假 隐式游标示例1 用隐式的游标判断是否修改数据成功 setserveroutputon beginupdatepersonsetid P003 whereid P010 ifSQL FOUNDthendbms output put line updatesuccessfull commit elsedbms output put line updatefaulied endif end 隐式游标示例2 判断影响的行数 setserveroutputon declarerowCountinteger beginupdatepersonsetname Jack rowCount SQL ROWCOUNT 输出本次共影响几行dbms output put line Effectedrowsis rowCount end 显式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用法 1 遍历数据 setserveroutputon declarersperson rowType cursorc1isselect fromperson beginopenc1 loopfetchc1intors 每一次将值写到rs记录集中exitwhenc1 notfound 判断是否获取到了数据dbms output put line 行号 c1 rowcount 编号 rs id 姓名 rs name endloop ifc1 isopenthen 关闭游标closec1 endif end 显式Cursor用法 2 setserveroutputon declarecursorc1isse