oracle存储过程培训资料.ppt

Oracle9iPL SQL程序设计 PL SQL程序设计 2 PL SQL概述 PL SQL是Oracle对关系数据库语言SQL的过程化扩充 它将数据库技术和过程化程序设计语言联系起来 是一种应用开发语言 可使用循环 分支处理数据 将SQL的数据操纵功能与过程化语言数据处理功能结合起来 PL SQL的使用 使SQL成为一种高级程序设计语言 支持高级语言的块操作 条件判断 循环语句 嵌套等 与数据库核心的数据类型集成 使SQL的程序设计效率更高 PL SQL是一种块结构语言 构成PL SQL的基本单位是逻辑块 块中可以嵌套子块 以块为单位向OracleServer传递数据执行 其块结构如下 DECLAREBEGINEXCEPTIONEND 说明部分 说明变量及常量 执行部分 使用SQL的DML语言事务处理 例外处理 处理错误及警告信息 逻辑块结束 PL SQL程序设计 3 PL SQL语言的特点 PL SQL中可以定义变量 变量有其作用范围PL SQL是以块的方式设计 块中可以嵌套子块 子块可以位于块中任何部分子块可以是PL SQL中的一条命令PL SQL中可以使用条件判断 循环 游标等高级语言程序设计方法模块式的过程化语言 以块为单位执行使用PL SQL可以优化程序设计 得到更好的性能 例如 执行10个SQL语句 需要访问10次Oracle核心 如果10个SQL组成一个PL SQL程序 则只需访问一次Oracle核心 将结果一次返回给用户 则程序执行效率高 节省时间 降低通信量 PL SQL程序设计 4 减少对于Oracle核心的访问 降低网络负责数据库数据类型集成PL SQL支持全部的SQL数据类型 这些共享的数据类型与SQL所提供的直接存取相结合 使PL SQL与Oracle数据字典结合成一体 PL SQL与OracleRDBMS集成在一起 可以使用PL SQL的集成数据类型 使变量的数据类型在数据库的操作中实时确定 PL SQL语言的特点 PL SQL程序设计 5 PL SQL与SQL语言结合 PL SQL语句含有SQL的DML语句 检索及事务处理PL SQL中可以使用的SQL语句如下 INSERTUPDATEDELETESELECTINTOCOMMITROLLBACKSAVEPOINT支持RDBMS的所有数据类型对于SQL的数据类型进行了扩充 增加了新的数据类型 使PL SQL所支持的数据类型更灵活 OracleRDBMS对于PL SQL中引用的表名 列名进行语句检查对于SQL语句的执行结果 PL SQL提供返回信息检查 通过PL SQL的例外处理 记录SQL的执行结果 PL SQL程序设计 6 PL SQL基础 变量的使用和其他高级语言一样 在PL SQL中可以使用变量注释单行注释 多行注释 数据类型日期型Date字符型CHAR VARCHAR2数字型NUMBER布尔型BOOLEAN TRUE FALSE NULL 数据类型 PL SQL数据类型扩展 数字型NUMBER存储整型及实型数据 可以定义精度和取值范围BINARY INTEGER可存储带符号整数 为整数计算优化性能DECNUMBER的子类型 存储实型数据DOUBLEPRECISIONNUMBER的子类型 高精度实数INTEGERNUMBER的子类型 整数INTNUMBER的子类型 整数 PL SQL数据类型扩展 数字型SMALLINTNUMBER的子类型 取值范围比INTEGER小NUMERICNUMBER的子类型 与NUMBER等价REALNUMBER的子类型 存储实型数据 PL SQL数据类型扩展 字符型VARCHAR2存放可变长字符串 有最大长度限制CHAR字符型 固定长度LONG长字符型CLOB大对象字符 Oracle8 Oracle8i 日期型Date存储日期数据 PL SQL数据类型扩展 布尔型BOOLEAN结果是TRUE或FALSE二进制数据RAW存储多媒体数据 如图象 声音 视频等BLOB大对象如图象 声音 视频等 Oracle8 Oracle8i PL SQL程序设计 11 PL SQL基础 数据定义标示符数据类型 标示符数据类型 PL SQL表达式 例如 v salarynumber 7 2 v enamevarchar2 20 SMITH PL SQL程序设计 12 PL SQL基础 变量赋值变量名 值 v salary 890 76 v date 25 MAR 99 Available now FALSE TYPE类型CreditReal 7 2 DebitCredit TYPE My nameEmployee ename TYPE TYPE属性 不必知道My name的真正数据类型 当数据库中列ename列定义改变时 数据库运行时自动修改 ROWTYPE 行类型 PL SQL程序设计 13 PL SQL基础 PL SQL的记录类型把逻辑相关的数据作为一个单元存储起来 在Declare段中定义record类型数据 使某一变量使用该record型数据 定义方法 TYPEr recordisRECORD v nameemp ename TYPE v jobemp job TYPE v salemp sal TYPE 变量定义r empr record 变量使用SELECTename job salINTOr empFROMempWHEREempno 7934 则 r emp v ename r emp v job r emp v sal已有值 给变量赋值 r employeer record r employee v ename JACK r employee v job CLERK r employee v sal 890 98 PL SQL程序设计 PL SQL程序设计 14 PL SQL基础 TABLE类型数据PL SQL中的表 table 类型类似于 语言中的结构类型数组 定义方法 TYPEtable empISTABLEOFemp ename TYPEINDEXBYBINARY INTEGER 一个PL SQL表有两个列 key value key列类型即是BINARY INTEGER 而value类型则是所定义的数据类型 Table类型使用 定义变量my name为table emp类型 则可以使用变量my name 也可以在SQL语句中使用table类型变量 my nametable emp my name 0 SCOTT my name 1 SMITH my name 2 SUSAN SELECTenameINTOmy name 10 FROMempWHEREempno 7934 PL SQL程序设计 15 在PL SQL中使用SQL INSERT语句的使用Declarev empnoEMP empno TYPE 1234 v enameEMP ename TYPE SCOTT v jobVARCHAR2 15 MANAGER v deptnoEMP deptno TYPE 20 v salNUMBER 7 2 890 50 BeginINSERTINTOemp empno ename job hiredate sal deptno VALUES v empno v ename v job SYSDATE v sal v deptno END 注意 非空 NOTNULL 必须有值 NOTNULL PL SQL程序设计 16 PL SQL中使用SQL DELETE语句的使用Declarev empnoEMP empno TYPE 1234 BeginDELETEFROMempWHEREempno v empno End 事务处理语句的使用在PL SQL中可以使用SQL的COMMIT ROLLBACK及SAVEPOINT语句 Declarev empnoEMP empno TYPE 1234 BeginDELETEFROMempWHEREempno v empno COMMIT End PL SQL程序设计 条件控制语句 IF THEN语句IF条件成立THEN可执行语句 ENDIF 条件控制语句 IF THEN ELSE语句IF条件成立THEN执行语句1 ELSE执行语句2 ENDIF 条件控制语句 IF THEN ELSIF语句IF条件1成立THEN执行语句1 ELSIF条件2成立THEN执行语句2 ELSIF条件3成立THEN执行语句3 ENDIF 循环语句 基本循环LOOP执行语句 EXITWHEN条件成立 ENDLOOP 循环语句 WHILE循环WHILE条件成立LOOP执行语句 ENDLOOP 循环语句 FOR循环FOR计数器IN低界 高界LOOP执行语句 ENDLOOP PL SQL程序设计 23 光标 CURSOR 设计 什么是光标光标使用光标说明 在Declare段中命名光标 将光标与一查询语句联系起来 语法 CURSOR光标名 参数 ISSELECT字句 打开光标获取活动集中的行FETCH语句检索活动集中的行 每次一行 每执行一次FECTCH 光标前进到活动集中的下一行 光标下移关闭光标 PL SQL程序设计 24 光标属性 光标属性每一个光标有四种属性 FOUND查询语句 FETCH语句 返回记录 NOTFOUND查询语句 FETCH语句 无返回记录 用于循环退出条件 ROWCOUNTFETCH已获取的记录数 ISOPEN光标已打开标记ifnotC ISOPENthenopenC endif LoopFETCHCINTOv ename v sal EXITWHENC NOTFOUND ENDLOOP 属性使用 PL SQL程序设计 25 隐式光标处理 隐式光标处理隐式光标是指在处理SQL时 不需定义光标 所使用的SQL语句包括 INSERT UPDATE DELETE子句 隐式光标属性SQL FOUNDSQL NOTFOUND由执行上述语句结果引起SQL ROWCOUNT隐式光标的使用Beginupdateempsetsal 1200whereempno 1234 ifSQL NOTFOUNDtheninsertintoErrors empno sal values 1234 1200 endif End 数据库中没有编码为1234的职工 PL SQL程序设计 26 例外处理 EXCEPTION 在PL SQL中 警告信息 出错信息 或返回信息统称为例外 Exception 有两中类型的例外 Oracle预定义的例外 是由PL SQL运行过程中 系统自动产生的信息 用户定义例外 是用户根据需要 自己定义使用的例外 执行时由用户自己引起 预定义的例外CURSOR ALREADY OPENVALUE ERRORNO DATA FOUNDINVALID NUMBERTOO MANY ROWSZERO DIVIDEINVALID CURSOR用户自定义例外用户定义的例外必须在DECLARE段中说明 在Begin段中用RAISE引起 在EXCEPTION段中使用 PL SQL程序设计 27 例外处理 EXCEPTION 用户定义例外的说明 定义名为out of range的例外Declareout of rangeEXCEPTION 例外的使用DeclareBeginExceptionWhenNO DATA FOUNDTHEN WhenCURSOR ALREADY OPENTHENWhenTOO MANY ROWSTHEN WhenOTHERSTHEN END 例外段 在Exception段中对返回信息一一作出响应 PL SQL程