浅谈db2与oracle存储过程的不同之处

1、oracle中调用存储过程使用exec关键字,db2中使用call关键字

2、db2中不支持”or replace”的语法;且参数名与IN/OUT的位置也需互换(如：in nd varchar(32)), 声明变量时须使用declare关键字，为变量赋值时须使用set关键字。

3、 对于Oracle中的%TYPE的功能

 oracle：name user%rowtype;   修改为  db2：declare name anchor row of user;(或视图)

REATE PROCEDURE SINO_ZGS.GENERATE_ZF_MRKB_YSZX_BBQS
 (IN VAR_ND VARCHAR(4), 
  IN VAR_QJ VARCHAR(2), 
  IN VAR_HZD VARCHAR(32), 
  IN VAR_LZD VARCHAR(32), 
  IN VAR_BBBH VARCHAR(20), 
  IN VAR_BMC VARCHAR(20), 
  IN VAR_ZDMC VARCHAR(20), 
  IN VAR_BJDMTYPE VARCHAR(1)
 ) 
  SPECIFIC SINO_ZGS.SQL140417182027500
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  BEGIN
    DECLARE VAR_RWZQ varchar(20);
    DECLARE bb_sql varchar(20000);
    DECLARE VAR_GXQJ varchar(2);
    DECLARE VAR_GXND varchar(4);
    DECLARE DEC_ZS_ZGSBB DECIMAL(20,6);
	
    DECLARE cur_zgsbb CURSOR FOR (
        select BBSJ_DATA from rprwbbsj 
        WHERE rpbbsj_rwbh =  '20101' AND sjzd_tbdw = '1' AND sjzd_bh = '0002' AND bbzd_bh = '20101'||VAR_BBBH 
        AND sjzd_bblx = '9' AND hzd_orde = VAR_HZD AND lzd_orde = VAR_LZD and bbzd_date = VAR_ND||'0'||VAR_QJ||'004');
    open cur_zgsbb;
    FETCH cur_zgsbb INTO DEC_ZS_ZGSBB;
    close cur_zgsbb;
	
    SET VAR_GXND = VAR_ND;
    set VAR_GXQJ = right(rtrim(char((INT(VAR_QJ)-1)/3+1+100)),2);

    IF INT(VAR_QJ) >= 10
    THEN
        SET VAR_RWZQ = VAR_ND||'0'||VAR_QJ||'004';
    ELSE 
        SET VAR_RWZQ = VAR_ND||'0'||VAR_QJ||'004';
    END IF;
	
    delete from SINO_ZGS.ZF_MRKB_YSZX_BBBJDM where ND=VAR_ND and QJ=VAR_QJ;
    commit;
  
    SET bb_sql ='update SINO_ZGS.ZF_MRKB_YSZXKD_'||VAR_BMC||' a 
        set a.'||VAR_ZDMC||' = (select round(b.SZ/10000,6) from SINO_ZGS.ZF_MRKB_YSZX_TEMP b where a.ZZNM = b.ZZBH and a.ND=b.ND
        and a.QJ=b.QJ and a.HZFS=b.HZFS)
        where a.'||VAR_ZDMC||' is null and a.ND='''||VAR_ND||''' and QJ='''||VAR_QJ||'''';
    PREPARE r_sql from bb_sql;
    EXECUTE r_sql;
    COMMIT;
END;