- Oracle PL/SQL宝典
- 赵越编著
- 878字
- 2020-08-28 06:11:37
第2章 认识 PL/SQL
PL/SQL作为Oracle的脚本语言,有着独特的使用方式及非常高的工作效率。本章将介绍有关SQL和PL/SQL的一些基础知识,这样初学者对PL/SQL的概念和使用方式就会有一个总体的认识。该章主要介绍如下几个知识点。
◆ 了解什么是PL/SQL及其优势,以及它的3个组成部分。
◆ 有关SQL*Plus的使用。
◆ 有关SQL Developer工具的介绍及使用。
◆ PL/SQL的数据类型。
2.1 SQL语言概述
SQL全称为Structured Query Language(结构化查询语言),它利用一些简单的句子构成基本语法来存取数据库内容。由于SQL简单易学,目前它已经成为关系数据库系统中使用最广泛的语言。
2.1.1 SQL语言的发展
SQL语言由Boyce和Chamberlin于1974年首先提出。1975—1979年间,在IBM San Jose Research Lab的关系数据库管理系统原型System R中,最早使用了该语言。
为了避免各产品之间的SQL语法不兼容,因此由ANSI(American National Standards Institute,美国国家标准局)制定了SQL-92标准(简称SQL2),定义出SQL的关键词与语法标准,以提高各家产品在SQL语法上的兼容性。
1999年,SQL标准的最新版本SQL-99(SQL3)发布,在SQL-92版本的基础上新增了一些特性,标志着SQL在满足用户需求方面又前进了一大步。
目前,SQL标准的最新版本为SQL-2011。大体而言,业界的产品都是在包含ANSI SQL的基础上,扩充自家产品的功能,以求能展现出本身的特色。
当前,大部分的数据库都支持SQL,它已成为操作数据库的标准语言。然而,各DBMS产品对SQL的支持程度也不尽相同。
2.1.2 SQL语言的功能
使用SQL语言可以创建、维护、保护数据库对象,并且可以操作对象中的数据。具体来讲, SQL可实现的功能如下。
◆ 数据定义语言(Data Definition Language,DDL):主要用于创建、修改或删除数据库对象,如表、视图、索引等。具体功能如下:
● 定义、删除、修改关系模式(基本表)。
● 定义、删除视图(View)。
● 定义、删除索引(Index)。
◆ 数据操纵语言(Data Manipulation Language,DML):主要用于查询、添加、修改或删除存储在数据库对象中的数据。具体功能如下:
● 数据库、表中数据的查询。
● 数据的增、删、改等操作。
◆ 数据控制语言(Data Control Language,DCL):可以控制访问数据库中特定对象的用户,还可以控制用户对数据库的访问类型。主要功能为用户访问权限的授予、收回。
2.1.3 SQL语言的执行方式
SQL语言最主要的执行方式有:交互式SQL、嵌入式SQL以及CLI(调用层接口)等。
1.交互式SQL
交互式SQL是直接执行SQL语句,一般DBMS都提供联机交互工具,可以从前端应用程序,如SQL Server中的Query Analyzer(查询分析器)、Oracle中的SQL*Plus Worksheet,直接与SQL服务器上的数据库进行通信。只要把查询输入到应用程序窗口,再执行SQL语句,就可以获取查询结果。通过这种方式可以迅速检查数据、验证连接和观察数据库对象。SQL语句由DBMS来进行解释。
2.嵌入式SQL
在这种方式中,将SQL语句嵌入到高级语言(宿主语言)中,使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力。例如,可以将SQL语句嵌入C应用程序代码中。在编译代码之前,预处理器将分析SQL语句,并把这些语句从C代码中分离出来。SQL代码被转换成能为RDMS理解的一种格式,其余的C代码则按照正常的方式进行编译。这种方式一般需要预编译,将嵌入的SQL语句转换为宿主语言编译器能处理的语句。
3.CLI(调用层接口)
CLI可以通过接口调用SQL语句。它定义了一套可以从宿主语言调用的公共函数,即提供一套API,通过函数调用实现对DBMS的操作。常用的CLI包括DBLib、OCI、ODBC、JDBC等,将逐步替代嵌入式SQL。
本书中,SQL代码实例的运行采用的是交互式,即在Oracle中的SQL*Plus工具下或等同该工具的其他工具下直接实现。
2.1.4 SQL语句结构
SQL语言由一些简单句子构成基本的语法,所有的SQL语句均有自己的格式,典型的SQL语句(查询语句)结构如图2.1所示。
图2.1 典型的SQL语句结构
SQL语法的基础是子句(clause),子句中会包括一些关键词(keyword)。每条SQL语句均由一个关键词开始,该关键词描述这条语句要产生的动作。SQL中常用的关键词及其功能如表2.1所示。
表2.1 SQL中常用的关键词及其功能
2.1.5 SQL环境
SQL环境是指数据在其中可以存在、对数据的SQL操作可以执行的框架。实际上,可以把SQL环境看作运行在某个设备上的数据库管理系统。基本的数据库元素,比如基本表、视图等都是在SQL环境中定义的。SQL环境包含模式、目录等元素,其结构关系如图2.2所示。
图2.2 SQL环境结构关系
1.模式对象
模式对象位于目录的底层,SQL数据就存储在这一层。通过使用SQL,就可以定义SQL对象,并可以修改、存储和操作对象中的数据。实际上,本书所介绍的大部分操作都直接影响到模式对象。
SQL-99标准定义了表、视图、域、约束、触发器等11种类型的模式对象,这些对象构成了SQL环境的基础。
2.模式(Schema)
这里所说的模式是指数据库模式,而不是关系模式,是表、视图、域等数据库模式对象的聚集,它是整个SQL环境体系结构的基本单位。在SQL中,它的创建采用CREATE SCHEMA语句,其语法如下:
CREATE SCHEMA <模式名> <模式元素>
比如,创建一个图书管理模式,包括图书信息、读者信息两个关系(表)和不能再借图书的读者视图,其说明简要描述如下:
CREATE SCHEMA LibrarySchema --创建模式 CREATE TABLE BookInfo --创建表 CREATE TABLE Reader --创建表 CREATE VIEW FullReader --创建视图
如果要把图书管理模式作为当前模式,则设置如下:
SET SCHEMA LibrarySchema
于是,随后定义的表、视图等模式元素均属于管理模式。
3.目录(Catalog)
SQL目录是对某一单个数据库的描述。它是模式的聚集,每个目录都有一个或多个模式,都包含INFORMATION-SCHEMA(信息模式)特殊模式,该模式包含此目录中所有模式的信息。
在目录中建立模式类似于在模式中建立表或视图之类的模式元素。可以用如下语句建立图书管理目录:
CREATE CATALOG LibraryCatalog
后面跟着属于图书管理目录的模式及模式说明。同样,可以用SET CATALOG(设置目录)语句设置当前目录。例如,要把图书管理目录作为当前目录,可进行如下设置:
SET CATALOG LibraryCatalog;
于是,随后定义的模式将属于管理目录。
本节主要对SQL环境涉及的概念做一下简单介绍,在SQL实际应用中,很少涉及模式、目录的创建和使用。
2.1.6 SQL DBMS的客户/服务器模型
在数据库的使用中,经常用到的是客户/服务器模型。关系DBMS模型和SQL都非常适合于使用该模型。其工作过程可简单描述如下。
◆ 运行在客户端的应用程序接收用户的输入并形成SQL语句,然后将其发送到服务器上的DBMS。
◆ DBMS翻译并执行SQL命令,并将结果发送回客户端(工作站)。
◆ 运行在客户端的应用程序格式化向用户显示结果。
◆ SQL的客户程序和服务程序与连接、会话、模块这三个概念紧密地联系在一起。
1.连接
在客户端上运行包含SQL的程序,则需要连接服务程序。可用如下的SQL连接语句,在客户程序和服务程序之间建立连接:
CONNECT TO <服务器名> AS <连接名>
用户可以建立多个连接,但在任何时间只能有一个是活动的。可以采用如下语句切换连接:
Set Connection <连接名>
访问完毕需要断开连接,语法如下:
Disconnect <连接名>
2.会话
当连接激活时,所执行的SQL操作将在客户程序和服务程序之间形成请求-响应式的交往,这种操作序列称为会话(Session)。
会话与建立它的连接处于同一状态,当连接处于待用状态时,会话也处于待用状态;当连接被激活时,会话也被激活。每个会话都有当前目录和该目录中的当前模式。
3.模块
SQL实现至少为客户端用户提供如下3种类型的应用程序(称为模块,Module)之一。
◆ 通用SQL接口,如交互式SQL。
◆ 嵌入式SQL,如嵌入于宿主语言中。
◆ 实际模块,如存储过程等。
2.2 什么是PL/SQL
PL/SQL是SQL语言的一种扩展。本节主要介绍有关PL/SQL的使用,实际上PL/SQL语言的使用也贯穿了整个Oracle的学习过程,是PL/SQL初学者或初级开发人员必须掌握的重要知识点,相信读者通过本节的学习,对该语言会有一个较全面的认识。
2.2.1 认识PL/SQL
PL/SQL(Procedural Language/Structured Query Language)称为过程化查询语言,是Oracle公司在标准的SQL语言基础上进行扩展而形成的一种可以在Oracle数据库上设计编程并执行的语言,并通过Oracle的PL/SQL引擎执行。本书将介绍PL/SQL以及它涉及SQL部分的知识。PL/SQL和普通的编程语言没有什么区别,可以实现逻辑判断、循环遍历及异常处理等,而这些是标准的SQL很难办到的。由于PL/SQL的基础是标准的SQL语句,因此就降低了学习者的门槛。总的来说,PL/SQL有以下几个特点。
◆ 支持事务控制和SQL数据操作命令。
◆ 支持SQL的所有数据类型,并且在此基础上扩展了新的数据类型,同时支持SQL的函数及运算符等。
◆ 服务器上的PL/SQL程序可以使用权限进行控制。
◆ 可以存储在Oracle服务器中。
◆ Oracle中有自己的DBMS包,可以处理数据的控制和定义命令。
由于PL/SQL扩展自SQL,因此与SQL比较,它具有下面几个优势。
1.模块化应用程序
在一段程序代码中可以实现一个或多个功能,如果使用标准的SQL完成这些功能可能需要执行语句;而如果使用程序块的话,则可以把这些操作都放到某个程序块内,然后对外提供一个调用方式和需要传入的参数(例如存储过程)。这对开发人员来说是一个很有用的功能,当代码编写一次后,其他人不需要再次编写同样的SQL代码,只需要调用一次已有的PL/SQL程序块即可,这种操作优势主要体现在存储过程或函数上。
2.提高运行性能
当标准的SQL被执行时,它只能一次一条地将SQL语句发送至服务器,在实际开发中,完成一个业务逻辑往往需要几条甚至几十条SQL语句,这就往往形成客户端会几十次地连接数据库服务器,而连接数据库本身就是一个很耗费资源的过程,当这个业务被完成时,在网络连接上会浪费大量的资源。
PL/SQL语句块可以包含多条SQL语句,而语句块可以嵌入到程序中,甚至可以存储到Oracle服务器上,用户只需要连接一次数据库就可以把所需要的参数传递过去,其他部分将在Oracle服务器内部执行完成,再返回最终的结果。这样就大大节省了网络资源的开销。图2.3描述了PL/SQL与标准的SQL语句多次访问数据库的差异。
图2.3 PL/SQL与标准的SQL语句多次访问数据库的差异
3.利用逻辑控制语句控制程序结构
既然PL/SQL是一种编程语言,那么它就可以完成一定的业务逻辑处理。要完成这种处理,就需要该编程语言有逻辑控制语句,而实际上PL/SQL可以利用条件语句或循环语句来控制程序流程,这样就大大增加了PL/SQL的实用性,我们可以利用逻辑控制语句完成标准的SQL语句完成不了的复杂业务。
PL/SQL可以很好地完成诸如这样的功能:学生的考试成绩可以分成多个等级,当由参数得到的成绩在某个范围内时,就可以将该成绩定成某个等级,在PL/SQL块中就可以利用CASE语句完成判断分类。而这样的功能在标准的SQL中很难实现,即便实现了也不是动态的数据。
4.可移植性强
PL/SQL可以成功运行在不同的服务器中,例如,从Windows数据库服务器移植到Linux数据库服务器;也可以把PL/SQL从一个版本的Oracle移植到其他版本的Oracle中。
5.可以更好地处理逻辑错误信息
标准的SQL在遇到错误时会提示异常,例如增加数据,一旦有异常就会终止,但是调用者却很难快速地发现错误点在哪里,即使发现出问题的地方也不能告诉开发人员该语句程序本身有问题,而不是逻辑上有问题。例如某个学生的成绩,成绩只能是小于100的正数,假如增加的数据中该字段是一个负数,正常来说是可以进入数据库的,但这在逻辑上是不被允许的,因为没有成绩为负的情况。而利用PL/SQL就可以完全避免发生类似的问题,我们可以利用流程拒绝这部分记录进入数据库。
利用PL/SQL还可以处理一些程序上的异常,而不至于因终止SQL操作,而造成调用SQL的展示页面出现生硬的错误提示。
2.2.2 PL/SQL结构
PL/SQL程序的基本单位是块,在PL/SQL中,程序代码块会很明确地分成3部分:声明部分、执行体部分和异常处理部分。其中,声明部分以DECLARE作为开始标志;执行体部分用BEGIN作为开始标志;而异常处理部分则以EXCEPTION作为开始标志。“执行体部分”是必需选项,而“声明部分”和“异常处理部分”则为可选部分。下面的一段文字描述了PL/SQL块的这3部分所在位置。
[DECLARE] --声明标志 /*声明部分,包括PL/SQL中的变量、常量以及类型等*/ BEGIN --执行标志 /*执行体部分,是整个PL/SQL块的主体部分,该部分在PL/SQL块中 必须存在,可以是SQL语句或者程序流程控制语句等*/ [EXCEPTION] --异常标志 /*异常处理部分,当出现异常时程序流程可以进入此处*/ END; --结束标志
在PL/SQL程序段中,其基本结构由这3部分组成。
下面的几个实例分别演示了PL/SQL语句块中这3部分的组合使用。
【实例2-1】只有执行体部分的代码块。
实例的代码中只有“BEGIN…END;”部分,该代码的作用是输出一句话,这是最简单的执行体。有关脚本如下:
01 BEGIN 02 DBMS_OUTPUT.PUT_LINE('这是输出的语句...'); 03 END; 04 /
【代码解析】
◆ 第1行表示执行体开始。
◆ 第2行表示通过标准输出设备打印一句话。
◆ 第3行表示执行体结束。
【执行效果】
通过SQL*Plus执行,效果见图2.4。
图2.4 PL/SQL块输出效果
通过DBMS_OUTPUT可以输出信息,这在PL/SQL编程中经常使用。
如果这段脚本没有输出结果,则需要在SQL*Plus下执行“SET SERVEROUTPUT ON”这样一行脚本,就可以打开SQL*Plus的输出功能。这在后面的章节中使用稍微频繁一些,希望读者注意这点。
【实例2-2】有声明和执行体部分的代码块。
实例中除了执行体外还有声明部分,这段脚本将为指定变量赋值,并打印到标准输出设备上。相关脚本如下:
01 DECLARE 02 v_res NUMBER(6,2); 03 BEGIN 04 v_res := 200/7; 05 DBMS_OUTPUT.PUT_LINE('输出结果是: ' || v_res); 06 END;
【代码解析】
◆ 第1行中的DECLARE是声明部分的关键词。
◆ 第2行表示声明变量v_res,数据类型为NUMBER。
◆ 第4行表示给v_res赋值,这里没用动态赋值方式,读者也容易理解。
◆ 第5行表示输出最后的结果。
【执行效果】
打开SQL*Plus并执行上面一段脚本,执行效果见图2.5。
图2.5 包含声明和执行体部分的PL/SQL块输出效果
【实例2-3】具有声明、执行体和异常处理部分的代码演示。
该实例将从DEPT表中查询指定数据,并把指定数据存储到变量中,当没有查询结果或出现多个查询结果时,会抛出对应的异常,要求把数据或异常提示输出到屏幕上。脚本如下:
01 DECLARE 02 v_dname VARCHAR2(12); 03 BEGIN 04 SELECT DNAME 05 INTO v_dname 06 FROM DEPT 07 WHERE LOC = 'DALLAS'; 08 DBMS_OUTPUT.PUT_LINE('输出结果为: ' || v_dname); 09 10 EXCEPTION 11 WHEN NO_DATA_FOUND THEN 12 DBMS_OUTPUT.PUT_LINE('没有对应的数据!'); 13 WHEN TOO_MANY_ROWS THEN 14 DBMS_OUTPUT.PUT_LINE('对应数据过多,请确认!'); 15 16 END; 17 /
【代码解析】
◆ 第1~2行是声明部分。
◆ 第3~16行是执行体部分,异常处理部分在执行体内。
◆ 第10~14行属于异常处理部分。
◆ 第4~7行表示把DNAME所对应的数据放到变量v_dname中。
◆ 第11、13行表示出现这两种异常时会输出相应的提示语句。
【执行效果】
打开SQL*Plus并执行上面一段脚本,执行效果见图2.6。
图2.6 完整的PL/SQL块输出结果
该实例中的SELECT…INTO…语句是PL/SQL特有的赋值语句,在后面的章节中会详细介绍。使用该类型语句时需要注意两个问题:第1个问题就是SELECT列名顺序和INTO后面的变量名顺序要一一对应;第2个问题就是该类型语句每次只能返回1条记录,如果返回记录超过1条或没有返回记录都会引发异常。
在这几个实例当中,每段程序都要包括“END”关键词,标志着该程序块的结束。
2.2.3 PL/SQL的主要组成部分
在PL/SQL语言中,标准的SQL语句执行起来是不受限制的。PL/SQL也有类似于SQL语言的分类,不过做了许多扩充。其分类如下。
◆ 数据定义语言(Data Definition Language,DDL):用来定义和管理数据库以及数据库中各种对象的语句,主要包括数据库对象的创建(CREATE)、删除(DROP)、修改(ALTER)操作。
◆ 数据操纵语言(Data Manipulation Language,DML):用来查询、添加、修改和删除数据库中数据的语句,这些语句包括SELECT、INSERT、UPDATE、DELETE等。
◆ 数据控制语言(Data Control Language,DCL):用来设置或者更改数据库用户或角色权限的语句,这些语句包括GRANT、DENY、REVOKE等。
◆ 系统存储过程(System Stored Procedure):是Oracle系统创建的存储过程,其目的在于能够方便地从系统表中查询信息,或者完成与更新数据库表相关的管理任务,或其他的系统管理任务。
◆ 一些附加的语言元素。
Oracle中分为系统存储过程和自定义存储过程,有关自定义存储过程,会在后面的章节中进行详细介绍。下面简单介绍一下附加的语言元素,主要包括以下几部分。
1.注释语句
注释是程序中不执行的文本字符串(也称为注解)。在PL/SQL中,可以使用两种类型的注释字符:一种是ANSI标准的注释符“--”,用于单行注释,一般对变量、条件子句可以采用该类注释;另一种是与其他编程语言(例如Java)相同的程序注释符“/* */”,用于多行注释,对于多行代码注释建议使用该类注释。
2.变量
变量是一种语言中必不可少的组成部分,在PL/SQL中使用变量必须先进行声明,否则会提示错误。关于变量的使用,会在后面的章节中进行介绍。
3.运算符
运算符是一些符号,能够用来执行算术运算、字符串连接、赋值,以及在字段、常量和变量之间进行比较。在通常情况下,运算符主要有6大类:算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符和字符串连接运算符。
4.函数
在PL/SQL中,函数用来执行一些特殊的运算以支持Oracle的标准命令。PL/SQL编程语言允许开发人员编写自己的函数,并且提供了下面两种SQL函数。
◆ 单行函数:每次使用返回一个结果。
◆ 集合函数:作用在多行记录上返回一个结果。
对于函数会在后面的章节中进行详细介绍。
5.流程控制语句
流程控制语句是指用来控制程序执行和流程分支的命令,包括条件执行语句IF...ELSE、重复执行语句WHILE以及跳转语句GOTO和RETURN等。
2.2.4 PL/SQL基本规范
对于PL/SQL语言,开发人员应该遵循一定的编程规范,严格按照规范编写脚本,能够写出高质量的程序,这样的程序具有高可读性,不仅利于其他开发人员的理解,而且利于以后的修改。下面简单列出了开发人员应遵循的规范。
(1)PL/SQL允许使用以下的字符集。
◆ 字母,包括大写和小写。
◆ 数字,即0~9。
◆ 空格、回车以及制表符。
◆ 符号包括+ - * / < > = ! ~ ^ ; : . ’@ % , " # $ & _ | ( ) [ ] { } ?。
(2)使用PL/SQL必须遵守以下要求。
◆ 标识符中只允许有字母、数字、下画线,并且以字母开头。
◆ 标识符最多30个字符。
◆ 标识符不区分大小写。所有的名称在存储时都被修改成大写。
◆ 不能使用保留字,这在其他编程语言中同样适用。如果与保留字同名,必须使用双引号括起来。
◆ 语句使用分号结束。如果多条语句在同一行,但只要它们都正常结束,那么执行就不会出现错误。语句块的结束标志END后面需要有分号。
◆ 语句的关键词、标识符、字段名称以及表的名称等都需要使用空格分隔。
◆ 字符类型和日期类型的数据需要使用单引号括起来。
(3)作者的建议:以下规范不是必须遵循的,但能提高代码的可读性。
◆ 全部的保留字、Oracle的内置函数、程序包以及用户定义的数据类型都用大写。
◆ 过程名称应大写。
◆ 每行只写一条语句。
◆ 变量以及自建的过程或游标、触发器名称都要使用有意义的名称命名。
◆ 命名应使用“_”的连接方式,而不是用大小写混合的方式。
◆ 变量前最好加上前缀,以表示该变量的数据类型、作用范围等。
◆ 变量所代表的含义最好加上注释。
◆ 重要的和不易理解的地方都应加上注释,不仅方便自己,而且方便其他开发人员阅读。
◆ 逗号后面以及运算符的前后都应加空格。
遵循以上的规范可有效提高代码的可读性,某些项目会根据实际情况做出更细的相关规范,读者在开发中应注意这些规范,养成良好的编程习惯。
2.3 PL/SQL在SQL*Plus中的使用
SQL*Plus可以操作Oracle数据库,随着数据库一起安装,是编写PL/SQL的常用工具。启动后,将进入一个CMD窗口,利用命令连接指定的数据库实例后,即可编辑相应的代码,对数据库的数据进行操作。
2.3.1 启动SQL*Plus
SQL*Plus是PL/SQL初学者必须学习的工具,当然,它不是必须使用的工具,和它的功能相似的产品有很多,比如:SQL Developer等。通过SQL*Plus可以完成对数据库的很多操作,其中主要有:
◆ 对数据库的数据进行增加、删除、修改、查询操作。
◆ 编写和执行PL/SQL代码。
◆ 管理数据库对象,如用户、表空间、角色、表、存储过程、函数、视图等对象。
当确认Oracle数据库安装成功后,就可以利用SQL*Plus工具对数据库进行访问了。启动SQL*Plus的方法有多种,其中一种就是直接在【开始】菜单中单击【程序】→【Oracle-OraDb11g_home1】→【应用程序开发】→【SQL Plus】命令,如图2.7所示,即可进入SQL*Plus界面。
图2.7 SQL*Plus启动路径
进入SQL*Plus界面后,还不能马上访问数据库中的数据,因为此时并没有连接到数据库,要想连接到指定的数据库,需要执行连接命令。下面的实例演示了如何连接指定的数据库实例。
【实例2-4】连接scott数据库实例。
要求在SQL*Plus下,连接scott数据库实例,密码为“123456+a”,SID为“ORCL”,并查询该用户下DEPT表的数据。执行的连接脚本如下:
scott/123456+a@orcl
执行的查询脚本如下:
SELECT * FROM DEPT;
【代码解析】
◆ scott表示数据库实例的用户名。
◆ SELECT后面的星号表示查询表中的所有数据。
【执行效果】
打开SQL*Plus并执行相关脚本,执行效果见图2.8。
图2.8 在SQL*Plus中连接数据库并查询数据
除了使用上面的这种方法连接数据库外,也可以在【开始】→【运行】当中输入以下语句:C:>sqlplus username/password@sid使用这种方法,可以直接连接到某个数据库实例。
2.3.2 SQL*Plus追加文本命令
其实在SQL*Plus下运行SQL语句是很简单的,只需在“SQL>”提示符后面输入需要执行的SQL语句,并按下【Enter】键即可。利用“/”可以直接运行上一次的程序。
SQL*Plus是命令编辑器,开发人员可以利用它编辑SQL语句,执行各种命令等操作。本小节将介绍一下SQL*Plus中的追加文本命令,该命令用于在当前行尾追加一行,具体语法如下所示:
APPEND TEXT;
【语法说明】
◆ APPEND表示追加文本的关键词。
◆ TEXT表示要追加的文本,APPEND和TEXT之间尽量使用2个空格。
【实例2-5】演示APPEND命令的使用方法。
要求首先对DEPT表执行基本查询,然后对该查询追加WHERE子句,查询DNAME为“SALES”的记录。执行SQL语句如下:
SELECT * FROM DEPT;
执行效果见图2.9,图中列出了DEPT表中的所有记录。在该语句后面追加WHERE条件语句,只把DNAME为“SALES”的记录查询出来。整个操作过程见图2.10。
从图2.10中可以看出,当追加WHERE子句后,可以按照计划把指定的记录查询出来。
图2.9 查询DEPT表中的所有数据
图2.10 追加文本后的命令
当使用APPEND命令时,它后面最好接1个以上空格,如果是1个空格的话,可能导致追加的文本和原来的文本之间没有空格。还有就是执行追加后的命令需要用到“/”才能正常执行。
2.3.3 SQL*Plus增加文本命令
本小节将介绍一下SQL*Plus中的增加文本命令,有2种用法,语法如下:
INPUT INPUT TEXT
【语法说明】
◆ INPUT:表示利用INPUT命令在缓冲区当前行之后可以添加多行。
◆ INPUT TEXT:表示利用INPUT命令在缓冲区当前行之后添加一行。
【实例2-6】演示INPUT命令的用法。
要求首先对DEPT表执行基本查询,然后利用INPUT命令对该查询追加排序子句。执行效果见图2.11。
图2.11 排序结果
该实例利用了在缓冲区当前行之后添加多行的方式创建了排序查询。除此之外,也可以利用INPUT命令直接添加“ORDER BY DNAME”这行代码,如“INPUT ORDER BY DNAME”,回车,执行查询。
2.3.4 SQL*Plus替换文本命令
利用替换文本的功能可以对某些错误的文本进行替换,以达到修改代码的目的。基本语法如下:
CHANGE /旧文本/新文本 CHANGE /文本
【语法说明】
◆ CHANGE /旧文本/新文本:表示利用CHANGE命令以“新文本”替换缓冲区指定的“旧文本”。
◆ CHANGE /文本:表示删除指定的文本。
【实例2-7】演示CHANGE命令的用法。
要求首先查询DEPT表的前2条数据,然后利用CHANGE命令改为查询SALGRADE表的前2条数据,执行效果见图2.12。最后利用CHANGE命令去掉查询记录数限制,查询SALGRADE表的所有记录,执行效果见图2.13。
图2.12 查询SALGRADE表的前2条数据
图2.13 查询SALGRADE表的所有记录
当利用CHANGE命令替换表名称时,需要两个表有相同的列,或利用星号代表所有列,否则会出现“列名不存在”的错误。
2.3.5 SQL*Plus删除命令
在SQL*Plus中使用的删除命令主要有以下几种。
1.删除缓冲区当前行
删除当前行的具体语法如下:
DEL
【实例2-8】演示删除当前行命令的使用方法。
首先查询DEPT表的所有数据,然后删除当前行,并验证。执行过程见图2.14。
【代码解析】
该实例首先分2行执行了一个查询命令,然后利用“/”重复执行上次执行的命令。读者可以发现,此时依然能够正常执行查询命令。当利用“DEL”命令删除当前行时,再次利用“/”执行查询命令,会发现提示“表名无效”的错误,说明“DEPT”这行已经被正常删除。
2.删除第n行
删除第n行的语法是在删除当前行的基础上完成的,语法如下:
DEL n
【实例2-9】演示删除第n行命令的使用方法。
要求首先查询DEPT表的所有数据,并排序,然后利用删除命令,去掉排序部分,执行查询。执行过程见图2.15。
图2.14 删除当前行命令演示过程
图2.15 删除排序行
【代码解析】
首先利用标准的查询列出了DEPT表中的数据,并按照“LOC”列进行排序;然后删除第4行,也就是“ORDER BY LOC”部分,利用“/”执行,读者可以发现,此时的查询,已经取消了按照“LOC”列进行排序。
3.删除第n行到当前行
删除第n行到当前行的语法结构如下:
DEL n *
【实例2-10】演示删除第n行到当前行命令的使用方法。
要求首先查询DEPT表的所有数据,并排序,然后删除第2行到当前行,并验证删除后的语句。执行过程见图2.16。
图2.16 删除第2行到当前行
【代码解析】
首先利用标准查询列出了DEPT表中的数据,并按照“LOC”列进行排序;然后删除第2行到当前行(“ORDER BY LOC”);最后利用“/”执行,读者可以发现,此时的查询不能正常执行,提示没有FROM关键字。也就是说,删除命令执行成功,已经删除了第2行到第4行。
除了以上介绍的删除命令外,还有其他的几种使用方式,这里不再一一举例说明,感兴趣的读者可以参考Oracle官方网站。
2.3.6 SQL*Plus清除缓冲区命令
SQL*Plus可以利用清除命令清除缓冲区中的程序,具体语法如下:
CLEAR BUFFER
【实例2-11】演示如何清除缓冲区内容。
要求首先查询DEPT表的所有内容,然后清除缓冲区内容,最后进行验证。具体的执行过程见图2.17。
图2.17 清除缓冲区命令
当执行“CLEAR BUFFER”命令时,会提示“buffer已清除”,此时再利用“/”执行命令,会提示缓冲区中没有程序。
2.3.7 SQL*Plus显示缓冲区内容命令
利用显示缓冲区内容命令可以更好地查看缓冲区中的命令,并对其进行操作。显示缓冲区命令和删除命令类似,同样有着多种组合,这里只介绍其中的几种,更多的命令读者可以参考Oracle官方网站。
1.显示缓冲区中所有的命令行
显示所有行的命令语法如下:
LIST
【实例2-12】演示如何显示缓冲区中的所有命令行。
要求首先清除缓冲区中的所有内容,然后查询DEPT表的数据,再利用显示缓冲区内容命令查看缓冲区内程序。具体操作步骤见图2.18。
LIST命令可以使用缩写“LIS”、“LI”、“L”,在后面介绍的有关LIST命令也可以使用缩写,感兴趣的读者可以自己试验。
2.显示缓冲区第n行
在LIST命令的基础上添加参数,可以组成其他命令。显示缓冲区第n行的语法结构如下:
LIST n
【实例2-13】演示如何显示缓冲区第n行。
要求首先清除缓冲区中的所有内容,然后查询DEPT表的数据,最后利用显示缓冲区内容命令查看第2行程序。具体操作步骤见图2.19。
图2.18 显示缓冲区中的所有命令行
图2.19 显示缓冲区第2行程序
3.显示缓冲区第n行到最后一行内容
利用LIST命令可以显示缓冲区第n行到最后一行的程序,语法结构如下:
LIST n last
【实例2-14】演示如何使用显示第n行到最后一行的内容命令。
要求首先清除缓冲区中的所有内容,然后查询DEPT表的数据,最后利用显示缓冲区内容命令查看第2行到最后一行的内容。具体操作步骤见图2.20。
图2.20 显示缓冲区第2行到最后一行的内容
2.3.8 SQL*Plus保存命令
开发人员在使用SQL*Plus操作数据库时,有保存命令的需求,通常会有2种方式来保存缓冲区的命令,下面就分别进行说明。
1.利用记事本保存
SQL*Plus可以调用记事本,然后把缓冲区的内容自动放入记事本中,利用记事本把其中的内容保存下来。具体语法如下:
ED
在SQL*Plus中输入该命令后,会提示“已写入file afiedt.buf”,并弹出记事本窗口,如图2.21所示,此时可以利用记事本的【文件】→【另存为】命令来保存该文件。
图2.21 SQL*Plus调用记事本
“ED”这个命令实际上是“EDIT”的缩写形式。
2.利用SAVE命令保存
SQL*Plus执行SAVE命令,可以把当前缓冲区的内容作为一个文件保存下来,具体语法如下:
SAVE file
【语法说明】
◆ file指要保存文件的路径和名称。
【实例2-15】验证如何利用SAVE命令保存缓冲区的内容。
要求首先清除缓冲区的内容,然后查询DEPT表的数据,最后利用SAVE命令保存当前缓冲区的内容。操作步骤见图2.22。当执行SAVE命令后,会在D盘下创建一个名为“sqltest.txt”的文件,打开后可以查看缓冲区的内容。
图2.22 SQL*Plus保存缓冲区的内容
2.3.9 查询结果的格式化
在使用SQL*Plus进行数据查询时,会遇到格式不完美的情况,这会影响到开发人员的阅读效果。例如,一个表有很多字段,当不对查询结果进行格式化时,会出现列自动换行的情况,致使人们无法很好地阅读查询结果。这种情况可以参考图2.23。
图2.23 未格式化的查询结果
这是EMP表的查询结果,可以看到,在查询结果列表中,列出现了自动换行的情况,导致了阅读不方便。这是一个很常见的问题,所以,在使用SQL*Plus时,在很多情况下都需要使用者对查询结果进行格式化。下面介绍几种常用的格式化输出结果的命令。
1.设置查询结果显示的行数
在SQL*Plus中查询数据时,默认把所有的数据都显示在整个页面上,这样就会出现页面显示数据比较乱的情况,开发人员看起来会很吃力。此时可以利用每页显示记录数的设置来解决这个问题,相关的语法如下:
SET PAGESIZE n
【语法说明】
◆ n代表每页显示的行数。在默认情况下,每页显示14行记录。需要注意的是,这里所说的每页并不是指一屏。
【实例2-16】验证设置每页显示的记录行数。
要求使用命令设置每页显示的记录行数,然后查询EMP表中的数据。操作步骤可以参考图2.24。
图2.24 每页显示18行的效果
该实例中页和页之间的间隔是一行(下面会对此进行讲解),并且每页显示18行,即图2.24中标出部分。
2.利用SET设置页和页之间的间隔
设置页和页之间的行数可以帮助读者区分两个页面,具体语法结构如下:
SET NEWPAGE n
【语法说明】
◆ n代码页和页之间的行数。
【实例2-17】对两页之间的行数进行设置。
要求利用命令设置每页显示的记录行数,然后设置两页之间的行数,最后查询EMP表中的数据。操作步骤可以参考图2.25。
图2.25中标出部分为一页,可以看出两页之间的间隔加大了,两页的数据也能看得更清楚了。
图2.25 两页之间的行数设置
利用下面的两个命令,可以显示每页的行数,也可以显示两页之间的行数。这两个命令的语法结构如下:
3.显示每页的行数以及两页之间的行数
SHOW PAGESIZE SHOW NEWPAGE
【实例2-18】演示显示每页行数以及两页间隔数。
这两个命令很简单,直接在SQL*Plus中运行即可,运行结果和步骤见图2.26。
图2.26 查看页面行数和两页间隔数
4.设置行字符数
设置行字符数命令是一个非常有用的命令,利用该命令可以使自动换行的列能够正常显示,查询结果更加容易理解。其语法结构如下:
SET LINESIZE n
【语法说明】
◆ n表示行要容纳的字符数。
与该命令配套的命令是显示行字符数据,其语法结构如下:
SHOW LINESIZE
【实例2-19】演示如何设置每行字符数,具体操作步骤见图2.27。
图2.27 正常显示所有列
这种用法很普遍,读者应该灵活运用。
5.列名的格式化
通常和设置行字符数命令配合使用的还有一个命令,就是列名的格式化命令,利用这两个命令,可以使得查询结果更加友好。有关列名格式化命令的语法结构如下:
COLUMN column_name FORMAT dataformat
【语法说明】
◆ column_name指格式化查询结果的列名。
◆ dataformat指格式化后的显示格式,其含义见表2-2。
表2-2 数据格式设置
【实例2-20】演示列名的格式化命令的用法。
要求对EMP表中的JOB列进行格式化,格式化后的列显示长度为12个字符。具体操作步骤见图2.28。
图2.28 JOB列格式化后的长度效果
当使用COLUMN命令设置列的格式之后,这个列的格式在当前窗口是一直保持的,除非重新设置该列的格式或者取消该列的格式。取消列格式命令的语法如下:
COLUMN column_name CLEAR
【实例2-21】演示取消已经格式化的列的格式。
要求把已经格式化的JOB字段重新还原成默认格式,具体步骤见图2.29。
图2.29 取消列的格式化效果
6.保存查询结果
很多时候开发人员会把查询结果保存到文件中,在SQL*Plus中读者可以使用“SPOOL”命令来完成这项操作。其语法结构如下:
SPOOL filename SPOOL OFF
【语法说明】
◆ filename表示查询结果要保存的文件路径和名称,后缀名可以是“txt”或“sql”,根据实际需求来定。当指定的文件不存在时,会自动创建;当文件存在时,内容将被覆盖。
◆ OFF表示该命令的结束,当完成查询时,调用该命令,表示查询结果输出完成,清空缓存。
【实例2-22】演示如何保存查询结果。
要求查询EMP表,并把查询结果存放到C盘的“ss.txt”中。操作步骤见图2.30。
图2.30 保存查询结果
此时,查看ss.txt文件,即可看到保存的查询结果,如图2.31所示。
图2.31 保存的查询结果
从ss.txt文件可以看到,它除了保存数据外,还保存了查询过程,这为数据的备份和再现提供了方便。
7.调用外部脚本文件
在SQL*Plus中可以调用外部脚本文件,也就是说,可以调用以前保存的执行脚本,而不用一句句地重新编写。调用外部脚本文件的语法结构如下:
@filename
【实例2-23】演示如何调用外部脚本文件。
要求调用外部脚本文件执行查询,其文件名为“sqlq.txt”,位于C盘根目录下,该文件内容见图2.32。
图2.32 外部脚本文件内容
下面在SQL*Plus中调用“sqlq.txt”文件,该文件可以是“*.sql”类型,调用过程见图2.33。
也可以利用START命令调用外部脚本文件,例如调用“sqlq.txt”文件,就可以写成“START C:\sqlq.txt”。
图2.33 调用外部脚本文件
2.3.10 注释的使用
注释在所有的高级编程语言中都存在,利用它可以快捷地提高代码可读性,是编程代码中不可缺少的一部分。PL/SQL中支持注释的使用,这在前面已经介绍过。下面介绍一个有关注释的命令,该命令可以在SQL*Plus中使用,其语法格式如下:
REMARK comment
【语法说明】
◆ comment表示注释语句。利用REMARK将在每行的开始使用,添加的注释只能是它所在的行,在一个完整的SQL语句中不能既包含REMARK又包含SQL脚本。
【实例2-24】演示如何利用REMARK注释语句。
要求在查询EMP表的数据前,对该查询添加注释,并把所有语句保存到“sqlq.txt”文件中。具体操作步骤见图2.34。
图2.34 REMARK命令的使用
以上验证了SQL*Plus中如何使用该命令。读者还可以把这些命令写到记事本中,如图2.35所示,然后在SQL*Plus中调用该记事本,查看执行效果,可以发现REMARK后面的语句被成功地注释掉了。
图2.35 记事本中的REMARK命令
“REM”命令是“REMARK”命令的缩写形式,实际上前面介绍的命令很多都具有缩写形式,读者可以自己进行试验。
2.3.11 在SQL*Plus中编辑语句
在前面的小节中重点介绍了SQL*Plus的一些常用命令,这些命令能够帮助初学者深入地了解SQL*Plus工具及其使用方法。由于SQL*Plus是一个非常重要的编辑PL/SQL的工具,所以,读者应认真理解它的常用命令,本小节将介绍如何在SQL*Plus中编写程序。
有关在SQL*Plus中编写PL/SQL语句前面已经接触到了,这里只用一个实例来演示如何在SQL*Plus中编写自定义函数。
【实例2-25】演示如何在SQL*Plus中编写自定义函数。
要求在SQL*Plus中编写一个自定义函数,并验证是否能正常使用。编写的具体脚本如下:
01 CREATE FUNCTION MYFUN(num1 in integer,num2 in integer) RETURN INTEGER 02 IS 03 BEGIN 04 RETURN(num1+num2); 05 END MYFUN; 06 /
【代码解析】
◆ 整个脚本代表的含义就是创建一个自定义函数,该函数会把传入的两个参数相加并返回。
◆ 有关该脚本的详细解释,读者可以参考后面的自定义函数部分。
【执行效果】
在SQL*Plus中逐行编写以上脚本并执行,可以创建一个函数;利用查询语句可以调用该自定义函数。整个过程见图2.36。
以上就是如何在SQL*Plus中编写PL/SQL的实例。刚开始时读者可能不适应以这种方式来编写脚本,所以初学者应多加练习。
图2.36 创建并调用自定义函数
DUAL表实际上是Oracle中实际存在的表,有1行1列,任何用户均可读取,通常在没有目标表的查询语句块中使用。
2.4 PL/SQL在SQL Developer中的使用
Oracle除了自带的SQL*Plus和企业管理器外,还提供了一个免费的可视化开发工具SQL Developer。相对来说,SQL Developer更容易上手,比较适合初学者,它是一种集成的开发环境,专门用于开发、测试、调试和优化Oracle PL/SQL存储程序单元。本节将介绍有关SQL Developer工具的启动和使用。
2.4.1 启动SQL Developer
要使用SQL Developer进行开发,首先必须登录。登录方法是,选择【开始】→【程序】→【Oracle-OraDb11g_home1】→【应用程序开发】→【SQL Developer】命令,弹出连接对话框,如图2.37所示。
在该对话框中,连接到java.exe的指定路径后,单击【OK】按钮,进入SQL Developer主窗口,如图2.38所示。
图2.37 登录SQL Developer
图2.38 SQL Developer主窗口
我们需要将所有关联的文件类型选中,然后单击“确定”按钮。
2.4.2 SQL Developer的布局
进入SQL Developer后,就可以利用可视化工具对数据库进行操作了。为了使读者更快地上手,下面对SQL Developer的布局进行简单介绍,如图2.39所示。
图2.39 SQL Developer的布局
图2.39中各标记部分的作用如下。
◆ 工具栏:具体的操作菜单,所有的操作都可以从这里找到。
◆ 对象列表:列出了可操作范围,并进行归类。例如:表、视图、函数、存储过程、触发器等。
◆ 窗口列表:所有打开的编辑页面都可以从这个地方浏览。
2.4.3 在SQL Developer中编写语句
使用SQL Developer工具编写PL/SQL语句块比使用SQL*Plus更加轻松,在该工具中编写语句就像在记事本中操作一样,可以随意地修改和添加注释,还增加了可视化操作,避免了处处利用脚本来完成。
由于SQL Developer不是本书学习PL/SQL的主要工具,所以,有关它的特性和使用在这里不做过多说明,在后面的章节中会适当地给读者演示该工具的部分功能。下面用一个实例来演示如何使用它执行PL/SQL语句块。
【实例2-26】演示如何使用SQL Developer执行PL/SQL语句块。
要求参照【实例2-25】,在SQL Developer中完成同样的操作。具体步骤如下:
step 1 登录SQL Developer。
step 2 在该工具中新建SQL窗口,具体步骤是单击【文件】→【新建】→【SQL文件】命令。
step 3 在新建的SQL窗口中,编写【实例2-25】的脚本,如图2.40所示。
step 4 执行该脚本。单击图2.39中的标记部分,如果没有错误提示,则表示该程序块成功执行。
图2.40 编写函数脚本
2.5 小结
本章主要讲解了SQL语言概述、PL/SQL基础,以及在SQL*Plus和SQL Developer中使用PL/SQL语言的方法。其中,在SQL语言概述部分讲解了SQL语言的发展、功能、执行形式、语句结构;在PL/SQL基础中讲解了PL/SQL的结构、主要组成及基本规范。读者通过本章的学习,完全可以认识PL/SQL是什么,在什么地方用。