Oracle数据库使用-PLSQL

异常处理部分 WHERE product = '芭比娃娃'
FOR UPDATE OF quantity;
IF qty_on_hand > 0 THEN
[DUEPCDLAATERPEroducts SET quantity = quantity + 1
deWclHaEraRtEiopnrosd]uct = '芭比娃娃';
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 WHERE itemcode = icode; END IF; DBMS_OUTPUT.PUT_LINE('itemrate='|| irate); 20 END;
使用 PL/SQL
回顾
同义词是现有数据库对象的别名 序列用于生成唯一、连续的序号 视图是基于一个或多个表的虚拟表 索引是与表相关的一个可选结构，用于提高 SQL
语句执行的性能 索引类型有标准索引、唯一索引、反向键索引、
位图索引和基于函数的索引 索引组织表基于主键访问数据
2
理解 PL/SQL 功能和特点 了解数据类型及其用法 理解逻辑比较 理解控制结构 掌握错误处理
END;
21
循环控制
循环控制用于重复执行一系列语句 循环控制语句包括：
LOOP、EXIT 和 EXIT WHEN
循环控制的三种类型：
LOOP - 无条件循环 WLsOHeOqIPuLeEnce-_o根f_s据tate条me件nts循环 FEWsONHeDqRILuLEeOn-cOcoePn循_d;oit环fi_osn固taLtOe定mOeP的nt次s 数
指定出现错误时需要执行 的操作
hWaDnHBdEMlNeSrO_sOT] HUETRPUSTT.PHUETN_LINE('出错:'|| SQLERRM);
ENNDD; ;
8
变量和常量 1-2
PL/SQL 块中可以使用变量和常量
在声明部分声明，使用前必须先声明 声明时必须指定数据类型，每行声明一个标识符 在可执行部分的 SQL 语句和过程语句中使用
数据类型
标量类型
LOB类型
属性类型
数字
存储非结构化数BF据IL块E
字符
BLOB %TYPE
%ROWTYPE
布尔型 日期时间
提C供L某OB个变量或数提据供库表表示列表中一行的记录类 的数据类型 型 NCLOB
11
数字数据类型
指定数值的存储格式
数字数据类型
BINARY_INTEGER
NUMBER
PLS_INTEGER
15
LOB 数据类型 2-2
LOB 类型的数据库列仅存储定位符，该定位符指 向大型对象的存储位置
SDETBSMERSVE_ROLUOTPBUT程ON序包用于操纵 LOB 数据 DECLARE
clob_var CLOB;
amount INTEGER;
offset INTEGER;
output_var VARCHAR2(100);
INSERT INTO purchase_record
BEVGALIUNES ('已购买芭比娃娃', SYSDATE);
包含 SQL 和 PL/SQL 语 句的可执行部分
eExNeDcuIFta; ble statements
COMMIT;
[EEXXCCEPETPIOTNIO/*N异常处理语句 */
布尔表达式由有三关种系类运算型符：与变量或常量组成
关数系字运布算符尔型
说明
字符= 布尔型比较两个变量是否相等，如果值相当，则返回 True
日<期>, !布= 尔型比较两个变量，如果不相等，则返回 True
<
比较两个变量，检查值 1 是否小于值 2
>
比较两个变量，检查值 1 是否大于 值 2
<=
比较两个变量，检查变量 1 是否小于等于变量 2
可移植性、灵活性和安全性
4
PL/SQL 的优点 2-1
支持 SQL，在 PL/SQL 中可以使用：
数据操纵命令
事务控制命令
游标控制
SQL 函数和 SQL 运算符
支持面向对象编程 (OOP) Procedure
可移植性，可B运Pergo行inced在ure任Call何操作系统和平台上的
Oralce 数据库SQL Command
BEGIN ... icode := 'i205'; SELECT p_category, itemrate * c_rate INTO p_catg, p_rate FROM itemfile WHERE itemcode = icode; ...
END;
10
PL/SQL 支持的内置数据类型
数据类型
Oracle
…
更佳的性能，EPnLd /SQL 经过编译执行
用户将整个语句块发送给 Oracle
5
PL/SQL 的优点 2-2
与 SQL 紧密集成，简化数据处理。
支持所有 SQL 数据类型 支持 NULL 值 支持 %TYPE 和 %ROWTYPE 属性类型
SQL PL/SQL
安全性，可以通过存储过程限制用户对数据的 访问
13
日期时间和布尔数据类型
日期时间类型
存储日期和时间数据 常用的两种日期时间类型
DATE TIMESTAMP
布尔数据类型
此类别只有一种类型，即BOOLEAN类型 用于存储逻辑值(TRUE、FALSE和NULL) 不能向数据库中插入BOOLEAN数据 不能将列值保存到BOOLEAN变量中 只能对BOOLEAN变量执行逻辑操作
6
PL/SQL 的体系结构
PL/SQL 引擎驻留在 Oracle 服务器中 该引擎接受 PL/SQL 块并对其进行编译执行
用户
将PL/SQL 块发送给 语句
引擎执将行SQSQL L语语句句发送给 SQL 语句执行器
Oracle 服务器 PL/SQL引擎
目标
3
PL/SQL 简介
PL/SQL 是过程语言(Procedural Language)与结 构化查询语言(SQL)结合而成的编程语言
PL/SQL 是对 SQL 的扩展 支持多种数据类型，如大对象和集合类型，可使
用条件和循环等控制结构 可用于创建存储过程、触发器和程序包，给SQL
语句的执行添加程序逻辑 与 Oracle 服务器和 Oracle 工具紧密集成，具备
条件控制 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;
过程语句 执行器
SQL 语句 执行器
7
PL/SQL 块简介
DPECLL/ASREQL 块是构成 PL/声S明Q部L分程定义序变的量、基游本标 单元
B将qEtGy_逻INon_辑han上d N相UMB关ER的(5);声明和语句和自组定合义异在常一起
PSFRELOL/EMSCPTQrqoudLaunc分ttisty I为NTO三qty个_on部_ha分nd ，声明部分、可执行部分和
DBMS_OUTPUT.PUT_LINE(output_var);
END; /
显示读到的信息
从CLOB数据中读取24个字 符存储到 output_var 变量中
16
属性类型
用于引用数据库列的数据类型，以及表示表中一 行的记录类型
属性类型有两种：
%TYPE - 引用变量和数据库列的数据类型 %ROWTYPE - 提供表示表中一行的记录类型
14
LOB 数据类型 2-1
用于存储大文本、图像、视频剪辑和声音剪辑等非 结构化数据。
LOB 数据类型可存储最大 4GB的数据。 LOB 类型包括：
BLOB 将大型二进制对象存储在数据库中 CLOB 将大型字符数据存储在数据库中 NCLOB 存储大型UNICODE字符数据 BFILE 将大型二进制对象存储在操作系统文件中
>=
比较两个变量，检查变量 1 是否大于等于变量 2
18
PL/SQL 支持的流程控制结构： 条件控制
IF 语句 CASE 语句
循环控制
LOOP 循环 WHILE 循环 FOR 循环
顺序控制
GOTO 语句 NULL 语句
19
控制结构
条件控制 2-1
IF 语句根据条件执行一系列语句，有三种形式： IFDE-TCLHAREEN、IF-THEN-ELSE 和 IF-THEN-ELSIF
EFONDR cLoOuOnPte;r IN [REVERSE] value1..value2 LOOP
sequence_of_statements END LOOP;
22
顺序控制
顺序控制用于按顺序执行语句 顺序控制语句包括： DECLARE
qtyhand itemfile.qty_hand%type;
声明变量和常量的语法：
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
给变量赋值有两种方法：
使用赋值语句 := 使用 SELECT INTO 语句
9
变量和常量 2-2
DECLARE icode VARCHAR2(6); p_catg VARCHAR2(20); p_rate NUMBER; c_rate CONSTANT NUMBER := 0.10;
使用属性类型的优点：
不需要知道被引用的表列的具体类型 如果被引用对象的数据类型发生改变，PL/SQL 变量
的数据类型也随之改变
icode itemfile.itemcode%TYPE; emp_rec scott.emp%ROWTYPE;
17
逻辑比较
布逻尔辑比表较达用式于的比结较果变为量TR和U常E、量F的AL值S，E或这N些U表LL达，式通 常称由 为布逻尔辑表运达算式符AND、OR和NOT连接
存储有符号整数，所需存储 空间少于NUMBER类型值
NA存T储UR整A数L 、实数和浮点数DECI存算MA储术L有计符算号快整速数而，有可效使
NATURALLN
FLOAT
POSITIVE
INTEGER
POSITIVEN
REAL
SIGNTYPE
12
字符数据类型
字符数据类型包括：
CHAR VARCHAR2 LONG RAW LONG RAW
PL/SQL 的数据类型 与 SQL数据类型的 比较
数据类型 CHAR LONG LONG RAW RAW VARCHAR2
SQL类型 1..2000 1..2GB 1..2GB 1..2000 1..4000
PL/SQL类型 1..32767 1..32760 1..32760 1..32767 1..32767
relevGel OiteTmOfile.语re_句leve-l%无typ条e; 件地转到标签指定的语句
BEGIN
SELENCTUqLtyL_h语an句d,re_-le什vel么INT也O q不tyh做an的d,re空lev语el 句
FROM itemfile WHERE itemcode = 'i201'; IF qtyhand < relevel THEN
BEGIN SELECT chapter_text INTO clob_var FROM my_book_text
从表中选择 CLOB 定位符 到 clob_var变量中
WHERE chapter_id=5;
amount := 24; -- 要读取的字符数
offset := 1; -- 起始位置
DBMS_LOB.READ(clob_var,amount,offset,output_var);