Oracle笔记(5)---复合变量

1、记录（Record）

什么是记录：

记录是由几个相关值构成的复合变量，常用于支持SELECT语句的返回值。使用记录可以将一行数据看成是一个单元进行处理，而不必将每一列单独处理，和面向对象程序设计语言里面的对象或者C语言的结构体类似。

 

记录的申明：

TYPE type_name IS RECORD(

Variable_name datatype[,

Variable_name datatype[,

.....

);

Real_name type_name;

 

例如：

DECLARE

TYPE myrecord IS RECORD(

id number,

name varchar2(14),           //写name dept.dname%TYPE;更准确，但要注意dname的类型必须是PL/SQL支持的类型

loc varchar2(13));

result myrecord;

BEGIN

SELECT deptno,dname,loc INTO result FROM dept WHERE deptno='10';

DBMS_OUTPUT.PUT_LINE('id='||result.id||' name='||result.name||' loc='||result.loc);

END;

 

如果不清楚表中列的具体信息或为了简便，可以使用 表名%ROWTYPE来定义记录，这种方式定义的记录类型和表中的行一致，

注意：

①　在给这种记录赋值的时候，必须包含表中的所有列，不能只选取部分列。SELECT 后必须列出所有列或使用*;

②　这种方式申明的记录由于没有自己定义内部的属性名称，所以默认与表中的列名相 同，而不能使用其他名称。

例如：

DECLARE

myrecord dept%ROWTYPE;

BEGIN

SELECT * INTO MYRECORD FROM dept WHERE deptno=10;

DBMS_OUTPUT.PUT_LINE(myrecord.deptno||'   '||myrecord.dname);

END;

 

 

2、PL/SQL Table

 

(1)什么是PL/SQL表

　　在PL/SQL块中临时使用,如数组一样的对象,不能用它定义表中的字段。PLSQL表可以存储N个数据,所有数据的类型相同。键值(key-value)对应，用来处理单行多列的数据。

　　

(2)PL/SQL表特点：

　　只包含一列或一个主键（一列不代表一个字段，可以是复合类型，如record）

　　列可以是任何标量数据类型

　　不能对列或主键进行命名

　　主键必须是BINARY_INTEGER（二进制整型）类型----9.2之前, 现在可以使用字符型

　　PL/SQL表大小无限制，下标没有上下限,  索引[下标]的整数可不连续,PL/SQL表索引[下标]的整数可为负

 

(3)申明：

TYPE type_name IS TABLE OF

     {column_type | variable%TYPE

     | table.column%TYPE} [NOT NULL]

     | table.%ROWTYPE 

     [INDEX BY BINARY_INTEGER];

 

例如：

DECLARE

TYPE table_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

temp_table table_type;

BEGIN

temp_table(1):='Asa';

temp_table(3):='Becky';

temp_table(-2):='Cooper';

DBMS_OUTPUT.PUT_LINE(temp_table(-2));

DBMS_OUTPUT.PUT_LINE(temp_table(1));

DBMS_OUTPUT.PUT_LINE(temp_table(3));

END;

 

注意：

¶      PL/SQL表索引下标的整数可不连续

¶      PL/SQL表索引下标的整数可为负

¶      但不能使用未定义的索引下标，比如说temp_table(5)

 ¶      PL/SQL表的索引可以定义为任意整数，如不定义，则下标默认由1开始。

¶      COUNT()函数返回PL/SQL表中的元素个数。

¶      当PL/SQL表存储多列时，把datatype改成记录类型或行级变量即可。

¶      FIRST函数返回第一个索引的值。

¶      LAST函数返回最后一个索引的值。

 

PL/SQL表的索引除了可以定义为整数，还可定义为字符串。元素的下表索引按照拼音排序。

例如：

 

DECLARE

TYPE area IS TABLE OF NUMBER INDEX BY VARCHAR2(20);

areal area;

BEGIN

areal('上海'):=1;

areal('广州'):=2

areal('北京'):=3;

DBMS_OUTPUT.PUT_LINE('第一个元素的索引是：'||areal.FIRST);    

DBMS_OUTPUT.PUT_LINE('最后一个元素的索引是：'||areal.LAST);

DBMS_OUTPUT.PUT_LINE('第一个元素的值是：'||areal(areal.FIRST));    

DBMS_OUTPUT.PUT_LINE('最后一个元素的值是：'||areal(areal.LAST));

END;

输出：

第一个元素的索引是：北京                                                        

最后一个元素的索引是：上海                                                     

第一个元素的值是：3                                                            

最后一个元素的值是：1    

 

(4)常用属性/函数

EXISTS(n)   　  如果指定的元素存在返回true,否则返回false.  例如：判断第二个元素是否存在 IF temp_table.EXISTS(2) THEN.....

COUNT       　　返回PLSQL TABLE中元素的数目. 例如：DBMS_OUTPUT.PUT_LINE(temp_table.COUNT);

FIRST、LAST    返回最小/最大索引值.,若PLSQL表为空返回null.   用法见上面的例子

PRIOR(n)   　　 返回索引为n的元素的前一个元素的索引. 例如：获取最后一个元素的前一个元素的索引，areal.PRIOR(areal.LAST)

NEXT(n)     　　返回索引为n的元素的后一个元素的索引. 例如：获取第一个元素之后的下一个元素的索引，areal.NEXT(areal.FIRST)

DELETE(N)　　 删除索引为n的元素。 例如删除索引为3的元素，temp_table.DELETE(3);

DELETE(m,n)    删除索引范围在m到n之间的元素。

EXTEND(n,i) 　 把第i个元素复制n份追加到PLSQl表中

TRIM(n)     　　从PLSQL TABLE 的尾部删除n个元素   

Note Extend and Trim are new to Oracle 8.

 

另一个使用案例：

DECLARE 
TYPE courselist IS TABLE OF VARCHAR2(10)  ;
  courses courselist;
BEGIN
  courses := courselist('Biol 4412', 'Psyc 3112', 'Anth 3001');
  courses.delete(3); -- delete element  
/* PL/SQL keeps a placeholder for element 3. So, the  next statement appends

element 4, not element 3. */
 courses.extend; -- append one null element
  /* Now element 4 exists, so the next statement does
  not raise SUBSCRIPT_BEYOND_COUNT. *；
 courses(4) := 'Engl 2005';
  dbms_output.put_line(courses(1)); 
dbms_output.put_line(courses(2));
  --dbms_output.put_line(courses(3));
  dbms_output.put_line(courses(4));
END;