《Oracle数据库应用与开发》第05章[PLSQL]理论课

1..32760 1..32767
1..32767
• PL/SQL 的数据类 型与 SQL数据类 型的比较
VARCHAR 1..4000 2
日期时间和布尔数据类型
• 日期时间类型
– 存储日期和时间数据 – 常用的两种日期时间类型
• DATE • TIMESTAMP
• 布尔数据类型
– 此类别只有一种类型，即BOOLEAN类型 – 用于存储逻辑值(TRUE、FALSE和NULL) – 不能向数据库中插入BOOLEAN数据 – 不能将列值保存到BOOLEAN变量中 – 只能对BOOLEAN变量执行逻辑操作
BINARY_INTEGER
NUMBER
PLS_INTEGER
存储整数、实数和浮点数 存储有符号整数，可使 存储有符号整数，所需存储 NATURAL DECIMAL 空间少于NUMBER类型值 算术计算快速而有效 NATURALN FLOAT POSITIVE POSITIVEN SIGNTYPE INTEGER
目标
• • • • • 理解 PL/SQL 功能和特点 了解数据类型及其用法 理解逻辑比较 理解控制结构 掌握错误处理
PL/SQL 简介
• PL/SQL 是过程语言(Procedural Language) 与结构化查询语言(SQL)结合而成的编程语 言 • PL/SQL 是对 SQL 的扩展 • 支持多种数据类型，如大对象和集合类型， 可使用条件和循环等控制结构 • 可用于创建存储过程、触发器和程序包， 给SQL语句的执行添加程序逻辑 • 与 Oracle 服务器和 Oracle 工具紧密集成， 具备可移植性、灵活性和安全性
PL/SQL 的优点 2-1
• 支持 SQL，在 PL/SQL 中可以使用：
– 数据操纵命令 – 事务控制命令 – 游标控制 – SQL 函数和 SQL 运算符
• 支持面向对象编程 (OOP) Oracle • 可移植性，可运行在任何操作系统和平台 上的Oralce 数据库 • 更佳的性能，PL/SQL 经过编译执行
使用PL/SQL
《Oracle数据库应用与开发》第05章
回顾
• • • • 同义词是现有数据库对象的别名 序列用于生成唯一、连续的序号 视图是基于一个或多个表的虚拟表 索引是与表相关的一个可选结构，用于提 高 SQL 语句执行的性能 • 索引类型有标准索引、唯一索引、反向键 索引、位图索引和基于函数的索引 • 索引组织表基于主键访问数据
将结果发送给用户
用户
执行过程语句 引擎将 SQL 语句发送给 执行 SQL 语句 SQL 语句执行器 SQL 语句 执行器
PL/SQL 块简介
• DECLARE PL/SQL 块是构成 声明部分定义变量、游标 PL/SQL 程序的基本单 qty_on_hand NUMBER(5); 和自定义异常 BEGIN 元 SELECT quantity INTO qty_on_hand • FROM Products = '芭比娃娃' 将逻辑上相关的声明和语句组合在一起 WHERE product FOR UPDATE OF quantity; • IFPL/SQL 0 THEN qty_on_hand > 分为三个部分，声明部分、可执 UPDATE Products SET quantity = quantity + 1 行部分和异常处理部分 包含 SQL 和 PL/SQL 语 WHERE product = '芭比娃娃';
LOB 数据类型 2-1
• 用于存储大文本、图像、视频剪辑和声音剪 辑等非结构化数据。 • LOB 数据类型可存储最大 4GB的数据。 • LOB 类型包括：
– BLOB 将大型二进制对象存储在数据库中 – CLOB 将大型字符数据存储在数据库中 – NCLOB 存储大型UNICODE字符数据 – BFILE 将大型二进制对象存储在操作系统文 件中
循环控制
• 循环控制用于重复执行一系列语句 • 循环控制语句包括：
– LOOP、EXIT 和 EXIT WHEN
• 循环控制的三种类型：
LOOP – LOOP - 无条件循环 sequence_of_statements WHILE condition LOOP END LOOP; – WHILE - 根据条件循环 sequence_of_statements END counter IN [REVERSE] value1..value2 FOR LOOP; – FOR - 循环固定的次数 LOOP sequence_of_statements END LOOP;
LOB 数据类型 2-2
• LOB 类型的数据库列仅存储定位符，该定 位符指向大型对象的存储位置 SET SERVEROUTPUT ON • DECLARE DBMS_LOB程序包用于操纵 LOB 数据
clob_var CLOB; amount INTEGER; offset INTEGER; output_var VARCHAR2(100); BEGIN 从表中选择 CLOB 定位符 SELECT chapter_text INTO clob_var 到 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); 从CLOB数据中读取24个字 END; 符存储到 output_var 变量中 显示读到的信息 /
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 WHERE itemcode = icode; END IF; DBMS_OUTPUT.PUT_LINE('itemrate='|| irate); END;
数据类型
• PL/SQL 支持的内置数据类型
数据类型
Biblioteka Baidu标量类型
LOB类型
属性类型
数字 字符
存储非结构化数据块 BFILE BLOB %TYPE %ROWTYPE
布尔型
日期时间
CLOB 提供某个变量或数据库表列 提供表示表中一行的记录类 的数据类型 型 NCLOB
数字数据类型
• 指定数值的存储格式
数字数据类型
• 声明变量和常量的语法：
– 使用赋值语句 := – 使用 SELECT INTO 语句
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
• 给变量赋值有两种方法：
变量和常量 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;
[EXCEPTION handlers]
PL/SQL中的复合符号
• • • • • • • := || -/**/ <<>> .. ** 赋值操作符 连接操作符 单行注释 多行注释 标签分隔符 范围操作符 求幂操作符
变量和常量 1-2
• PL/SQL 块中可以使用变量和常量
– 在声明部分声明，使用前必须先声明 – 声明时必须指定数据类型，每行声明一个标识 符 – 在可执行部分的 SQL 语句和过程语句中使用
逻辑比较
• 逻辑比较用于比较变量和常量的值，这些 布尔表达式的结果为TRUE、FALSE或 表达式称为布尔表达式 NULL，通常由逻辑运算符AND、OR和 NOT连接 • 布尔表达式由关系运算符与变量或常量组 • 成 布尔表达式有三种类型： 关系运算符 说明
= 比较两个变量是否相等，如果值相当，则返回 True – 数字布尔型 比较两个变量，如果不相等，则返回 True – <>, != 字符布尔型 < 比较两个变量，检查值 1 是否小于值 2 – 日期布尔型 > 比较两个变量，检查值 1 是否大于 值 2 <= 比较两个变量，检查变量 1 是否小于等于变量 2
INSERT INTO purchase_record 句的可执行部分 VALUES ('已购买芭比娃娃', SYSDATE); [DECLARE END IF; COMMIT; declarations] 指定出现错误时需要执行 EXCEPTION /* 异常处理语句 */ 的操作 BEGIN WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM); executable statements 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;
用户将整个语句块发送给 Oracle
Procedure Begin ProcedureCall SQL Command … End
PL/SQL 的优点 2-2
• 与 SQL 紧密集成，简化数据处理。
– 支持所有 SQL 数据类型 – 支持 NULL 值 – 支持 %TYPE 和 %ROWTYPE 属性类型
>=
比较两个变量，检查变量 1 是否大于等于变量 2
控制结构
PL/SQL 支持的流程控制结构： • 条件控制
– IF 语句 – CASE 语句
• 循环控制
– LOOP 循环 – WHILE 循环 – FOR 循环
• 顺序控制
– GOTO 语句
条件控制 2-1
• DECLARE IF 语句根据条件执行一系列语句，有三种 icode VARCHAR2(4); irate NUMBER; 形式：IF-THEN、IF-THEN-ELSE 和 IFBEGIN THEN-ELSIF icode := 'i203';
SQL PL/SQL
• 安全性，可以通过存储过程限制用户对数 据的访问
PL/SQL 的体系结构
• PL/SQL 引擎驻留在 Oracle 服务器中 • 该引擎接受 PL/SQL 块并对其进行编译执 行 Oracle 服务器
将PL/SQL 块发送给 Oracle 服务器 PL/SQL引擎 过程语句 执行器
属性类型
• 用于引用数据库列的数据类型，以及表示 表中一行的记录类型 • 属性类型有两种：
– %TYPE - 引用变量和数据库列的数据类型 – %ROWTYPE - 提供表示表中一行的记录类 型
• 使用属性类型的优点：
– 不需要知道被引用的表列的具体类型 icode itemfile.itemcode%TYPE; – 如果被引用对象的数据类型发生改变， emp_rec scott.emp%ROWTYPE; PL/SQL 变量的数据类型也随之改变
REAL
字符数据类型
• 字符数据类型包括： 数据类型
– CHAR – VARCHAR2 – LONG – RAW – LONG RAW
CHAR LONG SQL类型 1..2000 1..2GB PL/SQL类型 1..32767 1..32760
LONG RAW RAW
1..2GB 1..2000