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

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.

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.

Calling Functions in Pl/SQL Code

Functions accepts IN,OUT and IN OUT parameters

It should be called in PL/SQL Block of Code only

Function Returning REf CURSOR

 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;

Function Returns BOOLEAN Datatype

 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;

Forward Declarations

Table Functions

      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.