Notes 0011

Stored Procedures

In the last notes we have introduced Stored Procedures. In these notes, we shall discuss them in more detail.

General Format

The general format of a create procedure statement is this:

CREATE OR REPLACE
PROCEDURE procedure_name ( parameters ) IS
BEGIN
    procedure_body
END;

Where procedure_name can be any valid SQL name, parameters is a list of parameters to this procedure (we'll discuss them later), and procedure_body is various PL/SQL statements that make up the logic of the procedure.

Parameters

The parameters (or arguments) are optional. You don't have to specify anything (not even the parenthesis). For example, a sample procedure, which you no doubt have already seen:

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

Never actually defines any parameters. What's the use of a procedure that doesn't take any parameters and doesn't return anything? Well, you may be interested in the procedure's side effects, like in our case, we're interested in our procedure displaying 'Hello World!' and nothing else. There may be many instances where you may want to just do something to the database, without any particular parameters, and without returning anything.

Anyway, this section is about parameters so let's talk about parameters.

Parameters are defined in a similar way as in a CREATE TABLE statement, which is similar to how variables are declared. You first specify the name of the variable, and then the type. For example:

(N INT)

Would setup some procedure to accept an INT variable named N. Writing a simple procedure to display a variable name, you can come up with something like this:

CREATE OR REPLACE
PROCEDURE DISPN (N INT) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('N is ' || N);
END;

Which if you call, will promptly display:

SQL> CALL DISPN(1234567891);
N is 1234567891

You can also have multiple parameters. For example, you can accept A and B and display their sum and product.

CREATE OR REPLACE
PROCEDURE DISP_AB (A INT, B INT) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('A + B = ' || (A + B));
    DBMS_OUTPUT.PUT_LINE('A * B = ' || (A * B));
END;

Which when ran, displays something like (depending on the values you provide):

SQL> CALL DISP_AB(17,23);
A + B = 40
A * B = 391

Btw, it should be noted that you can use any PL/SQL type as an argument. For example, VARCHAR and others are perfectly acceptable. For example:

CREATE OR REPLACE
PROCEDURE DISP_NAME (NAME VARCHAR) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hi ' || NAME || '!');
END;

Which when called displays:

SQL> CALL DISP_NAME('John Doe');
Hi John Doe!

IN, OUT, IN OUT

There are various different parameter varieties (not types). For example, for the time being, we've only been giving the procedure data via parameters. This is the default (IN).

What we could also do is get data from the procedure, via an OUT parameter. To do that, we simply specify OUT in between the parameter name and its type. For example:

CREATE OR REPLACE
PROCEDURE SUM_AB (A INT, B INT, C OUT INT) IS
BEGIN
    C := A + B;
END;

Notice that the above code does not display the resulting sum, it just changes the value of the C parameter. Also notice the word OUT right after the declaration of C parameter name.

Anyway, we will use a code fragment to call the procedure:

DECLARE
    R INT;
BEGIN
    SUM_AB(23,29,R);
    DBMS_OUTPUT.PUT_LINE('SUM IS: ' || R);
END;

Which when ran, displays:

SUM IS: 52

Notice how we called the procedure with an argument to eventually retrieve the OUT result.

There is also the other special way of passing parameters: IN OUT. What that means is that we first can read the parameter, then we can change it. For example, we can write a procedure that doubles a number:

CREATE OR REPLACE
PROCEDURE DOUBLEN (N IN OUT INT) IS
BEGIN
    N := N * 2;
END;

To run it, we also create a small code fragment:

DECLARE
    R INT;
BEGIN
    R := 7;
    DBMS_OUTPUT.PUT_LINE('BEFORE CALL R IS: ' || R);
    DOUBLEN(R);
    DBMS_OUTPUT.PUT_LINE('AFTER CALL R IS: ' || R);
END;

Which when ran displays:

BEFORE CALL R IS: 7
AFTER CALL R IS: 14

Notice how this particular call first grabbed the value of a parameter, then set it in order to return the double of the value.

You can generally intermix these various ways of passing parameters (along with various types). You can use these to setup return values from procedures, etc.

Dropping Procedures

If you're interested in getting rid of a procedure totally, you can DROP it. The general format of a DROP is:

DROP PROCEDURE procedure_name;

That's all there is to stored procedures. We will do some practice exercises and more experimentation, but overall, that's all there is to them.

© 2006, Particle