Welcome

PL SQL Tutorial

PL SQL Functions

PL/SQL Stored Functions or Database functions, which executes sequence SQL statements(zero or more),and must return a Value.This Value can be of SQL Type or PL/SQL Type. It has name and accepts parameters. in Simple It's a precompiled sub-routines stored in the Database. Main aim of Modularizing the application into multiple taks each task is written as functions or procedures, so that these sub-routines can be called repeteadly/wherever necessary

     CREATE [OR REPLACE] FUNCTION name[ (parameter [,parameter]) ]
	RETURN return_datatype
     { IS | AS }
	[declaration_section]
     BEGIN
		executable_section
	[EXCEPTION
	exception_section]
     END [name];
     

Stored Functions returns a value, they can be used with in the expressions, where as procedures cannot.

Get Manager subordinates Count

Create or replace function getempcount(manager numeric)
return numeric
is
ecount numeric(3);
begin
     select count(empno) into ecount from emp where mgr = manager;
return ecount;
end;

Calling PL/SQL Functions in SQL

Calling PL/SQL function in SQL reguires functions should take IN parameters only/No parameters, NO OUT/INOUT parameters accepted. and also function must return SQL Data Types only. For ex: in Oracle BOOLEAN data type not supported in SQL,but supported in PL/SQL. function returning BOOLEAN value not accepted in SELECT statement.