PLSQL清华大学讲义1.ppt

PL SQL程序设计 课程内容 第一章 PL SQL程序结构第二章 过程和函数第三章 包第四章 触发器第五章 面向对象的PL SQL设计 第一章 PL SQL程序结构 1 PL SQL的简介 什么是PL SQL PL SQL是一种比较复杂的程序设计语言 用于从各种环境中访问ORACLE数据库 现在 PL SQL与服务器集成在一起 这样就可以快速而有效地处理PL SQL代码 PL SQL将SQL的强大性和灵活性与3GL的过程性结构融为一体 PL SQL通过增加了用在其他过程性语言中的结构来对SQL进行扩充 例如 变量和类型 包括系统定义的和用户定义的 控制结构 条件判断和循环语句 过程和函数 对象类型和方法过程性结构与ORACLESQL集成到一起 便产生了一种结构化的强有力的语言 PL SQL的优点 对SQL的扩充 允许使用全部DML命令 游标控制命令 事务控制命令和全部SQL函数 运算符 伪列 过程化和模块化 语句 过程 函数和包 更好的性能 减少与ORACLE的通信和对ORACLE的调用 可移植性 在ORACLE可运行的任何地方运行 与ORACLE数据类型相集成 后台技术 PL SQL用于何处 在SQL PLUS中使用PL SQL 当需要过程化逻辑时 就需要使用PL SQL块 例如 每隔几条记录完成某些操作或 基于前一条SQL命令完成某些操作 如果连续使用多条SQL命令 可能出现网络传输能力和操作系统空间再分配能力降低时 使用PL SQL 在宿主语言编程时使用PL SQL在pro c等宿主语言中嵌入PL SQL命令 用于对数据库数据进行存取操作 用宿主语言对数据的输入 输出格式进行控制 在DEVELOPER 2000中使用PL SQL在DEVELOPER 2000的各工具中编写过程 函数 触发器等需要过程化编码时使用 在ORACLEAPPLICATIONSERVER的开发中使用PL SQL PL SQL与SQL的比较 PL SQL支持的SQL命令所有数据操作语句 DML INSERT UPDATE DELETE SELECT等事物控制命令 COMMIT ROLLBACK SAVEPOINT等SQL命令的所有函数 SUM AVG UPPER LOWER等操作符 IN LIKE BETWEENAND等 PL SQL不支持的SQL命令数据定义命令 DDL CREATE DROP等会话控制命令 SETROLE等系统控制命令 ALTERSYSTEM等 注意 在PL SQL中使用SELECT语句时的语法与SQL PLUS中的SELECT语法有区别 其他PL SQL所支持的SQL命令在SQL PLUS和PL SQL中没有区别 SQL PLUS中的SELECT语法为 SELECT列名 列名 FROM表WHERE条件 而在PL SQL中的SELECT语法为 SELECT列名 列名 INTO变量1 变量2 FROM表WHERE条件 2 PL SQL程序结构 所有的PL SQL程序中的基本单位都是块 BLOCK PL SQL程序都是有块组成的 这些块可以顺序出现 也可以相互嵌套 有几种类型的块 无名块 块通常是动态生成的 只能被执行一次 有名块 块是一种带有标签的匿名块 该标签为该块指定了一个名字 有名块也是动态生成的 并且只能被执行一次 子程序 是存储在数据库内部的过程 函数 包 通过调用过程 函数 包便可以执行该子程序 触发器 是存储在数据库内部的有名块 当触发事件出现时 触发器便被多次显式地执行 块结构 基本块结构Declare 说明部分begin 语句执行部分exception 异常处理程序end 其中 只有语句执行部分是必需的 其他两部分都是可选的 嵌套的块结构PL SQL块中可以包含子块 子块可位于PL SQL块中的任何部分 子块也可是一条PL SQL命令 例如 Declare 说明部分Begin 主块的语句执行部分Begin 子块的语句执行部分Exception 子块的异常处理程序End Exception 主块的异常处理程序End 变量定义 语法 标识符 常数 数据类型 NOTNULL PL SQL表达式 表示给变量赋值 例如 V NUMNUMBER 2 12 V CHARVARCHAR2 10 ASASAS V DATEDATE 05 JUL 99 V BOOLEANBOOLEAN FALSE 标识符标识符 identifier 用来命名PL SQL对象 例如 变量 游标 类型和子程序 标识符由字母开头 后面跟随任何字符序列 包括字母 数字 货币符号 下划线字符和 号字符 其他字符则是非法的 一个标识符的最大长度是30个字符 字符不区分大小写 不能是ORACLE的保留字 数据类型数字型 用来存储整数或实数 NUMBER BINARY INTEGER PLS INTEGERNUMBER 存储整数和浮点数BINARY INTEGER 存储带符号的整数值 溢出时不发生错误PLS INTEGER 存储带符号的整数值 溢出时发生错误例如 V NUMNUMBER 5 v binarynumbinary integer 字符型 用类存储字符串或字符数据 包括VARCHAR2 CHAR LONG NCHAR NVARCHAR2 VARCHAR2 存储可变长度的字符串CHAR 存储固定长度的字符串LONG 存储可变长度的字符串 其最大长度是32760字节 NCHAR和NVARCHAR2 NLS字符类型用于存储来自不同于PL SQL语言的字符集中的字符集 例如 V CHARVARCHAR2 20 日期型 用来存储日期和时间信息 包括世纪 年 月 天 小时 分钟和秒 唯一的类型为DATE 例如 V DATEDATE 布尔型 布尔型的类型为BOOLEAN 布尔变量在PL SQL控制结构中使用 BOOLEAN变量只能存储TRUE FALSE NULL值 例如 V BOOLEANBOOLEAN 原始型 用来存储二进制数据 包括RAW LONGRAWRAW 存储定长的二进制数据 类似于CHAR类型 但不在字符集之间进行转换 LONGRAW 与LONG类似 最大长度为32760字节 但不在字符集之间进行转换 例如 V LONGLONG 复合类型 复合类型的变量包含一个或多个标量变量 包括 RECORD TABLE VARRAYRECORD 提供了处理分立的但又是作为一个整体单元的相关变量的一种机制 存储单行多域结构数据 例如 typet studentrecordisrecord studentidnumber 5 firstnamevarchar2 20 lastnamevarchar2 20 TABLE 类似于C语言中的数组 必须首先定义该表类型 然后再定义一个属于该类型的变量 例如 typet charactertableistableofvarchar2 10 indexbybinary integer v charactert charactertable VARRAY 多行单列的结构 数组 例如 declaretypeename typeistableofchar 10 ename arrayename type ibinary integer 0 beginforrecin selectenamefromemp loopi i 1 ename array i rec eanme endloop end LOB类型 用来存储大对象 大对象可以是一个二进制数或字符数值 只要长度小于4GB即可 大对象的操作通过DBMS LOB包进行处理 例如 V BLOBLOCATORBLOB V CLOBLOCATORCLOB 引用类型 REF 类似于C语言中的指针 被定义的引用类型的变量在程序生命周期中可以指向不同的存储位置 REF对象类型可以指向一个对象 例 v roomrefrefroomobj TYPE 即一个变量的类型用另一个已经定义的变量的类型定义 或用某一个表的某一列的类型定义 例如 BOOKPRINTNUMBER V BOOKBOOKPRINT TYPE V EMPNOEMP EMPNO TYPE ROWTYPE 变量的类型参考基表中记录的类型 例如 V RECEMP ROWTYPE 用法 V REC EMPNO 1001 用户定义的类型 根据用户自己的需要 用现有的PL SQL类型组合成一个子类型 创建表时变得及其简单 只要引用一下该类型即可 例如 createorreplacetypestudent typeasobject idnumber 5 firstnamevarchar2 20 lastnamevarchar2 20 majorvarchar2 30 current creditsnumber 3 createtablestudentinfoofstudent type V STUDENTSTUDENT TYPE 变量赋值 格式 变量名 常量或PL SQL表达式 变量的赋值可以在DECLARE段中 也可以在PL SQL语句执行部分赋值 例如 DECLARENUM VARNUMBER 2 5 或 DECLARENUM VARNUMBER 5 BEGINNUM VAR 5 或SELECTSALINTONUM VARFROMEMPWHEREEMPNO 7788 END 变量的作用范围 标识符在定义它的块内有效 标识符如果不在子块中重新定义 则在PL SQL块的所有子块中同样有效 重新定义后的标识符 作用范围仅在本子块中有效 说明 子块1和子块2的变量虽然都命名为V1 但只作用于各自的子块中 主程序内的变量V1可作用于全过程 数据类型的转换 PL SQL可以处理不同类型之间的转换 复合类型不能进行转换 因为在结构上相去很远 有两种类型的转换 隐式的 显式的 例如 SELECTTO CHAR CURRENT CREDITS INTOV CURRENTCREDITSFROMSTUDENTSWHEREID 1002 隐式数据类型转换PL SQL将自动在下列数据类型之间进行转换 字符和数字 字符 数字 数字 字符 字符和日期 字符 日期 日期 字符 例如 CHAR VAR 1234 NUM VAR 1212 DATE VAR 24 DEC 98 空值处理NULL 数字 NULL 空值加数字仍是空值 NULL 数字 NULL 空值与数字比较 结果仍是空值 NULL 字符串 字符串 空值与字符串进行连接运算 结果为原字符串 判断一个变量的值是否为NULL的正确写法为 ifmy varisnullthen endif 错误写法为 ifmy var nullthen endif 使用NULL值进行比较时 注意 例 a 5 b null ifabthen endif 例 x null y null ifx ythen endif 3 PL SQL的控制结构 与其他高级语言一样 PL SQL提供了许多的控制结构以便您在运行时刻对块的行为进行控制 这些结构包括条件语句和循环 条件语句 IF THEN ELSE的语法 IF布尔表达式1THEN语句系列1 ELSIF布尔表达式2THEN语句系列2 ELSE语句系列3 ENDIF 可根据需要写任意多的ELSIF子句 例如 DECLAREV salemp sal type BeginSelectsalintov salfromempwhereempno 7788 Ifv sal 500thenUpdateempsetsal sal 1 5whereempno 7788 Elsifv sal 1500thenUpdateempsetsal sal 1 3whereempno 7788 Elsifv sal 3000thenUpdateempsetsal sal 1 1whereempno 7788 ElseUpdateempsetsal sal 1 0whereempno 7788 Endif Commit End 循环 PL SQL提供了重复执行语句的机制 这就是循环 循环被分为四种类型 简单循环 WHILE循环 数字式FOR循环 游标式FOR循环 在游标章节中介绍 在循环体内必须有退出循环的语句 否则就是死循环 退出循环的语法有两种 EXITWHENCONDITION 或IFCONDITIONTHENEXIT ENDIF 1 简单循环语法 LOOP语句序列 ENDLOOP 例如 下面的块在TEMP TABLE中插入50行 DECLAREV counterbinary integer 1 BeginLoopInsertintotemp tablevalues v counter loopindex V counter v counter 1 Ifv counter 50thenExit Endif Endloop End 2 WHILE循环 语法 WHILEconditionLOOP语句序列 ENDLOOP 例如 用WHILE循环重新改写前面的例子 DECLAREV counterbinary integer 1 BeginWhilev counter 50loopInsertintotemp tablevalues v counter loopindex V counter v counter 1 Endloop End 注意 必须对V COUNTER进行初始化 否则条件V COUNTER 50将求值为NULL 3数字式FOR循环简单循环和WHILE循环的叠代次数在事先是不知道的 它取决于循环条件 而数字式FOR循环的叠代次数是一定的 语法 FORloop counterIN REVERSE low bound high boundLOOP语句序列 ENDLOOP 其中 IN 表示从小值到大值INREVERSE 表示从大值到下值 例如 用FOR循环重新编写我们的例子 DECLAREV COUNTERNUMBER 1 BEGINFORV COUNTERIN1 50LOOPINSERTINTOtemp tablevalues v counter loopindex Endloop End 例如 BEGINFORV COUNTERINREVERSE10 50LOOPINSERTINTOtemp tablevalues v counter loopindex Endloop END GOTO语句和块标号 PL SQL也提供了GOTO语句 语法是 GOTOlabel 这里label是在PL SQL块中定义的标签 标签是用双箭头括起来的 当执行GOTO语句时 控制会立即会无条件地转到由标签标识的语句 GOTO语句的作用是 解决程序跳转问题 例1 DECLAREV counterbinary integer 1 BeginLoopInsertintotemp tablevalues v counter loopcount V counter v counter 1 Ifv counter 50thenGotoendofloop Endif Endloop insertintotemp table char col values done end 对于GOTO的限制对于块 循环或IF语句而言 想要从外层跳到内层是非法的 从一个IF子句跳转到另一个子句中是非法的 从一个异常处理块内部跳转到当前块是非法的 例1 下面的语句是非法的BEGINGOTOinnerblock Begin end gotoinsideif ifx 3then insertinto endif end 例2 下面的语句是非法的beginifx 3then gotonextcondition else endif end 例3 下面的语句是非法的DECLAREV ROOMrooms rowtype BeginSelect intov roomfromroomswhererowid 1 insertintotemp table char col values foundarow exceptionwhenno data foundthengotoinsert end 为循环设定标签循环本身是可以被设定标签的 如果进行了设定 那么可以在EXIT语句中使用该标签指明要退出哪个循环 例如 begin forv outerindexin1 50loop forv innerindexin2 10loop ifv outerindex 40thenexitouter endif endloopinner endloopouter 块标号可以为每一个PL SQL块前面加一个块的标号 以解决变量的二义性问题 例如 declaredeptnonumber 20 begindeclaredeptnonumner 10 beginupdateempsetsal sal 1 1wheredeptno sample deptno commit end end 注意 如果不用标号加以限制 系统就会产生二义性 4 异常处理 许多编写得很好的程序都必须能够正确处理各种出错情况 并且尽可能地从错误中进行恢复 异常处理方法是程序对运行时刻错误作出反应并进行处理的方法 当引发一个异常情况时 控制便会转给块的异常处理部分 异常处理部分的语法如下 EXCEPTIONWHEN异常情况1THEN语句序列1 WHEN异常情况2THEN语句序列2 WHENOTHERSTHEN语句序列3 END OTHERS异常处理将对所有引发的异常情况执行其代码 它应该是块中最后一个处理语句 确保所有的错误都被检测到 但OTHERS只是简单地记录发生了错误 而没有记录发生的是哪一个错误 我们可以在OTHERS中用预定义函数SQLCODE和SQLERRM来决定引发异常处理的是哪个错误 异常情况包括系统预定义的异常情况 用户自定义的异常情况 系统预定义的异常情况ORACLE有一些预定义的异常情况和大多数通常的ORACLE错误是对应的 这些异常情况所使用的标识符在包STANDARD90中进行定义 在程序的异常处理部分直接对它们进行处理 例如 declaremy m type beginselectcommintomy commfromempwhereempno 7788 exceptionwhenno data foundthendbms output put line nodata whentoo many rowsthendbms output put line toomanyrow whenothersthennull end 用户自定义的异常情况 用户自定义的异常情况是程序定义的一个错误 程序所定义的这个错误并不一定非是一个ORACLE错误 它可能是与数据相关的一个错误 用户定义的异常情况的处理分三步 定义异常情况用户定义的异常情况是在PL SQL块的说明部分进行定义的 和变量相类似 异常情况有一个类型和作用域 例如 my exceptionexception 触发异常情况当一个异常情况相关的错误出现时 就会引发该异常情况 用户定义的异常情况是通过显式使用RAISE语句来引发的 而预定义的异常情况是当相关的ORACLE错误发生时被隐式触发的 例如 RAISEMY EXCEPTION 在程序的异常处理部分对定义的异常情况进行处理 例如 WHENMY EXCEPTIONTHEN 例1 DECLAREtin rectin rowtype v passwduser passwd type err psEXCEPTION BEGINselect intotin recfromtin selectpasswdintov passwdfromuserwhereuserid tin rec uid iftin rec ps v passwdtheninsertintotoutvalues loginok elseraiseerr ps endif exceptionwhenerr pstheninsertintotoutvalues passworderror whenno data foundtheninsertintotoutvalues useriderror end 例2 declaree toomanystudentexception v currentstudentnumber 3 v maxstudentnumber 3 v errorcodenumber v errortextvarchar2 200 beginselectcurrent student max studentsintov currentstudent v maxstudentfromclasseswheredepartment HIS andcourse 101 ifv currentstudent v maxstudentthenraisee toomanyexception endif exceptionwhenno data foundortoo many rowsthendbms output put line 发生系统预定义错误 whene toomanyexceptiontheninsertintolog table info values history101has v currentstudent whenothersthenv errorcoed sqlcode v errortext substr sqlerrm 1 200 insertintolog table code message info values v errorcode v errortext oracleerroroccured end EXCEPTION INITPRAGMA 用户可以将一个经过命名的异常情况和一个特别的ORACLE错误相关联 这会使您专门能够捕获此错误 而不是通过OTHERS处理器来进行捕获 在PL SQL中是通过EXCEPTION INITPRAGMA来实现的 语法 PRAGMAEXCEPTION INIT 异常名 错误代码 此语句必须在说明部分中定义 在异常处理部分对此异常进行处理 例如 declaree missingnullexception pragmaexception init e missingnull 1400 begininsertintostudents id values null exceptionwhene missingnulltheninsertintolog table info values ORA 1400occurred end 每次发生PRAGMAEXCEPTION INIT时 一个ORACLE错误只能和一个用户定义的异常情况相关联 使用RAISE APPLICATION ERROR 用户可以使用内置函数RAISE APPLICATION ERROR以创建自己的错误消息 这可能比已命名的异常情况更具说明性 语法 RAISE APPLICATION ERROR 错误号码 错误消息 错误代码从 20000到 20999之间错误信息是与此错误相关的正文 不多于512字节 例如 下面的过程将为一个新生注册以前检查是否在班级中有足够的地方容纳他 Createorreplaceprocedureregister P studentidinstudent id type P departmentinclasses department type P courseinclasses course type IsV currentstudentnumber V maxstudentnumber BeginSelectcurrent student max studentintoV currentstudent v maxstudentFromclassesWherecourse p courseanddepartment p department Ifv currentstudent 1 v maxstudentthenRaise application error 20000 cannotaddmorestudentto p department p course endif exceptionwhenno data foundthenraise application error 20001 p department p course doesnotexist end 5 游标 什么是游标 为了处理SQL语句 ORACLE必须分配一片内存区域 这就是上下文区 上下文包含了完成该处理所比需的信息 其中包括语句要处理的行的数目 一个指向语句被分析以后产生的表示形式的指针 以及查询的活动集 游标是一个指向上下文区的句柄或指针 通过游标 PL SQL程序可以控制上下文区和在处理语句时上下文区会发生些什么事情 游标主要用来处理一个查询语句有多行数据返回的情况 Oracle使用PrivateSQLArea的工作区执行SQL语句 并保存语句执行结果和相关的状态信息 游标是一个PL SQL结构 利用游标可以命名这些工作区 并通过游标访问工作区中的信息 游标有两种类型 显式游标 隐式游标 显式游标 显式游标处理的四个步骤当某一条查询语句 SELECT 返回多条记录时 必须显式地定义游标以处理每一行 其他的SQL语句都使用隐式游标 显式游标的处理包括四个步骤 定义游标 在说明部分 打开游标 在语句执行部分 将当前行结果提取到PL SQL变量中 在语句执行部分 关闭游标 在语句执行部分 定义游标定义游标定义了游标的名字 并将该游标和一个SELECT语句相关联 语法 CURSOR游标名ISSELECT语句 注意 此SELECT语句不能包含INTO子句 例如 cursorc1isselectename deptnofromemp 例 declarev departmentclasses department type v courseclasses course type cursorc classesisselect fromclasseswheredepartment v departmentandcourse v course 可以在定义游标的语句中引用前面已定义的变量 打开游标在定义了游标之后 就可以打开游标了 打开游标就是执行游标所对应的SELECT语句 将其查询结果放入工作区 并且指针指向工作区的首部 语法 OPEN游标名 例如 OPENC CLASSES 3 提取值到变量取值工作是将游标中的数据取出一行 放入指定的输出变量中 系统在每执行一次FETCH语句时 都将指针顺序下移一行 使下一行成为当前行 循环执行FETCH语句 直到整个活动集都被返回 语法 FETCH游标名INTO变量1 变量2 或 FETCH游标名INTOPL SQL RECORD 4 关闭游标完成对SELECT语句的处理之后 就可以关闭游标 游标关闭后 系统释放与该游标关联的资源 并使该游标的工作区变成无效 语法 CLOSE游标名 例如 CLOSEC CLASSES 游标的属性从游标工作区中逐一提取数据 可以在循环中完成 但循环的开始以及结束 必须以游标的属性为依据 游标属性及其描述如下 例1 用简单循环 declarev deptnoemp deptno type 例2 用WHILE循环 declarev deptnoemp deptno type 游标的FOR循环用游标的FOR循环 是对游标操作的一种简便方法 使用游标的FOR循环 则系统隐式地定义了一个 ROWTYPE类型的记录 作为循环的计数器 游标的FOR循环自动地打开游标取数据关闭游标语法 FOR记录名IN游标名LOOP语句1 语句2 ENDLOOP 例1 declarev deptnoemp deptno type 通过游标操纵数据库通过从游标工作区中抽取出来的数据 可以对数据库中的数据进行操纵 包括修改 删除操作 要想操纵数据库中的数据 在定义游标时 必须加上FOR