|
1、plsql大小写敏感，代码规则一般为：命令为大写，变量小写；
2、结构
declare
dec var
begin
exe section
exception
exception handle
end;
最简单的空语句
begin
null;
end;
3、不推荐在参数中使用NOCOPY提示符,使用IN/OUT；pass-by-value和pass-by-reference分别对应IN，IN and OUT以及 OUT only.
4、plsql datatype的function不能再command line里面调用，sql datatype的function可以。
5、DETERMINISTIC语句最好不要在有session变量的function中使用，一般在function-based indexes和materialized views上使用;
PIPELINED在function返回collections使用能提高性能，如同nested tables嵌套表或者是VARRAYs，返回system renference cursor时也有此效果。
6、10g 提供的新特性
Built-in Packages
10g release2开始，
DBMS_SCHEDULE 取代 DBMS_JOB 调度和处理批job；
DBMS_COPYTO对大对象进行加密/解密，支持多字符集；
DBMS_MONITORDelivers an API supporting tracing and statistic gathering by sessions；
Compile-Time Warnings
10g release1 开始
可以为session或者database设置，设置方法
ALTER SESSION SET plsql_warnings = 'enable:all';
Conditional Compilation
10g release2开始
设置、使用方法如下：
ALTER SESSION SET PLSQL_CCFLAGS = 'debug:1';单个变量设置
ALTER SESSION SET PLSQL_CCFLAGS = 'name1:value1 [, name(n+1):value(n+1) ]';多个变量设置
使用
BEGIN
$IF $$DEBUG = 1 $THEN $$变量引用
dbms_output.put_line('Debug Level 1 Enabled.');
$END正常的if-then-else此处为END IF
END;
function可以如下使用
CREATE OR REPLACE FUNCTION conditional_type
( magic_number $IF $$DEBUG = 1 $THEN SIMPLE_NUMBER $ELSE NUMBER $END )
RETURN NUMBER IS
BEGIN
RETURN magic_number;
END;
也可以在AS、IS后面使用。
Optimized PL/SQL Compiler
10g release 1
为session设置pl/sql优化参数 ALTER SESSION SET plsql_optimize_level = 0;
为单个procedure设置pl/sql优化参数 ALTER PROCEDURE some_procedure COMPILE plsql_optimize_level = 1;
使用上一个优化参数设置 ALTER PROCEDURE some_procedure COMPILE REUSE SETTINGS;
优化参数具体含义如下
0 No optimization.
1 Moderate optimization, may eliminate unused code or exceptions.
2 (default) Aggressive optimization, may rearrange source code flow.
Regular Expressions
10g release 1
REGEXP_LIKE This searches a string for a regular expression pattern match.
REGEXP_INSTR This searches for the beginning position of a regular expression pattern match.
REGEXP_SUBSTR This searches for a substring using a regular expression pattern match.
REGEXP_REPLACE This replaces a substring using a regular expression pattern match.
引号转换
10g release 1
原 SELECT 'It''s a bird, no plane, no it can''t be ice cream!' AS phrase
FROM dual;
新写法 SELECT q'(It's a bird, no plane, no it can't be ice cream!)' AS phrase
FROM dual;
都是输出It's a bird,no plane,no it can't be ice cream! | 
转播
