PL SQL Tutorial
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 } [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.
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 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.
ADS