Cursor
Cursor 是指向Oracle Server分配的私有内存区域的指针。主要有两种：
- Implicit：由Oracle自动创建和维护，用于执行一般的语句
- Explicit：有编程人员定义和维护
Implicit Cursor的属性
使用SQL cursor属性，可以判断SQL执行情况。
| 属性名 | 含义 |
|---|---|
| SQL%FOUND | Boolean型的属性，如果最近的一条SQL语句返回至少一行，那么值为TRUE |
| SQL%NOTFOUND | Boolean型的属性，如果如果最近的一条SQL语句没有返回哪怕是一行，那么值为TRUE |
| SQL%ROWCOUNT | Integer型的属性，表示被最近一条SQL语句影响的行数 |
应用举例
SET SERVEROUTPUT ON
VARIABLE rows_deleted VARCHAR2(30)
DECLARE
emp_id emp.empno%TYPE := 7839;
BEGIN
DELETE FROM emp
WHERE empno = emp_id;
:rows_deleted := (SQL%ROWCOUNT || ' row deleted.');
END;
/
PRINT rows_deleted
流程控制
作为一种编程语言，PL/SQL也有自己的流程控制语句。
IF 语句
语法
IF condition THNE
statements;
[ELSEIF condition THEN
statements;]
[ELSE
statements;]
END IF;
CASE 语句
语法
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultn
[ELSE resultN+1]
END;
/
遇到空值时应该注意的问题
- 和空值的比较仍然返回空
- 空值取反仍然为空
- 条件控制语句判断返回空则后继对应语句不执行
逻辑运算表
| AND | TRUE | FALSE | NULL | OR | TRUE | FALSE | NULL | NOT | - |
|---|---|---|---|---|---|---|---|---|---|
| TRUE | TRUE | FALSE | NULL | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE |
| FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | NULL | FALSE | TRUE |
| NULL | NULL | FALSE | NULL | NULL | TRUE | NULL | NULL | NULL | NULL |
循环控制语句
可以用Loops语句控制循环。主要有三种循环语句：
- Basic loop
- FOR loop
- WHILE loop
Basic loop
语法：
LOOP
statement1;
...
EXIT [WHEN condition];
END LOOP;
WHILE 循环
语法：
WHILE condition LOOP
statement1;
statement2;
...
END LOOP;
FOR 循环
按顺序对一系列值进行逐一处理。该语句自动定义了counter变量，需要明确定义循环的起点和终点：'lower_bound .. upper_bound'。
FOR counter IN [REVERSE]
lower_bound..upper_bound LOOP
statement1;
statemnet2;
...
END LOOP;
FOR 循环 Guideline
- counter 仅能在循环体内引用
- 不要对counter 赋值
- 循环判断的边界不可为NULL
- 若语句至少执行一次则使用basic loop
- 每次循环之前先判断则用 while loop
- 循环变量的取值已经确定则用for loop
嵌套循环和标签
循环可以嵌套多层，使用labels来区分不同的程序块和循环层，还可以使用EXIT label来退出外层循环。
- 实例
BEGIN
<<Outer_loop>>
LOOP
counter := counter+1;
EXIT WHEN counter>10
<<Inner_loop>>
LOOP
...
EXIT Outer_loop WHEN total_done = 'YES';
-- Leave both loops
EXIT WHEN inner_done = 'YES';
-- Leave inner loop only
END LOOP Inner_loop;
...
END LOOP Outter_loop;
END;
/
复合数据类型
之前一篇文章介绍过复合数据类型及其分类【1】。 #### PL/SQL record
如果需要一个记录变量存储多个数据类型的值，则使用PL/SQL record
Record
和包括C和C++等其他多数3GL语言类似，Record 由多个部分构成（每个部分称为field），可以是标量（scalar），也可以是表数据类型的索引。可以由用户定义并对应表的数据行。Record将多个字段的值合并起来构成一个逻辑单元，当从表中获取数据的时候很方便。
语法
TYPE type_name IS RECORD
(field_declaration[, field_declaration]);
identifier type_name;
对于field_declaration的定义
filed_name (field_type | variable%TYPE
| table.column%TYPE | table%ROWTYPE)
[[NOT NULL] (:= | DEFAULT) expr]
Record 示意图
record.JPG
%ROWTYPE 属性
通过%ROWTYPE，可以基于表和视图的列定义变量。可以通过reference%ROWTYPE的格式来定义，自动继承其基于的表或者视图的列名和类型。
- 语法
DECLARE
identifier reference%ROWTYPE;
- 优势
定义和使用的时候不必关心可变的列的个数和类型，在plsql中使用SELECT * 语句变得很简单。
使用记录类型更新表
SET SERVEROUTPUT ON
SET VERIFY OFF
SET DEFINE ON;
DEFINE employee_number = 1;
DECLARE
emp_rec employee%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM employee WHERE ENO=&employee_number;
emp_rec.birth_day := SYSDATE;
UPDATE employee SET ROW = emp_rec WHERE eno = &employee_number;
END;
/
SELECT * FROM employee;
其中用到了define来定义变量。
Oracle 提供了几种不同的定义变量的方式: def(ine) ,var(iable), declare 分别适用于不同的环境【2】。
- define
sqlplus 环境(command窗口) 中用于定义变量, 适用于人机交互处理，或者sql脚本。 - variable
plsql 匿名块中使用。非匿名块中不能使用。绑定变量主要是在sql中使用，达到sql解析一次，执行多次，从而提高效率的目的【3】。 - declare
plsql 块中使用，适用于匿名块或者非匿名块。
PL/SQL collections
如果需要集合变量存储同种数据类型多个值则需要用PL/SQL collections：
表索引（Index-By Table）或者 Associative Arrays。
关于associative array的讨论可以参看【4】
The index-by tables available in previous releases of Oracle have been renamed to Associative Arrays in Oracle9i Release 2. In addition to the rename Oracle have added the ability to index-by string values making them significantly more flexible.
在Oracle 9i R2，Index-By Table 被重新命名为 Associative Arrays。除了新的命名，还加入了新的特性：支持 String 类型的索引。
更详细的内容可以参考Oracle官方文档【5】。
这一类复杂数据结构有两个部分：
- Integer或者String数据类型的主键
- 标量或者Record类型的列
定义表索引
- 语法
TYPE type_name IS TABLE OF
(column_type | variable%TYPE
| table.column%TYPE) [NOT NULL]
| table%ROWTYPE
[INDEX BY PLS_INTEGER | BIANRY_INTEGER
| VARCHAR2(<size>)];
identifier type_name;
- 声明表索引以保存employees的姓
...
TYPE ename_table_type IS TABLE OF
employees.last_name%TYPE
INDEX BY PLS_INTEGER;
...
ename_table ename_table_type;
-
示意图
IBT.JPG
- 举例
DECLARE
TYPE ename_table_type IS TABLE OF
employees.last_name%TYPE
INDEX BY PLS_INTEGER;
TYPE hiredate_table_type IS TABLE OF DATE
INDEX BY PLS_INTEGER;
ename_table ename_table_type;
hiredate_table hiredate_table_type;
BEGIN
ename_table(1) := 'CAMERON';
hiredate_table := SYSTEM + 7;
IF ename_table.EXIST(1) THEN
INSERT INTO...
END;
/
- INDEX BY Table 的方法
- EXISTS
- COUNT
- FISRT and LAST
- PRIOR
- NEXT
- DELETE
- 记录类型的INDEX-BY Table
可以定义INDEX-BY Table来存储整个表的全部数据。
实例1
DECLARE
TYPE dept_table_type IS TABLE OF
departments%ROWTYPE
INDEX BY PLS_INTEGER;
dept_table dept_table_type;
-- Each element of dept_table is a record
实例2
SET SERVEROUTPUT ON
DECLARE
TYPE emp_table_type IS TABLE OF
employees%ROWTYPE INDEX BY PLS_INTEGER;
my_emp_table emp_table_type;
max_emp_table emp_table_type;
max_count NUMBER(3) := 104;
BEGIN
FOR i IN 100..max_count
LOOP
SELECT * INTO my_emp_table(i) FROM employees
WHERE employee_id = i;
END LOOP;
FOR i IN my_emp_table.FIRST..my_emp_table.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
END LOOP;
END;
/
嵌入表
可以定义 Nested Tables 来定义表中列，而Index-By Tables 仅可以定义plsql变量。数据在 Nested Tables 中并无固定顺序和编号，类似普通表中的数据，可以删除任意的行。
nt.JPG
- 实例
SET SERVEROUTPUT ON
DECLARE
TYPE location_type IS TABLE OF locations.city%TYPE
offices location_type;
table_count NUMBER;
BEGIN
offices := location_type('Bombay', 'Tokyo', 'Singapore', 'Oxford');
table_count := offices.count();
FOR i in 1..table_count LOOP
DBMS_OUTPUT.PUT_LINE(offices(i));
END LOOP;
END;
/
VARRAY
Variable-size array (VARRAY) 和 PLSQL table 类似，但是容量是定义时限定的，而且可以用于定义表的列，例如：
TYPE location_type IS VARRAY(3) OF locations.city%TYPE;
VARRAY.JPG
【1】Oracle SQL 学习笔记18 - PL/SQL
【2】Oracle 变量 之 define variable declare 用法及区别
【3】Oracle变量定义的三种方式(define,variable,declare)学习笔记
【4】Associative Arrays
【5】https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/05_colls.htm#34012
网友评论