块结构
PL/SQL程序可划分成称为块的结构，每一个块都包含有PL/SQL和SQL语句。
典型的PL/SQL块包含如下结构：
[DECLARE
declaration_statements 声明PL/SQL块其余部分使用的变量。DECLARE是可选的。
]
BEGIN
executable_statements 块中实际可执行的语句，其中可能包括诸如循环、条件逻辑等任务的语句
[EXCEPTION
exception_handing_statements 负责处理当块运行时可能发生的任何可执行错误。EXCEPTION是可选的。
]
END;
/
例子：根据给出的矩形面积和高度计算其宽度
SET SERVEROUTPUT ON 打开服务器输出以便在SQL*Plus中运行脚本时可以在屏幕上看到由DBMS_OUTPUT.PUT_LINE()产生的行。
DECLARE
v_width INTEGER;
v_height INTEGER;
v_area INTEGER;
BEGIN
--set the width equal to the area divided by the height 注释
v_width := v_area / v_height;
DBMS_OUTPUT.PUT_LINE('v_width = ' || v_width); DBMS_OUTPUT是Oracle数据库内置的代码包，DBMS_OUTPUT包含允许将值输出到屏幕上的过程。
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero');
END;
/ PL/SQL块结束
变量和类型
可通过%TYPE关键字来定义变量的类型，使用与表中指定列相同的类型。
v_product_price products.price%TYPE 这个变量的类型与products表的price列类型相同。
条件逻辑
IF condition1 THEN
statements1
ELSEIF condition2 THEN
statements2
ELSE
statements3
END IF;
循环
简单循环 直到显式结束循环之前一直运行。
LOOP
statements
END LOOP; 要结束简单循环，可以使用EXIT立即结束循环，EXIT WHEN 在指定条件出现时结束循环。
例：
v_counter := 0;
LOOP
v_counter := v_counter + 1;
EXIT WHEN v_counter = 5;
END LOOP; EXIT WHEN语句可以出现在循环代码中的任何位置。
可以使用CONTINUE或CONTINUE WHEN语句结束循环的当前迭代。CONTINUE无条件结束循环当前迭代，并继续执行下一个迭代。CONTINUE WHEN在满足特定条件时结束循环的当前迭代，然后继续执行下一个迭代。
例：
v_counter := 0;
LOOP
-- after the CONTINUE statement is executed, control returns here
v_counter := v_counter + 1;
IF v_counter = 3 THEN
CONTINUE; -- end current iteration unconditionally
END IF;
EXIT WHEN v_counter = 5;
END LOOP; CONTINUE或CONTINUE WHEN 不能跨过过程、函数或方法的边界。
WHILE循环 在某个特定条件出现之前一直运行
WHILE condition LOOP
statements
END LOOP;
FOR 循环 运行预先确定的次数，给循环变量指定下限和上限来确定循环运行的次数，然后，循环变量在每次循环中递增（或递减）。
FOR loop_variable IN [REVERSE] lower_bound.. upper_bound LOOP
statements
END LOOP;在没有使用REVERSE的情况下，循环变量初始化为下限值。使用REVERSE，循环变量初始化为上限值。
例：
FOR v_counter2 IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(v_counter);
END LOOP;
游标 获取查询返回的记录。在通过查询将记录检索到游标中后，可以一次从游标中取出一行。
使用游标步骤一：声明用于保存列值的变量
DECLARE
v_product_id products.product_id%TYPE;
v_name products.name%TYPE;
v_price products.price%TYPE;
步骤二：声明游标，游标的声明由游标名和希望执行的查询组成。
CURSOR cursor_name IS
select_statement; 查询在打开游标前并不会真正运行。
步骤三：打开游标
OPEN v_product_cursor
步骤四：从游标中取得记录
FETCH cursor_name
INTO variable[, variable ...]
当游标包含多条记录，要通过循环一次读取每一条记录。为了确定循环是否结束，可以使用布尔变量v_product_cursor%NOTFOUND。当从v_product_cursor中不能再读出记录时，这个变量就为真。
步骤五：关闭游标 释放系统资源
CLOSE v_product_cursor;
完整示例：
SET SERVEROUTPUT ON
DECLARE
--step 1: declare the variables
v_product_id products.product_id%TYPE;
v_name products.name%TYPE;
v_price products.price%TYPE;
--step 2:declare the cursor
CURSOR v_product_cursor IS
SELECT product_id, name, price
FROM products
ORDER BY product_id
BEGIN
--step 3:open the cursor
OPEN v_product_cursor;
LOOP
--step 4 :fetch the rows from the cursor
FETCH v_product_cursor
INTO v_product_id,v_name,v_price;
EXIT WHEN v_product_cursor%NOTFOUND
--use DBMS_OUTPUT.PUT_LINE() to display the variables
DBMS_OUTPUT.PUT_LINE(
'v_product_id = ' || v_product_id || ', v_name = name' || v_name ||
', v_space = ' v_space
);
END LOOP;
--step 5: close the cursor
CLOSE v_product_cursor;
END
/
游标与FOR循环
利用FOR循环可以访问游标中的记录。当使用FOR循环时，可以不显式的打开和关闭游标。
FOR循环会自动执行这些操作
OPEN-FOR语句
可以将游标分配给不同的查询，因此可以更加灵活的处理游标。
无约束游标
前面介绍的游标都有具体的返回类型，这些游标称为约束游标。约束游标的返回类型必须与游标运行的查询中的列相匹配。无约束游标没有返回类型，因此可以运行任何查询。
异常
ZERO_DIVIDE异常 试图用一个数字除以零的时候发生
DUP_VAL_ON_INDEX试图向具有唯一性索引约束的列中插入重复的值。
INVALID_NUMBER试图将无效的字符串转换成数字时。
ORTHERS异常可以处理所有异常。
过程
过程包含一组SQL和PL/SQL语句。过程可将业务逻辑集中在数据库中，任何能够访问数据库的程序都可以使用过程。
CREATE PROCEDURE [OR REPLACE] procedure_name
[ ( parameter_name [IN | OUT | IN OUT ] type [, ...]) ]
{ IS | AS}
BEGIN
procedure_body;
END procedure_name;
例子：
CREATE PROCEDURE update_product_price (
p_product_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 product_id = p_product_id
IF v_product_count = 1THEN
UPDATE products
SET price = price * p_factor
WHERE product_id = p_product_id;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END update_product_price
/
调用过程：
位置表示法CALL update_product_price(1,1.5);
命名表示法CALL update_product_price(p_factor => 1.3,p_product_id => 2);
混合表示法CALL update_product_price(3,p_factor => 1.7);
获取有关过程的信息：user_procedures(all_procedures)视图
删除过程DROP PROCEDURE update_product_price;
查看过程中的错误：SHOW ERRORS
函数
函数与过程很相似，唯一区别在于函数必须向调用它的语句返回一个值。
存储过程和函数有时合起来被称为存储子程序。
创建函数
CREATE FUNCTION [OR REPLACE] function_name
[ (parameter_name [ IN | out | IN OUT] type [, ...] ) ]
RETURN type
{ IS | AS }
BEGIN
function_body
END function_name
例子1：
CREATE FUNCTION circle_area (
p_radius IN NUMBER
) RETURN NUMBER AS
v_pi NUMBER := 3.1415926;
v_area NUMBER;
BEGIN
v_area := v_pi * POWER(p_radius,2);
RETURN v_area;
END circle_area;
/
例子2：
CREATE FUNCTION average_product_price (
p_product_type_id IN INTEGER
) RETURN NUMBER AS
v_average_product_price NUMBER;
BEGIN
SELECT AVG(price)
INTO v_average_product_price
FROM products
WHERE product_type_id = p_product_type_id;
RETURN v_average_product_price;
END average_product_price;
/
调用函数
SELECT circle_area(2) FROM dual;
命名表示法： SELECT circle_area(p_radius => 4) FROM dual;
SELECT average_product_price(1) FROM dual;
获取有关函数的信息：user_procedures(all_procedures)视图。
删除函数：DROP FUNCTION function_name;
包（package）
包可以将彼此相关的功能划分到一个自包含的单元中。通过这种方式将PL/SQL代码模块化，可以构建供其他编程人员重用的代码库。
事实上，ORACLE数据库提供了包库，可用来访问外部文件，管理数据库、生成HTML等等。
包通常由两部分组成：规范(specification)和包体(body)。
包的规范列出可用的过程、函数、类型和对象。所有的数据库用户都可以访问这些条目，因此这些条目称为公有项目（只有具有包访问权限的用户才能使用）。规范中通常不包括构成这些过程和函数的代码，包体中才包含实际的代码。
包体中任何在规范中未列出的项目对于这个包体都是私有对象。私有项目只能用在包体内。通过将公有项目和私有项目结合起来，可以构建出非常复杂的包结构，这种复杂性外部是看不到。这也是所有编程工作的主要目标之一：对用户隐藏复杂性。
创建包规范
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_product_id IN products.product_id%TYPE,
p_factor IN NUMBER
);
END product_package;
/
创建包体
CREATE [OR REPLACE] PACKAGE BODY package_name
{IS | AS }
package_body
END package_name;
例子：
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 products_id,name,price
FROM products;
RETURN v_products_ref_cursor;
END get_products_ref_cursor;
PROCEDURE update_product_price (
p_product_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 product_id = p_product_id;
IF v_product_count = 1 THEN
UPDATE products
SET price = price * p_factor
WHERE product_id = p_product_id;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END update_product_price;
END product_package;
/
调用包中的函数和过程
SELECT product_package.get_products_ref_cursor FROM dual;
CALL product_package.update_product_price(3,1.25);
获取有关包中函数和过程的信息：（user_procedures）
删除包：DROP PACKAGE product_package;
触发器trigger
触发器是当特定的SQL DML 语句，如INSERT,UPDATE或DELETE语句在特定的数据库表上运行时，由数据库自动激活的过程。
触发器对于实现表中某些列值的高级变更审计等功能非常有用。
触发器运行的时机
trigger可以在DML语句运行之前和之后被激活。 行级触发器（每行触发一次）与语句级触发器（DML语句触发一次）
创建触发器
CREATE [ OR REPLACE ] TRIGGER trigger_name
{BEFORE | 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;
例子：
CREATE TRIGGER before_product_price_update
BEFORE UPDATE OF price
ON products
FOR EACH ROW WHEN (new.price < old.price * 0.75)
BEGIN
dbms_output.put_line('pruduct_id = ' || :old.product_id);
dbms_output.put_line('Old price = ' || :old.price);
dbms_output.put_line('New price = ' || :new.price);
dbms_output.put_line('The price reduction is more than 25%');
INSERT INTO product_price_audit (
product_id,old_price,new_price
) VALUES (
:old.product_id, :old.price, :new.price
);
END before_product_price_update;
/
激活触发器
SET SERVEROUTPUT ON
UPDATE products
SET price = price * .7
WHERE product_id IN (5,10);
获得有关触发器的信息user_trigger(all_trigger)视图。
禁用和启用触发器
ALTER TRIGGER before_product_price_update DISABLE;
ALTER TRIGGER before_product_price_update ENABLE;
删除触发器
DROP TRIGGER before_product_price_update ;
Oracle Darabase 11g新增加的PL/SQL特性。
SIMPLE_INTEGER类型，当不需要存储NULL且对计算中产生的溢出截断不介意时，应该在PL/SQL中使用SIMPLE_INTEGER。否则就应该使用BINARY_INTEGER。
在PL/SQL中使用序列。
PL/SQL本地机器代码生成
默认情况下，每个PL/SQL程序单元都被编译成中间格式，即机器可读的代码。这些代码存储在数据库中，每次代码运行时需要进行解释。使用PL/SQL本地编译，PL/SQL被转换为本地代码，并存储在共享库中。
本地代码的运行速度比中间代码的运行速度快得多，因为本地代码在运行之前不需要解释。
918
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
