Home arrow Oracle arrow 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.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, Named Notations, Storing Procedures and Functions
  2. What are Parameter Default values?
  3. What are STORED PROCEDURES?
  4. What are STORED FUNCTIONS?
  5. PACKAGE and PACKAGE BODY
By: Jagadish Chatarji
Rating: starstarstarstarstar / 25
September 06, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: