sinat_36169916 于 2016.09.19 14:37 提问

select * from tab1 for update

create table tab_11 as

select *

from v_number

where pcode = 20161408

and scode in

``````   (select scode from tab1)
``````

delete from v_number

where = 20161408

and scode in

``````   (select scode from tab1)
``````

---285

insert into v_number

select 20161408,

``````     a.scode,

a.ICODE,

from v_number a
``````

where (a.pcode ,a.scode) in (select copy,scode from tab1)

delete from r_item

where pcode = 20161408

and scode in

``````(select scode from tab1)
``````

insert into r_item

select 20161408, scode, percent

from r_item

where (pcode , scode) in

``````   (select copy,scode from tab1)
``````

select * from tab2 for update

create table tab2_20161408 as

select * from ra_number where pcode=20161408

and (scode,cy) in (select scode,cy from tab2)

delete from ra_number where pcode=20161408

and (scode,cy) in (select scode,cy from tab2)

insert into ra_number

select 20161408,

``````   a.scode,

a.ITEMCODE
``````

from ra_number a where (a.pcode,a.scode,a.cy) in

(select copy,scode,cy from tab2)

insert into v_number

select 20161408,

``````     a.scode,

a.ICODE,

from v_number a
``````

where (a.pcode ,a.scode) in (select copy,scode from tab2)

2个回答

shihengzhen101   2016.09.19 15:08

shihengzhen101 你可以看看下面的代码

sinat_36169916 tab_11设为变量

shihengzhen101   2016.09.19 15:25

``````
DROP PROCEDURE IF EXISTS pro_1;
CREATE PROCEDURE pro_1(IN paramtableName VARCHAR(64))
BEGIN

select * from tab1 for update

-- create table tab_11 as select * from v_number where pcode = 20161408 and scode in (select scode from tab1);

SET @prepareStatement = CONCAT("create table ", paramtableName, " as select * from v_number where pcode = 20161408 and scode in (select scode from tab1);");
PREPARE stmt FROM @prepareStatement;
EXECUTE stmt ;

delete from v_number where = 20161408 and scode in (select scode from tab1);

---285

insert into v_number select 20161408, a.scode, a.ICODE, from v_number a where (a.pcode ,a.scode) in (select copy,scode from tab1);

delete from r_item where pcode = 20161408 and scode in (select scode from tab1) insert into r_item;

select 20161408, scode, percent from r_item  (select copy,scode from tab1)

select * from tab2 for update

create table tab2_20161408 as

select * from ra_number where pcode=20161408

and (scode,cy) in (select scode,cy from tab2)

delete from ra_number where pcode=20161408

and (scode,cy) in (select scode,cy from tab2)

insert into ra_number

select 20161408,

a.scode,

a.ITEMCODE

from ra_number a where (a.pcode,a.scode,a.cy) in

(select copy,scode,cy from tab2)

insert into v_number

select 20161408,

a.scode,

a.ICODE,

from v_number a

where (a.pcode ,a.scode) in (select copy,scode from tab2)

END;
``````
sinat_36169916 好的，谢谢