oracle MOD命令调用函数

vx6bjr1n  于 2022-12-11  发布在  Oracle
关注(0)|答案(1)|浏览(101)

I have two functions, which are working fine. I want to use the MOD or decode command to call the functions with different options but I can't seem to get the code below to work.
Below is my test CASE. Any help would be greatly appreciated. Thanks in advance for your time and expertise.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'

CREATE OR REPLACE FUNCTION random_timestamp(
      p_from      IN TIMESTAMP,
      p_to        IN TIMESTAMP,
      p_fraction  IN VARCHAR2   DEFAULT 'Y'
    ) RETURN TIMESTAMP
   IS
      return_val_y TIMESTAMP     := p_from + dbms_random.value () * (p_to - p_from + INTERVAL '1' DAY);
      return_val_n TIMESTAMP (0) := return_val_y;
 BEGIN
      RETURN  CASE
                    WHEN UPPER (SUBSTR (p_fraction, 1, 1)) = 'Y'
          THEN return_val_y
          ELSE return_val_N
          END;
END random_timestamp;
/

CREATE OR REPLACE FUNCTION random_interval(
  p_min      IN NUMBER,
  p_max      IN NUMBER,
  p_duration IN VARCHAR2,
  p_fraction  IN VARCHAR2   DEFAULT 'Y'
) RETURN INTERVAL DAY TO SECOND
IS
      return_val_y   INTERVAL DAY TO SECOND   := NUMTODSINTERVAL(DBMS_RANDOM.VALUE(p_min, p_max), p_duration);

   return_val_n   INTERVAL DAY TO SECOND   :=
( EXTRACT(DAY FROM return_val_y) * 24 * 60 * 60
         + EXTRACT(HOUR FROM return_val_y) * 60 * 60
         + EXTRACT(MINUTE FROM return_val_y) * 60
         + FLOOR(EXTRACT(SECOND FROM return_val_y))
         ) * INTERVAL '1' SECOND;
BEGIN
      RETURN  CASE
                    WHEN UPPER (SUBSTR (p_fraction, 1, 1)) = 'Y'
          THEN return_val_y
          ELSE return_val_N
       END;
 END random_interval;
/

/* can't get this to work */

SELECT
    CASE MOD(LEVEL, 2)
     WHEN 0
     THEN   
         random_timestamp(TIMESTAMP '2022-04-01 00:00:00', TIMESTAMP '2022-04-30 00:00:00', 'Y') as ts,
         random_interval(1, 10, 'HOUR', 'Y') as invr
     ELSE 
         random_timestamp(TIMESTAMP '2022-04-01 00:00:00', TIMESTAMP '2022-04-30 00:00:00', 'N') as ts,
         random_interval(1, 10, 'HOUR', 'N') as invr
     END
FROM   dual
CONNECT BY level <= 10;
yhived7q

yhived7q1#

CASE表达式返回单个表达式;不是多个表达式。如果您想要多个表达式,请将CASE从 Package 两个表达式(这是错误的)移动到每个函数调用内部:

SELECT random_timestamp(
         TIMESTAMP '2022-04-01 00:00:00',
         TIMESTAMP '2022-04-30 00:00:00',
         CASE MOD(LEVEL, 2) WHEN 0 THEN 'Y' ELSE 'N' END
       ) as ts,
       random_interval(
         1,
         10,
         'HOUR',
         CASE MOD(LEVEL, 2) WHEN 0 THEN 'Y' ELSE 'N' END
       ) as invr
FROM   dual
CONNECT BY level <= 10;

fiddle

相关问题