2 lijun0668 lijun0668 于 2016.03.25 12:30 提问

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

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;

1个回答

CSDNXIAOD
CSDNXIAOD   2016.03.30 11:23

Oracle与MySql函数
对比: sql函数转成oracle函数
----------------------biu~biu~biu~~~在下问答机器人小D,这是我依靠自己的聪明才智给出的答案,如果不正确,你来咬我啊!

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!