楼主提供的代码中,没有看到进行位或运算的代码,您使用的是 select nvl(date,date2) into date3 from dual;Result:=date3; 这段代码是无法完成位或运算的,建议楼主使用bit_or()函数进行位运算。且由于楼主的位运算是对多条记录进行,而不是两条记录,所以楼主自定义的函数是无法完成该功能的,建议楼主使用游标或者自定聚合函数来实现该功能。
先建立一个工作表,然后 Select distinct ID from A 得到所有的 ID。然后针对每一个ID Select date from A where id = "id" 这样 date的列表就来了,进行位运算。做好位运算以后 把 id 和 date 都塞到 工作表里面去。所有这一切做成以后,就把表A里面的数据都删掉,然后insert info A select * from 工作表。
游标? 麻烦能来点代码吗,初学者 ,多指教。把代码放在 /stackoverflow 上,英语水平不行半天没解释清楚。 ---这个date或这里的kxrq 是varchar2(31),上面的是我从中抽取出的问题,这是实际的环境 while i<=31 loop kxrq:=substr(cs_kxrq,i,1); kxrq2:=substr(cs_kxrq2,i,1); if(kxrq='1'or kxrq2='1')then kxrq3:=kxrq3||one; else kxrq3:=kxrq3||zero; end if; end loop;还这样算过但重要的不在这个或运算 重点在于 查询两个 前者两列相同,最后一列不同的 我在计算完,进行更新,对这两个重复的行?哪一行更新,而且又删除哪一行? 这是头疼的地方。 你说了,要有游标的方法,麻烦能给两段代码看下好不。谢谢了。
http://stackoverflow.com/questions/7035372/pl-sql-procedures-same-row //丢人丢到外面了,英语不行,但是这句好像有点意思 @eaolson So you're trying to remove cases where ID and NAME are duplicated? How do you choose between the duplicates? That's an odd date field. I thought it was a binary number when I looked at it. But most importantly: what is the error message you're getting?
CREATE OR REPLACE TYPE T_LINK AS OBJECT ( STR VARCHAR2(30000), STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER );CREATE OR REPLACE TYPE BODY T_LINK IS STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER IS BEGIN SCTX := T_LINK('0'); RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS BEGIN SELF.STR := utl_raw.bit_or(SELF.STR, VALUE); RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN RETURNVALUE := SELF.STR; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS BEGIN NULL; RETURN ODCICONST.SUCCESS; END; END;CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN VARCHAR2 AGGREGATE USING T_LINK;with temp as(select 1 id, 'zhang' name, '1001' dt from dual union all select 1, 'zhang', '1100' from dual union all select 2, 'wang', '1000' from dual union all select 2, 'wang', '1100' from dual) select id,name,f_link(dt) from temp group by id,name ID NAME F_LINK(DT) 1 zhang 1101 2 wang 1100
create table so7t ( id number, name varchar2(10), data number -- date is a reserved word and can't be used as identifier );-- 1001 insert into so7t values (1, 'zhang', 9); -- 1100 insert into so7t values (1, 'zhang', 12); -- 0001 insert into so7t values (2, 'wang', 1); -- 0010 insert into so7t values (2, 'wang', 2);select * from so7t;/* from http://www.dbsnaps.com/oracle/bitwise-operators-in-oracle/ */ create or replace function bitor (x number, y number) return number is begin return (x+y)-bitand(x,y); end; / show errorscreate or replace procedure solve ( p_id in number ) as type ids_t is table of number; v_ids ids_t; v_result number := 0; begin select data bulk collect into v_ids from so7t where id = p_id; for i in v_ids.first .. v_ids.last loop v_result := bitor(v_result, v_ids(i)); end loop; delete from so7t where id = p_id; insert into so7t values (p_id, 'DIY', v_result); end; /begin solve(1); commit; solve(2); commit; end; /Table before solve: ID NAME DATA ---------- ---------- ---------- 1 zhang 9 1 zhang 12 2 wang 1 2 wang 2Table after solve: ID NAME DATA ---------- ---------- ---------- 1 DIY 13 2 DIY 3 唯独少了点。在表中,我得先找到重复的行,还有我问题没提清楚,误认为那是二进制数,它实际是一个字符串 varchar2(31)
实际上麻烦的地方在于这个运算,而不是判断哪行更新和删除,因为这个不需要判断,因为你这个没有主键,所以可以全部更新,然后重复行只保留一行就可以了。删除重复行的代码如下: delete A where rowid not in (select min(rowid) from A group by id,name,date);
//这是在其他问答网问到的create or replace function bitor (x number, y number) return number is begin return (x+y)-bitand(x,y); end; //这是我写的(相当郁闷的解决方法) while i<=31 loop kxrq:=substr(cs_kxrq,i,1); kxrq2:=substr(cs_kxrq2,i,1); if(kxrq='1'or kxrq2='1')then kxrq3:=kxrq3||one; else kxrq3:=kxrq3||zero; end if; end loop;
那么请注意12楼提供的算法,就已经满足你的要求。12楼使用的就是我所说的自定义聚合函数。 现在你要做的分三步: 1. 使用12楼的代码(可以修改下,F_LINK实在不是一个合适的函数名)创建出聚合函数 2. 执行以下语句进行update UPDATE A set date = (select f_link(date) from A as A2 where A2.ID = A.ID and A2.Name = A.name) 3. 执行以下语句删除重复的记录 delete A where rowid not in (select min(rowid) from A group by id,name,date);
呵呵,谢谢。。 求和的概念是 “或运算” 即1or1=1,1or0=1 ,0or0=0,不知道这样是否正确,就是对日期的一个合并,对应位置有值就是“1”都没值就是“0”
2 没有主键,是个临时表
楼主提供的代码中,没有看到进行位或运算的代码,您使用的是
select nvl(date,date2) into date3 from dual;Result:=date3;
这段代码是无法完成位或运算的,建议楼主使用bit_or()函数进行位运算。且由于楼主的位运算是对多条记录进行,而不是两条记录,所以楼主自定义的函数是无法完成该功能的,建议楼主使用游标或者自定聚合函数来实现该功能。
Select date from A where id = "id" 这样 date的列表就来了,进行位运算。做好位运算以后
把 id 和 date 都塞到 工作表里面去。所有这一切做成以后,就把表A里面的数据都删掉,然后insert info A select * from 工作表。
游标? 麻烦能来点代码吗,初学者 ,多指教。把代码放在 /stackoverflow 上,英语水平不行半天没解释清楚。 ---这个date或这里的kxrq 是varchar2(31),上面的是我从中抽取出的问题,这是实际的环境
while i<=31 loop
kxrq:=substr(cs_kxrq,i,1);
kxrq2:=substr(cs_kxrq2,i,1);
if(kxrq='1'or kxrq2='1')then
kxrq3:=kxrq3||one;
else
kxrq3:=kxrq3||zero;
end if;
end loop;还这样算过但重要的不在这个或运算
重点在于 查询两个 前者两列相同,最后一列不同的
我在计算完,进行更新,对这两个重复的行?哪一行更新,而且又删除哪一行?
这是头疼的地方。
你说了,要有游标的方法,麻烦能给两段代码看下好不。谢谢了。
//丢人丢到外面了,英语不行,但是这句好像有点意思 @eaolson
So you're trying to remove cases where ID and NAME are duplicated? How do you choose between the duplicates? That's an odd date field. I thought it was a binary number when I looked at it. But most importantly: what is the error message you're getting?
(
STR VARCHAR2(30000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER
);CREATE OR REPLACE TYPE BODY T_LINK IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER IS
BEGIN
SCTX := T_LINK('0');
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.STR := utl_raw.bit_or(SELF.STR, VALUE);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SELF.STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN VARCHAR2 AGGREGATE USING T_LINK;with temp as(select 1 id, 'zhang' name, '1001' dt from dual
union all select 1, 'zhang', '1100' from dual
union all select 2, 'wang', '1000' from dual
union all select 2, 'wang', '1100' from dual)
select id,name,f_link(dt) from temp
group by id,name ID NAME F_LINK(DT)
1 zhang 1101
2 wang 1100
create table so7t (
id number,
name varchar2(10),
data number -- date is a reserved word and can't be used as identifier
);-- 1001
insert into so7t values (1, 'zhang', 9);
-- 1100
insert into so7t values (1, 'zhang', 12);
-- 0001
insert into so7t values (2, 'wang', 1);
-- 0010
insert into so7t values (2, 'wang', 2);select * from so7t;/* from http://www.dbsnaps.com/oracle/bitwise-operators-in-oracle/ */
create or replace function bitor (x number, y number)
return number
is
begin
return (x+y)-bitand(x,y);
end;
/
show errorscreate or replace procedure solve (
p_id in number
) as
type ids_t is table of number;
v_ids ids_t;
v_result number := 0;
begin
select data bulk collect into v_ids from so7t where id = p_id; for i in v_ids.first .. v_ids.last loop
v_result := bitor(v_result, v_ids(i));
end loop; delete from so7t where id = p_id; insert into so7t values (p_id, 'DIY', v_result);
end;
/begin
solve(1);
commit; solve(2);
commit;
end;
/Table before solve: ID NAME DATA
---------- ---------- ----------
1 zhang 9
1 zhang 12
2 wang 1
2 wang 2Table after solve: ID NAME DATA
---------- ---------- ----------
1 DIY 13
2 DIY 3
唯独少了点。在表中,我得先找到重复的行,还有我问题没提清楚,误认为那是二进制数,它实际是一个字符串 varchar2(31)
delete A where rowid not in (select min(rowid) from A group by id,name,date);
return number
is
begin
return (x+y)-bitand(x,y);
end;
//这是我写的(相当郁闷的解决方法) while i<=31 loop
kxrq:=substr(cs_kxrq,i,1);
kxrq2:=substr(cs_kxrq2,i,1);
if(kxrq='1'or kxrq2='1')then
kxrq3:=kxrq3||one;
else
kxrq3:=kxrq3||zero;
end if;
end loop;
//他不是二进制,是一个 varchar2(31)是个长字符串,存储了31位的0或1,1表示开行,0表示不开行。晕死这数据是从别的系统得到的。该死的设计。 我只是想把他们合起来,该位置只要有1 就为1。运算就是这样的。
现在你要做的分三步:
1. 使用12楼的代码(可以修改下,F_LINK实在不是一个合适的函数名)创建出聚合函数
2. 执行以下语句进行update
UPDATE A set date = (select f_link(date) from A as A2 where A2.ID = A.ID and A2.Name = A.name)
3. 执行以下语句删除重复的记录
delete A where rowid not in (select min(rowid) from A group by id,name,date);