2.4 执行必要的PL/SQL任务
Oracle PL/SQL程序设计（第5版）
让我们把焦点转移到当把SQL*Plus作为前端工具时，该如何创建、运行、删除以及其他的PL/SQL程序管理任务。不要期待这部分能够覆盖所有细节，后面的章节会更加深入细致地介绍这些主题，这里只是快速过一遍。
2.4.1 创建存储过程
要想创建一全新的PL/SQL存储程序，你需要使用SQL中的一个CREATE语句。比如，如果你想创建一个存储函数，这个函数能够对一个字符串中的单词个数计数，你就可以使用CREATE FUNCION语句：
CREATE FUNCTION wordcount (str IN VARCHAR2)
RETURN PLS_INTEGER
AS
declare local variables here
BEGIN
implement algorithm here
END;
/
只是一个简单的BEGIN-END块，要在SQL*Plus中运行这个语句还需要一个结尾的斜杠，这个斜杠自己占一行。
如果DBA已经把Oracle的CREATE PROCEDURE权限赋予给你了（这个权限也允许创建函数），这个语句会让Oracle编译并把这个存储过程保存到你的模式下，一但代码编译完成，你就会看以如下这样一个成功信息：
Function created.
如果在你的模式下已经有另一个数据库对象也叫wordcount，比如一个表或者一个包， CREATE FUNCTION这个命令就会失败，并给出错误消息ORA-0955：name is already used by an existing object。这也是Oracle提供了OR REPLACE选项的原因，而且99%的情况下你都会使用这个选项。
CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)
RETURN PLS_INTEGER
AS
same as before
使用OR REPLACE选项，能够避免先删除然后重建程序的方法所带来的副作用；换句话说，它能够保留你已经赋予其他用户或者角色的针对这个对象的权限。幸运的是，这个命令只会替换相同类型的对象，所以如果你想要创建的是一个函数，它不会自动删除一个叫做workcount的数据表。
和之前多次使用的匿名代码块一样，程序员通常会把这些语句保存到一个操作系统文件中。我为这个函数创建一个名叫wordcount.fun的文件，然后使用SQL*Plus的@命令来运行这个文件：
SQL> @wordcount.fun
Function created.
之前已经说过，SQL*Plus默认的时候并不回显脚本的内容。你可以开启SET ECHO ON，然后代码在屏幕上滚动，还带着数据库所分配的行号，这个选项在问题诊断时非常有用。让我们注释掉一个变量声明，这就给程序中引入了一个错误：
SQL> /* File on web: wordcount.fun */
SQL> SET ECHO ON
SQL> @wordcount.fun
SQL> CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)
2 RETURN PLS_INTEGER
3 AS
4 /* words PLS_INTEGER := 0; ***Commented out for intentional error*** */
5 len PLS_INTEGER := NVL(LENGTH(str),0);
6 inside_a_word BOOLEAN;
7 BEGIN
8 FOR i IN 1..len + 1
9 LOOP
10 IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len
11 THEN
12 IF inside_a_word
13 THEN
14 words := words + 1;
15 inside_a_word := FALSE;
16 END IF;
17 ELSE
18 inside_a_word := TRUE;
19 END IF;
20 END LOOP;
21 RETURN words;
22 END;
23 /
Warning: Function created with compilation errors.
SQL>
这个消息告诉我们，函数已经创建了，但却带有编译错误，因此这个函数还不能使用。我们已经成功地把函数的源代码保存到数据库中，现在我们需要对数据库给出的错误信息的细节进行梳理。查看错误消息的完整文本的最快捷方式就是用SQL*Plus的SHOW ERRORS命令，简写成SHO ERR：
SQL> SHO ERR Errors for FUNCTION WORDCOUNT:
LINE/COL ERROR
-------- ----------------------------------------------
14/13 PLS-00201: identifier 'WORDS' must be declared
14/13 PL/SQL: Statement ignored
21/4 PL/SQL: Statement ignored
21/11 PLS-00201: identifier 'WORDS' must be declared
编译器已经发现了变量的存在，并准确的报告出行号和列号。要想获得一个错误更详细的内容，你可以根据错误的标识符去文档Oracle’s Database Error Messages中查找——这里是PLS-00201。
其实在后台，SHOW ERRORS命令也仅仅是查询Oracle数据字典的USER_ERRORS视图。你当然可以自己查询这个视图，不过通常没必要这么做。
显示其他错误
很多Oracle程序员只知道这个SQLPlus命令的一种形式：*
SQL>SHOW ERRORS
他们错误地认为必须直接查看USER_ERRORS视图去看最近的编译错误。其实，可以在SHOW ERRORS后面加上一个对象类别和对象名称，这样就可以只显示和该对象相关的错误：
SQL>SHOW ERRORS category [schema.]object
比如，要查看workcount函数最近的编译错误，使用：
SQL>SHOW ERRORS FUNCTION wordcount
在解释输出时要小心：
No errors.
这个输出可能意味着下列三种情况之一：（1）对象编程成功；（2）提供的是错误的类别（比如给的是过程而不是函数）；（3）没有指定名字的对象。
这个命令能够支持的类别的完整列表随着Oracle版本变化而变化，不过下面这些是都包括的：
DIMENSION
FUNCTION
JAVA SOURCE
JAVA CLASS
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE
TYPE BODY
VIEW
习惯上，在每一个创建PL/SQL存储过程的CREATE语句后面都要加上一个SHOW ERRORS命令。因此一个用于SQL*Plus环境的，创建存储过程的“最佳实践”模板应该是这样的：
CREATE OR REPLACE program-type
AS
your cod
END;
/
SHOW ERRORS
（我不常在脚本中使用SET ECHO ON，只是在需要时才会在命令行中使用。）
如果你的程序包含的是编译器能够检测到的错误，CREATE命令仍然会让Oracle把程序保存到数据库中，只不过程序的状态是无效的。但如果拼错了CREATE语法，数据库就无法分辨你想做什么，因此也不会在数据库中保存代码。
2.4.2 执行存储过程
我们已经看到两种调用存储过程的方法了：封装在一个PL/SQL代码块中，或者使用SQL*Plus的EXECUTE命令。你也可以在存储过程中使用其他存储过程。比如，你可以在任何一处能够使用整数表达式的地方使用wordcount函数。下面代码简单演示了，我是如何使用一个较怪的输入（CHR(9)是一个，ASCII的“tab”字符）来测试wordcount函数的。
BEGIN
DBMS_OUTPUT.PUT_LINE('There are ' || wordcount(CHR(9)) || ' words in a tab');
END;
/
这个wordcount是表达式的一部分，而表达式整体又作为DBMS_OUTPUT.PUT_LINE的参数，wordcount表达式的返回值是一个整数。PL/SQL会自动把整数转换成字符串，然后和其他两个字符串拼接起来，最终结果是：
There are 0 words in a tab
在一个SQL语句里可以调用多个PL/SQL函数，下面是使用wordcount函数的一些例子：
在select子句中使用这个函数，以计算某一表列的词的数量；
SELECT isbn, wordcount(description) FROM books;
使用ANSI兼容的CALL语句，把函数的返回值绑定到一个SQL*Plus变量；然后显示结果：
VARIABLE words NUMBER
CALL wordcount('some text') INTO :words;
PRINT :words
和上一个例子相同，但是执行的是一个远程数据库的函数，指向这个远程数据库的数据库链接叫做test.newyork.ora.com：
CALL wordcount@test.newyork.ora.com('some text') INTO :words;
函数是属于bob用户，但是以其他有相应授权的用户登录后执行函数：
SELECT bob.wordcount(description) FROM books WHERE id = 10007;
2.4.3 显示存储过程
总有一个时刻，你会想得到你拥有的存储过程列表，或者想要查看保存在数据字典中的程序代码的最新版本。如果借助于一些基于GUI的导航助手，这是一个相当简单的任务，不过就算没有这种工具，通过写一些SQL语句从数据字典中取出这些信息也不是很难的工作。
比如，要想得到你的程序（或者表、索引等）的完整列表，你可以查询USER_OBJECTS这个视图，像这样：
SELECT * FROM USER_OBJECTS;
这个视图会显示出名称、类型、创建时间、最后一次编译时间、状态（有效或者无效），以及其他有用的信息。
如果你只是想知道一个PL/SQL程序的调用接口的概要信息，最简单的方法就是在SQL*Plus中使用DESCRIBE命令。
SQL> DESCRIBE wordcount
FUNCTION wordcount RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
----------------- ------------- ------ --------
STR VARCHAR2 IN
DESCRIBE命令同样适用于表、视图、对象类型、过程和包。要想看到存储过程的完整代码，可以查看USER_SOURCE或者TRIGGER_SOURCE视图（如何查看这些数据字典视图会在第20章中有更详细的说明）。
2.4.4 存储程序的授权和别名
对于刚刚创建出来的PL/SQL存储过程，正常情况下只有创建者或DBA能够执行它。要想授权给其他人执行存储过程，使用GRANT语句：
GRANT EXECUTE ON wordcount TO scott;
要想去掉这个权限，使用REVOKE命令：
REVOKE EXECUTE ON wordcount FROM scott;
你也可以把EXECUTE权限授予一个角色：
GRANT EXECUTE ON wordcount TO all_mis;
或者，如果需要，你也可以允许任何使用数据库的用户来运行程序：
GRANT EXECUTE ON wordcount TO PUBLIC;
假设你把权限授予了某个人比如Scott，然后又授予给Scott所拥有的某个角色，接着又授权给了PUBLIC，数据库会一直牢记这三次授权，直到这些授权被收回。但这三个中的任何一个都足够让Scott运行这个程序。如果您不想让Scott运行这个程序，你必须从Scott收回权限，再从PUBLIC收回权限，最后再从all_mis角色收回权限（或者从Scott收回这个角色）才真正可以。
要想查看你已经授予其他用户或者角色的权限列表，你可以查看USER_TAB_PRIVS_MDDE数据字典视图。不过PL/SQL的程序名字是保存在TABLE_NAME列中的，这点不是那么很直观：
SQL> SELECT table_name, grantee, privilege
2 FROM USER_TAB_PRIVS_MADE
3 WHERE table_name = 'WORDCOUNT';
TABLE_NAME GRANTEE PRIVILEGE
-------------------------------------------------------- -----------
WORDCOUNT PUBLIC EXECUTE
WORDCOUNT SCOTT EXECUTE
WORDCOUNT MIS_ALL EXECUTE
假设Scott已经有了wordcount的EXECUTE权限，他可能想为这个函数创建一个别名，这样一来他就不必在每次使用这个函数时还要加上模式名做前缀了：
SQL> CONNECT scott/tiger
Connected.
SQL> CREATE OR REPLACE SYNONYM wordcount FOR bob.wordcount;
现在Scott可以通过别名的方式来执行程序了：
IF wordcount(localvariable) > 100 THEN...
这是一个不错的方法，因为如果函数的属主发生了变化，需要修改的只有这个别名（而不需要对任何存储过程做改动）。
对过程、函数、包或者用户自定义类型都可以创建别名。为过程、函数或包创建的别名不仅可以隐藏模式信息，而且还可以隐藏数据库信息，你可以很容易地为远程程序创建别名，就像对本地程序那么做。不过，别名也只能隐藏起模式和数据库信息，你不能为包中的子程序创建别名。
删除一个别名也是很容易的：
DROP SYNONYM wordcount;
2.4.5 删除存储程序
如果你真的不再需要某个存储程序了，你可以通过SQL的DROP语句来删除它：
DROP FUNCTION wordcount;
包是有两个元素组成的（声明部分和包体），你可以一次性完整地删除整个包：
DROP PACKAGE pkgname;
或者你也可以只删除包体部分，而不会影响对应的声明部分：
DROP PACKAGE BODY pkgname;
如果你删除了一个存储过程，而其他程序又调用了该存储过程，那么这些调用程序就会被标识成无效。
2.4.6 隐藏存储过程的源代码
当你用上面展示的方法创建PL/SQL程序时，从数据字典中可以得到明文形式的源代码。任何DBA都可以查看甚至修改这些代码。要想保护商业秘密或者防止代码被篡改，你需要某种手段能够在代码发布前“弄乱”它。
Oracle提供了一个叫做wrap的命令行工具，这个工具可以把许多CREATE语句转换成由明文和十六进制符号组成的混合体。这个工具并不是加密，但确实向隐藏代码前进了一大步，下面是一个被封转后的文件的节选：
FUNCTION wordcount wrapped
0
abcd
abcd
...snip...
1WORDS:
10:
1LEN:
1NVL:
1LENGTH:
1INSIDE_A_WORD:
1BOOLEAN:
...snip...
a5 b 81 b0 a3 a0 1c 81
b0 91 51 a0 7e 51 a0 b4
2e 63 37 :4 a0 51 a5 b a5
b 7e 51 b4 2e :2 a0 7e b4
2e 52 10 :3 a0 7e 51 b4 2e
d :2 a0 d b7 19 3c b7 :2 a0
d b7 :2 19 3c b7 a0 47 :2 a0
如果你需要真正的加密功能——比如，你要发布的是类似于密码这样真正需要保护的信息——你就不能使用这个工具了1。‡
要了解wrap工具的更多内容，可以参见第20章。