头几天有人问了一个问题:
--创建类型minutiae
create OR REPLACE type minutiae_t as object(x number(3,0),y number(3,0),t varchar2(10),theta number(6,2));
/
--创建类型minutiaeset
create OR REPLACE type minutiaeset_t as table of minutiae_t;
/
--创建表tb_f
create table tb_f(fingerId NVARCHAR2(50),minutiaeSet minutiaeset_t)
nested table minutiaeSet store as minutiaeSettab;
/
--创建过程,向表中插入数据
create or replace procedure insert_into_minutiaeset_t
as
mydata minutiaeset_t;
begin
mydata:=minutiaeset_t(minutiae_t(1,3,'end',2.1));
insert into tb_f values('102.bmp',mydata);
end;
/
--更新嵌套表数据
declare
mydata minutiaeset_t;
begin
mydata:=minutiaeset_t(minutiae_t(1,3,'end',2.1));
update tb_f set minutiaeSet=mydata where fingerId='102.bmp';
end;
/
问题是如何根据查询条件在嵌套表中追加数据?例如,想要在fingerId='102.bmp'的一行中,追加特征点数据。
--------------------------
以我的能力可以给出两种解决方法:
--方法1:
select * from table(select minutiaeset from tb_f where fingerid='102.bmp');
X Y T THETA
---- ---- ---------- --------
1 3 end 2.10
insert into table(select minutiaeset from tb_f where fingerid='102.bmp') values(minutiae_t(5,7,'start',3));
1 row inserted
select * from table(select minutiaeset from tb_f where fingerid='102.bmp');
X Y T THETA
---- ---- ---------- --------
1 3 end 2.10
5 7 start 3.00
--方法2:
CREATE OR REPLACE PROCEDURE proc_append_minutiaeset_t(newvalue minutiae_t,search_value VARCHAR2)
AS
temp_nested_table minutiaeset_t:=minutiaeset_t();
BEGIN
SELECT minutiaeset INTO temp_nested_table FROM tb_f WHERE fingerid=search_value;
temp_nested_table.extend;
temp_nested_table(temp_nested_table.count):=newvalue;
update tb_f set minutiaeSet=temp_nested_table where fingerId=search_value;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception!');
END;
/
exec proc_append_minutiaeset_t(minutiae_t(2,2,'mid',2),'102.bmp')
SQL> select * from table(select minutiaeset from tb_f where fingerid='102.bmp');
X Y T THETA
---- ---- ---------- --------
1 3 end 2.10
5 7 start 3.00
2 2 mid 2.00
不过,用嵌套表作为数据的永久存储机制好像并不能得到什么好处。我认为是这样。