Oracle数据库培训-PLSQL编程.ppt

PL SQL编程 本章学习目标本章将讲述PL SQL基础语法 结构和组件 以及如何设计并执行一个PL SQL程序 第二章PL SQL编程 本章内容安排 2 1PL SQL简介 2 2PL SQL语法 2 3存储过程 2 4触发器 2 5游标 2 6包 2 7函数 2 1PL SQL简介 2 1 1PL SQL特点 2 1 2开发及运行环境 1 PL SQL的优点 1 PL SQL是一种高性能的基于事务处理的语言 能运行在任何Oracle环境中 支持所有数据处理命令 通过使用PL SQL程序单元处理SQL的数据定义和数据控制元素 2 PL SQL支持所有SQL数据类型和所有SQL函数 同时支持所有Oracle对象类型 3 PL SQL块可以被命名和存储在Oracle服务器中 同时也能被其他的PL SQL程序或SQL命令调用 任何客户 服务器工具都能访问PL SQL程序 具有很好的可重用性 2 可以使用Oracle数据工具管理存储在服务器中的PL SQL程序的安全性 可以授权或撤销数据库其他用户访问PL SQL程序的能力 5 PL SQL代码可以使用任何ASCII文本编辑器编写 所以对任何Oracle能够运行的操作系统都是非常便利的 2 1 1PL SQL特点 2 PL SQL的缺点对于SQL Oracle必须在同一时间处理每一条SQL语句 在网络环境下这就意味作每一个独立的调用都必须被oracle服务器处理 这就占用大量的服务器时间 同时导致网络拥挤 而PL SQL是以整个语句块发给服务器 这就降低了网络拥挤 服务器端PL SQL不需要显式的安装或许可 它是Oracle9i数据库的隐式部分 此处有相应的文档 PL SQL编译器和解释器也嵌入到OracleDeveloper中 为开发者在客户端和服务器端提供一致的 可利用的开发模型 2 1 2开发及运行环境 2 2 2常量与变量 2 2 3数据类型 2 2 4运算符 2 2 1PL SQL块 2 2 5流程控制 2 2 6异常处理 2 2PL SQL语法 1 块结构 2 块的命名和匿名 3 块的执行 2 2 1PL SQL块 1 块结构PL SQL是一种块结构的语言 组成PL SQL程序的单元是逻辑块 一个PL SQL程序包含了一个或多个逻辑块 每个块都可以划分为三个部分 1 块的三个部分 声明部分 Declarationsection 声明部分包含了变量和常量的数据类型和初始值 这个部分是由关键字DECLARE开始 如果不需要声明变量或常量 那么可以忽略这一部分 执行部分 Executablesection 执行部分是PL SQL块中的指令部分 由关键字BEGIN开始 所有的可执行语句都放在这一部分 其他的PL SQL块也可以放在这一部分 异常处理部分 Exceptionsection 这一部分是可选的 在这一部分中处理异常或错误 对异常处理的详细讨论在后面进行 2 PL SQL块语法PL SQL块语法结构如下 DECLARE declarationstatementsBEGINexecutablestatements EXCEPTION exceptionstatementsEND例 declarein idnumber 2 in namevarchar2 20 beginin id 1 in name James updatetestsett name in namewheret id in id end PL SQL块中的每一条语句都必须以分号结束 SQL语句可以多行 但分号表示该语句的结束 一行中可以有多条SQL语句 他们之间以分号分隔 每一个PL SQL块由BEGIN或DECLARE开始 以END结束 注释由 标示 2 块的命名和匿名PL SQL程序块可以是一个命名的程序块也可以是一个匿名程序块 匿名程序块可以用在服务器端也可以用在客户端 执行部分包含了所有的语句和表达式 执行部分以关键字BEGIN开始 以关键字EXCEPTION结束 如果EXCEPTION不存在 那么将以关键字END结束 分号分隔每一条语句 使用赋值操作符 或SELECTINTO或FETCHINTO给每个变量赋值 执行部分的错误将在异常处理部分解决 在执行部分中可以使用另一个PL SQL程序块 这种程序块被称为嵌套块 所有的SQL数据操作语句都可以用于执行部分 PL SQL块不能在屏幕上显示SELECT语句的输出 SELECT语句必须包括一个INTO子串或者是游标的一部分 执行部分使用的变量和常量必须首先在声明部分声明 执行部分必须至少包括一条可执行语句 NULL是一条合法的可执行语句 事物控制语句COMMIT和ROLLBACK可以在执行部分使用 数据定义语言 DataDefinitionlanguage 不能在执行部分中使用 DDL语句与EXECUTEIMMEDIATE一起使用或者是DBMS SQL调用 3 块的执行SQL PLUS中匿名的PL SQL块的执行是在PL SQL块后输入 来执行 命名的程序与匿名程序的执行不同 执行命名的程序块必须使用execute关键字 如果在另一个命名程序块或匿名程序块中执行这个程序 那么就不需要EXECUTE关键字 1 变量 2 常量 3 有效字符集 2 2 2常量与变量 1 变量 1 声明变量声明变量的语句格式如下 Variable name CONSTANT databyte NOTNULL DEFAULTexpression 注意 可以在声明变量的同时给变量强制性的加上NOTNULL约束条件 此时变量在初始化时必须赋值 2 给变量赋值给变量赋值有两种方式 直接给变量赋值 通过SQLSELECTINTO或FETCHINTO给变量赋值 2 常量常量与变量相似 但常量的值在程序内部不能改变 常量的值在定义时赋予 声明方式与变量相似 但必须包括关键字CONSTANT 常量和变量都可被定义为SQL和用户定义的数据类型 为了减少这部分程序的修改 编程时使用 TYPE ROWTYPE方式声明变量 使变量声明的类型与表中的保持同步 随表的变化而变化 这样的程序在一定程度上具有更强的通用性 TYPE对应列 ROWTYPE对应行 3 有效字符集 1 所有的大写和小写英文字母 2 数字0 9 3 符号 0 一 一 PL SQL标识符的最大长度是30个字符 并且不区分字母的大小写 但是适当地使用大小写 可以提高程序的可读性 2 2 3数据类型 例如 定义如下若干类型变量 常量 DECLAREORDER NONUMBER 3 CUST NAMEVARCHAR2 20 ORDER DATEDATE EMP NOINTEGER 25 PICONSTANTNUMBER 3 1216 BEGINNULL END 2 2 4运算符 与其他程序设计语言相同 PL SQL有一系列操作符 主要有 算术操作符 关系操作符 inlikeisnullbetween and逻辑操作符andornot 1 条件结构 2 循环控制 3 GOTO语句 2 2 5流程控制 2 嵌套 1 条件结构1 If条件判断逻辑结构If条件判断逻辑结构有三种表达方式 表达式一 ifconditionthenStatementendif该表达式的功能为 若条件为真 执行then后的语句 否则 跳出条件语句执行endif后的语句 表达式二 ifconditionthenStatements 1elseStatements 2endif该表达式的功能为 如果条件为真执行then后的语句 否则执行else后的语句 表达式三 ifcondition1thenStatements 1elsifcondition2thenStatements 2elseStatements 3endif该表达式的功能为 如果if后的条件成立 执行then后面的语句 否则判断elseif后面的条件 条件成立执行第二个then后面的语句 否则执行else后的语句 这是条件语句嵌套 IF可以嵌套 可以在IF或IF ELSE语句中使用IF或IF ELSE语句 2 Case表达式Case语句的基本格式如下 Case变量WHEN表达式1then值1WHEN表达式2then值2WHEN表达式3then值3WHEN表达式2then值2ELSE值5END Case语句的功能 首先设定变量的值作为条件 然后顺序检查表达式 一旦从中找到与条件匹配的表达式值 就停止case语句的处理 2 循环控制 1 loop exit end循环控制语句LOOP循环语句是其中最基本的一种 LOOP语句的格式如下 LOOPstatementsENDLOOP 这种循环语句是没有终止的 如果不人为控制的话 其中的statements将会无限地执行 一般可以通过加入EXIT语句来终结该循环 2 WHILE LOOP循环控制语句WHILE LOOP有一个条件与循环相联系 如果条件为TRUE 则执行循环体内的语句 如果结果为FALSE 则结束循环 3 FOR LOOP循环控制语句FOR LOOP循环控制语句的格式如下 FORcounterIN REVERSE start range end rangeLOOPstatements ENDLOOP LOOP和WHILE循环的循环次数都是不确定的 FOR循环的循环次数是固定的 counter是一个隐式声明的变量 初始值是start range 第二个值是start range 1 直到end range 如果start range等于end range 那么循环将执行一次 如果使用了REVERSE关键字 那么范围将是一个降序 3 GOTO语句GOTO语句的格式如下 GOTOLABEL 执行GOTO语句时 控制会立即转到由标签标记的语句 PL SQL中对GOTO语句有一些限制 对于块 循环 IF语句而言 从外层跳转到内层是非法的 2 嵌套程序块的内部可以有另一个程序块这种情况称为嵌套 嵌套要注意的是变量 定义在最外部程序块中的变量可以在所有子块中使用 如果在子块中定义了与外部程序块变量相同的变量名 在执行子块时将使用子块中定义的变量 子块中定义的变量不能被父块引用 同样GOTO语句不能由父块跳转道子块中 反之则是合法的 1 PL SQL的异常 2 预定义异常 3 自定义异常 2 2 6异常处理 1 PL SQL的异常异常处理块中包含了与异常相关的错误发生以及当错误发生时要进行执行和处理的代码 异常部分的语法一般如下 BEGINEXCEPTIONWHENexcep name1THEN WHENexcep name2THEN WHENOTHERSTHEN END 2 预定义异常表2 3常见异常情况表 3 自定义异常异常不一定必须是oracle返回的系统错误 用户可以在自己的应用程序中创建可触发及可处理的自定义异常 调用异常处理需要使用Raise语句 异常情态的传播指的是当在程序块的声明 执行 异常部分分别出现异常情态时 或在本块中没有相应的异常处理器时会将这个异常情态传播到哪里 会去激发那个块中的处理器 传播规则是这样的 当一个异常情态是在块的执行部分引发的 最常见的 PL SQL使用下面的规则确定激活哪个异常处理器 1 若当前块对该异常情态设置了处理器 则执行它并成功完成该块的执行 然后控制转给包含块 2 若当前块没有该处理器 则通过在包含块中引发它来传播异常情态 然后对包含块执行PL SQL的异常操作 另外 无论是在声明部分引发了一个异常情态 还是在异常处理部分引发 则该异常情态将立即传播给包含块 在包含块引用上述规则进行异常情态的处理 即使在当前块设置了OTHERS处理器也不会被执行 2 3 2存储过程的调用 2 3 3存储过程的释放 2 3 4实例 2 3 1存储过程的创建 2 3存储过程 创建存储过程的语句如下 CREATE ORREPLACE PROCEDURE 过程名 参数1 方式l 数据类型1 参数2 方式2 数据类型2 IS ASis 或as完全等价BEGINPL SQL过程体END 过程名 2 3 1存储过程的创建 过程参数有以下三种类型 1 in参数类型 表示输入给过程的参数 2 out参数类型 表示参数在过程中将被赋值 可以传给过程体的外部 3 inout参数类型 表示该类参数既可以向过程体传值 也可以在过程体中赋值 以便向过程体外传值 存储过程可以直接用EXECUT命令调用或PL SQL程序块内部调用 用EXECUT命令调用存储过程的格式如下 SQL executeproc name par1 par2 2 3 2存储过程的调用 存储过程也可以被另外的PL SQL块调用 调用的语句是 declarepar1 par2 beginproc name par1 par2 end 在调用前要声明变量par1 par2 当某个存储过程不再需要时 应将其从内存中删除 以释放它占用的内存资源 释放过程的语句格式如下 SQL dropprocedureproc name proc name为过程名 2 3 3存储过程的释放 编写存储过程 显示所指定雇员名所在的部门名和位置 CREATEORREPLACEPROCEDUREDeptMesg penameemp ename TYPE pdnameOUTdept dname TYPE plocOUTdept loc TYPE ASBEGINSELECTdname locINTOpdname plocFROMemp deptWHEREemp deptno dept deptnoANDemp ename pename END VARIABLEvdnameVARCHAR2 12 VARIABLEvlocVARCHAR2 13 EXECUTEDeptMesg SMITH vdname vloc PRINTvdnamevloc 2 3 4实例 2 2 2触发器的类型 2 2 3触发器的创建 2 2 4触发器的修改和删除 2 2 1触发器的设计规则 2 2触发器 2 2 5实例 1 作用范围清晰 2 不要让触发器去完成Oracle后台已经能够完成的功能 3 限制触发器代码的行数 2 不要创建递归的触发器 5 触发器仅在被触发语句触发时进行集中的 全局的操作 同用户和数据库应用无关 2 2 1触发器的设计规则 可以创建被如下语句所触发的触发器 1 DML语句 DELETE INSERT UPDATE 2 DDL语句 CREATE ALTER DROP 3 数据库操作 SERVERERROR LOGON LOGOFF STARTUP SHUTDOWN 2 2 2触发器的类型 1 使用CREATETRIGGER语句创建触发器使用CREATETRIGGER语句创建触发器的语句格式如下 CREATE ORREPLACE TRIGGERname BEFORE AFTER event OR ONtable FOR EACH ROW STATEMENT WHEN condition plsqlblock callprocedures statement 2 2 3触发器的创建 2 注意事项 1 触发器可以声明为在对记录进行操作之前 在之前 检查约束之前和INSERT UPDATE或DELETE执行前 或之后 在检查约束之后和完成INSERT UPDATE或DELETE操作 触发 2 一个FOREACHROW执行指定操作的触发器为操作修改的每一行都调用一次 3 SELECT并不更改任何行 因此不能创建SELECT触发器 这种场合下规则和视图更适合 2 触发器和某一指定的表格有关 当该表格被删除时 任何与该表有关的触发器同样会被删除 5 在一个表上的每一个动作只能有一个触发器与之关联 6 在一个单独的表上 最多只能创建三个触发器与之关联 一个INSERT触发器 一个DELETE触发器和一个UPDATE触发器 删除触发器的语句格式为 DROPTRIGGERnameONtable 一个触发器由三部分组成 触发事件或语句 触发限制和触发器动作 触发事件或语句是指引起激发触发器的SQL语句 可为对一指定表的INSERT UNPDATE或DELETE语句 触发限制是指定一个布尔表达式 当触发器激发时该布尔表达式是必须为真 触发器作为过程 是PL SQL块 当触发语句发出 触发限制计算为真时该过程被执行 2 2 4触发器的修改和删除 编写一个数据库触发器 当任何时候某个部门从dept表中删除时 该触发器将从emp表中删除该部门的所有雇员 CREATEORREPLACETRIGGERdel emp deptnoBEFOREDELETEONdeptFOREACHROWBEGINDELETEFROMempWHEREdeptno OLD deptno END 2 2 5实例 2 5 2隐式游标 2 5 3实例 2 5 1显式游标 2 5游标 Oracle游标是一种用于轻松的处理多行数据的机制 没有游标 Oracle开发人员必须单独地 显式地取回并管理游标查询选择的每一条记录 游标的另一项功能是 它包含一个跟踪当前访问的记录的指针 这使程序能够一次处理多条记录 1 声明游标 2 打开游标 3 从游标中取回数据 2 5 1显式游标 2 关闭游标 1 声明游标声明游标的语句格式如下 DECLAREcursor nameISSELECTstatement声明游标完成了下面两个目的 1 给游标命名 2 将一个查询与游标关联起来 2 打开游标打开游标的语句格式如下 OPENcursor name 打开游标将激活查询并识别活动集 可是在执行游标取回命令之前 并没有真正取回记录 OPEN命令还初始化了游标指针 使其指向活动集的第一条记录 游标被打开后 直到关闭之前 取回到活动集的所有数据都是静态的 换句话说 游标忽略所有在游标打开之后 对数据执行的SQLDML命令 INSERT UPDATE DELETE和SELECT 因此只有在需要时才打开它 要刷新活动集 只需关闭并重新打开游标即可 3 从游标中取回数据FETCH命令以每次一条记录的方式取回活动集中的记录 通常将FETCH命令和某种迭代处理结合起来使用 在迭代处理中 FETCH命令每执行一次 游标前进到活动集的下一条记录 FETCH命令的语句格式如下 FETCHcursor nameINTOrecord list 执行FETCH命令后 活动集中的结果被取回到PL SQL变量中 以便在PL SQL块中使用 每取回一条记录 游标的指针就移向活动集的下一条记录 2 关闭游标CLOSE语句关闭以前打开的游标 使得活动集不确定 CLOSE语句的格式 CLOSEcursor name 隐式游标也可以叫做SQL游标 和显式的游标不同 不能对一个SQL游标显式的执行OPEN CLOSE和FETCH语句 Oracle隐式的打开SQL游标 处理SQL游标 然后再关闭该游标 Oracle提供隐式游标的主要目的就是利用这些游标的属性来确定SQL语句运行的情况 2 5 2隐式游标 以上游标应用的完整程序代码如下 setserveroutputon declarev enameemp first name type v salaryemp salary type cursorc empisselectfirst name salaryfromempwhererownum 10orderbysalary beginopenc emp loopfetchc empintov ename v salary exitwhenc emp notfound dbms output put line SalofEmp v ename is v salary endloop closec emp end 2 5 3实例 2 7 2自定义函数 2 7 1Oracle系统函数 2 7函数 Oracle提供了丰富的系统函数 按类分别见表2 5 表2 6 表2 7 表2 8所示 2 7 1Oracle系统函数 1 命令格式 2 函数的调用 2 7 2自定义函数 3 函数的释放 2 实例 1 命令格式创建函数的语句格式如下 FUNCTIONname parameter parameter RETURNdatatypesIS localdeclarations BEGINexecutestatements EXCEPTIONexceptionhandlers END name 2 函数的调用无论在命令行还是在程序语句中 函数都可以通过函数名称直接在表达式中调用 例如 将函数count num 女 的返回值赋予变量man num SQL execute man num count num 女 3 函数的释放当函数不再使用时 要用drop命令将其从内存中删除 例如 SQL dropfunctioncount num 2 实例编写一个函数以显示该雇员在此组织中的工作天数 CREATEORREPLACEFUNCTIONHire Day noemp empno TYPE RETURNNUMBERASvhiredateemp hiredate TYPE vdayNUMBER BEGINSELECThiredateINTOvhiredateFROMempWHEREempno no vday CEIL SYSDATE vhiredate RETURNvday END 2 6 2实例