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.
Difference between procedure and function in PL SQL:
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 Block Type (Function):
FUNCTION name
RETURN datatype 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 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;
/
Difference between SQL and PL/SQL:
| SQL | PL/SQL |
| SQL is a single query that is used to perform DML and DDL operations. | PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc. |
What is the difference between ‘IS’ and ‘AS’ in PL/SQL?
The PL/SQL language evolved such the the “IS” and “AS” operators are equivalent. Functionally the “IS” and “AS” syntax performs identical functions and can be used interchangeably.
