五章使用PLSQLppt课件

1、第五章第五章运用 PL/SQL回想 同义词是现有数据库对象的别名 序列用于生成独一、延续的序号 视图是基于一个或多个表的虚拟表 索引是与表相关的一个可选构造，用于提高 SQL 语句执行的性能 索引类型有规范索引、独一索引、反向键索引、位图索引和基于函数的索引 索引组织表基于主键访问数据目的 了解 PL/SQL 功能和特点 了解数据类型及其用法(重点) 了解逻辑比较 - 了解控制构造(重点) 掌握错误处置(重点)专业英语 LOB大对象(Large OBject )PL/SQL 简介 PL/SQL 是过程言语(Procedural Language)与构造化查询言语(SQL)结合而成的编程言语 P

2、L/SQL 是对 SQL 的扩展 支持多种数据类型，如大对象和集合类型，可运用条件和循环等控制构造 可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑 与 Oracle 效力器和 Oracle 工具严密集成，具备可移植性、灵敏性和平安性PL/SQL 的优点 2-1 支持 SQL，在 PL/SQL 中可以运用： 数据支配命令 事务控制命令 游标控制 SQL 函数和 SQL 运算符 支持面向对象编程 (OOP) 可移植性，可运转在任何操作系统和平台上的Oralce 数据库 更佳的性能，PL/SQL 经过编译执行用户将整个语句块发送给 OracleOracleProcedureBeg

3、in ProcedureCall SQL Command EndPL/SQL 的优点 2-2 PL/SQLSQL 与 SQL 严密集成，简化数据处置。 支持一切 SQL 数据类型 支持 NULL 值 支持 %TYPE 和 %ROWTYPE 属性类型 平安性，可以经过存储过程限制用户对数据的访问PL/SQL 的体系构造 PL/SQL 引擎驻留在 Oracle 效力器中 该引擎接受 PL/SQL 块并对其进展编译执行将PL/SQL 块发送给 Oracle 效力器用户用户执行过程语句引擎将 SQL 语句发送给SQL 语句执行器Oracle 效力器效力器PL/SQL引擎引擎SQL 语句语句执行器执行器

4、过程语句过程语句执行器执行器执行 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 U

5、PDATE 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 语句的可执行部分指定

6、出现错误时需求执行的操作变量和常量 1-2 PL/SQL 块中可以运用变量和常量 在声明部分声明，运用前必需先声明 声明时必需指定数据类型，每行声明一个标识符 在可执行部分的 SQL 语句和过程语句中运用 声明变量和常量的语法： 给变量赋值有两种方法： 运用赋值语句 := 运用 SELECT INTO 语句变量和常量 2-2DECLARE icode VARCHAR2(6); p_catg VARCHAR2(20); p_rate NUMBER; c_rate CONSTANT NUMBER := 0.10;BEGIN . icode := i205; SELECT p_category, i

7、temrate * c_rate INTO p_catg, p_rate FROM itemfile WHERE itemcode = icode; .END;数据类型 PL/SQL 支持的内置数据类型数据类型数据类型LOB类型标量类型属性类型%ROWTYPE数字字符布尔型日期时间BFILEBLOBCLOBNCLOB%TYPE提供某个变量或数据库表列的数据类型提供表示表中一行的记录类型 存储非构造化数据块数字数据类型 指定数值的存储格式数字数据类型数字数据类型NUMBERBINARY_INTEGERPLS_INTEGERNATURALNATURALLNPOSITIVEPOSITIVENDECI

8、MALFLOATINTEGERREALSIGNTYPE存储有符号整数，所需存储空间少于NUMBER类型值存储整数、实数和浮点数存储有符号整数，可使算术计算快速而有效字符数据类型 字符数据类型包括： CHAR VARCHAR2 LONG RAW LONG RAW PL/SQL 的数据类型与 SQL数据类型的比较数据类型SQL类型PL/SQL类型CHAR1.20001.32767LONG1.2GB1.32760LONG RAW1.2GB1.32760RAW1.20001.32767VARCHAR21.40001.32767日期时间和布尔数据类型 日期时间类型 存储日期和时间数据 常用的两种日期时间

9、类型 DATE TIMESTAMP 布尔数据类型 此类别只需一种类型，即BOOLEAN类型 用于存储逻辑值(TRUE、FALSE和NULL) 不能向数据库中插入BOOLEAN数据 不能将列值保管到BOOLEAN变量中 只能对BOOLEAN变量执行逻辑操作LOB 数据类型 2-1 用于存储大文本、图像、视频剪辑和声音剪辑等非构造化数据。 LOB 数据类型可存储最大 4GB的数据。 LOB 类型包括： BLOB 将大型二进制对象存储在数据库中 CLOB 将大型字符数据存储在数据库中 NCLOB 存储大型UNICODE字符数据 BFILE 将大型二进制对象存储在操作系统文件中LOB 数据类型 2-2

10、 LOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置 DBMS_LOB程序包用于支配 LOB 数据SET SERVEROUTPUT ONDECLARE 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

11、,amount,offset,output_var); DBMS_OUTPUT.PUT_LINE(output_var);END;/从表中选择 CLOB 定位符到 clob_var变量中从CLOB数据中读取24个字符存储到 output_var 变量中显示读到的信息属性类型 用于援用数据库列的数据类型，以及表示表中一行的记录类型 属性类型有两种： %TYPE - 援用变量和数据库列的数据类型 %ROWTYPE - 提供表示表中一行的记录类型 运用属性类型的优点： 不需求知道被援用的表列的详细类型 假设被援用对象的数据类型发生改动，PL/SQL 变量的数据类型也随之改动icode itemfil

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

13、否大于 值值 2=比较两个变量，检查变量比较两个变量，检查变量 1 是否大于等于变量是否大于等于变量 2控制构造PL/SQL 支持的流程控制构造：条件控制IF 语句CASE 语句循环控制LOOP 循环WHILE 循环FOR 循环顺序控制GOTO 语句NULL 语句条件控制 2-1 IF 语句根据条件执行一系列语句，有三种方式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIFDECLARE icode VARCHAR2(4); irate NUMBER;BEGIN icode := i203; SELECT itemrate INTO irate FROM itemfil

14、e 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;条件控制 2-2BEGIN CASE &grade WHEN A THEN DBMS_OUTPUT.PUT_LIN

15、E(优良优良); 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; CASE 语句用于根据单个变量或表达式与多个值进展比较 执行 CASE 语句前，先计算选择器的值循环控制 循环控制用于反复执行一系列语句 循环控制

16、语句包括： LOOP、EXIT 和 EXIT WHEN 循环控制的三种类型： LOOP - 无条件循环 WHILE - 根据条件循环 FOR - 循环固定的次数LOOP sequence_of_statementsEND LOOP;WHILE condition LOOP sequence_of_statementsEND LOOP;FOR counter IN REVERSE value1.value2LOOP sequence_of_statementsEND LOOP;顺序控制 顺序控制用于按顺序执行语句 顺序控制语句包括： GOTO 语句 - 无条件地转到标签指定的语句 NULL 语句

17、 - 什么也不做的空语句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; UPDATE itemfile SET qty_hand = qty_hand + re_level WHERE itemcode

18、 = i201; NULL;END;动态 SQL 动态 SQL 是指在PL/SQL程序执行时生成的 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

19、) := 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 程序违反 O

20、racle 规那么或超越系统限制时隐式引发 用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常经过 RAISE 语句显式引发错误处置 2-2 处置预定义异常 处置用户定义异常DECLARE ordernum VARCHAR2(5);BEGIN SELECT orderno INTO ordernum FROM order_master;EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (前往多行);END;DECLARE invalidCATEGORY EXCEPTION; 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(无法识