Do you know in Oracle PL/SQL How to Create Oracle SQL Functions and where you can use them. In this article of Oracle PL/SQL , I will tell you how to create function & uses .
Lets first understand Oracle SQL function.
Functions are built into Oracle Database and functions are available to use in SQL statement.
Single-row functions return a single result row for every row of a queried table or view.
Aggregate functions return a single result row based on groups of rows, rather than on single rows.
SELECT AVG(MAX(amount)) FROM employees GROUP BY department_id;
Analytic functions compute an aggregate value based on a group of rows.They differ from aggregate functions in that they return multiple rows for each group.
This Functions manipulate REF values, which are references to objects of specified object types.
Model functions can be used only in the
model_clause of the
SELECT statement. The model functions are:
These function are defined by user and can be used in a same way as Built-in functions. Lets see how we define function .
For Creating function we use Create Function Statement . Lets Understand by writing code.
CREATE OR REPLACE function TREASURY.CCLPROJECTNAME (PC varchar2) RETURN varchar2 IS val varchar2(850); begin select distinct PROJECT_HNAME INTO VAL FROM CCL_PROJECT_MASTER where PCODE= PC; return(val); end; /
After creating this function we have to use this function .
select CCLPROJECTNAME ('05Ab563') Project_Name from dual ;
In this way we create a user defined function.
Hope you understood the concept of Oracle SQL Function.
Related Topics :