2 sinat 36169916 sinat_36169916 于 2016.09.19 14:37 提问

帮忙把sql语句写入存储过程。

请大神帮忙把这两段sql语句写入一个存储过程里,并且把表名设为变量,因为每次执行这段语句需要修改表名table_11 ,谢谢

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
shihengzhen101   Rxr 2016.09.19 15:08

要把tab_11设成变量还是tab1设成变量

shihengzhen101
shihengzhen101 你可以看看下面的代码
大约一年之前 回复
sinat_36169916
sinat_36169916 tab_11设为变量
大约一年之前 回复
shihengzhen101
shihengzhen101   Rxr 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
sinat_36169916 好的,谢谢
大约一年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片