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

PL/SQL Function Syntax:
     CREATE [OR REPLACE] FUNCTION name[ (parameter [,parameter]) ]
	RETURN return_datatype
     { IS | AS }
     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
ecount numeric(3);
     select count(empno) into ecount from emp where mgr = manager;
return ecount;

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.