我insert into client table 是 cno, clname, cpass, cdname, ccur, ccurname, crate, cbal, cbname1, cbano1, cbname2, cbano2, ct1, ct2, ct3, ct4, reg, ctype, odt, cby, lastup, lastuser, tstamp
insert 过后
select client 的 cno 然后我要insert into site_acc_order 的 cno
site_acc_order 是 cno, cdname, usr, commis, amt, cby, cdt, mby, mdt, stampt
不过select 不到 client 的 cno
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `agent`.`addcom` $$
- CREATE DEFINER=`hang`@`%` PROCEDURE `addcom`(
- in name1 varchar(20),
- in pas varchar(15),
- in cid integer(20),
- in sessname varchar(20),
- in username integer(20),
- in commission integer(20),
- in amount integer(20)
- )
- Begin
- declare ccurname1 varchar(5);
- declare crate1 decimal(12,8);
- declare cn integer(20);
- if not exists (SELECT clname FROM client WHERE clname=name1) then
- select ccurname,crate into ccurname1,crate1 from ccur where ccurid=cid;
- if (ccurname1 is null)then
- rollback;
- else
- select ccurname1;
- select crate1;
- insert into client (clname,cpass,cdname,ccur,ccurname,crate,reg,ctype,cby,lastup,lastuser) values (name1,pas,name1,cid,ccurname1,crate1,'0','D',sessname,sysdate(),sessname);
- end if;
- else
- rollback;
- end if;
- #..........问题在这select不到 client 的 cno
- select cno into cn from client WHERE cdname=name1;
- if (cn is null)then
- rollback;
- else
- select cn;
- insert into site_acc_order (cno,cdname,usr,commis,amt,cby,cdt,mby,mdt) values (cn,name1,username,commission,amount,sessname,sysdate(),sessname,sysdate());
- end if;
- #...........
- End $$
- DELIMITER ;
复制代码
if can leave yr msn or skype 4 me ...
[ 本帖最后由 dannylim86 于 2008-8-2 17:49 编辑 ] |