这是用存储过程写的: create or replace procedure sp_copy(sRID in VARCHAR2) is sID1 VARCHAR2(40); sID2 VARCHAR2(40); --sEXQMKFCD VARCHAR2(128); begin select max(id+1) into sID1 FROM ADMIN_YUCTDFFSFCSBINFO; --复制 记录到ADMIN_YUCTDFFSFCSBINFO,这里我复制时省略了一些字段,你可以自己加,可以用下面的语句得到每个字段的值 --SELECT EXQMKFCD,EXQMKDT,.... INTO sEXQMKFCD,sEXQMKDT,....from ADMIN_YUCTDFFSFCSBINFO where rid=srid; --注意,sEXQMKFCD,sEXQMKDT,....等参数必须在前面is下定义好的 INSERT INTO ADMIN_YUCTDFFSFCSBINFO(ID,RID,FLAG) VALUES(sID1,sRID,'0'); commit;
select max(id+1) into sID2 FROM ADMIN_YUCTDFFSFCSLIST; --复制 记录到ADMIN_YUCTDFFSFCSLIST INSERT INTO ADMIN_YUCTDFFSFCSLIST(ID,PID) VALUES(sID2,sRID); commit; end sp_copy;
上面写错了,还有一个更新 create or replace procedure sp_copy(sRID in VARCHAR2) is sID1 VARCHAR2(40); sID2 VARCHAR2(40); --sEXQMKFCD VARCHAR2(128); begin select max(id+1) into sID1 FROM ADMIN_YUCTDFFSFCSBINFO; --复制 记录到ADMIN_YUCTDFFSFCSBINFO,这里我复制时省略了一些字段,你可以自己加,可以用下面的语句得到每个字段的值 --SELECT EXQMKFCD,EXQMKDT,.... INTO sEXQMKFCD,sEXQMKDT,....from ADMIN_YUCTDFFSFCSBINFO where rid=srid; --注意,sEXQMKFCD,sEXQMKDT,....等参数必须在前面is下定义好的 INSERT INTO ADMIN_YUCTDFFSFCSBINFO(ID,RID,FLAG) VALUES(sID1,sRID,'1'); commit; update ADMIN_YUCTDFFSFCSBINFO set FLAG='0' where rid=sRID; COMMIT;
select max(id+1) into sID2 FROM ADMIN_YUCTDFFSFCSLIST; --复制 记录到ADMIN_YUCTDFFSFCSLIST INSERT INTO ADMIN_YUCTDFFSFCSLIST(ID,PID) VALUES(sID2,sRID); commit; end sp_copy;
再申明一次哈,不要存储过程,只要sql语句。 急!!!!!!!!!!!!!
用函数很方便,而却只要一条SQL语句就可以
那你可以告诉我在ibites里面怎么调用函数吗? 我只会调用sql语句啊
insert all into ADMIN_YUCTDFFSFCSBINFO (id,rid,flag) values (ida,rid,flag) into ADMIN_YUCTDFFSFCSLIST (id,pid) values (idb,rid) select (select max(id)from ADMIN_YUCTDFFSFCSBINFO)+rownum as ida , (select max(id) from ADMIN_YUCTDFFSFCSLIST)+rownum as idb , a.rid as rid, '0' as flag from ADMIN_YUCTDFFSFCSBINFO a,ADMIN_YUCTDFFSFCSLIST b where a.rid=b.pid and a.rid='3' ;
我用两条语句试试你看可不可以 insert into ADMIN_YUCTDFFSFCSBINFO() (select zsf_id.nextval,rid,....pvbk,'0' from ADMIN_YUCTDFFSFCSBINFO where rid=...) 我默认为你的flag修改以后为‘0’至于子表的复制, insert into ADMIN_YUCTDFFSFCSLIST() (select zsf1_id.nextval,pid,.... from ADMIN_YUCTDFFSFCSLIST where pid = ...)
楼上正解 一条SQL估计实现不了
为什么一条sql实现不了啊?真正的高手在哪里?
insert all into ADMIN_YUCTDFFSFCSBINFO (id,rid,flag) values (ida,rid,flag) into ADMIN_YUCTDFFSFCSLIST (id,pid) values (idb,rid) select (select max(id)from ADMIN_YUCTDFFSFCSBINFO)+rownum as ida , (select max(id) from ADMIN_YUCTDFFSFCSLIST)+rownum as idb , a.rid as rid, '0' as flag from ADMIN_YUCTDFFSFCSBINFO a,ADMIN_YUCTDFFSFCSLIST b where a.rid=b.pid and a.rid='3' ; 我不是给你写了吗?怎么不看
create or replace procedure sp_copy(sRID in VARCHAR2) is
sID1 VARCHAR2(40);
sID2 VARCHAR2(40);
--sEXQMKFCD VARCHAR2(128);
begin
select max(id+1) into sID1 FROM ADMIN_YUCTDFFSFCSBINFO;
--复制 记录到ADMIN_YUCTDFFSFCSBINFO,这里我复制时省略了一些字段,你可以自己加,可以用下面的语句得到每个字段的值
--SELECT EXQMKFCD,EXQMKDT,.... INTO sEXQMKFCD,sEXQMKDT,....from ADMIN_YUCTDFFSFCSBINFO where rid=srid;
--注意,sEXQMKFCD,sEXQMKDT,....等参数必须在前面is下定义好的
INSERT INTO ADMIN_YUCTDFFSFCSBINFO(ID,RID,FLAG)
VALUES(sID1,sRID,'0');
commit;
select max(id+1) into sID2 FROM ADMIN_YUCTDFFSFCSLIST;
--复制 记录到ADMIN_YUCTDFFSFCSLIST
INSERT INTO ADMIN_YUCTDFFSFCSLIST(ID,PID)
VALUES(sID2,sRID);
commit;
end sp_copy;
create or replace procedure sp_copy(sRID in VARCHAR2) is
sID1 VARCHAR2(40);
sID2 VARCHAR2(40);
--sEXQMKFCD VARCHAR2(128);
begin
select max(id+1) into sID1 FROM ADMIN_YUCTDFFSFCSBINFO;
--复制 记录到ADMIN_YUCTDFFSFCSBINFO,这里我复制时省略了一些字段,你可以自己加,可以用下面的语句得到每个字段的值
--SELECT EXQMKFCD,EXQMKDT,.... INTO sEXQMKFCD,sEXQMKDT,....from ADMIN_YUCTDFFSFCSBINFO where rid=srid;
--注意,sEXQMKFCD,sEXQMKDT,....等参数必须在前面is下定义好的
INSERT INTO ADMIN_YUCTDFFSFCSBINFO(ID,RID,FLAG)
VALUES(sID1,sRID,'1');
commit;
update ADMIN_YUCTDFFSFCSBINFO set FLAG='0' where rid=sRID;
COMMIT;
select max(id+1) into sID2 FROM ADMIN_YUCTDFFSFCSLIST;
--复制 记录到ADMIN_YUCTDFFSFCSLIST
INSERT INTO ADMIN_YUCTDFFSFCSLIST(ID,PID)
VALUES(sID2,sRID);
commit;
end sp_copy;
急!!!!!!!!!!!!!
我只会调用sql语句啊
into ADMIN_YUCTDFFSFCSLIST (id,pid) values (idb,rid)
select (select max(id)from ADMIN_YUCTDFFSFCSBINFO)+rownum as ida ,
(select max(id) from ADMIN_YUCTDFFSFCSLIST)+rownum as idb ,
a.rid as rid,
'0' as flag
from ADMIN_YUCTDFFSFCSBINFO a,ADMIN_YUCTDFFSFCSLIST b
where a.rid=b.pid and a.rid='3' ;
insert into ADMIN_YUCTDFFSFCSBINFO()
(select zsf_id.nextval,rid,....pvbk,'0' from ADMIN_YUCTDFFSFCSBINFO where rid=...)
我默认为你的flag修改以后为‘0’至于子表的复制,
insert into ADMIN_YUCTDFFSFCSLIST()
(select zsf1_id.nextval,pid,.... from ADMIN_YUCTDFFSFCSLIST where pid = ...)
一条SQL估计实现不了
into ADMIN_YUCTDFFSFCSLIST (id,pid) values (idb,rid)
select (select max(id)from ADMIN_YUCTDFFSFCSBINFO)+rownum as ida ,
(select max(id) from ADMIN_YUCTDFFSFCSLIST)+rownum as idb ,
a.rid as rid,
'0' as flag
from ADMIN_YUCTDFFSFCSBINFO a,ADMIN_YUCTDFFSFCSLIST b
where a.rid=b.pid and a.rid='3' ;
我不是给你写了吗?怎么不看
leon528() : 找到答案 麻烦贴出来学习中,,,,