1、第五章,使用 PL/SQL,2,回顾,同义词是现有数据库对象的别名 序列用于生成唯一、连续的序号 视图是基于一个或多个表的虚拟表 索引是与表相关的一个可选结构，用于提高 SQL 语句执行的性能 索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引 索引组织表基于主键访问数据,3,目标,理解 PL/SQL 功能和特点 了解数据类型及其用法 理解逻辑比较 理解控制结构,4,PL/SQL 简介,PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言 PL/SQL 是对 SQL 的扩展 支持多种数据类型，如大对象和集合类型，可使用条
2、件和循环等控制结构 可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑 与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性,5,PL/SQL 的优点 2-1,支持 SQL，在 PL/SQL 中可以使用： 数据操纵命令 事务控制命令 游标控制 SQL 函数和 SQL 运算符 支持面向对象编程 (OOP) 可移植性，可运行在任何操作系统和平台上的Oralce 数据库 更佳的性能，PL/SQL 经过编译执行,用户将整个语句块发送给 Oracle,6,PL/SQL 的优点 2-2,与 SQL 紧密集成，简化数据处理。 支持所有 SQL 数据类型 支持
3、NULL 值 支持 %TYPE 和 %ROWTYPE 属性类型,安全性，可以通过存储过程限制用户对数据的访问,7,PL/SQL 的体系结构,PL/SQL 引擎驻留在 Oracle 服务器中 该引擎接受 PL/SQL 块并对其进行编译执行,将PL/SQL 块发送给 Oracle 服务器,用户,执行过程语句,引擎将 SQL 语句发送给SQL 语句执行器,执行 SQL 语句,将结果发送给用户,8,PL/SQL 块简介,PL/SQL 块是构成 PL/SQL 程序的基本单元 将逻辑上相关的声明和语句组合在一起 PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分DECLARE declarat
4、ionsBEGINexecutable statementsEXCEPTION handlersEND;,DECLAREqty_on_hand NUMBER(5); BEGINSELECT quantity INTO qty_on_handFROM ProductsWHERE product = 芭比娃娃FOR UPDATE OF quantity;IF qty_on_hand 0 THENUPDATE Products SET quantity = quantity + 1WHERE product = 芭比娃娃;INSERT INTO purchase_recordVALUES (已购买芭
5、比娃娃, SYSDATE);END IF;COMMIT; EXCEPTION /* 异常处理语句 */WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(出错:| SQLERRM); END;,声明部分定义变量、游标和自定义异常,包含 SQL 和 PL/SQL 语句的可执行部分,指定出现错误时需要执行的操作,9,PL/SQL 块,它包括一组语句 PL/SQL 程序中的基本单元 将相关的声明和语句进行逻辑分组 声明对于块是局部的 在块完成之后，将超出范围,10,PL/SQL块的结构,一个程序块是由三个部分或节构成的 声明部分 可执行部分 异常处理部分,11,PL/SQL 结
6、构（续）,声明部分 定义变量的可选部分 可执行部分 构成可执行语句的必要部分 异常处理部分 构成错误处理代码的可选部分,12,PL/SQL 结构（续）,可以将语句在 PL/SQL 中分组 命名组称为子程序 未命名组是匿名程序块 程序块可以包括称为嵌套程序块的多个块 只允许在可执行程序和异常处理部分进行嵌套 最多允许 200 级嵌套,13,PL/SQL 结构（续）,可以用任何编辑器编写 PL/SQL程序，并将其保存为具有 .sql 扩展名的文件 也可以使用 SQL*Plus中的“ED”命令创建 PL/SQL 程序文件 使用 “ ”命令执行 PL/SQL 程序文件,14,变量和常量 1-2,PL/
7、SQL 块中可以使用变量和常量 在声明部分声明，使用前必须先声明 变量名不区分大小写 多个变量不能在一个语句中声明 声明时必须指定数据类型，每行声明一个标识符 在可执行部分的 SQL 语句和过程语句中使用 声明变量和常量的语法：,identifier CONSTANT datatype NOT NULL := | DEFAULT expr;,给变量赋值有两种方法： 使用赋值语句 := 使用 SELECT INTO 语句,15,变量和常量 2-2,DECLAREicode VARCHAR2(6);p_catg VARCHAR2(20);p_rate NUMBER;c_rate CONSTANT
8、NUMBER := 0.10; BEGIN.icode := i205;SELECT p_category, itemrate * c_rateINTO p_catg, p_rateFROM itemfile WHERE itemcode = icode;. END;,16,使用 SELECT INTO 进行赋值,还可以按如下方法使用 SELECT INTO 对变量赋值 SELECT INTO FROM WHERE ;示例 SELECT first_fare INTO oldfare FROM fare WHERE route_code = SAN-LOU;,17,使用 SELECT INTO
9、 进行赋值（续）,SELECT 语句执行后，将出现下列情况之一 只检索了一行 检索了多行 不检索任何行 仅当它检索一行时，SELECT 才成功操作 其他两种情况将导致错误并产生异常处理程序,18,数据类型,PL/SQL 支持的内置数据类型,数据类型,LOB类型,标量类型,属性类型,%ROWTYPE,数字,字符,布尔型,日期时间,BFILE,BLOB,CLOB,NCLOB,%TYPE,提供某个变量或数据库表列的数据类型,提供表示表中一行的记录类型,存储非结构化数据块,19,数字数据类型,指定数值的存储格式,数字数据类型,NUMBER,BINARY_INTEGER,PLS_INTEGER,NATU
10、RAL,NATURALLN,POSITIVE,POSITIVEN,DECIMAL,FLOAT,INTEGER,REAL,SIGNTYPE,存储有符号整数，所需存储空间少于NUMBER类型值,存储整数、实数和浮点数,存储有符号整数，可使算术计算快速而有效,20,字符数据类型,字符数据类型包括： CHAR VARCHAR2 LONG RAW LONG RAW PL/SQL 的数据类型与 SQL数据类型的比较,21,数据类型：字符型（赋值）,Char数据： 当赋值字符串长度小于声明长度时，将在字符串的尾部填充空格示例：Last_name CHAR(10):=CHEN;Last_name 的值为CHE
11、N （尾部有6个空格） 当赋值字符串长度大于声明长度时，将出错示例：acronym CHAR(4); acronym := SPCA 将出错 Varchar2数据： 当赋值字符串长度小于定义长度时，将保持字符串原值，不进行任何处理示例：Last_name VARCHAR2(10):=CHEN ;Last_name 的值为CHEN 当赋值字符串长度大于定义长度时，将出错示例：acronym VARCHAR2 (4); acronym := SPCA 将出错,22,日期时间和布尔数据类型,日期时间类型 存储日期和时间数据 常用的两种日期时间类型 DATE TIMESTAMP 布尔数据类型 此类别只
12、有一种类型，即BOOLEAN类型 用于存储逻辑值(TRUE、FALSE和NULL) 不能向数据库中插入BOOLEAN数据 不能将列值保存到BOOLEAN变量中 只能对BOOLEAN变量执行逻辑操作,23,LOB 数据类型 2-1,用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。 LOB 数据类型可存储最大 4GB的数据。 LOB 类型包括： BLOB 将大型二进制对象存储在数据库中 CLOB 将大型字符数据存储在数据库中 NCLOB 存储大型UNICODE字符数据 BFILE 将大型二进制对象存储在操作系统文件中,24,LOB 数据类型 2-2,LOB 类型的数据库列仅存储定位符，该定
13、位符指向大型对象的存储位置 DBMS_LOB程序包用于操纵 LOB 数据,SET SERVEROUTPUT ON DECLAREclob_var CLOB;amount INTEGER;offset INTEGER;output_var VARCHAR2(100); BEGINSELECT chapter_text INTO clob_var FROM my_book_textWHERE chapter_id=5;amount := 24; - 要读取的字符数offset := 1; - 起始位置DBMS_LOB.READ(clob_var,amount,offset,output_var);
14、DBMS_OUTPUT.PUT_LINE(output_var); END; /,从表中选择 CLOB 定位符 到 clob_var变量中,从CLOB数据中读取24个字符存储到 output_var 变量中,显示读到的信息,25,%TYPE 属性,可以提供变量、常量或列的数据类型 示例 Oldfare fare.first_fare%TYPE; newfare oldfare%TYPE; 当声明一个引用数据库中的列的变量时非常有用 不必明确知道列的实际数据类型 如果列定义发生变化，则变量的数据类型在运行时也将随之更改,26,%ROWTYPE属性,当记录变量具有与表或视图中的行或从游标获取的行相
15、同的结构时有用 记录中的字段具有与表/视图中的列相同的名称和数据类型 示例 emp_rec emp%ROWTYPE; 可以使用下列属性引用特定字段 emp_rec.emp_num;,27,布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接 布尔表达式有三种类型： 数字布尔型 字符布尔型 日期布尔型,逻辑比较,逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式 布尔表达式由关系运算符与变量或常量组成,28,字符型（比较规则）,如果运算符两边都是CHAR类型，使用填充空格规则，使两边长度都相等再比较 last_name1 CHAR(5) := BELL
16、O; last_name2 CHAR(10) := BELLO ; 则Last_name1 = last_name2 如果运算符两边都是VARCHAR2类型，使用不填充空格规则 last_name1 VARCHAR2(5) := BELLO; last_name2 VARCHAR2(10) := BELLO ; 则Last_name1 last_name2,29,字符型（比较规则）,如果一个是CHAR类型，另一个是VARCHAR2类型，使用不填充空格规则 last_name1 VARCHAR2(5) := BELLO; last_name2 CHAR(10) := BELLO; 则Last_n
17、ame1 last_name2 所有的字符串常量的类型都是CHAR类型,30,控制结构,PL/SQL 支持的流程控制结构： 条件控制 IF 语句 CASE 语句 循环控制 LOOP 循环 WHILE 循环 FOR 循环 顺序控制 GOTO 语句 NULL 语句,31,条件控制 2-1,IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF,DECLAREicode VARCHAR2(4);irate NUMBER; BEGINicode := i203;SELECT itemrate INTO irate FROM itemfil
18、e WHERE itemcode = icode;IF irate 200 THENUPDATE itemfile SET itemrate = itemrate - 200WHERE itemcode = icode;ELSEUPDATE itemfile SET itemrate = itemrate - 50WHERE itemcode = icode;END IF;DBMS_OUTPUT.PUT_LINE(itemrate=| irate); END;,32,条件控制 2-2,BEGINCASE ,CASE 语句用于根据单个变量或表达式与多个值进行比较 执行 CASE 语句前，先计算选
19、择器的值,33,循环控制,循环控制用于重复执行一系列语句 循环控制语句包括： LOOP、EXIT 和 EXIT WHEN 循环控制的三种类型： LOOP - 无条件循环 WHILE - 根据条件循环 FOR - 循环固定的次数,LOOP sequence_of_statements END LOOP;,WHILE condition LOOP sequence_of_statements END LOOP;,FOR counter IN REVERSE value1value2 LOOP sequence_of_statements END LOOP;,34,顺序控制,顺序控制用于按顺序执行语
20、句 顺序控制语句包括： GOTO 语句 - 无条件地转到标签指定的语句 NULL 语句 - 什么也不做的空语句,DECLAREqtyhand itemfile.qty_hand%type;relevel itemfile.re_level%type; BEGINSELECT qty_hand,re_level INTO qtyhand,relevelFROM itemfile WHERE itemcode = i201;IF qtyhand UPDATE itemfile SET qty_hand = qty_hand + re_levelWHERE itemcode = i201;NULL;
21、 END;,35,动态 SQL,动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句 编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行 DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行 执行动态 SQL 的语法：EXECUTE IMMEDIATE dynamic_sql_stringINTO define_variable_listUSING bind_argument_list;,DECLAREsql_stmt VARCHAR2(200);emp_id NUMBER(4) := 7566;emp
22、_rec emp%ROWTYPE; BEGINEXECUTE 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;,36,总结,PL/SQL 是一种可移植的高性能事务处理语言 PL/SQL 引擎驻留在 Oracle 服务器中 PL/SQL 块由声明部分、可执行部分和异常处理部分组成 PL/SQL 数据类型包括标量数据类型、LOB 数据类型和属性类型 控制结构包括条件控制、循环控制和顺序控制 PL/SQL 支持动态 SQL,
