1、PL/SQL简介,Oracle SQL和PL/SQL编程,目标,解释使用PL/SQL块与使用一些SQL语句相比的好处 识别PL/SQL块的各个部分并说明他们的内容 识别PL/SQL块的必需部分和可选部分 说明如何执行PL/SQL块 解释变量的用途 解释常量与变量之间的区别 识别有效的变量名称 列出PL/SQL变量的有效数据类型 为PL/SQL变量指定一种动态数据类型 初始化PL/SQL变量 在PL/SQL块中使用DML语句,内容,PL/SQL的优点 PL/SQL块基本结构 PL/SQL块声明部分 PL/SQL块可执行部分 PL/SQL注释,PL/SQL的优点,在通过网络执行一个SQL语句来访问
2、Oracle数据库时，会将各个语句都发送到服务器，进行处理，然后执行它们，向用户返回结果（或一个消息）。每一个SQL语句都最少需要在网络中两次经过“旅行”，一次是从用户到服务器，另一次是从服务器到用户。这会产生大量网络数据流量。 在过程语言SQL（Procedure Language SQL，PL/SQL）程序嵌入SQL语句是取代执行多个SQL语句的另一种方法。PL/SQL扩展了SQL的功能。具有如下优点： 允许用户包括异常处理和控制结构，从而允许更高的灵活性和效率。 对经常执行的任务，可以存储PL/SQL块并由各种应用程序（或者用户）使用。,内容,PL/SQL的优点 PL/SQL块基本结构
3、PL/SQL块声明部分 PL/SQL块可执行部分 PL/SQL注释,基本结构,一个PL/SQL代码单元就称为一个“块”。 各个PL/SQL块都可以划分为三部分：声明，可执行，异常处理。 在这三部分中，“可执行部分是每一个PL/SQL程序中唯一的必需部分”。声明和异常处理部分是可选的,DECLARE 声明部分，声明语句块中使用的变量或常量BEGIN 可执行部分EXCEPTION 异常处理部分END; 语句块结束,基本结构,声明部分： 如果在块中使用了任何变量或常量，那么必需首先在这个部分指出它们。 可执行部分： 这一部分可能包括SQL语句和（或）PL/SQL语句。 SQL语句和PL/SQL语句之
4、间的主要区别是SQL语句用来访问或处理数据库表中的数据，而PL/SQL语句主要处理PL/SQL块中包含的数据。 异常处理部分： 用来在执行块的过程中出现了错误时显示消息或者指出应该执行的其他操作。 这个异常处理部分不是用来解决在编译块时发现的语法错误，它处理在执行语句的过程中发生的错误。 END语句结束： END关键字后面有一个分号。分号用来结束PL/SQL块中的各个语句。,基本结构,示例：编写PL/SQL块，检索BOOKS表中的一本书名和零售价，然后将价格增加20%，显示这本书新的零售价和书名。,DECLAREc_rateincrease CONSTANT NUMBER(3,2):=1.2;
5、v_title VARCHAR2(30);v_retail books.retail%TYPE;v_newretail NUMBER(5,2); BEGINSELECT title,retail,retail*c_rateincreaseINTO v_title,v_retail,v_newretailFROM booksWHERE isbn=1059831198;DBMS_OUTPUT.PUT_LINE(The new price for |v_title|is $|v_newretail); END;,基本结构,分析示例： PL/SQL块的声明部分包括一个常量（c_)和三个变量（v_）。
6、可执行部分使用一个SELECT语句来检索两个不同列（Title和Retail）的内容，并将这些值存储到（INTO）在声明部分指出的变量中。 注意，在SELECT子句中对Retail列执行力算术运算，以便确定这本书的新价格。零售价将乘以存储在c_rateincrease变量（它的值实际上是1.20或者120%）中的数量。 在将储在声明的变量中之后，就可以使用DBMS_OUTPUT程序包（DBMS_ OUTPUT.PUT_LINE）的PUT_LINE函数显示它们。与以前在SQL*Plus中执行的SELECT命令不同，PL/SQL块在默认情况下不会显示SELECT语句的结果。 DBMS_OUTPUT
7、是一个包含一组函数的程序包，用户可以应用这些函数来显示为变量指定的值。 使用DBMS_ OUTPUT.PUT_LINE显示信息之前，需要在SQL提示符下键入SET SERVEROUTPUT ON ， SERVEROUTPUT 环境变量指出分配一个缓冲区来存储PL/SQL块的输出。ON可以创建缓冲区。,基本结构,结果中，在声明部分，各个变量声明都是以分号结尾。在可执行部分，各个完整的语句也是以分号结尾的。虽然SELECT语句出现在多行上，但是Oracle将SELECT关键字和下一个分号之间的所有内容都视为一个语句。END关键字指出这个块的结束，后面跟一个分号。 一个PL/SQL块可能会包含多个分
8、号，那么Oracle如何知道何时应该执行这个块？在SQL*Plus中，用户只需在SQL提示符上或者在一个空行上输入一条斜线（/）就会执行这个块。,内容,PL/SQL的优点 PL/SQL块基本结构 PL/SQL块声明部分 PL/SQL块可执行部分 PL/SQL注释,声明部分,PL/SQL块声明部分定义了在块中使用的变量。至少必须为各个变量指定一个名称并指出变量所能包含的数据类型。此外，还可以在声明部分初始化变量。在“初始化”一个变量时，将为它指定一个值。除非将这个变量定义为一个常量，否则可能会在PL/SQL块中某个位置更改最初指定的值。,Variablename CONSTANT datatyp
9、e NOT NULL:=| DEFAULT value_or_expression;,声明变量语法格式：,声明部分 变量名称,变量名称最多可以包括30个字符、数字或特殊符号。这个名称必须以一个字符开始。 变量名称不应该与在这个块中引用的列名称相同。 下表是各种变量的标准命名约定,声明部分 常量,前面示例中，通过在声明部分声明一个常量，然后在执行计算的任何位置使用这个变量名称，用户只需要更新一次这个比例即可，而不必在整个表中多次更新比例。 用户可以使用可选的CONSTANT 关键字来指定一个常量。在将一个变量声明为常量时，必须指定一个值，否则Oracle服务器将返回一个错误消息。,声明部分 PL
10、/SQL数据类型,PL/SQL变量可以划分为4中数据类型： 1.标量（Scalar） 2.复合（Composite） 3.引用（参照 Reference） 4.大对象（Large Object,LOB）,声明部分 PL/SQL数据类型,RECORD TABLE VARRAY,REF CURSOR REF objec_type,BFILE BLOB CLOB NCLOB,BINARY_DOUBLE BINARY_FLOAT BINARY_INTEGER BEC BECIMAL DOUBLE PRECISION FLOAT INT INTEGER NATURAL NATURALN NUMBER N
11、UMBERIC PLS_INTEGER POSITIVE REAL SIGNTYPE SMALLINT,CHAR CHARACTER LONG LONG RAW NCHAR NVARCHAR2 RAW ROWID STRING UROWID VARCHAR VARCHAR2,BOOLEAN,DATE,标量类型,复合类型,引用类型,LOB类型,数字,字符,布尔,日期,声明部分 PL/SQL数据类型,标量数据类型 可以用来存储一个单独的值，它包括的数据类型与用于数据库表的列的数据类型相同。此外，PL/SQL还包括一种布尔数据类型（为其指定的值可以是TURE，FALSE或NULL）以及用于整数的数据
12、类型（BINARY_INTEGER和PLS_INTEGER）。 注意：Oracle的 SQL中没有布尔数据类型，所以不能使用DML语句为表中的列指定布尔变量的值（TURE、FALSE或NULL）。,声明部分 PL/SQL数据类型,常用标量数据类型,声明部分 PL/SQL数据类型,常用标量数据类型,声明部分 PL/SQL数据类型,复合数据类型 是可以组合在一起并被视为一个单元的数据集合。它可以用来确定表中一个整行的结构，而不必定义各个单独的列。 引用数据类型 存储指向其他程序项目的指针 大对象（LOB）数据类型 用来存储指定大对象（例如映像）位置的定位器。,声明部分 PL/SQL数据类型,不同标
13、量数据类型的声明的例子,声明部分 PL/SQL数据类型,%TYPE属性 可以用来指定与同一个块中声明的另一个变量或数据库表中的一列相同的数据类型。要想将一个变量的数据类型声明为与同一个块中声明的另一个变量相同，只需要在%TYPE属性前面添加这个变量的名称即可。 许多情况下，当你声明一个用于PL/SQL块的变量时，是为了使用这个变量来包含当前存储在表的一列中的某个值。在这种情况下，你必须确保数据类型是相同的。可以使用%TYPE属性来复制所引用列的定义。例如,假设你不记得BOOKS表的Title列中可以存储多少个字符，或者记不起已经将其定义为存储VARCHAR2还是CHAR数据，可以使用books
14、.title%TYPE 但是，如果数据库表的列具有一个NOT NULL值约束，那么在PL/SQL块中不会实现这个约束。,声明部分 PL/SQL数据类型,NOT NULL约束 要想确保变量总是包括一个值，可以在声明它时为其指定一个NOT NULL约束。要想为变量指定一个NOT NULL约束，只需要在声明这个变量时在数据类型之后包括关键字NOT NULL即可。如果将这个变量定义为NOT NULL，那么，必须为其指定一个值或者初始化它，否则在执行块时将返回一个出错消息。,声明部分 PL/SQL数据类型,初始化变量 可以使用DEFAULT关键字或者PL/SQL“赋值运算符”（:=）来初始化变量，或者说
15、为其指定一个初始值。 在声明变量时，必须初始化具有NOT NULL约束的任何变量。 对于使用非数字数据类型声明的变量，初始化使用的值也必须包括在单引号中。,声明部分 PL/SQL数据类型,初始化变量 使用赋值运算符（:=）和DEFAULT关键字进行初始化的例子,内容,PL/SQL的优点 PL/SQL块基本结构 PL/SQL块声明部分 PL/SQL块可执行部分 PL/SQL注释,可执行部分,一个块的可执行部分是由BEGIN关键字标识的。 可执行部分是PL/SQL块的唯一一个必需部分，它包含在执行块本身时执行的SQL语句和PL/SQL语句。可以在PL/SQL块中执行的SQL语句类型与可以在SQL提
16、示符上执行的SQL语句几乎完全相同。主要区别在于SELECT语句的语法。,可执行部分,SELECT语句 在PL/SQL块中可以包括SELECT来检索数据库表中的数据。如下图所示：,可执行部分,SELECT语句 在PL/SQL块中使用SELECT语句时，必须对标准SQL SELECT语句的语法进行少量修改。 PL/SQL中的SELECT语句语法,SELECT columnname,columnname, INTO variablename,variablename, FROM tablename WHERE condition;,可执行部分,SELECT语句 在执行SQL语句时，Oracle服务
17、器将分配一个内存区域，它不仅存储这个语句，还存储语句的结果。这个内存区域称为“游标”。存在两种游标：隐式游标和显示游标。 在使用DML语句或者只返回一行结果的SELECT语句时，Oracle服务器将创建一个“隐式游标”。隐式游标是自动的，不需要用户的任何干预。 “显示游标”必须由用户来创建和管理。后面内容会讲解。 因为必须将PL/SQL块中的SELECT语句检索到的数据放在一个隐式游标中，所以使用了INTO子句来指出存储所检索数据的变量（即隐式游标中的区域）。这些变量是在块的声明部分创建的。 数据库表中的列名称是在这个语句的SELECT子句中列出的。INTO子句指出隐式游标中包含的变量。要想正
18、确执行，必须以相同的顺序列出SELECT子句中的列名称和变量名称。FROM子句指定了包含所引用数据的表。 注意：语法格式中SELECTINTO命令只允许查询返回一行。在使用隐式游标时， SELECTINTO语句只能返回一行结果。所以使用了WHERE子句来限制结果只有一行。PL/SQL块可以使用一个显示游标来检索多行结果，后面内容讲解。,可执行部分,PL/SQL中的DML语句 PL/SQL块中不能使用DDL语句，可以使用DML语句来添加、更新和删除存储在表中的数据。INSERT、UPDATE和DELETE命令的语法与SQL中相同。 例子：编写PL/SQL，向PUBLISHER表添加一个新的出版社
19、。,BEGININSERT INTO publisherVALUES(6,A NEW PUBLISHER,JAYE WRIGHT,800-555-3591);COMMIT; END;,可执行部分,PL/SQL中的DML语句 例子结果,例子中不需要使用任何变量， 所以省略了PL/SQL块中的声明部分COMMIT命令确保在执行了INSERT命令之后，其他用户可以立即使用,可执行部分,PL/SQL中的DML语句 更新信息 例子：更新出版社联系人姓名，将Jaye更改为Jay,BEGINUPDATE publisherSET contact = JAY WRIGHTWHERE pubid=6;COMMI
20、T; END;,可执行部分,PL/SQL中的DML语句 更新信息 例子结果,可执行部分,PL/SQL中的DML语句 删除信息 例子：从publisher表中删除出版社6的信息,BEGINDELETE FROM publisherWHERE pubid=6;COMMIT; END;,可执行部分,PL/SQL中的DML语句 删除信息 例子结果,内容,PL/SQL的优点 PL/SQL块基本结构 PL/SQL块声明部分 PL/SQL块可执行部分 PL/SQL注释,注释,在 PL/SQL 里，可以使用两种符号来写注释，即： 使用双 -( 减号 ) 加注释： PL/SQL 允许用 来写注释，它的作用范围是
21、只能在一行有效。使用 /* */ 来加一行或多行注释，如：,v_retail NUMBER(5,2) 价格变量。,/*/ /* 文件名： statistcs_sal.sql */ /*/,提示：被解释存放在数据库中的 PL/SQL 程序，一般系统自动将程序头部的注释去掉。 只有在 PROCEDURE 之后的注释才被保留；另外程序中的空行也自动被去掉。,总结,PL/SQL块可以组合几个SQL语句并减少网络数据流量 PL/SQL块划分为三个部分：声明，可执行和异常处理 可执行部分是PL/SQL块中唯一一个必需的部分 每一个PL/SQL块都必须以END关键字和一个分号结束 声明部分是使用DEFAUL
22、T关键字标识的 变量是在PL/SQL块的声明部分定义和初始化的 变量引用了计算机内存中一个保留区域。常量是其值在执行块的过程中不会发生变化的变量。 在声明部分，各个变量的声明都必须以分号结束 变量数据的类别包括标量，复合，引用和大对象 有效的标量数据类型包括可以用于定义有效的标量数据类型包括可以用于定义列的SQL数据类型以及BOOLEAN、BINARY_INTEGER和PLS_INTEGER,总结,可以使用赋值运算符（:=）或者DEFAULT关键字来初始化变量 PL/SQL块的可执行块包括了SELECT语句来检索表中的一行，那么它必须包括INTO子句 在执行DML语句或者SELECT语句只检索表中的一行时,Oracle将创建一个隐式游标 在发生一个隐含的或者明确的COMMIT操作之前，其他用户不能查看在PL/SQL块中包含的DML操作对数据进行的更改,2019/7/7,Inspur group,谢谢大家!,
