ORACLE提供了四种类型的可存储的程序
. 函数
. 过程
. 包: 包其实就是被组合在一起的相关对象的集合，当包中任何函数或存储过程被调用，包就被加载入内存中，包中的任何函数或存储过程的子程序访问速度将大大加快。包由两个部分组成：规范和包主体(body),规范描述变量、常量、游标、和子程序，包体完全定义子程序和游标
. 触发器
FUNCTION name [(parameter[, parameter, …])] RETURN datatypes IS
[local declarations]
BEGIN
Execute statements
[EXCEPTION]
[Exception handlers]
END [name]
PROCEDURE name [(parameter[, parameter, …])] IS
[local declarations]
BEGIN
Execute statements
[EXCEPTION]
[Exception handlers]
END [name]
声明变量:
variable_name [CONSTANT] datatype [NOT NULL] [:=|DEFAULT expression]
注意:可以在声明变量的同时给变量强制性的加上NOT NULL约束条件，此时变量在初始化时必须赋值
声明变量:
直接赋值：X := 200; Y = Y + (X * 20);
通过SQL SELECT INTO或FETCH INTO给变量赋值：
total_salary NUMBER NOT NULL;
total_commission NUMBER NOT NULL;
SELECT SUM(salary), SUM(salary * 0.1) INTO total_salary, total_commission FROM employee WHERE dept = 10;
常量：ZERO_VALUE CONSTANT NUMBER :=0;
数据类型：
标准的数据类型分为四类：number, character, date/time, boolean
Numberic: BINARY_INTEGER, NUMBER, PLS_INTEGER
Character: CHAR, LONG, RAW, LONGRAW, ROWID, VARCHAR2
Date&Bool: BOOLEAN, DATE
LOB(Large Object): BFILE, BLOB, CLOB, NCLOB
操作符：
算术操作符：+, -, /, *, **(乘方)
关系操作符：<, <=, >, >=, =, !=, <>, :=(赋值操作符)
比较操作符：IS NULL, LIKE, BETWEEN, IN
逻辑操作符：AND, OR, NOT
执行部分：
执行部分包含了所有的语句和表达式,执行部分以关键字BEGIN开始，以关键字EXCEPTION结束，如果EXCEPTION不存在，那么将以关键字END结束。分号分隔每一条语句，使用赋值操作符:=或SELECT INTO或FETCH INTO给每个变量赋值，执行部分的错误将在异常处理部分解决，在执行部分中可以使用另一个PL/SQL程序块，这种程序块被称为嵌套块。所有的SQL数据操作语句都可以用于执行部分，PL/SQL块不能在屏幕上显示SELECT语句的输出。SELECT语句必须包括一个INTO子串或者是游标的一部分，执行部分使用的变量和常量必须首先在声明部分声明，执行部分必须至少包括一条可执行语句，NULL是一条合法的可执行语句，事物控制语句COMMIT和ROLLBACK可以在执行部分使用，数据定义语言DDL(Data Definition language)不能在执行部分中使用，DDL语句与EXECUTE IMMEDIATE一起使用或者是DBMS_SQL调用。
匿名PL/SQL块：SQL*PLUS中匿名的PL/SQL块的执行是在PL/SQL块后输入/来执行
DECLARE
V_common_precent CONSTANT NUMBER :=10;
BEGIN
UPDATE emp SET comm = sal * V_common_present WHERE deptno = 10;
END
/ //最后的这个斜杠是为了执行PL/SQL块
命名PL/SQL块：命名的程序与匿名程序的执行不同，执行命名的程序块必须使用execute关键字
CREATE OR REPLACE PROCEDURE update_commission
(v_dept IN NUMBER, v_percent IN NUMBER DEFAULT 10) IS
BEGIN
UPDATE emp SET comm = sal * v_percent WHERE deptno = v_dept;
END
/
EXECUTE update_commission(10, 15);
PL/SQL块调用：如果在另一个命名程序块或匿名程序块中执行这个程序，那么就不需要EXECUTE关进字。
DECLARE
v_dept NUMBER;
BEGIN
SELECT a.deptno INTO v_dept FROM emp a WHERE job="PRESIDENT";
update_commission(v_dept);
END
/
结构控制：
|
IF...THEN...ELSE/IF...THEN...ELSEIF
IF condition1 THEN
Statement1;
ELSEIF condition2 THEN
Statement2;
ELSE
Statement3;
END IF;
|
IF(a > b) AND (a > c) THEN
g:=a;
ELSE IF (a < b) AND (a < c) THEN
g:=c;
ELSE
g:=b;
IF c > g THEN
g:=c;
END IF
END IF
|
循环控制：
|
LOOP
Statements;
END LOOP
|
WHILE condition LOOP
Statements;
END LOOP
|
FOR counter IN [REVERSE] start_range..end_range LOOP
Statements;
END LOOP;
|
|
X:=100;
LOOP
X:=X+10;
IF X>1000 THEN
EXIT;
END IF
END LOOP;
Y:=X;
X:=100;
LOOP
X:=X+10;
EXIT WHEN X>1000;
X:=X+10;
END LOOP;
Y:=X;
|
X:=100;
WHILE X <= 1000 LOOP
X:=X+10;
END LOOP;
Y=X;
|
X:=100;
FOR v_count IN 1 .. 10 LOOP
X:=X+10;
END LOOP;
Y:=X;
|
标签：用户可以使用标签使程序获得更好的可读性。程序块或循环都可以被标记。标签的形式是<>。
|
标记程序块
|
标记循环
|
GOTO语句: GOTO label;
|
|
<>
[DECLARE]
... ... ...
BEGIN
........
[EXCEPTION]
.......
END label_name
|
<>
LOOP
.........
<>
loop
..........
<>
loop
....
EXIT outer_loop WHEN v_condition=0;
END LOOP innermost_loop;
END LOOP inner_loop;
END LOOP outer_loop;
|
X:=100;
FOR v_counter IN 1..10 LOOP
IF v_counter = 4 THEN
GOTO end_of_loop
END IF
X:=X+10;
<>
NULL
END LOOP
Y:=X;
|
嵌套：程序块的内部可以有另一个程序块这种情况称为嵌套。嵌套要注意的是变量，定义在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。同样GOTO语句不能由父块跳转道子块中，反之则是合法的。
<<OUTER BLOCK>>
DECLARE
A_NUMBER INTEGER;
B_NUMBER INTEGER;
BEGIN
--A_NUMBER and B_NUMBER are variable here
<>
DECLARE
C_NUMBER INTEGER
B_NUMBER NUMBER(20);
BEGIN
C_NUMBER:=A_NUMBER;
C_NUMBER=OUTER_BLOCK.B_NUMBER;
END SUB_BLOCK;
END OUT_BLOCK;