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 usedScenario: 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

‍

‍

‍