-->

14 March 2021

PL/SQL Function

  Asp.Net CS By Example       14 March 2021
 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.

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.
logoblog

Thanks for reading PL/SQL Function

Previous
« Prev Post

No comments:

Post a Comment

Please do not enter any spam link in the comment box.