Home arrow Oracle arrow Page 2 - Subprocedures and Oracle PL/SQL Subprograms

Creating a Stored Procedure - Oracle

In this second part of a six-part series on Oracle PL/SQL subprograms, you will learn how Oracle implements subprograms, and how to create a stored procedure. This article is excerpted from chapter 36 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

  1. Subprocedures and Oracle PL/SQL Subprograms
  2. Creating a Stored Procedure
By: Apress Publishing
Rating: starstarstarstarstar / 1
March 07, 2011

print this article



The following abbreviated syntax is available for creating a stored procedure; see the Oracle Database XE documentation for a complete definition:

CREATE [OR REPLACE] PROCEDURE procedure_name ([parameter[, ...]])
   [characteristics, ...] [IS | AS] plsql_subprogram_body

The following is used to create a stored function:

CREATE [OR REPLACE] FUNCTION function_name ([parameter[, ...]])
   RETURNS type
[characteristics, ...] [IS | AS] plsql_subprogram_body

Finally, you create and use anonymous PL/SQL blocks as follows:


TheDECLARE section is optional regardless of whether you are writing a procedure, a function, or an anonymous block. As you can infer from the syntax, you cannot pass variables, return variables, or reference the block from any other procedure or function; you can, however, save the block in a text file and retrieve it from the SQL Commands interface or embed the block within another stored function or procedure.

In this example, you use the SQL Commands interface to calculate an employee’s salary after two consecutive 10 percent raises. Figure 36-1 shows the anonymous block itself and the results after you click the Run button.

Figure 36-1.  Running an anonymous PL/SQL block in SQL Commands

Although you could obtain the results in Figure 36-1 by using one or more SQL statements, the advantages of using PL/SQL are evident. The list of steps you use to obtain your results is easy to understand, and the output from the block would be difficult to obtain using just SQL commands. Note the embedded procedure call toDBMS_OUTPUT.PUT_LINE. This predefined stored procedure is included with your installation of Oracle Database XE that produces text output from your procedures. We show you more examples of calling procedures from within a procedure later in this chapter in the section “Creating and Using a Stored Function.”

The other advantage of using an anonymous block is clear only if you look at the output lineStatement Processed. When you click the Run button, the entire block is sent to Oracle for processing as a unit; you see the results after Oracle executes the block. This minimizes the network traffic to and from the Oracle server in contrast to sending SQL commands one at a time.

For our second introductory example, let’s create a simple stored procedure that returns the static stringHello, World:

create or replace procedure say_hello as begin
dbms_output.put_line('Hello, World'); end;

You don’t need to pass any parameters; the procedure already has the text to print. Note theOR REPLACEclause; if the procedure already exists, it will be replaced. If you do not specifyOR REPLACEand the procedure already exists, you will get an error message and the procedure is not replaced.

Now execute the procedure using the following command:


Note that from the SQL Commands interface, you must use an anonymous block to call a stored procedure. Executing this procedure within the anonymous block returns the following output:

Hello, World

Statement processed.

0.00 seconds

In contrast to the previous example, once you create the procedure, you can call it repeatedly from different sessions without sending the procedure definition each time.

Please check back for the next part of this series.

>>> More Oracle Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: