CREATE function ts_fmt(TS timestamp, fmt varchar(20)) returnsvarchar(50) return
with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as ( select substr( digits (day(TS)),9),
substr( digits (month(TS)),9) , rtrim(char(year(TS))) , substr( digits (hour(TS)),9),
substr( digits (minute(TS)),9), substr( digits (second(TS)),9), rtrim(char(microsecond(TS)))
from sysibm.sysdummy1 ) select case fmt when 'yyyymmddhhmiss'
then yyyy || mm || dd || hh || mi || ss when 'mm/dd/yyyy'
then mm || '/' || dd || '/' || yyyy when 'yyyy/dd/mm hh:mi:ss'
then yyyy || '/' || mm || '/' || dd || ' ' || hh || ':' || mi || ':' || ss when 'nnnnnn'
then nnnnnn else 'date format ' || coalesce(fmt,' ') || ' not recognized.' end from tmp;
CREATE FUNCTION FUNC_GET_FOLDERAUTHORITYID ( username varchar(100), treenodeid integer )
RETURNS VARCHAR(300)
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE strCodeValue varchar(300);--
SET strCodeValue = (username || TO_CHAR(treenodeid));--
return strCodeValue ;--
end;
이와 같은 DB2에서 쓰는 함수 정의가 있는데 오라클로 하면 어떻게 해야하나요?
도와주세면 감사하겠습니다. |