现有一个表 shop (shop_no varchar2(15) not null,varchar2(10)other)
偶增加一条记录时,如果shop表当前记录数大于1,则向另外一个表中插入记录,这个定义为shop1.shop1表的定义如下(A varchar2(15) not null,varchar2(15) B not null)插入到shop1的规则是:假设 shop中的有数据 shop_no other
11
12
则shop1中的数据必须是 A B
11 12
12 11如果shop表中再插入shop_no为13的话,则shop1中需再添加如下记录: A B
11 13
12 13
13 11
13 12
不知道怎么实现。有高手么?不盛感激。
偶增加一条记录时,如果shop表当前记录数大于1,则向另外一个表中插入记录,这个定义为shop1.shop1表的定义如下(A varchar2(15) not null,varchar2(15) B not null)插入到shop1的规则是:假设 shop中的有数据 shop_no other
11
12
则shop1中的数据必须是 A B
11 12
12 11如果shop表中再插入shop_no为13的话,则shop1中需再添加如下记录: A B
11 13
12 13
13 11
13 12
不知道怎么实现。有高手么?不盛感激。
insert into shop1
select shop_no,'新的shop_no'
from shop
insert into shop1
select '新的shop_no',shop_no
from shop
insert into shop(shop_no) values('新的shop_no')
create trigger tri_test
before insert on shop
referencing new as new_value
for each row
begin
for i in 1..2 loop
insert into shop1 values(:new_value.shop_no,:new_value.shop_no);
end loop;
commit;
end;
/
偶的意思是插入一条数据必须A和B是不同的shop_no可以理解为阶乘的概念:如果shop有2条记录 则shop1也有2条数据
如果shop有3条记录 则shop1共有6条数据
如果shop有4条记录 则shop1共有24条数据
。
如果shop有n条记录 则shop1有n!条数据删除shop里面的一条记录。则shop1表中的记录也应该删除和该shop_no有关联的记录。
before insert on shop
referencing new as new_value
for each row
declare
cursor cur_temp is select shop_no from shop;
l_temp varchar2(15);
begin
open cur_temp;
loop
fetch cur_temp into l_temp;
insert into shop1 values(:new_value.shop_no,l_temp);
insert into shop1 values(l_temp,:new_value.shop_no);
exit when cur_temp%notfound;
end loop;
close cur_temp;
end;不知道是不是你要的那种结果!
loop
fetch cur_temp into l_temp;
insert into shop1 values(:new_value.shop_no,l_temp);
insert into shop1 values(l_temp,:new_value.shop_no);
exit when cur_temp%notfound;
end loop;
应该是:
loop
fetch cur_temp into l_temp;
exit when cur_temp%notfound;
insert into shop1 values(:new_value.shop_no,l_temp);
insert into shop1 values(l_temp,:new_value.shop_no);
end loop;
感觉你只要建立个视图,你要的信息都在视图里,没必要将这些重复数据再写到数据库中。SQL> select * from kkk;SHOP_NO
--------------------
11
12
13SQL> select a.shop_no,b.shop_no from kkk a ,kkk b where a.shop_no!=b.shop_no;SHOP_NO SHOP_NO
-------------------- --------------------
12 11
13 11
11 12
13 12
11 13
12 136 rows selected
SQL> create or replace view myview (a,b) as select a.shop_no,b.shop_no from kkk a ,kkk b where a.shop_no!=b.shop_no;View createdSQL> select * from myview;A B
-------------------- --------------------
12 11
13 11
11 12
13 12
11 13
12 136 rows selectedSQL> select * from myview where a=13 or b=13;A B
-------------------- --------------------
13 11
13 12
11 13
12 13SQL>
一 有 Informatica经验
二 2年以上相关经验
三 英文良好 至少能英文面试
四 做GE 的项目,要求熟悉informatic,数据仓库存储,数据转换,懂oracle pl/sql,,英文可以工作交流 Siebel工程师:
Siebel technical areas (Configuration/scripting/workflows). We would like to deploy 1/2 sr associate to start with and then gradually train 1-2 exp. Java/PLSQL associate to further build the team.有意者请发中英文简历至[email protected].谢谢!