create or replace procedure ModifyLadingItemPack( ASoditemguid varchar2,
–左券电子仓单明细GUID ALadingitemGUID varchar2, –提单子项GUID ACDResID
varchar2, –电子仓单号 APackageIDList varchar2, –仓单捆包具体GUID列表
Aladingnum float, Aladingpieces float, AReturnValue out integer
–重临值:)is ALadingItem_PackagesGUID varchar(32卡塔尔国; ATempIDList
varchar2(4000卡塔尔(قطر‎; AID varchar2(32卡塔尔国; APackageWeight float; APackagePiece
float; ATotalPackWeight float;begin AReturnValue := 1; ATempIDList :=
APackageIDList; APackageWeight :=0; APackagePiece :=0;
———————————更新合同电子仓单明细—————————
while ( INST宝马X5(ATempIDList,: 卡塔尔 0 卡塔尔(قطر‎ or ( LENGTH(ATempIDList 卡塔尔 0 卡塔尔(قطر‎ loop if
INST奥德赛(ATempIDList,: 卡塔尔(قطر‎ 0 then select sys_guid() into
ALadingItem_PackagesGUID from dual; AID := SUBST梅赛德斯-AMG(ATempIDList ,1,INST安德拉(
ATempIDList,:卡塔尔(英语:State of Qatar) – 1 卡塔尔国; ATempIDList := SUBSTLX570(ATempIDList,INSTOdyssey(
ATempIDList,:卡塔尔 + 1 , LENGTH(ATempIDList卡塔尔(英语:State of Qatar) – INST逍客(ATempIDList,:卡塔尔卡塔尔;
———————————提单捆包明细———————————-
insert into HT_LadingItem_packages(sGUID, sLadingItemGUID, sCDResID,
sPackageID) values(ALadingItem_PackagesGUID, ALadingitemGUID,
ACDResID,AID卡塔尔国;
———————————更新仓单捆包明细状态—————————
UPDATE ZY_Packages SET state = 5 WHERE SGUID = AID; select fWeight into
ATotalPackWeight from ZY_Packages where sGUID=AID;
APackageWeight:=APackageWeight+ATotalPackWeight;
APackagePiece:=APackagePiece+1; else AID := ATempIDList; ATempIDList :=
; end if; end loop; update HT_SODItem set
fLadingNum=fLadingNum+APackageWeight,fLadingPieces=fLadingPieces+APackagePiece
where sGUID=ASoditemguid; COMMIT;EXCEPTION WHEN OTHERS THEN BEGIN
AReturnValue := 99; ROLLBACK; END;end;

拍卖思路:
1、查找判别 相近数量是或不是已存在
2、如不真实,则实行Insert操作

CREATE PROCEDURE `mysql_sp_insert_data`(
  IN p_TableName varchar(100)   –表名
, IN p_ColumnNames varchar(200卡塔尔国 –字段组合 ,以”,”分隔
, IN p_ColumnDatas varchar(500卡塔尔国 –数据整合 ,以”@”分隔
)
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ”
BEGIN
   declare tmpExistDataCount int(4);
   declare tmpWhere varchar(800);
   declare tmpSqlStr varchar(1000);
   declare tmpSqlToRun varchar(2000);
   declare tmpSplitName varchar(10);
   declare tmpSplitData varchar(10);
   declare tmpNames varchar(800);
   declare tmpDatas varchar(800);
   declare tmpIndexName int(4);
   declare tmpIndexData int(4);
  
 set tmpNames = p_ColumnNames;
 set tmpDatas = p_ColumnDatas;
 set tmpSplitName = “,”;
 set tmpSplitData = “@”;
 
 set tmpWhere = ” where 1 = 1 “;
 
select INSTR(tmpNames,tmpSplitName) into tmpIndexName;
 
if tmpIndexName > 0  then

 while tmpIndexName > 0 do
    select INSTR(tmpNames,tmpSplitName) into tmpIndexName;
    select INSTR(tmpDatas,tmpSplitData) into tmpIndexData;
  
    set tmpWhere = concat(tmpWhere , ” and ” ,
SUBSTRING(tmpNames,1,tmpIndexName -1) ,” = “);
    set tmpWhere = concat(tmpWhere , SUBSTRING(tmpDatas,1,tmpIndexData
-1 ) );
    
    
    set tmpNames = SUBSTRING(tmpNames,tmpIndexName + 1,LENGTH(tmpNames)

  • tmpIndexName);
        set tmpDatas = SUBSTRING(tmpDatas,tmpIndexData + 1,LENGTH(tmpDatas)
  • tmpIndexData);

    select INSTR(tmpNames,tmpSplitName) into tmpIndexName;

 end while;

end if;
 
  if LENGTH(tmpNames) > 0 then
     set tmpWhere = concat(tmpWhere , ” and ” , tmpNames ,” = “);
     set tmpWhere = concat(tmpWhere , tmpDatas );
  end if;

   set tmpSqlStr = ” select count(*) “;
   set tmpSqlStr = concat(tmpSqlStr  ,” from  “, p_TableName ,tmpWhere
, ”  ; ”  );
   set @sql1 = tmpSqlStr;
   prepare tmpSqlToRun from @sql1;
  
   select ( tmpSqlStr) into tmpExistDataCount;
  

   if tmpExistDataCount = 0  then
      set p_ColumnDatas =  REPLACE(p_ColumnDatas,tmpSplitData,”,”);
      set tmpSqlStr = ” “;
      set tmpSqlStr = concat(tmpSqlStr ,”  insert into  ” ,
p_TableName);
      set tmpSqlStr = concat(tmpSqlStr ,” (  “, p_ColumnNames, ” )
values ( ”  ,p_ColumnDatas , ” ) ;” );
      SET @sql = tmpSqlStr;
      prepare tmpSqlToRun from @sql;
     
    —  select tmpSqlStr;
     
      execute tmpSqlToRun;
  
   end if;

END;

–测试
CREATE TABLE `table1` (
  `field1` int(11) NOT NULL,
  `field2` varchar(20) DEFAULT NULL,
  `field3` datetime DEFAULT NULL,
  PRIMARY KEY (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

call
mysql_sp_insert_data(“table1″,”field1,field2,field3″,”1@’1’@null”);

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章