《专业级Oracle Database 12c安装、配置与维护》之在SQL*Plus中编写PL/SQL

        当编写PL/SQL程序时，可以用多种方式来运行。可以直接在SQL*Plus(或某些其他SQL环境，如SQL Developer)中运行，也可以将之存储在数据库中，然后从SQL环境或程序运行。当在数据库中存储程序时，该程序被称为存储程序(stored program)或存储对象(stored object)。这在本章稍后讲解。现在，让我们来讨论如何使用SQL*Plus编写程序。虽然是从SQL*Plus的环境内部说明这个例子，但这些程序也可以使用其他的SQL接口，如SQLDeveloper、TOAD或任何其他你喜欢的产品来运行。

首次编写一个程序时，可以在SQL*Plus中使用命令行创建并修改。操作步骤如下：

(1) 登录到SQL*Plus。根据环境，可能需要在命令行上发出sqlplus命令。注意，在Oracle Database 11c中，Windows图形版本的SQL*Plus已被弃用。因此，如果愿意，也可以使用SQL Developer执行这项工作。

(2) 通过命令行输入程序。

(3) 编写完每一行之后，按下Enter键进入到下一行。

进度检查答案 1. 以下条目中的任何4项都是可以接受的答案：Oracle表单、报表、数据仓库构建器、Oracle应用软件、Oracle门户、SQL*Plus、Oracle网格控制器、Oracle预编译器和Oracle应用服务器。 2. 在PL/SQL块中，可包含的三个部分是声明部分、执行部分和异常部分。 3. 在PL/SQL块中，唯一必需的部分是执行部分。 4. 用于存储每个变量的数据类型将是： A. number或number(8,2)。应该始终在数值数据类型中存储数值。可以指定精度，或者当不知道数据的确切性质时，干脆定义为number，但不指定精度。 B. boolean。boolean(布尔)数据类型用于存储true(真)和false(假)信息。 C. date。date数据类型存储日期和时间信息。 D. varchar2(10)。字符值应该存储在varchar2数据类型中。对于存储该数据来说，这种方式更有效，但有32767个字节的限制。如果需要存储的数据超过32KB字节，那么应该使用long数据类型，该类型允许存储多达2GB的数据。 E. number或number(2)。当不需要小数位时(整数)，这些都是数值的首选数据类型(number不指定精度，见选项A的解释，number(*,0)指定不包含小数部分)。

 

(4) 输入完程序后，记得要以“/”字符终止它。这告诉Oracle运行刚刚输入完的程序。

(5) 监控Oracle，查看程序是否运行成功。如果程序没有错误(语法错误)地运行，将显示消息“PL/SQL过程已成功完成”。如果显示任何其他东西，就表明发生了错误。

(6) 要查看程序产生的错误(假定已经创建一个存储对象，如我们将在本章后面讨论的)，输入show errors。SQL*Plus将显示在程序当前运行过程中遇到的错误。

(7) 如果收到错误，就需要编辑程序。如果SQL*Plus和Oracle环境设置正确，可以简单地在命令行上输入edit，将程序加载到编辑器中，这样就可以修改了。一旦退出编辑器，程序会重新加载到SQL缓冲区，并可以再次运行。

现在，让我们进行演示，说明如何构建PL/SQL程序，并得到部分输出结果。

专家问答

问：怎样才能从PL/SQL程序得到反馈/输出？

答：Oracle为此提供名为DBMS_OUTPUT的内置包。Oracle提供了许多包，它们为用户提供额外的功能，比如将数据输出到屏幕。通过将DBMS_OUTPUT.PUT_LINE命令放置到程序，Oracle的PL/SQL可以提供信息给用户，如本章所举的例子所示。

要看到此信息，必须在执行PL/SQL例程之前，启用屏幕输出，这可通过在SQL>提示符下输入SET SERVEROUTPUT ON来完成。

项目1  创建PL/SQL程序

这是你将创建的第一个PL/SQL程序。这个概念很简单，首先声明一些变量，并给变量赋值，然后使用SQL*Plus将数据输出到屏幕上。

步骤

(1) 登录到SQL*Plus。

(2) 在SQL>提示符下，输入SERVEROUTPUT命令：SET SERVEROUTPUT ON ;

(3) 输入下面的PL/SQL程序：

<span style="font-family:Microsoft YaHei;font-size:14px;">Declare
L_start_date date;
Begin
L_start_date := '29-SEP-2005';
dbms_output.putline (l_start_date); --show date
End;
/</span>

(4) 现在应该在屏幕上看到下面的输出：

<span style="font-family:Microsoft YaHei;font-size:14px;">SQL> /
29-SEP-05
PL/SQL procedure successfully completed.</span>

(5) 现在，你已经完成了第一个PL/SQL程序。

(6) 将时间添加到该程序中，并添加使用其他日期格式的行或执行一些日期加法运算。例如，你可能希望添加下面的代码，并查看提供的结果：

<span style="font-family:Microsoft YaHei;font-size:14px;">L_start_date := to_date('14-JAN-2063', 'DD-MON-YYYY');
dbms_output.put_line(l_start_date);
L_start_date :=to_date('09-JUN-91:13:01', 'DD-MON-YY:HH24:MI');
dbms_output.put_line (l_start_date);</span>

接下来，我们需要讨论如何在PL/SQL程序中包含数据库中的数据。

项目总结

本项目初步介绍如何构建PL/SQL程序。现在，我们已经了解了如何创建、运行程序，然后再重新运行，并查看输出结果。虽然这是一个简单的例子，但它展示了所有PL/SQL程序的基本情况。

1.1  PL/SQL程序中的SQL

到现在为止，你已经见到了很多结构。你应该知道，PL/SQL程序必须有BEGIN和END语句。可以有变量、循环或逻辑控制，但现在需要将数据库中真正的数据放入到程序中。PL/SQL的强大功能在于与SQL的紧密集成。你可能需要一些信息，以便可以创建报表、更新数据、创建新的数据、删除旧数据，或执行几乎你能想到的任何其他功能。对于你，了解怎么才能将数据集成到PL/SQL代码中是非常重要的。如果没有数据，PL/SQL只是PL。

1.2  PL/SQL游标

如何让数据进入程序？很简单——从数据库中提取(select)它们。这是在程序中使用SQL的最简单方法。因此，插入类似下面的行就能够访问数据库中的数据：

<span style="font-family:Microsoft YaHei;font-size:14px;">select prod_name
into v_prod_name
from products</span>

让我们来解析这条语句，看看这对程序意味着什么。当查看这条select语句时，可以看到，它看起来非常类似于标准的select语句。然而，你应该也已经注意到语句中的“INTO”这个词。你可能想知道它是干什么用的。这就是使用select语句把值放到变量中的办法。

下面的例子演示了如何在PL/SQL程序中包含SQL语句：

<span style="font-family:Microsoft YaHei;font-size:14px;">1 declare
2           v­­_prod_name varchar2(80);
3 begin
4                       select prod_name
5                       into v_prod_name
6                       from products
7                       where rownum = 1;
8           dbms_output.put_line(v_prod_name);
9* end;
10 /</span>

除了从数据库中选择一个值外，你还有能力选择多个值，并添加想要包含的条件。要做到这一点，请使用以下游标格式：

<span style="font-family:Microsoft YaHei;font-size:14px;">select prod_name, prod_list_price, prod_min_price from products
where rownum < 10</span>

在程序中，可以使用任何SQL语句。可以是select、insert、update或delete语句。所有这些语句都受到支持。当像在前面的例子中那样使用select语句时，它被称为隐式游标(implicit cursor)。隐式游标是包含在程序的可执行部分，并有into语句(在select语句的情况下)的SQL语句。使用隐式游标，Oracle会为你处理一切，但这样做有如下代价：程序的运行速度会变慢。你要明白自己在做什么，因为虽然可能不是执行select语句的最佳方式，但当想运行insert、update或delete语句时，必须使用隐式游标。所以，让我们继续往下看，看看有没有更好的办法来做同样的事情。我们将重新审视前面的例子，比较这两个办法。

更好的方法是创建显式游标。显式游标(explicit cursor)是在程序的DECLARE部分声明的select语句。这样做使得Oracle会在运行程序之前，为你准备SQL语句。

有时，你可能不知道要运行的确切SQL语句是什么。为了用更动态的解决方案帮助你，以允许在运行时定义SQL语句，PL/SQL在程序中同时支持动态和静态处理。动态SQL使你可以在运行时动态构建SQL语句，而静态SQL语句是预先知道的。可以通过使用动态SQL创建更通用灵活的应用程序，因为SQL语句的完整文本在编译时可能是未知的。

可以使用EXECUTEIMMEDIATE语句来处理大部分动态的SQL语句。动态SQL对于执行SQL语句特别有用。下面的示例演示如何使用动态SQL来使得程序更加灵活地应对变化：

<span style="font-family:Microsoft YaHei;font-size:14px;">DECLARE
sql_stmt                                                            VARCHAR2(200); -- 保存SQL语句的变量
column_name                                                  VARCHAR2(30);           -- 用于列名的变量
dept_id                                                             NUMBER(4);
dept_name                                                       VARCHAR2(30);
mgr_id                                                                  NUMBER(6);
loc_id                                                                  NUMBER(4);
BEGIN</span>

-- 创建一条用EXECUTEIMMEDIATE执行的SQL语句(sql_stmt)

-- 该语句使用绑定变量INSERT(插入)一行到departments表

-- 请注意，引号'...'内没有分号(;)

<span style="font-family:Microsoft YaHei;font-size:14px;">sql_stmt := 'INSERT INTO departmentsVALUES (:dptid, :dptname,:mgrid, :locid)';
dept_id:= 46;
dept_name:= 'Special Projects';
mgr_id:= 200;
loc_id:= 1700;</span>

-- 使用在USING子句中的变量值作为绑定变量执行sql_stmt

EXECUTEIMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id,loc_id;

-- 使用EXECUTEIMMEDIATE删除以前插入的行

-- 代替列名，并使用绑定变量

<span style="font-family:Microsoft YaHei;font-size:14px;">column_name:= 'DEPARTMENT_ID';
EXECUTEIMMEDIATE 'DELETE FROM departments WHERE ' || column_name ||
' = :num'
USINGdept_id;
END;</span>

在Oracle 12c中，可以用作绑定变量的类型已经被扩展：布尔变量和嵌套表现在都可以用作动态SQL中的绑定变量。最终，这进一步扩展了可以通过动态SQL支持的灵活性和功能。

这使得该程序能非常有效地利用内存。让我们来看看使用显式游标的程序是什么样子：

<span style="font-family:Microsoft YaHei;font-size:14px;">1           declare
2                   v_prod_name varchar2(80);
3                   cursor get_data is
4                   selectprod_name
5                   from products;
6       begin
7                   open get_data;
8               fetch get_data into v_prod_name;
9                   dbms_output.put_line(v_prod_name);
10              close
11* end;</span>

在上面的代码中，我们将最初的例子转换为使用显式游标的程序。请注意，select语句现在包含在声明部分。另外，不再有into子句。这个功能被移到执行部分，被用在FETCH命令中。

专家问答

问：为什么要在我们的select语句中包含rownum = 1？

答：我们之所以在使用隐式游标时包含rownum = 1，是因为在这个特定的例子中，select语句将返回多行。这将导致终止本PL/SQL块处理的Oracle错误。为了避免这种情况发生，我们需要包含rownum =1条件。

还要注意的是，我们引入了三个新的PL/SQL命令：OPEN、FETCH和CLOSE。有了这三个简单的命令，就可以使用SQL游标从数据库中获取数据。OPEN命令告诉Oracle保留select语句将需要使用的内存。同时，FETCH命令从结果集的第一行提取数据，而CLOSE命令关闭刚才打开的游标，并将内存交还给Oracle用作其他用途。

注意：

当用游标完成工作时，要始终记得显式地关闭它们。如果不这样做，就可能遇到内存问题，或者可能得到不希望的结果。

1.3  游标FOR循环

可以通过将游标与循环组合来更好地感觉游标的威力。游标FOR循环是select游标与FOR循环(我们在会下一节更详细地介绍FOR循环)结合的结果。这使你可以从数据库中检索多行，如果结果集有多行的话。另外也简化了编程，你不必担心打开或关闭游标，Oracle处理循环中所有的事。让我们来看看游标FOR循环的一个例子。最重要的行已设置为粗体：

<span style="font-family:Microsoft YaHei;font-size:14px;">SQL> set serveroutput on
SQL>    declare
2             v_prod_name varchar2(80);
3             cursorcur_get_data is
4             selectprod_name
5             fromproducts;
6             begin
7             for i in cur_get_data
8             LOOP
9                                             dbms_output.put_line(i.prod_name);
10             END LOOP;
11             end;
12 /
5MP Telephoto DigitalCamera
17" LCDw/built-in HDTV Tuner
Envoy 256MB - 40GB
Y Box
Mini DV Camcorder with3.5" Swivel LCD
Envoy Ambassador
Laptop carrying case
Home Theatre Package with DVD-Audio/Video Play ...</span>

注意：

要在FOR循环中引用列，使用循环变量的名称并将它与游标声明中定义的列名连接。因此，结果将是一个命名为cursor_name.fieldname的变量(在例子中，我们使用变量i.prod_name来引用列)。

在实践中，游标FOR循环是PL/SQL真正强大的地方。它使你能够在select语句的结果集中轻松移动，并成功执行你需要的逻辑和操作。

我们刚刚触及从PL/SQL获取信息的初步知识。你还需要熟悉调试PL/SQL程序，这可能是一项非常复杂的任务。经验告诉我们，对于发现的错误，永远不能掉以轻心或忽略。使用简单的工具，如DBMS_OUTPUT，就有办法跟踪程序的进展。

注意：

Oracle提供了几个包来分析PL/SQL程序。第一个包是DBMS_PROFILER。这个包分析程序正在怎么运行，并且收集每一行的执行时间的统计信息。这可以帮助你发现运行速度很慢或低效的代码。当需要访问程序更高级的统计信息时，请花时间去研究这个包，以及如何将它集成到PL/SQL代码中。因为本书是初学者的指南，当需要某个重要特性的功能时，我们只引导你找到它。在Oracle 12c中，Oracle还推出了另一项功能——  UTL_CALL_STACK。这个包提供了一个接口，用于让PL/SQL程序员获取有关当前正在执行的程序的信息，包括来自动态和词法栈的子程序名以及堆栈的深度。不同的函数返回给定动态深度的子程序名、单元名称、所有者名称、版本名称和行号。更多函数返回错误栈信息。这些信息可以被用来创建更准确的错误日志和应用程序的执行跟踪信息。

我们已经看到了如何编写和调试程序，现在可以编写更为复杂的程序了。

《专业级Oracle Database 12c安装、配置与维护》试读电子书，免费提供，有需要的留下邮箱，一有空即发送给大家。 别忘啦顶哦！

购书地址：

京东：http://item.jd.com/11550019.html

当当：http://product.dangdang.com/23572366.html 

微信：qinghuashuyou  

更多最新图书请点击查看哦（即日起-12月31日：关注@qinghuashuyou 发送：关注技术方向，即有机会参与图书抽奖，每周抽取十个幸运读者）