Oracle SQL 变量

一、定义标题变量
    当编写PL/SQL程序时，如果要引用标量变量，必须首先在定义部分定义标量变量，然后才能在执行部分或例外处里部分中使用这些标量变量。
（1）语法
    在PL/SQ块中定义变量和常量的语法如下：
    identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]
    identifier：用于指定变量或常量的名称
    CONSTANT：用于指定常量。当定义常量时，必须指定它的初始值，并且其数值不能改变。
    datatype：用于指定变量或常量的数据类型。
    NOT NULL：用于强制初始化变量（不能为NULL）。当指定NOT NULL选项时，必须要为变量提供数值。
    :=：用于为变量和常量指定初始值。
    DEFAULT：用于为变量和常量指定初始值。
    expr：用于指定初始值的PL/SQL表达式，可以是文本值、其他变量、函数等。
（2）定义标题变量示例
    v_ename    varchar2(10);
    v_sal    number(6,2);
    v_balance    binary_float; -- oracle 10g新数据类型
    c_tax_rate    constant number(3,2):=5.5;
    v_hiredate    date;
    v_valid        boolean        not null default false;
（3）使用标量变量
    当在定义部分定义了标量变量之后，在执行部分和例外处理分部分可以引用这些标量变量。需要注意，在PL/SQL块中为变量赋值不同于其他编程语言，必须要在等号前加冒号（:=）。

 

declare
        v_ename        varchar2(10);
        v_sal        number(6,2);
        c_tax_rate    constant number(3,2):=5.5;
        v_tax_sal    number(6,2);
    begin
        select ename,sal into v_name,v_sal from emp where empno=&eno;
        v_tax_sal:=c_tax_rate*v_sal;
        dbms_output.put_line('雇员名：'|| v_name);
        dbms_output.put_line('雇员工资：'|| v_sal);
        dbms_output.put_line('所得税：'|| v_tax_sal);
    end;
    /

 

（4）使用%TYPE属性
    当定义PL/SQL变量存放列值时，必须确保变量使用合适的数据类型长度，否则在运行过程中可能会出现PL/SQL运行错误。为了避免这种不必要的错误，可以使用%TYPE属性定义变量。当使用%TYPE属性定义变量时，它会按照数据库列或其他变量来确定新变量的类型和长度。

    declare
        v_ename        emp.ename%TYPE;
        v_sal        emp.sal%TYPE;
        c_tax_rate    constant number(3,2):=5.5;
        v_tax_sal    v_sal%TYPE;
    begin
        select ename,sal into v_name,v_sal from emp where empno=&eno;
        v_tax_sal:=c_tax_rate*v_sal;
        dbms_output.put_line('雇员名：'|| v_name);
        dbms_output.put_line('雇员工资：'|| v_sal);
        dbms_output.put_line('所得税：'|| v_tax_sal);
    end;
    /

 

二、复合变量
    复合变量是指用于存放多个值的变量。当定义复合变量时，必须要使用PL/SQL的复合数据类型。PL/SQL包括PL/SQL记录、PL/SQL表、嵌套表以及VARRAY等4种复合数据类型。
（1）PL/SQL记录
    PL/SQL记录类似于高级语言中的结构，每个PL/SQL记录一般都包含多个成员。当使用PL/SQL记录时，首先需要在定义部分记录类型和记录变量，然后在执行部分引用该记录变量。需要注意，当引用记录成员时，必须要加记录变量作为前缀（记录变量.记录成员）。
    示例如下：

    declare
        TYPE emp_record_type is RECORD
        (
            name emp.ename%TYPE,
            salary emp.sal%TYPE,
            tital emp.job%TYPE
        );
        emp_record emp_record_type; -- 声明变量时，变量在前，类型在后。
    begin
        select ename,sal,job into emp_record from emp where empno=&empno;
        dbms_output.put_line('雇员名：'|| emp_record.name);
    end;
    /

    emp_record_type是PL/SQL的记录类型，并且该PL/SQL记录类型包含了三个成员（name,salary,title）；emp_record是记录的变量；emp_record.name则表示引用记录变量emp_record的成员name。
（2）PL/SQL表
    PL/SQL表类似于高级语言中的数组。需要注意，PL/SQL表与高级语言的数组有所区别，高级语言数组的下标不能为负，但PL/SQL表的下标可以为负值；高级语言数组的元素个数有限制，而PL/SQL表的元素个数没有限制，并且其下标没有上限。当用不用PL/SQL表时，必须首先在定义部分定义PL/SQL表类型和PL/SQL表变量，然后在执行部分中引用该PL/SQL表变量。
    示例如下：

    declare
        TYPE ename_table_type is TABLE OF emp.ename%TYPE
            INDEX BY BINARY_INTEGER;
        ename_table ename_table_type;
    begin
        select ename into ename_table(-1) from emp where empno=&empno;
        dbms_output.put_line('雇员名：'||ename_table(-1));
    end;
    /

    ename_table_type为PL/SQL表类型；emp.ename%TYPE指定了PL/SQL表元素的数据类型和长度；ename_table为PL/SQL表变量，ename_table(-1)则表示下标为-1的元素。
（3）嵌套表
    嵌套表（Nested Table）类似于高级语言中的数组。需要注意，高级语言数组和嵌套表的下标都不能为负值；高级语言的元素个数是有限制的，而嵌套表的元素个数是没有限制的。嵌套表和PL/SQL表非常类似，但嵌套表可以作为表列的数据类型，而PL/SQL表不能作为表列的数据类型。当在表列中使用嵌套表时，必须首先使用CREATE TYPE语句建立嵌套表类型。
    示例如下：

    CREATE OR REPLACE TYPE emp_type as OBJECT
    (
        name varchar(10),salary number(6,2),hiredate date
    );
    /
    CREATE OR REPLACE TYPE emp_array IS TABLE OF emp_type;

    对象类型emp_type用于存储雇员信息，而emp_array是基于emp_type的嵌套表类型，它可以用于存储多个雇员的信息。当建立嵌套表类型之后，就可以在表列或对象属性中将其作为用户自定义数据类型来引用。但需要注意，当使用嵌套表类型作为表列时，必须要为其指定专门的存储表。
    示例如下：

    CREATE TABLE department
    (
        deptno number(2),
        dname varchar(10),
        employee emp_array
    )NESTED TABLE employee STORE AS employee;

（4）VARRAY
    VARRAY（变长数组）类似于嵌套表，它可以作为表列和对象类型属性的数据类型。但需要注意，嵌套表的元素个数没有限制，而VARRAY的元素个数是有限制的。当使用VARRAY时，必须首先建立 VARRAY类型。
    示例如下：

    CREATE OR REPLACE TYPE article_type as OBJECT
    (
        title varchar2(30),pubdate date
    );
    /
    CREATE OR REPLACE TYPE article_array IS VARRAY(20) OF article_type;

    对象类型article_type用于存储文章信息，而article_array则用于存储多篇文章的信息，并且最多可以存储20篇文章。当建立了VARRAY类型之后，可以在或对象属性中将其作为用户自定义数据类型来引用。
    示例如下：

    CREATE TABLE department
    (
        id number(6),
        name varchar2(10),
        article article_array
    );

 

    注意，嵌套表列数据需要存储在专门的存储表中，而VARRAY数据则与其他列数据一起存放在表段中。

三、参照变量
    参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用空间。在编写PL/SQL程序时，可以使用游标变量（REF CURSOR）和对象类型变量 REF obj_type等两种参照变量类型。
（1）REF CURSOR
    当使用显式游标时，需要在定义显式游标时指定相应的SELECT语言，这种显式游标称为静态游标。当使用游标变量时，在定义游标变量时不需要指定SELECT语句，而是在打开游标时指定SELECT语句，从而实现动态的游标操作。
    示例如下：

    declare
        TYPE c1 IS REF CURSOR;
        emp_cursor c1;
        v_ename emp.ename%TYPE;
        v_sal emp.sal%TYPE;
    begin
        OPEN emp_cursor
          FOR SELECT ename,sal FROM emp WHERE deptno=&no;
        LOOP
          FETCH emp_cursor INTO v_ename,v_sal;
          EXIT WHEN emp_cursor%NOTFOUND;
          dbms_output.put_line(v_ename);
        END LOOP;
        CLOSE emp_cursor;
    end;
    /

 

    c1为REF CURSOR类型，而emp_cursor为游标变量，并且在打开游标变量时指定了其所对应的SELECT语句。
（2）REF obj_type
    当编写对象类型应用时，为了共享相同对象，可以使用REF引用对象类型，REF实际是指向对象实例的指针。下面通过示例说明如何使用REF。首先建议对象类型home和对象表homes，然后插入数据。
    示例如下：

    CREATE OR REPLACE TYPE home_type AS OBJECT
    (
        street varchar2(50),
        city varchar2(20),
        state varchar2(10),
        zipcode varchar2(6),
        owner varchar2(10)
    );
    /
    CREATE TABLE homes OF home_type;
    INSERT INTO homes VALUES('呼伦北路12号','呼和浩特','内蒙','010010','马鸣');
    INSERT INTO homes VALUES('呼伦北路13号','呼和浩特','内蒙','010010','秦斌');
    INSERT INTO homes VALUES('呼伦北路14号','呼和浩特','内蒙','010010','马武');
    COMMIT;

    对象表homes存放着家庭所在地以及户主姓名。假定每个家庭有四口人，当进行人口统计时，为了使得同一家庭的每个家庭成员可以共享家庭地址，可以使用REF引用home_type对象类型，从而降低占用空间。
    示例如下：

    CREATE TABLE person
    (
        id number(6) primary key,
        name varchar2(10),
        address REF home_type
    );
    INERT INTO person select 1,'马鸣',ref(p) FROM homes p where p.owner='马鸣';
    INERT INTO person select 1,'马武',ref(p) FROM homes p where p.owner='马武';
    INERT INTO person select 3,'秦斌',ref(p) FROM homes p where p.owner='秦斌';

    当为person表插入数据时，address列将会存放指向homes表相应数据的地址指针。

四、LOB变量
    LOB变量是指用于存储大批量数据的变量。Oracle将LOB分为两种：内部LOB和外部LOB。内部LOB包括CLOB、BLOB和NCLOB有一种类型，它们的数据被存储在数据库中，并且支持事务操作（提交、回退、保存点）。外部LOB只有BFILE一种类型，该类型的数据被存储在OS文件中，并且不支持事务操作。其中，CLOB/NCLOB用于存储大批量字符数据。BLOB用于存储大批量二进制数据。而BFILE则存储指向OS文件的指针。

五、非PL/SQL变量
    当在SQL*PLUS或应用程序（例如Pro、C/C++）中与PL/SQL块之间进行数据交互时，需要使用SQL*PLUS变量或应用程序变量来完成。当在PL/SQL块中引用非PL/SQL变量时，必须要在非PL/SQL变量前加冒号（“:”）。

(1)使用SQL*PLUS变量
    在PL/SQL块中引用SQL*PLUS变量时，必须首先使用VARIABLE命令定义变量；而如果要在SQL*PLUS中输出变量内容，则需要使用PRINT命令。
    示例如下：

    var name varchar2(10);
    begin
        select ename into :name from emp where empno=7788;
    end;
    /
    print name;