In common with the procedure in oracle, Altibase Function statement is almost equal to that of oracle.
Notices
Using return statement, Unless a user specify an column length for varchar or char data type, column length might be implicitly set to 1.
LOB type value is prohibited to use as parameter value or return value.
Example
Oracle
Altibase
Comments
CREATE OR REPLACE FUNCTION half_of_square(original NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (original * original)/2 + (original * 4);
END half_of_square;
/
CREATE OR REPLACE FUNCTION half_of_square(original NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (original * original)/2 + (original * 4);
END half_of_square;
/
CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN NUMBER AS
rows NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;
RETURN rows;
END;
/
CREATE FUNCTION row_count (tab_name VARCHAR2(40)) RETURN NUMBER AS
rows NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;
RETURN rows;
END;
/
If you omit column size for char data type(char, varchar etc..), it is regarded as 1 byte length of char data type((e.g) char(1), varchar2(1)) in Altibase.
CREATE OR REPLACE FUNCTION get_rate
(p1 IN CHAR, p2 IN CHAR, p3 IN VARCHAR)
RETURN NUMBER
AS
v_rate NUMBER;
BEGIN
SELECT NVL(SUM(rate), 0)
INTO v_rate
FROM (SELECT rate
FROM t1
WHERE start_date = TO_DATE(p1)
AND end_date = TO_DATE(p2)
AND user_id = '000000' || p3
AND seq_no = 0);
RETURN v_rate;
END;
/
CREATE OR REPLACE FUNCTION get_rate
(p1 IN CHAR(30), p2 IN CHAR(30), p3 IN VARCHAR(9))
RETURN NUMBER
AS
v_rate NUMBER;
BEGIN
SELECT NVL(SUM(rate), 0)
INTO v_rate
FROM (SELECT rate
FROM t1
WHERE start_date = TO_DATE(p1)
AND end_date = TO_DATE(p2)
AND user_id = '000000' || p3
AND seq_no = 0);
RETURN v_rate;
END;
/
As parameter variable, column size for char data type should be omitted in oracle as above.
