oracle 有数组类型码,Oracle数组集合类型

集合可以有三种实现方式：

1 自定义一个TYPE使用VARRAY来得到一个数组但只能对基本类型定义如：

CREATE TYPE 类型名 AS VARRAY(100) OF VARCHAR2(20)；

不能使用如下：

CREATE TYPE 类型名 AS VARRAY(100) OF 表名%ROWTYPE；

注意：使用VARRAY时一定要先指定数组大小

不然搞创建数组类型

2 内嵌表如：

TYPE 类型名 IS TABLE OF 具体类型如：(表名%ROWTYPE)；

内嵌表数组分二种：Index_by表同嵌套表如上的就是嵌套表而Index_by表只要在其尾回上

INDEX BY BINARY_INTEGER就可以了

嵌套表的元素可以是集合,注意赋值的时候是varray_element.record_column := 的形式.

除了构造函数外,集合还有很多内建函数，按照面向对象编成的叫法称之为方法。

方法==========描述=================================================================使用限制

COUNT=========返回集合中元素的个数

DELETE========删除集合中所有元素

DELETE(x)=====删除元素下标为x的元素================================================对VARRAY非法

DELETE(x,y)===删除元素下标从X到Y的元素=============================================对VARRAY非法

EXIST(x)======如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE

EXTEND========在集合末尾添加一个元素===============================================对Index_by非法

EXTEND(x)=====在集合末尾添加x个元素================================================对Index_by非法

EXTEND(x,n)===在集合末尾添加元素n的x个副本=========================================对Index_by非法

FIRST=========返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。

LAST==========返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT.

LIMIT=========返回VARRY集合的最大的元素个数===============================Index_by集合和嵌套表无用

NEXT(x)=======返回在第x个元素之后及紧挨着它的元素的值，如果x是最后一个元素，返回null.

PRIOR(x)======返回在第x个元素之前紧挨着它的元素的值，如果x是第一个元素，则返回null。

TRIM==========从集合末端开始删除一个元素========================================对于index_by不合法

TRIM(x)=======从集合末端开始删除x个元素========================================对index_by不合法

********************************************************************************************

记录可以定义为：

TYPE 类型名 IS RECORDER (具休类型)

也可用：变量名 表名%ROWTYPE

例子：

隐式定义记录中，我们不用描述记录的每一个域，在声明记录变量时使用%ROWTYPE命令定义与数据库表，视图，游标有相同结构的记录。

有一些PL/SQL指令在使用隐式定义记录时没有使用%ROWTYPE属性，比如游标FOR循环或触发器中的:old和:new记录

3. 综合实例BULK COLLECT的用法

通过上面的测试和分析，我们可以看到Bulk

Collect批查询在某种程度上可以提高查询效率，它首先将所需数据读入内存，然后再统计分析，这样就可以提高查询效率。

但是，如果Oracle数据库的内存较小，Shared Pool Size不足以保存Bulk

Collect批查询结果，那么该方法需要将Bulk Collect的集合结果保存在磁盘上，在这种情况下，

Bulk Collect方法的效率反而不如其他两种方法，有兴趣的读者可以进一步测试。

另外，除了Bulk

Collect批查询外，我们还可以使用FORALL语句来实现批插入、删除和更新，这在大批量数据操作时可以显著提高执行效率。

--------------------------------------------------------

--DEMO1

--------------------------------------------------------

set serveroutput on;

DECLARE

TYPE

CourseList IS TABLE OF VARCHAR2(10);

COURSES

COURSELIST;

BEGIN

-- initiate

oracle array, the size is 3

courses :=

CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');

-- add one

more element at end of array, value is null

courses.EXTEND;

-- set value

for the 4th element. if we didn't EXTEND the array, it will pop up

error.

courses(4)

:= 'Engl 2005';

DBMS_OUTPUT.PUT_LINE('courses total count is : ' ||

COURSES.COUNT);

for i IN

1..COURSES.COUNT LOOP

DBMS_OUTPUT.PUT_LINE('the ' || i || ' of courses is : ' ||

COURSES(i));

END

LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('ERROR CODE : ORA' || SQLCODE || ', SQL ERROR

: ' || SQLERRM );

RAISE;

END;

/

--------------------------------------------------------

--DEMO2

--------------------------------------------------------

--VARRAY must initiate the size 100

CREATE OR REPLACE TYPE ARRARY_TYPE AS VARRAY(4) OF

VARCHAR2(30);

/

--TABLE needn't to initiate the size

CREATE OR REPLACE TYPE TABLE_TYPE AS TABLE OF

VARCHAR2(30);

/

set serveroutput on;

DECLARE

ARRARY_T ARRARY_TYPE;

TABLE_T TABLE_TYPE;

i smallint := 1;

BEGIN

-- initiate

oracle array, the size is 3

ARRARY_T :=

ARRARY_TYPE('Biol 4412', 'Psyc 3112', 'Anth 3001');

TABLE_T :=

TABLE_TYPE('Biol 4412', 'Psyc 3112', 'Anth 3001');

-- add one

more element at end of array, value is null

-- it will

not extend the size for ARRARY_T

ARRARY_T.EXTEND;

TABLE_T.EXTEND;

-- set value

for the 4th element. if we didn't EXTEND the array, it will pop up

error.

ARRARY_T(4)

:= 'Engl 2005';

TABLE_T(4)

:= 'Engl 2005';

DBMS_OUTPUT.PUT_LINE('ARRARY_T total count is : ' ||

ARRARY_T.COUNT);

DBMS_OUTPUT.PUT_LINE('TABLE_T total count is : ' ||

ARRARY_T.COUNT);

WHILE i

<= TABLE_T.COUNT LOOP

DBMS_OUTPUT.PUT_LINE('the ' || i || ' of ARRARY_T is : ' ||

ARRARY_T(i));

DBMS_OUTPUT.PUT_LINE('the ' || i || ' of TABLE_T is : ' ||

TABLE_T(i));

i := i+1;

END

LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('ERROR CODE : ORA' || SQLCODE || ', SQL ERROR

: ' || SQLERRM );

RAISE;

END;

/

--------------------------------------------------------

--DEMO3

--------------------------------------------------------

------------

--DEMO3-1

------------

set SERVEROUTPUT on;

DECLARE

type

RECORD_TYP is RECORD (

TXNREF TABLE_TEST.TRANSACTIONREF%type,

TXNDESC TABLE_TEST.trandesc%TYPE

);

type

RECORD_ARRARY is table of RECORD_TYP;

P_RECORD

RECORD_ARRARY;

CURSOR

cur_acctmvmt is select transactionref, trandesc

from TABLE_TEST;

begin

open

CUR_ACCTMVMT;

FETCH

CUR_ACCTMVMT BULK COLLECT into P_RECORD;

CLOSE

CUR_ACCTMVMT;

DBMS_OUTPUT.PUT_LINE('TOTAL COUNT : ' || P_RECORD.COUNT);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('ERROR CODE : ORA' || SQLCODE || ', SQL ERROR

: ' || SQLERRM );

RAISE;

END;

/

------------

--DEMO3-2

------------

set SERVEROUTPUT on;

DECLARE

type

RECORD_TYP is RECORD (

TXNREF TABLE_TEST.TRANSACTIONREF%type,

TXNDESC TABLE_TEST.trandesc%TYPE

);

type

RECORD_ARRARY is table of RECORD_TYP index by binary_integer;

P_RECORD

RECORD_ARRARY;

begin

select

transactionref, trandesc BULK COLLECT into P_RECORD from

TABLE_TEST;

DBMS_OUTPUT.PUT_LINE('TOTAL COUNT : ' || P_RECORD.COUNT);

for I in

1..100 LOOP

DBMS_OUTPUT.PUT_LINE('TXNREF : ' || P_RECORD(I).TXNREF || ',

TXNDESC' || P_RECORD(I).TXNDESC);

end

LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('ERROR CODE : ORA' || SQLCODE || ', SQL ERROR

: ' || SQLERRM );

RAISE;

end;

/

--------------------------------------------------------

--DEMO4

--------------------------------------------------------

create or replace

PROCEDURE LOAD_TABLE_TEST_KEY(

REF_ID OUT LOAD_ID_TTYP

)

AS

BEGIN

REF_ID := LOAD_ID_TTYP();

FOR i IN (SELECT ROWIDTOCHAR(ROWID) AS ID FROM

TABLE_TEST t where rownum <= 1000000) LOOP

REF_ID.extend;

--

ARRARY.count means get the total elements of array

REF_ID(REF_ID.count) := i.id;

END LOOP;

END;

/

SHOW ERRORS;

/