Oracle中陣列使用方法

在這裡介紹的是Oracle中常量陣列的使用方法，

首先定義一個陣列型別，

它的語法結構：Type 陣列型別名 Is VArray(陣列長庫) Of 陣列成員型別;

之後宣告一個常量陣列，

它的語法結構：常量陣列名 陣列型別名 := 陣列型別名(常量陣列值1,常量陣列值2,..常量陣列值N);

[@more@]

舉例如下：

CREATE OR REPLACE PROCEDURE up_checkUserQueryPower
IS
ssql VarChar2(4096);
iUserNum Integer;
n Integer;
i Integer;
sPower VarChar2(5);
tmpRC PK_VAR.RC;
TYPE T_UserPower IS VARRAY(13) OF VarChar2(5); -- 定義陣列型別T_UserPower
V_VAR T_UserPower := T_UserPower('610','602','603','604','611','612','613','614','615','616','617','618','619'); -- 給陣列賦值
BEGIN
ssql := 'Insert into TBBCUSERSYSPOWER (USERNUM,SYSPOWERCODE) Values(:Variant1,:Variant2)';
open tmpRC for select distinct UserNum from tbbcuser;
loop
Fetch tmpRC Into iUserNum;
exit when tmpRC%notfound;
for i in 1..V_VAR.Count loop
sPower := V_VAR(i);
execute immediate ssql using iUserNum,sPower;
end loop;

end loop;
close tmpRC;
Commit;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END up_checkUserQueryPower;

/