|
|
51CTO旗下网站
|
|
移动端

2.4.1 创建存储程序

《Oracle PL/SQL程序设计(第6版)(上下册)》第2章创建和运行PL/SQL代码,本章将展示如何在一个最基本的层面上(使用SQL*Plus)完成上述任务,同时对这些细小差别进行大致的讲述。本节为大家介绍创建存储程序。

作者:方鑫 译来源:人民邮电出版社|2017-09-06 20:06

【51CTO技术沙龙】10月27日,让我们共同探索AI场景化应用实现之道

2.4 执行基本的PL/SQL任务

现在,我们把注意力集中,当SQL*PLUS被当作前端工具时,如何用它管理PL/SQL程序,如创建、执行、删除等。这里不会覆盖具体细节,后面的章节会有更细致的讲解,这里只是简单进行概述。

2.4.1 创建存储程序

我们可以使用SQL的CREATE语句创建一个新的存储程序。例如,如果我们要创建一个存储函数,统计一个字符串里的单词个数,我们就可以使用CREATE FUNCTION语句:

  1. CREATE FUNCTION wordcount (str IN VARCHAR2)  
  2. RETURN PLS_INTEGER  
  3. AS  
  4. declare local variables here  
  5. BEGIN  
  6. implement algorithm here  
  7. END;  

上面只是一个简单的BEGIN-END程序块,如果要从SQL*Plus中运行它的话,需要在结尾加一斜杠,这个斜杠独占一行。

假定DBA已经赋予了我们CREATE PROCEDURE的权限(这个权限也允许创建函数),那么这个语句会触发Oracle对之进行编译并把这个存储函数保存到我们的schema中。当代码编译完成时,我们能看到如下的提示成功信息:

  1. Function created. 

如果另一个数据对象,例如表或程序包,也叫“wordcount”,已经存在于我们的schema下,那么CREATE FUNCTION会失败,伴随的错误信息是ORA-00955: name is already used by an existing object。这就是为什么Oracle提供了OR REPLACE选项的原因,我们在99%的时间里都会用到它:

  1. CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)  
  2.    RETURN PLS_INTEGER  
  3. AS same as before 

使用OR REPLACE选项,可以避免先删除、再创建对象可能导致的副作用,换句话说,它保留了原来其他用户或角色对这个对象的权限。幸运的是,它仅仅能替代同类别的对象,所以如果我们想要创建一个叫“wordcount”的函数,它不会删除掉一个叫“wordcount”的表。

和之前多次使用的匿名块一样,程序员们通常把这些语句保存在操作系统的文件中。我们可以为上面的代码创建一个名为wordcount.fun的文件,然后通过SQL*Plus的“@”命令来调用它:

  1. SQL> @wordcount.fun  
  2. Function created. 

之前提到过,SQL*Plus默认不回显脚本内容。我们可以通过SET ECHO ON命令,让脚本源码在屏幕上显示,显示还包含了数据库分配的行序号;这个设置有利于我们进行程序调试。

我们现在注释掉一个变量声明(第4行),这样就使程序编译时发生错误:

  1. /* File on web: wordcount.fun */  
  2. SQL> SET ECHO ON  
  3. SQL> @wordcount.fun  
  4. SQL> CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)  
  5. 2 RETURN PLS_INTEGER  
  6. 3 AS  
  7. 4 /* words PLS_INTEGER :0; ***Commented out for intentional error*** */  
  8. 5 len PLS_INTEGER :NVL(LENGTH(str),0);  
  9. 6 inside_a_word BOOLEAN;  
  10. 7 BEGIN  
  11. 8 FOR i IN 1..len + 1  
  12. 9 LOOP  
  13. 10 IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len  
  14. 11 THEN  
  15. 12 IF inside_a_word  
  16. 13 THEN  
  17. 14 words :words + 1;  
  18. 15 inside_a_word :FALSE;  
  19. 16 END IF;  
  20. 17 ELSE  
  21. 18 inside_a_word :TRUE;  
  22. 19 END IF;  
  23. 20 END LOOP;  
  24. 21 RETURN words;  
  25. 22 END;  
  26. 23 /  
  27. Warning: Function created with compilation errors. 

此信息告诉我们,函数已经创建了,但有一个编译错误,所以函数处于不可用状态。我们已经成功地把源码存入了数据库,现在我们需要对数据库引发的错误进行细致梳理。查看完整错误信息最方便的方法是使用SQL*Plus的SHOW ERRORS命令,简化形式是SHO ERR:

  1. SQL> SHO ERR  
  2. Errors for FUNCTION WORDCOUNT:  
  3. LINE/COL ERROR  
  4. -------- ----------------------------------------------  
  5. 14/13 PLS-00201: identifier 'WORDS' must be declared  
  6. 14/13 PL/SQL: Statement ignored  
  7. 21/4 PL/SQL: Statement ignored  
  8. 21/11 PLS-00201: identifier 'WORDS' must be declared 

编译器已经探到了变量的存在,指出了错误所在的具体行数。如果想要更仔细地了解任一Oracle服务器端的报错,我们可以在“Oracle’s Database Error Messages”文档中,用错误标识符(这里是PLS-00201)进行查找。

其实在后端,SHOW ERRORS的命令就是查询Oracle数据字典中的USER_ERRORS视图。我们也可以自己去查询这个视图,不过通常没必要那么做(见下面的备注框)。

显示其他错误

许多Oracle程序员都只知道一种显示错误的SQL*Plus命令:

  1. SQL> SHOW ERRORS 

他们错误地以为,若要查看最新的编译错误信息,就只能直接查询USER-ERRORS视图。实际上,我们可以在SHOW ERRORS命令后面加上对象类别和对象名称,它就可以显示此对象的最新编译错误信息了:

  1. SQL> SHOW ERRORS category [schema.]object 

例如,若要查看“wordcount”函数的最新错误信息,我们可以这样进行指定:

  1. SQL> SHOW ERRORS FUNCTION wordcount 

在对输出进行解释时要注意:

No errors.这个返回信息包含三种可能的含义:(1)对象编译成功;(2)提供的是错误的类别(例如,给的类别是过程而不是函数);(3)没有对应此名称的对象。

这个命令支持的完整的对象类别列表根据Oracle版本的变化而不同,但以下类别是各版本均支持的:

  1. DIMENSION  
  2. FUNCTION  
  3. JAVA SOURCE  
  4. JAVA CLASS  
  5. PACKAGE  
  6. PACKAGE BODY  
  7. PROCEDURE  
  8. TRIGGER  
  9. TYPE  
  10. TYPE BODY  
  11. VIEW 

存储PL/SQL程序的CREATE语句后面都会加上一个SHOW ERROR命令,所以在SQL*Plus环境中,创建存储过程的“最佳实践”模板如下:

  1. CREATE OR REPLACE program-type  
  2. AS  
  3. your code  
  4. END;  
  5. /  
  6. SHOW ERRORS 

(通常我们不应把SET ECHO ON放进脚本,而是在需要的时候手动输入)。

当编译器探查我们的程序包含的错误时,CREATE仍然会把这个程序存储到数据库中,但会把其状态置为“无效”。而当我们的CREATE语句出现语法错误时,数据库无法分辨我们的真实意图,就不会存储程序了。

喜欢的朋友可以添加我们的微信账号:

【责任编辑:book TEL:(010)68476606】

回书目   上一节   下一节
点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

网管员必读——网络组建

本书以一个模拟局域网组建为思路,介绍了与局域网组建各主要方面相关的知识及组建、配置方法。本书所介绍的内容主要包括:局域网组建规划、...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊