RelationalDBDesign RelationalDBDesign


Data Manipulation   «Prev  Next»

Oracle Functions Returned Values

  1. TO_CHAR(BIRTH_DATE,'yy/dd HH24:MI') , 67/05 07:30
  2. ROUND(TRUNC(LENGTH(FIRSTNAME)+BANK_BALANCE,1),0), 1249
  3. TO_CHAR(BIRTH_DATE+5,'DD') || '/' ||
    TO_CHAR(LAST_DAY(BIRTH_DATE),'DD') ||
    TO_CHAR( BIRTH_DATE,' HH:MI')
    , 10/30 07:30
  4. FLOOR(BANK_BALANCE/100)*100+INTEREST_RATE*1000 , 1245.1
  5. SIGN(TO_DATE('01-JAN-75') - BIRTH_DATE) * TRUNC(BANK_BALANCE,-2), 1200

Create function Syntax

The syntax for the create function command is more complicated than the syntax for the create procedure command. At a high level, the syntax is
create [or replace] function [schema .] function
[( argument [ in | out | in out ] [nocopy] datatype
[, argument [ in | out | in out ] [nocopy] datatype]...
)]
return datatype
[{ invoker_rights_clause | deterministic | parallel_enable_clause }
[ invoker_rights_clause | deterministic | parallel_enable_clause ]...
]
{ { aggregate | pipelined } using [schema .] implementation_type
| [pipelined] { is | as } { pl/sql_function_body | call_spec }};

Both the header and the body of the function are created by this command. The return keyword specifies the datatype of the function's return value. This can be any valid PL/SQL datatype. Every function must have a return clause, since the function must, by definition, return a value to the calling environment. The following example shows a function named OVERDUE_CHARGES, which returns the overdue book charges by person, based on calculations against the BOOKSHELF_CHECKOUT table. The input is the name of the person, while the output is the balance for that person.