OraclePLSQL基础培训

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

2、过SQL Server的Transact-SQL或其他数据库版本的”T-SQL”，未使用过Oracle PL/SQL的读者4目录目录u 课程介绍u 基础知识u 循环执行u 条件转移u 游标与数据u 寄存对象u 内置函数u 异常处理5基础知识基础知识-PL/SQL-PL/SQL介绍介绍PL/SQL概述 PL/SQL是Procedural Language extensions to SQL简写 将过程结构与SQL无缝集成，实现数据结构和算法，最终实现对数据库的数据处理和其他相关功能 PL/SQL是块结构化语言，块是PL/SQL基本单元PL/SQL扩充 变量和类型 条件语句 循环语句6基础知识基础

3、知识-Hello World-Hello World参考代码7目录目录u 课程介绍u 基础知识u 循环执行u 条件转移u 游标与数据u 寄存对象u 内置函数u 异常处理8条件转移条件转移 IF THEN IF THEN ELSE IF THEN ELSIF9条件转移条件转移-IF THEN-IF THEN语法IF 条件判定为真 THEN 可执行语句块END IF;示例10条件转移条件转移-IF THEN-IF THEN示例11条件转移条件转移-IF THEN ELSE-IF THEN ELSE语法IF 条件判定为真 THEN 可执行语句块1ELSE 可执行语句块2END IF;示例12条件转移

4、条件转移-IF THEN ELSE-IF THEN ELSE示例13条件转移条件转移-IF THEN ELSIF-IF THEN ELSIF语法IF 条件判定为真 THEN 可执行语句块1ELSIF 可执行语句块2.ELSIF 可执行语句块NEND IF;示例14目录目录u 课程介绍u 基础知识u 循环执行u 条件转移u 游标与数据u 寄存对象u 内置函数u 异常处理15循环执行循环执行 LOOP WHILE LOOP FOR LOOP16循环执行循环执行-LOOP-LOOP语法LOOP 可执行语句块EXIT WHEN 条件判定END LOOP;说明LOOP是无限循环，可使用EXIT显式退出L

5、OOP循环EXIT有两种格式:IF.THEN EXIT END IF和EXIT WHENEXIT语句必须放在循环内EXIT可以出现多次，第一个EXIT执行后，马上退出循环EXIT只能退出循环，不能退出PL/SQL块，要退出PL/SQL块，使用RETURN;17循环执行循环执行-LOOP-LOOP示例18循环执行循环执行-LOOP-LOOP示例19循环执行循环执行-WHILE LOOP-WHILE LOOP语法WHILE 条件判定 LOOP 可执行语句块END LOOP;说明每次执行“可执行语句块”前，先判断“条件判定”是否为TRUE，因此循环有可能一次也不执行如果“条件判定”一直为TRUE，则

6、循环无限进行下去，注意在“可执行语句”部分设置“条件判定”可用EXIT显式退出当前循环，EXIT有两种格式:IF.THEN EXIT END IF和EXIT WHENEXIT语句必须放在循环内EXIT可以出现多次，第一个EXIT执行后，马上退出循环EXIT只能退出循环，不能退出PL/SQL块，要退出PL/SQL块，使用RETURN;20循环执行循环执行-WHILE LOOP-WHILE LOOP示例21循环执行循环执行-FOR LOOP-FOR LOOP语法FOR 计数器变量 IN REVERSE 低界值.高界值 LOOP 可执行语句块END LOOP;说明是每次执行“可执行语句块”前，先判断

7、“条件判定”是否为TRUE，因此循环有可能一次也不执行 可用EXIT显式退出当前循环，EXIT有两种格式:IF.THEN EXIT END IF和EXIT WHEN EXIT语句必须放在循环内 EXIT可以出现多次，第一个EXIT执行后，马上退出循环 EXIT只能退出循环，不能退出PL/SQL块，要退出PL/SQL块，使用RETURN;22循环执行循环执行-FOR LOOP-FOR LOOP示例23目录目录u 课程介绍u 基础知识u 循环执行u 条件转移u 游标与数据u 寄存对象u 内置函数u 异常处理24游标与数据游标与数据概念分类属性动作声明游标使用游标隐式游标SELECT INTO25游

8、标与数据游标与数据- -游标概念游标概念概念 游标是一个机制，通过这个机制可以给一个SQL语句命名，并操作该SQL返回的数据或者使用该SQL返回的数据操作其他的数据分类 显式游标:显式声明游标，且由代码管理游标生命周期 隐式游标:非显式声明游标，由系统管理游标生命周期26游标与数据游标与数据- -属性动作属性动作属性清单操作动作27游标与数据游标与数据- -声明游标声明游标语法示例 声明游标指向所有部门 声明游标指向所有参数指定部门的员工28游标与数据游标与数据- -使用游标使用游标单重循环LOOP.END LOOPWHILE.LOOP.END LOOPFOR.LOOP.END LOOP29游

9、标与数据游标与数据- -使用游标使用游标- -LOOP.END LOOP示例30游标与数据游标与数据- -使用游标使用游标- -WHILE.LOOP.END LOOP示例31游标与数据游标与数据- -使用游标使用游标- -FOR.LOOP.END LOOP示例32游标与数据游标与数据- -使用游标使用游标多重循环FOR.LOOP.END LOOP33游标与数据游标与数据- -使用游标使用游标- -FOR.LOOP.END LOOP示例34游标与数据游标与数据- -隐式游标隐式游标SELECT INTOSELECT INTO背景从数据库中获取数据到变量时，如果相关SQL只返回低于1条记录，使用显

10、式游标过于麻烦，Oracle提供SELECT INTO快速实现改功能。所有DML语句执行时创建一个隐式游标，所以SELECT INTO也是游标，不过其创建和打开、关闭全部由ORACLE自动完成语法35游标与数据游标与数据- -隐式游标隐式游标SELECT INTOSELECT INTO示例36游标与数据游标与数据- -隐式游标隐式游标SELECT INTOSELECT INTO序列取序列值，序列是一种数据库对象，就像一个自增长的计数器可以创建时指定初始值和最大值，以及增长步长(均匀增量)序列有currentVal和nextVal属性分别是序列当前值和序列下一值可通过s_seq_name.cur

11、rentVal和s_seq_name.nextVal获取值每次调用nextVal，序列就会自动按设定步长增长语法37游标与数据游标与数据- -隐式游标隐式游标SELECT INTOSELECT INTO示例38隐式游标与显式游标的区别隐式游标与显式游标的区别显式游标 1.由用户显式声明 cursor 2.游标将指向活动集中的当前行 3.控制显式游标分为三个步骤 Open Fetch 提取 Close隐式游标 由Oracle数据库自动创建，名称是(SQL) ,主要用途是可以返回一个操作是否成功或失败. 1.有Oracle在内部声明，由系统管理 2.用于处理 -DML语句 -注意只能用于DML语句

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

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

14、处理异常处理- -处理异常处理异常示例自定义异常45异常处理异常处理- -关键字关键字OTHERSOTHERS用途代表未知异常在无法预测或不想指明要捕获的异常类型时使用一个PL/SQL代码块如果有多个异常处理语句，带OTHERS的异常处理语句必须放在最后46异常处理异常处理- -处理异常处理异常- -异常捕获与传播异常捕获与传播示例异常处理部分触发的异常47目录目录u 课程介绍u 基础知识u 循环执行u 条件转移u 游标与数据u 寄存对象u 内置函数u 异常处理48寄存对象寄存对象 过程 函数 包 触发器49寄存对象寄存对象- -过程过程什么是过程声明过程调用过程删除过程50寄存对象寄存对象-

15、 -过程过程- -什么是过程什么是过程概念过程是特殊的PL/SQL代码块，在普通代码块基础上加上过程声明语句Procedure.再加上参数，代码块就变为过程过程可以编译到数据库中，编译后自动保存在数据库中过程可以通过名称和包、schema等限定词被其他代码调用过程参数有IN/OUT/IN OUT三种类型，其中IN类型可以被过程体引用，但不能改写值；OUT类型不可被过程体引用，但可改写值；IN OUT 既可以被过程体引用，又可以改写值51寄存对象寄存对象- -过程过程- -声明过程声明过程语法是52寄存对象寄存对象- -过程过程- -声明过程声明过程示例新建一个部门，名称由参数指定53寄存对象寄

16、存对象- -过程过程- -声明过程声明过程示例将参数1指定的部门，工资低于参数2指定的工资，增加参数3，并返回是否更新成功，以及相应的提示信息(续)54寄存对象寄存对象- -过程过程- -调用过程调用过程方法位置标志法带名标志法55寄存对象寄存对象- -过程过程- -调用过程调用过程示例位置标志法56寄存对象寄存对象- -过程过程- -调用过程调用过程示例带名标志法57寄存对象寄存对象- -过程过程- -删除过程删除过程语法DROP PROCEDURE procedure_name;示例DROP PROCEDURE p_copy_dept_and_emp;58寄存对象寄存对象- -函数函数什么

17、是函数声明函数调用函数删除函数59寄存对象寄存对象- -函数函数- -什么是函数什么是函数概念过程是特殊的PL/SQL代码块，在普通代码块基础上加上过程声明语句Function. Return Type再加上参数，代码块就变为函数函数可以编译到数据库中，编译后自动保存在数据库中函数可以通过名称和包、schema等限定词被其他代码调用函数参数有IN/OUT/IN OUT三种类型，其中IN类型可以被函数体引用，但不能改写值；OUT类型不可被函数体引用，但可改写值；IN OUT 既可以被函数体引用，又可以改写值函数有返回值，只能返回一个值60寄存对象寄存对象- -函数函数语法61寄存对象寄存对象-

18、-函数函数示例将参数1指定的部门的所有员工姓名连接起来，中间用逗号隔开62寄存对象寄存对象- -函数函数- -调用函数调用函数方法位置标志法带名标志法63寄存对象寄存对象- -函数函数- -调用函数调用函数示例位置标志法64寄存对象寄存对象- -函数函数- -调用函数调用函数示例带名标志法65寄存对象寄存对象- -函数函数- -删除函数删除函数语法DROP FUNCTION procedure_name;示例DROP FUNCTION fn_get_emp_name;66寄存对象寄存对象- -包包什么是包声明包调用包删除包67寄存对象寄存对象- -包包- -什么是包什么是包概念包是是特殊的PL

19、/SQL代码块，是过程、函数、变量的容器包可以编译到数据库中，编译后自动保存在数据库中可以通过包名、schema等限定词调用包中的其他数据库对象包分包头和包体，通过PACKAGE和PACKAGE BODY关键词分别声明包头和包体68寄存对象寄存对象- -包头包头语法69寄存对象寄存对象- -包体包体示例70寄存对象寄存对象- -包包- -声明包声明包示例包头71寄存对象寄存对象- -包包- -调用包对象调用包对象方法位置标志法带名标志法72寄存对象寄存对象- -包包- -调用函数调用函数示例位置标志法73寄存对象寄存对象- -包包- -调用函数调用函数示例带名标志法74寄存对象寄存对象- -包

20、包- -删除包删除包语法DROP PACKAGE package_name;示例DROP PACKAGE PKG_SQL_APP;75目录目录u 课程介绍u 基础知识u 循环执行u 条件转移u 游标与数据u 寄存对象u 内置函数u 异常处理76内置函数内置函数概念内置函数是Oracle提供的、预定义好的函数内置函数不用声明或引用包就可以使用分类多行函数，对查询返回的数据的多行起作用，多行函数在SELECT语句中，配合GROUP BY一起使用单行函数，对查询返回的数据的每一行起作用，单行函数也可以在PL/SQL中直接使用，将函数返回值赋值给变量77内置函数内置函数多行函数COUNTAVGSUMM

21、INMAX78内置函数内置函数单行函数数值函数字符函数转换函数日期函数杂项函数79内置函数内置函数- -单行函数单行函数数值函数ABSSIGINCEILFLOORROUNDTRUNCMOD80内置函数内置函数- -单行函数单行函数- -数值函数数值函数-ABS-ABS用途返回绝对值语法ABS(number_param)81内置函数内置函数- -单行函数单行函数- -数值函数数值函数-TO_NUMBER-TO_NUMBER示例82内置函数内置函数- -单行函数单行函数字符函数LENGTHLENGTHBLOWERUPPERINSTRSUBSTRREPLACELPADRPADLTRIMRTRIM83

22、内置函数内置函数- -单行函数单行函数- -字符函数字符函数-LENGTH-LENGTH用途返回字符长度语法LENGTH(string_param)84内置函数内置函数- -单行函数单行函数- -字符函数字符函数-LENGTH-LENGTH示例85内置函数内置函数- -单行函数单行函数- -字符函数字符函数-LENGTHB-LENGTHB用途返回字符按字节计算长度语法LENGTHB(string_param)86内置函数内置函数- -单行函数单行函数- -字符函数字符函数-LENGTHB-LENGTHB示例87内置函数内置函数- -单行函数单行函数- -字符函数字符函数-LOWER-LOWER

23、用途将字符转为小写，参数字符串中可包含非字符，如数字，特殊符号等语法LOWER(string_param)88内置函数内置函数- -单行函数单行函数- -字符函数字符函数-LOWER-LOWER示例89内置函数内置函数- -单行函数单行函数- -字符函数字符函数-UPPER-UPPER用途将字符转为大写语法LOWER(string_param)90内置函数内置函数- -单行函数单行函数- -字符函数字符函数-UPPER-UPPER示例91内置函数内置函数- -单行函数单行函数转换函数TO_NUMBERTO_CHARTO_DATE92内置函数内置函数- -单行函数单行函数- -数值函数数值函数-

24、TO_NUMBER-TO_NUMBER用途将字符转为数字型语法TO_NUMBER(param)93内置函数内置函数- -单行函数单行函数- -数值函数数值函数-TO_NUMBER-TO_NUMBER示例94内置函数内置函数- -单行函数单行函数- -数值函数数值函数-TO_CHAR-TO_CHAR用途将数字转为字符将日期类转为字符型，按一定格式语法TO_CHAR(string_param)TO_CHAR(date_param,format,nlsparams)95内置函数内置函数- -单行函数单行函数- -数值函数数值函数-TO_CHAR-TO_CHAR示例96内置函数内置函数- -单行函数单行函数- -数值函数数值函数-TO_DATE-TO_DATE用途将字符类转为日期型，按一定格式语法TO_DATE(string_paramformat,nls_params)97内置函数内置函数- -单行函数单行函数- -数值函数数值函数-TO_DATE-TO_DATE示例98内置函数内置函数- -单行函数单行函数日期函数SYSDATETRUNCNEXT_DAYADD_MONTHSMONTHS_BETWEEN99内置函数内置函数- -单行函数单行函数- -数值函数数值函数-SYSDATE-SYSDATE用途返回当前系统时间，数据库所在服务器时间语法SYSDATE10