Oracle数据库使用.pptx

使用 PL/SQL2回顾q同义词是现有数据库对象的别名q序列用于生成唯一、连续的序号q视图是基于一个或多个表的虚拟表q索引是与表相关的一个可选结构，用于提高 SQL 语句执行的性能q索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引q索引组织表基于主键访问数据3目标q理解 PL/SQL 功能和特点q了解数据类型及其用法q理解逻辑比较q理解控制结构q掌握错误处理4PL/SQL 简介qPL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言qPL/SQL 是对 SQL 的扩展q支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构q可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑q与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性5PL/SQL 的优点 2-1q支持 SQL，在 PL/SQL 中可以使用：q数据操纵命令q事务控制命令q游标控制qSQL 函数和 SQL 运算符q支持面向对象编程(OOP)q可移植性，可运行在任何操作系统和平台上的Oralce 数据库q更佳的性能，PL/SQL 经过编译执行用户将整个语句块发送给 OracleOracleProcedureBegin ProcedureCall SQL Command End6PL/SQL 的优点 2-2 PL/SQLSQLq与 SQL 紧密集成，简化数据处理。q支持所有 SQL 数据类型q支持 NULL 值q支持%TYPE 和%ROWTYPE 属性类型q安全性，可以通过存储过程限制用户对数据的访问7PL/SQL 的体系结构qPL/SQL 引擎驻留在 Oracle 服务器中q该引擎接受 PL/SQL 块并对其进行编译执行将PL/SQL 块发送给 Oracle 服务器用户用户执行过程语句引擎将 SQL 语句发送给SQL 语句执行器Oracle 服务器PL/SQL引擎引擎SQL 语句执行器过程语句过程语句执行器执行器执行 SQL 语句将结果发送给用户8PL/SQL 块简介qPL/SQL 块是构成 PL/SQL 程序的基本单元q将逻辑上相关的声明和语句组合在一起qPL/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 语句的可执行部分指定出现错误时需要执行的操作9变量和常量 1-2 qPL/SQL 块中可以使用变量和常量q在声明部分声明，使用前必须先声明q声明时必须指定数据类型，每行声明一个标识符q在可执行部分的 SQL 语句和过程语句中使用q声明变量和常量的语法：identifieridentifier CONSTANT CONSTANT datatypedatatype NOT NULL NOT NULL :=|DEFAULT:=|DEFAULT exprexpr;q给变量赋值有两种方法：q使用赋值语句:=q使用 SELECT INTO 语句10变量和常量 2-2DECLARE 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;11数据类型qPL/SQL 支持的内置数据类型数据类型数据类型LOB类型标量类型属性类型%ROWTYPE数字字符布尔型日期时间BFILEBLOBCLOBNCLOB%TYPE提供某个变量或数据库表列的数据类型提供表示表中一行的记录类型 存储非结构化数据块12数字数据类型q指定数值的存储格式数字数据类型数字数据类型NUMBERBINARY_INTEGERPLS_INTEGERNATURALNATURALLNPOSITIVEPOSITIVENDECIMALFLOATINTEGERREALSIGNTYPE存储有符号整数，所需存储空间少于NUMBER类型值存储整数、实数和浮点数存储有符号整数，可使算术计算快速而有效13字符数据类型q字符数据类型包括：qCHARqVARCHAR2qLONGqRAWqLONG RAWqPL/SQL 的数据类型与 SQL数据类型的比较数据类型SQL类型PL/SQL类型CHAR1.20001.32767LONG1.2GB1.32760LONG RAW1.2GB1.32760RAW1.20001.32767VARCHAR21.40001.3276714日期时间和布尔数据类型q日期时间类型q存储日期和时间数据q常用的两种日期时间类型qDATEqTIMESTAMPq布尔数据类型q此类别只有一种类型，即BOOLEAN类型q用于存储逻辑值(TRUE、FALSE和NULL)q不能向数据库中插入BOOLEAN数据q不能将列值保存到BOOLEAN变量中q只能对BOOLEAN变量执行逻辑操作15LOB 数据类型 2-1q用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。qLOB 数据类型可存储最大 4GB的数据。qLOB 类型包括：qBLOB 将大型二进制对象存储在数据库中qCLOB 将大型字符数据存储在数据库中qNCLOB 存储大型UNICODE字符数据qBFILE 将大型二进制对象存储在操作系统文件中16LOB 数据类型 2-2qLOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置qDBMS_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,amount,offset,output_var);DBMS_OUTPUT.PUT_LINE(output_var);END;/从表中选择 CLOB 定位符到 clob_var变量中从CLOB数据中读取24个字符存储到 output_var 变量中显示读到的信息17属性类型q用于引用数据库列的数据类型，以及表示表中一行的记录类型q属性类型有两种：q%TYPE -引用变量和数据库列的数据类型q%ROWTYPE -提供表示表中一行的记录类型q使用属性类型的优点：q不需要知道被引用的表列的具体类型q如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变icode itemfile.itemcode%TYPE;emp_rec scott.emp%ROWTYPE;18q布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接q布尔表达式有三种类型：q数字布尔型q字符布尔型q日期布尔型逻辑比较q逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式q布尔表达式由关系运算符与变量或常量组成关系运算符关系运算符说明说明=比较两个变量是否相等，如果值相当，则返回比较两个变量是否相等，如果值相当，则返回 True,!=比较两个变量，如果不相等，则返回比较两个变量，如果不相等，则返回 True比较两个变量，检查值比较两个变量，检查值 1 是否大于是否大于 值值 2=比较两个变量，检查变量比较两个变量，检查变量 1 是否大于等于变量是否大于等于变量 219控制结构PL/SQL 支持的流程控制结构：q条件控制qIF 语句qCASE 语句q循环控制qLOOP 循环qWHILE 循环qFOR 循环q顺序控制qGOTO 语句qNULL 语句20条件控制 2-1qIF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIFDECLARE 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;21条件控制 2-2BEGIN 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;qCASE 语句用于根据单个变量或表达式与多个值进行比较q执行 CASE 语句前，先计算选择器的值22循环控制q循环控制用于重复执行一系列语句q循环控制语句包括：qLOOP、EXIT 和 EXIT WHENq循环控制的三种类型：qLOOP -无条件循环qWHILE -根据条件循环qFOR -循环固定的次数LOOP sequence_of_statementsEND LOOP;WHILE condition LOOP sequence_of_statementsEND LOOP;FOR counter IN REVERSE value1.value2LOOP sequence_of_statementsEND LOOP;23顺序控制q顺序控制用于按顺序执行语句q顺序控制语句包括：qGOTO 语句-无条件地转到标签指定的语句qNULL 语句-什么也不做的空语句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=i201;NULL;END;24动态 SQLq动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句q编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行qDDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行q执行动态 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;25错误处理 2-1q在运行程序时出现的错误叫做异常q发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分q异常有两种类型：q预定义异常-当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发q用户定义异常 -用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发26错误处理 2-2q处理预定义异常q处理用户定义异常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(无法识别该类别);END;27qRAISE_APPLICATION_ERROR 过程q用于创建用户定义的错误信息q可以在可执行部分和异常处理部分使用q错误编号必须介于 20000 和 20999 之间q错误消息的长度可长达 2048 个字节q引发应用程序错误的语法：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;28总结qPL/SQL 是一种可移植的高性能事务处理语言 qPL/SQL 引擎驻留在 Oracle 服务器中qPL/SQL 块由声明部分、可执行部分和异常处理部分组成qPL/SQL 数据类型包括标量数据类型、LOB 数据类型和属性类型q控制结构包括条件控制、循环控制和顺序控制 qPL/SQL 支持动态 SQLq运行时出现的错误叫做异常q异常可以分为预定义异常和用户定义的异常