oracle-database-11g-plsql-编程实战笔记

Chap1 DML 语句是 select insert update delete 和 merge DDL 语句是 create alter drop rename truncate comment DCL 语句是 grant revoke TCL 语句是 commit rollback 和 savepoint sql16 个基本命令 参考书 OCA 认证考试指南 IZ0 051 清华大学出版社 oracle database sql language reference 11g 有非遵循格式字符串依赖于格式掩码 chap2 2 1 3 关于语句中有多个单引号时处理 1 select It s a bird no plan can t be as pharse from dual 此处两个单引号即为一个单引号 2 只能用q 再加 语句 select q It s a bird no plan can t be as pharse from dual 均输出 PHARSE It s a bird no plan can t be 2 1 4 定义变量与申明变量的区别 定义变量定义变量即为变量分配名称并指定数据类型 申明变量申明变量首先需要定义变量 然后为其赋值 赋值也称为初始化 替代变量替代变量前面要加 lv whom为申明变量 a为替代变量 为替代变量 a没有变量没有变量 类型类型 begin lv whom end 或者 declare lv whom varchar2 20 begin lv whom end 但是要在输入框中字符加两个单引号 替代变量替代变量用 define 申明 且定义时不可以指定类型定义时不可以指定类型 默认为 char 型 Define x emp Select from 调用要用 select 此处解析后变为 select adasd z from dual 此处必须要有单引号 使 adasd 作为直接变量 否则出错 所以替代变量是否加单引号要根据解析的值来确定 注意 避免在声明块中将任何实参赋给局部变量注意 避免在声明块中将任何实参赋给局部变量 constant 变量除变量除 外 外 且 且替代变量与绑定变量都不应该在声明块中赋值替代变量与绑定变量都不应该在声明块中赋值 定义为 constant 的变量 必须在申明块中申明 这意味着常量必须先定义 再给他赋一个 不变的值 绑定变量 绑定变量 bind 用用 var 或或 variable 申明申明 使用冒号 作为前缀 var a number begin a 22 end 上面也可以输出结果 下面也可以输出结果 但二者不能同时放一块 print a 此处 a 前面不能加冒号 print 可用来输出所有类型的变量 另外也可通过 exec 直接给绑定变量赋值 SQL var x number 必须要先定义再赋值 而替代变量定义时不需指定类型 直接赋值 即可 SQL var y number SQL exec x 1 y 2 exec 等价于等价于begin end PL SQL procedure successfully completed x 1 y 2 也可以先定义好绑定变量 再将其赋给其他变量 var a number begin a 22 end 现将其运行再 declare lv a number begin lv a a dbms output put line hello lv a end 结果 hello 22 将上面的代码放入到 d q sql 中用 d q sql 调用即可 但是里面的代码要正确 2 1 5 命名块 1 过程块 create or replace procedure abc aa varchar2 is begin dbms output put line sdhl aa end exec abc dkjdhhdj 可能出现 错误一 ORA 00955 名称已由现有对象使用 这个错误说的不仅仅是有可能你有存储过程使用了 abc 还有可能是你有 表名叫 abc 或者其他 oracle 对象叫 abc 说明 其实由于你已经使用了 create or repalace procedure 即使有存储过 程叫 abc 也会被覆盖掉 你用下面的命令查询一下 有什么对象叫 abc 删掉它或者换一个存储过程名字 SELECT OBJECT NAME OBJECT TYPE FROM USER OBJECTS WHERE OBJECT NAME STUDENT2 错误二 过程中的参数不能指定具体长度 2 函数块 错误一 函数里面要有 return 不是 dbms output put line 但实验之后二者均可以成但实验之后二者均可以成 功创建函数 但是调用时只有功创建函数 但是调用时只有 return 的能没有错误的执行 的能没有错误的执行 create or replace function abc aa varchar2 return varchar2 is begin dbms output put line sdhl aa end 改为 create or replace function abc aa varchar2 return varchar2 is begin return sdhl aa end 但函数不能像过程一样来执行 因为但函数不能像过程一样来执行 因为 execute 命令不允许管理函命令不允许管理函 数返回值 必须要用数返回值 必须要用 call 或或 begin dbms end 命令将返回命令将返回 值放入一个绑定变量 要先定义 中 在输出值放入一个绑定变量 要先定义 中 在输出 必须要先执行函数 在执 行下面语句 不能一起运行 variable result varchar2 20 call abc akdhak into result call 后面只能用绑定变量 或者 或者 SQL begin 2 dbms output put line abc du 3 end 4 sdhldu 此处若用 print 则绑定变量不能加冒号 只能为 print result 但可以查询 select result from dual exec 是 sqlplus 的命令 只能在 sqlplus 中使用 call 是 sql 命令 任何工具都可以使用 2 1 6 嵌套块 命名块还可以嵌套在其他命名块或匿名块中 但嵌套命名块并不是已经发布的 这意味着在调 用一个命名块时 被调用的命名块可能还没有定义 declare procedure a is begin dbms output put line b hello 此处b为被调用函数 但之前没有被解 析 所以运行会出错 end a function b return varchar2 is begin return hello end b begin a end 注 所有匿名块都会在程序都会在实际执行前进行分析 分析是一个编译过程 分析过程将会 识别标识符 保留字 预定义标识符 引用标识符 用户定义变量 子例程或 UDT 命名块 也是标识符 PL SQL 按照自顶向下的次序 将标识符读取到内存中 上例中函数 b 因为在过 程 a 的下面还没被解析 所以会出错 使用 前向引用 可以修正这个问题 函数或过程的前向引用只需要函数或过程的署名函数或过程的前向引用只需要函数或过程的署名 不 需要同时包括署名和实现 这些署名在 PL SQL 中叫做 占位程序 stub 占位程序允许 编译过程在实现命名块之前接受其标识符名称 更正 declare procedure a function b return varchar2 procedure a is begin dbms output put line b hello end a function b return varchar2 is begin return hello end b begin a end 2 2 变量 类型 赋值和运算符 2 2 1 文本数据类型 用伪列来隐式地定义数据类型 伪列如 type Char 20 为一个定长的类型 不管其中变量的长度有没有达到 20 最后显示长度均为 20 字 符 Varchar2 20 则要根据实际情况来确定其长度 Clob character large object 字符大对象 同 varchar2 2 2 2 日期和时间戳类型 1 日期 有两种字符串字面值赋值支持到 date 类型的隐式转换 Lv date date 22 mon 75 或者 Lv date date 22 mon 1975 除了上面两种 任何其他字符串字面值都要求使用 to date 内置 SQL 函数来覆盖格式掩码 如 lv date 1 date to date 19750430 YYYYMMDD 2 间隔 间隔子类型允许将天的间隔表示秒 将年的间隔表示月份数 Interval day to second 数据类型的默认值在两个 date 相减时能起作用 只要在执行 减法之前将其转化为 timestamp 因为 to timestamp 函数保留了 date 的精度 而该精度 低于 timestamp 将天的间隔转换为秒的数据类型为 interval day day 4 或其他数 to second declare lv interval interval day to second lv end day date sysdate sysdate与date对应 lv start day date 18 4月 2012 begin lv interval to timestamp lv end day to timestamp lv start day dbms output put line lv interval end 结果 20 14 56 54 000000 PL SQL procedure successfully completed Date 数据类型默认支持 2 位数字表示的天 timestamp 的精度要求使用 9 位数字表示的天 或者 declare lv interval interval day 9 to second lv end day timestamp systimestamp systimestamp与timestamp对应 lv start day timestamp 18 4月 2012 begin lv interval lv end day lv start day dbms output put line lv interval end 结果 000002943 02 52 41 860000 PL SQL procedure successfully completed 将年的间隔转化为月份数的数据类型为 interval year to month 从一个日期中提取年从一个日期中提取年 用 to char extract year from lv end day 完整代码 declare lv a date 20 4月 2009 a varchar2 12 begin a to char extract year from lv a dbms output put line a end 结果为 2009 3 时间戳 timestamp Timestamp 数据类型精度要比 date 数据类型精度高 2 2 3 数值类型 可以将 number 数据类型隐性转化为 intger 类型 可能会丢失一些小数点 Binary float 32 位浮点数 Binary double 是一个 64 位浮点数 2 2 4 复合变量类型 SQL UDT 用于保存一个数据结构 2 3 控制结构 1 If 语句 if elsif else 2 case 语句 分为简单的 case 语句 case when then else end case break 隐式存在 与搜索型的 case 语句 case when then else end case 简单 case 语句可使用 char nchar varchar2 数据类型 而搜索 case 语句可以使用任意 布尔表达式 搜索搜索 case 语句不局限于等值匹配语句不局限于等值匹配 2 3 3 循环结构 Loop 循环循环 退出要借助 exit 或 exit when 语句 For loop 循环循环 分为范围循环和游标循环 插入 关于游标 见插入 关于游标 见 E sas sql 各类知识要点各类知识要点 游标游标 A 范围循环即 for i in 1 4 loop B 游标循环 1 隐式 for i in select 语句 loop 2 显式 for i in cur v 游标 loop 其中第 2 种要比第一种要有更好的可读性 以后用这种 C where current of 字句 while 循环循环 while loop 内可含 continue goto 语句 Simple 循环语句循环语句 即利用隐式游标属性来进行循环判断 如 if SQL FOUND then else end if 2 4 批量操作 见 见 E sas sql 各类知识要点各类知识要点 游标游标 批量处理是进行成批处理和大批量处理的默认选择 本章小结 本章小结 1 始终在执行块中进行赋值或初始化 除非局部变量被视为常量 2 在实现任何局部块命名之前 始终未其定义前向引用规范 从而确保这些局部命名块能够 互相调用 3 应尽可能使用表集合 避免用 varray 因为前者不受固定大小的限制 Varray 通常需要 更改大小限制 这将花费时间和金钱 4 显式游标应尽可能使用形参 5 应尽可能使用 SQL 数据类型作为函数的返回值 SQL 数据类型不需要编写管道化表函数对 其进行封装就可以在 SQL 中使用 并且数据类型总是可检测的 因为它是目录中的一个命名集 合或结构 本章的问题 1 在程序块中至少包括一个 null 语句 否则会编译错误 2 PL SQL 中必须定义一个 record 结构 因为该结构是仅适用于 PL SQL 环境的变量 3 for 循环必须知道其范围 ide 上界 而游标集返回的行数设置了游标 for 循环的上边界 4 可以使用 bulk collect into 语句讲一个完整游标或游标的某些行选择到一个记录结 构集合中 可以使用 limit 关键字来限制行集 5 在循环内部可以使用 forall 语句 但该语句没有结束块语句 所以需要 end forall 或 end loop 或 end forall loop 语句来结束 forall 语句 Chap3 事物作用域事物作用域 3 1 数据库数据库 ACID 测试测试 Atomicity 原子性 consistency 一致性 isolation 隔离性 durability 持久性 3 2 多版本并发控制 MVCC multiversioned concurrency control 使用数据库快照为用户提供数据库的 永久内存副本 3 3 3 3 1 保存点 提交和事务回滚 若有 2 个保存点 且回滚到了第一个保存点 则第二个保存点就不存在了 3 3 2 提交 注意 A 数据字典作强制隐式的提交 B Commit 命令中的注释将写入到 dba pc2 pending 字典视图中 C 必须具有 force transaction 或 force any transaction 系统权限才能强制提交 D Nowait 选项不会验证对重做和归档日志文件的写入选项不会验证对重做和归档日志文件的写入 E Work 选项将会在所有选项将会在所有 commit 语句中有效语句中有效 例1 标准commit UPDATE hr employees SET salary salary 1 03 WHERE department id in 20 30 40 Commit 或将或将commit改为改为 COMMIT WORK WRITE IMMEDIATE WAIT 例例2 含有 含有nowait和和batch选项的选项的commit 使用使用nowait选项将不会验证对重做和归档日志的写入选项将不会验证对重做和归档日志的写入 避免重写避免重写oracle的提交等待过程 这样做可能会使自己的事物陷入危险的提交等待过程 这样做可能会使自己的事物陷入危险 例3 强制commit语句并写入注释 INSERT INTO hr job history VALUES 100 01 Jan 2000 trunc sysdate AD PRES 90 COMMIT COMMENT In doubt transaction forced by process xyz on date 123 Commit force 2 33 192 发出发出commit force需要一个具有需要一个具有dba权限的账户 单引号 权限的账户 单引号 内的信息代表 内的信息代表 可疑的事物可疑的事物id 3 4DML锁定和隔离控制锁定和隔离控制 为了防止不可重复读取或影子读取 a 设置事物级别为读取一致 在跟踪分布式事物时 设置事物设置事物级别为读取一致 在跟踪分布式事物时 设置事物 语句命名将会非常有益 语句命名将会非常有益 setset transactiontransaction namename 可更好地监控长时间的事务 可更好地监控长时间的事务 SET TRANSACTION READ ONLY NAME Distributed to NYC 添加到该设置事物语句的 distributed to NYC 注释将会保存在dba pc2 pending字 典视图中 http www stanford edu dept itss docs oracle 10g server 101 b10759 statements 10005 htm SELECT product id warehouse id quantity on hand FROM oe inventories nyc 001 WHERE product id 3246 COMMIT b 锁定锁定 用户具有锁定自己所拥有的全部表的权限 如果想要锁定另一个模用户具有锁定自己所拥有的全部表的权限 如果想要锁定另一个模 式中的表 则必须具有式中的表 则必须具有lock any table系统权限系统权限 B 1在游标内使用for update DECLARE CURSOR c employees IS SELECT FROM hr employees FOR UPDATE WAIT 10 该行锁定c employees游标中所引用的行 其中wait 10表示 若游标所引用的行已经存在锁定 则等待10秒 若前一次 锁定持续时间大于10秒 则此次更新将会被取消 也可以用nowait关键字 表示存在锁定的情况下立即返回事物 BEGIN FOR r employees IN c employees LOOP UPDATE hr employees SET salary salary 1 025 WHERE CURRENT OF C employees 该语句引用游标中的最新行 最后锁定会一直持续 直至发出commit或rollback命令 END LOOP END B 2发起发起lock table语句语句 LOCK TABLE hr employees IN EXCLUSIVE MODE NOWAIT UPDATE hr employees SET salary salary 1 025 WHERE department id 10 COMMIT 与for update语句不同 lock table命令将会锁定表中的每一行 此外 短语in exclusive mode 将会锁定除select语句之外的全部操作 有效的锁定模式有有效的锁定模式有 row exclusive模式 模式 限制最少的锁定级别 允许限制最少的锁定级别 允许 行共享 并防止用户锁定整个表或锁定到行共享 并防止用户锁定整个表或锁定到row share模式模式 row share模式 模式 除了没有独占共享的限制之外 该模式等同于除了没有独占共享的限制之外 该模式等同于row exclusive share模式 模式 允许允许select但是不允许其他更新但是不允许其他更新 share row exclusive 模式 等同于模式 等同于share模式 但是同时禁止用户锁模式 但是同时禁止用户锁 定到定到share模式模式 exclusive模式模式 限制最多的级别 阻止除限制最多的级别 阻止除select之外的所有之外的所有DML操作操作 C 改善事物性能 利用批量DML操作的功能 3 5调用者和定义者权限调用者和定义者权限 以程序所有者的身份来保留程序执行的全部权限 在命名程序头使用authid definer 关键 字 例1发出定义者权限 CREATE OR REPLACE FUNCTION hr quarterly sales pi employee id in number pi quarter in date AUTHID DEFINER AS declarative code goes here BEGIN do something here and return EXCEPTION handle the exception here and return END 例2 发出调用者权限 以程序调用者的身份来调用程序 用关键字authid current user CREATE OR REPLACE FUNCTION hr give raise pi employee id in number AUTHID CURRENT USER AS declarative code goes here BEGIN do something here and return EXCEPTION handle the exception here and return END 本章小结本章小结 1 确保自己有一个良好的备份 仅仅激活像rman这样的备份程序是不够的 2 使用简洁而有意义的名称来命名保存点 3 强制可疑事务要求深入了解oracle系统更改号 system change number SCN 和数据字典 只有当事务无法再次尝试时才使用commit force命令 4 使用set transaction name 参数对分布式事务命名 测验小结 1 原子性意味着一个事务全部写入到永久存储中或任何部分都没有写入到永久存储中 2 一致性意味着一个并发多用户系统中为所有事务分配相同的服务cpu时间量和内存 3 隔离性意味着一个事务的任何部分在事务完成并提交之前都不可见 4 持久性意味着事务在完成之后写入到冗余磁盘阵列 5 Undo retention 参数 还不是很熟悉 设置语句在内存中运行的时间长度 Chap4 错误管理错误管理 数据库强化的两种方法是使用DBMS ASSERT包和绑定变量 DBMS ASSERT包 用于验证输入参数的形式是否正确 以及模式和对象名称是否实际存在 绑定 变量防止匿名PL SQL块的嵌套 4 1错误类型 4 1 1编译时错误 即语法上有错误 很多程序员基于编写较大的代码块 而不愿意停下来执行临时编译 一次性调试很多错误可能会花费大量时间查看不重要的信息 尤其当错误栈中打印出许多不必 要的信息时 一种更高效的编程方法是按逻辑分组来编写每个程序块 在函数单元完成后执行 编译 例 循环式编码方法可以避免不必要的调试 4 1 2语义错误 语义错误只在程序内部发生的逻辑错误 如加减乘除的错误 1 Oracle提供的错误条件 Oracle提供了超过2300页的预定义错误条件 程序员可以参考他们来编写或直接调用 2 Oracle sqlcode和和sqlerrm SQLERRM是系统内置变量保存了当前错误的详细信息 Oracle databases error messages 11g guide 中的每个错误都有唯一的代号和消息 与错误条件相关联的数字值称为 sqlcode 可以使用pragma exception init关键字将变量与sqlcode 代号关联 当希望在发生oracle错误的情况下执行特定任务时 该功能特别 有用 例 用pragma exception init捕获系统错误 CREATE TABLE hr emergency contact employee id NUMBER full name VARCHAR2 50 phone home VARCHAR2 15 phone cell VARCHAR2 15 phone pager VARCHAR2 15 ALTER TABLE emergency contact ADD CONSTRAINT ec employee id unk UNIQUE employee id full name BEGIN FOR i IN 1 2 LOOP 此处因为循环两次 导致插入的数据一样 所以会导致违反此处因为循环两次 导致插入的数据一样 所以会导致违反 unique约束约束 INSERT INTO emergency contact VALUES 1 Jane Doe 1 123 456 7890 NULL 1 123 567 8901 END LOOP END 输出 ORA 00001 违反唯一约束条件 SCOTT EC EMPLOYEE ID UNK ORA 06512 在 line 4 用用pragma exception init来捕获来捕获 为什么要捕获错误 因为发现错误时 为什么要捕获错误 因为发现错误时oracle则不执行其他代码了 则不执行其他代码了 为了对其执行额外的任务 则就需要进行捕获错误 此处当程序遇为了对其执行额外的任务 则就需要进行捕获错误 此处当程序遇 到到ORA 00001错误时 运行执行错误时 运行执行dbms 而不会立即中断程序 而不会立即中断程序 declare unique constraint exception pragma exception init unique constraint 00001 BEGIN FOR i IN 1 2 LOOP INSERT INTO emergency contact VALUES 1 Jane Doe 1 123 456 7890 NULL 1 123 567 8901 END LOOP exception when unique constraint then dbms output put line o you throw the unique constraint error END 输出 o you throw the unique constraint error PL SQL procedure successfully completed 3 异常作用域异常作用域 Pl sql块的连续性通过在匿名子块中封装可能的错误代码来维护 封装技术有 标准子块封装 循环子块封装 保存点重定向 封装技术有 标准子块封装 循环子块封装 保存点重定向 goto跳转跳转 例1 封装子块 DECLARE ln parent NUMBER ln child level1 NUMBER ln child level2 NUMBER ln random 0 1 NUMBER BEGIN BEGIN DBMS OUTPUT PUT LINE Made it past Parent ln random 0 1 ROUND DBMS RANDOM VALUE 0 1 ln parent 1 ln random 0 1 BEGIN DBMS OUTPUT PUT LINE Made it past Child Level 1 ln random 0 1 ROUND DBMS RANDOM VALUE 0 1 ln child level1 1 ln random 0 1 BEGIN DBMS OUTPUT PUT LINE Made it past Child Level 2 ln random 0 1 ROUND DBMS RANDOM VALUE 0 1 ln child level2 1 ln random 0 1 END END END EXCEPTION WHEN OTHERS THEN DBMS OUTPUT PUT LINE SQLERRM END 例例2 在循环内封装在循环内封装 DECLARE ln employee id NUMBER ln order total NUMBER CURSOR c employee IS SELECT FROM hr employees BEGIN FOR r employee IN c employee LOOP ln employee id r employee employee id DECLARE no salesman found exception BEGIN SELECT SUM order total INTO ln order total FROM oe orders WHERE sales rep id ln employee id IF ln order total IS NOT NULL THEN DBMS OUTPUT PUT LINE ln order total ELSE RAISE no salesman found END IF EXCEPTION WHEN no salesman found THEN DBMS OUTPUT PUT LINE Caught NO SALESMAN FOUND END END LOOP EXCEPTION WHEN OTHERS THEN DBMS OUTPUT PUT LINE SQLERRM END 例3 保存点异常重试 4 定义自定义错误条件定义自定义错误条件 为了定义自己的错误条件 首先必须创建一个异常变量 最后在代码的异常为了定义自己的错误条件 首先必须创建一个异常变量 最后在代码的异常 块内必须说明这个错误的处理方法块内必须说明这个错误的处理方法 例 DECLARE ln order total number ln promotion id number 1 ln order count number no promo found exception BEGIN SELECT COUNT INTO ln order count FROM oe orders WHERE promotion id ln promotion id IF ln order count 0 THEN SELECT SUM order total INTO ln order total FROM oe orders WHERE promotion id ln promotion id ELSE raise no promo found END IF EXCEPTION WHEN no promo found THEN DBMS OUTPUT PUT LINE No Sales found for Promotion ln promotion id END 开发人员总是使用这个逻辑 问题在于开发人员总是使用这个逻辑 问题在于oracle已经使用已经使用 no data found条件处理这种情况条件处理这种情况 下面对其精简 DECLARE ln order total number ln promotion id number 1 BEGIN SELECT order total INTO ln order total FROM oe orders WHERE promotion id ln promotion id IF ln order count 0 THEN SELECT SUM order total INTO ln order total FROM oe orders WHERE promotion id ln promotion id EXCEPTION WHEN NO DATA FOUND THEN DBMS OUTPUT PUT LINE No Sales found for Promotion ln promotion id END 注注 掌握并适当利用内置错误 以简化自己的代码 掌握并适当利用内置错误 以简化自己的代码 Raise application error 过程是过程是DBMS STANDARD内置包内置包 的组成成分 该过程允许生成的组成成分 该过程允许生成ORA 相关信息 相关信息 而不需要首先声明而不需要首先声明 异常变量或使用异常变量或使用exception init指令 可以简化代码 指令 可以简化代码 例使用例使用Raise application error捕获错误条件捕获错误条件 DECLARE CURSOR c rental IS SELECT c member id c first name c last name full name t transaction amount FROM video store transaction t video store rental r video store contact c WHERE r rental id t rental id AND r customer id c contact id BEGIN FOR r rental IN c rental LOOP IF r rental transaction amount 75 THEN RAISE APPLICATION ERROR 20001 No transaction may be more than 75 TRUE END IF END LOOP END 注 上述程序并未创建异常变量 上述程序并未创建异常变量 此外 将用户定义20001 SQLCODE与错误消息关联 其 中的true参数通知过程在错误栈中包含该错误 第三个参数的默认值是false 通知 RAISE APPLICATION ERROR过程清除错误栈 并只打印错误消息过程清除错误栈 并只打印错误消息 4 2 PL SQL工具 用户编写的许多PL SQL应用程序将需要花费一定的时间来执行 一种最常见但却存在问题的 技术是在提交时使用时间戳 更新的时间戳列标记哪些行已经被程序修改 然而问题在于 提交本身基于需呀耗费不少时间 将其放在应用程序的中间会引发如下问题 减慢应用程序 导致oracle服务器上的内存和cpu占用率突然增高 生成大量的重做日志信息 我们需要的是一种记录程序活动而不需要额外消耗太多系统资源的方法 这种输出也称为程程 序工具 利用内置程序结合实际需要写的代码 序工具 利用内置程序结合实际需要写的代码 本章小结 本章小结 1 如果 如果PL SQL程序在创建时没有进行适当的错误处理和工具化 则开发人程序在创建时没有进行适当的错误处理和工具化 则开发人 员需要花很多时间来维护并反复检查程序结果 通过工具化代码并提供适当员需要花很多时间来维护并反复检查程序结果 通过工具化代码并提供适当 的错误管理结果 将会节省大量的维护和排错时间 的错误管理结果 将会节省大量的维护和排错时间 2 运行错误消息回传到其主调用应用程序 而不要使用 运行错误消息回传到其主调用应用程序 而不要使用when others短语短语 3 使用保存点回滚部分完成的事务 可以编码产生孤立数据 从而维护数据 使用保存点回滚部分完成的事务 可以编码产生孤立数据 从而维护数据 完整性 完整性 4 避免使用 避免使用goto语句语句 5 掌握并适当采用内置错误 以简化自己的代码 掌握并适当采用内置错误 以简化自己的代码 6 尽可能工具化自己的 尽可能工具化自己的pl sql程序程序 测验小结 测验小结 1 DBMS ASSET包允许验证输入参数来过滤传入的包允许验证输入参数来过滤传入的web参数参数 2 PLS 错误是错误是PL SQl错误错误 3 ORA 错误与常规数据库错误和错误与常规数据库错误和SQL有关有关 4 RAISE APPLICATION ERROR允许引发一个自定义异常 但是不等价于允许引发一个自定义异常 但是不等价于RAISE 语句 语句 RAISE语句允许调用申明块中预定义的语句允许调用申明块中预定义的EXCEPTION变量变量 5 pragma exception init允许将一个默认错误号映射到用户定允许将一个默认错误号映射到用户定 义变量 然后可以使用义变量 然后可以使用RAISE语句引发该错误 语句引发该错误 6 SQLCODE对于对于PL SQL块中引入的错误返回错误代码号块中引入的错误返回错误代码号 7 使用在使用在 20000和和 20999范围之间的错误号才能引发范围之间的错误号才能引发 RAISE APPLICATION ERROR调用调用 Chap5 函数函数 5 1函数构架函数构架 5 1 1传值函数传值函数 传值函数在调用时接受值 在完成时返回单个值 传值函数在调用时接受值 在完成时返回单个值 形参只有形参只有in 模式模式 5 1 2传址函数传址函数 在调用传址函数时 至少发送一个或多个局部变量引用作为实在调用传址函数时 至少发送一个或多个局部变量引用作为实 参 参 形参有三种可能模式 形参有三种可能模式 in in out out 传址函数并不像传值函数那样将形参全部消耗 虽然传址函数并不像传值函数那样将形参全部消耗 虽然in模式参模式参 数被消耗 但数被消耗 但in out模式变量通常会在更改状态后返回模式变量通常会在更改状态后返回 5 1 3函数模型选择函数模型选择 当希望消耗输入并产生某种结果时 应该将函数实现为传值函当希望消耗输入并产生某种结果时 应该将函数实现为传值函 数数 当需要验证客户端或当需要验证客户端或web交互程序的完成情况或返回结果时 交互程序的完成情况或返回结果时 应该使用传址模式 应该使用传址模式 5 1 5调用参数说明调用参数说明 create or replace function three a number 0 b number 0 c number 1 return number is begin return a b c end 位置参数说明位置参数说明 实际调用参数依次映射到形参 但是必须为列表中的每个形参实际调用参数依次映射到形参 但是必须为列表中的每个形参 提供实参或调用值 当希望跳过某个形参时 可以赋值为提供实参或调用值 当希望跳过某个形参时 可以赋值为null begin dbms output put line three 3 4 5 end 结果为 2 PL SQL procedure successfully completed 命名参数说明命名参数说明 实际调用参数不需要映射到形参序列实际调用参数不需要映射到形参序列 begin dbms output put line three c 4 b 3 a 5 end 混合参数说明混合参数说明 位置参数必须位于命名参数之前 且在第一个位置参数之后 位置参数必须位于命名参数之前 且在第一个位置参数之后 只能省略可选参数 只能省略可选参数 begin dbms output put line three 8 c 4 end 第一个参数是形参a 所以结果是8减去0并除以4 结果 2 PL SQL procedure successfully completed 5 2函数开发函数开发 限制 限制 PL SQL函数不能包含函数不能包含 数据操作语言数据操作语言 DML 语言 语言 或调用另一个包含或调用另一个包含DML语句的语句的pl sql单元 否则将会引发单元 否则将会引发 ORA 14551异常 该错误表示不能再查询内部执行异常 该错误表示不能再查询内部执行DML操作 操作 一个问题 虽然可以再一个问题 虽然可以再SQL语句或语句或PL SQL块内部调用不带块内部调用不带 参数列表并且不适用括号的函数 参数列表并且不适用括号的函数 但是在但是在CALL语句中调用相同语句中调用相同 程序时必须使用空括号 程序时必须使用空括号 例 利用之前的例 利用之前的three函数函数 variable a number call three into a 没有括号则会出错 结果 Method called a 0 select a from dual 结果 A 0 a 0 当然 也可以通过使用当然 也可以通过使用SQl来调用该函数 以节约时间 加不加括号均可以 来调用该函数 以节约时间 加不加括号均可以 SQL select three from dual THREE 良好的编程需要括号良好的编程需要括号 0 SQL select three from dual THREE 0 5 2 1确定性字句确定性字句 确定性函数在确定性函数在return number后面加上关键字后面加上关键字deterministic 见各类知识要点见各类知识要点 能够确保对于任何输入都可以相同的工作方式工能够确保对于任何输入都可以相同的工作方式工 作作 插入 设置输出列格式插入 设置输出列格式 Column a format 99 999 90 Select b as a from dual With c as select 1 as a1 0 as a2 1 as a3 from dual Select three a1 a2 a3 as a from c c为虚拟表或试 图 结果为结果为 A 1 完整例子 CREATE OR REPLACE FUNCTION pv future value NUMBER periods NUMBER interest NUMBER RETURN NUMBER DETERMINISTIC IS BEGIN RETURN future value 1 interest 100 periods END pv 调用 VARIABLE result NUMBER CALL pv 10000 5 6 INTO result COLUMN money today FORMAT 99 999 90 SELECT result AS money today FROM dual WITH data set AS SELECT 235000 AS principal 30 AS years 5 875 AS interest FROM dual SELECT pv principal years inte