plsql_overview3.htm
Objectives:
  • Creating Function with a parameter
    • IN - read only parameter, passed by reference
    • OUT - returns to the calling routine, passed by value
  • calling function with a block and exec
  • Also review : stored_programs_compared1.htm
Script used for a function

--centfer.sql
create or replace
FUNCTION centfer(i IN NUMBER)
RETURN NUMBER AS
v_num NUMBER;j NUMBER;
BEGIN
v_num :=((i * 9/5) + 32 );
j:= v_num;
RETURN j ;
END centfer;
/
 

Calling from SQL * PLUS pane:

declare
n2 number(5,2);
begin
n2:=centfer(29.45);
DBMS_OUTPUT.PUT_LINE(' 29.25 celcius :'|| n2);
end;

execute dbms_output.put_line(centfer(29.45));
Script used for a Procedure which returns a value as an out(put) parameter

--procentfer.sql
create or replace
PROCEDURE procentfer(i IN NUMBER, j OUT NUMBER)
AS
v_num NUMBER;
BEGIN
v_num :=((i * 9/5) + 32 );
j:= v_num;
END procentfer;
/

Calling the above procedure

declare
n2 number(5,2);
begin
procentfer(29.45, n2);
DBMS_OUTPUT.PUT_LINE(' 29.25 celcius :'|| n2);
end;
/
Glimpse of Other DBMS: MySQL
PL/PGSQL