Stored Program Units (Procedures )
A stored procedure, function, or package is a PL/SQL program unit that:
Has a name.
Can take parameters, and can return values.
Is stored in the data dictionary.
Can be called by many users.
Naming Procedures and Functions
Because a procedure or function is stored in the database, it must be named. This distinguishes it from other stored procedures and makes it possible for applications to call it. Each publicly-visible procedure or function in a schema must have a unique name, and the name must be a legal PL/SQL identifier.
Parameters for Procedures and Functions
Stored procedures and functions can take parameters. The following example shows a stored procedure that is similar to the anonymous block
To execute the following, use CREATE OR REPLACE PROCEDURE...
PROCEDURE Get_emp_names (Dept_num IN NUMBER) IS
CURSOR c1 (Depno NUMBER) IS
SELECT Ename FROM Emp_tab
WHERE deptno = Depno;
FETCH c1 INTO Emp_name;
EXIT WHEN C1%NOTFOUND;
In this stored procedure example, the department number is an input parameter which is used when the parameterized cursor c1 is opened