本篇是 Oracle基础小结 系列之一。
PL/SQL程序单元包括：PL/SQL匿名块、PL/SQL函数、PL/SQL存储过程、PL/SQL包、PL/SQL触发器等。这里就用过的几个做简单记录，另外虽然PL/SQL异常处理不是程序单元，但也是其中重要的组成部分，也放在这里一并阐述。因此这部分包括：
1、PL/SQL匿名块
2、PL/SQL函数
3、PL/SQL存储过程
4、PL/SQL包
5、PL/SQL触发器
6、PL/SQL异常处理
PL/SQL匿名块
PL/SQl匿名块即是PL/SQL单元的基础，其它程序单元均是匿名块增加单元头信息构成相应功能单元。注意PL/SQL里的变量需要在BEGIN前先声明。一个PL/SQL匿名块（Wiki）如下:
<<label>> -- this isoptional
declare
-- this section is optional
number1 number(2);
number2 number1%type :=17; -- value default
text1 varchar2(12) :='Hello world';
text2 date := SYSDATE; -- current date and time
begin
-- this section is mandatory, must contain at least oneexecutable statement
SELECT street_number
INTO number1
FROM address
WHERE name = 'INU';
exception
-- this section is optional
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Code is '||to_char(sqlcode ) );
DBMS_OUTPUT.PUT_LINE('Error Message is '|| sqlerrm );
end;
引用：
https://en.wikipedia.org/wiki/PL/SQL#PL.2FSQL_anonymous_block
PL/SQL函数
PL/SQL函数同其它语言函数一样，参数有输入（IN）输出（OUT）引用（IN OUT），并且有返回值（RETURN）。函数的参数类型不带长度信息，这点与声明部分不同。
格式：
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type[, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
示例：
1. create or replace function func (
2. -- 入参、出参列表， 逗号分隔。
3. uid in varchar2, -- 不能带长度信息
4. startDate in date, -- 第二个输入参数
5. defaultVar in varchar2 default "", -- 默认参数，如果不传，要注意参数的顺序
6. isok out number, -- 输出参数
7. result out varchar2 -- 第二个输出参数
8. )
9. return number -- 定义返回类型
10.as
11.-- 变量声明，每个声明用分号结束。可以在声明的同时初始化
12.var1 varchar2(11);
13.var2 number(2) := 123;
14.
15.begin
16. -- 字符串拼接用 ||
17. dbms_output.put_line('isok:' || 'abc');
18.
19.
20. return ret_val;
21.end;
引用：
http://www.tutorialspoint.com/plsql/plsql_functions.htm
http://wen866595.iteye.com/blog/1733887
PL/SQL存储过程
PL/SQL的存储过程在结构上与函数有些类似，但是不带返回值。结构如下：
CREATE [OR REPLACE] PROCEDUREprocedure_name
[(parameter_name [IN | OUT | IN OUT] type[, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
示例如下：
1. create or replace procedure sp_name (
2. -- 入参、出参列表， 逗号分隔。
3. uid in varchar2, -- 不能带长度信息
4. startDate in date, -- 第二个输入参数
5. defaultVar in varchar2 default "", -- 默认参数，如果不传，要注意参数的顺序
6. isok out number, -- 输出参数
7. result out varchar2 -- 第二个输出参数
8. )
9. as
10.-- 变量声明，每个声明用分号结束。可以在声明的同时初始化
11.var1 varchar2(11);
12.var2 number(2) := 123;
13.
14.begin
15. -- 字符串拼接用 ||
16. dbms_output.put_line('isok:' || 'abc');
17.
18. -- 调用其他存储过程
19. sub_sp_name(param1, prarm2, outParam1, outParam2);
20.
21.end; -- 存储过程结束
22./
引用：
http://www.tutorialspoint.com/plsql/plsql_procedures.htm
http://wen866595.iteye.com/blog/1733887
PL/SQL包
PL/SQL包是包含类型、变量和子程序等的模式对象，类似于面向对象中类的概念。PL/SQL包包括包的声明（Package specification）和包体或定义（Package body or definition）两部分。包的声明例子如下：
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/
包体的例子如下：
CREATE OR REPLACE PACKAGE BODY cust_sal AS
PROCEDURE find_sal(c_id customers.id%TYPE) IS
c_sal customers.salary%TYPE;
BEGIN
SELECT salary INTO c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line('Salary: '|| c_sal);
END find_sal;
END cust_sal;
/
包创建后，访问包中元素（变量、函数、存储过程等），使用package_name.element_name;使用上面包的程序如下：
DECLARE
code customers.id%type := &cc_id;
BEGIN
cust_sal.find_sal(code);
END;
/
引用：
http://www.tutorialspoint.com/plsql/plsql_packages.htm
PL/SQL触发器
触发器存储了在某些事件发生时触发的程序。通常用于数据库操作（DELETE, INSERT, or UPDATE）、数据库定义（CREATE,ALTER, or DROP）和数据库操纵（SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN）。它经常用于实现Oracle数据库的自增等。创建触发器的结构如下：
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
引用：
http://www.tutorialspoint.com/plsql/plsql_triggers.htm
PL/SQL异常处理
PL/SQL异常处理（EXCEPTION）用于捕捉抛出和处理异常。异常信息有两种：系统预定义和用户自定义。EXCEPTION写在BEGIN END块之间。在异常处理时，通常可以写ROLLBACK;语句来回滚。EXCEPTION的语法结构如下：
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN -- exception1可以是系统预定义或用户自定义
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........
WHEN others THEN
exception3-handling-statements
END;
在程序执行中还可以使用RAISE主动报异常，尤其是用户自定义异常，其语法结构如下：
DECLARE
exception_name EXCEPTION;
BEGIN
IFcondition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
一个EXCEPTION例子如下，这里既包含可系统预定义异常也包括了用户自定义异常，同时使用了RAISE和raise_application_error报异常。raise_application_error的好处在于可以把生涩的系统异常提示，写成软件相关的用户可理解的异常提示。
CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION; -- 自定义异常类型变量
BEGIN
IFsales_in = 0 THEN
RAISE no_sales; -- 报异常
ELSE
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicateorder_id.'); -- 将系统预定义异常以一种用户可理解的提示报出来
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submitthe order.'); -- 将用户自定义异常以一种用户可理解的提示报出来
WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting anorder.'); -- 以通用消息报其他异常
END;
/
附Oracle预定义的异常：
Exception |
Oracle Error |
SQLCODE |
Description |
ACCESS_INTO_NULL |
06530 |
-6530 |
It is raised when a null object is automatically assigned a value. |
CASE_NOT_FOUND |
06592 |
-6592 |
It is raised when none of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause. |
COLLECTION_IS_NULL |
06531 |
-6531 |
It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray. |
DUP_VAL_ON_INDEX |
00001 |
-1 |
It is raised when duplicate values are attempted to be stored in a column with unique index. |
INVALID_CURSOR |
01001 |
-1001 |
It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor. |
INVALID_NUMBER |
01722 |
-1722 |
It is raised when the conversion of a character string into a number fails because the string does not represent a valid number. |
LOGIN_DENIED |
01017 |
-1017 |
It is raised when s program attempts to log on to the database with an invalid username or password. |
NO_DATA_FOUND |
01403 |
+100 |
It is raised when a SELECT INTO statement returns no rows. |
NOT_LOGGED_ON |
01012 |
-1012 |
It is raised when a database call is issued without being connected to the database. |
PROGRAM_ERROR |
06501 |
-6501 |
It is raised when PL/SQL has an internal problem. |
ROWTYPE_MISMATCH |
06504 |
-6504 |
It is raised when a cursor fetches value in a variable having incompatible data type. |
SELF_IS_NULL |
30625 |
-30625 |
It is raised when a member method is invoked, but the instance of the object type was not initialized. |
STORAGE_ERROR |
06500 |
-6500 |
It is raised when PL/SQL ran out of memory or memory was corrupted. |
TOO_MANY_ROWS |
01422 |
-1422 |
It is raised when s SELECT INTO statement returns more than one row. |
VALUE_ERROR |
06502 |
-6502 |
It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs. |
ZERO_DIVIDE |
01476 |
1476 |
It is raised when an attempt is made to divide a number by zero. |
引用：
https://www.techonthenet.com/oracle/exceptions/when_others.php