A wise man once said, insanity is doing the same thing over and over again but expecting different results.
If that is the case then no one’s saner than a computer programmer*, because programming is executing the same code over and over again and expecting the same results. We’ve already talked about bundling up statements in anonymous PL/SQL blocks; now it’s time to bring out the big dogs of rerunnable code – procedures and functions.
(* which is strange – most programmers, in my experience, are crazier than a fly trapped in a bottle! Except me, of course.)
Procedures and functions are named PL/SQL blocks. Having names bestows them with certain advantages: they can be stored within the database, which means they can be reused easily; they can call each other; and they can accept parameters that modify their behaviour.
But what, you may ask, is the difference between a procedure and a function?
Good question, my friend.
A procedure is a named PL/SQL block that carries out one or more actions.
A function is a named PL/SQL block that returns a value.
They’re close cousins and, based on those definitions, it may be difficult to tell them apart. An example might help: we might write a procedure named insert_new_friend that will accept a new friend’s name and a new phone number as parameters and will insert a new record in both the FRIEND_NAME and FRIEND_PHONE tables for that friend and number. So if, we go to a party and make ten new friends, instead of writing ten sets of long-winded insert statements, we would simply call insert_new_friend ten times with ten different sets of parameters. Our procedure will carry out the actions for us.
We might also write a function named get_friend_phone_number that will accept a friend’s name as a parameter, use it to query the FRIEND_NAME table to find out their friend_id, use that to query the FRIEND_PHONE table, and then return to us their phone number. So if we came across a juicy piece of gossip that we just had to share, we might call get_friend_phone_number ten times with different parameters to get ten different phone numbers. In each case, our function will return a value to us.
Procedures carry out action(s); functions return a value.
CREATE [OR REPLACE] PROCEDURE <procedure_name> [parameter(s)] AS [Variable declaration section] BEGIN <Statements> END [<procedure_name>];
You’ll notice that the structure of a stored procedure is pretty much identical to that of an anonymous block; the only differences are that stored procedures must be given a name, can accept parameters, and they dispense with the necessity of the keyword DECLARE at the start of the declaration section.
Here’s the syntax for functions.
CREATE [OR REPLACE] FUNCTION <function_name> [parameter(s)] RETURN <datatype> AS [variable declaration section] BEGIN <Statements> END [<function_name>];
Functions have the same syntactical skeleton as anonymous blocks and procedures; the important difference is that, since all functions must return a value, in defining a function we must specify the datatype of the value that we are planning to return. Following on from this, the body of all functions must contain a RETURN statement: this isn’t shown in our syntactical wireframe above, but we’ll talk about it a little later.
Creating Oracle Objects
So far in this series, we’ve talked about Oracle objects – mostly tables, but now procedures and functions – but I haven’t said anything about how these objects are created in the first place. We’ll go into it in more detail in a future article, but you’ll notice that the syntax is always CREATE [OR REPLACE] object_type <object_name> [AS].
I’ll give you a quick example. In Oracle we have something called a sequence. A sequence is an object that is used to generate a list of numbers. They are very useful when we need unique numbers to populate id columns like friend_id in FRIEND_NAME or phone_id in PHONE_NUMBER. The syntax for creating a sequence is as follows:
CREATE SEQUENCE friend_id_seq START WITH 100 INCREMENT BY 1;
Run the code to create the friend_id_seq sequence, and then write another one to create a sequence called phone_id_seq, also starting with 100 and incrementing by 1. We’ll use them later.
However, let’s get back to procedures.
Let’s start with a simple example. Why don’t we create a procedure that will analyse our database for us and report on the breakdown of our friends. We can call it something like friends_analysis.
CREATE OR REPLACE PROCEDURE friends_analysis AS BEGIN FOR i IN (SELECT COUNT(*) cnt, gender FROM friend_name GROUP BY gender) LOOP IF i.gender = 'M' THEN dbms_output.put_line('I have '||i.cnt||' male friends.'); ELSIF i.gender = 'F' THEN dbms_output.put_line('I have '||i.cnt||' female friends.'); END IF; END LOOP; /* Assume the value in friend_name.friend_id represents the order in which we became friends. */ FOR i IN (SELECT first_name, middle_name, last_name FROM friend_name WHERE friend_id = (SELECT MIN(friend_id) FROM friend_name ) ) LOOP dbms_output.put_line('Our oldest friend is '||i.first_name||' '||i.middle_name||' '||i.last_name); END LOOP; FOR i IN (SELECT first_name, middle_name, last_name FROM friend_name WHERE friend_id = (SELECT MAX(friend_id) FROM friend_name ) ) LOOP dbms_output.put_line('Our newest friend is '||i.first_name||' '||i.middle_name||' '||i.last_name); END LOOP; END friends_analysis;
Okay, maybe we lied a little when we said it’d be simple, but you should be able to follow the logic. Notice how, in finding our oldest and newest friends, we embedded a select statement within another to get the friend_id? That is called a sub-query and is a great time-saving trick. If sub-queries did not exist, I would have had to select the minimum friend_id and save it in a variable before being able to use it to find our oldest friend.
But back to our procedure. When we run the code above it will not output an analysis of our friends; instead it will create a procedure named friends_analysis in our database, ready for us to use whenever we want to – and reuse as often as we want.
Procedures can be called from other procedures, from anonymous blocks, from functions – wherever they’re needed in your PL/SQL. Let’s call ours from an anonymous block.
BEGIN friends_analysis; END;
The fact that procedures – and functions – can be called repeatedly from numerous places is what makes them so useful. Think about it: it saves the developer the trouble of all that typing; it makes bug-fixing easier since you only need to correct an error in a single place; if your requirement changes you only have to make a change in one place; it makes code easier to read (the anonymous block above is only 3 lines long – and yet it does so much!).
We’ve been throwing the word parameter around like it’s confetti at a wedding. A parameter is a special kind of variable which is used to pass data into a procedure or function. Earlier, we talked about creating a procedure that would accept a new friend’s name and a phone number – as parameters – and insert the details into the right tables. Let’s write it to illustrate the usefulness of parameters.
CREATE OR REPLACE PROCEDURE insert_new_friend (pFirst_name VARCHAR2, pLast_name VARCHAR2, pGender VARCHAR2, pPhone_country NUMBER, pPhone_area NUMBER, pPhone_number NUMBER ) AS -- declare our variables. v_friend_id NUMBER; v_phone_id NUMBER; BEGIN -- add a record to the friend_name table. INSERT INTO friend_name (friend_id, first_name, last_name, gender) VALUES (friend_id_seq.nextval, pFirst_name, pLast_name, pGender) RETURNING friend_id INTO v_friend_id; -- Next we need to add a new record to the PHONE_NUMBER table. INSERT INTO phone_number( phone_id, country_code, area_code, phone_number) VALUES (phone_id_seq.nextval, pPhone_country, pPhone_area, pPhone_number) RETURNING phone_id INTO v_phone_id; -- Finally, we need to associate our new friend with this phone number. INSERT INTO friend_phone (friend_id, phone_id, start_date) VALUES (v_friend_id, v_phone_id, SYSDATE); END insert_new_friend;
And that’s it. So now if, at our party, we made a friend from London and another from Lagos, Nigeria, we might simply call our procedure from an anonymous block, passing in the right parameters.
BEGIN insert_new_friend ('Jane', 'Simpson', 'F', 44, 207, 555551); insert_new_friend ('Ola', 'Sanusi', 'M', 234, 1, 890555); END;
By calling our new procedure with the names of our new friends, we are populating our parameters – pFirst_name, pLast_name etc – and so we can use them in our insert statements. This is how we are able to use the exact same procedure for Jane Simpson, for Ola Sanusi and for however many new friends we make in the future.
There are a few other new things that I sneaked into our procedure:
- To get the next number from a sequence, we use the following syntax: <sequence_name>.nextval. This always gets the next number; so if your sequence is at 100 and you call <sequence_name>.nextval three times in three select statements, you will (probably) get 101, 102 and 103. After you’ve run <sequence_name>.nextval, you can run <sequence_name>.currval to get the current value, rather than the next one.
- The RETURNING … INTO clause can be used with insert and update statements to place a value in a variable. In our procedure, we’re adding a new friend_id using friend_id_seq.nextval; however, we want to assign that number to our v_friend_id variable so we can use it later.
Functions, as we said earlier, must return a value. Basically, functions must answer a single, specific question. You can write a function for the following:
- Find out a friend’s phone number
- Return a friend’s gender
- Test a premise. For example, return TRUE if you have a friend in London, or FALSE if you don’t.
But you cannot write a function for the following:
- Find a friend’s phone number and gender.
Because functions must always answer a single, specific question. (It is possible, using more complex datatypes to return a single value comprised of other bits of information, but that is outside the scope of this series.)
We talked earlier about creating a function named get_friend_phone_number to answer the specific question: what is the parameterised friend’s phone number. Let’s try creating it now.
CREATE OR REPLACE FUNCTION get_friend_phone_number (pFirst_name VARCHAR2, pLast_name VARCHAR2) RETURN NUMBER AS V_phone_no NUMBER; BEGIN FOR i IN (SELECT pn.phone_number FROM phone_number pn, friend_name fn, friend_phone fp WHERE UPPER(fn.first_name) = UPPER(pFirst_name) AND UPPER(fn.last_name) = UPPER(pLast_name) AND fn.friend_id = fp.friend_id AND fp.start_date <= SYSDATE AND NVL(fp.end_date, SYSDATE + 1) > SYSDATE AND fp.phone_id = pn.phone_id) LOOP v_phone_no := i.phone_number; END LOOP; -- All functions MUST return something (even if it is a null). RETURN v_phone_no; END get_friend_phone_number;
Procedures, we have said, carry out actions, while functions return a value. For this reason, functions are called a little differently from procedures, which are simply executed. A function can be assigned to a variable or used with an SQL statement. I’ll show you what I mean.
DECLARE v_joey_phone NUMBER; BEGIN -- Assign our function to a variable. v_joey_phone := get_friend_phone_number('Joey','Tribiani'); dbms_output.put_line('Joey''s phone number is '||v_joey_phone); -- Use our function in a select statement FOR i IN (SELECT first_name, last_name, get_friend_phone_number(first_name, last_name) telno FROM friend_name) LOOP dbms_output.put_line(i.first_name||': '||i.telno); END LOOP; END;
Functions are amazingly versatile; they can return any datatype and can be used pretty much anywhere any value can be. But you already knew that. You’ve been using functions, pretty much from day one. Don’t believe me? What do you think SYSDATE, UPPER, TO_DATE, COUNT, MIN, MAX and NVL are? They are, respectively, a function that returns the current date; a function that accepts a string as a parameter and returns the uppercase value; a function that takes a string as a parameter and returns a date; an aggregate function that counts the parameter; an aggregate function that returns the minimum (or maximum) value; and a function that accepts two parameters and returns either the first or the second if the first is null. The only difference between them and get_friend_phone_number is that they are built-in Oracle functions – but they’re functions no less.
And dbms_output.put_line? That’s a built-in procedure: it accepts a string as a parameter and carries out the action of printing it to the screen.
So there you have it – procedures and functions. They are, arguably, the most important things in all of computer programming; they’re the Lego bricks with which everything else must be built. All well-written applications are made up of procedures which contain other procedures and call functions which, in turn, may call other functions and execute other procedures.
The keywords are reusability and modularity. If you have an action that you know you will carry out more than once (insert a new friend, for instance) you must create a procedure to do it; if you have a question you know you will ask more than once (what is a friend’s phone number?) you must create a function to answer it. And, as much as possible, you should keep your procedures and functions single-purposed and small. That way, when you need to build complex algorithms, all you have to do is construct it by calling one procedure after the other in whatever sequence you decide.
The more focused your procedures, the easier it is to build complex structures. It’s just like Lego.
Everything is awesome.
Beginners Guide, oracle