Oracle SQL Functions

Learn PL/SQL : Create Oracle SQL Functions

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 .

How to Create Oracle SQL Functions

Lets first understand Oracle SQL function.

What is a function ?

Functions are built into Oracle Database and functions are available to use in SQL statement.

Types of Functions in SQL

  • Single Row Function
  • Aggregate Function
  • Analytic Function
  • Object Reference Function
  • Model Function
  • User Defined Function

Single Row Function

Single-row functions return a single result row for every row of a queried table or view.

CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER

Aggregate Function

Aggregate functions return a single result row based on groups of rows, rather than on single rows.

AVG
COLLECT
CORR
CORR_*
COUNT
COVAR_POP

SELECT AVG(MAX(amount)) FROM employees GROUP BY department_id;

AVG(MAX(amount))

—————-

10925

Analytic Function

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.

Object Reference Function

This Functions manipulate REF values, which are references to objects of specified object types.

DEREF
MAKE_REF
REF
REFTOHEX
VALUE

Model Function

Model functions can be used only in the model_clause of the SELECT statement. The model functions are:

CV
ITERATION_NUMBER
PRESENTNNV
PRESENTV
PREVIOUS

User Defined Function

These function are defined by user and can be used in a same way as Built-in functions. Lets see how we define function .

Create Function in SQL

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 ;

Output:-

Project_Name

Irrigation Project.

In this way we create a user defined function.

Hope you understood the concept of Oracle SQL Function.

Related Topics :

About the Author

Prashant Goswami

I am the blog writer who will teach how to love programming. My experience in programming is about 6 years. And i am the lover of programming , so i will make you people.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: