PL/SQL(Procedural Language/SQL, 过程化 SQL 语言), 是一种高级数据库程序设计语言, 专门用于在各种环境下对 ORACLE 数据库进行访问. 由于该语言集成于数据库服务器中, 所以 PL/SQL 代码可以对数据进行快速高效的处理.
PL/SQL 中可引用的 SQL 语句
可用 DML 语句: SELECT INTO,INSERT,UPDATE,DELETE.
可用 TCL 语句: COMMIT,ROLLBACK,SAVEPOINT.
不能使用 DDL 语句.
PL/SQL 块
PL/SQL 块: 声明部分 + 执行部分 + 异常处理部分
-- 单行注释
- DECLARE
- /* 声明部分: 声明变量, 类型, 游标, 局部存储过程和函数 */
- BEGIN
- /* 执行部分: 执行过程和 SQL 语句 */
- [EXCEPTION]
- /* 异常处理部分 */
- END;
PL/SQL 变量
PL/SQL 变量命名
标识符 | 命名规则 |
---|---|
程序变量 | V_name |
程序常量 | C_Name |
游标变量 | Name_cursor |
异常标识 | E_name |
表类型 | Name_table_type |
表 | Name_table |
记录类型 | Name_record |
PL/SQL 变量类型
基本数据类型
number,char,varchar2,long,date
记录类型
记录类型: 把逻辑相关的数据作为一个单元存储起来, 用于存放互不相同但逻辑相关的信息.
- TYPE record_type IS RECORD(
- Field1 type1 [NOT NULL] [:= exp1 ],
- Field2 type2 [NOT NULL] [:= exp2 ],
. . . . . .
Fieldn typen [NOT NULL] [:= expn ] ) ;
%TYPE 类型
%TYPE 类型: 指某个已定义变量的数据类型类型, 或数据表中某列的数据类型.
使用 %TYPE 特性的优点:
所引用的数据库列的数据类型可以不必知道;
所引用的数据库列的数据类型可以实时改变.
%RowType 类型
%RowType 类型: 返回一个与数据库表的数据结构一致的记录类型.
使用 %ROWTYPE 特性的优点:
所引用的数据库中列的个数和数据类型可以不必知道;
所引用的数据库中列的个数和数据类型可以实时改变.
PL/SQL 特殊运算符
赋值运算符::=
关系运算符:=>
上下限运算符:..
PL/SQL 流程控制
条件语句
IF 语句
IF < 条件语句 1> THEN
语句 1;
ELSIF < 条件语句 2> THEN
语句 2;
ELSE
语句 3;
END IF;
注意是 ELSIF 不是 ELSEIF;
CASE 语句
CASE < 变量 >
WHEN < 值 1> THEN < 结果 1>
WHEN < 值 2> THEN < 结果 1>
...
WHEN < 值 N> THEN < 结果 N>
[ELSE < 结果 N+1>]
END;
循环语句
do...while 循环
LOOP
循环语句;
EXIT WHEN < 条件语句 >
END LOOP;
while 循环
WHILE < 条件语句 > LOOP
循环语句;
END LOOP;
for 循环
FOR < 循环计数器 > IN [REVERSE] < 下限 > .. < 上限 > LOOP
循环语句;
END LOOP;
GOTO 语句
定义标号:<< 标号名 >>;
GOTO 语句: GOTO 标号名;
NULL 语句
NULL 语句: 不做任何事, 增强代码可读性.
PL/SQL 异常处理
异常错误类型
预定义错误: 无需在程序中定义, 由 Oracle 自动将其引发.
非预定义错误: 用户需在程序中定义, 然后由 Oracle 自动将其引发.
用户定义错误: 用户在程序中定义, 然后显式地在程序中将其引发.
异常处理
EXCEPTION
WHEN < 异常 1> THEN < 异常处理代码 >
WHEN < 异常 2> THEN < 异常处理代码 >
WHEN OTHERS THEN < 异常处理代码 >
游标 - CURSOR
为了处理 SQL 语句, Oracle 会分配一片叫上下文 (context area) 的区域来处理所必需的信息, 即系统为用户开设的一个数据缓冲区, 存放 SQL 语句的执行结果. 游标就是一个指向上下文的句柄或指针.
显式游标
显式游标主要是用于对查询语句的处理, 尤其是查询结果为多条记录的情况.
显式游标处理:
定义游标: 定义游标名及对应的 SELECT 查询
CURSOR cursor_name (参数 1 参数类型, 参数 2 参数类型...) IS
SELECT 查询块;
数据类型不能使用长度约束.
打开游标: 执行游标的 SELECT 查询, 将查询结果放入缓冲区, 并指向缓冲区首部.
OPEN cursor_name (参数 1 => 值, 参数 2 => 值...);
提取游标数据: 检索结果集中的数据行, 放入指定输出变量中.
FETCH cursor_name INTO {variable_list | record_variable };
关闭游标: 释放游标占用的系统资源.
CLOSE cursor_name;
显式游标属性
游标属性 | 描述 |
---|---|
%FOUND | 布尔型,当最近一次读记录时成功返回, 则值为 TRUE |
%NOTFOUND | 布尔型,当最近一次读记录时返回失败, 则值为 TRUE |
%ISOPEN | 布尔型,当游标已打开时返回 TRUE |
%ROWCOUNT | 数值型,返回已从游标中读取的记录数 |
游标的 FOR 循环:
FOR 索引 IN 游标 [值 1, 值 2...] LOOP
循环语句;
END LOOP;
隐式游标
隐式游标主要用于数据更新操作. 隐式游标的名字为 SQL, 由 Oracle 系统提供, 无需用户处理.
隐式游标属性
游标属性 | 描述 |
---|---|
SQL%FOUND | 布尔型,当最近一次读记录时成功返回, 则值为 TRUE |
SQL%NOTFOUND | 布尔型,当最近一次读记录时返回失败, 则值为 TRUE |
SQL%ISOPEN | 布尔型,当游标已打开时返回 TRUE |
SQL%ROWCOUNT | 数值型,返回已从游标中读取的记录数 |
存储过程与函数
存储过程用于执行特定操作, 无返回值; 函数用于执行复杂操作, 有返回值; 存储函数与函数统称为 PL/SQL 子程序.
存储过程 - PROCEDURE
存储过程: 执行特定操作, 无返回值, 多用于更新操作.
定义存储过程
- CREATE [OR REPLACE] PROCEDURE Procedure_name
- [ (argment [ { IN | IN OUT }] Type,
- argment [ { IN | OUT | IN OUT } ] Type ]
- { IS | AS }
< 类型. 变量的说明 >
BEGIN
< 执行部分 >
EXCEPTION
< 异常处理 >
END;
函数 - FUNCTION
函数: 执行复杂操作, 有返回值.
定义函数
CREATE [OR REPLACE] FUNCTION < 函数名 >[(argument [ { IN | IN OUT }] type,argument [ { IN | OUT | IN OUT } ] type]RETURN < 返回值类型 >
{ IS | AS }
< 类型. 变量的说明 >
BEGIN
< 执行部分 >
EXCEPTION
< 异常处理 >
END;
函数执行方式
- dbms_output.put_line(fun());
- select fun() from dual;
函数参数类型
输入参数 IN;
输出参数 OUT;
输入输出参数 IN OUT;
参数传递类型
位置表示法: 根据参数位置依此传值
argument_value1[,argument_value2 ...]
名称表示法: 使用关系运算符 => 为参数传值
argument => parameter [,...]
触发器 - TRIGGER
触发器: 用户定义的一类由事件触发而执行的特殊过程.
定义触发器
CREATE [OR REPLACE] TRIGGER < 触发器名称 >
- {BEFORE | AFTER | INSTEAD OF}
- {INSERT | DELETE | UPDATE [OF column [, column ...]]}
ON < 表 / 视图 >
- [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
- [FOR EACH ROW | STATEMENT]
- [WHEN condition]
- trigger_body;
触发事件: INSERT | DELETE | UPDATE
触发时机: BEFORE | AFTER
触发频率:
ROW: 行级触发; 当某触发事件发生时, 对受到该操作影响的每一行数据, 触发器都单独执行一次.
STATEMENT: 语句级触发; 当某触发事件发生时, 该触发器只执行一次.
触发器的新值与旧值:
:new 事件触发后的新的数据行;
:old 事件触发前的旧的数据行;
来源: http://www.bubuko.com/infodetail-2696675.html