In the last notes we have introduced Stored Procedures. In these notes, we shall discuss them in more detail.
The general format of a create procedure statement is this:
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.
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:
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:
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:
Which if you call, will promptly display:
You can also have multiple parameters. For example, you can accept A and B and display their sum and product.
Which when ran, displays something like (depending on the values you provide):
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:
Which when called displays:
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:
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:
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:
To run it, we also create a small code fragment:
Which when ran displays:
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.
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.