问题描述

将Oracle函数转成mysql的函数，只要语义通过就行，谢谢大家

FUNCTION createRotationStatement(p_project IN varchar2 DEFAULT NULL,
p_whse IN varchar2 DEFAULT NULL,
p_ower IN varchar2 DEFAULT NULL,
p_outbkey IN varchar2 DEFAULT NULL,
p_rowno IN varchar2 DEFAULT NULL
)

RETURN varchar2 IS
v_orderClause varchar2(32767) := NULL;

BEGIN

r_orderRoute := getRoute(p_project, p_whse, p_ower, p_outbkey, p_rowno);

IF (r_orderRoute.e_skurotation1 IS NOT NULL) AND
   (r_orderRoute.e_rotation1 IS NOT NULL) THEN

  IF r_orderRoute.e_rotation1 = 'LIFO' THEN
    r_orderRoute.e_rotation1 := 'DESC';
  ELSE
    r_orderRoute.e_rotation1 := 'ASC';
  END IF;
  --                v_orderClause := v_orderClause||' DECODE(UPPER('''||r_orderRoute.e_skurotation1||'''),''LOT'',le.lot,le.'||r_orderRoute.e_skurotation1||') '||r_orderRoute.e_rotation1||', ';
  v_orderClause := v_orderClause || ' DECODE(UPPER(''' ||
                   r_orderRoute.e_skurotation1 || '''),''LOT'',le.lot,
        ''LOT08'',TO_CHAR(le.LOT08,''yyyy-mm-dd''),
        ''LOT09'',TO_CHAR(le.LOT09,''yyyy-mm-dd''),
        ''LOT10'',TO_CHAR(le.LOT10,''yyyy-mm-dd''),
        ''LOT16'',TO_CHAR(le.LOT16,''yyyy-mm-dd''),
        ''REC_DATE'',TO_CHAR(inv.REC_DATE,''yyyy-mm-dd''),
        le.LOT) ' || r_orderRoute.e_rotation1 || ', ';
END IF;

IF (r_orderRoute.e_skurotation2 IS NOT NULL) AND
   (r_orderRoute.e_rotation2 IS NOT NULL) THEN

  IF r_orderRoute.e_rotation2 = 'LIFO' THEN
    r_orderRoute.e_rotation2 := 'DESC';
  ELSE
    r_orderRoute.e_rotation2 := 'ASC';
  END IF;
  --                v_orderClause := v_orderClause||' DECODE(UPPER('''||r_orderRoute.e_skurotation2||'''),''LOT'',le.lot,le.'||r_orderRoute.e_skurotation2||') '||r_orderRoute.e_rotation2||', ';
  v_orderClause := v_orderClause || ' DECODE(UPPER(''' ||
                   r_orderRoute.e_skurotation2 || '''),''LOT'',le.lot,
        ''LOT08'',TO_CHAR(le.LOT08,''yyyy-mm-dd''),
        ''LOT09'',TO_CHAR(le.LOT09,''yyyy-mm-dd''),
        ''LOT10'',TO_CHAR(le.LOT10,''yyyy-mm-dd''),
        ''LOT16'',TO_CHAR(le.LOT16,''yyyy-mm-dd''),
        ''REC_DATE'',TO_CHAR(inv.REC_DATE,''yyyy-mm-dd''),
        le.LOT) ' || r_orderRoute.e_rotation2 || ', ';
END IF;

IF (r_orderRoute.e_skurotation3 IS NOT NULL) AND
   (r_orderRoute.e_rotation3 IS NOT NULL) THEN

  IF r_orderRoute.e_rotation3 = 'LIFO' THEN
    r_orderRoute.e_rotation3 := 'DESC';
  ELSE
    r_orderRoute.e_rotation3 := 'ASC';
  END IF;
  --                v_orderClause := v_orderClause||' DECODE(UPPER('''||r_orderRoute.e_skurotation3||'''),''LOT'',le.lot,le.'||r_orderRoute.e_skurotation3||') '||r_orderRoute.e_rotation3||', ';
  v_orderClause := v_orderClause || ' DECODE(UPPER(''' ||
                   r_orderRoute.e_skurotation3 || '''),''LOT'',le.lot,
        ''LOT08'',TO_CHAR(le.LOT08,''yyyy-mm-dd''),
        ''LOT09'',TO_CHAR(le.LOT09,''yyyy-mm-dd''),
        ''LOT10'',TO_CHAR(le.LOT10,''yyyy-mm-dd''),
        ''LOT16'',TO_CHAR(le.LOT16,''yyyy-mm-dd''),
        ''REC_DATE'',TO_CHAR(inv.REC_DATE,''yyyy-mm-dd''),
        le.LOT) ' || r_orderRoute.e_rotation3 || ', ';
END IF;

DBMS_OUTPUT.PUT_LINE('YYYYY:' || v_orderClause);

RETURN SUBSTR(TRIM(v_orderClause), 1, LENGTH(TRIM(v_orderClause)) - 1);

END createRotationStatement;

解决方案

Oracle与MySql函数
对比: sql函数转成oracle函数