PLSQL语法小结

文章部分内容收集整理自ITPUB论坛：

1.PLSQL集合：

INDEX_BY表
1).使用的时候需要先赋值后读取，否则会出现异常：ORA-01403: no data found。
2).这种数组不需要事先指定上限，下标可以不连续，可以是0或负数。
3).只存在于PL/SQL中，不能直接存储在数据库表中。
4).元素可以是任意类型。
5).元素个数没有限制。
NESTED TABLE：
1).必须进行初期化，否则会出现异常：ORA-06531: Reference to uninitialized collection。
2).创建时下标从1开始，连续，但是当元素被删除后可以是稀疏的。当下标超出允许范围时会出现异常：ORA-06532: Subscript outside of limit。
3).可以直接存储在数据库表中。可以使用SQL进行操作。存储在数据库中的嵌套表不能用PL/SQL直接操作，而只能用SQL操作。
4).元素类型不可以是BOOLEAN、NCHAR、NCLOB、NVARCHAR2或REF CURSOR。如果存在NOT NULL，那么嵌套表的元素不能是NULL。
5).元素个数没有限制。
ASSOCIATED ARRAY:
1).必须进行初期化，否则会出现异常：ORA-06531: Reference to uninitialized collection
2).下标从1开始，连续。当下标超出允许范围时会出现异常：ORA-06532: Subscript outside of limit
3).可以直接存储在数据库表中
4).元素类型不可以是BOOLEAN、NCHAR、NCLOB、NVARCHAR2或REF CURSOR。
5).元素个数有限（可变数组的大小也可以使用EXTEND方法来增加，不能够被扩展超过为可变数组类型声明的极限大小。）。
集合的方法：
COUNT 返回集合中元素的个数。
DELETE 删除集合中所有元素。
DELETE(x) 删除元素下标为x的元素，如果x为null,则集合保持不变 对VARRAY非法。
DELETE(x,y) 删除元素下标从X到Y的元素，如果X>Y集合保持不变 对VARRAY非法。
EXIST(x) 如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE。
EXTEND 在集合末尾添加一个元素 对Index_by非法。
EXTEND(x) 在集合末尾添加x个元素 对Index_by非法。
EXTEND(x,n) 在集合末尾添加元素n的x个副本 对Index_by非法。
FIRST 返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。
LAST 返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT。
LIMIT 返回VARRY集合的最大的元素个数，对于嵌套表和Index_by集合无用。
NEXT(x) 返回在元素x之后及紧挨着它的元素的值，如果该元素是最后一个元素，则返回null。
PRIOR(x) 返回集合中在元素x之前紧挨着它的元素的值，如果该元素是第一个元素，则返回null。
TRIM 从集合末端开始删除一个元素 对index_by不合法。
TRIM(x) 从集合末端开始删除x个元素 对index_by不合法，如果N大于COUNT，则抛出异常。
备考：
DELETE一个元素之后，COUNT会相应减少，当你对已删除的下标进行赋值，COUNT则会相应增加。
TRIM将会从末尾删除数组元素，而且不可以对被删除的下标再次赋值，必须EXTEND之后才可以。


2.FX和FM：
FX是精确匹配模式，使用FX格式后:
1).字符及对应的格式必须严格一一对应，甚至连分隔符都要相符。
2).不允许有多余的空格。
3).数值参与格式需要完全对应(或通过FM参数去掉前置0)
FM是填充模式
1).如果是字符格式化，指定FM参数后将仅返回指定属性实际所占长度(不再以空格填充)。
2).如果是数值格式化，指定FM参数会自动舍弃被格式化元素的前置0。


3.游标的加锁机制：
如果用隐性游标循环，发生异常时锁会被释放。
如果用显性游标循环，发生异常时锁不会被释放。


4.LOG ERRORS：
当你决定使用DML的LOG ERRORS特性时，你必须先创建一个错误日志表。这个表可能包含DML表的所有列----但是仅限于那些支持的数据类型。CLOB不是一种被支持的类型。
因此DBMS_ERRLOG.CREATE_ERROR_LOG 将会失败："ORA-20069: Unsupported column type(s) found:" 意味着错误日志表未能被创建。
如果我试图在plch_employees表使用带LOG ERRORS的DML语句，ORACLE会试图去写入ERR$_PLCH_EMPLOYEES，但是无法找到这张表因此抛出异常：ORA-00942: table or view does not exist。
如果我告诉ORACLE跳过不支持的列，那么一切正常：
BEGIN
   DBMS_ERRLOG.create_error_log (
       dml_table_name => 'PLCH_EMPLOYEES'
     , skip_unsupported=>true);
END;
/


5：复合型数据类型：
对记录类型不能使用IS NOT NULL判断，否则会出现：PLS-00306: wrong number or types of arguments in call to 'IS NOT NULL'


6：TO_CHAR：
你不能在TO_CHAR(NUMBER)中使用TH格式，只能在TO_CHAR (datetime)中使用。


7.自治事务：
1).在匿名PL/SQL块中,只有顶级的匿名PL/SQL块可以被设为AT
2).如果AT试图访问被MT控制的资源,可能有deadlock发生.
3).Package 不能被声明为AT,只有package所拥有的function和procedure 才能声明为AT
4).AT程序必须以commit 或rollback结尾,否则会产生Oracle错误ORA-06519: active autonomous transaction
5).对自治事务来说，被修改过的行的当前状态是不可用的。反之，主事务能够检测到已经执行过的自治事务的结果。
假如主事务的隔离级别设成了SERIALIZABLE, 它就看不到自治事务的修改结果。
6).保存点无法在自治事务中回滚到父事务中的一个保存点，只能在内部使用保存点。


8.LIKE的通配符：
_匹配单个字符，%匹配任意个字符，*和?是忽悠人的。


9.包中初始化语句的执行顺序：
包头声明的初始值先于包体。


10.PL/SCOPE：
http://www.itpub.net/thread-1336860-1-1.html


11.PRAGMA：
PRAGMA用来指示编译器的所采取的行为
PRAGMA instruction_to_compiler;
PLSQL编译器，将在PLSQL的申明部分DECLARATION接受这样的指示，
PLSQL提供几种PRAGMA可供选项
AUTONOMOUS_TRANSACTION:通知PLSQL运行时环境，提交或者回滚任何数据库的变更，在当前块中，前提是不影响主程序或者外部事务的运行
EXCEPTION_INIT:通知编译器，让你申明的错误标识与一个特定的错误号码相对应，应当紧跟在申明的EXCEPTION后面
RESTRICT_REFERENCES:通知编译器一个包程序的纯正水平线
SERIALLY_REUSABLE:通知编译器


RESTRICT_REFERENCES PRAGMA
用法如下：
PRAGMA RESTRICT_REFERENCES ( subprogram_name, [RNDS, WNDS, RNPS, WNPS, TRUST])
subprogram_name: PL/SQL 函数的名字
RNDS: (Read No Database State) 表示该subprogram不会查询(query)数据库中的表。
WNDS: (Write No Database State) 表示该subprogram不会改变数据库中的表的数据。
RNPS: (Read No Package State) 不访问包中的变量
WNPS:(Write No Package State) 不改变包中的变量值
TRUST: 表示信任该subprogram不会违反前面的任何约束，一般用在PL/SQL调用外部函数，比如java代码。


12.%ROWTYPE：
%ROWTYPE是一种记录，如果你定义两个结构一模一样的记录类型，它们仍然不能够互换，会报类型不匹配的错误。


13.AUTHID CURRENT_USER：
一个定义为调用者权限（AUTHIDCURRENT_USER）的函数，如果它在一个VIEW或者触发器中被调用，这时是按照定义者的权限来运作的。


14.异常捕获：
如果异常发生在BEGIN之前，比如DECLARE部分或PACKAGE的变量初始化部分，那么这个异常就不会被当前块的EXCEPTION捕获，而是继续向外传播。


15.DETERMINISTIC:
1).表示给定的输入一定返回同样的输出，不随时间环境或数据库变化。利用自定义函数创建函数索引时，此函数必须具有DETERMINISTIC关键字。
2).嵌套子程序里面不可以用DETERMINISTIC。
3).使用同样的参数，加上DETERMINISTIC 之后, 函数结果会被缓存，只有一次调用。


16.RESULT_CACHE [RELIES_ON]:
1).每当表发生了修改并被提交，所有依赖于这个表的结果缓存将被清空。随后对函数的调用会导致数据再次进入缓存。
2).一旦你在会话中修改了表，那么所有依赖于这个表的缓存将被忽略。


17.DBMS_ASSERT
DBMS_ASSERT.SIMPLE_SQL_NAME检查一个名字是否为SQL中可用的简单名字：
  名字必须以字母开头，随后可跟随数字、字母或_, $, # 字符；
  允许带双引号,双引号之间可以是任意字符；
  假如双引号之内的名字本身就带有双引号，那么必须重复双引号两次来表示；
  输入参数如果前后带有空格被忽略。
  名字的长度没有被检测。
DBMS_ASSERT.qualified_sql_name则更宽松一些，允许带.(小数点，比如用在记录成员、PACKAGE里面的函数、SCHEMA OWNER等)和@ (用在DBLINK)


18.WHERE CURRENT OF, FOR UPDATE OF: