Oracle SQL 学习笔记18 - PL/SQL

PL/SQL Developer是一个集成开发环境，专门开发面向Oracle数据库的应用。PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算【1】。

使用Variables

Variables可以用于历史数据存储，管理存储的值和重复使用。

定义和初始化PL/SQL Variables

语法

identifier  [CONSTANT]  datatype  [NOT NULL]
  [:= | DEFAULT expr];

实例1：

DECLARE
  emp_hiredate  DATE
  emp_deptno  NUMBER(2)  NOT NULL  :=  10;
  location  VARCHAR2(13)  :=  'Atlanta';
  c_comm  CONSTANT NUMBER  :=  1400;

实例2:

SET SERVEROUTPUT ON
DECLARE
    Myname VARCHAR2(20);
BEGIN
    DBMS_OUTPUT.PUT_LINE('My name is: ' || Myname);
    Myname := 'John';
    DBMS_OUTPUT.PUT_LINE('My name is: ' || Myname);   
END;

定义和初始化的一般规则

1. 遵循统一的命名规则
2. 使用有意义的名称
3. NOT NULL 和 CONSTANT 变量必须在定义时初始化
4. 用赋值操作符 (:=) 或者 DEFAULT 关键词初始化

Myname  VARCHAR2(20)  :=  'John'; 
Myname  VARCHAR2(20)  DEFAULT  'John'; 

5. 名行定义一个变量
6. 不用使用列名当作变量名

DECLARE
    eno NUMBER(6);
BEGIN
    SELECT  eno
    INTO    eno
    FROM    EMPLOYEE
    WHERE   EMPLOYEE.ENAME = 'Ross';
END;

7. 当变量必须有值时则使用 NOT NULL 约束。

plsql基本数据类型

• CHAR [(maximum_length)]
• VARCHAR2 (maximum_length)
• LONG
• LONG RAW
• NUMBER [(precision, scale)]
• BINARY_INTEGER
  从使用范围上讲
  INTEGER 是一个SQL数据类型，可以用在定义表时，也可以用在PL/SQL中。BINARY_INTEGER 是一个PL/SQL数据类型，只能用在PL/SQL中。
  从表示内容上讲
  INTEGER是ORACLE的NUMBER类型的子类型，可以简写为INT，是为了兼容ANSI/ISO和IBM的数据类型而定义的。它的存储范围很大可以达精度为38位，数量级为127位的整数(包括整数与负数)。当定义一个列的类型为INTEGER时，实际上数据库自动将该列的类型转换为NUMBER(38)。
  BINARY_INTEGER 表示一个有符号整数，表示的范围为-2的31次方到2的31次方。它有几个子类型，如NATURAL 表示自然数，POSITIVE表示正整数。从表示的范围来讲，BINARY_INTEGER是INTEGER的子集【3】。
• PLS_INTEGER
  BINARY_INTEGER 和 PLS_INTEGER 都是整数类型。BINARY_INTEGER类型变量值是由Oracle来执行，不会出现溢出，但是执行速度较慢，因为他是由Oracle模拟执行。而 PLS_INTEGER 的执行是由硬件即直接由CPU来运算，因而会出现溢出，但其执行速度较前者快许多。【2】
• BOOLEAN
• BINARY_FLOAT (IEEE 754)
• BINARY_DOUBLE (IEEE 754)
• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE
• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND

%TYPE 属性

通过%TYPE属性，可以基于数据库列的定义或者其他已经定义的变量的类型来定义变量。
语法：

identifier  table.column_name%TYPE

实例：

...
emp_lname  employees.last_name%TYPE
balance  NUMBER(7,2);
min_balance  balance%TYPE  :=  1000;
...

复合数据类型

Oracle--plsql复合数据类型的变量称为复合组合变量，也叫做组合变量，在复合变量中包括多个内部组件，每个组件都可以单独存放值，因此一个复合组合变量也叫做组合变量，在复合变量中包括多个内部组件，每个组件都可以单独存放值，因此一个复合变量可以存放多个值。
复合变量类型不是数据库中已经存在的数据类型，因此复合变量在声明类型之前，首先要先创建复合类型，复合类型创建后可以多次使用，以便定义多个复合变量【4】。

复合数据类型分类

复合数据类型主要分为以下几类：

1. 单行多列的数据处理，使用记录
2. 多行单列的数据处理，使用索引表、嵌套表、VARRAY
3. 多行多列的数据处理，CURSOR

记录

• 语法

           /*定义一个record类型*/
           type record类型名称 is record(
                属性  数据类型,
                属性  数据类型
                ...
           );
           /*使用record类型名称定义一个record变量*/
           record变量名   record类型名称

或者使用表的字段来定义

           /*使用表的字段类型定义一个record类型*/
           type record类型名称 is record(
                属性  表名.属性名%type,
                属性  表名.属性名%type
                ...
           );
           /*使用record类型名称定义一个record变量*/
           record变量名   record类型名称

• 实例：

SET SERVEROUTPUT ON
declare
    type type_emp_record is record(
         ename emp.ename%type,
         sal   emp.sal%type
    );
    emp_record type_emp_record;
begin
    select ename,sal into emp_record from emp where empno='7839';
    dbms_output.put_line(emp_record.ename || ':' || emp_record.sal);
end;

索引表、嵌套表和VARRAY

• 语法

/*定义一个table类型*/
type    table类型名  is  table  of  元素类型名    index  by  binary_integer;
/*定义一个table变量*/
变量名      table类型名;

访问数据的方式：

table变量（下标）:=值；

• 实例-索引表

SET SERVEROUTPUT ON
declare
    type emp_table_type is table of varchar2(40) index by binary_integer;
    emp_table emp_table_type;
begin
    select ename into emp_table(0) from emp where emp.empno='7839';
    dbms_output.put_line(emp_table(0));
end;

• 实例-嵌套表

SET SERVEROUTPUT ON
declare
    type emp_table_record is table of varchar2(40);
    emp_table emp_table_record := emp_table_record('tom','tom'); 
begin
    select ename into emp_table(2) from emp where empno='7839';
    dbms_output.put_line(emp_table(1)||emp_table(2));
end;

• 实例-VARRAY

SET SERVEROUTPUT ON
declare
    type emp_varray_record is varray(10) of varchar2(40);
    emp_varray emp_varray_record := emp_varray_record('tom','tom');
begin
    select ename into emp_varray(2) from emp where empno='7839';
    dbms_output.put_line(emp_varray(1)||emp_varray(2));
end;

记录表 和 CURSOR

• 记录表实例

SET SERVEROUTPUT ON
declare
    type emp_record_type is record(
         ename emp.ename%type,
         sal   emp.sal%type
    );
    type emp_table_type is table of emp_record_type index by binary_integer;
    emp_table emp_table_type;
begin
    select ename,sal into emp_table(0) from emp where sal='600';
    select ename,sal into emp_table(1) from emp where sal='700';
    dbms_output.put_line(emp_table(0).ename||':'||emp_table(0).sal);
    dbms_output.put_line(emp_table(1).ename||':'||emp_table(1).sal);
end;

• CURSOR 语法

/*声明游标*/
cursor   游标名   is  select语句;
/*打开游标*/
open   游标名;
/*提取数据，将结果存储到变量中，一般都是record类型*/
fetch   游标名   into  变量;
/*关闭游标*/
close 游标名

• CURSOR属性
  % ISOPEN 判断游标是否打开，若打开就返回true，否则就返回false。
  % ROWCOUNT 当前游标指针的偏移量。
  %FOUND 如果游标提取到新数据，就返回true，否则就返回false。
  %NOTFOUND 如果游标没有提取到新数据，就返回true，否则就返回false。
• CURSOR实例

declare  
    /*使用emp创建一个游标类型*/
    cursor   empcursor   is  select  empno, ename, sal from emp;
    /*使用游标创建一个record变量*/
    var_emp   empcursor%rowtype;
    begin
        open    empcursor;
        loop 
                fetch  empcursor  into var_emp;
                   /* 如果没有提取到 新数据  则结束循环 */
                exit     when     empcursor%notfound;
                dbms_output.put_line(var_emp.empno ||':'||var_emp.ename||':'||var_emp.sal);
        end  loop;
        close    empcursor;
end;

index by binary_integer

如语句：

type  numbers  is table of number index by binary_integer;

其作用是，加了index by binary_integer后，numbers类型的下标就是自增长，numbers类型在插入元素时，不需要初始化，不需要每次extend增加一个空间。
而如果没有这句话index by binary_integer，那就得要显示对其初始化，且每插入一个元素到numbers类型的table中时，都需要先extend【5】。

LOB类型变量

LOB.JPG

像Oracle这种关系型数据库，比较擅长处理结构化的数据，那么对于非结构化的数据，Oracle是怎么处理和存储的呢？Lob (Large Object)是Oracle专门用来处理半结构化和非结构化数据，它是一个大对象数据类型，可以存储超过4000字节的字符串、二进制数据。

Lob的类型

有两种Lob，分别是Internal Lob和External Lob。所谓Internal Lob是指Lob数据存储在Oracle数据文件里，External Lob是指Lob数据存储在数据库外部的操作系统里。

Internale Lob包括：
1）CLOB (Character Lob）：用于存储字符串类型的Lob，如文本和XML文件等，字符串已数据库字符集编码；
2）BLOB (Binary Lob）：用于存储二进制类型的Lob；
3）NCLOB (National Character Lob）：和CLOB类似，但字符串是国家字符集编码。

External Lob包括：
1）BFILE (Binary File Lob）：数据库存储一个执行外部文件的指针，所以它是只读的。【6】

代码注释

在PL/SQL中，有两种注释方式：

1. 双短线：--
2. /* */

PL/SQL中的SQL语句

SELECT

在PL/SQL脚本中可以使用SELECT语句。使用SELECT语句的同时，需要使用INTO子句，还要保证查询返回且仅返回一行数据，否则报错。

SET SERVEROUTPUT ON
DECLARE
  fname VARCHAR2(25)
BEGIN
  SELECT  first_name  INTO  fname
  FROM  employees WHERE  employee_id=200
  DBMS_OUTPUT.PUT_LINE(' First Name is : ' ||fname);
END;

DML

类似的，PL/SQL中也可以执行DML语句（INSERT，UPDATE，DELETE，MERGE）。

变量的可视范围

SCOPE.JPG
SCOPE2.JPG

【1】PL/SQL
【2】Binary_Integer和Pls_Integer的区别
【3】BINARY_INTEGER和INTEGER的区别
【4】Oracle--plsql复合数据类型
【5】Oracle中index by binary_integer的作用
【6】Oracle Lob介绍

关于复合数据类型，参考了以下文章：
PLSQL复合数据类型
PLSQL 复合类型数据
尤其是关于CURSOR，参考：
【Oracle】Oracle中复合数据类型