Introduction to Stored Procedures
Just like any other procedural language, PL/SQL has code fragments that are called PROCEDURES.
You can call these PROCEDURES from other code fragments, or directly from SQL*Plus (or some other client program).
Before you begin to write procedures though, you need to verify that you have enough privileges to do that. If you don't (which probably means you're using a plain user account), then you need to login as administrator (or ask the administrator) to grant you access. To grant such priviledge yourself (in case you're the administrator - running Oracle on your own machine) you can do:
GRANT CREATE PROCEDURE TO someusername;
From that point on, the user someusername will be allowed to create, drop, and replace procedures and functions.
Procedures are code fragments that don't normally return a value, but may have some outside effects (like updating tables). The general format of a procedure is:
Of course, you'll usually be either creating or replacing the procedure, so you'd want to add on CREATE (OR REPLACE) to the declaration. For example, to create (or replace) a HELLO procedure, you might do something like this:
The above declares a HELLO procedure that just displays 'Hello World'. You can run it as part of a code fragment, or inside other procedures (or functions). For example:
Or you can simply execute it in SQL*Plus by typing: