Notes 0010

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

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:

PROCEDURE procedure_name IS
BEGIN
    procedure_body
END;

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:

CREATE OR REPLACE
PROCEDURE HELLO IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
END;

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:

BEGIN
    HELLO();
END;

Or you can simply execute it in SQL*Plus by typing:

CALL HELLO();

© 2006, Particle