OraclePLSQL基础培训课件(PPT 162页)

1、Oracle PL/SQL 基础培训.第1页，共162页。目录 课程介绍 基础知识 循环执行 条件转移 游标与数据 寄存对象 内置函数 异常处理.第2页，共162页。课程介绍教程概述本教程假设读者已了解关系型数据库基本原理，明白表、视图、主键、索引、外键、约束、关联等基本概念，熟悉Oracle SQL本教程定位是Oracle PL/SQL简明、实用教程，偏向于基本PL/SQL开发，若进一步学习PL/SQL高级功能，请参阅其他教程或书籍文档课程目标是完成课程后可进行大部分Oracle PL/SQL开发(编写)适用对象学习过Oracle SQL，未使用过Oracle PL/SQL的读者使用过SQL

2、 Server的Transact-SQL或其他数据库版本的”T-SQL”，未使用过Oracle PL/SQL的读者.第3页，共162页。目录 课程介绍 基础知识 循环执行 条件转移 游标与数据 寄存对象 内置函数 异常处理.第4页，共162页。基础知识PL/SQL介绍Hello WorldPL/SQL块分析变量声明运算符号.第5页，共162页。基础知识-PL/SQL介绍PL/SQL概述PL/SQL是Procedural Language extensions to SQL简写将过程结构与SQL无缝集成，实现数据结构和算法，最终实现对数据库的数据处理和其他相关功能PL/SQL是块结构化语言，块是

3、PL/SQL基本单元PL/SQL扩充变量和类型条件语句循环语句.第6页，共162页。基础知识-Hello World参考代码.第7页，共162页。基础知识-PL/SQL块分析基本语法语法分析PL/SQL是块结构程序，使用DECLARE、BEGIN、EXCEPTION、END关键字声明块，块包含声明部分、执行部分、异常部分使用- 做单行注释,/* */做多行注释在DECLARE部分声明变量NULL语句表示什么都不做RETURN语句，显示退出PL/SQL块执行PL/SQL块可以嵌套.第8页，共162页。基础知识声明变量基本语法变量类型声明变量示例变量作用域.第9页，共162页。语法说明variab

4、le_name 变量名称CONSTANT 声明为常量Datatype 数据类型NOT NULL 变量不能为空(必须有赋值):= 赋值符号DEFAULT 赋默认值initial_value 初始值基础知识-声明变量-基本语法.第10页，共162页。基础知识-声明变量-变量类型数字：NUMBER(p,s) ：p是数字长度，s是小数保留位，如NUMBER(7,2)，就是最多保存7位的数字，其中小数点前是5位，小数点后是2位。p，s可以为空，一般为空字符VARCHAR2：存放可变长字符串，有最大长度限制CHAR：字符型，固定长度CLOB：大对象字符LONG：长字符型日期DATE布尔BOOLEAN二进制

5、RAW：存储多媒体数据，如图象、声音、视频等BLOB：大对象如图象、声音、视频等.第11页，共162页。基础知识-声明变量-变量类型引用：%TYPE ：引用某个表某个字段的类型和长度，%ROWTYPE ：引用某个表全部字段的类型和长度记录Record Type：复合多个其他类型，其中定义Record Type语法如下TYPE record_type is RECORD( record_field1 field1type NOT NULL := expr1 ; record_field2 field2type NOT NULL := expr2 ; record_fieldn fieldntyp

6、e NOT NULL := exprn ;)record_type是定义的新的记录类型record_field1 record_fieldn是记录类型字段名称Field1type fieldntype是各字段类型NOT NULL可选项，指定字段是否可以为空Express1expressn给各字段赋初始值.第12页，共162页。基础知识-声明变量-声明变量示例变量声明：.第13页，共162页。基础知识-声明变量-声明变量示例变量赋值：.第14页，共162页。基础知识-声明变量-变量作用域作用域：PL/SQL变量作用域从变量声明开始，到代码块结束子块声明的变量，对块不可见字块同名变量会覆盖父块变量

7、定义父块时指定块名，在字块通过父块名.变量名，引用父块同名变量.第15页，共162页。基础知识-运算符号算术运算符关系运算符逻辑运算符.第16页，共162页。基础知识-运算符号算术运算符号+ 加 1+1- 减 2-1* 乘 2*3/ 除 4/2| 字符连接 Beijing | 2008.第17页，共162页。基础知识-运算符号关系运算符号= 等于 ，1=1 不等于， 21 大于，21 小于， 1= 大于等于， 2=1= 小于等于，1=2IS NULL 为空，没有值，不是空字符串IS NOT NULL 不为空，有值BETWEEN AND 检索两值之间的内容，2 BETWEEN 1 AND 3IN

8、检索匹配列表中的值LIKE检索匹配字符样式的数据.第18页，共162页。基础知识-运算符号逻辑运算符号AND 两个为真则结果为真OR 一个为真即为真NOT 取相反的逻辑值.第19页，共162页。目录 课程介绍 基础知识 循环执行 条件转移 游标与数据 寄存对象 内置函数 异常处理.第20页，共162页。条件转移IF THENIF THEN ELSEIF THEN ELSIF.第21页，共162页。条件转移-IF THEN语法IF 条件判定为真 THEN 可执行语句块END IF;示例.第22页，共162页。条件转移-IF THEN示例.第23页，共162页。条件转移-IF THEN ELSE语

9、法IF 条件判定为真 THEN 可执行语句块1ELSE 可执行语句块2END IF;示例.第24页，共162页。条件转移-IF THEN ELSE示例.第25页，共162页。条件转移-IF THEN ELSIF语法IF 条件判定为真 THEN 可执行语句块1ELSIF 可执行语句块2.ELSIF 可执行语句块NEND IF;示例.第26页，共162页。条件转移-IF THEN ELSIF示例.第27页，共162页。目录 课程介绍 基础知识 循环执行 条件转移 游标与数据 寄存对象 内置函数 异常处理.第28页，共162页。循环执行LOOPWHILE LOOPFOR LOOP.第29页，共162

10、页。循环执行-LOOP语法LOOP 可执行语句块EXIT WHEN 条件判定END LOOP;说明LOOP是无限循环，可使用EXIT显式退出LOOP循环EXIT有两种格式:IF.THEN EXIT END IF和EXIT WHENEXIT语句必须放在循环内EXIT可以出现多次，第一个EXIT执行后，马上退出循环EXIT只能退出循环，不能退出PL/SQL块，要退出PL/SQL块，使用RETURN;.第30页，共162页。循环执行-LOOP示例.第31页，共162页。循环执行-LOOP示例.第32页，共162页。循环执行-WHILE LOOP语法WHILE 条件判定 LOOP 可执行语句块END

11、LOOP;说明每次执行“可执行语句块”前，先判断“条件判定”是否为TRUE，因此循环有可能一次也不执行如果“条件判定”一直为TRUE，则循环无限进行下去，注意在“可执行语句”部分设置“条件判定”可用EXIT显式退出当前循环，EXIT有两种格式:IF.THEN EXIT END IF和EXIT WHENEXIT语句必须放在循环内EXIT可以出现多次，第一个EXIT执行后，马上退出循环EXIT只能退出循环，不能退出PL/SQL块，要退出PL/SQL块，使用RETURN;.第33页，共162页。循环执行-WHILE LOOP示例.第34页，共162页。循环执行-WHILE LOOP示例.第35页，共

12、162页。循环执行-WHILE LOOP示例.第36页，共162页。循环执行-WHILE语法FOR 计数器变量 IN REVERSE 低界值.高界值 LOOP 可执行语句块END LOOP;说明是每次执行“可执行语句块”前，先判断“条件判定”是否为TRUE，因此循环有可能一次也不执行可用EXIT显式退出当前循环，EXIT有两种格式:IF.THEN EXIT END IF和EXIT WHENEXIT语句必须放在循环内EXIT可以出现多次，第一个EXIT执行后，马上退出循环EXIT只能退出循环，不能退出PL/SQL块，要退出PL/SQL块，使用RETURN;.第37页，共162页。循环执行-FOR

13、 LOOP示例.第38页，共162页。循环执行-WHILE LOOP示例.第39页，共162页。循环执行-WHILE LOOP示例.第40页，共162页。目录 课程介绍 基础知识 循环执行 条件转移 游标与数据 寄存对象 内置函数 异常处理.第41页，共162页。游标与数据概念分类属性动作声明游标使用游标隐式游标SELECT INTO.第42页，共162页。游标与数据-游标概念概念游标是一个机制，通过这个机制可以给一个SQL语句命名，并操作该SQL返回的数据或者使用该SQL返回的数据操作其他的数据分类显式游标:显式声明游标，且由代码管理游标生命周期隐式游标:非显式声明游标，由系统管理游标生命周

14、期.第43页，共162页。游标与数据-属性动作属性清单操作动作.第44页，共162页。游标与数据-声明游标语法示例声明游标指向所有部门声明游标指向所有参数指定部门的员工.第45页，共162页。游标与数据-使用游标单重循环LOOP.END LOOPWHILE.LOOP.END LOOPFOR.LOOP.END LOOP.第46页，共162页。游标与数据-使用游标-LOOP.END LOOP关键代码块.第47页，共162页。游标与数据-使用游标-LOOP.END LOOP示例.第48页，共162页。游标与数据-使用游标-LOOP.END LOOP示例.第49页，共162页。游标与数据-使用游标-W

15、HILE.LOOP.END LOOP关键代码块.第50页，共162页。游标与数据-使用游标-WHILE.LOOP.END LOOP示例.第51页，共162页。游标与数据-使用游标-WHILE.LOOP.END LOOP示例.第52页，共162页。游标与数据-使用游标-FOR.LOOP.END LOOP关键代码块.第53页，共162页。游标与数据-使用游标-FOR.LOOP.END LOOP示例.第54页，共162页。游标与数据-使用游标-FOR.LOOP.END LOOP示例.第55页，共162页。游标与数据-使用游标-FOR.LOOP.END LOOP示例.第56页，共162页。游标与数据-

16、使用游标-FOR.LOOP.END LOOP示例.第57页，共162页。游标与数据-使用游标多重循环FOR.LOOP.END LOOP.第58页，共162页。游标与数据-使用游标-FOR.LOOP.END LOOP示例.第59页，共162页。游标与数据-隐式游标SELECT INTO背景从数据库中获取数据到变量时，如果相关SQL只返回低于1条记录，使用显式游标过于麻烦，Oracle提供SELECT INTO快速实现改功能。所有DML语句执行时创建一个隐式游标，所以SELECT INTO也是游标，不过其创建和打开、关闭全部由ORACLE自动完成语法.第60页，共162页。游标与数据-隐式游标SE

17、LECT INTO示例.第61页，共162页。游标与数据-隐式游标SELECT INTO序列取序列值，序列是一种数据库对象，就像一个自增长的计数器可以创建时指定初始值和最大值，以及增长步长(均匀增量)序列有currentVal和nextVal属性分别是序列当前值和序列下一值可通过s_seq_name.currentVal和s_seq_name.nextVal获取值每次调用nextVal，序列就会自动按设定步长增长语法.第62页，共162页。游标与数据-隐式游标SELECT INTO示例.第63页，共162页。游标与数据-隐式游标SELECT INTO示例.第64页，共162页。游标与数据-隐式

18、游标SELECT INTO示例.第65页，共162页。目录 课程介绍 基础知识 循环执行 条件转移 游标与数据 寄存对象 内置函数 异常处理.第66页，共162页。异常处理什么是异常预定义异常自定义异常处理异常关键字OTHERS编译指令EXCEPTION_INIT.第67页，共162页。异常处理-什么是异常概述PL/SQL运行时有可能会发生错误或警告，如除数为零，没有找到数据，DML操作违反数据库对象约束等，这种运行时错误就称为异常，异常是一种错误形态ORACLE将所有已知底层错误用一系列错误代码标记，并对应相应错误信息描述ORACLE将部分已知底层SQL或者硬件错误，封装成异常，并提供异常处

19、理机制处理异常对于业务逻辑上的错误，需要用户自定义异常来描述和控制异常机制实现程序主逻辑和错误处理逻辑分离，使程序可读性和健壮性得到进一步提高分类预定义异常:由Oracle数据库自带，在使用时不用先声明自定义异常:由开发人员自定义，在使用前需要先声明.第68页，共162页。异常处理-预定义异常清单.第69页，共162页。异常处理-自定义异常概述将业务逻辑错误封装成异常，这种异常就是自定义异常自定义异常在使用前必须先声明，可在PL/SQL代码块声明部分和包中声明自定义异常异常不是变量，声明不能赋值异常作用域和变量一直，在一个块和其子块可见，子块声明与父块同名异常，会覆盖父块声明的异常语法.第70

20、页，共162页。异常处理-处理异常示例自定义异常.第71页，共162页。异常处理-处理异常示例自定义异常.第72页，共162页。异常处理-处理异常异常触发异常捕获与传播.第73页，共162页。异常处理-处理异常异常触发对于预定义异常，一个与异常相关的错误发生的时候，就会自动触发该异常，也可以使用RAISE语句显式触发对于自定义异常，必须在代码中使用RAISE语句显式触发.第74页，共162页。异常处理-处理异常示例预定义异常.第75页，共162页。异常处理-处理异常示例自定义异常.第76页，共162页。异常处理-处理异常-异常捕获与传播处理原则只有PL/SQL代码块执行部分触发的异常才会被相应

21、代码块合适的异常处理部分捕获如果触发异常的PL/SQL代码块没有合适异常处理部分，则异常会传播到该代码块的父代码块的合适的异常处理部分如果在父代码块仍未找到合适的异常处理部分，则继续往上传播异常，直至找到合适的异常处理部分找到合适的异常处理部分并执行后，同一语句块的其他异常处理部分不会被执行，即使执行的合适的异常处理部分再次触发异常如果最后仍然没有照到合适的异常处理部分，则将控制权交给当前调用环境示例执行部分触发的异常声明部分触发的异常异常处理部分触发的异常.第77页，共162页。异常处理-处理异常-异常捕获与传播示例执行部分触发的异常.第78页，共162页。异常处理-处理异常-异常捕获与传播

22、示例声明部分触发的异常.第79页，共162页。异常处理-处理异常-异常捕获与传播示例异常处理部分触发的异常.第80页，共162页。异常处理-关键字OTHERS用途代表未知异常在无法预测或不想指明要捕获的异常类型时使用一个PL/SQL代码块如果有多个异常处理语句，带OTHERS的异常处理语句必须放在最后.第81页，共162页。异常处理-处理异常-异常捕获与传播示例异常处理部分触发的异常.第82页，共162页。异常处理-处理异常-异常捕获与传播示例异常处理部分触发的异常.第83页，共162页。异常处理-编译指令EXCEPTION_INIT用途将ORACLE错误代码与自定义异常关联发生相应错误时，转

23、换为异常，使用异常机制处理错误语法.第84页，共162页。异常处理-处理异常-编译指令EXCEPTION_INIT示例ORA-1843，月份错误.第85页，共162页。异常处理-处理异常-编译指令EXCEPTION_INIT示例ORA-1476，除数为零.第86页，共162页。目录 课程介绍 基础知识 循环执行 条件转移 游标与数据 寄存对象 内置函数 异常处理.第87页，共162页。寄存对象过程函数包触发器.第88页，共162页。寄存对象-过程什么是过程声明过程调用过程删除过程.第89页，共162页。寄存对象-过程-什么是过程概念过程是特殊的PL/SQL代码块，在普通代码块基础上加上过程声明

24、语句Procedure.再加上参数，代码块就变为过程过程可以编译到数据库中，编译后自动保存在数据库中过程可以通过名称和包、schema等限定词被其他代码调用过程参数有IN/OUT/IN OUT三种类型，其中IN类型可以被过程体引用，但不能改写值；OUT类型不可被过程体引用，但可改写值；IN OUT 既可以被过程体引用，又可以改写值.第90页，共162页。寄存对象-过程-声明过程语法是.第91页，共162页。寄存对象-过程-声明过程示例新建一个部门，名称由参数指定.第92页，共162页。寄存对象-过程-声明过程示例复制参数1指定的部门及其员工，部门名称和员工姓名在原来名称前加上”copied b

25、y 参数2 ”，员工号码为序列值.第93页，共162页。寄存对象-过程-声明过程示例复制参数1指定的部门及其员工，部门名称和员工姓名在原来名称前加上”copied by 参数2 ”，员工号码为序列值(续).第94页，共162页。寄存对象-过程-声明过程示例将参数1指定的部门，工资低于参数2指定的工资，增加参数3，并返回是否更新成功，以及相应的提示信息.第95页，共162页。寄存对象-过程-声明过程示例将参数1指定的部门，工资低于参数2指定的工资，增加参数3，并返回是否更新成功，以及相应的提示信息(续).第96页，共162页。寄存对象-过程-调用过程方法位置标志法带名标志法.第97页，共162页

26、。寄存对象-过程-调用过程示例位置标志法.第98页，共162页。寄存对象-过程-调用过程示例带名标志法.第99页，共162页。寄存对象-过程-删除过程语法DROP PROCEDURE procedure_name;示例DROP PROCEDURE p_copy_dept_and_emp;.第100页，共162页。寄存对象-函数什么是函数声明函数调用函数删除函数.第101页，共162页。寄存对象-函数-什么是函数概念过程是特殊的PL/SQL代码块，在普通代码块基础上加上过程声明语句Function. Return Type再加上参数，代码块就变为函数函数可以编译到数据库中，编译后自动保存在数据库

27、中函数可以通过名称和包、schema等限定词被其他代码调用函数参数有IN/OUT/IN OUT三种类型，其中IN类型可以被函数体引用，但不能改写值；OUT类型不可被函数体引用，但可改写值；IN OUT 既可以被函数体引用，又可以改写值函数有返回值，只能返回一个值.第102页，共162页。寄存对象-函数语法.第103页，共162页。寄存对象-函数示例将参数1指定的部门的所有员工姓名连接起来，中间用逗号隔开.第104页，共162页。寄存对象-函数-调用函数方法位置标志法带名标志法.第105页，共162页。寄存对象-函数-调用函数示例位置标志法.第106页，共162页。寄存对象-函数-调用函数示例带

28、名标志法.第107页，共162页。寄存对象-函数-删除函数语法DROP FUNCTION procedure_name;示例DROP FUNCTION fn_get_emp_name;.第108页，共162页。寄存对象-包什么是包声明包调用包删除包.第109页，共162页。寄存对象-包-什么是包概念包是是特殊的PL/SQL代码块，是过程、函数、变量的容器包可以编译到数据库中，编译后自动保存在数据库中可以通过包名、schema等限定词调用包中的其他数据库对象包分包头和包体，通过PACKAGE和PACKAGE BODY关键词分别声明包头和包体.第110页，共162页。寄存对象-包头语法.第111页

29、，共162页。寄存对象-包体示例.第112页，共162页。寄存对象-包-声明包示例包头.第113页，共162页。寄存对象-包-声明包示例包体.第114页，共162页。寄存对象-包-调用包对象方法位置标志法带名标志法.第115页，共162页。寄存对象-包-调用函数示例位置标志法.第116页，共162页。寄存对象-包-调用函数示例带名标志法.第117页，共162页。寄存对象-包-删除包语法DROP PACKAGE package_name;示例DROP PACKAGE PKG_SQL_APP;.第118页，共162页。寄存对象-触发器什么是触发器声明触发器调用触发器删除触发器.第119页，共162

30、页。寄存对象-触发器-什么是触发器概念过程是特殊的PL/SQL代码块，在普通代码块基础上加上声明语句Trigger. Before/After On等关键字后，代码块就变为触发器触发器可以编译到数据库中，编译后自动保存在数据库中触发器在按定义时指定表数据变化的事件中自动被调用表数据变化事件包括新增、更新、删除.第120页，共162页。寄存对象-触发器-声明触发器语法.第121页，共162页。寄存对象-触发器-声明触发器示例.第122页，共162页。寄存对象-触发器-声明触发器示例.第123页，共162页。寄存对象-触发器-声明触发器示例.第124页，共162页。寄存对象-触发器-调用触发器方法

31、:系统自动调用表数据新增表数据更新表数据删除.第125页，共162页。寄存对象-触发器-删除触发器语法DROP TRIGGER trigger_name;示例DROP TRIGGER trg_insert_emp_his;.第126页，共162页。目录 课程介绍 基础知识 循环执行 条件转移 游标与数据 寄存对象 内置函数 异常处理.第127页，共162页。内置函数概念内置函数是Oracle提供的、预定义好的函数内置函数不用声明或引用包就可以使用分类多行函数，对查询返回的数据的多行起作用，多行函数在SELECT语句中，配合GROUP BY一起使用单行函数，对查询返回的数据的每一行起作用，单行函

32、数也可以在PL/SQL中直接使用，将函数返回值赋值给变量.第128页，共162页。内置函数多行函数COUNTAVGSUMMINMAX.第129页，共162页。内置函数单行函数数值函数字符函数转换函数日期函数杂项函数.第130页，共162页。内置函数-单行函数数值函数ABSSIGINCEILFLOORROUNDTRUNCMOD.第131页，共162页。内置函数-单行函数-数值函数-ABS用途返回绝对值语法ABS(number_param).第132页，共162页。内置函数-单行函数-数值函数-TO_NUMBER示例.第133页，共162页。内置函数-单行函数字符函数LENGTHLENGTHBLO

33、WERUPPERINSTRSUBSTRREPLACELPADRPADLTRIMRTRIM.第134页，共162页。内置函数-单行函数-字符函数-LENGTH用途返回字符长度语法LENGTH(string_param).第135页，共162页。内置函数-单行函数-字符函数-LENGTH示例.第136页，共162页。内置函数-单行函数-字符函数-LENGTHB用途返回字符按字节计算长度语法LENGTHB(string_param).第137页，共162页。内置函数-单行函数-字符函数-LENGTHB示例.第138页，共162页。内置函数-单行函数-字符函数-LOWER用途将字符转为小写，参数字符串

34、中可包含非字符，如数字，特殊符号等语法LOWER(string_param).第139页，共162页。内置函数-单行函数-字符函数-LOWER示例.第140页，共162页。内置函数-单行函数-字符函数-UPPER用途将字符转为大写语法LOWER(string_param).第141页，共162页。内置函数-单行函数-字符函数-UPPER示例.第142页，共162页。内置函数-单行函数转换函数TO_NUMBERTO_CHARTO_DATE.第143页，共162页。内置函数-单行函数-数值函数-TO_NUMBER用途将字符转为数字型语法TO_NUMBER(param).第144页，共162页。内置函数-单行函数-数值函数-TO_NUMB