Oracle10g PL/SQL基础


Oracle Database 10g:PL/SQL 基础 电子演示 D17112CN21 版本 2.1 2007 年 3 月 D49203 ® 版权所有 © 2007,Oracle。保留所有权利。 免责声明 本文档包含专有权信息,并受版权法和其它知识产权法的保护。您可以复制和打印本文档以供在 Oracle 培 训课程中单独使用。不得以任何方式修改或变更本文档。除了在依照版权法中制定的“合理使用”范围内 使用本文档外,在未经 Oracle 明确授权的情况下,您不得以全部或部分的形式使用、共享、下载、上载、 复制、打印、显示、展示、再版、发布、许可、张贴、传播或散布本文档。 本文档中包含的信息如有更改,恕不另行通知。如果您在本文档中发现任何问题,请书面通知:Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA.。不能保证本文档中没有错误。 有限权利声明 如果将本文档交付给美国政府或代表美国政府使用本文档的任何人,请适用下列通知中的规定: 美国政府权利 美国政府使用、修改、再版、发行、展示、显示或公开这些培训资料的权利必须遵守 Oracle 许可协议和 (或)美国政府合同法中适用的条款。 商标声明 Oracle、JD Edwards、PeopleSoft 和 Siebel 是 Oracle 公司和(或)其分公司的注册商标。其它名称可能 是其各自拥有者的商标。 作者 Tulika Srivastava Sunitha Patel 技 术撰稿 人和 审稿人 Chaitanya Koratamaddi Christoph Burandt Zarko Cesljas Dairy Chan Isabelle Cornu Kathryn Cunningham Burt Demchick Joel Goodman Jonathan Grove Jessie Ho Craig Hollister Alison Holloway Bryn Llewellyn Malika Marghadi Hildegard Mayr Nancy Greenberg Miyuki Osato Nagavalli Pataballa Srinivas Putrevu Bryan Roberts Helen Robertson Grant Spencer Lex Van Der Werff 编辑 Richard Wallis Arijit Ghosh 制图 员 Steve Elwood 出版 商 Nita Brozowski Srividya Rameshkumar 版权所有 © 2007,Oracle。保留所有权利。 简介 版权所有 © 2007,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 描述课程对象 • 描述课程表 • 识别本课程使用的数据库表 • 识别可帮助您设计完整业务解决方案的 Oracle 产品 版权所有 © 2007,Oracle。保留所有权利。 课程目标 学完本课程后,应能完成以下工作: • 了解 PL/SQL 提供对 SQL 的编程扩展 • 编写 PL/SQL 代码以便与数据库交互 • 设计可有效执行的 PL/SQL 程序单元 • 使用 PL/SQL 编程构造和条件控制语句 • 处理运行时错误 • 描述存储过程和函数 版权所有 © 2007,Oracle。保留所有权利。 课程表 第一天的课程: I. 简介 1. PL/SQL 简介 2. 声明 PL/SQL 变量 3. 创建可执行部分 4. 与 Oracle 数据库服务器交互 5. 编写控制结构 版权所有 © 2007,Oracle。保留所有权利。 课程表 第二天的课程: 6. 使用组合数据类型 7. 使用显式游标 8. 包括异常错误处理 9. 创建存储过程和函数 版权所有 © 2007,Oracle。保留所有权利。 人力资源 (hr) 数据集 版权所有 © 2007,Oracle。保留所有权利。 Oracle10g 网格基础结构 版权所有 © 2007,Oracle。保留所有权利。 Oracle Database 10g 多媒体 对象关系数据 消息 文档 版权所有 © 2007,Oracle。保留所有权利。 Oracle Application Server 10g 业务智能 事务处理应用程序 门户 集成 应用程序开发框架 应用程序服务器 版权所有 © 2007,Oracle。保留所有权利。 Oracle Enterprise Manager 10g Grid Control • 软件预配 • 应用程序服务级别监视 版权所有 © 2007,Oracle。保留所有权利。 Oracle Internet 平台 系统管理 网络服务 数据库 应用程序 服务器 Internet 应用程序 任意 浏览器 任意 FTP 客户机 任意邮件 客户机 SQL PL/SQL Java 客户机 表现形式和业 务逻辑 业务逻辑 和数据 开发工具 版权所有 © 2007,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 描述课程对象和课程表 • 识别 hr 方案中的表以及它们之间的关系 • 识别 Oracle 10g 网格基础结构中可用于开发完整业务 解决方案的各种产品 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 简介 版权所有 © 2007,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 说明需要使用 PL/SQL 的原因 • 说明 PL/SQL 的优点 • 确定 PL/SQL 块的不同类型 • 将 iSQL*Plus 用作 PL/SQL 的开发环境 • 在 PL/SQL 中输出消息 版权所有 © 2007,Oracle。保留所有权利。 什么是 PL/SQL PL/SQL: • SQL 的过程语言扩展的代表 • 是 Oracle Corporation 用于关系数据库的标准数据 访问语言 • 将过程构造与 SQL 无缝集成 版权所有 © 2007,Oracle。保留所有权利。 关于 PL/SQL PL/SQL: • 为代码的可执行单元提供了块结构。使用如此明确定义 的结构可以简化代码的维护。 • 提供了以下过程构造: – 变量、常量和类型 – 控制结构,如条件语句和循环 – 可重复使用的程序单元,只需编写一次,即可多次执行 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 环境 PL/SQL 引擎 Oracle 数据库服务器 SQL 语句 执行程序 过程 语句 执行程序 过程 SQL PL/SQL 块 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 的优点 • 过程构造与 SQL 的集成 • 提高性能 SQL IF...THEN SQL ELSE SQL END IF; SQL SQL 1 SQL 2 … 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 的优点 • 模块化程序开发 • 与 Oracle 工具集成 • 可移植性 • 异常错误处理 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 块结构 • DECLARE(可选) – 变量、游标、用户定义的异常错误 • BEGIN(必需) – SQL 语句 – PL/SQL 语句 • EXCEPTION(可选) – 出现错误时 执行的操作 • END;(必需) 版权所有 © 2007,Oracle。保留所有权利。 块类型 匿名 过程 函数 [DECLARE] BEGIN --statements [EXCEPTION] END; PROCEDURE name IS BEGIN --statements [EXCEPTION] END; FUNCTION name RETURN datatype IS BEGIN --statements RETURN value; [EXCEPTION] END; 版权所有 © 2007,Oracle。保留所有权利。 程序构造 应用程序触发器 应用程序包 应用程序过程或函数 匿名块 工具构造 对象类型 数据库触发器 存储程序包 存储过程或函数 匿名块 数据库服务器构造 对象类型 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 编程环境 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 编程环境 iSQL*Plus 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 编程环境 版权所有 © 2007,Oracle。保留所有权利。 iSQL*Plus 体系结构 版权所有 © 2007,Oracle。保留所有权利。 创建匿名块 在 iSQL*Plus 工作区中键入匿名块: 版权所有 © 2007,Oracle。保留所有权利。 执行匿名块 单击“ Execute(执行)”按钮以执行匿名块: PL\SQL 过程成功完成。 版权所有 © 2007,Oracle。保留所有权利。 测试 PL/SQL 块的输出 • 使用以下命令启用 iSQL*Plus 中的输出: SET SERVEROUTPUT ON • 使用预定义 Oracle 程序包及其过程: – DBMS_OUTPUT.PUT_LINE SET SERVEROUTPUT ON … DBMS_OUTPUT.PUT_LINE(' The First Name of the Employee is ' || f_name); … 版权所有 © 2007,Oracle。保留所有权利。 测试 PL/SQL 块的输出 版权所有 © 2007,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 集成 SQL 语句与 PL/SQL 程序构造 • 说明 PL/SQL 的优点 • 区分不同 PL/SQL 块类型 • 将 iSQL*Plus 用作 PL/SQL 的编程环境 • 在 PL/SQL 中输出消息 版权所有 © 2007,Oracle。保留所有权利。 练习 1:概览 本练习包含以下主题: • 确定哪些 PL/SQL 块可以成功执行 • 创建并执行简单的 PL/SQL 块 版权所有 © 2007,Oracle。保留所有权利。 声明 PL/SQL 变量 版权所有 © 2007,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 识别有效的标识符和无效的标识符 • 列出变量的用法 • 声明和初始化变量 • 列出和说明各种数据类型 • 说明使用 %TYPE 属性的优点 • 声明、使用和输出绑定变量 版权所有 © 2007,Oracle。保留所有权利。 变量的使用 变量可用于: • 临时存储数据 • 处理存储的值 • 可重用性 SELECT first_name, department_id INTO emp_fname, emp_deptno FROM … emp_fname emp_deptno Jennifer 10 版权所有 © 2007,Oracle。保留所有权利。 标识符 标识符可用于: • 命名变量 • 提供变量名称惯例 – 必须以字母开头 – 可以包含字母或数字 – 可以包含特殊字符(如美元符号、下划线和英镑符号) – 其长度不得超过 30 个字符 – 不得为保留字 版权所有 © 2007,Oracle。保留所有权利。 在 PL/SQL 中处理变量 可对变量执行以下操作: • 在声明部分进行声明和初始化 • 在可执行部分使用并为其赋新值 • 作为参数传递给 PL/SQL 子程序 • 用于存放 PL/SQL 子程序的输出内容 版权所有 © 2007,Oracle。保留所有权利。 声明和初始化 PL/SQL 变量 语法 示例 identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; DECLARE emp_hiredate DATE; emp_deptno NUMBER(2) NOT NULL := 10; location VARCHAR2(13) := 'Atlanta'; c_comm CONSTANT NUMBER := 1400; 版权所有 © 2007,Oracle。保留所有权利。 声明和初始化 PL/SQL 变量 SET SERVEROUTPUT ON DECLARE Myname VARCHAR2(20); BEGIN DBMS_OUTPUT.PUT_LINE('My name is: '||Myname); Myname := 'John'; DBMS_OUTPUT.PUT_LINE('My name is: '||Myname); END; / SET SERVEROUTPUT ON DECLARE Myname VARCHAR2(20):= 'John'; BEGIN Myname := 'Steven'; DBMS_OUTPUT.PUT_LINE('My name is: '||Myname); END; / 1 2 版权所有 © 2007,Oracle。保留所有权利。 字符串文字中的分隔符 SET SERVEROUTPUT ON DECLARE event VARCHAR2(15); BEGIN event := q'!Father's day!'; DBMS_OUTPUT.PUT_LINE('3rd Sunday in June is : '||event); event := q'[Mother's day]'; DBMS_OUTPUT.PUT_LINE('2nd Sunday in May is : '||event); END; / 版权所有 © 2007,Oracle。保留所有权利。 变量类型 • PL/SQL 变量: – 标量 – 组合 – 引用 – 大对象 (LOB) • 非 PL/SQL 变量:绑定变量 版权所有 © 2007,Oracle。保留所有权利。 TRUE 变量类型 25-JAN-01 Atlanta 256120.08 The soul of the lazy man desires, and he has nothing; but the soul of the diligent shall be made rich. 版权所有 © 2007,Oracle。保留所有权利。 声明和初始化 PL/SQL 变量的准则 • 遵循命名惯例。 • 使用有意义的变量名。 • 初始化指定为 NOT NULL 和 CONSTANT 的变量。 • 初始化具有赋值运算符 (:=) 或 DEFAULT 关键字的 变量: • 每行声明一个标识符,以增强可读性并简化代码的维护。 Myname VARCHAR2(20):='John'; Myname VARCHAR2(20) DEFAULT 'John'; 版权所有 © 2007,Oracle。保留所有权利。 声明 PL/SQL 变量的准则 • 避免将列名用作标识符。 DECLARE employee_id NUMBER(6); BEGIN SELECT employee_id INTO employee_id FROM employees WHERE last_name = 'Kochhar'; END; / • 如果变量必须存放值,则使用 NOT NULL 约束条件。 版权所有 © 2007,Oracle。保留所有权利。 标量数据类型 • 存放单值 • 没有内部组成部分 Atlanta TRUE 25-JAN-01 256120.08 The soul of the lazy man desires, and he has nothing; but the soul of the diligent shall be made rich. 版权所有 © 2007,Oracle。保留所有权利。 基本标量数据类型 • CHAR [(maximum_length)] • VARCHAR2 (maximum_length) • LONG • LONG RAW • NUMBER [(precision, scale)] • BINARY_INTEGER • PLS_INTEGER • BOOLEAN • BINARY_FLOAT • BINARY_DOUBLE 版权所有 © 2007,Oracle。保留所有权利。 基本标量数据类型 • DATE • TIMESTAMP • TIMESTAMP WITH TIME ZONE • TIMESTAMP WITH LOCAL TIME ZONE • INTERVAL YEAR TO MONTH • INTERVAL DAY TO SECOND 版权所有 © 2007,Oracle。保留所有权利。 BINARY_FLOAT 和 BINARY_DOUBLE • 以 IEEE 754 格式表示浮点数字 • 提供更好的交互操作性和更高的操作速度 • 存储数据类型 NUMBER 无法存储的值 • 提供封闭算术运算和透明舍入的优点 版权所有 © 2007,Oracle。保留所有权利。 声明标量变量 示例 DECLARE emp_job VARCHAR2(9); count_loop BINARY_INTEGER := 0; dept_total_sal NUMBER(9,2) := 0; orderdate DATE := SYSDATE + 7; c_tax_rate CONSTANT NUMBER(3,2) := 8.25; valid BOOLEAN NOT NULL := TRUE; ... 版权所有 © 2007,Oracle。保留所有权利。 %TYPE 属性 %TYPE 属性 • 用于声明变量,依据为: – 数据库列定义 – 另一个已声明的变量 • 其前缀为: – 数据库表和列 – 已声明变量的名称 版权所有 © 2007,Oracle。保留所有权利。 使用 %TYPE 属性声明变量 语法 示例 ... emp_lname employees.last_name%TYPE; balance NUMBER(7,2); min_balance balance%TYPE := 1000; ... identifier table.column_name%TYPE; 版权所有 © 2007,Oracle。保留所有权利。 声明 Boolean 变量 • 仅 TRUE、FALSE 和 NULL 值可以赋给 Boolean 变量。 • 条件表达式使用逻辑运算符 AND 和 OR 以及一元运算符 NOT 检查变量值。 • 此类变量始终生成 TRUE、FALSE 或 NULL。 • 算术、字符和日期表达式可用于返回 Boolean 值。 版权所有 © 2007,Oracle。保留所有权利。 绑定变量 绑定变量: • 在环境中创建 • 也称为宿主变量 • 使用 VARIABLE 关键字创建 • 在 SQL 语句和 PL/SQL 块中使用 • 即使在执行 PL/SQL 块后也可访问 • 通过在前面加冒号来引用 版权所有 © 2007,Oracle。保留所有权利。 输出绑定变量 示例 VARIABLE emp_salary NUMBER BEGIN SELECT salary INTO :emp_salary FROM employees WHERE employee_id = 178; END; / PRINT emp_salary SELECT first_name, last_name FROM employees WHERE salary=:emp_salary; 版权所有 © 2007,Oracle。保留所有权利。 输出绑定变量 示例 VARIABLE emp_salary NUMBER SET AUTOPRINT ON BEGIN SELECT salary INTO :emp_salary FROM employees WHERE employee_id = 178; END; / 版权所有 © 2007,Oracle。保留所有权利。 替代变量 • 用于在运行时获得用户输入内容 • 通过在前面加“&”号来在 PL/SQL 块中引用 • 用于避免对运行时获得的值进行硬编码 VARIABLE emp_salary NUMBER SET AUTOPRINT ON DECLARE empno NUMBER(6):=&empno; BEGIN SELECT salary INTO :emp_salary FROM employees WHERE employee_id = empno; END; / 版权所有 © 2007,Oracle。保留所有权利。 替代变量 1 2 3 版权所有 © 2007,Oracle。保留所有权利。 有关替代变量的提示 SET VERIFY OFF VARIABLE emp_salary NUMBER ACCEPT empno PROMPT 'Please enter a valid employee number: ' SET AUTOPRINT ON DECLARE empno NUMBER(6):= &empno; BEGIN SELECT salary INTO :emp_salary FROM employees WHERE employee_id = empno; END; / 版权所有 © 2007,Oracle。保留所有权利。 SET VERIFY OFF DEFINE lname= Urman DECLARE fname VARCHAR2(25); BEGIN SELECT first_name INTO fname FROM employees WHERE last_name='&lname'; END; / 对用户变量使用 DEFINE 示例 版权所有 © 2007,Oracle。保留所有权利。 组合数据类型 TRUE 23-DEC-98 ATLANTA 1 5000 2 2345 312 4 3456 1 SMITH 2 JONES 3 NANCY 4 TIM PL/SQL 表结构 PL/SQL 表结构 PLS_INTEGER VARCHAR2 PLS_INTEGER NUMBER 版权所有 © 2007,Oracle。保留所有权利。 LOB 数据类型变量 书 (CLOB) 相片 (BLOB) 电影 (BFILE) NCLOB 版权所有 © 2007,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 识别有效标识符和无效标识符 • 在 PL/SQL 块的声明部分声明变量 • 在可执行部分初始化变量并使用变量 • 区分标量和组合数据类型 • 使用 %TYPE 属性 • 使用绑定变量 版权所有 © 2007,Oracle。保留所有权利。 练习 2:概览 本练习包含以下主题: • 确定有效的标识符 • 确定有效的变量声明 • 在匿名块中声明变量 • 使用 %TYPE 属性声明变量 • 声明和输出绑定变量 • 执行 PL/SQL 块 版权所有 © 2007,Oracle。保留所有权利。 编写可执行语句 版权所有 © 2007,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 识别 PL/SQL 块中的词法单元 • 使用 PL/SQL 中的内置 SQL 函数 • 说明何时发生隐式转换,何时应处理显式转换 • 编写嵌套块并用标签限定变量 • 编写包含适当缩进的可读代码 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 块中的词法单元 词法单元: • 是任何 PL/SQL 块的构建块 • 是字符序列,包括字母、数字、制表符、空格、换行符 和符号 • 可以进行以下分类: – 标识符 – 分隔符 – 文字 – 注释 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 块的语法和准则 • 文字: – 字符文字和日期文字必须用单引号引起来。 – 数字可以是简单的值,或用科学计数法表示。 • 语句可以占用多行。 name := 'Henderson'; 版权所有 © 2007,Oracle。保留所有权利。 注释代码 • 在单行注释前使用两个连字符 (--)。 • 将多行注释放在符号 /* 和 */ 之间。 示例 DECLARE ... annual_sal NUMBER (9,2); BEGIN -- Begin the executable section /* Compute the annual salary based on the monthly salary input from the user */ annual_sal := monthly_sal * 12; END; -- This is the end of the block / 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 中的 SQL 函数 • 过程语句中可用的函数: – 单行数字 – 单行字符 – 数据类型转换 – 日期 – 时间戳 – GREATEST 和 LEAST – 其它函数 • 过程语句中不能使用的函数: – DECODE – 分组函数 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 中的 SQL 函数:示例 • 计算字符串的长度: • 将雇员姓名转换为小写形式: desc_size INTEGER(5); prod_description VARCHAR2(70):='You can use this product with your radios for higher frequency'; -- get the length of the string in prod_description desc_size:= LENGTH(prod_description); emp_name:= LOWER(emp_name); 版权所有 © 2007,Oracle。保留所有权利。 数据类型转换 • 将数据转换为可比较的数据类型 • 有两种类型: – 隐式转换 – 显式转换 • 以下是部分转换函数: – TO_CHAR – TO_DATE – TO_NUMBER – TO_TIMESTAMP 版权所有 © 2007,Oracle。保留所有权利。 数据类型转换 date_of_joining DATE:= '02-Feb-2000'; date_of_joining DATE:= 'February 02,2000'; date_of_joining DATE:= TO_DATE('February 02,2000','Month DD, YYYY'); 1 2 3 版权所有 © 2007,Oracle。保留所有权利。 嵌套块 可以嵌套 PL/SQL 块。 • 可执行部分 (BEGIN ... END) 可以包含嵌套块。 • 异常错误部分可以包含嵌套块。 版权所有 © 2007,Oracle。保留所有权利。 嵌套块 DECLARE outer_variable VARCHAR2(20):='GLOBAL VARIABLE'; BEGIN DECLARE inner_variable VARCHAR2(20):='LOCAL VARIABLE'; BEGIN DBMS_OUTPUT.PUT_LINE(inner_variable); DBMS_OUTPUT.PUT_LINE(outer_variable); END; DBMS_OUTPUT.PUT_LINE(outer_variable); END; / 示例 版权所有 © 2007,Oracle。保留所有权利。 变量的作用域和可见性 DECLARE father_name VARCHAR2(20):='Patrick'; date_of_birth DATE:='20-Apr-1972'; BEGIN DECLARE child_name VARCHAR2(20):='Mike'; date_of_birth DATE:='12-Dec-2002'; BEGIN DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name); DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth); DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name); END; DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth); END; / 1 2 版权所有 © 2007,Oracle。保留所有权利。 限定标识符 <> DECLARE father_name VARCHAR2(20):='Patrick'; date_of_birth DATE:='20-Apr-1972'; BEGIN DECLARE child_name VARCHAR2(20):='Mike'; date_of_birth DATE:='12-Dec-2002'; BEGIN DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name); DBMS_OUTPUT.PUT_LINE('Date of Birth: ' ||outer.date_of_birth); DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name); DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth); END; END; /` 版权所有 © 2007,Oracle。保留所有权利。 确定变量的作用域 <> DECLARE sal NUMBER(7,2) := 60000; comm NUMBER(7,2) := sal * 0.20; message VARCHAR2(255) := ' eligible for commission'; BEGIN DECLARE sal NUMBER(7,2) := 50000; comm NUMBER(7,2) := 0; total_comp NUMBER(7,2) := sal + comm; BEGIN message := 'CLERK not'||message; outer.comm := sal * 0.30; END; message := 'SALESMAN'||message; END; / 1 2 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 中的运算符 • 逻辑 • 算术 • 连接 • 控制运算顺序的括号 • 指数运算符 (**) 与 SQL 中的 运算符相同 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 中的运算符 示例 • 递增对循环计数的计数器。 • 设置布尔标记的值。 • 验证某个雇员编号是否包含一个值。 loop_count := loop_count + 1; good_sal := sal BETWEEN 50000 AND 150000; valid := (empno IS NOT NULL); 版权所有 © 2007,Oracle。保留所有权利。 编程准则 采用以下方法可方便代码维护: • 用注释说明代码 • 制定代码的大小写惯例 • 制定标识符和其它对象的命名惯例 • 通过缩进提高可读性 版权所有 © 2007,Oracle。保留所有权利。 缩进代码 为清楚起见,可缩进各级代码。 示例: BEGIN IF x=0 THEN y:=1; END IF; END; / DECLARE deptno NUMBER(4); location_id NUMBER(4); BEGIN SELECT department_id, location_id INTO deptno, location_id FROM departments WHERE department_name = 'Sales'; ... END; / 版权所有 © 2007,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 使用 PL/SQL 中的内置 SQL 函数 • 编写嵌套块以分解逻辑相关的功能 • 确定何时执行显式转换 • 在嵌套块中限定变量 版权所有 © 2007,Oracle。保留所有权利。 练习 3:概览 本练习包含以下主题: • 复习作用域和嵌套规则 • 编写和测试 PL/SQL 块 版权所有 © Oracle,2007。保留所有权利。 与 Oracle 服务器交互 版权所有 © Oracle,2007。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 确定哪些 SQL 语句可直接包含在 PL/SQL 可执行块中 • 在 PL/SQL 中使用 DML 语句处理数据 • 在 PL/SQL 中使用事务处理控制语句 • 使用 INTO 子句保存 SQL 语句返回的值 • 区分隐式游标和显式游标 • 使用 SQL 游标属性 版权所有 © Oracle,2007。保留所有权利。 PL/SQL 中的 SQL 语句 • 使用 SELECT 命令从数据库中检索行。 • 使用 DML 命令更改数据库中的行。 • 使用 COMMIT、ROLLBACK 或 SAVEPOINT 命令控制 事务处理。 版权所有 © Oracle,2007。保留所有权利。 PL/SQL 中的 SELECT 语句 使用 SELECT 语句检索数据库中的数据。 语法: SELECT select_list INTO {variable_name[, variable_name]... | record_name} FROM table [WHERE condition]; 版权所有 © Oracle,2007。保留所有权利。 PL/SQL 中的 SELECT 语句 • INTO 子句是必需的。 • 查询必须返回且仅返回一行。 示例 SET SERVEROUTPUT ON DECLARE fname VARCHAR2(25); BEGIN SELECT first_name INTO fname FROM employees WHERE employee_id=200; DBMS_OUTPUT.PUT_LINE(' First Name is :'||fname); END; / 版权所有 © Oracle,2007。保留所有权利。 在 PL/SQL 中检索数据 检索指定雇员的 hire_date 和 salary。 示例 DECLARE emp_hiredate employees.hire_date%TYPE; emp_salary employees.salary%TYPE; BEGIN SELECT hire_date, salary INTO emp_hiredate, emp_salary FROM employees WHERE employee_id = 100; END; / 版权所有 © Oracle,2007。保留所有权利。 SET SERVEROUTPUT ON DECLARE sum_sal NUMBER(10,2); deptno NUMBER NOT NULL := 60; BEGIN SELECT SUM(salary) -- group function INTO sum_sal FROM employees WHERE department_id = deptno; DBMS_OUTPUT.PUT_LINE ('The sum of salary is ' || sum_sal); END; / 在 PL/SQL 中检索数据 返回指定部门所有雇员的薪金总和。 示例 版权所有 © Oracle,2007。保留所有权利。 命名惯例 DECLARE hire_date employees.hire_date%TYPE; sysdate hire_date%TYPE; employee_id employees.employee_id%TYPE := 176; BEGIN SELECT hire_date, sysdate INTO hire_date, sysdate FROM employees WHERE employee_id = employee_id; END; / 版权所有 © Oracle,2007。保留所有权利。 命名惯例 • 使用命名惯例可避免 WHERE 子句中出现歧义。 • 避免将数据库列名称用作标识符。 • 由于 PL/SQL 会先检查数据库表中的列,所以可能引发 语法错误。 • 局部变量名称和形参名称的优先级高于数据库表名称的 优先级。 • 数据库表列名称的优先级高于局部变量名称的优先级。 版权所有 © Oracle,2007。保留所有权利。 使用 PL/SQL 处理数据 使用 DML 命令更改数据库表: • INSERT • UPDATE • DELETE • MERGE INSERT UPDATE DELETE MERGE 版权所有 © Oracle,2007。保留所有权利。 插入数据 将新雇员信息添加到 EMPLOYEES 表中。 示例 BEGIN INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES(employees_seq.NEXTVAL, 'Ruth', 'Cores', 'RCORES',sysdate, 'AD_ASST', 4000); END; / 版权所有 © Oracle,2007。保留所有权利。 更新数据 增加所有库管员的薪金。 示例 DECLARE sal_increase employees.salary%TYPE := 800; BEGIN UPDATE employees SET salary = salary + sal_increase WHERE job_id = 'ST_CLERK'; END; / 版权所有 © Oracle,2007。保留所有权利。 删除数据 删除 employees 表中属于部门 10 的行。 示例 DECLARE deptno employees.department_id%TYPE := 10; BEGIN DELETE FROM employees WHERE department_id = deptno; END; / 版权所有 © Oracle,2007。保留所有权利。 合并行 在 copy_emp 表中插入或更新行,以便与 employees 表匹配。 DECLARE empno employees.employee_id%TYPE := 100; BEGIN MERGE INTO copy_emp c USING employees e ON (e.employee_id = c.empno) WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, c.email = e.email, . . . WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name, . . .,e.department_id); END; / 版权所有 © Oracle,2007。保留所有权利。 SQL 游标 • 游标是指向由 Oracle 服务器分配的专用内存区域的 指针。 • 有两种类型的游标: – 隐式:由 Oracle 服务器在内部创建和管理,用于处理 SQL 语句 – 显式:由程序员显式声明 版权所有 © Oracle,2007。保留所有权利。 隐式游标的 SQL 游标属性 使用 SQL 游标属性,可以测试 SQL 语句的结果。 布尔型属性,若最近的 SQL 语句未返回任何行, 则计算结果为 TRUE SQL%NOTFOUND 布尔型属性,若最近的 SQL 语句至少返回一行, 则计算结果为 TRUE SQL%FOUND 一个整数值,表示最近的 SQL 语句所影响的行数SQL%ROWCOUNT 版权所有 © Oracle,2007。保留所有权利。 隐式游标的 SQL 游标属性 删除 employees 表中包含指定雇员 ID 的行。输出被删除 的行数。 示例 VARIABLE rows_deleted VARCHAR2(30) DECLARE empno employees.employee_id%TYPE := 176; BEGIN DELETE FROM employees WHERE employee_id = empno; :rows_deleted := (SQL%ROWCOUNT || ' row deleted.'); END; / PRINT rows_deleted 版权所有 © Oracle,2007。保留所有权利。 小结 在本课中,您应该已经学会如何: • 在 PL/SQL 中嵌入 DML 语句、事务处理控制语句和 DDL 语句 • 使用 INTO 子句;该子句是 PL/SQL 中所有 SELECT 语句的必备子句 • 区分隐式游标和显式游标 • 使用 SQL 游标属性确定 SQL 语句的结果 版权所有 © Oracle,2007。保留所有权利。 练习 4:概览 本练习包含以下主题: • 选择表中的数据 • 向表中插入数据 • 更新表中的数据 • 删除表中的记录 版权所有 © 2007,Oracle。保留所有权利。 编写控制结构 版权所有 © 2007,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 确定控制结构的用途和类型 • 构造 IF 语句 • 使用 CASE 语句和 CASE 表达式 • 构造并确定不同的循环语句 • 条件控制结构使用准则 版权所有 © 2007,Oracle。保留所有权利。 执行的控制流 for loop while 版权所有 © 2007,Oracle。保留所有权利。 IF 语句 语法: IF condition THEN statements; [ELSIF condition THEN statements;] [ELSE statements;] END IF; 版权所有 © 2007,Oracle。保留所有权利。 简单 IF 语句 DECLARE myage number:=31; BEGIN IF myage < 11 THEN DBMS_OUTPUT.PUT_LINE(' I am a child '); END IF; END; / 版权所有 © 2007,Oracle。保留所有权利。 IF THEN ELSE 语句 SET SERVEROUTPUT ON DECLARE myage number:=31; BEGIN IF myage < 11 THEN DBMS_OUTPUT.PUT_LINE(' I am a child '); ELSE DBMS_OUTPUT.PUT_LINE(' I am not a child '); END IF; END; / 版权所有 © 2007,Oracle。保留所有权利。 IF ELSIF ELSE 子句 DECLARE myage number:=31; BEGIN IF myage < 11 THEN DBMS_OUTPUT.PUT_LINE(' I am a child '); ELSIF myage < 20 THEN DBMS_OUTPUT.PUT_LINE(' I am young '); ELSIF myage < 30 THEN DBMS_OUTPUT.PUT_LINE(' I am in my twenties'); ELSIF myage < 40 THEN DBMS_OUTPUT.PUT_LINE(' I am in my thirties'); ELSE DBMS_OUTPUT.PUT_LINE(' I am always young '); END IF; END; / 版权所有 © 2007,Oracle。保留所有权利。 IF 语句中的 NULL 值 DECLARE myage number; BEGIN IF myage < 11 THEN DBMS_OUTPUT.PUT_LINE(' I am a child '); ELSE DBMS_OUTPUT.PUT_LINE(' I am not a child '); END IF; END; / 版权所有 © 2007,Oracle。保留所有权利。 CASE 表达式 • CASE 表达式选择一个结果,并返回该结果。 • 为选择结果,CASE 表达式使用表达式。这些表达式 返回的值用于从多个备选项选择一个 。 CASE selector WHEN expression1 THEN result1 WHEN expression2 THEN result2 ... WHEN expressionN THEN resultN [ELSE resultN+1] END; / 版权所有 © 2007,Oracle。保留所有权利。 CASE 表达式:示例 SET SERVEROUTPUT ON SET VERIFY OFF DECLARE grade CHAR(1) := UPPER('&grade'); appraisal VARCHAR2(20); BEGIN appraisal := CASE grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' ELSE 'No such grade' END; DBMS_OUTPUT.PUT_LINE ('Grade: '|| grade || ' Appraisal ' || appraisal); END; / 版权所有 © 2007,Oracle。保留所有权利。 搜索型 CASE 表达式 DECLARE grade CHAR(1) := UPPER('&grade'); appraisal VARCHAR2(20); BEGIN appraisal := CASE WHEN grade = 'A' THEN 'Excellent' WHEN grade IN ('B','C') THEN 'Good' ELSE 'No such grade' END; DBMS_OUTPUT.PUT_LINE ('Grade: '|| grade || ' Appraisal ' || appraisal); END; / 版权所有 © 2007,Oracle。保留所有权利。 CASE 语句 DECLARE deptid NUMBER; deptname VARCHAR2(20); emps NUMBER; mngid NUMBER:= 108; BEGIN CASE mngid WHEN 108 THEN SELECT department_id, department_name INTO deptid, deptname FROM departments WHERE manager_id=108; SELECT count(*) INTO emps FROM employees WHERE department_id=deptid; WHEN 200 THEN ... END CASE; DBMS_OUTPUT.PUT_LINE ('You are working in the '|| deptname|| ' department. There are '||emps ||' employees in this department'); END; / 版权所有 © 2007,Oracle。保留所有权利。 处理 NULL 值 使用空值 (null) 时,牢记以下规则可避免一些常见错误: • 涉及空值的简单比较运算结果始终为 NULL。 • 对空值应用逻辑运算符 NOT 将得到空结果 NULL。 • 如果条件控制语句中的条件运算结果为 NULL,则不 执行与其相关的语句序列。 版权所有 © 2007,Oracle。保留所有权利。 逻辑表 使用比较运算符构建简单的布尔型条件。 AND TRUE FALSE NULL TRUE FALSE NULL TRUE NULL NULL NULL FALSE FALSE FALSE FALSE FALSE NOT TRUE FALSE NULL FALSE TRUE NULL TRUE NULL OR TRUE FALSE NULL TRUE TRUE TRUE TRUETRUE FALSE NULL NULL NULLFALSE 版权所有 © 2007,Oracle。保留所有权利。 布尔型条件 各种情况下的 flag 值是什么? REORDER_FLAG AVAILABLE_FLAG FLAG TRUE TRUE TRUE FALSE NULL TRUE NULL FALSE flag := reorder_flag AND available_flag; ? (1) ? (2) ? (3) ? (4) 版权所有 © 2007,Oracle。保留所有权利。 迭代控制:LOOP 语句 • 循环是指多次重复一条语句或语句序列。 • 有三种循环类型: – 基本循环 – FOR 循环 – WHILE 循环 版权所有 © 2007,Oracle。保留所有权利。 基本循环 语法: LOOP statement1; . . . EXIT [WHEN condition]; END LOOP; 版权所有 © 2007,Oracle。保留所有权利。 DECLARE countryid locations.country_id%TYPE := 'CA'; loc_id locations.location_id%TYPE; counter NUMBER(2) := 1; new_city locations.city%TYPE := 'Montreal'; BEGIN SELECT MAX(location_id) INTO loc_id FROM locations WHERE country_id = countryid; LOOP INSERT INTO locations(location_id, city, country_id) VALUES((loc_id + counter), new_city, countryid); counter := counter + 1; EXIT WHEN counter > 3; END LOOP; END; / 基本循环 示例 版权所有 © 2007,Oracle。保留所有权利。 WHILE 循环 语法: 使用 WHILE 循环可在条件为 TRUE 时重复语句。 WHILE condition LOOP statement1; statement2; . . . END LOOP; 版权所有 © 2007,Oracle。保留所有权利。 WHILE 循环 示例 DECLARE countryid locations.country_id%TYPE := 'CA'; loc_id locations.location_id%TYPE; new_city locations.city%TYPE := 'Montreal'; counter NUMBER := 1; BEGIN SELECT MAX(location_id) INTO loc_id FROM locations WHERE country_id = countryid; WHILE counter <= 3 LOOP INSERT INTO locations(location_id, city, country_id) VALUES((loc_id + counter), new_city, countryid); counter := counter + 1; END LOOP; END; / 版权所有 © 2007,Oracle。保留所有权利。 FOR 循环 • 使用 FOR 循环可方便迭代次数的测试。 • 不要声明计数器,它是隐式声明的。 • “lower_bound .. upper_bound”是必需的语法。 FOR counter IN [REVERSE] lower_bound..upper_bound LOOP statement1; statement2; . . . END LOOP; 版权所有 © 2007,Oracle。保留所有权利。 FOR 循环 示例 DECLARE countryid locations.country_id%TYPE := 'CA'; loc_id locations.location_id%TYPE; new_city locations.city%TYPE := 'Montreal'; BEGIN SELECT MAX(location_id) INTO loc_id FROM locations WHERE country_id = countryid; FOR i IN 1..3 LOOP INSERT INTO locations(location_id, city, country_id) VALUES((loc_id + i), new_city, countryid ); END LOOP; END; / 版权所有 © 2007,Oracle。保留所有权利。 FOR 循环 准则 • 仅在循环内部引用计数器;计数器在循环外部没有意义。 • 不要将计数器用作赋值目标。 • 循环上下限都不得为 NULL。 版权所有 © 2007,Oracle。保留所有权利。 循环准则 • 如果循环内部的语句必须至少执行一次,请使用基本 循环。 • 如果必须在每次迭代开始时计算条件的值,请使用 WHILE 循环。 • 如果知道迭代的次数,请使用 FOR 循环。 版权所有 © 2007,Oracle。保留所有权利。 嵌套循环和标签 • 可以进行多个级别的循环嵌套。 • 使用标签可区分块和循环。 • 使用引用标签的 EXIT 语句退出外部循环。 版权所有 © 2007,Oracle。保留所有权利。 嵌套循环和标签 ... BEGIN <> LOOP counter := counter+1; EXIT WHEN counter>10; <> LOOP ... EXIT Outer_loop WHEN total_done = 'YES'; -- Leave both loops EXIT WHEN inner_done = 'YES'; -- Leave inner loop only ... END LOOP Inner_loop; ... END LOOP Outer_loop; END; / 版权所有 © 2007,Oracle。保留所有权利。 小结 在本课中,您应该已经学会了如何使用以下控制结构改变 语句的逻辑流: • 条件(IF 语句) • CASE 表达式和 CASE 语句 • 循环: – 基本循环 – FOR 循环 – WHILE 循环 • EXIT 语句 版权所有 © 2007,Oracle。保留所有权利。 练习 5:概览 本练习包含以下主题: • 使用 IF 语句执行条件操作 • 使用循环结构执行迭代步骤 版权所有 © 2007,Oracle。保留所有权利。 使用组合数据类型 版权所有 © 2007,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 创建用户定义的 PL/SQL 记录 • 创建具有 %ROWTYPE 属性的记录 • 创建 INDEX BY 表 • 创建记录的 INDEX BY 表 • 描述记录、表和记录表之间的差异 版权所有 © 2007,Oracle。保留所有权利。 组合数据类型 • 可以存放多个值(不同于标量类型) • 有两种类型: – PL/SQL 记录 – PL/SQL 集合 – INDEX BY 表或关联性数组 – 嵌套表 – VARRAY 版权所有 © 2007,Oracle。保留所有权利。 组合数据类型 • 如果要存储不同类型的值,但一次只存储一个事件, 可使用 PL/SQL 记录。 • 如果要存储相同数据类型的值,可使用 PL/SQL 集合。 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 记录 • 必须包含数据类型为任意标量、RECORD 或 INDEX BY 表的一个或多个组件(称为字段) • 与大多数 3GL 语言(包括 C 和 C++)中的结构类似 • 是用户定义的,可以是表中某行的一部分 • 将字段集合作为逻辑单元处理 • 便于从表中提取一行数据进行处理 版权所有 © 2007,Oracle。保留所有权利。 创建 PL/SQL 记录 语法: TYPE type_name IS RECORD (field_declaration[, field_declaration]…); field_name {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE} [[NOT NULL] {:= | DEFAULT} expr] identifier type_name; 1 2 field_declaration: 版权所有 © 2007,Oracle。保留所有权利。 创建 PL/SQL 记录 声明变量,用于存储新雇员的姓名、职务和薪金。 示例 ... TYPE emp_record_type IS RECORD (last_name VARCHAR2(25), job_id VARCHAR2(10), salary NUMBER(8,2)); emp_record emp_record_type; ... 版权所有 © 2007,Oracle。保留所有权利。 PL/SQL 记录结构 示例 100 King AD_PRES employee_id number(6) last_name varchar2(25) job_id varchar2(10) 字段 2(数据类型) 字段 3(数据类型)字段 1(数据类型) 字段 2(数据类型) 字段 3(数据类型)字段 1(数据类型) 版权所有 © 2007,Oracle。保留所有权利。 %ROWTYPE 属性 • 根据数据库表或视图中的列集合声明变量。 • 在 %ROWTYPE 前加上数据库表或视图。 • 记录中的字段将从表或视图的列中提取其名称和数据 类型。 语法: DECLARE identifier reference%ROWTYPE; 版权所有 © 2007,Oracle。保留所有权利。 %ROWTYPE 属性 • 根据数据库表或视图中的列集合声明变量。 • 在 %ROWTYPE 前加上数据库表。 • 记录中的字段将从表或视图的列中提取其名称和数据 类型。 版权所有 © 2007,Oracle。保留所有权利。 使用 %ROWTYPE 的优点 • 不需要知道底层数据库列的数目和数据类型 - 实际上, 它们可能会在运行时发生更改。 • 使用 SELECT * 语句检索行时,可以利用 %ROWTYPE 属性。 版权所有 © 2007,Oracle。保留所有权利。 %ROWTYPE 属性 ... DEFINE employee_number = 124 DECLARE emp_rec employees%ROWTYPE; BEGIN SELECT * INTO emp_rec FROM employees WHERE employee_id = &employee_number; INSERT INTO retired_emps(empno, ename, job, mgr, hiredate, leavedate, sal, comm, deptno) VALUES (emp_rec.employee_id, emp_rec.last_name, emp_rec.job_id,emp_rec.manager_id, emp_rec.hire_date, SYSDATE, emp_rec.salary, emp_rec.commission_pct, emp_rec.department_id); END; / 版权所有 © 2007,Oracle。保留所有权利。 使用 %ROWTYPE 插入记录 ... DEFINE employee_number = 124 DECLARE emp_rec retired_emps%ROWTYPE; BEGIN SELECT employee_id, last_name, job_id, manager_id, hire_date, hire_date, salary, commission_pct, department_id INTO emp_rec FROM employees WHERE employee_id = &employee_number; INSERT INTO retired_emps VALUES emp_rec; END; / SELECT * FROM retired_emps; 版权所有 © 2007,Oracle。保留所有权利。 使用记录更新表中的行 SET SERVEROUTPUT ON SET VERIFY OFF DEFINE employee_number = 124 DECLARE emp_rec retired_emps%ROWTYPE; BEGIN SELECT * INTO emp_rec FROM retired_emps; emp_rec.leavedate:=SYSDATE; UPDATE retired_emps SET ROW = emp_rec WHERE empno=&employee_number; END; / SELECT * FROM retired_emps; 版权所有 © 2007,Oracle。保留所有权利。 INDEX BY 表或关联性数组 • 是包含两列的 PL/SQL 结构: – 整数数据类型或字符串数据类型的主键 – 标量数据类型或记录数据类型的列 • 无大小限制。但是,大小取决于关键数据类型可以存放 的值。 版权所有 © 2007,Oracle。保留所有权利。 创建 INDEX BY 表 语法: 声明一个用于存储雇员姓氏的 INDEX BY 表: TYPE type_name IS TABLE OF {column_type | variable%TYPE | table.column%TYPE} [NOT NULL] | table%ROWTYPE [INDEX BY PLS_INTEGER | BINARY_INTEGER | VARCHAR2()]; identifier type_name; ... TYPE ename_table_type IS TABLE OF employees.last_name%TYPE INDEX BY PLS_INTEGER;... ename_table ename_table_type; 版权所有 © 2007,Oracle。保留所有权利。 INDEX BY 表结构 唯一键 值 ... ... 1 Jones 5Smith 3 Maduro ... ... PLS_INTEGER 标量 版权所有 © 2007,Oracle。保留所有权利。 创建 INDEX BY 表 DECLARE TYPE ename_table_type IS TABLE OF employees.last_name%TYPE INDEX BY PLS_INTEGER; TYPE hiredate_table_type IS TABLE OF DATE INDEX BY PLS_INTEGER; ename_table ename_table_type; hiredate_table hiredate_table_type; BEGIN ename_table(1) := 'CAMERON'; hiredate_table(8) := SYSDATE + 7; IF ename_table.EXISTS(1) THEN INSERT INTO ... ... END; / 版权所有 © 2007,Oracle。保留所有权利。 使用 INDEX BY 表方法 以下方法可以进一步简化 INDEX BY 表的使用: • EXISTS • COUNT • FIRST and LAST • PRIOR • NEXT • DELETE 版权所有 © 2007,Oracle。保留所有权利。 记录的 INDEX BY 表 定义一个 INDEX BY 表变量,用于存放表中的一个整行。 示例 DECLARE TYPE dept_table_type IS TABLE OF departments%ROWTYPE INDEX BY PLS_INTEGER; dept_table dept_table_type; -- Each element of dept_table is a record 版权所有 © 2007,Oracle。保留所有权利。 记录的 INDEX BY 表:示例 SET SERVEROUTPUT ON DECLARE TYPE emp_table_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER; my_emp_table emp_table_type; max_count NUMBER(3):= 104; BEGIN FOR i IN 100..max_count LOOP SELECT * INTO my_emp_table(i) FROM employees WHERE employee_id = i; END LOOP; FOR i IN my_emp_table.FIRST..my_emp_table.LAST LOOP DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name); END LOOP; END; / 版权所有 © 2007,Oracle。保留所有权利。 嵌套表 1 2 3 4 .. 2 GB Bombay Sydney Oxford London .... 版权所有 © 2007,Oracle。保留所有权利。 Bombay Sydney Oxford London .... VARRAY Tokyo 1 2 3 4 .. 10 版权所有 © 2007,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 定义和引用组合数据类型的 PL/SQL 变量 – PL/SQL 记录 – INDEX BY 表 – 记录的 INDEX BY 表 • 使用 %ROWTYPE 属性定义 PL/SQL 记录 版权所有 © 2007,Oracle。保留所有权利。 练习 6:概览 本练习包含以下主题: • 声明 INDEX BY 表 • 使用 INDEX BY 表处理数据 • 声明 PL/SQL 记录 • 使用 PL/SQL 记录处理数据 版权所有 © 2007,Oracle。保留所有权利。 使用显式游标 版权所有 © 2007,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 区分隐式游标和显式游标 • 讨论使用显式游标的原因 • 声明和控制显式游标 • 使用简单循环和游标 FOR 循环提取数据 • 声明和使用包含参数的游标 • 使用 FOR UPDATE 子句锁定行 • 使用 WHERE CURRENT 子句引用当前行 版权所有 © 2007,Oracle。保留所有权利。 游标 Oracle 服务器执行的每个 SQL 语句都有一个关联的单独 游标: • 隐式游标:由 PL/SQL 声明和管理,用于所有 DML 和 PL/SQL 的 SELECT 语句 • 显式游标:由程序员声明和管理 版权所有 © 2007,Oracle。保留所有权利。 显式游标操作 活动集 表 100 King AD_PRES 101 Kochhar AD_VP 102 De Haan AD_VP . . . . . . . . . 139 Seo ST_CLERK 140 Patel ST_CLERK . . . 版权所有 © 2007,Oracle。保留所有权利。 控制显式游标 • 将当前行加 载到变量中 • 测试现有 的行 • 找到行时返 回到 FETCH 否 • 释放活动集 是 • 创建一个 指定的 SQL 区域 • 确定活动集 FETCH EMPTY? CLOSEDECLARE OPEN 版权所有 © 2007,Oracle。保留所有权利。 控制显式游标 提取一行。 关闭游标。 游标 指针 打开游标。1 2 3 游标 指针 游标 指针 版权所有 © 2007,Oracle。保留所有权利。 声明游标 语法: 示例 CURSOR cursor_name IS select_statement; DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id =30; DECLARE locid NUMBER:= 1700; CURSOR dept_cursor IS SELECT * FROM departments WHERE location_id = locid; ... 版权所有 © 2007,Oracle。保留所有权利。 打开游标 DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id =30; ... BEGIN OPEN emp_cursor; 版权所有 © 2007,Oracle。保留所有权利。 提取游标中的数据 SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id =30; empno employees.employee_id%TYPE; lname employees.last_name%TYPE; BEGIN OPEN emp_cursor; FETCH emp_cursor INTO empno, lname; DBMS_OUTPUT.PUT_LINE( empno ||' '||lname); ... END; / 版权所有 © 2007,Oracle。保留所有权利。 提取游标中的数据 SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id =30; empno employees.employee_id%TYPE; lname employees.last_name%TYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO empno, lname; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE( empno ||' '||lname); END LOOP; ... END; / 版权所有 © 2007,Oracle。保留所有权利。 关闭游标 ... LOOP FETCH emp_cursor INTO empno, lname; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE( empno ||' '||lname); END LOOP; CLOSE emp_cursor; END; / 版权所有 © 2007,Oracle。保留所有权利。 游标和记录 通过将值提取到 PL/SQL 记录,处理活动集中的行。 DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id =30; emp_record emp_cursor%ROWTYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_record; ... 版权所有 © 2007,Oracle。保留所有权利。 游标 FOR 循环 语法: • 游标 FOR 循环是处理显式游标的快捷方式。 • 出现隐式打开、提取、退出和关闭。 • 记录是隐式声明的。 FOR record_name IN cursor_name LOOP statement1; statement2; . . . END LOOP; 版权所有 © 2007,Oracle。保留所有权利。 游标 FOR 循环 SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id =30; BEGIN FOR emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE( emp_record.employee_id ||' ' ||emp_record.last_name); END LOOP; END; / 版权所有 © 2007,Oracle。保留所有权利。 显式游标属性 获取游标的状态信息。 Boolean 游标打开时,计算结果为 TRUE 最近的提取操作未返回行时,计算结果 为 TRUE 最近的提取操作返回行时,计算结果为 TRUE;是对 %NOTFOUND 的补充 计算结果为迄今返回的总行数 Boolean Boolean Number 属性 类型 说明 %ISOPEN %NOTFOUND %FOUND %ROWCOUNT 版权所有 © 2007,Oracle。保留所有权利。 %ISOPEN 属性 • 仅在游标打开时提取行。 • 执行提取之前,使用 %ISOPEN 游标属性测试游标是否 已打开。 示例 IF NOT emp_cursor%ISOPEN THEN OPEN emp_cursor; END IF; LOOP FETCH emp_cursor... 版权所有 © 2007,Oracle。保留所有权利。 %ROWCOUNT 和 %NOTFOUND:示例 SET SERVEROUTPUT ON DECLARE empno employees.employee_id%TYPE; ename employees.last_name%TYPE; CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO empno, ename; EXIT WHEN emp_cursor%ROWCOUNT > 10 OR emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(TO_CHAR(empno) ||' '|| ename); END LOOP; CLOSE emp_cursor; END ; / 版权所有 © 2007,Oracle。保留所有权利。 使用子查询的游标 FOR 循环 不需要声明游标。 示例 SET SERVEROUTPUT ON BEGIN FOR emp_record IN (SELECT employee_id, last_name FROM employees WHERE department_id =30) LOOP DBMS_OUTPUT.PUT_LINE( emp_record.employee_id ||' '||emp_record.last_name); END LOOP; END; / 版权所有 © 2007,Oracle。保留所有权利。 包含参数的游标 语法: • 打开游标和执行查询时,将参数值传递给游标。 • 多次打开显式游标,每次使用不同的活动集。 CURSOR cursor_name [(parameter_name datatype, ...)] IS select_statement; OPEN cursor_name(parameter_value,.....) ; 版权所有 © 2007,Oracle。保留所有权利。 包含参数的游标 SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor (deptno NUMBER) IS SELECT employee_id, last_name FROM employees WHERE department_id = deptno; dept_id NUMBER; lname VARCHAR2(15); BEGIN OPEN emp_cursor (10); ... CLOSE emp_cursor; OPEN emp_cursor (20); ... 版权所有 © 2007,Oracle。保留所有权利。 FOR UPDATE 子句 语法: • 使用显式锁定拒绝访问事务处理期间的其它会话。 • 更新或删除之前锁定行。 SELECT ... FROM ... FOR UPDATE [OF column_reference][NOWAIT | WAIT n]; 版权所有 © 2007,Oracle。保留所有权利。 WHERE CURRENT OF 子句 语法: • 使用游标更新或删除当前行。 • 在游标查询中包含 FOR UPDATE 子句可先锁定行。 • 使用 WHERE CURRENT OF 子句可引用显式游标中的 当前行。 WHERE CURRENT OF cursor ; UPDATE employees SET salary = ... WHERE CURRENT OF emp_cursor; 版权所有 © 2007,Oracle。保留所有权利。 包含子查询的游标 示例 DECLARE CURSOR my_cursor IS SELECT t1.department_id, t1.department_name, t2.staff FROM departments t1, (SELECT department_id, COUNT(*) AS staff FROM employees GROUP BY department_id) t2 WHERE t1.department_id = t2.department_id AND t2.staff >= 3; ... 版权所有 © 2007,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 区分游标类型: – 隐式游标用于所有 DML 语句和单行查询。 – 显式游标用于零行、一行或多行的查询。 • 创建和处理显式游标 • 使用简单循环和游标 FOR 循环处理游标中的多个行 • 使用游标属性确定游标状态 • 使用 FOR UPDATE 和 WHERE CURRENT OF 子句更新或 删除当前提取的行 版权所有 © 2007,Oracle。保留所有权利。 练习 7:概览 本练习包含以下主题: • 声明和使用显式游标查询表中的行 • 使用游标 FOR 循环 • 应用游标属性测试游标状态 • 声明和使用包含参数的游标 • 使用 FOR UPDATE 和 WHERE CURRENT OF 子句 版权所有 © 2007,Oracle。保留所有权利。 处理异常错误 版权所有 © 2007,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 定义 PL/SQL 异常错误 • 识别未处理的异常错误 • 列出并使用不同类型的 PL/SQL 异常错误处理程序 • 捕获意外的错误 • 描述嵌套块中异常错误传播的影响 • 定制 PL/SQL 异常错误消息 版权所有 © 2007,Oracle。保留所有权利。 异常错误示例 SET SERVEROUTPUT ON DECLARE lname VARCHAR2(15); BEGIN SELECT last_name INTO lname FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John''s last name is : ' ||lname); END; / 版权所有 © 2007,Oracle。保留所有权利。 异常错误示例 SET SERVEROUTPUT ON DECLARE lname VARCHAR2(15); BEGIN SELECT last_name INTO lname FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John''s last name is : ' ||lname); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (' Your select statement retrieved multiple rows. Consider using a cursor.'); END; / 版权所有 © 2007,Oracle。保留所有权利。 处理 PL/SQL 的异常错误 • 异常错误是指程序执行过程中引发的 PL/SQL 错误。 • 引发异常错误的方式有: – 由 Oracle 服务器隐式引发 – 由程序显式引发 • 处理异常错误的方式有: – 使用处理程序捕获异常错误 – 将其传播到调用环境 版权所有 © 2007,Oracle。保留所有权利。 处理异常错误 引发了 异常错误 是否捕获了 异常错误? 是 执行 EXCEPTION 部分的语句 正常终止 否 突然终止 传播异常错误 版权所有 © 2007,Oracle。保留所有权利。 异常错误类型 • 预定义的 Oracle 服务器 • 非预定义的 Oracle 服务器 • 用户定义 } 隐式引发 显式引发 版权所有 © 2007,Oracle。保留所有权利。 捕获异常错误 语法: EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; . . . [WHEN exception3 [OR exception4 . . .] THEN statement1; statement2; . . .] [WHEN OTHERS THEN statement1; statement2; . . .] 版权所有 © 2007,Oracle。保留所有权利。 捕获异常错误的准则 • 异常错误处理部分以关键字 EXCEPTION 开始。 • 允许使用多个异常错误处理程序。 • 退出块之前,只处理一个处理程序。 • WHEN OTHERS 是最后一个子句。 版权所有 © 2007,Oracle。保留所有权利。 捕获预定义的 Oracle 服务器错误 • 在异常错误处理例程中引用预定义名称。 • 预定义异常错误示例: – NO_DATA_FOUND – TOO_MANY_ROWS – INVALID_CURSOR – ZERO_DIVIDE – DUP_VAL_ON_INDEX 版权所有 © 2007,Oracle。保留所有权利。 捕获非预定义的 Oracle 服务器错误 声明部分 声明 命名异常错误 使用 PRAGMA EXCEPTION_INIT EXCEPTION 部分 处理引发的异常错误 关联 引用 版权所有 © 2007,Oracle。保留所有权利。 非预定义的错误 捕获 Oracle 服务器错误编号 –01400(“不能插入 NULL”): SET SERVEROUTPUT ON DECLARE insert_excep EXCEPTION; PRAGMA EXCEPTION_INIT (insert_excep, -01400); BEGIN INSERT INTO departments (department_id, department_name) VALUES (280, NULL); EXCEPTION WHEN insert_excep THEN DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED'); DBMS_OUTPUT.PUT_LINE(SQLERRM); END; / 1 2 3 版权所有 © 2007,Oracle。保留所有权利。 用于捕获异常错误的函数 • SQLCODE:返回错误代码的数字值 • SQLERRM:返回与错误编号关联的消息 版权所有 © 2007,Oracle。保留所有权利。 用于捕获异常错误的函数 示例 DECLARE error_code NUMBER; error_message VARCHAR2(255); BEGIN ... EXCEPTION ... WHEN OTHERS THEN ROLLBACK; error_code := SQLCODE ; error_message := SQLERRM ; INSERT INTO errors (e_user, e_date, error_code, error_message) VALUES(USER,SYSDATE,error_code, error_message); END; / 版权所有 © 2007,Oracle。保留所有权利。 捕获用户定义的异常错误 使用 RAISE 语句 显式引发异常错误 声明部分 命名异常错误 可执行部分 异常错误处理部分 处理引发的异常错误 引发 引用声明 版权所有 © 2007,Oracle。保留所有权利。 捕获用户定义的异常错误 ... ACCEPT deptno PROMPT 'Please enter the department number:' ACCEPT name PROMPT 'Please enter the department name:' DECLARE invalid_department EXCEPTION; name VARCHAR2(20):='&name'; deptno NUMBER :=&deptno; BEGIN UPDATE departments SET department_name = name WHERE department_id = deptno; IF SQL%NOTFOUND THEN RAISE invalid_department; END IF; COMMIT; EXCEPTION WHEN invalid_department THEN DBMS_OUTPUT.PUT_LINE('No such department id.'); END; / 1 2 3 版权所有 © 2007,Oracle。保留所有权利。 调用环境 在封装块的异常错误处理例程中捕获异常错误封闭的 PL/SQL 块 通过 SQLCA 数据结构访问异常错误编号预编译器应用程序 通过 ERROR_CODE 和 ERROR_TEXT 打包函数, 在 ON-ERROR 触发器中访问错误编号和消息 Oracle Developer Forms 在屏幕上显示错误编号和消息Procedure Builder 在屏幕上显示错误编号和消息iSQL*Plus 版权所有 © 2007,Oracle。保留所有权利。 传播子块中的异常错误 DECLARE . . . no_rows exception; integrity exception; PRAGMA EXCEPTION_INIT (integrity, -2292); BEGIN FOR c_record IN emp_cursor LOOP BEGIN SELECT ... UPDATE ... IF SQL%NOTFOUND THEN RAISE no_rows; END IF; END; END LOOP; EXCEPTION WHEN integrity THEN ... WHEN no_rows THEN ... END; / 子块可以处理异常错 误,或将异常错误传 递到封装块。 版权所有 © 2007,Oracle。保留所有权利。 RAISE_APPLICATION_ERROR 过程 语法: • 可以使用此过程从存储子程序发出用户定义的错误消息。 • 可以向应用程序报告错误,避免返回未处理的异常错误。 raise_application_error (error_number, message[, {TRUE | FALSE}]); 版权所有 © 2007,Oracle。保留所有权利。 RAISE_APPLICATION_ERROR 过程 • 在两个不同的地方使用: – 可执行部分 – 异常错误部分 • 通过与其它 Oracle 服务器错误一致的方式,向用户 返回错误条件 版权所有 © 2007,Oracle。保留所有权利。 RAISE_APPLICATION_ERROR 过程 可执行部分: 异常错误部分: BEGIN ... DELETE FROM employees WHERE manager_id = v_mgr; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20202, 'This is not a valid manager'); END IF; ... ... EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201, 'Manager is not a valid employee.'); END; / 版权所有 © 2007,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 定义 PL/SQL 异常错误 • 在 PL/SQL 块中添加一个 EXCEPTION 部分,用于处理 运行时异常错误 • 处理不同类型的异常错误: – 预定义的异常错误 – 非预定义的异常错误 – 用户定义的异常错误 • 传播嵌套块中的异常错误并调用应用程序 版权所有 © 2007,Oracle。保留所有权利。 练习 8:概览 本练习包含以下主题: • 处理指定的异常错误 • 创建并调用户定义的异常错误 版权所有 © 2007,Oracle。保留所有权利。 创建存储过程和函数 版权所有 © 2007,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 区分匿名块和子程序 • 创建并从匿名块中调用简单过程 • 创建简单函数 • 创建接受参数的简单函数 • 区分过程和函数 版权所有 © 2007,Oracle。保留所有权利。 过程和函数 • 是已命名的 PL/SQL 块 • 称为 PL/SQL 子程序 • 具有与匿名块相似的块结构: – 可选的声明部分(不带有 DECLARE 关键字) – 必需的可执行部分 – 可选的异常错误处理部分 版权所有 © 2007,Oracle。保留所有权利。 匿名块和子程序的区别 可以接受参数不能接受参数 称为函数的子程序必须返回值不返回值 已命名,因此可由其它应用程序调用不能由其它应用程序调用 存储在数据库中不存储在数据库中 只需编译一次每次都要编译 已命名的 PL/SQL 块未命名的 PL/SQL 块 子程序匿名块 版权所有 © 2007,Oracle。保留所有权利。 过程:语法 CREATE [OR REPLACE] PROCEDURE procedure_name [(argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .)] IS|AS procedure_body; 版权所有 © 2007,Oracle。保留所有权利。 过程:示例 ... CREATE TABLE dept AS SELECT * FROM departments; CREATE PROCEDURE add_dept IS dept_id dept.department_id%TYPE; dept_name dept.department_name%TYPE; BEGIN dept_id:=280; dept_name:='ST-Curriculum'; INSERT INTO dept(department_id,department_name) VALUES(dept_id,dept_name); DBMS_OUTPUT.PUT_LINE(' Inserted '|| SQL%ROWCOUNT ||' row '); END; / 版权所有 © 2007,Oracle。保留所有权利。 调用过程 BEGIN add_dept; END; / SELECT department_id, department_name FROM dept WHERE department_id=280; 版权所有 © 2007,Oracle。保留所有权利。 函数:语法 CREATE [OR REPLACE] FUNCTION function_name [(argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .)] RETURN datatype IS|AS function_body; 版权所有 © 2007,Oracle。保留所有权利。 函数:示例 CREATE FUNCTION check_sal RETURN Boolean IS dept_id employees.department_id%TYPE; empno employees.employee_id%TYPE; sal employees.salary%TYPE; avg_sal employees.salary%TYPE; BEGIN empno:=205; SELECT salary,department_id INTO sal,dept_id FROM employees WHERE employee_id= empno; SELECT avg(salary) INTO avg_sal FROM employees WHERE department_id=dept_id; IF sal > avg_sal THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; / 版权所有 © 2007,Oracle。保留所有权利。 调用函数 SET SERVEROUTPUT ON BEGIN IF (check_sal IS NULL) THEN DBMS_OUTPUT.PUT_LINE('The function returned NULL due to exception'); ELSIF (check_sal) THEN DBMS_OUTPUT.PUT_LINE('Salary > average'); ELSE DBMS_OUTPUT.PUT_LINE('Salary < average'); END IF; END; / 版权所有 © 2007,Oracle。保留所有权利。 将参数传递给函数 DROP FUNCTION check_sal; CREATE FUNCTION check_sal(empno employees.employee_id%TYPE) RETURN Boolean IS dept_id employees.department_id%TYPE; sal employees.salary%TYPE; avg_sal employees.salary%TYPE; BEGIN SELECT salary,department_id INTO sal,dept_id FROM employees WHERE employee_id=empno; SELECT avg(salary) INTO avg_sal FROM employees WHERE department_id=dept_id; IF sal > avg_sal THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION ... ... 版权所有 © 2007,Oracle。保留所有权利。 使用参数调用函数 BEGIN DBMS_OUTPUT.PUT_LINE('Checking for employee with id 205'); IF (check_sal(205) IS NULL) THEN DBMS_OUTPUT.PUT_LINE('The function returned NULL due to exception'); ELSIF (check_sal(205)) THEN DBMS_OUTPUT.PUT_LINE('Salary > average'); ELSE DBMS_OUTPUT.PUT_LINE('Salary < average'); END IF; DBMS_OUTPUT.PUT_LINE('Checking for employee with id 70'); IF (check_sal(70) IS NULL) THEN DBMS_OUTPUT.PUT_LINE('The function returned NULL due to exception'); ELSIF (check_sal(70)) THEN ... END IF; END; / 版权所有 © 2007,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 创建简单过程 • 从匿名块中调用过程 • 创建简单函数 • 创建可接受参数的简单函数 • 从匿名块中调用函数 版权所有 © 2007,Oracle。保留所有权利。 练习 9:概览 本练习包含以下主题: • 将现有匿名块转换为过程 • 修改过程使其可以接受参数 • 编写匿名块以调用过程
还剩218页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 20 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

yangjueshe

贡献于2010-10-07

下载需要 20 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf