第五章 使用 PL/SQL

回顾 同义词是现有数据库对象的别名 序列用于生成唯一、连续的序号 视图是基于一个或多个表的虚拟表 索引是与表相关的一个可选结构，用于提高 SQL 语句执行的性能 索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引 索引组织表基于主键访问数据

目标 理解 PL/SQL 功能和特点 了解数据类型及其用法 理解逻辑比较 理解控制结构 掌握错误处理

PL/SQL 简介 PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言 PL/SQL 是对 SQL 的扩展 支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构 可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑 与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性

PL/SQL 的优点 2-1 支持 SQL，在 PL/SQL 中可以使用： 支持面向对象编程 (OOP) 数据操纵命令 事务控制命令 游标控制 SQL 函数和 SQL 运算符 支持面向对象编程 (OOP) 可移植性，可运行在任何操作系统和平台上的Oralce 数据库 更佳的性能，PL/SQL 经过编译执行 Procedure Begin ProcedureCall SQL Command … End Oracle 用户将整个语句块发送给 Oracle

PL/SQL 的优点 2-2 与 SQL 紧密集成，简化数据处理。 安全性，可以通过存储过程限制用户对数据的访问 支持所有 SQL 数据类型 支持 NULL 值 支持 %TYPE 和 %ROWTYPE 属性类型 PL/SQL SQL 安全性，可以通过存储过程限制用户对数据的访问

PL/SQL 的体系结构 PL/SQL 引擎驻留在 Oracle 服务器中 该引擎接受 PL/SQL 块并对其进行编译执行 用户 过程语句 执行器 将PL/SQL 块发送给 Oracle 服务器 将结果发送给用户 执行过程语句 用户 引擎将 SQL 语句发送给SQL 语句执行器 执行 SQL 语句

包含 SQL 和 PL/SQL 语句的可执行部分 PL/SQL 块是构成 PL/SQL 程序的基本单元 将逻辑上相关的声明和语句组合在一起 PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分 [DECLARE declarations] BEGIN executable statements [EXCEPTION handlers] END; DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand FROM Products WHERE product = '芭比娃娃' FOR UPDATE OF quantity; IF qty_on_hand > 0 THEN UPDATE Products SET quantity = quantity + 1 WHERE product = '芭比娃娃'; INSERT INTO purchase_record VALUES ('已购买芭比娃娃', SYSDATE); END IF; COMMIT; EXCEPTION /* 异常处理语句 */ WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM); END; 声明部分定义变量、游标和自定义异常 包含 SQL 和 PL/SQL 语句的可执行部分 指定出现错误时需要执行的操作

变量和常量 1-2 PL/SQL 块中可以使用变量和常量 声明变量和常量的语法： 给变量赋值有两种方法： 在声明部分声明，使用前必须先声明 声明时必须指定数据类型，每行声明一个标识符 在可执行部分的 SQL 语句和过程语句中使用 声明变量和常量的语法： identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; 给变量赋值有两种方法： 使用赋值语句 := 使用 SELECT INTO 语句

变量和常量 2-2 DECLARE icode VARCHAR2(6); p_catg VARCHAR2(20); p_rate NUMBER; c_rate CONSTANT NUMBER := 0.10; BEGIN ... icode := 'i205'; SELECT p_category, itemrate * c_rate INTO p_catg, p_rate FROM itemfile WHERE itemcode = icode; END;

数据类型 PL/SQL 支持的内置数据类型 数据类型 标量类型 LOB类型 属性类型 数字 存储非结构化数据块 BFILE 字符 BLOB %TYPE %ROWTYPE 布尔型 CLOB 提供某个变量或数据库表列的数据类型 提供表示表中一行的记录类型 日期时间 NCLOB

数字数据类型 指定数值的存储格式 数字数据类型 BINARY_INTEGER NUMBER PLS_INTEGER 存储整数、实数和浮点数 存储有符号整数，可使算术计算快速而有效 NATURAL DECIMAL NATURALLN FLOAT POSITIVE INTEGER POSITIVEN REAL SIGNTYPE

字符数据类型 字符数据类型包括： PL/SQL 的数据类型与 SQL数据类型的比较 CHAR VARCHAR2 LONG RAW 1..2000 1..32767 LONG 1..2GB 1..32760 LONG RAW RAW VARCHAR2 1..4000

日期时间和布尔数据类型 日期时间类型 布尔数据类型 存储日期和时间数据 常用的两种日期时间类型 此类别只有一种类型，即BOOLEAN类型 DATE TIMESTAMP 布尔数据类型 此类别只有一种类型，即BOOLEAN类型 用于存储逻辑值(TRUE、FALSE和NULL) 不能向数据库中插入BOOLEAN数据 不能将列值保存到BOOLEAN变量中 只能对BOOLEAN变量执行逻辑操作

LOB 数据类型 2-1 用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。 LOB 数据类型可存储最大 4GB的数据。 CLOB 将大型字符数据存储在数据库中 NCLOB 存储大型UNICODE字符数据 BFILE 将大型二进制对象存储在操作系统文件中

从CLOB数据中读取24个字符存储到 output_var 变量中 DBMS_LOB程序包用于操纵 LOB 数据 SET SERVEROUTPUT ON DECLARE clob_var CLOB; amount INTEGER; offset INTEGER; output_var VARCHAR2(100); BEGIN SELECT chapter_text INTO clob_var FROM my_book_text WHERE chapter_id=5; amount := 24; -- 要读取的字符数 offset := 1; -- 起始位置 DBMS_LOB.READ(clob_var,amount,offset,output_var); DBMS_OUTPUT.PUT_LINE(output_var); END; / 从表中选择 CLOB 定位符 到 clob_var变量中 从CLOB数据中读取24个字符存储到 output_var 变量中 显示读到的信息

属性类型 用于引用数据库列的数据类型，以及表示表中一行的记录类型 属性类型有两种： 使用属性类型的优点： %TYPE - 引用变量和数据库列的数据类型 %ROWTYPE - 提供表示表中一行的记录类型 使用属性类型的优点： 不需要知道被引用的表列的具体类型 如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变 icode itemfile.itemcode%TYPE; emp_rec scott.emp%ROWTYPE;

逻辑比较 逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式 布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接 布尔表达式有三种类型： 数字布尔型 字符布尔型 日期布尔型 逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式 布尔表达式由关系运算符与变量或常量组成 关系运算符 说明 = 比较两个变量是否相等，如果值相当，则返回 True <>, != 比较两个变量，如果不相等，则返回 True < 比较两个变量，检查值 1 是否小于值 2 > 比较两个变量，检查值 1 是否大于 值 2 <= 比较两个变量，检查变量 1 是否小于等于变量 2 >= 比较两个变量，检查变量 1 是否大于等于变量 2

控制结构 PL/SQL 支持的流程控制结构： 条件控制 循环控制 顺序控制 IF 语句 CASE 语句 LOOP 循环 WHILE 循环 FOR 循环 顺序控制 GOTO 语句 NULL 语句

条件控制 2-1 IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF DECLARE icode VARCHAR2(4); irate NUMBER; BEGIN icode := 'i203'; SELECT itemrate INTO irate FROM itemfile WHERE itemcode = icode; IF irate > 200 THEN UPDATE itemfile SET itemrate = itemrate - 200 WHERE itemcode = icode; ELSE UPDATE itemfile SET itemrate = itemrate - 50 END IF; DBMS_OUTPUT.PUT_LINE('itemrate='|| irate); END;

条件控制 2-2 CASE 语句用于根据单个变量或表达式与多个值进行比较 执行 CASE 语句前，先计算选择器的值 BEGIN CASE ‘&grade’ WHEN ’A’ THEN DBMS_OUTPUT.PUT_LINE(’优异’); WHEN ’B’ THEN DBMS_OUTPUT.PUT_LINE (优秀’); WHEN ’C’ THEN DBMS_OUTPUT.PUT_LINE (’良好’); WHEN ’D’ THEN DBMS_OUTPUT.PUT_LINE (’一般’); WHEN ’F’ THEN DBMS_OUTPUT.PUT_LINE (’较差’); ELSE DBMS_OUTPUT.PUT_LINE (’没有此成绩’); END CASE; END;

循环控制 循环控制用于重复执行一系列语句 循环控制语句包括： 循环控制的三种类型： LOOP、EXIT 和 EXIT WHEN WHILE - 根据条件循环 FOR - 循环固定的次数 LOOP sequence_of_statements END LOOP; WHILE condition LOOP sequence_of_statements END LOOP; FOR counter IN [REVERSE] value1..value2 LOOP sequence_of_statements END LOOP;

顺序控制 顺序控制用于按顺序执行语句 顺序控制语句包括： GOTO 语句 - 无条件地转到标签指定的语句 NULL 语句 - 什么也不做的空语句 DECLARE qtyhand itemfile.qty_hand%type; relevel itemfile.re_level%type; BEGIN SELECT qty_hand,re_level INTO qtyhand,relevel FROM itemfile WHERE itemcode = 'i201'; IF qtyhand < relevel THEN GOTO updation; ELSE GOTO quit; END IF; <<updation>> UPDATE itemfile SET qty_hand = qty_hand + re_level WHERE itemcode = 'i201'; <<quit>> NULL; END;

动态 SQL 动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句 DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行 执行动态 SQL 的语法： EXECUTE IMMEDIATE dynamic_sql_string [INTO define_variable_list] [USING bind_argument_list]; DECLARE sql_stmt VARCHAR2(200); emp_id NUMBER(4) := 7566; emp_rec emp%ROWTYPE; BEGIN EXECUTE IMMEDIATE 'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)'; sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; END;

错误处理 2-1 在运行程序时出现的错误叫做异常 发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分 异常有两种类型： 预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发 用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发

错误处理 2-2 处理预定义异常 处理用户定义异常 DECLARE invalidCATEGORY EXCEPTION; DECLARE category VARCHAR2(10); BEGIN category := '&Category'; IF category NOT IN ('附件','顶盖','备件') THEN RAISE invalidCATEGORY; ELSE DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category); END IF; EXCEPTION WHEN invalidCATEGORY THEN DBMS_OUTPUT.PUT_LINE('无法识别该类别'); END; DECLARE ordernum VARCHAR2(5); BEGIN SELECT orderno INTO ordernum FROM order_master; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE ('返回多行'); END;

引发应用程序错误 RAISE_APPLICATION_ERROR 过程 引发应用程序错误的语法： 用于创建用户定义的错误信息 可以在可执行部分和异常处理部分使用 错误编号必须介于 –20000 和 –20999 之间 错误消息的长度可长达 2048 个字节 引发应用程序错误的语法： RAISE_APPLICATION_ERROR(error_number, error_message); DECLARE rate itemfile.itemrate%TYPE; rate_exception EXCEPTION; BEGIN SELECT NVL(itemrate,0) INTO rate FROM itemfile WHERE itemcode = 'i207'; IF rate = 0 THEN RAISE rate_exception; ELSE DBMS_OUTPUT.PUT_LINE('项费率为：' || rate); END IF; EXCEPTION WHEN rate_exception THEN RAISE_APPLICATION_ERROR(-20001, '未指定项费率'); END;

总结 PL/SQL 是一种可移植的高性能事务处理语言 PL/SQL 引擎驻留在 Oracle 服务器中 PL/SQL 数据类型包括标量数据类型、LOB 数据类型和属性类型 控制结构包括条件控制、循环控制和顺序控制 PL/SQL 支持动态 SQL 运行时出现的错误叫做异常 异常可以分为预定义异常和用户定义的异常