pl/sql

PL/SQL


1、pl/sql块的基本结构如下：
DECLARE
...
BEGIN
...
EXCEPTION
...
END

2、变量声明
variable_name [CONSTANT] data_type NOT NULL [DEFAULT | := value]

3、PLS_INTEGER：一个有符号整数，比NUMBER变量更小的表示范围，占用更少内存，更有效地利用CPU，
运算比NUMBER和BINARY_INTEGER更快；

BINARY_INTEGER：与PLS_INTEGER相似，比NUMBER变量需要更少的内存，
区别：PLS_INTEGER运算溢出时，常会发生异常，但是当BINARY_INTEGER运算溢出时，如果可以指派给一个NUMBER变量，
就不会触发异常；

4、控制语句：
  条件：
IF condition1 THEN
statements1
ELSIF condition2 THEN
statements2
ELSE
statements3
END IF;

CASE
WHEN condition1 THEN statements1
...
WHEN condition2 THEN statementsn
[ELSE statementsN]
END CASE;

循环
LOOP
statements
EXIT[WHEN condition]
END LOOP;

WHILE condition LOOP
statements
END LOOP;

FOR loop_variable in [REVERSE] lower_bound..upper_bound LOOP
statements
END LOOP;

5、数据类型
复合变量
%TYPE、%ROWTYPE
v_name temp.ename%TYPE;

PL/SQL记录
TYPE record_name_type IS RECORD(
field1_name data_type [NOT NULL][DEFAULT | :=] default_value
...
);

PL/SQL集合
索引表：与高级语言数组的区别：索引表的元素个数没有限制，并且下标可以为负值。
下标数据类型不仅可以使用BINARY_INTEGER和PLS_INTEGER，还可以是VARCHAR2;
TYPE type_name IS TABLE OF element_type
[NOT NULL]INDEX BY key_type
identifier type_name
如：TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;


嵌套表：与高级语言数组区别，其下标从1开始，元素个数没有限制，元素值可以是无序的；
索引表不能作为表列的数据类型，但嵌套表可以；
TYPE type_name IS TABLE OF element_type;
idetifer type_name;

变长数组：下标从1开始，元素最大个数有限制，可以作为表列数据类型；
TYPE type_name IS VARRAY(size_limit) OF element_type [NOT NULL]
identifier type_name;
如：ename_table:=ename_table_type('MARY', 'LILI');

PL/SQL记录表：PL/SQL记录其用于处理单行多列数据，PL/SQL集合用于处理多行单列数据，
而PL/SQL记录用于处理多行多列数据；
如：TYPE emp_table_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;

集合方法
collection_name.method_name{(parameters)}
EXISTS()、COUNT()、LIMIT()、FIRST()、LAST()、PRIOR()、NEXT()、EXTEND()、TRIM()、DELETE();

7、游标：隐含游标/显式游标；
CURSOR cursor_name IS SELECT...
OPEN cursor_name;
FETCH cursor_name INTO variable1, variable2, ..;
CLOSE cursor_name;

游标属性
cursor_name%ISOPEN、%FOUND、%NOTFOUND、%ROWCOUNT

参数化游标
CURSOR cursor_name(parameter) IS SELECT...
OPEN cursor_name(parameter);

隐式游标：PL/SQL在执行一个SQL语句时，Oracle服务器将自动创建一个隐式游标。隐式游标是内存中处理该语句的工作区域，
其名称固定为SQL。隐式游标无须声明和打开，使用完后也不用关闭，所有这一切都由系统自动维护；
如：SELECT...INTO语句使用了一个隐式游标；

使用游标更新或删除数据
CURSOR cursor_name IS SELECT...FOR UPDATE;
UPDATE table_name SET column=...WHERE CURRENT OF cursor_name;
DELETE table_name WHERE CURRENT OF cursor_name;

游标FOR循环
FOR record_name IN cursor_name LOOP
statement1;
statement2;
END LOOP;

游标变量
上面由用户定义的显式游标和隐式游标都与固定的查询语句相关联，所以称之为静态游标，游标变量和静态游标不同，
它是一种动态游标，在运行期间可以与不同的查询语句相关联。游标变量有点像指向记录集的一个指针，游标变量也可以使用游标的属性；
TYPE cursor_variable_type IS REF CURSOR [RETURN return_type]：有返回类型为强类型，否则弱类型；
OPEN cursor_variable FOR SELECT...;

8、异常处理
EXCEPTION
WHEN exception1 THEN
statements
...
WHEN exceptionn THEN
statementsn
WHEN OTHERS THEN
statements

预定义异常
Dup_val_on_index：破坏唯一性限制；
NO_DATA_FOUND：SELECT INTO没有找到数据；
TOO_MANY_ROWS：SELECT INTO返回多行；
CURSOR_ALERADY_OPEN：试图打开一个已经打开的游标；
ACCESS_INTO_NULL：试图为null对象赋值；

用户自定义异常
exception_name EXCEPTION;
PRAGMA EXCEPTION_INTO(exception_name, exception_no);
RAISE exception_name：触发异常；

异常函数
SQLCODE：异常code；SQLERRM：异常信息；

自定义错误消息：
raise_application_error(error_number, message[,{TRUE|FALSE}])

9、存储过程
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] datatype [,...])]
{is | AS}
BEGIN
procedure_body
END procedure_name;

EXECUTE procedure_name;
call procedure_name();

10、函数
CREATE [OR REPLACE] FUNCTION function_name
[parameter1 {IN | OUT | IN OUT} datatype
...
parameterN {IN | OUT | IN OUT} datatype]
RETURN datatype
{IS | AS}
begin
function_body;
end;

11、程序包：包头、包体；
包头
CREATE [OR REPLACE] PACKAGE package_name
{IS | AS}
package_specification
END package_name;

包体
CREATE [OR REPLACE] PACKAGE BODY package_name
{IS | AS}
BEGIN
...
END;
END package_name;

重载
CREATE [OR REPLACE] PACKAGE overload package_name

包构造过程：初始化全局变量，以BEGIN开始，END结束；

12、触发器：DML触发器、替代触发器、系统触发器、DDL触发器；
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} trigger_event
ON {table_name | view_name}
[FOR EACH ROW]//默认是语句级
[WHEN]//限制条件
BEGIN
trigger_body
END trigger_name;

查看触发器
SELECT TRIGGER_TYPE, TRIGGER_NAME FROM USER_TRIGGERS;

条件谓词
INSERTING、UPDATING、DELETING

触发器的新值和旧值
:old.sal、:new.sal;
old只对update、delete有效；
new只对update、insert有效；

INSTEAD OF触发器
1、只适用于视图；
2、必须指定FOR EACH ROW选项；