mysql存储过程例子

-- Procedure "proc_product_saveAs" DDL

CREATE PROCEDURE `proc_product_saveAs`(in p_id integer,in product_no varchar(60),in product_name varchar(60),in product_category varchar(60),in handinch_or_len varchar(60),in plating_method varchar(60),in ingredients_category integer,in nummask varchar(60),out result integer)
BEGIN

-- 声明变量,所有的声明必须在非声明的语句前面
declare _new_product_id  int default -1;

-- 如果出现异常,或自动处理并rollback,但不再通知调用方了
-- 如果希望应用获得异常,需要将下面这一句,以及启动事务和提交事务的语句全部去掉
declare exit handler for sqlexception rollback;

set result = -1;

-- 启动事务
    start transaction;

INSERT INTO
  `tbl_complex_baseproductinfo`
(
  `CPBH`,
  `BHZH`,
  `SC_OR_CD`,
  `CPPM`,
  `DDFS`,
  `ZLLB`,
  `CPLB`,
  `CPXL`,
  `KQ_OR_KC`,
  `KQ_OR_KC_YS`,
  `KQ_OR_KC_ZL`,
  `CPZZ`,
  `CSID`,
  `ZZBL`,
  `YBBH`,
  `YBJZ`,
  `FBBH`,
  `FBJZ`,
  `JMBH`,
  `JMCS`,
  `ZLQY`,
  `DMWD`,
  `MJBH`,
  `CPXH`,
  `CPLZ`,
  `PTZY`,
  `ZB`,
  `CD`,
  `KD`,
  `HD`,
  `FDS`,
  `YPWG`,
  `CJRQ`,
  `XGRQ`,
  `thumb`,
  `pictures`
)
select
product_no,
  nummask,
  `SC_OR_CD`,
  product_name,
  plating_method,
   ingredients_category,
   product_category,
  `CPXL`,
   handinch_or_len,
  `KQ_OR_KC_YS`,
  `KQ_OR_KC_ZL`,
  `CPZZ`,
  `CSID`,
  `ZZBL`,
  `YBBH`,
  `YBJZ`,
  `FBBH`,
  `FBJZ`,
  `JMBH`,
  `JMCS`,
  `ZLQY`,
  `DMWD`,
  `MJBH`,
  `CPXH`,
  `CPLZ`,
  `PTZY`,
  `ZB`,
  `CD`,
  `KD`,
  `HD`,
  `FDS`,
  `YPWG`,
  `CJRQ`,
  `XGRQ`,
  `thumb`,
  `pictures`
from tbl_complex_baseproductinfo base where base.PRODUCT_ID =  p_id;

set _new_product_id = LAST_INSERT_ID( );

insert into tbl_complex_productstone( product_id,STONE_ID,SETTING_ID, SXSL,SXSL_ZJL, STDZ ,STZC) select  _new_product_id,STONE_ID,SETTING_ID, SXSL,SXSL_ZJL, STDZ ,STZC   from tbl_complex_productstone stone where stone.PRODUCT_ID =  p_id;

insert into tbl_complex_productfitting( product_id,FITTING_ID,SXSL, SXCD,PJDZ, PJHZ,JJFS,JGLX,LLGX )  select   _new_product_id,FITTING_ID,SXSL, SXCD,PJDZ, PJHZ,JJFS,JGLX,LLGX  from tbl_complex_productfitting fitting where fitting.PRODUCT_ID =  p_id;

insert into tbl_complex_productauxiliary( product_id, AUX_ID)  select   _new_product_id, AUX_ID from tbl_complex_productauxiliary auxiliary where auxiliary.PRODUCT_ID =  p_id;

insert into tbl_complex_productmakestep( product_id, STEP_ID,SM,PXZD)  select   _new_product_id, STEP_ID,SM,PXZD from tbl_complex_productmakestep step where step.PRODUCT_ID =  p_id;


set result = _new_product_id;

-- 运行没有异常,提交事务
commit;


END;

lunzi   2010-05-09 22:28:11 评论:0   阅读:3064   引用:0

发表评论>>

署名发表(评论可管理,不必输入下面的姓名)

姓名:

主题:

内容: 最少15个,最长1000个字符

验证码: (如不清楚,请刷新)

Copyright@2004-2010 powered by YuLog