Learning Series
Home Database Learn SQL

SQL Functions

Category: SQL | Comments (1)

SQL Functions

Page 1 of 2

SQL Programming Overview

In the article SQL Programming, we discussed creating the “Hello, World!” function in PostgreSQL, MySQL, and Oracle. In this article, we take “Hello, World!” one step farther and introduce the parts of a function and how to pass parameters to a function.


This article uses PostgreSQL’s PL/pgSQL procedural programming language, but with a little massaging, these functions can be used in any SQL-compliant database. Most of that “massaging” can be seen in the SQL Programming article.


Parts of a Function

Figure 1 shows a “Hello, World!” function that is slightly more complicated than those covered in the previous article. It also clearly illustrates the three main parts every function has.



First, we see the function declaration: CREATE OR REPLACE FUNCTION hello_world() RETURNS TEXT. This names the new (or replacement) function, declares its argument types between parentheses (in this case, there are none), and states the type of the returned data (TEXT, here). The “code block” encompasses the second and third parts of the function.


The second part is the variable declaration section, which begins with the DECLARE keyword. In this part, all the variables that will be used in the function must be declared, along with their type. Variables declared here may be assigned default values. For functions that do not use variables, the DECLARE keyword can be omitted, but it is best to think of it as still being part of the function, even though it is empty.


The third part of the function is the main body. This part is always surrounded by the BEGIN and END keywords, and it contains the code that will be run when the function is called. If the function is supposed to return a value, as directed by the RETURNS keyword on the CREATE FUNCTION line, it must be returned in this part of the function. It must also be of the same type that was specified after the RETURNS keyword.


Next Page: SQL Functions: Passing Parameters


Next: SQL Perl/PL Overview


Comments

hope i can benedit from your courses and tkx
Comment posted by: rony sokhn on 2008-08-15T06:29:54


Post Comment


Members Please Login

Name:


Email:
 
(Optional. Used for Notification)

Title:

 
Comment:


Validation Code:
 <=>  (Enter this code in text box)
Subscribe





Daily Email Updates

Get Latest Learning Series Updates delivered directly to your Inbox...

Enter your email address:

Latest Learning Series Updates

Learn SQL Tutorials

Related Tutorials