CREATE FUNCTION

CREATE [OR REPLACE] FUNCTION [schema.]name
    [ ( arg_name arg_type [ ,arg_name arg_type ]... ) ]
    RETURNS
    {
        return_data_type
    |
        TABLE table_name ( column_name column_type [, column_name column_type ]... )
    }
    [ DETERMINISTIC | NOT DETERMINISTIC ]
    [ LANGUAGE SQL | LANGUAGE JAVA ]
    [ SECURITY INVOKER | SECURITY DEFINER ]
{
    EXTERNAL 'jar_id:method_name'
|
    AS
        sql_code_block
    END_FUNCTION;
}

DROP FUNCTION IF EXISTS func_is_date;
SET DELIMITER @
CREATE FUNCTION func_is_date (i_date string)
       RETURNS BOOLEAN
       DETERMINISTIC
       LANGUAGE SQL
       SECURITY INVOKER
AS
   VAR l_out BOOLEAN = 'TRUE';
   VAR l_timestamp TIMESTAMP;
   try
       l_timestamp = (SELECT cast(DATE(i_date) as timestamp) FROM DUAL);
   catch(error)
       l_out = 'FALSE';
   end_try;
   RETURN l_out;
END_FUNCTION;
@
SET DELIMITER ;

SELECT func_is_date('2014-08-01') from dual;
 [TEST.FUNC_IS_DATE]
 -------------------
         TRUE

SELECT func_is_date('2014-09-45') from dual;
 [TEST.FUNC_IS_DATE]
 --------------------
        FALSE

SET OUTPUT VERTICAL;
SELECT * FROM system.functions;
==================================== Row #1 ====================================
SCHEMA: TEST
FUNCTIONNAME: FUNC_IS_DATE
PARAMETERCOUNT: 1
CREATOR: DBA
FUNCTIONTYPE: 0
FUNCTIONTEXT: (i_date string)
       RETURNS BOOLEAN
       DETERMINISTIC
       LANGUAGE SQL
       SECURITY INVOKER
AS
   VAR l_out BOOLEAN = 'TRUE';
   VAR l_timestamp TIMESTAMP;
   try
       l_timestamp = (SELECT cast(DATE(i_date) as timestamp) FROM DUAL);
   catch(error)
       l_out = 'FALSE';
   end_try;
   RETURN l_out;
END_FUNCTION
VERSION: 1

SET DELIMITER @
CREATE FUNCTION function1 (in_parm1 STRING)
      RETURNS STRING
AS
      THROW 'function1 with 1 parameter';
END_FUNCTION;
@

CREATE FUNCTION function1 (in_parm1 STRING, in_parm2 STRING)
      RETURNS STRING
AS
      THROW 'function1 with 2 parameters';
END_FUNCTION;
@
SET DELIMITER ;

SELECT function1('string1') FROM DUAL;
function1 with 1 parameter

SELECT function1('string1','string2') FROM DUAL;
function1 with 2 parameters

DROP FUNCTION function1;
/* function TEST.FUNCTION1 is an overloaded function: use the syntax */
/* <schema>.<name>/<parameter count> to identify the one to be dropped */

DROP FUNCTION function1/1;
DROP FUNCTION function1/2;

DROP FUNCTION IF EXISTS fnc_position;
SET DELIMITER @
CREATE FUNCTION fnc_position (i_position STRING)
      RETURNS string
AS
      RETURN (SELECT CASE i_position
                WHEN 'C' then 'Center'
                WHEN 'D' then 'Defense'
                WHEN 'G' then 'Goalie'
                WHEN 'L' then 'Left Wing'
                WHEN 'R' then 'Right Wing'
                WHEN 'F' then 'Forward'
                ELSE 'UnKnown'
               END FROM DUAL);
END_FUNCTION;
@
SET DELIMITER ;

CREATE VIEW vw_players_list AS
 SELECT FIRSTNAME, LASTNAME, fnc_position(position) as position_name
  FROM PLAYERS;

SELECT * FROM vw_players_list ORDER BY position_name;
   FIRSTNAME     LASTNAME   POSITION_NAME
 ------------- ------------ --------------
 Antti         Aalto            Center
 Reg           Abbott           Center
 Sid           Abel             Center
  ...

DROP FUNCTION IF EXISTS fnc_position;
#Error: function "USER.FNC_POSITION/1" is referenced by view USER.VW_PLAYERS_LIST and cannot be dropped
DROP VIEW vw_players_list;
DROP FUNCTION IF EXISTS fnc_position;

DROP FUNCTION IF EXISTS fnc_tbl_get_teams;
SET DELIMITER @
CREATE FUNCTION fnc_tbl_get_teams (i_PlayerId STRING)
      RETURNS TABLE fnc_tbl_get_teams (
          PlayerId     STRING,
          year         INTEGER,
          position     VARCHAR(4),
          team_name    VARCHAR(48),
          stint        INTEGER,
          gamesplayed  INTEGER,
          conferenceid VARCHAR(2),
          Divisionid   VARCHAR(2) )
AS
    RETURN ( SELECT
              CAST(s.playerId AS STRING) AS PLAYERID,
              s.year,
              s.position,
              t.name AS TEAM_NAME,
              s.stint,
              s.gamesplayed,
              t.conferenceid,
              t.divisionid FROM scoring s, teams t
            WHERE s.playerId = i_PlayerId
               AND s.year = t.year
               AND s.teamid = t.teamid );
END_FUNCTION;
@

SET DELIMITER ;

SELECT p.playerid,
    p.firstname as FIRST,
    p.lastname as LAST,
    f.year,
    f.position as POS,
    f.team_name,
    f.stint,
    f.gamesplayed as GAMES,
    f.conferenceid as CONF,
    f.divisionid as DIV
FROM players p, fnc_tbl_get_teams(p.playerid) f
 WHERE p.firstnhl = 2010 and p.lastnhl = 2011
 ORDER BY p.playerid, f.year, f.stint;
 PLAYERID  FIRST LAST  YEAR  POS     TEAM_NAME       STINT  GAMES CONF DIV
 --------- ----- ----- ----- --- ------------------- ------ ----- ---- ---
 adamlu01  Luke  Adam  2010   L  Buffalo Sabres        1      19   EC  NE
 adamlu01  Luke  Adam  2011   L  Buffalo Sabres        1      52   EC  NE
 aulieke01 Keith Aulie 2010   D  Toronto Maple Leafs   1      40   EC  NE
 aulieke01 Keith Aulie 2011   D  Toronto Maple Leafs   1      17   EC  NE
 aulieke01 Keith Aulie 2011   D  Tampa Bay Lightning   2      19   EC  SE
...

DROP FUNCTION IF EXISTS fnc_tbl_get_teams;
SET DELIMITER @
CREATE FUNCTION fnc_tbl_get_teams (i_PlayerId STRING)
      RETURNS TABLE fnc_tbl_get_teams (
        PlayerId     STRING,
        year         STRING,
        position     STRING,
        team_name    STRING,
        stint        STRING,
        games_played INTEGER,
        conference   STRING,
        Division     STRING )
AS
      INSERT INTO fnc_tbl_get_teams
        SELECT s.playerId,
               s.year,
               s.position,
               t.name,
               s.stint,
               s.gamesplayed,
               t.conferenceid,
               t.divisionid
          FROM scoring s, teams t
         WHERE s.playerId = i_PlayerId
           AND s.year = t.year
           AND s.teamid = t.teamid;
      RETURN;
END_FUNCTION;
@

SET DELIMITER ;

SELECT p.playerid,
    p.firstname as FIRST,
    p.lastname as LAST,
    f.year,
    f.position as POS,
    f.team_name,
    f.stint,
    f.games_played as GAMES,
    f.conference as CONF,
    f.division as DIV
FROM players p, fnc_tbl_get_teams(p.playerid) f
 WHERE p.firstnhl = 2010 and p.lastnhl = 2011
 ORDER BY p.playerid, f.year, f.stint;
 PLAYERID   FIRST LAST YEAR  POS        TEAM_NAME      STINT  GAMES CONF DIV
 --------- ------ ---- ----- --- --------------------- ------ ----- ---- ---
 adamlu01  Luke   Adam  2010  L   Buffalo Sabres          1   19     EC  NE
 adamlu01  Luke   Adam  2011  L   Buffalo Sabres          1   52     EC  NE
 aulieke01 Keith  Aulie 2010  D   Toronto Maple Leafs     1   40     EC  NE
 aulieke01 Keith  Aulie 2011  D   Toronto Maple Leafs     1   17     EC  NE
 aulieke01 Keith  Aulie 2011  D   Tampa Bay Lightning     2   19     EC  SE
...

DROP FUNCTION IF EXISTS fnc_format_name;
SET DELIMITER @
CREATE FUNCTION fnc_format_name (team_id STRING)
  RETURNS TABLE output (fullname VARCHAR(50))
AS
  RETURN ( SELECT firstname||' '||lastname AS fullname
             FROM players
            WHERE playerid IN (SELECT playerid
                                 FROM scoring
                                WHERE teamid = team_id)
         );
END_FUNCTION;
@
SET DELIMITER ;

SELECT fnc_format_name.fullname FROM fnc_format_name('CHI') LIMIT 3;
   FULLNAME
 -------------
 Clarence Abel
 Sid Abel
 Craig Adams