Oracle /SQL存储过程日期间隔精度ORA-30088

Question

在分配日期时间格式时，我在实现PL/SQL过程时遇到了问题。我指的是我的MySQL存储过程，我是Oracle /SQL的新手。

下面是我从MySQL过程中进行的PL/SQL转换

CREATE OR REPLACE 
PROCEDURE "DateTimeSP" (xType IN VARCHAR2, xResult OUT VARCHAR2)
AS
BEGIN

CASE
    WHEN xType = 'TIME' THEN 

             SELECT CURRENT_TIMESTAMP() FROM dual INTO xResult;

    WHEN xType = 'DATE' THEN 

            SELECT TRUNC(SYSDATE) FROM dual INTO xResult;

    WHEN xType = 'NOW' THEN 

            ( SELECT SYSTIMESTAMP) FROM dual INTO xResult;

    WHEN xType = 'NOWFORMAT' THEN 

              SELECT TO_CHAR(SYSTIMESTAMP,'%m/%d/%Y %l:%i:%s %p') FROM dual INTO xResult;

END CASE;

END;

这是我的错误日志

日期时间间隔精度ORA-30088 PL/ sql :ORA-00933: sql命令未正确结束 PL/ SQL :忽略SQL语句 请-00103:当期望下列之一时遇到符号选择

Answer 1

我为甲骨文修改了你的存储过程-

CREATE OR REPLACE 
PROCEDURE DateTimeSP (xType IN VARCHAR2, xResult OUT VARCHAR2)
AS
BEGIN
    CASE
        WHEN xType = 'TIME' THEN 
            SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS')
              INTO xResult
              FROM dual;
        WHEN xType = 'DATE' THEN 
            SELECT TRUNC(SYSDATE)
              INTO xResult  -- Returns date in the default format
              FROM dual;
        WHEN xType = 'NOW' THEN 
           SELECT TO_CHAR(SYSTIMESTAMP)
             INTO xResult
             FROM dual;
        WHEN xType = 'NOWFORMAT' THEN 
           SELECT TO_CHAR(SYSTIMESTAMP, 'MM/DD/YYYY HH:MI:SS AM')
             INTO xResult
             FROM dual;
    END CASE;
END;
/

不要忘记在TO_CHAR上详细阅读Oracle Docs函数。对于格式化日期、时间和时间戳，它是一个非常灵活的功能。