Multilevel VARRAY Exampledeclare
type t1 is varray(10) of integer;
type nt1 is varray(10) of t1; -- multilevel varray type
va t1 := t1(2,3,5);
-- initialize multilevel varray
nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va);
i integer;
va1 t1;
begin
-- multilevel access
i := nva(2)(3); -- i will get value 73
dbms_output.put_line(i); -- add a new varray element to nva
nva.extend;
nva(5) := t1(56, 32); -- replace an inner varray element
nva(4) := t1(45,43,67,43345); -- replace an inner integer element
nva(4)(4) := 1; -- replaces 43345 with 1 -- add a new element to the 4th varray element
-- and store integer 89 into it.
nva(4).extend;
nva(4)(5) := 89;
end;
type t1 is varray(10) of integer;
type nt1 is varray(10) of t1; -- multilevel varray type
va t1 := t1(2,3,5);
-- initialize multilevel varray
nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va);
i integer;
va1 t1;
begin
-- multilevel access
i := nva(2)(3); -- i will get value 73
dbms_output.put_line(i); -- add a new varray element to nva
nva.extend;
nva(5) := t1(56, 32); -- replace an inner varray element
nva(4) := t1(45,43,67,43345); -- replace an inner integer element
nva(4)(4) := 1; -- replaces 43345 with 1 -- add a new element to the 4th varray element
-- and store integer 89 into it.
nva(4).extend;
nva(4)(5) := 89;
end;
解决方案 »
- product_user_profile权限设置无效
- 同一数据库中有两个表A和B,表A有一个字段t1中有10个数字,表B中字段t2有5个数据,查询语句实现数据相加减
- oracle触发器如何得到被更新的数据
- jdbc连接oracle数据库11.1失败
- 请各位帮帮我看一个存储过程
- 請大家來看看這個問題
- 问题:同时有多个数据库时,sqlplus和sqlloader默认的是哪个数据库
- spotlight for sqlserver和SQL Expert for SQL server的序列号谁有?
- 安装时,主机名,端口号,服务名怎么填?
- Oracle中一个存储过程最大能有多大?
- 急,请问用oracle10g 客户端能连上oracle8吗
- oracle 恢复
type tb1 is table of varchar2(20);
type ntb1 is table of tb1; -- table of table elements
type tv1 is varray(10) of integer;
type ntb2 is table of tv1; -- table of varray elements
vtb1 tb1 := tb1('one', 'three');
vntb1 ntb1 := ntb1(vtb1);
vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3)); -- table of varray elements
begin
vntb1.extend;
vntb1(2) := vntb1(1); -- delete the first element in vntb1
vntb1.delete(1); -- delete the first string from the second table in the nested table
vntb1(2).delete(1);
end;
type tb1 is table of integer index by binary_integer; -- the following is index-by table of index-by tables
type ntb1 is table of tb1 index by binary_integer;
type va1 is varray(10) of varchar2(20); -- the following is index-by table of varray elements
type ntb2 is table of va1 index by binary_integer;
v1 va1 := va1('hello', 'world');
v2 ntb1;
v3 ntb2;
v4 tb1;
v5 tb1; -- empty table
begin
v4(1) := 34;
v4(2) := 46456;
v4(456) := 343;
v2(23) := v4;
v3(34) := va1(33, 456, 656, 343); -- assign an empty table to v2(35) and try again
v2(35) := v5;
v2(35)(2) := 78; -- it works now
end;
return varchar2
/*****************************************************/
/* 功能:由时间获取值别 */
/*****************************************************/
is
strShift varchar2(2);
strDate varChar(10);
intDays number;
strResult varchar2(2);
arrCity(8,7) varchar2(1);
begin
arrCity(1,1) :='*';
arrCity(1,2) :='N';
arrCity(1,3) :='M';
arrCity(1,4) :='T';
arrCity(1,5) :='A';
arrCity(1,6) :='*';
arrCity(2,1) :='*';
arrCity(2,2) :='*';
arrCity(2,3) :='N';
arrCity(2,4) :='T';
arrCity(2,5) :='M';
arrCity(2,6) :='A';
if DatePara is Null then return Null ; end if ;
strDate := To_Char(DatePara,'YYYY-MM-DD');
if (DatePara >To_Date(strDate||' 07:00:00','YYYY-MM-DD hh24:mi:ss'))
and (DatePara <To_Date(strDate||' 14:30:00','YYYY-MM-DD hh24:mi:ss'))
then
strShift := 'M';
else if (DatePara >To_Date(strDate||' 14:30:00','YYYY-MM-DD hh24:mi:ss'))
and ( DatePara <To_Date(strDate||' 22:00:00','YYYY-MM-DD hh24:mi:ss'))
then
strShift := 'A';
else
strShift := 'N';
end if;
end if ;
intDays := Trunc(DatePara-To_Date('1990-1-8','YYYY-MM-DD'));
intDays := Mod(intDays,42);
intDays := Ceil(intDays/7);
if strShift='M' Then Return 'T'||Lpad(intDays,1,'0'); End if ;
intDays := intDays-1;
if intDays <= 0 then intDays := intDays + 6; end if ;
if strShift='A' then return 'T'||Lpad(intDays,1,'0'); end if ;
intDays := intDays-1;
if intDays <= 0 then intDays := intDays + 6; end if ;
return 'T'||Lpad(intDays,1,'0') ;
end Test;
定义的就不正确,看看上边的例子吧
错误信息:
create or replace function Test(DatePara in Date)
return varchar2
/*****************************************************/
/* 功能:由时间获取值别 */
/*****************************************************/
is
strShift varchar2(2);
strDate varChar(10);
intDays number;
strResult varchar2(2);
type aCity is array(8) of varchar2(1);
type arrCity is array(7) of acity;
begin
--arrCity(1,1) :='*';
--arrCity(1,2) :='N';
--arrCity(1,3) :='M';
--arrCity(1,4) :='T';
--arrCity(1,5) :='A';
--arrCity(1,6) :='*';
--arrCity(2,1) :='*';
--arrCity(2,2) :='*';
--arrCity(2,3) :='N';
--arrCity(2,4) :='T';
--arrCity(2,5) :='M';
--arrCity(2,6) :='A';
return 'T'||Lpad(intDays,1,'0') ;
end Test;
type aCity is array(8) of varchar2(1);
type tarrCity is array(7) of acity; arrCity tarrCity:=tarrCity();
begin
arrCity.extend(7);
for i in 1..7 loop
arrCity(i):=acity();
arrCity(i).extend(8);
end loop;
arrCity(1)(1) :='*';
arrCity(1)(2) :='N';
arrCity(1)(3) :='M';
arrCity(1)(4) :='T';
arrCity(1)(5) :='A';
arrCity(1)(6) :='*';
arrCity(2)(1) :='*';
arrCity(2)(2) :='*';
arrCity(2)(3) :='N';
arrCity(2)(4) :='T';
arrCity(2)(5) :='M';
arrCity(2)(6) :='A';
end;
Compilation errors for FUNCTION LPOMS.TESTError: PLS-00535: VARRAY 类型不能包含 NESTED TABLE,VARRAY 或 LOB
Line: 12
Text: type tarrCity is array(7) of acity;Error: PL/SQL: Item ignored
Line: 12
Text: type tarrCity is array(7) of acity;