Procedure and Function in PL SQL

“A procedures or function is a group or set of SQL and PL/SQL statements that perform a specific task.” A function and procedure is a named PL/SQL Block which is similar . The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

PL/SQL Block Type (Procedure)

PROCEDURE name

IS

[DECLARE]

BEGIN

–statements

[EXCEPTION]

END;

PL/SQL Procedures :

CREATE PROCEDURE GETEMP IS — HEADER
EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
LNAME EMPLOYEES.LAST_NAME%TYPE;
BEGIN
EMP_ID := 100;
SELECT LAST_NAME
INTO LNAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = EMP_ID;
DBMS_OUTPUT.PUT_LINE(‘LAST NAME: ‘||LNAME);
END;
/

PL/SQL Block Type (Function)

FUNCTION name

RETURN datatype IS

[DECLARE]

BEGIN

–statements

[EXCEPTION]

END ;

PL/SQL FUNCTIONS :

CREATE or REPLACE FUNCTION AVG_SALARY RETURN NUMBER IS
AVG_SAL NUMBER(8,2)
BEGIN
SELECT AVG(SALARY) INTO AVG_SAL FROM EMPLOYEES;
RETURN AVG_SAL;
END;
/