stored_programs_compared1.htm
Objective: Stored Procedure in Oracle, PostgreSQL and MySQL
MySQL :

DROP FUNCTION IF EXISTS ctof;
DELIMITER $$

CREATE FUNCTION ctof (i NUMERIC(4,2))
RETURNS NUMERIC(4,2)
BEGIN
DECLARE t1 NUMERIC(4,2) ;
set t1 = ((i * 9/5)+32);
RETURN t1;

END$$

Calling a stored function

/* calling the stored function with parameter */

select ctof(29.25);
Oracle :

/* compiled stored function */
create or replace
FUNCTION CTOF(i IN NUMBER)
RETURN NUMBER IS
t1 number(4,2);
BEGIN
dbms_output.put_line('Internal display');
dbms_output.put_line((i * 9/5) + 32);
t1:=((i*9/5)+32);
RETURN t1;
END ;

/* calling the stored function with parameter */
DECLARE
C NUMBER(4,2):=0;
BEGIN
C :=CTOF(29.25);
dbms_output.put_line('CTOF returned : '|| C);
END;
PostgreSQL :

CREATE OR REPLACE FUNCTION cfcal(i numeric
)
RETURNS numeric AS $$
DECLARE
temp1 numeric ;
BEGIN
RETURN (i * 9/5) + 32 ;
END;
$$ LANGUAGE plpgsql;

/* calling the stored function with parameter */

select cfcal(23.72);