PL/SQL Function

Create function syntax:

CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ]

   RETURN return_data_type

IS | AS -- Declarative part (optional)

-- Declarations of local types, variables, subprograms

BEGIN -- Executable part (required)

-- SQL statements
-- PL/SQL statements

EXCEPTION -- Exception-handling part (optional)

-- Exception handlers for exceptions (errors) raised in executable part

END [function_name]; -- End of executable part (required)

Create function example:

create or replace function get_name (p_dep_id number)
return varchar2 as
 v_error varchar2(250);
 v_name varchar2(100);
begin
 select lastname into v_name from employee where departmentid = p_dep_id;
 return v_name;
exception
 when no_data_found then
  v_error := 'No data found!';
  return v_error;
 when too_many_rows then
  v_error := 'Too many rows!';
  return v_error;
end;

Execute function example:

declare
v_name varchar2(100);
begin
v_name := get_name(31);
DBMS_OUTPUT.PUT_LINE('The name is: '||v_name);
end;

Drop function syntax:

DROP FUNCTION function_name; 

Drop function example:

DROP FUNCTION get_name;