Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 10 Current »


Overview

In common with the procedure in ORACLE, ALTIBASE HDB 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 HDB

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 HDB.

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.

  • No labels