精通Oracle核心技术和项目实战之Oracle编程基础.ppt

第12章Oracle编程基础,本章介绍Oracle编程基础，主要有PL/SQL相关内容和Oracle的控制语句。PL/SQL（ProceduralLanguage/SQL）是Oracle在SQL标准语言上进行扩展后形成的程序设计语言，是一种Oracle数据库特有的、支持应用开发的语言。Oracle编程中也需要结合使用流程控制语句，用于控制PL/SQL语句、语句块的执行过程。,12.1PL/SQL简介,PL/SQL是Oracle公司开发的一种编程语言，它是对SQL的扩充。在PL/SQL程序中，可以定义变量、数据类型、函数以及过程，可以使用流控制语句，可以包含SQL语句，还可以进行错误处理。利用PL/SQL程序，可以对数据库进行复杂的访问。,12.1.1PL/SQL基本结构,PL/SQL是一种结构化编程语言。程序的基本单元是块，主要的块形式有函数、过程和匿名块。一个PL/SQL块由以下三部分组成，其大体结构如图所示。,12.1.1PL/SQL基本结构,【示例12-1】输出语句“Hello,Lifeforfun,styleforteens”。,12.1.2PL/SQL注释,注释增强了程序的可读性，使得程序更易于理解。注释在进行编译时被PL/SQL编译器忽略。注释有单行注释和多行注释两种，这与许多高级语言的注释风格是一样的。1.单行注释单行注释由两个连字符（-）开始，一直到行尾（回车符标志着注释的结束）。【示例12-2】对示例12-1进行单行注释。,12.1.2PL/SQL注释,2.多行注释多行注释由/*开头，由*/结尾，这和C语言是一样的。【示例12-3】多行注释说明。,12.1.3PL/SQL基本规则,做任何事情都有规范，编程也一样。在PL/SQL中，也需要遵守一些规范，这样可以提高代码的可读性，提高工作效率。,12.1.3PL/SQL基本规则,1.PL/SQL中允许出现的字符集。字母，包括大写和小写。数字，即09。空格、回车符。数学符号，包括+、-、*、/、=。间隔符，包括()、?、!、;、:、,、#、$、%、&等。,12.1.3PL/SQL基本规则,2.PL/SQL需要遵守的要求。标识符是不区分大小写的。标识符中只允许出现字母、数字、下划线，并且以字母开头。语句以分号结束。字符型和日期型必须使用括号括起。语句的关键词、标识符,12.1.3PL/SQL基本规则,3.PL/SQL中可以参考的规范。每行只写一条语句。所有的过程名称大写。所有的变量以及自建的过程或游标、触发器名称都使用有意义的名称命名。命名应该以“_”的连接方式，也可以使用大小写混合的方式。变量前最好加上前缀，以表示该变量的数据类型、作用范围等。每个变量都应加上注释。在重要的程序段都应加上注释。这些只是基本的规则，可以提高代码的可读性。在日常中应该注意这些规范，形成良好的编程习惯。,12.1.4PL/SQL数据类型,Oracle定义的数据类型很多，掌握这些简单的数据类型有助于编写一些复杂的程序。下面将对常用数据类型进行介绍。,12.1.4PL/SQL数据类型,1.数字类型数字类型变量存储整数或者实数。它包含number、pls_integer和binary_integer3种基本类型。其中，number类型的变量可以存储整数或浮点数，而pls_integer和binary_integer类型的变量只存储整数。,12.1.4PL/SQL数据类型,2.字符类型字符类型变量用来存储字符串或者字符数据。其类型包括varchar2、char、nchar、nvarchar2等。varchar2：可以存储变长字符串。char类型表示定长字符串。nchar类型表示的是存储Unicode字符集的定长字符集数据。nvarchar2类型表示的是存储Unicode字符集的变长字符集数据。,12.1.4PL/SQL数据类型,3.日期类型日期类型是用来存放表示日期的数据的。日期类型有date、timestamp。date类型用来存储日期和时间的。date类型的时间精确到秒。timestamp类型也是用来存储日期和时间信息，包括世纪、年、月、天、小时、分钟和秒。与date类型的区别就是在显示日期和时间时更精确。timestamp类型可以精确到小数秒。使用timestamp类型存放日期还可以显示当前是上午还是下午。,12.1.4PL/SQL数据类型,4.布尔类型布尔类型中的唯一类型是boolean，主要用于控制程序流程。一个布尔类型变量的值可以是true、false和null。,12.1.4PL/SQL数据类型,5.type定义的数据类型上面介绍了几种常用的数据类型，下面来介绍一下如何定义数据类型，它类似C语言中的结构类型。定义数据类型的语句格式如图所示。,12.1.5PL/SQL运算符,运算符是一种符号，用来指定要在一个或者多个表达式中执行的操作。运算符一般也用在执行列、常量或变量之间的数学运算和比较操作中。而表达式则是标识符、值和运算符的组合。简单的表达式可以是一个常量、函数、列名、变量和子查询。可以用运算符将两个或更多的简单表达式连接起来组成复杂的表达式。PL/SQL运算符包括：算数运算符、位运算符、比较运算符、逻辑运算符、赋值运算符、字符串连接符和一元运算符。,12.1.5PL/SQL运算符,1.算术运算符算术运算符用于执行数字型表达式的算术运算。在PL/SQL中支持的算术运算及其说明如下所示。+（加），加法或正号。-（减），减法或负号。*（乘），乘法运算。/（除），除法运算。,12.1.5PL/SQL运算符,2.比较运算符比较运算用来比较两个表达式的大小。在PL/SQL中可以支持的比较运算符主要包括以下几种。，大于。=，等于。=，大于或等于。（!=），不等于,12.1.5PL/SQL运算符,3.逻辑运算符逻辑运算符用来对逻辑条件进行测试，以获得其真实情况。它与比较运算符一样，根据测试结果返回布尔值true、false、null。逻辑运算符有and、or、not、between、like等，具体的在查询一章中已详细讲解。,12.1.5PL/SQL运算符,4.字符串连接符字符窜连接连接符“|”用于实现字符串之间的连接操作，如图所示。,12.1.5PL/SQL运算符,5.赋值运算符在PL/SQL中，赋值运算符为“:=”，即将表达式的值赋给一个变量。【示例12-4】声明一个变量，并为该变量赋值为“confidence”。,12.1.5PL/SQL运算符,6.运算符的优先级Oracle中的运算符具有不同的优先级。同一个表达式中包含不同的运算符时，运算符的优先级决定了表达式的计算和比较操作的顺序。Oracle中各种运算符的优先级的顺序如下所示。括号，()正、负运算，+、-乘、除运算，*、/加、减、字符连接运算，+、-、|比较运算，=、，=，（!=）逻辑非运算，not逻辑与运算，andall、any、between、in、like、or、some等运算赋值运算，:=,12.1.5PL/SQL运算符,在进行数据查询时，经常需要对查询到的数据进行再次计算处理。PL/SQL中允许直接在select子句中使用计算列，计算列是通过某些列的数据进行演算得来的结果。【示例12-5】结合order_items数据表，计算unit_price打9折之后的值。,12.1.6PL/SQL表达式,数据库中经常使用表达式来计算结果，尤其在变量和常量的使用过程中。在前面已经接触过表达式的使用，它和普通编程语言的表达式类似。本小节系统地介绍表达式的类型以及如何使用表达式。1.表达式简介表达式不能独立构成语句，表达式的结果是一个值。如果不给这个值安排一个存放的位置，则表达式本身毫无意义。通常，表达式作为赋值语句的一部分出现在赋值值运算符的右边，或者作为函数的参数等。,12.1.6PL/SQL表达式,2.字符和数值表达式（1）数值表达式是由数值类型的常量、变量以及函数，由运算符连接而成。在PL/SQL中，可以使用的算数运算符有。加号+。减号-。乘号*。除号。（2）对于字符表达式，唯一的运算符就是“|”，它的作用是把几个字符串连接在一起，如表达式：wonderful|life|.的值就相等于wonderfullife。,12.1.6PL/SQL表达式,3.关系表达式和逻辑表达式（1）关系表达式是将字符或数值用关系运算符连接起来。（2）逻辑表达式是由逻辑符号和常量或变量等组成的表达式。,12.1.7PL/SQL中变量、常量的定义与使用,在PL/SQL块中可以定义变量、常量和数据类型，这使得PL/SQL块对数据的处理更加灵活。变量、常量和类型的定义放在PL/SQL块的变量声明部分。,12.1.7PL/SQL中变量、常量的定义与使用,1.变量和常量在PL/SQL程序运行时，需要定义一些变量来存放一些数据。（1）定义常量定义常量的语句格式如图所示。,12.1.7PL/SQL中变量、常量的定义与使用,（2）定义变量变量的定义有两种格式，如图所示。【示例12-6】定义了一个有关住址的变量，它是变长字符型，最大长度为20个字符。,12.1.7PL/SQL中变量、常量的定义与使用,（3）变量初始化变量在定义时可以指定默认值或初始值，在PL/SQL块的运行过程中还可以为其赋值。【示例12-7】定义三个变量id、name、hiredate，演示变量的声明、赋值和输出操作。【示例12-8】在上个示例中，已经了定义三个变量，id、name、hiredate，现在结合%type属性方法，添加一个变量是leavedate，该变量的数据类型与hiredate相同。,12.2PL/SQL中定义类型,在前面简单讲解了type定义的数据类型。现在讲解常用的自定义类型：记录类型和集合类型。它们都是复合数据类型。,12.2.1记录类型,记录类型允许在一个类型中包含若干类型不同的字段，字段类型可以是基本数据类型，也可以是另一个复合数据类型。记录类型的定义格式如图所示。,12.2.1记录类型,【示例12-9】例如，要存储顾客的信息，可以定义一个记录类型，包括姓名、电话、地址、记录日期等字段。【示例12-10】定义了两个shopper类型的变量，分别表示两个顾客。【示例12-11】在定义上述类型和变量的基础上，为记录类型中的字段赋值，并输出结构。【示例12-12】根据customers表的结构，可以定义记录类型变量cus。【示例12-13】根据记录类型变量cus，为customer_id、cust_first_name，city这三个字段赋值，并输出其字段值。,12.2.2集合类型,记录类型变量中包含若干类型不同的数据，而集合类型变量中包含多个相同类型的元素。要创建一个集合，先要定义一个集合类型，然后再定义该类型的集合变量。定义集合类型的语法如图所示。,12.2.2集合类型,【示例12-14】定义了一个集合类型，元素类型是整数。【示例12-15】定义两个集合变量begins1和begins2，并分别调用构造函数进行初始化，begins1中包含三个元素，begins2是一个空集合，不包含任何元素。集合类型类似于面向对象技术中的类，除了可以使用构造函数外，还有一些方法可以用来对集合进行操作。,12.2.2集合类型,集合的部分方法如表所示。,12.2.2集合类型,【示例12-16】结合两个集合变量begins1和begins2，演示集合的使用方法。除了上述定义集合类型的方法，还有一种方法，其语法结构如图所示。,12.2.2集合类型,【示例12-17】使用第二种定义集合类型方法，定义两个集合变量begins3，并对该变量进行赋值。,12.3Oracle中的条件控制语句,在Oracle数据库中，PL/SQL提供了丰富的流控制语句，用来对程序的执行流程进行控制。通过控制语句，我们可以编写更复杂的PL/SQL块。流控制语句分为两类，即条件判断语句和循环语句。所谓条件语句，就是指程序根据具体条件表达式来执行一组命令的结构。Oracle中的条件语句包括ifelse和casewhen两种形式。,12.3.1流程控制ifelse判断,if语句是一种条件判断语句，它根据条件判断的结果执行不同的分支语句。if语句的语法结构如图所示。,12.3.1流程控制ifelse判断,三种if语句的流程图如图所示。【示例12-18】在表customersnew中存储了有关顾客信息的记录。,12.3.1流程控制ifelse判断,1.if.结构这是if语句中最简单的结构方式，它只有一个if语句，如果给定的表达式不成立，那么将继续向下执行。【示例12-19】现欲统计表customersnew中，列status是“gold”的顾客信息，要求输出统计结果，如果存在贵宾顾客，则输出“本公司有拥有贵宾会员卡的顾客”，结合使用if.then语句。,12.3.1流程控制ifelse判断,2.ifelse结构该类型的表达式不是选A就是选B。该结构表示要么执行if后面的语句，要么执行else后面的语句，是二选一的模式。该结构执行完毕后，程序会继续向后执行。【示例12-20】参考上一个示例，现欲统计表customersnew中，列status是“gold”的顾客信息，要求输出统计结果，如果存在贵宾顾客，则输出“本公司有拥有贵宾会员卡的顾客”；如果不存在，则输出“本公司没人拥有贵宾会员卡”，结合使用if.else语句。,12.3.1流程控制ifelse判断,3.ifelsif结构该结构是前面两种使用方式的综合，它可以提供多个if条件选择，当程序执行到该结构部分时，它会对每一个条件进行判断，一旦条件为真，程序会执行相应的语句，而后继续判断下一个条件，直到所有条件判断完成。该结构执行完毕后，程序会继续向后执行。【示例12-21】当需要进行判断的情况多于一种时，可以利用elsif。,12.3.2流程控制casewhen分支,case语句与if语句类似，也是根据条件选择对应的语句执行。但是case结构用于多条件分支结构，可以完成计算多个条件并为每个条件返回单个值。可以使流程控制更加清晰。case结构的主要流程图如图所示。,12.3.2流程控制casewhen分支,1.简单case语句它给出一个表达式，并把表达式同提供的几个可预见的结果做比较，若与可预见结果相匹配，则执行对应的语句序列，如图所示。,12.3.2流程控制casewhen分支,2.搜索式case语句它会提供多个布尔表达式，然后选择第一个为true表达式，执行对应的脚本，其语法结构如图所示。,12.3.2流程控制casewhen分支,【示例12-22】表customersnew中，列status有“gold”、“Silver”、“Platinum”三种情况，利用顾客编号，结合case语句，输出对应的的顾客status类型，“gold”对应贵宾卡会员、“Silver”对应银卡会员、“Platinum”对应金卡会员。【示例12-23】使用搜索式case语句的方式进行改写上一个示例12-22。【示例12-24】在表order_items中存储了有关订单产品的记录信息。使用搜索式case语句的方式，在数据表order_items中，要求根据order_id得到的对应的价格unit_price，判断价格属于哪个范围并给出输出提示。,12.4Oracle中的循环语句,所谓循环语句，即指程序按照指定的逻辑条件循环执行一组命令的语句。Oracle中的循环语句包括三种方式：无条件循环、while循环和for循环。,12.4.1无条件循环,Oracle中，可以使用loop语句对数据进行循环处理，利用该语句可以循环执行指定的语句序列。其使用语法如图所示。,12.4.1无条件循环,无条件循环在循环开始时，不指定循环条件，但是必须在循环内部指定跳出循环的条件，否则，该循环将一直执行，造成死循环，loop循环语句的基本流程如图所示。,12.4.1无条件循环,【示例12-26】可以利用无条件循环输出customersnew表中，顾客编号在101112范围之间的所有顾客姓名。【示例12-27】可以利用exitwhen形式跳出loop循环。,12.4.2while循环,while循环与基本的loop循环比较的话，它可以结束loop循环。while关键