Stored Program Units (Procedures )

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

Caution:
To execute the following, use CREATE OR REPLACE PROCEDURE...

PROCEDURE Get_emp_names (Dept_num IN NUMBER) IS
Emp_name VARCHAR2(10);
CURSOR c1 (Depno NUMBER) IS
SELECT Ename FROM Emp_tab
WHERE deptno = Depno;
BEGIN
OPEN c1(Dept_num);
LOOP
FETCH c1 INTO Emp_name;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Emp_name);
END LOOP;
CLOSE c1;
END;


In this stored procedure example, the department number is an input parameter which is used when the parameterized cursor c1 is opened