SQL Programming Overview

Anybody who has done something for a long time has probably wanted to change how things work at some point or another. A worker at a mill might have found a more efficient way of cutting logs, or a mathematics teacher might have had a hand in changing a school’s algebra curriculum. SQL lets you change how things work, too, with stored procedures.

Essentially, a procedure is a set of instructions used to carry out some specific task. The SQL CREATE FUNCTION instruction allows a database user to create a procedure (also called a function, a user-defined function (UDF), or a routine). While developing functions, the CREATE FUNCTION instruction is used as CREATE OR REPLACE FUNCTION, to prevent the “function already exists” error from appearing. Further, ALTER FUNCTION is used to modify a function, and DROP FUNCTION is used to remove a function.

Perhaps the most popular SQL programming language (sometimes called a procedural extension) is PL/SQL, a language developed by and proprietary to Oracle. PostgreSQL has a similar language called PL/pgSQL, and Sybase and Microsoft SQL Server have Transact-SQL. MySQL also has a procedural SQL language, but it does not have a specific name. Other, similar extensions exist for most modern SQL databases.

In this article, we explore the fundamentals of the procedural languages of PostgreSQL, MySQL, and Oracle.

“Hello, World!”

Every concerted programming effort traditionally starts with the “Hello, World!” program. Originally conceived in an internal Bell Laboratories memorandum by Brian Kernighan, “Hello, World!” has become a customary way to begin one’s adventure into a new programming language. Figure 1 shows the creation and execution of “Hello, World!” in PostgreSQL’s PL/pgSQL language.

The natural thing to do now is dissect the procedure and explain its constituent parts.

{qbapagebreak title=Function Creation and Execution}

Function Creation and Execution

CREATE FUNCTION, of course, tells the database a function is being created. The function name, which used to call the function later, follows CREATE FUNCTION. In this case, that name is hello_world(). The RETURNS clause tells the database what type of data will be produced by the function. Our hello_world() function returns TEXT data.

The $$ symbol is PostgreSQL-specific and acts like double quotes, informing the database that everything between two $$ symbols should be treated as a single text string. BEGIN and END define the beginning and end of the operations the function should perform, and RETURN simply passes the ‘Hello, World!’ string out of the function when it finishes running. ‘Hello, World!’ is the TEXT data pre-referenced in the RETURNS TEXT clause on the CREATE FUNCTION line, and, after it is returned from the SELECT statement, it appears in the result set. Finally, LANGUAGE plpgsql is specified because this is a PL/pgSQL function. PostgreSQL requires the language to be explicitly mentioned, since PostgreSQL supports many different procedural languages.

Figure 2 shows the creation and execution of a similar hello_world() function in MySQL.

As you can see, the syntax and execution methods for the hello_world() functions written in PostgreSQL and MySQL are nearly the same.

In comparing these two examples, the most important thing to note is the DELIMITER in MySQL. This serves a similar function to the $$ symbol in PostgreSQL. The DELIMITER is the character that ends an SQL statement. In MySQL and most other SQL databases, it defaults to a semicolon (‘;’). However, when it is changed, in this case to //, MySQL interprets the semicolon as being simply another part of the FUNCTION statement, rather than the end of the function. The function is completed with the // symbol, and the DELIMITER is reset to the semicolon on the next line.

Some databases, like MySQL, can use the DETERMINISTIC adjective to qualify functions. This essentially means that, if a function is run many times with the same input values, it will always return the same values. Most basic functions are deterministic in nature.

Figure 3 shows the creation and execution of hello_world() in Oracle’s PL/SQL language.

Again, the Oracle example is similar to the PostgreSQL and MySQL examples. Three differences should be noted, however. First, the empty parentheses after hello_world in the CREATE FUNCTION line, as seen in the other examples, are not allowed in Oracle. Parentheses may only exist if one or more parameters are to be passed into the function.

Also, the / symbol at the end of the CREATE FUNCTION statement serves as an “end-of-file” marker, which informs Oracle that the function is complete. And, lastly, a SELECT instruction must always be executed FROM an object in Oracle. In this case, the dual table was chosen because it is the Oracle “dummy” table. However, any other table would have served the same purpose.

Beyond “Hello, World!”

While “Hello, World!” is the standard way to begin learning a programming language, there is much more to SQL programming than simply returning a string of text. Further examples and explanations of programming in SQL and other SQL-related procedural languages are given in other articles in this series.