Function
PL/SQL FUNCTION:-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
SQL>CREATE or REPLACE FUNCTION fun_emp_name(no in number)
RETURN varchar2
IS
name varchar2(20);
BEGIN
select ename into name from emp where empno = no;
return name;
END;
/
Function created.
Function Calling:-
1) Call in select statement
select fun_emp_name(7369) from dual;
2) Call in PLSQL Program
declare
vname varchar2(20);
begin
vname:=fun_emp_name(7369);
dbms_output.put_line('Ename is '||vname);
end;
/
OR
SQL>DECLARE
no number :=&no;
name varchar2(20);
BEGIN
name := fun1(no);
dbms_output.put_line('Name:'||' '||name);
end;
/
Drop Function:-We can drop PL/SQL function using DROP FUNCTION statements.
Syntax
DROP FUNCTION function_name;
Example
SQL>DROP FUNCTION fun_emp_name;
Function dropped.
No comments:
Post a Comment