Oracle SQL function overloading

    xiaoxiao2021-12-10  9

    Sample of implement Oracle SQL function overloading, and default parameter.

    To define your own to_char(...) function support different kinds of input data type. Please notice they must be defined in a function package.

    CREATE OR REPLACE PACKAGE <PKGNAME> AS FUNCTION MY_TOCHAR(PVALUE IN TIMESTAMP) RETURN VARCHAR2; FUNCTION MY_TOCHAR(PVALUE IN DATE, PFORMAT IN VARCHAR DEFAULT 'YYYY-MM-DD') RETURN VARCHAR2; FUNCTION MY_TOCHAR(PVALUE IN NUMBER, PRECISION IN INT DEFAULT 0, SCALE IN INT DEFAULT 0) RETURN VARCHAR2; END; / CREATE OR REPLACE PACKAGE BODY <PKGNAME> AS FUNCTION MY_TOCHAR(PVALUE IN TIMESTAMP) RETURN VARCHAR2 IS BEGIN RETURN TO_CHAR(PVALUE, 'YYYY-MM-DD-HH24.MI.SS.FF6'); END; FUNCTION MY_TOCHAR(PVALUE IN DATE, PFORMAT IN VARCHAR) RETURN VARCHAR2 IS BEGIN RETURN TO_CHAR(PVALUE, PFORMAT); END; FUNCTION MY_TOCHAR(PVALUE IN NUMBER, PRECISION IN INT, SCALE IN INT) RETURN VARCHAR2 IS VFORMAT VARCHAR2(100); BEGIN IF PRECISION > 0 THEN VFORMAT := ...; IF SCALE > 0 THEN VFORMAT := ...; END IF; RETURN LTRIM(TO_CHAR(PVALUE, VFORMAT)); ELSE RETURN TO_CHAR(PVALUE); END IF; END; END; /

    转载请注明原文地址: https://ju.6miu.com/read-700405.html

    最新回复(0)