A wise man once said, insanity is doing the same thing over and over again but expecting different results.<\/p>\n
If that is the case then no one\u2019s saner than a computer programmer*, because programming is executing the same code over and over again and expecting the same<\/i> results. We\u2019ve already talked about bundling up statements in anonymous PL\/SQL blocks; now it’s time to bring out the big dogs of rerunnable code \u2013 procedures and functions.<\/p>\n
(* which is strange \u2013 most programmers, in my experience, are crazier than a fly trapped in a bottle! Except me, of course.)<\/p>\n
Procedures and functions are named\u00a0PL\/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.<\/p>\n
But what, you may ask, is the difference between a procedure and a function?<\/p>\n
Good question, my friend.<\/p>\n
A procedure is a named PL\/SQL block that carries out one or more actions.<\/p>\n
A function is a named PL\/SQL block that returns a value.<\/p>\n
They\u2019re close cousins and, based on those definitions, it may be difficult to tell them apart. An example might help: we might write a procedure<\/i> named insert_new_friend that will accept a new friend\u2019s 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<\/i> ten times with ten different sets of parameters. Our procedure will carry out the actions for us.<\/p>\n
We might also write a function<\/i> named get_friend_phone_number that will accept a friend\u2019s 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<\/i> ten times with different parameters to get ten different phone numbers. In each case, our function will return a value to us.<\/p>\n
Procedures carry out action(s); functions return a value.<\/p>\n
CREATE [OR REPLACE] PROCEDURE <procedure_name> [parameter(s)] AS\r\n\t[Variable declaration section] \r\nBEGIN\r\n\t<Statements>\r\nEND [<procedure_name>];<\/pre>\nYou’ll\u00a0notice 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.<\/p>\n
Here\u2019s the syntax for functions.<\/p>\n
CREATE [OR REPLACE] FUNCTION <function_name> [parameter(s)] RETURN <datatype> AS\r\n\t[variable declaration section]\r\nBEGIN\r\n\t<Statements>\r\nEND [<function_name>];<\/pre>\nFunctions 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\u2019t shown in our syntactical wireframe above, but we\u2019ll talk about it a little later.<\/p>\n
Creating Oracle Objects<\/h4>\n
So far in this series, we\u2019ve talked about Oracle objects \u2013 mostly tables, but now procedures and functions \u2013 but I haven\u2019t said anything about how these objects are created in the first place. We\u2019ll go into it in more detail in a future article, but you\u2019ll notice that the syntax is always CREATE [OR REPLACE] object_type <\/i>\u00a0<object_name> [AS].<\/p>\n
I\u2019ll 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<\/strong>. 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:<\/p>\n
CREATE SEQUENCE friend_id_seq\r\nSTART WITH 100\r\nINCREMENT BY 1;<\/pre>\nRun 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\u2019ll use them later.<\/p>\n
However, let\u2019s get back to procedures.<\/p>\n
Procedures<\/h4>\n
Let\u2019s start with a simple example. Why don\u2019t 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.<\/i><\/p>\n
CREATE OR REPLACE PROCEDURE friends_analysis AS\r\nBEGIN\r\n\tFOR i IN (SELECT COUNT(*) cnt, gender\r\n\t\t FROM friend_name\r\n\t\t GROUP BY gender) LOOP\r\n\r\n\t\tIF i.gender = 'M' THEN\r\n\t\t\tdbms_output.put_line('I have '||i.cnt||' male friends.');\r\n\t\tELSIF i.gender = 'F' THEN\r\n\t\t\tdbms_output.put_line('I have '||i.cnt||' female friends.');\r\n\t\tEND IF;\r\n\tEND LOOP;\r\n\r\n\t\/* Assume the value in friend_name.friend_id represents the order in which we became friends. *\/\r\n\tFOR i IN (SELECT first_name, middle_name, last_name\r\n\t\t FROM friend_name\r\n\t\t WHERE friend_id = (SELECT MIN(friend_id)\r\n\t\t\t FROM friend_name\r\n )\r\n ) LOOP\r\n\r\n\t\tdbms_output.put_line('Our oldest friend is '||i.first_name||' '||i.middle_name||' '||i.last_name);\r\n\tEND LOOP;\r\n\r\n\tFOR i IN (SELECT first_name, middle_name, last_name\r\n\t\t FROM friend_name\r\n\t\t WHERE friend_id = (SELECT MAX(friend_id)\r\n\t\t\t\t FROM friend_name\r\n )\r\n ) LOOP\r\n\r\n\t\tdbms_output.put_line('Our newest friend is '||i.first_name||' '||i.middle_name||' '||i.last_name);\r\n\tEND LOOP;\r\nEND friends_analysis;<\/pre>\nOkay, maybe we lied a little when we said it\u2019d 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<\/b> 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.<\/p>\n
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 \u2013 and reuse as often as we want.<\/p>\n
Procedures can be called from other procedures, from anonymous blocks, from functions \u2013 wherever they\u2019re needed in your PL\/SQL. Let\u2019s call ours from an anonymous block.<\/p>\n
BEGIN\r\n\tfriends_analysis;\r\nEND;<\/pre>\nThe fact that procedures \u2013 and functions \u2013 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 \u2013 and yet it does so much!).<\/p>\n
Parameters<\/h4>\n
We\u2019ve been throwing the word parameter around like it\u2019s confetti at a wedding. \u00a0A parameter is a special kind of variable which is used to pass data into a procedure or function<\/strong>. Earlier, we talked about creating a procedure that would accept a new friend\u2019s name and a phone number \u00a0\u2013 as parameters \u2013 and insert the details into the right tables. Let\u2019s write it to illustrate the usefulness of parameters.<\/p>\n
CREATE OR REPLACE PROCEDURE insert_new_friend (pFirst_name VARCHAR2, \r\n pLast_name VARCHAR2, \r\n pGender VARCHAR2, \r\n pPhone_country NUMBER, \r\n pPhone_area NUMBER, \r\n pPhone_number NUMBER ) AS\r\n -- declare our variables.\r\n\tv_friend_id\tNUMBER;\r\n\tv_phone_id\tNUMBER;\r\nBEGIN\r\n\t-- add a record to the friend_name table.\r\n\tINSERT INTO friend_name (friend_id, first_name, last_name, gender)\r\n\tVALUES (friend_id_seq.nextval, pFirst_name, pLast_name, pGender)\r\n\tRETURNING friend_id INTO v_friend_id;\r\n\r\n\t-- Next we need to add a new record to the PHONE_NUMBER table.\r\n\tINSERT INTO phone_number( phone_id, country_code, area_code, phone_number)\r\n\tVALUES (phone_id_seq.nextval, pPhone_country, pPhone_area, pPhone_number)\r\n\tRETURNING phone_id INTO v_phone_id;\r\n\r\n\t-- Finally, we need to associate our new friend with this phone number.\r\n\tINSERT INTO friend_phone (friend_id, phone_id, start_date)\r\n\tVALUES (v_friend_id, v_phone_id, SYSDATE);\r\nEND insert_new_friend;<\/pre>\nAnd that\u2019s it.\u00a0 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.<\/p>\n
BEGIN\r\n\tinsert_new_friend ('Jane', 'Simpson', 'F', 44, 207, 555551);\r\n\tinsert_new_friend ('Ola', 'Sanusi', 'M', 234, 1, 890555);\r\nEND;<\/pre>\nBy calling our new procedure with the names of our new friends, we are populating our parameters \u2013 pFirst_name, pLast_name etc \u2013 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.<\/p>\n
There are a few other new things that I sneaked into our procedure:<\/p>\n
\n
- 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.<\/li>\n
- The RETURNING \u2026 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.<\/li>\n<\/ul>\n
Functions<\/h4>\n
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:<\/p>\n
\n
- Find out a friend’s phone number<\/li>\n
- Return a friend’s gender<\/li>\n
- Test a premise. For example, return TRUE if you have a friend in London, or FALSE if you don’t.<\/li>\n<\/ul>\n
But you cannot write a function for the following:<\/p>\n
\n
- Find a friend’s phone number and<\/em> gender.<\/li>\n<\/ul>\n
Because functions must always answer a single<\/em>, 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.)<\/p>\n
We talked earlier about creating a function named get_friend_phone_number<\/i> to answer the specific question: what is the parameterised friend’s phone number. Let’s try creating it now.<\/p>\n
CREATE OR REPLACE FUNCTION get_friend_phone_number (pFirst_name VARCHAR2,\r\n pLast_name VARCHAR2) RETURN NUMBER AS\r\n\tV_phone_no\tNUMBER;\r\nBEGIN\r\n FOR i IN (SELECT pn.phone_number\r\n FROM phone_number pn, friend_name fn, friend_phone fp\r\n WHERE UPPER(fn.first_name) = UPPER(pFirst_name)\r\n AND UPPER(fn.last_name) = UPPER(pLast_name)\r\n AND fn.friend_id = fp.friend_id\r\n AND fp.start_date <= SYSDATE AND NVL(fp.end_date, SYSDATE + 1) > SYSDATE\r\n AND fp.phone_id = pn.phone_id) LOOP\r\n\r\n v_phone_no := i.phone_number;\r\n END LOOP;\r\n\r\n -- All functions MUST return something (even if it is a null).\r\n RETURN v_phone_no;\r\nEND get_friend_phone_number;<\/pre>\nProcedures, 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.<\/p>\n
DECLARE\r\n\tv_joey_phone\t\tNUMBER;\r\n\r\nBEGIN\r\n\t-- Assign our function to a variable.\r\n\tv_joey_phone\t\t:= get_friend_phone_number('Joey','Tribiani');\r\n\tdbms_output.put_line('Joey''s phone number is '||v_joey_phone);\r\n\r\n\t-- Use our function in a select statement\r\n\tFOR i IN (SELECT first_name, last_name, get_friend_phone_number(first_name, last_name) telno\r\n\t\t FROM friend_name) LOOP\r\n\r\n\t\tdbms_output.put_line(i.first_name||': '||i.telno);\r\n\tEND LOOP;\r\nEND;<\/pre>\nFunctions 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<\/i> is that they are built-in Oracle functions \u2013 but they’re functions no less.<\/p>\n
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.<\/p>\n
Conclusion<\/h4>\n
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.<\/p>\n
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.<\/p>\n
The more focused your procedures, the easier it is to build complex structures. It’s just like Lego.<\/p>\n
Everything is awesome.<\/p>\n
<\/p>\n
Next Article:\u00a0Oracle for Absolute Beginners: Part 7 \u2013 Creating Tables, Constraints and Triggers<\/a><\/h3>\n","protected":false},"excerpt":{"rendered":"
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\u2019s saner than a computer programmer*, because programming is executing the same code over and over again and expecting the same results. We\u2019ve already talked about bundling up statements in anonymous PL\/SQL blocks; now…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48366,4459,124952],"coauthors":[48557],"class_list":["post-73202","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-beginners-guide","tag-oracle","tag-redgate-deploy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73202","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73202"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73202\/revisions"}],"predecessor-version":[{"id":88955,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73202\/revisions\/88955"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73202"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73202"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73202"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73202"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}