Oracle SQL 学习笔记19 - Cursor 及流程控

2020-02-07 本文已影响0人赵阳_c149

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

Oracle SQL 学习笔记19 - Cursor 及流程控

Cursor

Implicit Cursor的属性

应用举例

流程控制

IF 语句

CASE 语句

遇到空值时应该注意的问题

逻辑运算表

循环控制语句

Basic loop

WHILE 循环

FOR 循环

FOR 循环 Guideline

嵌套循环和标签

复合数据类型

Record

语法

Record 示意图

%ROWTYPE 属性

使用记录类型更新表

PL/SQL collections

表索引（Index-By Table）或者 Associative Arrays。

定义表索引

嵌入表

VARRAY

猜你喜欢

热点阅读

Oracle SQL 学习笔记19 - Cursor 及 流程控

Cursor

Implicit Cursor的属性

应用举例

流程控制

IF 语句

CASE 语句

遇到空值时应该注意的问题

逻辑运算表

循环控制语句

Basic loop

WHILE 循环

FOR 循环

FOR 循环 Guideline

嵌套循环和标签

复合数据类型

Record

语法

Record 示意图

%ROWTYPE 属性

使用记录类型更新表

PL/SQL collections

表索引（Index-By Table）或者 Associative Arrays。

定义表索引

嵌入表

VARRAY

猜你喜欢

热点阅读

Oracle SQL 学习笔记19 - Cursor 及流程控