What are STORED PROCEDURES? - Oracle

The procedures which get stored within the database can be called STORED PROCEDURES. In the past few articles, we worked with several procedures within the PL/SQL program. The PL/SQL program is automatically lost from memory once you close your SQL*Plus or iSQL*Plus (unless you save it in an SQL file). 

Now we shall see how to store procedures in the database to make them STORED PROCEDURES. Let us consider the following program:

create or replace procedure dispEmp as
  cursor c_emp is
   select ename, sal from emp;
 begin
  for r_emp in c_emp
  loop
   dbms_output.put_line(r_emp.ename || ',' || r_emp.sal); 
  end loop;
 end;

Make sure that you are not actually executing the procedure in the above program. You are just asking the Oracle database to accept and store this new procedure ‘dispEmp’ into its database. That is why you will get a message along the lines of ‘Procedure successfully Created’. 

The above created a procedure successfully inside the database. Now, how do we execute it? There exists a separate command ‘execute’ exclusively dedicated to executing stored procedures. The command to execute the above stored procedure would be the following:

Execute dispEmp;

This causes the stored procedure ‘dispEmp’ to be executed successfully.  Now, how do we execute the above stored procedure from another PL/SQL? The following shows how to do it.

BEGIN
 dispEmp;
END;

That’s it. Very simple. There exist no declarations within the above PL/SQL program. The only issue is that we need to call an existing stored procedure. We need not use the command ‘Execute’ here, as it is an SQL command.

Now wherever you carry your database, the STORED PROCEDURES also come along with the database!