oracle第七讲plsql剖析.ppt

Oracle数据库管理系统第七讲PL SQL基础 第七讲pl sql基础 课程目标 SQL与PL SQL PL SQL内置包 1 2 3 PL SQL块结构和组成元素 4 第七讲pl sql基础 一 概述 Oracle5以前是没有PL SQL这个产品的 后来由于数据库的发展和软件技术的发展 出现了第4代开发工具 称为4GL 4GL的功能是在处理界面上有优势 但在数据库的数据处理上仍不是它们的特长 鉴于数据库在SQL方面的优势 Oracle公司在Oracle6版本以后开发了PL SQL产品 Oracle在后来的版本中增强了PL SQL的功能 包括现在的许多系统的处理几乎是由PL SQL写成 而不是用C来编写 第七讲pl sql基础 一 概述 1什么是PL SQL2PL SQL的好处3PL SQL可用的SQL语句 第七讲pl sql基础 1 什么是PL SQL PL SQL是ProcedureLanguage StructuredQueryLanguage的缩写 Oracle的SQL是支持ANSI AmericannationalStandardsInstitute 和ISO92 InternationalStandardsOrganization 标准的产品 PL SQL是对SQL语言存储过程的扩展 第七讲pl sql基础 1 什么是PL SQL 目前的PL SQL包括两部分 一部分是数据库引擎部分 数据库PL SQL 另一部分是可嵌入到许多产品 如C语言 JAVA语言等 工具中的独立引擎 工具PL SQL 两者的编程非常相似 都具有编程结构 语法和逻辑机制 第七讲pl sql基础 2PL SQL的好处 提高应用程序的运行性能模块化的设计思想减少网络传输量提高安全性 第七讲pl sql基础 3PL SQL可用的SQL语句 在PL SQL中可以使用的SQL语句有 INSERTUPDATEDELETESELECTINTOCOMMITROLLBACKSAVEPOINT 提示 在PL SQL中只能用SQL语句中的DML部分 不能用DDL部分 如果要在PL SQL中使用DDL 如Createtable等 的话 只能以动态的方式来使用 第七讲pl sql基础 3PL SQL可用的SQL语句 1 Oracle的PL SQL组件在对PL SQL程序进行解释时 同时对其所使用的表名 列名及数据类型进行检查 2 PL SQL可以在SQL PLUS中使用 3 PL SQL可以在高级语言中使用 4 PL SQL可以在Oracle的开发工具中使用 5 其它开发工具也可以调用PL SQL编写的过程和函数 如PowerBuilder等都可以调用服务器端的PL SQL过程 第七讲pl sql基础 三 运行PL SQL程序 PL SQL程序的运行是通过Oracle中的一个引擎来进行的 这个引擎可能在Oracle的服务器端 也可能在Oracle应用开发的客户端 引擎执行PL SQL中的过程性语句 然后将SQL语句发送给数据库服务器来执行 再将结果返回给执行端 第七讲pl sql基础 三 运行PL SQL程序 比如已经用下面的脚本创建了一个存储过程 createprocedurefundstat1 start year monthinvarchar2 end year monthinvarchar2 isyear month0varchar2 7 年月 begin end 第七讲pl sql基础 三 运行PL SQL程序 在SQL PLUS下运行可以用下面命令启动运行 SQL executefunstat1 2001 01 2001 02 类似地 在Powerbuilder和Developer 2000下可以用下面语句来启动运行 EXECUTEfundstat1 2001 01 2001 03 或date1 2001 01 date2 2001 03 EXECUTEfundstat1 date1 date2 第七讲pl sql基础 四 PL SQL内置包 Oracle系统本身提供了一套功能强大的内置包 这些系统包都以DBMS 开头进行命名 用这些包可以完成许多Oracle管理功能 下面是一些常用包的简单说明 DBMS ALERT数据库报警 允许会话间通信 DBMS JOB任务调度服务 DBMS LOB大对象操作用的包 DBMS PIPE数据库管道用的包 DBMS SQL动态SQL所用的包 UTL FILE文本文件I O所用的包 第七讲pl sql基础 五 PL SQL块结构和组成元素 PL SQL结构PL SQL块标识符PL SQL变量类型运算符和表达式变量赋值变量作用范围以可见性注释简单例子 第七讲pl sql基础 五 PL SQL块结构和组成元素 PL SQL结构 PL SQL块中可以包含子块 子块可以位于PL SQL中的任何部分 子块也可以是PL SQL中的一条命令 已定义的对象有一定的作用范围 第七讲pl sql基础 五 PL SQL块结构和组成元素 Declare 本部分可以定义变量 光标等 v namevarchar2 10 v sqlnumber 7 2 Begin 执行部分 selectenameintov namefromscott empwhereempno 7777 begin 子块开始 也是执行语句 EXCEPTION 执行异常处理部分 end EXCEPTION 执行异常部分 end 本PL SQL程序结束 第七讲pl sql基础 五 PL SQL块结构和组成元素 PL SQL块 PL SQL程序组成 声明部分 执行部分 异常处理部分 PL SQL块的结构如下 Declare 声明部分 在此声明PL SQL用到的变量 类型及光标 begin 执行部分 过程及SQL语句 即程序的主要部分 Exception 执行异常部分 错误处理 End 第七讲pl sql基础 五 PL SQL块结构和组成元素 标识符 标识符名不能超过30字符 第一个字符必须为字母 可包含字母 数字 和 不分大小写 不能是SQL保留字 提示 一般不要把变量名声明与表中字段名完全一样 如果这样可能得到不正确的结果 第七讲pl sql基础 五 PL SQL块结构和组成元素 例如 下面的例子将会删除所有的记录 而不是KING的记录DECLAREenamevarchar2 20 KING BEGINDELETEFROMempWHEREename ename END 第七讲pl sql基础 五 PL SQL块结构和组成元素 下面是建议的命名方法 第七讲pl sql基础 五 PL SQL块结构和组成元素 变量类型 例 declareorder nonumber 3 cust namevarchar2 20 order datedate emp nointeger 25 piconstantnumber 3 14159 beginnull end 第七讲pl sql基础 五 PL SQL块结构和组成元素 变量类型 1 记录类型 记录类型用于表示逻辑相关的一组信息 这些信息按某种结构组织在一起 定义记录类型语法如下 TYPErecord typeISRECORD Field1type1 NOTNULL exp1 Field2type2 NOTNULL exp2 Fieldntypen NOTNULL expn 第七讲pl sql基础 五 PL SQL块结构和组成元素 DECLARETYPEmy recISRECORD idnumber 3 namevarchar2 20 teachert rec1 BEGINteacher id 10 teacher name Jack dbms output put line teacher id teacher name END 第七讲pl sql基础 五 PL SQL块结构和组成元素 可以用select语句对记录变量进行赋值 只要保证记录字段与查询结果列表中的字段相配即可 DECLARETYPEt studentrecordISRECORD Firstnamesutdents first name TYPE Lastnamesutdents last name TYPE Majorsutdents major TYPE v stduentt StudentRecord BeginSelectfirst name last name majorIntov studentFromstudentsWhereid 10000 End 第七讲pl sql基础 五 PL SQL块结构和组成元素 使用 ROWTYPE PL SQL可以基于已定义的记录类型变量或数据库中已存在的表来定义与其结构相同的记录类型变量 格式如下 1 新变量名已定义的记录类型变量 ROWTYPE 2 新变量名表名 ROWTYPE 第七讲pl sql基础 五 PL SQL块结构和组成元素 表类型 相当于数组 DECLARETYPEmy table typeisTABLEOFvarchar 20 INDEXBYBINARY INTEGER my tablemy table type Beginmy table 1 百川东到海 my table 3 何时复西归 dbms output put line my table 1 dbms output put line my table 3 end 第七讲pl sql基础 五 PL SQL块结构和组成元素 用户定义的子类型 SUBTYPEsubtype nameISbase type constraint NOTNULL SUBTYPEcharacterISCHAR SUBTYPEintegerISNUMBER 38 0 第七讲pl sql基础 五 PL SQL块结构和组成元素 用户定义的子类型 使用子类型一旦定义子类型 就可以在声明的地方使用子类型 例 DECLARESUBTYPEAccumulatorISNUMBER totalAccumulator 7 2 第七讲pl sql基础 五 PL SQL块结构和组成元素 用户定义的子类型 DECLARESUBTYPENumeralISNUMBER 1 0 x axisNumeral y axisNumeral BEGINx axis 10 END 第七讲pl sql基础 五 PL SQL块结构和组成元素 运算符和表达式 关系运算符 第七讲pl sql基础 五 PL SQL块结构和组成元素 运算符和表达式 一般运算符 一般运算符有 加号 减号 乘号 除号 赋值号 关系号 范围运算符 字符连接符 第七讲pl sql基础 五 PL SQL块结构和组成元素 运算符和表达式 逻辑运算符 逻辑运算符有 isnull是空值Between介于两者之间In在一列值中间And逻辑与Or逻辑或Not取反 如isnotnull notin 第七讲pl sql基础 五 PL SQL块结构和组成元素 字符及数字运算特点 空值加数字仍是空值 null null空值加 连接 字符 结果为字符 null 第七讲pl sql基础 五 PL SQL块结构和组成元素 字符及数字运算特点 Boolean赋值 布尔值有TRUE FALSE及NULL三个值DECLAREdoneBOOLEAN BEGINdone FALSE WHILENOTdoneLOOP ENDLOOP END 第七讲pl sql基础 五 PL SQL块结构和组成元素 数据库赋值 数据库赋值是通过select语句来完成的 每执行select语句一次只能赋值一次 一般要求被赋值的变量与select中的列名要一一对应 第七讲pl sql基础 五 PL SQL块结构和组成元素 DECLAREemp idemp empno TYPE emp nameemp ename TYPE wagesNUMBER 7 2 BEGIN SELECTename sal commINTOemp name wagesFROMempWHEREempno emp id END 第七讲pl sql基础 五 PL SQL块结构和组成元素 可转换的类型赋值 1 CHAR转换为NUMBER使用TO NUMBER函数来完成字符到数字的转换 如 v total to number 100 0 sal 2 NUMBER转换为CHAR使用TO CHAR函数可以实现数字到字符的转换 如 v comm to char 123 45 元 第七讲pl sql基础 五 PL SQL块结构和组成元素 可转换的类型赋值 3 字符转换为日期使用TO DATE函数可以实现字符到日期的转换 如 v date to date 2001 07 03 yyyy mm dd 4 日期转换为字符使用TO CHAR函数可以实现日期到字符的转换 如 v to day to char sysdate yyyy mm ddhh24 mi ss 第七讲pl sql基础 五 PL SQL块结构和组成元素 可转换的类型赋值 SQL selectto number 133 200fromdual TO NUMBER 133 200 333SQL selectto char 321 元 fromdual TO CHAR 321元 第七讲pl sql基础 五 PL SQL块结构和组成元素 变量作用范围以及可见性 变量的作用范围是在你所引用的程序单元 块 子程序 包 内 即从声明变量开始到该块的结束 一个变量 标识 只能在你所引用的程序单