《Oracle Database 11g SQL 开发指南》学习札记——第11章_PL/SQL编程简介
11.1、块结构
[DECLARE
declaration_statements
]
BEGIN
executable_statements
[EXCEPTION
exception_handing _statements
]
END;
/
每一条语句都由'；'结尾，PL/SQL块由‘/’结尾。 SET SERVEROUTPUT ON命令打开服务器输出。
还可以用%TYPE关键字来定义变量类型，它告诉PL/SQL使用与表中列相同的类型。
11.3、在PL/SQL中，IF、THEN、 ELSE、ELSEIF、ENDIF等关键字来执行条件逻辑，还可以嵌套使用。
11.4、循环。
简单循环、while循环、for循环。
11.4.1、简单循环：直到显式结束循环之前一直运行。
LOOP
ststement
END LOOP; 要结束可以使用exit或exit when语句。
如：v_count :=0; loop v_count := v_count+1;
exit when v_count= 5; end loop;
在Oracle 11g中也可以使用CONTINUE 或CONTINUE WHEN 语句结束当前迭代；
如：v_count :=0; loop v_count := v_count+1;
if v_count = 3 then continue; end if ;
或者将此行换为：CIONTINUE WHEN v_count = 3;
exit when v_count= 5; end loop;
11.4.2、WHILE 循环：在某个条件出现之前一直运行。
如：v_count := 0; WHILE v_count<6
LOOP v_count := v_count+1; END LOOP;
11.4.3、FOR循环：预先确定次数。
如：FOR c IN [ REVERSE ] 1..5
LOOP
dbms_output.put_line(c);
END LOOP;
11.5、游标：可以使用游标（cursor）获取查询返回的记录。
步骤如下：1、声明一些变量，用于保存记录的列值。（注意使用%TYPE，类型要兼容）
2、声明游标并指定查询。（放在声明中：CURSOR cursor_name IS select_statement;）
3、打开游标。（在执行中：OPEN cursor_name）
4、每次从游标中获取一条记录，并将列值存储在第一步声明的变量中，然后对这些变量执行操作
FETCH cursor_name INTO variable[, variable···];
游标可能包含很多条记录，因此要使用循环，为确定循环是否结束，可以使用布尔变量coursor_name%NOTFOUND.
5、关闭游标。CLOSE cursor_name;
例如：SET SERVEROUTPUT ON
DECLARE
v_product_id products.product_id%TYPE;
v_name products.product_name%TYPE;
v_price products.product.price%TYPE;
CURSOR v_product_cursor IS
SELECT product_id name price FROM propucts ORDER BY product_id;
BEGIN
OPEN v_product_cursor;
LOOP
FETCH v_product_cursor
INTO v_product_id, v_name, v_price
EXIT WHEN v_product_cursor%NOTFOUND;
END LOOP;
END;
11.5.7、游标与FOR循环
利用for循环可以访问游标中的记录。当使用for循环时，可以不显式的打开和关闭游标——for循环会自动执行这些操作。
FOR record_name IN cursor_name LOOP statement1; statement2;··· ; END LOOP;不需要事先声明循环的记录型变量。
隐含open, fetch, 和close。
重写上例：SET SERVEROUTPUT ON
DECLARE
CURSOR v_product_cursor IS
SELECT product_id name price FROM propucts ORDER BY product_id;
BEGIN
FOR v_product IN v_product_cursor LOOP
DBMS.OUTPUT.PUT_LINE( v_product.product_id || v_product.name || v_product.price )
END LOOP;
END;
11.5.8、OPEN-FOR语句
DECLARE
TYPE t_product_cursor IS REF CURSOR RETURN products%ROWTYPE;声明一个 REF CURSOR类型
v_product_cursor t_product_cursor;
v_product products%ROWTYPE;
BEGIN
OPEN v_product_cursor FOR SELECT * FROM products WHERE product_id < 5;
LOOP
FETCH v_product_cursor INTO v_product;
EXIT WHEN v_product_cursor%NOTFOUND;
DBMS.OUTPUT.PUT_LINE( v_product.product_id || v_product.name || v_product.price );
END LOOP;
CLOSE v_product_cursor;
END;
REF CURSOR是一个指向游标的指针，这样声明的t_product_cursor结合OPEN FOR 语句就可以重用。
11.5.9、无约束游标
前面的游标都有具体的返回类型，称为约束游标；其返回类型必须与游标运行的查询中的列相匹配。无约束油表没有返回类型，因此可以运行任何查询。
DECLARE
TYPE t_cursor IS REF CURSOR ;声明一个 REF CURSOR类型，没有返回类型。
v_cursor t_cursor;
v_product products%ROWTYPE;
v_customer customers%ROWTYPE;
BEGIN
OPEN v_cursor FOR SELECT * FROM products WHERE product_id < 5;
LOOP
FETCH v_cursor INTO v_product;
EXIT WHEN v_cursor%NOTFOUND;
DBMS.OUTPUT.PUT_LINE( v_product.product_id || v_product.name || v_product.price );
END LOOP;
OPEN v_cursor FOR SELECT * FROM customers WHERE customer_id < 3;
LOOP
FETCH v_cursor INTO v_customer;
EXIT WHEN v_cursor%NOTFOUND;
DBMS.OUTPUT.PUT_LINE( v_customer.customer_id || v_customer.name );
END LOOP;
CLOSE v_cursor;
END;
11.6、异常。
系统预定义的异常：
ACCESS_INTO_NULL(ORA-06530):给未初始化对象赋值。
CASE_NOT_FOUND(ORA-06592):case中未找到匹配的when子句，也没有默认的else。
COLLECTION_IS_NULL(ORA-06531):试图将除exists之外的集合方法应用到一个未初始化的嵌套表或变长数组上。或企图给他们赋值。
CURSOR_ALREADY_OPEN(ORA-06511):试图打开已经打开的游标。
DUP_VAL_ON_INDEX(ORA-00001):试图向有唯一索引约束的列中插入重复值。
INVALID_CURSOR(ORA-01001):程序试图进行非法的游标操作。
INVALID_NUMBER(ORA-01722):试图将字符串转换成数字数字时失败。
LOGIN_DENIED(ORA-01017):试图用非法的用户名和密码连接数据库。
NO_DATA_FOUNC(ORA-01403):SELECT INTO 语句没有返回任何记录，或试图访问嵌套表中已经删除的元素，或者试图在表索引中访问未初始化的元素。
NOT_LOGGED_ON(ORA-01012):试图在连接数据库之前访问数据库中的数据。
PROGRAM_ERROR(ORA-06501):PL/SQL内部错误。
ROWTYPE_MISMATCH(ORA-06504):赋值中的宿主游标变量和PL/SQL游标变量返回类型不兼容。
SELF_IS_NULL(ORA-30625):试图在空对象中的调用MEMBER方法。
STORAGE_ERROR(ORA-06500):PL/SQL内存用尽，或内存出现问题。
SUBSCRIPT_BEYOND_COUNT(ORA-06533):试图通过大于集合中元素个数的索引值引用嵌套表或变长数组元素。
SUBSCRIPT_OUTSIDE_LIMIT(ORA-06532):试图通过合法范围之外的索引值（如-1）引用嵌套表或变长数组元素。
SYS_INVALID_ROWID(ORA-01410):将字符串转换成通用记录号rowid的操作失败，原因是该字符串并非合法的rowid。
TIMEOUT_ON_RESOURCE(ORA-00051):当数据库等待某项资源时发生超时。
TOO_MANAY_ROWS(ORA-01422):SELECT INTO 语句返回记录数多于一条。
VALUE_ERROR(ORA-06502):发生算数、转换、截或大小约束错误。
ZERO_DIVIDE(ORA-01476):试图用0除某个数字。
11.7、过程
11.7.1、创建存储过程
CREATE [ OR REPLACE ] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT ] type [,··· ]) ]
{IS | AS }
BEGIN
procedure_body
END;
11.7.2、调用过程，update_products_price( id ,p_factor)
位置表示法：CALL update_products_price( 1,1.5);
11g新增：命名表示法：CALL update_products_price( id=>1 ,p_factor=>1.5);
两种方式合起来是混合表示法。
11.7.3、获取有关过程的信息
从all_procedures中可以获得有关要访问的所有过程信息。
11.7.4、删除过程：DROP PROCEDURE update_products_price;
11.7.5、查看过程中的错误：SHOW ERRORS
11.8、函数
11.8.1、创建函数：CREATE [ OR REPLACE ] FUNCTION
function_name [(parameter_name [IN | OUT | IN OUT ] type [,··· ]) ]
RETURN type
{IS | AS }
BEGIN
function_body
END function_name;
例如：创建一个计算圆面积的函数：
CREATE FUNCTION circle_area (p_radius IN NUMBER )
RETUN NUMBRE
AS v_pi number :=3.1415926;
v_area number;
BEGIN
v_area := v_pi*POWER(p_radius,2);
RETURN v_area;
END circle_area;
11.8.2、调用函数：可以像调用其他数据库内置函数那样调用自定义的函数；
select circle_area(2) from dual;
oracle11g中可以使用命名表示法和混合表示法：select circle_area(p_radius =>2) from dual;
11.8.3、获取有关函数的信息
可以从user_procedures视图中获得有关函数的信息。
11.8.4、删除函数：DROP FUNCTION circle_area;
11.9、包。
包包含两部分：规范和包体；包的规范列出可用的过程、函数、类型、对象。所有数据库用户都可以访问这些条目，
因此称为公有项目。包体中任何没有在规范中列出的项目对于包体都是私有的对象。
11.9.1、创建包规范
CREATE [ OR REPLACE ] PACKAGE package_name
{ IS | AS }
package_specification
END package_name;
例如：CREATE PACKAGE product_package AS
TYPE t_ref_cursor IS REF CURSOR;
FUNCTION get_products_ref_cursor RETURN t_ref_cursor;
PROCEDURE update_product_price ( p_id IN products.product_id%TYPE, p_factor IN number);
END product_package;
11.9.2、创建包体
CREATE [ OR REPLACE ] PACKAGE BODY package_name
{ IS | AS }
package_body
END package_name;
下面例子为product_package创建包体：
CREATE PACKAGE BODY product_package AS
FUNCTION get_products_ref_cursor
RETURN t_ref_cursor IS
v_products_ref_cursor t_ref_cursor;
BEGIN
OPEN v_products_ref_cursor FOR
select product_id, name,price
from products;
RETURN v_products_ref_cursor;
END get_products_ref_cursor;
PROCEDURE update_product_price(
p_id IN products.product_id%TYPE,
p_factor IN number
) AS
v_product_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO v_product_count
FROM products
WHERE prouct_id = p_id;
IF v_product_count = 1 THEN
UPDATE products
SET price = price * p_factor
WHERE product_id = p_id;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END update_product_price;
END product_package;
/
11.9.3、调用包中的函数和过程
select product_package.get_products_ref_cursor from dual;
CALL product_package.update_product_price(3 , 1.25);
11.9.4、获取包中的函数和过程信息
从user_procedures中检索object_name为product_package的所有信息。
11.9.5、删除包：DROP PACKAGE product_package;
11.10、触发器：当特定的SQL DML 语句在特定的数据库上运行时，由数据库自动运行的过程。
11.10.1、运行时机：可在DML语句运行前或者后被激活。分为行级触发器和语句级触发器。
当UPDATE语句在某个列上激活行级触发器时，这个触发器可以同时访问此列的原值和新值。也可以通过使用触发器条件来限制行级触发器的激活。
11.10.2、创建触发器
CREATE [ OR REPLACE ] TRIGGER trigger_name
{BEFOR |AFTER | INSTEAD OF | FOR } trigger_event
ON table_name
[ FOR EACH ROW ]
[ { FORWARD | REVERSE } CROSSEDITION ]
[ { FOLLOWS | PRECEDES } schema.other_trigger } ]
[ {ENABLE | DISABLE } ]
[ WHEN trigger_condition ] ]
BEGIN
trigger_body
END trigger_name;
其中[ { FORWARD | REVERSE } CROSSEDITION ]是11g新增的特性，一般用于数据库管理员和应用程序管理员
FORWARD 默认设置，当联机应用程序正在增加补丁或升级时对数据库进行某种改变，触发器被激活。REVERSE是在那之后。
{ FOLLOWS | PRECEDES } schema.other_trigger } Oracle 11g新增，规定一个触发器在schema.other_trigger 中规定的另一个触发器激活之后或者之前激活。
[ {ENABLE | DISABLE } ] Oracle 11g新增，指出创建时启用还是禁用此触发器。
11.10.3、获取有关触发器信息
从user_triggers视图可以获得触发器信息。从all_triggers中可以获得要访问的所有触发器信息。
SQL*PLUS 中SET LONG命令可以查看触发器的所有代码。
11.10.4、禁用和启用触发器 ALTER TRIGGER trigger_name DISABLE/ENABLE;
11.10.5、删除触发器:DROP TRIGGER trigger_name;
11.11 Oracle Database 11g新增的PL/SQL特性
11.11.1、SIMPLE_INTEGER 类型
它是BINARY_INTEGER的子类型，存储范围与BINARY_INTEGER相同但是不能存储null值。使用此类型时算术溢出被截断，因此溢出时不会产生错误
11.11.2、在PL/SQL中使用序列（可以使用nextval和currval伪列）
CREATE SEQUENCE s_product_id;