存储过程中如何动态执行多条 SQL?

[Blackride]

我想传一个字符串:有多条SQL
如:str:='begin
drop table Tbtempalarm1;
drop table Tbtempalarm2;
Create table Tbtempalarm1 as select * from tbAlarm;
Create table Tbtempalarm2 as select * from tbFactory;
end;'

能以 EXECUTE immediate str; 的形式执行吗?

有什么方法能实现吗?

[bzszp]

没试过
这种问题自己试一下就行了
把结果贴出来
实在不行就分开执行呗

我这人比较懒
：）

[Michaelyfj]

分开执行吗，一样能实现，为什么要一次执行呢？我认为没必要

[Blackride]

跟在 PL/SQL 的sql window中执行begin
drop table Tbtempalarm1;
drop table Tbtempalarm2;
Create table Tbtempalarm1 as select * from tbAlarm;
Create table Tbtempalarm2 as select * fromt bFactory;
end;

报一样的错~~

[Blackride]

哦 ~~ 你能指导一下 分开执行的实现方法吗! 我这里

str:='drop table Tbtempalarm1;
drop table Tbtempalarm2;
Create table Tbtempalarm1 as select * from tbAlarm;
Create table Tbtempalarm2 as select * from tbFactory;'
必须是一个参数~~
怎样才能在存储过程中分开~~

[Michaelyfj]

解析这个参数，分开成几个SQL语句，然后用DBMS_SQL进行执行

[Blackride]

问题是我不知道怎么解析!(接触得少!) 能指导指导吗?

[beckhambobo]

可以执行
str:='begin
drop table Tbtempalarm1;--grant drop any table to new_user
drop table Tbtempalarm2;
Create table Tbtempalarm1 as select * from tbAlarm;--grant create any table to new_user
Create table Tbtempalarm2 as select * from tbFactory;
end;'；

EXECUTE immediate str;

[pingshx]

str:='drop table Tbtempalarm1';
EXECUTE immediate str;
str:='drop table Tbtempalarm2';
EXECUTE immediate str;
str:=' Create table Tbtempalarm1 as select * from tbAlarm';
EXECUTE immediate str;
str:='Create table Tbtempalarm2 as select * from tbFactory';
EXECUTE immediate str;

[firetoucher]

如果是sql server
用osql把

[hansion3406]

你可以用一个事务来做嘛..

[postech]

这是一个存储过程
create proc UpdateSendGoodsNum(@SendID varchar(20))
as
/*
从SendGoodDet表中选择明细数据，根据换算关系，把数据更新到StockMaterial表中


declare @SendID varchar(20)
set @SendID='SG-2003-06-13-003'
*/
declare Send_cur cursor
for
select b.MaterialID,sum(s.Num) as 'Num',s.UnitID ,h.OrderID,b.PartNo
from sendgooddet s
join bom b on b.partno=s.partno
join OrderHed h on h.poid=s.poid
where sendid=@SendID
group by b.MaterialID,s.UnitID,s.UnitID ,h.OrderID,b.PartNo

declare @MaterialID varchar(20),
@Num decimal(10,2),
@UnitID varchar(4),
@StockID varchar(6),
@Sum decimal(20),
@UnitID2 varchar(4),
@OrderID varchar(20),
@PartNo varchar(50)
select @StockID=stockID from sendgoodhed where sendid=@SendID
open Send_cur
fetch next from Send_cur into @MaterialID,@Num,@UnitID,@OrderID,@PartNo
while @@fetch_status=0
begin
--选择仓库的单位
select @UnitId2=UnitID from stockmaterial where materialid=@MaterialID and stockid=stockid
select @Sum=Converts*@num from UnitConvert where Unit1=@UnitID and unit2=@unitid2
-- if @State=0
Update StockMaterial set num=num-@Sum
where stockid=@StockID and MaterialID=@MaterialID
--修改OrderDet'
select @UnitID2=UnitID from OrderDet where OrderID=@OrderID and PartNo=@PartNo
select @Sum=Converts*@Num from UnitConvert where Unit1=@UnitID and Unit2=@Unitid2
update OrderDet set SendNum=SendNum+@Sum
where OrderID=@OrderID and PartNo=@PartNo
--判断OrderHed是否已经完成
declare @Count int
select @Count=count(*) from orderdet where OrderID=@OrderID and Num>SendNum
if @Count=0
update OrderHed set Flag=2 where OrderID=@OrderID
else
update OrderHed set Flag=1 where OrderID=@OrderID
/* else
Update StockMaterial set num=num+@Sum
where stockid=@StockID and MaterialID=@MaterialID
*/
fetch next from Send_cur into @MaterialID,@Num,@UnitID,@OrderID,@PartNo
end
close Send_cur
deallocate Send_cur


如果你想传入一个字符串来执行：
str：＝'
drop table Tbtempalarm1 go
drop table Tbtempalarm2 go
Create table Tbtempalarm1 as select * from tbAlarm go
Create table Tbtempalarm2 as select * from tbFactory go
'
在每条语句后加GO就可以了，这样就按照顺序来执行，你这不是存储过程啊，不过是在前端的SQL语句而已。

[bzszp]

楼上的好象是sql server的吧

[dxxfun]

我觉得SQL SERVER 的处理上比ORACLE灵活多了，微软做的东西就是易用性强。

[Blackride]

唉 没办法了 只有 拆开 一句一句执行了!~~