create_replace_function2.htm
Convert Celsius to Fahrenheit
  • The example below,  function, which would return return a numeric value from an equation  embedded in a packet "$$' . This packet suffices as an Alias of a PL/pgSQL function.
  • You may call this UDF (user defined function) using server side scripts like PHP with SQL constructs.
  • Formatting the returned value with unlimited decimal places to two decimal points with "SQL ROUND() Function", which is supported by SQL engine within PL/pgsSQL
  • Note : With PHP as an interface language, the syntax of calling  a function differs between Oracle and PostgreSQL
    • Oracle : begin :bv:=CTOF(29.25) ; //
    • PostgreSQL : select  CTOF(29.25);
CREATE OR REPLACE FUNCTION cfcal(i numeric
)
RETURNS numeric AS $$
DECLARE
temp1 numeric ;
BEGIN
RETURN (i * 9/5) + 32 ;
END;

$$ LANGUAGE plpgsql;
select cfcal(37.6);
Using psql command line
Query was saved with a custom name
However: Object browser will keep name as created with psql, and it is to note that file that saved with sql extension can have any reasonable name

 
SQL  ROUND FUNCTION:

 

Using PHP scripts:

In Oracle the  function oci_bind_by_name, binds to PHP variable which defer SQL injection through PHP script.
Script : PLSQL Script:

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 ;

PHP Script: Using I : oci_bind_name for stored function
<?php
$sql = "";
$conn = oci_connect('hr', 'Son', 'localhost/orcl.gateway.2wire.net');
-----
$stmt = oci_parse($conn, $sql);
oci_bind_by_name($stmt, ":bv", $p1, 6);
$result= oci_execute($stmt);
echo"<br/> Output ..".$p1. ".. end of message<br/>";
oci_free_statement($stmt);
oci_close($conn);
echo "<br/> Oracle db connection closed";
//
}
?>

Calling CTOF : begin :bv:=CTOF(29.25); end;

If you don want to use oci_bind_by_name, you may simply call the above function, like "select CTOF(25.21) as CElFah from dual"

PHP Script :

<?php

------

$stmt = oci_parse($conn, $sql);
oci_execute($stmt);

---

?>