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.
Functions accepts IN,OUT and IN OUT parameters
It should be called in PL/SQL Block of Code only
1 create or replace function getemp 2 RETURN SYS_REFCURSOR 3 as 4 emp_ref_type SYS_REFCURSOR; 5 EMP_REC EMP%ROWTYPE; 6 BEGIN 7 OPEN emp_ref_type for select * from emp; 14 RETURN emp_ref_type; 15* END;
1 create or replace function checkAge(n number) 2 return BOOLEAN 3 as 4 begin 5 if n > 18 then 6 return TRUE; 7 else 8 return FALSE; 9 end if; 10* end checkAge;Calling checkAge function in PL/SQL Block. it should be checked/called in PL/SQL Block of code , because return type is PL/SQL Data type.
BEGIN IF CHECKAGE(10) THEN DBMS_OUTPUT.PUT_LINE('UNDER AGE'); ELSE DBMS_OUTPUT.PUT_LINE('ELIGIBLE'); END IF; END;
A Table Function is a User-Defined Function that returns collection of rows(an Associate Arrays,Nested Tables,orVARRAY)
SELECT * FROM TABLE(table_function_name(parameter_list))
PIPELINED table functions are special case of Table Functions.
ADS