Oracle SQL 变量

丽5908

　　一、定义标题变量




    当编写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;