PL、SQL 功能和特点

ACCP V4.0,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,*,第五章,使用 PL/SQL,回顾,同义词是现有数据库对象的别名,序列用于生成唯一、连续的序号,视图是基于一个或多个表的虚拟表,索引是与表相关的一个可选结构，用于提高 SQL 语句执行的性能,索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引,索引组织表基于主键访问数据,2,目标,理解 PL/SQL 功能和特点,了解数据类型及其用法,理解逻辑比较,理解控制结构,掌握错误处理,3,PL/SQL 简介,PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言,PL/SQL 是对 SQL 的扩展,支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构,可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑,与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性,4,PL/SQL 的优点 2-1,支持 SQL，在 PL/SQL 中可以使用：,数据操纵命令,事务控制命令,游标控制,SQL 函数和 SQL 运算符,支持面向对象编程 (OOP),可移植性，可运行在任何操作系统和平台上的Oralce 数据库,更佳的性能，PL/SQL 经过编译执行,用户将整个语句块发送给 Oracle,Oracle,Procedure,Begin,ProcedureCall,SQL Command,End,5,PL/SQL 的优点 2-2,PL/SQL,SQL,与 SQL 紧密集成，简化数据处理。,支持所有 SQL 数据类型,支持 NULL 值,支持 %TYPE 和 %ROWTYPE 属性类型,安全性，可以通过存储过程限制用户对数据的访问,6,PL/SQL 的体系结构,PL/SQL 引擎驻留在 Oracle 服务器中,该引擎接受 PL/SQL 块并对其进行编译执行,将,PL/SQL 块发送给 Oracle 服务器,用户,执行过程语句,引擎将 SQL 语句发送给SQL 语句执行器,Oracle,服务器,PL/SQL引擎,SQL,语句执行器,过程语句执行器,执行 SQL 语句,将结果发送给用户,7,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 语句的可执行部分,指定出现错误时需要执行的操作,8,变量和常量 1-2,PL/SQL 块中可以使用变量和常量,在声明部分声明，使用前必须先声明,声明时必须指定数据类型，每行声明一个标识符,在可执行部分的 SQL 语句和过程语句中使用,声明变量和常量的语法：,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;,BEGIN,.,icode := i205;,SELECT p_category, itemrate * c_rate,INTO p_catg, p_rate,FROM itemfile WHERE itemcode = icode;,.,END;,10,数据类型,PL/SQL 支持的内置数据类型,数据类型,LOB类型,标量类型,属性类型,%ROWTYPE,数字,字符,布尔型,日期时间,BFILE,BLOB,CLOB,NCLOB,%TYPE,提供某个变量或数据库表列的数据类型,提供表示表中一行的记录类型,存储非结构化数据块,11,数字数据类型,指定数值的存储格式,数字数据类型,NUMBER,BINARY_INTEGER,PLS_INTEGER,NATURAL,NATURALLN,POSITIVE,POSITIVEN,DECIMAL,FLOAT,INTEGER,REAL,SIGNTYPE,存储有符号整数，所需存储空间少于NUMBER类型值,存储整数、实数和浮点数,存储有符号整数，可使算术计算快速而有效,12,字符数据类型,字符数据类型包括：,CHAR,VARCHAR2,LONG,RAW,LONG RAW,PL/SQL 的数据类型与 SQL数据类型的比较,数据类型,SQL类型,PL/SQL类型,CHAR,1.2000,1.32767,LONG,1.2GB,1.32760,LONG RAW,1.2GB,1.32760,RAW,1.2000,1.32767,VARCHAR2,1.4000,1.32767,13,日期时间和布尔数据类型,日期时间类型,存储日期和时间数据,常用的两种日期时间类型,DATE,TIMESTAMP,布尔数据类型,此类别只有一种类型，即BOOLEAN类型,用于存储逻辑值(TRUE、FALSE和NULL),不能向数据库中插入BOOLEAN数据,不能将列值保存到BOOLEAN变量中,只能对BOOLEAN变量执行逻辑操作,14,LOB 数据类型 2-1,用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。,LOB 数据类型可存储最大 4GB的数据。,LOB 类型包括：,BLOB 将大型二进制对象存储在数据库中,CLOB 将大型字符数据存储在数据库中,NCLOB 存储大型UNICODE字符数据,BFILE 将大型二进制对象存储在操作系统文件中,15,LOB 数据类型 2-2,LOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置,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 变量中,显示读到的信息,16,属性类型,用于引用数据库列的数据类型，以及表示表中一行的记录类型,属性类型有两种：,%TYPE - 引用变量和数据库列的数据类型,%ROWTYPE - 提供表示表中一行的记录类型,使用属性类型的优点：,不需要知道被引用的表列的具体类型,如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变,icode itemfile.itemcode%TYPE;,emp_rec scott.emp%ROWTYPE;,17,布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接,布尔表达式有三种类型：,数字布尔型,字符布尔型,日期布尔型,逻辑比较,逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式,布尔表达式由关系运算符与变量或常量组成,关系运算符,说明,=,比较两个变量是否相等，如果值相当，则返回 True, !=,比较两个变量，如果不相等，则返回 True,比较两个变量，检查值 1 是否大于 值 2,=,比较两个变量，检查变量 1 是否大于等于变量 2,18,控制结构,PL/SQL 支持的流程控制结构：,条件控制,IF 语句,CASE 语句,循环控制,LOOP 循环,WHILE 循环,FOR 循环,顺序控制,GOTO 语句,NULL 语句,19,条件控制 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,WHERE itemcode = icode;,END IF;,DBMS_OUTPUT.PUT_LINE(itemrate=| irate);,END;,20,条件控制 2-2,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;,CASE 语句用于根据单个变量或表达式与多个值进行比较,执行 CASE 语句前，先计算选择器的值,21,循环控制,循环控制用于重复执行一系列语句,循环控制语句包括：,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 value1.value2,LOOP,sequence_of_statements,END LOOP;,22,顺序控制,顺序控制用于按顺序执行语句,顺序控制语句包括：,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 ,UPDATE itemfile SET qty_hand = qty_hand + re_level,WHERE itemcode = i201;,NULL;,END;,23,动态 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) := 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;,24,错误处理 2-1,在运行程序时出现的错误叫做异常,发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分,异常有两种类型：,预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发,用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发,25,错误处理 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 := ,IF category NOT IN (附件,顶盖,备件) THEN,RAISE invalidCATEGORY;,ELSE,DBMS_OUTPUT.PUT_LINE(您输入的类别是| category);,END IF;,EXCEPTION,WHEN invalidCATEGORY THEN,DBMS_OUTPUT.PUT_LINE(无法识别该类别);,END;,26,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;,27,总结,PL/SQL 是一种可移植的高性能事务处理语言,PL/SQL 引擎驻留在 Oracle 服务器中,PL/SQL 块由声明部分、可执行部分和异常处理部分组成,PL/SQL 数据类型包括标量数据类型、LOB 数据类型和属性类型,控制结构包括条件控制、循环控制和顺序控制,PL/SQL 支持动态 SQL,运行时出现的错误叫做异常,异常可以分为预定义异常和用户定义的异常,28,