做一个系统的接口 对方是用的oracle我们系统是用的MS SQL但自己不会用oracle 所以请各位大侠帮忙改一下 谢谢--3产品分类--------------------------------------------------------------------------------------------------------
DECLARE @NewID VARCHAR(50),@UserID VARCHAR(50),@CUID VARCHAR(50)
--获取默认用户ID
SELECT @UserID=FID FROM t_PM_User where FNumber='user'
--获取默认的管理单元ID
SELECT @CUID=FID FROM t_Org_Admin WHERE FName_L2='环球国际集团' DECLARE @matgroup varchar(20)
DECLARE @matgroupname varchar(800)
DECLARE @matgrouptype varchar(20)
DECLARE @SupGroup varchar(20)
DECLARE @SupGroupName varchar(800)
DECLARE @memo varchar(100)
--处理新增
DECLARE Item_Cursor CURSOR FOR
SELECT matgroup,matgroupname,matgrouptype,SupGroup,SupGroupName,memo FROM Mid_iMatGroup where ISNULL(TransType,0)=0 and ISNULL(TransStatus,0)=0
OPEN Item_Cursor
FETCH NEXT FROM Item_Cursor INTO @matgroup,@matgroupname,@matgrouptype,@SupGroup,@SupGroupName,@memo
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @NewID=fnEWid from VW_NEWID
SELECT @NewID=dbo.newbosid(@NewID)
INSERT INTO T_PJY_ProductSort
(
FMattypename, FSupGroupName, FLastUpdateTime, FMatgrouptype, FNumber,
FCreateTime, FName_l2, FMatgroup, FDescription_l2, FMemo,
FID, FLastUpdateUserID, FName_l3, FDescription_l3, FCreatorID,
FSimpleName, FSupGroup, FMatgroupname, FMattype, FControlUnitID
)
VALUES
(
N'',@SupGroupName,GetDate(),@matgrouptype,@matgroup,
GetDate(),@matgroupname,@matgroup,N'',@memo,
@NewID,@UserID,@matgroupname,NULL,@UserID,
N'',@SupGroup,@matgroupname,N'',@CUID
) FETCH NEXT FROM Item_Cursor INTO @matgroup,@matgroupname,@matgrouptype,@SupGroup,@SupGroupName,@memo
END
CLOSE Item_Cursor
DEALLOCATE Item_Cursor--处理修改
update T1 Set FSupGroupName=@SupGroupName,FLastUpdateTime=GetDate(),FMatgrouptype=@matgrouptype,FNumber=@matgroup,
FName_l2=@matgroupname,FMatgroup=@matgroup,FMemo=@memo,FName_l3=@matgroupname,FLastUpdateUserID=@UserID,FSupGroup=@SupGroup,FMatgroupname=@matgroupname
from T_PJY_ProductSort T1
INNER JOIN Mid_iMatGroup T2 ON (T1.FMatgroup COLLATE Chinese_PRC_CI_AS=T2.matgroup COLLATE Chinese_PRC_CI_AS)
where ISNULL(T2.TransType,0)=1 AND ISNULL(TransStatus,0)=0--反写中间表
update T1 Set EASID=CAST(FID AS VARCHAR(50))
from Mid_iMatGroup T1
INNER JOIN T_PJY_ProductSort T2 ON (T1.Matgroup COLLATE Chinese_PRC_CI_AS=T2.Fmatgroup COLLATE Chinese_PRC_CI_AS)
--产品分类--------------------------------------------------------------------------------------------------------
DECLARE @NewID VARCHAR(50),@UserID VARCHAR(50),@CUID VARCHAR(50)
--获取默认用户ID
SELECT @UserID=FID FROM t_PM_User where FNumber='user'
--获取默认的管理单元ID
SELECT @CUID=FID FROM t_Org_Admin WHERE FName_L2='环球国际集团' DECLARE @matgroup varchar(20)
DECLARE @matgroupname varchar(800)
DECLARE @matgrouptype varchar(20)
DECLARE @SupGroup varchar(20)
DECLARE @SupGroupName varchar(800)
DECLARE @memo varchar(100)
--处理新增
DECLARE Item_Cursor CURSOR FOR
SELECT matgroup,matgroupname,matgrouptype,SupGroup,SupGroupName,memo FROM Mid_iMatGroup where ISNULL(TransType,0)=0 and ISNULL(TransStatus,0)=0
OPEN Item_Cursor
FETCH NEXT FROM Item_Cursor INTO @matgroup,@matgroupname,@matgrouptype,@SupGroup,@SupGroupName,@memo
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @NewID=fnEWid from VW_NEWID
SELECT @NewID=dbo.newbosid(@NewID)
INSERT INTO T_PJY_ProductSort
(
FMattypename, FSupGroupName, FLastUpdateTime, FMatgrouptype, FNumber,
FCreateTime, FName_l2, FMatgroup, FDescription_l2, FMemo,
FID, FLastUpdateUserID, FName_l3, FDescription_l3, FCreatorID,
FSimpleName, FSupGroup, FMatgroupname, FMattype, FControlUnitID
)
VALUES
(
N'',@SupGroupName,GetDate(),@matgrouptype,@matgroup,
GetDate(),@matgroupname,@matgroup,N'',@memo,
@NewID,@UserID,@matgroupname,NULL,@UserID,
N'',@SupGroup,@matgroupname,N'',@CUID
) FETCH NEXT FROM Item_Cursor INTO @matgroup,@matgroupname,@matgrouptype,@SupGroup,@SupGroupName,@memo
END
CLOSE Item_Cursor
DEALLOCATE Item_Cursor--处理修改
update T1 Set FSupGroupName=@SupGroupName,FLastUpdateTime=GetDate(),FMatgrouptype=@matgrouptype,FNumber=@matgroup,
FName_l2=@matgroupname,FMatgroup=@matgroup,FMemo=@memo,FName_l3=@matgroupname,FLastUpdateUserID=@UserID,FSupGroup=@SupGroup,FMatgroupname=@matgroupname
from T_PJY_ProductSort T1
INNER JOIN Mid_iMatGroup T2 ON (T1.FMatgroup COLLATE Chinese_PRC_CI_AS=T2.matgroup COLLATE Chinese_PRC_CI_AS)
where ISNULL(T2.TransType,0)=1 AND ISNULL(TransStatus,0)=0--反写中间表
update T1 Set EASID=CAST(FID AS VARCHAR(50))
from Mid_iMatGroup T1
INNER JOIN T_PJY_ProductSort T2 ON (T1.Matgroup COLLATE Chinese_PRC_CI_AS=T2.Fmatgroup COLLATE Chinese_PRC_CI_AS)
--产品分类--------------------------------------------------------------------------------------------------------
var
NewID VARCHAR2(50);
UserID VARCHAR2(50);
CUID VARCHAR2(50);
matgroup varchar2(20);
matgroupname varchar2(800);
matgrouptype varchar2(20);
SupGroup varchar2(20);
SupGroupName varchar2(800);
memo varchar2(100);
begin
SELECT FID into UserID FROM t_PM_User where FNumber='user';
SELECT FID into CUID FROM t_Org_Admin WHERE FName_L2='环球国际集团';
for rec in (
SELECT matgroup,matgroupname,matgrouptype,SupGroup,SupGroupName,memo FROM Mid_iMatGroup
where nvl(TransType,0)=0 and nvl(TransStatus,0)=0
)
Loop
SELECT fnEWid into NewID from VW_NEWID;
SELECT newbosid(NewID) into NewID from dual;
INSERT INTO T_PJY_ProductSort (
FMattypename, FSupGroupName, FLastUpdateTime, FMatgrouptype, FNumber,
FCreateTime, FName_l2, FMatgroup, FDescription_l2, FMemo,
FID, FLastUpdateUserID, FName_l3, FDescription_l3, FCreatorID,
FSimpleName, FSupGroup, FMatgroupname, FMattype, FControlUnitID
)
VALUES(
N'',rec.SupGroupName,sysdate,rec.matgrouptype,rec.matgroup,
sysdate,rec.matgroupname,rec.matgroup,N'',rec.memo,
NewID,UserID,rec.matgroupname,NULL,UserID,
N'',rec.SupGroup,rec.matgroupname,N'',CUID
)
SupGroupName := rec.SupGroupName;
matgrouptype := rec.matgrouptype;
matgroup := rec.matgroup;
matgroupname := rec.matgroupname;
memo := memo;
SupGroup := rec.SupGroup;
end loop;
update T1 Set (
FSupGroupName,FLastUpdateTime,FMatgrouptype,FNumber,FName_l2,FMatgroup,
FMemo,FName_l3,FLastUpdateUserID,FSupGroup,FMatgroupname
)=( select SupGroupName,sysdate,matgrouptype,matgroup,matgroupname,matgroup,
memo,matgroupname,UserID,SupGroup,matgroupname from Mid_iMatGroup t2
where t2.matgroup = t1.FMatgroup and nvl(T2.TransType,0)=1
)
where nvl(TransStatus,0)=0;
update T1 Set EASID=(select CAST(FID AS VARCHAR2(50)) from T_PJY_ProductSort where Fmatgroup= t1.Matgroup);
end;
/