pl/sql reference综述

pl/sql reference综述

plsql user's guide and reference 已经读完一个多月了，再使用相关的功能，发现很多都记不清了。有必要review一遍。

pl/sql是我们在进行程序开发及后期维护、管理等工作中的一把利器。其与sql联系紧密，除explain plan外绝大部分sql语句可以在pl/sql中直接使用，授权、建表等DDL操作无法直接调用，但可以借助动态语句解决。
通常来说，处于简单考虑，使用sql是我们的首选。对于需要批量操作、定时运行、结构复杂需要多步操作等问题，借助pl/sql则更好。实现功能时不能一味地追求难度大、通用性好，而应该结合应用，在设计开始前从功能、性能、必要性等方面考虑可以剔除很多不必要的需求。

pl/sql相对于sql来说，可以以包、存储过程等形式存储在服务器上，一次解析后再调用时可能不需要再解析；为了保证安全性，可以加密存储；批量数据处理方面，恰当的使用可以每次提取一批数据，而不是sql那样每次一条，效率较高；由于其特点是描述性语句和面向对象思想的结合，既简单有高效。

pl/sql结构分为声明、执行、异常处理三部分。

声明变量时，既可以使用sql中的大部分数据类型，也可以使用pl/sql特有的plsql_integer、binary_integer、reference types（ref cursor）等，还可以使用%type、%rowtype。声明时可以为变量指定默认值和非空约束。
变量的值可以是表达式或字符串。
定义type时，自定义几列、选取表的几列、其他的type都可以。

执行部分包括待处理的语句块，可以包含其他pl/sql块的调用（嵌套）。通常来说若事务失败，整个事务（上次提交后的操作）会被回滚。为了记录日志等操作，可以添加自治事务，避免与主处理块相互影响。
如果在执行部分有嵌套，需要注意变量的作用域和可见性。

在异常处理部分给出数据块处理中出现异常时的解决方法。既可以使用系统中定义好的异常，也可以使用在声明部分自定义的异常。

oracle中NULL值的使用应该多留意。当前未指定和NULL的含义是相同的。

前面提到，oracle的数据类型包含了sql的，还有很多特有数据类型。处于对本地化的支持，还有很多本地化的数据类型（如NCHAR、NVARCHAR）。
处理数据时要注意环境设置（字符集）对数据长度的影响，变量间比较或变量与字面值比较时要注意char、nchar等定长类型的末尾空格。语句中最好对变量进行显式的类型转换。
在不同版本间操作数据时，特别注意不同版本对数据类型的支持。

对于pl/sql的结构控制，主要是IF、loop、case、语句的使用，goto语句违背结构化设计思想，尽量避免。出于事务完整性考虑，相关联的操作统一提交，并且注意事务的规模不要太大，以免中间出现问题时难以恢复。

个人认为pl/sql的难点和重点主要体现在集合、对象、大数据量处理三个方面。从官方文档中对这两方面的介绍篇幅就可见一斑。
oracle有嵌套表（nested tables）、变长数据（varrarys）、索引表（associative arrays|Index-by tables）三种。
三种集合类型区别主要在于上下界、连续性、是否可以作为表列的数据类型使用。
利用集合的exists、count、limit等方法可以将查到的数据进行中间处理（，然后再插入目标表中）。
对于大数据量的表，用简单的pl/sql语句可能能够实现功能，但好的性能依赖于更少的步骤、集合的合理使用等。

大数据量处理的核心思想就是分批提取、分配提交。即使是数据量较少，开发人员也需要在可读性和性能上做出平衡。尽量减少大表的访问次数、减少数据的查询量（减少I/O）。
查询并插入大量数据时，先将数据插入游标中，在从游标中分批将数据插入目标表；
对大量数据进行修改时，？？？？？？

 

如果事先不知道变量的名称、数量等信息，需要执行时指定，可以采用动态语句。此外，DDL操作在pl/sql中不支持，需要放入动态语句中；为了减少代码量、使语句更灵活，也可以使用动态sql。但由于动态语句是在语句执行时才解析的，因此效率会受到影响。

动态语句中不但可以包含语句，还能包含语句块，例如：EXECUTE IMMEDIATE 'BEGIN dbms_output.put_line(''semicolons''); END;';

oracle的子过程包括存储过程、函数、嵌套子过程、递归子过程等。调用其他schema下的对象时，默认是该用户的权限。通过authid可以指定使用当前用户的权限。

当处理一系列彼此相关或者同类事物时，可以将存储过程、函数等放入包内，提高了数据的内聚性。同时将细节隐藏，只将部分需要调用的过程显示给用户，更加安全，性能也更好。
包分为包声明和包体两部分，包声明中应该包含所有用户需要调用对象的声明，否则无法调用。包体中的内容应该有序，编译时从上到下。有嵌套关系的子过程之间，被调用的应该放在前面，否则会由于找不到对象而报错。
经常使用的dbms_output、dbms_pipe、utl_file、utl_http、dbms_alert等需要掌握。

为了避免系统报错、在出错时方便查找原因，应该尽量捕获所有的异常、异常的提示信息满足应用的需要。异常处理部分是按顺序判断的，when others then的方式捕获异常放在最后。
系统内部定义的异常直接调用就可以，自定义异常需要在声明部分指定。
“exception_name exception；”，异常处理部分使用raise语句；
pragma exception_init(exception_name,-oracle_error_number);声明带错误编号的异常。
执行部分可以用自定义异常：raise_application_error(error_number, message[, {TRUE | FALSE}]);（错误号范围是-20000 .. -20999）

在嵌套语句块中，发生异常时将逐层向外扫描，如果最外层都没有捕获到异常，就报错。
异常应该也是两类：（个人观点）
一种是可以预见到或者说程序设计的一部分。比如判断表达式是否大于零，如果大于，则继续执行，否则向日志表中插入提示信息；
另一种是不可预见或者说不希望发生，但又无法避免的。比如动态语句中从游标获取数据，取出的数据为空，这时用no_data_found或others等进行补货并提示。

为了在异常发生后继续执行后续代码，可以将该部分内容放入子过程块，并在其异常处理部分中放入异常处理。这样，发生异常后只是该子过程块结束，主过程块继续执行。

还有一点需要注意：声明部分出现的异常无法补获，只能报错。

oralce是将pl/sql警告分为三类，即server、performance、informational
根据需要，将警告的参数设置为ENABLE：ALL|PERFORAMNCE\DISABLE:ALL等

nocopy选项主要是在子过程块调用时传递形参而非实际变量
管道化表函数主要用于数据仓库环境下的多事务处理
使用特性时需要注意其应用场景和限制条件。

随着面向对象设计的蔓延，oracle也在逐渐优化其object type的功能。
此部分目前没有实际的应用经验，因此不详述。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-752212/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-752212/