《Oracle PL/SQL程序设计(第5版)》一一2.4 执行必要的PL/SQL任务-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

《Oracle PL/SQL程序设计(第5版)》一一2.4 执行必要的PL/SQL任务

简介:

本节书摘来自异步社区出版社《Oracle PL/SQL程序设计(第5版)》一书中的第2章,第2.4节,作者:【美】Steven Feuerstein , Bill Pribyl,更多章节内容可以访问云栖社区“异步社区”公众号查看。

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章。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章