Pl - SQL

Procedure: A procedure is a subprogram that performs a specific action.
[DECLARE
     --- declarations]
BEGIN
    --- statements
EXCEPTION
   --- handlers]
END;

As we do in programming languages C, C++, we can do certain tasks with the use of variables and codes, we can use while, if else, for loop in these programming languages, oracle also provide us if else for conditioning, for loops for looping and defining variables and use result of one query in another task in oracle with the use of PL/SQL.

If we consider a example of raising salary of an employee with the procedure raise_salary:

create table emp_audit(emp_id number(10), audit_comment varchar2(50));
CREATE OR REPLACE PROCEDURE raise_salary(emp_id number, amount number) is
    current_salary NUMBER;
    salary_missing EXCEPTION;
BEGIN
    SELECT
        sal INTO current_salary
    FROM
        emp
    WHERE
        empno = emp_id;

    IF current_salary IS NULL THEN
        RAISE salary_missing;
    ELSE
        UPDATE emp
            SET  sal = sal + amount
        WHERE empno = emp_id;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Salary Updated Successfully');
    END IF;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            INSERT INTO emp_audit values (emp_id, 'Invalid EMP NO');
            DBMS_OUTPUT.PUT_LINE('Invalid EMP NO');
            COMMIT;
        WHEN salary_missing THEN
            INSERT INTO emp_audit values (emp_id, 'Salary is NULL');
            DBMS_OUTPUT.PUT_LINE('Salary is NULL');
            COMMIT;
END;
set serveroutput on;

SQL> select EMPNO , sal from emp where rownum < 5;
SQL> exec raise_sal(7499,100);
Salary Updated Successfully

select EMPNO , sal from emp where rownum < 5;

SQL> update emp set sal = null where empno = 7499;

1 row updated.

SQL> exec raise_salary(7499, 100);
Salary is NULL
In procedure we can use insert, update and delete but in case of create table or alter table statements  we have to use execute_immediate.
execute_immediate('drop table emp');

NO_DATA_FOUNT is a predefind exception.

If we does not write a exception in procedure then an abnormal termination of program will happen when find an exception.

FUNCTION
---------------
A function is a subprogram that computes a value. Functions and procedures are structured alike, except that function have a RETURN clause.

Below function calculates and Returns the max salary for a Department No.

CREATE OR REPLACE FUNCTION max_dept_sal(dept_id number) RETURN number is
max_sal number;
BEGIN
    SELECT
        max(sal) INTO max_sal
    FROM
        emp
    WHERE
        deptno = dept_id;
    RETURN max_sal;
END;




select max_dept_sal(20) from dual;

Difference in function and procedure is function return a value but procedure does not.
In function Return followed by a return type, it may be number, char, varchar.
We can not run function as procedure because it returns a value. Procedure can run through exec procedure_name(arguments) but function will run within a query as select function_name(arguments) from dual.


PACKAGE
-------------
Package is a logical group by PLSQL programs e.g. procedures and functions. A package consists of Package specification and Package Body.