2.2.2 PL/SQL结构_Oracle PL/SQL宝典-QQ阅读男生都市网

书名：Oracle PL/SQL宝典
作者名：赵越编著
本章字数：1289字
更新时间：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是什么，在什么地方用。

第2章 认识 PL/SQL

2.1 SQL语言概述

2.1.1 SQL语言的发展

2.1.2 SQL语言的功能

2.1.3 SQL语言的执行方式

2.1.4 SQL语句结构

2.1.5 SQL环境

2.1.6 SQL DBMS的客户/服务器模型

2.2 什么是PL/SQL

2.2.1 认识PL/SQL

2.2.2 PL/SQL结构

2.2.3 PL/SQL的主要组成部分

2.2.4 PL/SQL基本规范

2.3 PL/SQL在SQL＊Plus中的使用

2.3.1 启动SQL＊Plus

2.3.2 SQL＊Plus追加文本命令

2.3.3 SQL＊Plus增加文本命令

2.3.4 SQL＊Plus替换文本命令

2.3.5 SQL＊Plus删除命令

2.3.6 SQL＊Plus清除缓冲区命令

2.3.7 SQL＊Plus显示缓冲区内容命令

2.3.8 SQL＊Plus保存命令

2.3.9 查询结果的格式化

2.3.10 注释的使用

2.3.11 在SQL＊Plus中编辑语句

2.4 PL/SQL在SQL Developer中的使用

2.4.1 启动SQL Developer

2.4.2 SQL Developer的布局

2.4.3 在SQL Developer中编写语句

2.5 小结

第2章认识 PL/SQL