Oracle数据库中一些基础的语法结构,方括号里的内容为可选项
大括号是必填
1PL/SQL结构块
DECLARE /* * 声明部分——定义常量、变量、复杂数据类型、游标、用户自定义异常 */ BEGIN /* * 执行部分——PL/SQL语句和SQL语句 */ EXCEPTION /* * 异常处理部分——处理运行异常 */ END; /*块结束标记 */
2 创建序列
1 CREATE SEQUENCE sequence_name 2 3 4 5 6 7 8 ... 9 ;
3 删除序列
1 DROP SEQUENCE sequence_name ;
4 修改序列
1 ALTER SEQUENCE sequence_name
2 { INCREMENT BY integer
3 | { MAXVALUE integer | NOMAXVALUE }
4 | { MINVALUE integer | NOMINVALUE }
5 | { CYCLE | NOCYCLE }
6 | { CACHE integer | NOCACHE }
7 | ...
8 } ...
9 ;
注意，不能修改序列的初始值,否则会报ORA-02283。如果需要修改初始值，先删除序列再重新创建序列设定初始值。
复合数据类型
5.1. 记录（Record）
1 TYPE record_name IS RECORD( 2 varable1 data_type1 , 3 varable2 data_type2 , 4 ......, 5 varablen data_typen 6 );
5.2. 表(TABLE)
5.2.1. 索引表
1 TYPE table_name IS TABLE OF element_type 2 INDEX BY ;
5.2.2 嵌套表
1 TYPE type_name IS TABLE OF element_type;
5.3. 数组（VARRAY）
1 TYPE varray_name IS VARRAY(size) OF element_type ;
6 显式游标
6.1声明/定义游标
1 CURSOR cursor_name 2 …)] 3 4 IS 5 select_statement;
6.2 打开游标
1 OPEN cursor_name actual_cursor_parameter ]... ) ]
6.3读取数据
1 FETCH { cursor | cursor_variable | :host_cursor_variable }
2 { into_clause | bulk_collect_into_clause } ;
6.4 关闭游标
1 CLOSE cursor_name;
7 游标FOR循环
1 FOR index_variable IN cursor_name…)] LOOP 2 -- 游标处理语句 3 END LOOP;
8条件结构
1. 简单IF结构
1 -- 简单IF结构 2 IF THEN 3 满足条件时执行的语句 4 END IF;
2. IF-ELSE结构
1 -- IF-ELSE结构 2 IF THEN 3 满足条件时执行的语句 4 ELSE 5 不满足条件时执行的语句 6 END IF;
3 多重IF
1 -- 多重IF 2 IF THEN 3 满足条件1时执行的语句 4 ELSIF THEN 5 满足条件2时执行的语句 6 ELSIF THEN 7 满足条件3时执行的语句 8 ELSE 9 满足条件1、2、3均不满足时执行的语句 10 END IF;
注意：ELSIF不能写成ELSEIF
9 CASE
语法一
1 CASE 条件表达式 2 WHEN 条件表达式结果1 THEN 3 语句1 4 WHEN 条件表达式结果2 THEN 5 语句2 6 ...... 7 WHEN 条件表达式结果n THEN 8 语句n 9 10 END CASE;
语法二
1 CASE 2 WHEN 条件表达式1 THEN 3 语句1 4 WHEN 条件表达式2 THEN 5 语句2 6 ...... 7 WHEN 条件表达式n THEN 8 语句n 9 10 END CASE;
10 循环结构
简单循环
1 LOOP 2 循环体语句; 3 4 END LOOP;
WHILE 循环
1 WHILE LOOP 2 循环体语句; 3 END LOOP;
FOR循环
1 2 FOR 循环计数器 IN 下限 .. 上限 LOOP 3 循环体语句; 4 END LOOP ;
11 异常处理通常放在PL/SQL程序的后部，语法结构为
1 EXCEPTION
2 WHEN { exception ... | OTHERS }
3 THEN statement ...
11.1非预定义的异常处理
非预定义异常有错误号没有名字，处理的办法是：自己定义一个名字，绑定到错误号，捕获错误名。处理这类异常，首先必须对非预定义的Oracle异常进行定义。
如：
1 myexcp EXCEPTION;
然后使用EXCEPTION_INIT语句与标准的ORACLE错误联系起来，如：
2 PRAGMA EXCEPTION_INIT(myexcp,-02292);
说明：ORA-02292是违反完整性约束的错误代码。
11.2 定义异常
1 异常名称 EXCEPTION; 2 PRAGMA EXCEPTION_INIT(异常的名字,错误号);
11.3抛出异常
1 RAISE 异常的名称
RAISE_APPLICATION_ERROR过程可用于创建用户定义的错误信息，可以在可执行部分和异常处理部分使用
1 RAISE APPLICATION_ERROR(错误号,错误描述);
11.4 处理异常
1 1 EXCEPTION
2 2 WHEN { exception ... | OTHERS }
3 3 THEN statement ...
12. FORALL
使用FORALL，可以将多个DML批量发送给SQL引擎来执行，最大限度地减少上下文交互所带来的开销。
1 FORALL index_name IN
2 { lower_bound .. upper_bound
3 | INDICES OF collection_name
4 | VALUES OF index_collection
5 }
6 dml_statement;
index_name：一个无需声明的标识符，作为集合下标使用。
lower_bound .. upper_bound：数字表达式，来指定一组连续有效的索引数字下限和上限。该表达式只需解析一次。
INDICES OF collection_name：用于指向稀疏数组的实际下标。跳过没有赋值的元素，例如被 DELETE 的元素，NULL 也算值。
VALUES OF index_collection_name：把该集合中的值当作下标，且该集合值的类型只能是 PLS_INTEGER/BINARY_INTEGER。
SAVE EXCEPTIONS：可选关键字，表示即使一些DML语句失败，直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。
dml_statement：静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE IMMEDIATE）DML语句
13存储过程
1 CREATE PROCEDURE procedure_name
2 ... ) ]
3
4 { IS | AS }
5 { body | call_spec | EXTERNAL} ;
procedure_name：过程名称。
parameter_declaration：参数声明
格式如下
1 parameter_name datatype
2 | { OUT | IN OUT } datatype
IN：输入参数。
OUT：输出参数。
IN OUT：输入输出参数。
invoker_rights_clause：这个过程使用谁的权限运行
as或is用于开始一个PL/SQL块
declare_section：声明部分。
body：过程块主体，执行部分。
一般只有在确认procedure_name过程是新过程或是要更新的过程时，才使用OR REPALCE关键字
13 创建函数
1 CREATE FUNCTION function_name
2 ... )
3 ]
4 RETURN datatype
5 6 7 8
9 }...
10 ]
11 { { AGGREGATE | PIPELINED } USING implementation_type
12 | { IS | AS } { body
13 | call_spec
14 | EXTERNAL
15 }
16 } ;
14 建立包规范
1 CREATE
2 PACKAGE package_name
3
4 { IS | AS } item_list_1 END ;
package_name：包名。
invoker_rights_clause：使用谁的权限运行
item_list_1：声明包的公用组件列表
1 { type_definition -- 数据类型
2 | cursor_declaration -- 游标
3 | item_declaration -- 变量、常量等
4 | function_declaration -- 函数
5 | procedure_declaration -- 过程
6 }
7 8 9 10 11 12 13 14 ...
15 建立包体
1 CREATE PACKAGE BODY package_name
2 { IS | AS }
3 BEGIN statement ...
4 ... ]
5
6 END ;
动态SQL 使用EXECUTE IMMEDIATE语句
1 EXECUTE IMMEDIATE dynamic_sql_stmt 2 3 | using_clause 4 | dynamic_returning_clause 5 ] ;
dynamic_sql_stmt：是代表一条SQL语句或一个PL/SQL块的字符串表达式。
into_clause：用于存放被选出的字段值的变量或被选出的行记录。格式如：
INTO { variable [, variable ]... | record )
using_clause：SQL或PL/SQL字符串中包括用于参数绑定的占位符时，该子句为占位符绑定值，也可用于返回值。输入bind_argument参数是一个表达式，它的值将被输入(IN模式)或输出(OUT模式)或输入输出(IN OUT模式)到动态SQL语句或是PL/SQL块中。一个输出bind_argument参数就是一个能保存动态SQL返回值的变量。格式如：
USING [ IN | OUT | IN OUT ] bind_argument [ [,] [ [ IN | OUT | IN OUT ] bind_argument ]...
dynamic_returning_clause：指明用于存放返回值的变量或记录。格式如：
{ RETURNING | RETURN } { into_clause | bulk_collect_into_clause }
：