Functions are
PLSQL BLocks, used for calculation.
PL/SQL functions create using
CREATE FUNCTION statement.
PL/SQL Functions Syntax:-
CREATE [OR REPLACE] FUNCTION [SCHEMA.]function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section
variable declarations;
constant declarations;
]
BEGIN
[executable_section
PL/SQL executable block;
]
[EXCEPTION]
[exception_section
PL/SQL Exception block
]
END [function_name];
PL/SQL Function Example:-
Example1:-
Create Function that take employee number and return their name.
CREATE or REPLACE FUNCTION fun_get_emp_name(
in_empno in number
)
RETURN varchar2
IS
name varchar2(20);
BEGIN
select ename
into name
from emp
where empno = in_empno;
return name;
END;
Function created.
Function Calling:-
1) Call in select statement.
select fun_get_emp_name(101) from dual;
2) Call in PLSQL Program
declare
vname varchar2(20);
begin
vname:=fun_get_emp_name(101);
dbms_output.put_line('Ename is '||vname);
end;
Drop Function:-
We can drop PL/SQL function using DROP FUNCTION statements.
Syntax:
DROP FUNCTION function_name;
Example:
DROP FUNCTION fun_get_emp_name;
After running above statement the pl/sql function fun_get_emp_name will get dropped.
No comments:
Post a Comment
Please do not enter any spam link in the comment box.