HomeOracle Page 3 - Database Interaction with PL/SQL, Named Notations, Storing Procedures and Functions
What are STORED PROCEDURES? - Oracle
This is part 16 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we worked with PL/SQL TABLE types in between sub-programs. In this article, we will look into Named Notation, default values of parameters, stored procedures, stored functions and finally introduce the concepts of package and package body.
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:
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!