一、定义标题变量
当编写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;