How To Create Function In Oracle Sql Developer

Question: What is the proper way to call a PL/SQL function? I understand that a function is used in SQL, but is SQL the only way to call a PL/SQL function?

Answer: Calling Procedures from PL/SQL is easy, and there are several methods for invoking a function:

1 - Call an independent function from inside SQL from dual.

2 - Call the function from SQL using a table.

3 - Call a function within an assignment operator.

4 - Call a PL/SQL function from inside an "IF" Boolean expression.

With SQL, you simply embed the function call inside your SQL:

CREATE OR REPLACE FUNCTION
c_to_f (degree NUMBER) RETURN NUMBER IS
buffer NUMBER;
BEGIN
buffer := (degree * 9/5) + 32;
RETURN buffer;
END;
/
;

show errors

select c_to_f(45) from dual;

You don't always have to use the DUAL pseudotable to call a function, you can also pass a table name to the function:

select plus_tax_2('8042', 'BU1111') from sales where category = 'HISTORY';

Calling Functions from PL/SQL

In a PL/SQL block, a function can be called in a SQL statement (as seen above) or used in a simple assignment operation.

SQL> declare
2 v_far number := &Farenheit;
3 v_cels number := 0;
4 begin
5 v_cels := f2c(v_far);
6 dbms_output.put_line(
'Degrees Celcius is '|| f2c(v_far));
7 end;
8 /

Enter value for farenheit: 46

old 2: v_far number := &Farenheit;
new 2: v_far number := 46;

Degrees Celcius is 7.77777777777777777777777777777777777778

Functions can also be used to validate variables. In the example below the value of n_test is validated before additional processing.

SQL> create or replace function valid_numb
2 (n_numb IN number)
3 return boolean
4 as
5 begin
6 if n_numb < 10 then return true;
7 else return false;
8 end if;
9 end;
10 /

Function created.

SQL> declare
2 n_test number := &Test;
3 begin
4 if (valid_numb(n_test))
5 then dbms_output.put_line('Valid');
6 else dbms_output.put_line('Invalid');
7 end if;
8 end;
9 /

Enter value for test: 8

old 2: n_test number := &Test;
new 2: n_test number := 8;

Valid

PL/SQL procedure successfully completed.

SQL> /

Enter value for test: 12

old 2: n_test number := &Test;
new 2: n_test number := 12;

Invalid

PL/SQL procedure successfully completed.

This is a simple test to determine if the number is less than 10. The function valid_numb returns a type Boolean, so it can be used as the condition of a comparison operation. Line 4 calls the function in order to get a true/false value which is used to control the program execution.

When should you use a procedure or a function? Since most modules can be written either as a procedure or a function, the decision between them comes down to efficiency and maintainability. Functions are preferred when a module returns one value. This value may be a variable, record or array, but as long as the module always returns one value, it is a good candidate for a procedure.

Get the Complete
Oracle SQL Tuning Information

The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.