Oracle functions are database objects who takes some input, performs some action and returns something.
We tell Oracle to execute or run a function by calling them from the SELECT query. It takes inputs as parameters and returns a value to the caller.
There are two types of functions based on who created it. Those are,
- User-defined functions
- The system defined functions (created and provided by Oracle)
This page contains (Skip to topic)..
Functions which are written or designed by us are known as user-defined functions. We will talk on this extensively in
Advanced PLSQL course. However, let us create a basic function which greets the input user and returns a string as the greeting.
Here is the syntax to follow for creating a user-created function.
CREATE [OR REPLACE] FUNCTION function_name [ (parameter mode datatype [,parameter...]) ] RETURN return_datatype IS | AS [declaration_statements;] BEGIN executable_statements; [EXCEPTION when exception then Executable_statements;] END [function_name];
So our say_hello function is as follows.
CREATE OR REPLACE FUNCTION say_hello(name in varchar2) RETURN varchar2 AS BEGIN RETURN ‘hello ‘||name||’,Wellcome’; END say_hello;
Now that we knew how we can create user defined function, let us see what are Oracle built-in functions.
Oracle built-in function
The built-in functions also take a single input or inputs processes it and give a result. The thing that separates them from predefined functions is, Oracle provides these for us. We can use them by calling them.
SELECT length(‘Rozalin’) FROM dual;
Here are some of the Oracle built-in functions listed below.
Oracle built-in functions
|Oracle Functions||What this function is about?|
|SUBSTR||Extract a part of the string from a bigger string.|
|LENGTH||Finds the length of the input string|
|CONCAT||Concatenation of two strings|
|LOWER, UPPER||Converts oracle strings to lower or upper case|
|INSTR||Finds the start index of an occurrence of a character or string in a bigger string|
|LPAD, RPAD||Padding some characters to left or right|
|TO_CHAR||Converts any type to string|
|REPLACE||Replace a part of the string from a bigger string|
We will cover these string functions as part of this oracle basics course. So, let us see the built-in SUBSTR function syntax, usage with practical examples.