跟在 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;报一样的错~~
drop table Tbtempalarm1;
drop table Tbtempalarm2;
Create table Tbtempalarm1 as select * from tbAlarm;
Create table Tbtempalarm2 as select * fromt bFactory;
end;报一样的错~~
drop table Tbtempalarm2;
Create table Tbtempalarm1 as select * from tbAlarm;
Create table Tbtempalarm2 as select * from tbFactory;'
必须是一个参数~~
怎样才能在存储过程中分开~~
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;
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;
用osql把
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.PartNodeclare @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语句而已。