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.

Next Page: Function Creation and Execution