福建省电力公司oracle培训教材--PLSQL语言篇.ppt

ORACLE入门 PL SQL语言篇 技术支持部 本课程学习内容 PL SQL简介PL SQL数据类型 ORACLE的数据类型 ORACL内置的SQL函数PL SQL中使用SQLPL SQL中游标的使用动态PL SQLPL SQL的异常处理 PL SQL简介 PL SQL ProceduralLanguage SQL 即模块化的程序设计语言 用于从各种环境中访问ORACLE数据库 它具备了许多SQL中所没有的过程化属性方面的特点 主要包括 变量和类型控制结构 条件语句 循环语句 过程 函数游标异常处理 PL SQL程序的用途 无名块就是没有命名的PL SQL块 它可以嵌入某一个应用之中 存储过程 函数也就是命名了的PL SQL块 它可以接收参数 并且可以重复的被调用 触发器是与数据库中的表相关的PL SQL块 可以自动的触发 包命名了的PL SQL块 由一组相关的过程 函数和标识符组成 PL SQL的程序结构 PL SQL的基本单位是 块 Block 所有的PL SQL程序都是由一个或多个PL SQL块构成的 这些块可以相互进行嵌套 通常一个块完成程序的一个单元的工作 一个基本的块由三个部分组成 定义部分定义变量 常量 游标 异常处理可执行部分包括对数据库进行操作的SQL语句 以及对块中的语句进行组织 控制的PL SQL语句 异常处理 Exception 部分可执行部分中的语句 在执行过程中出错或出现非正常现象时 所做的响应处理 PL SQL数据类型 PL SQL数据类型 常用的数据类型CHAR 存放固定长度的字符串VARCHAR2 存放可变长度的字符串NUMBER 存放0 正负数 浮点数DATE 存放时间数据 包括日期和时间 LONG 存放变长字符串 一般用来存储大文本RAWLONG存放多媒体数据 如声音 图片例如 创建一雇员表CREATETABLEemp empnonumber 4 enamevarchar2 10 hiredatedate salnumber 7 2 deptnonumber 2 ORACLE内置的SQL函数 SQL函数按照传入参数的类型 可分为字符串函数 数值函数 日期函数 其他函数 以下分别列举较常用的部分进行说明 字符串函数 UPPER s 将字符串 s 转换成大写的形式返回 LOWER s 将字符串 s 转换成小写的形式返回 SUBSTR s a b 返回从字符位置a开始有b个字符长的 s 的一部分 若a为正数 从左边向右边计算若a为负数 从右边向左边计算实例 Selectsubstr abcdefg123 4 fromdual 结果返回 defg123 Selectsubstr abcdefg123 4 2 fromdual 结果返回 de Selectsubstr abcdefg123 4 2 fromdual 结果返回 g1 RTRIM s1 s2 返回删除从最右边算起出现在s2中的字符的s1 s2缺省为空格实例 Selectrtrim aabbccdd cd fromdual 结果返回 aabb Selectrtrim aabbccdd dc fromdual 结果返回 aabb ORACL内置的SQL函数 Concat s1 s2 返回串接上s2之后的s1 该函数与 运算符作用相同 实例 selectconcat abc def fromdual 返回结果 abcdef select abc def fromdual 返回结果 abcdef Length s 以字节为单位返回字符串s的长度 ORACL内置的SQL函数 数值函数Ceil n 返回大于或等于n的整数Selectceil 18 6 ceil 18 6 fromdual Floor n 返回小于或等于n的整数Selectfloor 18 6 floor 18 6 fromdual Mod x y 返回x除以y得余数 若y为0 则返回x Selectmod 23 5 mod 4 1 3 fromdual 返回结果 1 1 0 1Round x y 返回舍入到小数点右边y为的x值 Selectround 1 56 round 1 56 1 round 123 4 1 fromdual 返回结果 1 1 0 1 120 ORACL内置的SQL函数 日期函数Sysdate返回当前的日期和时间Add months D x Last day D 返回日期D的月份的最后一天的日期Months Between D1 D2 返回在D1和D2之间月的数目 Trunc D format 返回结尾由format指定的单位的日期 示例 Selecttrunc sysdate year fromdual 返回今年的第一天Selecttrunc sysdate mm fromdual 返回本月的第一天Selecttrunc sysdate D fromdual 返回本周的第一天 ORACL内置的SQL函数 转换函数To char D format 将日期转换为指定格式的字符串 示例 Selectto char sysdate yyyy mm ddhh mi ss fromdual To Date string format 将字符串转换成日期格式示例 Selectto date 2000 10 01 yyyy mm dd fromdual Last day D 返回日期D的月份的最后一天的日期To Number string format ORACL内置的SQL函数 其它函数Nvl a b 空值替换函数 若a为空 则替换成b 示例 Selectename sal sal nvl comm 0 fromdual DECODE 条件 值1 翻译值1 值2 翻译值2 值n 翻译值n 缺省值 该函数的含义如下 IF条件 值1THENRETURN 翻译值1 ELSIF条件 值2THENRETURN 翻译值2 ELSIF条件 值nTHENRETURN 翻译值n ELSERETURN 缺省值 ENDIF PL SQL的注释 注释增强了可阅读性 使得程序更易于理解 单行注释 comment多行注释 comment 注意 此注释不能作用在SQL语言上 示例 DECLAREv deptnonumber 2 与雇员表中部门代码字段交互的变量v salnumber 7 2 与雇员表中工资字段交互的变量BEGIN thisisatest selectdeptno salintov deptno v salfromempwhereempno 7788 END PL SQL块的定义部分 在PL SQL块中引用的所有标识符 都必须在定义部分中明确定义 定义常量格式 标识符 CONSTANT 数据类型 表达式 例 定义一常量PI 值为3 14 PICONSTANTNUMBER 3 2 3 14 定义标量型变量标量型数据类型 是指数据类型为个体型 格式 NOTNULL DEFAULT 例 定义一宽度为10个字符的字符串变量X DECLAREXCHAR 5 yCHAR 5 ORACLE ZCHAR 5 default oracle 代表数据库列的变量 先看一个示例 创建一PL SQL块 根据部门号 返回部门名称 DECLAREv dnamedept dname type BEGINSELECTdnameINTOv dnameFROMDEPTWHEREdeptno 10 DBMS OUTPUT PUT LINE v dname EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE sorry nodatafound END 问题 所引用的数据库表中的数据类型不知道 所引用的数据库表中的数据类型将来改变改变怎么办 PL SQL块的定义部分 另一种定义标量型变量的方法 TYPE定义一个变量 其数据类型与已知变量的数据类型相同 或者与数据库表的某个列的数据类型相同 TYPE的优点在于 所引用的数据库表中的数据类型可以不必知道 所引用的数据库表中的数据类型可以实时改变 格式 NOTNULL DEFAULT TYPE例 定义一个变量 其数据类型基于另一个变量DECLAREV 1NUMBER 7 2 V 11V1 TYPE 12345 6 例 定义一个变量 其数据类型基于数据库中表的列DECLAREv enameEMP ENAME TYPE V SALEMP SAL TYPE PL SQL块的定义部分 另一种定义组合型变量的方法 ROWTYPE定义一个变量 其数据类型与数据库表的数据结构相同 ROWTYPE的优点在于 所引用的数据库表中的数据类型可以不必知道 所引用的数据库表中的数据类型可以实时改变 简易格式 ROWTYPE例 DECLAREv empemp rowtype BEGINSELECT INTOv empFROMempWHEREempno 7788 DBMS OUTPUT PUT LINE v emp empno DBMS OUTPUT PUT LINE v emp ename DBMS OUTPUT PUT LINE v emp job DBMS OUTPUT PUT LINE v emp sal END 变量的引用和赋值 标量变量赋值格式 例 V NAME JOAN v demptno 10 组合型变量赋值格式 变量 域名 主键值 表达式 例 v emp sal 8888 v m 8888 PL SQL中使用SQL 在PL SQL块中 通过SQL语句对ORACLE数据库中的数据进行存取 在PL SQL中 可以使用的SQL语句有 SELECT INSERT DELETE UPDATE COMMIT ROLLBACK不可以直接使用的SQL语句有 数据定义语句 DDL 如 CREATETALBE DROPTABLE数据控制语句 DCL 如 GRANT REVOKE备注 在PL SQL2 1以上版本 允许通过DBMS SQL包来创建动态SQL语句 PL SQL中使用SQL SELECT语句 SELECT语句 将数据从数据库中检索出来并放入PL SQL变量中 格式 SELECTINTOFROM例 查询某个雇员的姓名及工资 DECLAREv empnoemp empno type 7788 v enameemp ename type v salemp sal type BEGINselectename salintov ename v salfromempwhereempno v empno DBMS OUTPUT PUT LINE v empno v ename v sal EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE sorry nodatafound END PL SQL中的SELECT语句中必须包含INTO子句 而且对应的个数要相同 位置要一一对应 查询结果只返回一条记录 否则会产生异常情况 1 查询结果多于一条记录异常变量 TOO MANY ROWS 2 查询结果没有返回记录异常变量 NO DATA FOUND PL SQL中使用SQL 在PL SQL中 对数据库进行插入 INSERT 删除 DELETE 修改 UPDATE 语句 其语法形式与SQL中的是完全一样的 例 在EMP表中删去某个雇员 BEGINDELETEempWHEREempno 7788 COMMIT END PL SQL的执行部分 流程控制语句 流程控制语句主要有三种 条件控制循环控制跳转控制 流程控制语句 条件控制 语法格式 IF 条件 THEN 语句 ELSIF 条件 THEN 语句 ELSE 语句 ENDIF 例 根据职务浮动工资IFv job MANAGER THENv sal v sal 1 3 ELSIFv job SALESMAN THENv sal v sal 1 2 ELSEv sal v sal 1 1 ENDIF updateempsetsal v salwhereempno 1234 流程控制语句 循环控制 在PL SQL中循环控制的有以下四种 简单循环FOR循环WHERE循环用于游标的FOR循环 循环控制 简单循环 语法格式 LOOP 语句1 语句2 EXITWHEN 条件 ENDLOOP 例 把数值1到50顺序插入表中 V counter 1 LOOPINSERTINTOtemp tableVALUES v counter EXITWHENv counter 50 V count v count 1 ENDLOOP 循环控制 FOR循环 语法格式 FOR 循环变量 IN REVERSE 下界 上界 LOOP 语句1 语句2 ENDLOOP REVERSE 使计数器由上界到下界递减计数 例 把数值1到50顺序插入表中 FORv counterIN1 50LOOPINSERTINTOtemp tableVALUES v counter ENDLOOP 循环控制 WHILE循环 语法格式 WHILE 条件 LOOP 语句1 语句2 ENDLOOP 例 把数值1到50顺序插入表中 V counter 1 WHILEv counter 50LOOPINSERTINTOtemp tableVALUES v counter V count v count 1 ENDLOOP 跳转控制语句 语法格式 GOTO 在进行PL SQL编程时 尽量避免或不用GOTO语句 因为这种无条件的跳转语句打破了程序的逻辑性 有悖于自顶向下的编程风格 PL SQL游标的使用 游标 CURSOR 的功能 是ORALCE系统为了将所有查询结果返回给用户程序而提供的 一个游标 实际上是在内存中开辟一个工作区 它对应一条SELECT语句 当打开游标时 就是执行游标所对应的SELECT语句 并将其查询结果放入工作区 并且指针指向工作区的首部 通过光标上的操作可以把这些记录检索到客户端的应用程序 SELECT INTO 只能查询数据库的单条记录 并把记录的数据赋给变量 游标 定义和操纵游标 步骤 定义游标打开游标从游标中取值关闭游标 定义游标定义游标 就是定义一个游标名 以及与其相对应的SELECT语句 语法格式 CURSOR 游标名 IS SELECT子句 示例 定义一个包含所有雇员记录的游标 cursorcur empisselect fromemp 打开游标打开游标 就是执行游标所对应的SELECT语句 将其查询结果放入工作区 并且指针指向工作区的首部 语法格式 OPEN 游标名 从游标中取值取值工作是将游标工作区中的数据取出一行 放入指定的输出变量中 语法格式 FETCH 游标名 INTO 变量1 变量2 示例 fetchcur empintov empno v ename v sal v comm v deptno关闭游标释放与该游标相关的资源 语法格式 CLOSE 示例 closecur emp 游标的属性 从游标工作区中逐一地取数据 可以在循环中完成 但循环的开始以及结束 需以游标属性为依据 游标属性有 ISOPEN 判断游标是否被打开 NOTFOUND 判断何时中断循环 FOUND 与 NOTFOUND相反 ROWCOUNT 实际从游标工作区抽取的记录数示例 Opencur emp Loopfetchcur empintov empno v ename v sal v deptno exitwhencur emp NOTFOUND Endloop 游标 用于游标的FOR循环 游标的FOR循环 是一种简单的游标操作方法 系统隐式地进行游标的打开 提取数据 循环 关闭 格式 FOR 记录变量 IN 游标名 LOOP 语句 ENDLOOP 由系统隐含定义的记录名示例 Declarecursorcur empisselect fromemp Beginforv empincur emploopDBMS OUTPUT PUT LINE v emp ename DBMS OUTPUT PUT LINE v emp sal endloop End 一个完整的示例 例 建立一存储过程 根据职务修改工资CREATEORREPLACEPROCEDUREp update salASCURSORcur empISSELECT FROMemp v empcur emp ROWTYPE BEGINOPENcur emp LOOPFETCHcur empINTOv emp EXITWHENcur emp NOTFOUND IFv emp job MANAGER THENv emp sal v emp sal 1 3 ELSIFv emp job SALESMAN THENv emp sal v emp sal 1 2 ELSEv emp sal v emp sal 1 1 ENDIF UPDATEempSETsal v emp salWHEREempno v emp empno ENDLOOP CLOSEcur emp COMMIT END 一个完整的示例 用FOR循环 CREATEPROCEDUREp update salASCURSORcur empISSELECT FROMemp BEGINFORv empINcur empLOOPIFv emp job MANAGER THENv emp sal v emp sal 1 3 ELSIFv emp job SALESMAN THENv emp sal v emp sal 1 2 ELSEv sal v sal 1 1 ENDIF UPDATEempSETsal v emp salWHEREempno v emp empno ENDLOOP COMMIT END 示例 DECLARECURSORc1isSELECTename empno salFROMempORDERBYsalDESC startwithhighestpaidemployeemy enameCHAR 10 my empnoNUMBER 4 my salNUMBER 7 2 BEGINOPENc1 FORiIN1 5LOOPFETCHc1INTOmy ename my empno my sal EXITWHENc1 NOTFOUND INSERTINTOtempVALUES my sal my empno my ename COMMIT ENDLOOP CLOSEc1 END 异常处理 PL SQL中 将程序执行过程中的一个警告或错误称为一个异常 EXCEPTION 异常情况的种类有三种 预定义的ORACLE错误ORACLE预定一的异常情况大约有24个 对这种异常情况的处理 无须在程序中定义 由ORACLE自动将其引发 非预定义的ORACLE错误即其他标准的ORACLE错误 对这种异常情况的处理 需在定义部分定义 然后由ORACLE自动将其引发 用户定义的错误程序执行过程中 出现编程人员认为非正常的 对这种异常情况的处理 需在定义部分定义 然后显式由地将其引发 异常处理 语法格式 EXCEPTIONWHENTHEN 语句 WHEN 异常情况2 THEN 语句 WHENOTHERSTHEN 语句 OTHERS 指没有列在异常处理部分中的其他异常情况 DECLARE BEGIN EXCEPTION END PL SQL块执行过程 异常发生异常处理 异常处理 预定义的ORACLE错误 异常处理 预定义异常示例 BEGINinsertintoemp empno ename values