Create a Database Function in 5 min

Hi ,

The SQL CREATE FUNCTION statement is used to create stored functions that are stored in an Oracledatabase. A procedure or function is similar to a miniature program. Usually if we would want to get an output while passing an input , function would be widely used.

As always I like to give a scenario where this can be used

 

Scenario:

There is an application that usually runs a large piece of SQL and then based on the output decides the next steps of the process. With time the SQL code was larger than the application size and wanted an easier route where if a value is passed , it can search the database and provide an output which can be displayed in the application. This example below is based on Oracle Database.

 

Create the Function

Code :

CREATE OR REPLACE FUNCTION SCHEMA_NAME.FUNCTION_NAME(
V_INPUT IN number — The input variable name )

RETURN varchar2 IS V_OUTPUT varchar2(30);  –defined below in the code
BEGIN

select COLUMN into V_OUTPUT from TABLE_NAME where COLUMN= V_INPUT ;

RETURN (V_OUTPUT );

END SCHEMA_NAME.FUNCTION_NAME;

 

Here highlighted in blue is where you can edit and reuse the code . Also, in this case column value gets stored in variable V_OUTPUT based on the filter condition passed in V_INPUT.

 

Execution the function and testing

select SCHEMA_NAME.FUNCTION_NAME(123) from dual;

 

You can apply the function in you select statement to return an output.

 

Thanks

Jethin Abraham

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s