Syntax of Create Function in Oracle PL/SQL

Create and drop function in Oracle PL/SQL

Function act as set to execution code which must return value.
It stored in data dictionary. So process is fast never compile each time.

Permission Required:

-- For Create the Function
CREATE PROCEDURE privilege for your own schema.
CREATE ANY PROCEDURE privilege to create function anywhere.</p>

--For execute the function
EXECUTE privilege is required for execution.
Example:
Grant create procedure to SCOTT:
grant EXECUTE on fn to SCOTT;

Syntax of Function:

CREATE OR REPLACE FUNCTION <function_name>
( <parameterl IN/OUT <datatype>
, <parameter2> IN/OUT <datatype>)
RETURN <datatype>
[ IS | AS ]
<declaration variable>
BEGIN
<execution> 
EXCEPTION
<exception handling>
END; 

Example of Create or Execute function:

-- Create function 
Create or Replace function fn 
return int
is 
begin
 return 1;
end;
/

---Execute the function
select fn from dual;

Another Example of Create Function

--Create function for Increment salary by 10% of all employees
Create or replace function increment_salary(v_salary number) 
return number
v_int_salary number;
begin
	v_int_salary := v_salary * 1.1;
	return v_int_salary;
End;
/

-- Execute the function 
select increment_salary(1000) from dual;

Drop Function

Drop function <function_name>

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.