How stored functions can be called from SQL : Function Definition « Stored Procedure Function « Oracle PL / SQL






How stored functions can be called from SQL

    

SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'Enn', 'F');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL> CREATE TABLE myTable
  2    (num_col    NUMBER
  3    ,char_col   VARCHAR2(60));

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION FullName (p_empID  emp.ID%TYPE) RETURN VARCHAR2 IS
  2
  3    v_Result  VARCHAR2(100);
  4  BEGIN
  5    SELECT fname || ' ' || lname INTO v_Result FROM emp WHERE ID = p_empID;
  6
  7    RETURN v_Result;
  8  END FullName;
  9  /

Function created.

SQL>
SQL>
SQL> SELECT FullName(ID) full_name FROM emp WHERE ID < 10 ORDER BY full_name;

FULL_NAME
--------------------------------------------------------------------------------
A B
C D
Enn F
G H
G Z

5 rows selected.

SQL>
SQL>
SQL> drop table emp;

Table dropped.

SQL> drop table myTable;

Table dropped.

   
    
    
    
  








Related examples in the same category

1.Define and call a function
2.Define and use function in select clause
3.A stored function.
4.Use user-defined function in if statement
5.Recursive function
6.function with no return type
7.Recursive function Factorial
8.A local function
9.Recursive function 2
10.demonstrates the behavior of the DETERMINISTIC keyword.
11.Function to convert celsius to fahrenheit
12.Function to convert fahrenheit to celsius
13.A function is executed like any other SQL built-in function:
14.Count Employee from a function and return value back
15.Raise exception from inner function