ORACLE中%TYPE和%ROWTYPE的使用

1. 使用%TYPE

 

在许多情况下，PL/SQL变量可以用来存储在数据库表中的数据。在这种情况下，变量应该拥有与表列相同的类型。例如，students表的first_name列的类型为VARCHAR2(20),我们可以按照下述方式声明一个变量：

 

DECLARE

 

v_FirstName VARCHAR2(20);

 

但是如果first_name列的定义改变了会发生什么（比如说表改变了，first_name现在的类型变为VARCHAR2(25)）？那就会导致所有使用这个列的PL/SQL代码都必须进行修改。如果你有很多的PL/SQL代码，这种处理可能是十分耗时和容易出错的。

 

这时，你可以使用”%TYPE”属性而不是将变量类型硬性编码。

 

例如：

 

DECLARE

 

v_FirstName students.first_name%TYPE;

 

通过使用%TYPE,v_FirstName变量将同students表的first_name列的类型相同（可以理解为将两者邦定起来）。

 

每次匿名块或命名块运行该语句块以及编译存储对象（过程、函数、包、对象类和触发器）时，就会确定该类型。

 

使用%TYPE是非常好的编程风格，因为它使得PL/SQL更加灵活，更加适应于对数据库定义的更新。

 

2. 使用%ROWTYPE

 

2.1 PL/SQL记录

 

PL/SQL记录类型类似于C语言中的结构，是一种复合类型，是用户自定义的。

 

记录提供了一种处理独立的但又作为一个整体单元相关的变量的机制。请看：

 

DECLARE

 

v_StudentID NUMBER(5);

 

v_FirstName VARCHAR2(20);

 

v_LastName VARCHAR2(20);

 

这3个变量在逻辑上是相互关联的，因为他们指向students表中不同的字段。如果为这些变量声明一个记录类型，那么他们之间的关系就十分明显，可作为一个单元进行处理。

 

DECLARE

 

/*Define a record type to hold common student informationi*/

 

TYPE t_StudentRecord IS RECORD(

 

StudentID NUMBER(5),

 

FirstName VARCHAR2(20),

 

LastName VARCHAR2(20);

 

/*Declare a variable of this type.*/

 

v_StudentInfo t_StudentRecord;

 

2.2 记录赋值

 

可以用SELECT语句向记录赋值，这将会从数据库中检索数据并将该数据存储到记录中。注意的是，记录中字段应该和查询结果列表中的字段相匹配。

 

SELECT studentID,firstName,lastName

 

into v_StudentInfo

 

from students where studentID=32;

 

2.3 使用%ROWTYPE

 

在PL/SQL中将一个记录声明为具有相同类型的数据库行的作法是很常见的。PL/SQL提供了%ROWTYPE运算符，使得这样的操作更为方便。

 

例如：

 

DECLARE

 

v_RoomRecord rooms%ROWTYPE;

 

将定义一个记录，该记录中的字段将与rooms表中的列相对应。

 

--数据准备

--组织机构结构表
CREATE TABLE SF_ORG
(
ORG_ID INT NOT NULL, --组织机构主键ID
ORG_NAME VARCHAR2(50),--组织机构名称
PARENT_ID INT--组织机构的父级
)

--一级组织机构
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(1, '一级部门1',0);

--二级部门

INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(2, '二级部门2',1);
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(3, '二级部门3',1);
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(4, '二级部门4',1);

--%TYPE

DECLARE 
  V_ORG_NAME SF_ORG.ORG_NAME%TYPE; --与ORG_NAME类型相同
  V_PARENT_ID SF_ORG.PARENT_ID%TYPE;--与PARENT_ID类型相同
BEGIN
  SELECT ORG_NAME,PARENT_ID INTO V_ORG_NAME,V_PARENT_ID
  FROM SF_ORG SO
  WHERE SO.ORG_ID=&ORG_ID;
  DBMS_OUTPUT.PUT_LINE('部门名称：' || V_ORG_NAME);
  DBMS_OUTPUT.PUT_LINE('上级部门编码：' || TO_CHAR(V_PARENT_ID));
END;

--%ROWTYPE

DECLARE 
  V_SF_ORG_REC SF_ORG%ROWTYPE; --与SF_ORG表中的各个列相同
BEGIN
  SELECT * INTO V_SF_ORG_REC
  FROM SF_ORG SO
  WHERE SO.ORG_ID=&ORG_ID;
  DBMS_OUTPUT.PUT_LINE('部门ID：' || TO_CHAR(V_SF_ORG_REC.ORG_ID));
  DBMS_OUTPUT.PUT_LINE('部门名称：' || V_SF_ORG_REC.ORG_NAME);
  DBMS_OUTPUT.PUT_LINE('上级部门编码：' || TO_CHAR(V_SF_ORG_REC.PARENT_ID));
END;