Informix Dynamic Server 11.50 Fundamentals exam 555 Certification Preparation, Part 5: Using SQL

This tutorial is the fifth in a series of tutorials designed to help you become familiar with all the different aspects of IBM® Informix® Dynamic Server (IDS) and help you get ready for the IDS Fundamentals Certification exam. This tutorial covers the use of SQL to manipulate data.

Sanjit Chakraborty (sanjitc@us.ibm.com), Advanced Support Engineer, IBM

Sanjit Chakraborty photo Sanjit Chakraborty has been a member of the Informix Advanced Support Group since 1999. He is an IBM Certified System Administrator for IDS and DB2, and a designated archiving subject matter expert. He developed several IDS RAS features, Down System Support applications, knowledge management, and tools, and is a co-developer of the IDS Fundamentals and IDS System Administration Certification exams. He is also an author and technical reviewer of many technical articles, tutorials, and training course materials on various Informix topics.



19 November 2009

Also available in Portuguese

Before you start

This tutorial gives an overview of the Informix Dynamic Server (IDS) implementation of Structured Query Language (SQL).

About this series

This complimentary series of nine tutorials has been developed to help you prepare for the IBM Informix Dynamic Server 11.50 Fundamentals certification exam (555). This certification exam will test your knowledge of entry-level administration of IDS 11.50, including basic SQL, how to install IDS 11.50, how to create databases and database objects, security, transaction isolation, backup and recovery procedures, and data replication technologies and purposes. These tutorials provide a solid base for each section of the exam. However, you should not rely on these tutorials as your only preparation for the exam.

About this tutorial

This tutorial introduces you to SQL and helps to give you a good understanding of how IDS uses SQL to manipulate data in a relational database.

Objectives

This tutorial is designed to help you become familiar with:

  • How to perform a query on a table
  • How to manipulate data using SQL
  • The concept of a transaction
  • The use of stored procedures and user-defined routines

Prerequisites

This tutorial is written for IDS database professionals whose skills and experience are at a beginning to intermediate level. You should be familiar with basic database concepts.

System requirements

To complete this tutorial, you do not need a copy of IDS. However, if you have one available to use, you will definitely get more out of the tutorial. If you don't have a copy already, you can download the free trial version (see Resources).


SQL

Understanding SQL

SQL is a language that is used to define and manipulate database objects. You use SQL to define a database table, insert data into the table, change the data in the table, and retrieve data from the table. The most important use of SQL is the SELECT statement. The SELECT statement allows you to retrieve data from a relational database, whereas INSERT, UPDATE, MERGE, and DELETE allow you to manipulate data in the database. Like all languages, SQL has a defined syntax and a set of language elements. In this tutorial, the focus is on data retrieval and data manipulation using SQL.

All the SQL examples in this tutorial are based on the stores_demo database, which comes with IDS product. The stores_demo database contains a set of tables that describe an imaginary business. You can easily create and populate the stores_demo database by running the dbaccessdemo script with the log option: dbaccessdemo -log. You need to have the Informix environment variables set when running the dbaccessdemo script. The dbaccessdemo script is located in the bin directory of your Informix installation.


Data Manipulation Language (DML) - Retrieving data

Retrieve data from a table using a SELECT statement

The SELECT statement is used to retrieve data from a table or a view. This operation is also called a query. You need the Connect privilege to access the relational database, as well as the Select privilege on the table or view from which SELECT retrieves data. In its simplest form, the SELECT statement can be used to retrieve all the data from a table or view. The following statement is an example of using the SELECT statement to retrieve all the data from the customer table:

SELECT * FROM customer;

The SELECT statement can be as simple as the one above, or more complex if you use multiple SQL clauses and keywords. There are many different SQL clauses available in IDS. You will see some of the most common SQL clauses in this tutorial.

You can use the FIRST keyword to restrict the number of rows in a query result. The following code is an example showing how to retrieve the first 10 rows from the customer table:

SELECT FIRST 10 * FROM customer;

To retrieve only specific columns from a table, specify a list of column names separated by commas after the SELECT keyword. The following example illustrates how to retrieve data for the columns named fname and lname from the customer table:

SELECT fname, lname FROM customer;

Use the DISTINCT keyword with a SELECT statement to eliminate duplicate rows from a query result. Here is an example of eliminating duplicate customer data from the orders table:

SELECT DISTINCT customer_num FROM orders;

You can use the AS clause with a SELECT statement to assign a meaningful name to an expression or column in the SELECT list. The following example shows using the AS clause to calculate the processing time between the order date and the shipped date from the orders table:

SELECT ship_date-order_date AS process_time FROM orders;

There are many built-in SQL functions available that you can use with the SELECT statement. Table 1 provides a list of built-in functions that are often used with SELECT statements:

Table 1. Common SQL functions
FunctionsDescription
Aggregate functions
COUNTCount of the numbers of rows in the query result
SUMAdd the values of a numeric column
AVGAverage value of a numeric column
MAXMaximum value of a column
MINMinimum value of a column
Time functions
DAYInteger that represents the day
MONTHInteger that represents the month
YEARInteger that represents the year
WEEKDAYInteger that represents the day of the week (0 is Sunday)
DATEReturns date values
CURRENTReturns current date-time value
TODAYReturns system date
Miscellaneous functions
TRUNCReturns a truncated value of an expression
HEXReturns a hexadecimal value
ROUNDReturns a rounded value
LENGTHReturns the length of a character column

Here is an example of the SELECT statement with the COUNT (*) function that returns the number of rows in the customer table:

SELECT COUNT(*) FROM customer;

Filtering data

Filter the data returned by a query using the WHERE clause and predicates

You can use the WHERE clause to select specific rows from a table or view by specifying one or more selection criteria or search conditions. A search condition consists of one or more predicates. A predicate specifies something about a row that is either true or false. Consider the following when building search conditions:

  • Apply arithmetic operations only to numeric data types.
  • Make comparisons only among compatible data types.
  • Enclose character values within quotation marks.
  • Specify character values exactly as they appear in the database.

Let's take a look at a few examples that illustrate how to use the WHERE clause.

First, an example showing how to find the names of customers from the customer table whose customer numbers are greater than 120:

SELECT fname, lname, customer_num FROM customer 
  WHERE customer_num > 120;

Here is an example of finding the names of customers from the customer table who live in Denver and whose customer numbers are greater than 120:

SELECT fname, lname, customer_num FROM customer
  WHERE customer_num > 120
  AND city = 'Denver';

The LIKE keyword supports wild card characters in the quoted string. The percent sign (%) is a wild card character that represents a string of zero or more characters.

The following example shows how to find all the customer names from the customer table that start with the letter L:

SELECT fname FROM customer
  WHERE fname LIKE 'L%';

There are many keywords and operators that you can use with a WHERE clause to define your search condition. Table 2 provides a list of the most common keywords and operators:

Table 2. List of common keywords and operators to use in a WHERE clause
Keywords / OperatorsDescription
Keywords
BETWEENRange of values
INSubset of values
LIKEVariable text search
MATCHESVariable text search
IS NULLSearch for NULL strings
IS NOT NULLSearch for non-NULL strings
AND/OR/NOTLogical operators used to connect two or more conditions
Arithmetic operators
+Addition
-Subtraction
*Multiplication
/Division
Relational operators
= or ==Equal
!= or <> Does not equal
>Greater than
>=Greater than or equal to
<Less than
<=Less than or equal to

You can use the preceding keywords and operators in a WHERE clause to create comparison-condition queries that perform the following actions:

  • Include values
  • Exclude values
  • Find a range of values
  • Exclude a range of rows
  • Find a subset of values
  • Identify NULL values

Including rows

Use the equal sign (=) relational operator to include rows in a WHERE clause, as the next example shows.

SELECT customer_num, call_code, call_dtime, res_dtime
  FROM cust_calls
  WHERE user_id = 'maryj';

This query will return all rows from cust_calls table. Here is an example of a query using the 1=1 expression:

SELECT customer_num, call_code, call_dtime, res_dtime
  FROM cust_calls
  WHERE 1=1;

Excluding rows

Use the != or <> relational operators to exclude rows in a WHERE clause.

Listing 1. Examples of excluding rows with the != and <> operators
SELECT customer_num, company, city, state
  FROM odin.customer
  WHERE state != 'CA';

SELECT customer_num, company, city, state
  FROM odin.customer
  WHERE state <> 'CA';

Finding a range of rows

Listing 2 shows two ways to specify a range of rows in a WHERE clause:

  • Using the BETWEEN and AND keywords
  • Using the >= and <= operators
Listing 2. Example of specifying a range
SELECT catalog_num, stock_num, manu_code, cat_advert
  FROM catalog
  WHERE catalog_num BETWEEN 10005 AND 10008;

SELECT catalog_num, stock_num, manu_code, cat_advert
  FROM catalog
  WHERE catalog_num >= 10005 AND catalog_num <= 10008;

Excluding a range of rows

Listing 3 uses the keywords NOT BETWEEN to exclude rows that have the character range 94000 through 94999 in the zipcode column:

Listing 3. Example of excluding a range
SELECT fname, lname, city, state
   FROM customer
   WHERE zipcode NOT BETWEEN '94000' AND '94999';

Finding a subset of values

Like excluding rows, the example shown in Listing 4 assumes the use of an ANSI-compliant database. The owner qualifier is in quotation marks to preserve the case sensitivity of the literal string.

Listing 4. Example of finding a subset of values using the OR and IN keywords
SELECT lname, city, state, phone
   FROM 'Aleta'.customer
   WHERE state = 'AZ' OR state = 'NJ'
   ORDER BY lname;

SELECT lname, city, state, phone
   FROM 'Aleta'.customer
   WHERE state IN ('AZ', 'NJ')
   ORDER BY lname;

Identifying NULL values

Use the IS NULL or IS NOT NULL keywords to check for NULL values. A NULL value represents either the absence of data or an unknown value. A NULL value is not the same as a zero, a blank, or an empty string.

Listing 5. Example of returning all rows that have a null paid_date value
SELECT order_num, customer_num, po_num, ship_date
   FROM orders
   WHERE paid_date IS NULL
   ORDER BY customer_num;

Sorts

Sorting the data returned by a query using the ORDER BY clause

Use the ORDER BY clause to sort the query result by one or more columns. The column names that are specified in the ORDER BY clause do not have to be specified in the Projection list. The following example illustrates using the ORDER BY clause, where data is retrieved from the customer table and sorted based on the customer number:

SELECT fname, lname FROM customer
   ORDER BY customer_num;

By default, the ORDER BY clause sorts data in ascending order. You can change the query result to descending order by using the DESC keyword in the ORDER BY clause.


Joins

Retrieve data from more than one table using joins

You can join two or more tables to combine a query result. It is very common that required data is distributed across two or more tables. For example, a full join of two three-column tables produces a query result with six columns. A join condition specifies a relationship between at least one column from each table to be joined. Because the columns in a join condition are being compared, they must have compatible data types. Listing 6 provides an example of joining two tables, customer and orders. The column name customer_num is the relationship between tables.

Listing 6. Example of a join
SELECT a.company, b.order_num, b.order_date
   FROM customer a, orders b
   WHERE a.customer_num = b.customer_num ;

IDS allows several types of joins:

  • CROSS JOIN: produces a Cartesian product by joining all possible pairs of rows
  • INNER JOIN: produces only the common rows from tables that meet the join condition
  • LEFT OUTER JOIN: produces some rows of one table, and all rows of another
  • RIGHT OUTER JOIN: resembles LEFT, but the roles of the two tables are reversed
  • FULL OUTER JOIN: produces the union of all rows from an INNER join of the two tables and of all rows of each table that have no match in the other table

The example in Listing 6 is an example of an INNER JOIN. The INNER JOIN returns only rows from tables that meet the join condition. If a row exists in one table but not the other, it is not included in the query result. The same query can be rewritten by explicitly specifying the INNER JOIN in the FROM clause, as shown in Listing 7:

Listing 7. Example of an INNER JOIN
SELECT a.company, b.order_num, b.order_date
   FROM customer a INNER JOIN orders b
   ON a.customer_num = b.customer_num ;

Listing 8 shows the results of that inner join query.

Listing 8. Results from INNER JOIN query
COMPANY                ORDER_NUM ORDER_DATE

Play Ball                   1001 05/20/1998
All Sports Supplies         1002 05/21/1998
Play Ball                   1003 05/22/1998
Watson & Son                1004 05/22/1998

The Listing 9 is an example of a LEFT OUTER JOIN. You are using the same data set as the previous example and an almost identical SQL statement. The only difference is the type of join. In this join statement, the customer table is the dominant table that appears to the left of the keyword that begins the OUTER JOIN. A LEFT OUTER JOIN returns all of the rows for which the join condition is true and, in addition, returns all other rows from the dominant table, and displays the corresponding values from the subservient table as NULL. The ORDERS table is the subservient table here.

Listing 9. Example of a LEFT OUTER JOIN
SELECT a.company, b.order_num, b.order_date
   FROM customer a LEFT OUTER JOIN orders b
   ON a.customer_num = b.customer_num ;

Listing 10 shows the results of the left outer join:

Listing 10. Query results from LEFT OUTER JOIN
COMPANY                ORDER_NUM ORDER_DATE

All Sports Supplies        1002 05/21/1998
Sports Spot
Phil's Sports
Play Ball                  1001 05/20/1998
Play Ball                  1003 05/22/1998
Play Ball                  1011 06/18/1998
Play Ball                  1013 06/22/1998
Los Altos Sports
Watson & Son               1004 05/22/1998
Watson & Son               1014 06/25/1998

Combining queries

Combine two or more queries into a single query using set operators

You can combine two or more queries into a single query by using the UNION clause, the NOT EXISTS or NOT IN clause, or the EXISTS or IN clause. Set operators process the results of the queries, eliminate duplicates, and return the final result set.

The UNION clause generates a final query result by combining two or more other query results.

Use the NOT EXISTS or NOT IN clause to introduce sub-queries that show the difference between the two sets. These clauses generate a query result by including all rows that are returned by the first query, but not by the second or any subsequent queries.

Use the EXISTS or IN clause to introduce sub-queries that show the intersection of two sets. These clauses generate a query result by including only rows that are returned by all the queries.

Listing 11 is an example of a query that makes use of the UNION set operator. In this example, you select the state column from the customer table and the corresponding code column from the state table.

Listing 11. Example of combining two queries using the UNION clause
SELECT state FROM customer
   WHERE customer_num BETWEEN 100 AND 125

UNION

SELECT code FROM state
   WHERE sname LIKE 'C%';

It is not necessary for combined columns or tables to be the same. Corresponding columns in the projection clauses for the combined queries must have compatible data types, but the columns do not need to use the same column names.


Grouping results

Summarize query result using the GROUP BY clause

Use the GROUP BY clause to organize rows in a query result. Each unique set of groups is represented by a single row in the query result. The GROUP BY clause is also used to specify what columns are to be grouped together to provide input to column functions such as COUNT, SUM, MIN, MAX, and AVG. Listing 12 shows a list of all customers and the city name where they live from the customer table:

Listing 12. Data from the customer table
FNAME           CITY

Chris           Bartlesville
Kim             Blue Island
James           Brighton
Bob             Cherry Hill
Eileen          Denver
Raymond         Los Altos
Margaret        Los Altos
Lana            Menlo Park
Alfred          Menlo Park
Jean            Mountain View
George          Mountain View
Dick            Oakland
Charles         Palo Alto
Philip          Palo Alto
Fred            Phoenix
Frank           Phoenix
Cathy           Princeton
Anthony         Redwood City
Roy             Redwood City
Donald          Redwood City
Arnold          Redwood City
Frank           Redwood City

The following example shows the use of the GROUP BY clause to count the number of customers who live in each city:

SELECT city, COUNT(city) TOTAL
  FROM customer
  GROUP BY city;

The COUNT function yields the following rows in the query result. You specify city as the GROUP BY column and label the expression column as TOTAL. A COUNT function returns the number of rows grouped into each new row.

Listing 13. Results from GROUP BY expression with COUNT function
CITY                       TOTAL

Los Altos                      2
Mountain View                  2
Brighton                       1
Palo Alto                      2
Denver                         1
Oakland                        1
Phoenix                        2
Princeton                      1
Redwood City                   5
Blue Island                    1
Menlo Park                     2
Cherry Hill                    1
Bartlesville                   1

Inserting data

Add data to a table using the INSERT statement

The INSERT statement is used to add new rows to a table or a view. Inserting a row into a view also inserts the row into the table on which the view is based. There are a few different ways you can insert data into a table. Let's take a look at some examples of the INSERT statement.

Here is an example of inserting a row into customer table using the VALUES clause:

INSERT INTO customer VALUES 
  (202,'Cerny', 'Paul', 'IBM ', '2 Main St','', 'Lenexa', 'KS',66012,'');

Here is an example of finding records from the customer table whose customer numbers are greater than 120 and inserting the same record into a temporary table named temp_cust:

SELECT * FROM customer
  WHERE customer_num > 120
  INSERT INTO TEMP temp_cust;

Here is an example of inserting records into the customer table from a delimited text file named customer.txt using the LOAD FROM clause. The text file can have one or more records.

LOAD FROM customer.txt 
  INSERT INTO customer;

Inserting values into serial columns

A table can have a column of the serial data types (SERIAL, SERIAL8, BIGSERIAL). The database server generates values for serial columns. When you insert values, specify the value zero for the serial column. The database server generates the next actual value in sequence. Serial columns do not allow NULL values.

You can insert consecutive numbers, explicit values, or explicit values that reset the value in a SERIAL, SERIAL8, or BIGSERIAL column.

  • To insert a consecutive serial value, specify a zero (0) for the serial column in the INSERT statement. In this case, the database server assigns the next highest value. The value the database server generates for you is one greater than the maximum value in the column.
  • To insert an explicit value, specify a non-zero value for a serial column and the database server uses the value. If the serial column is uniquely indexed or has a unique constraint, and your value duplicates one already in the table, an error results. If the serial column does not have a unique constraint, duplicate values can be inserted in a serial column. If the value is greater than the current maximum value, you will create a gap in the series.
  • To create a gap in the series (that is, to reset the serial value), specify a positive value that is greater than the current maximum value in the column.

NULL values are not valid in serial columns. If you use the interactive schema editor in DB-Access to define the table, a unique index is applied automatically to a SERIAL column.


Updating data

Change data in a table or view using the UPDATE statement

The UPDATE statement is used to modify the data in a table or a view. You can specify a condition by using the WHERE clause with the UPDATE statement. Each row that satisfies the conditions specified by a WHERE clause is modified in the table or view. If you do not specify a WHERE clause, all rows are modified in the table. The following example uses the UPDATE statement to modify the shipping charge to $15.00 for a particular order number:

UPDATE orders
  SET ship_charge = 15.00
  WHERE order_num = 1001;

Deleting data

Purge data from a table using the DELETE statement

The DELETE statement is used to delete entire rows of data from a table. You can specify a condition by using a WHERE clause with a DELETE statement. Each row that satisfies the conditions specified by the WHERE clause is deleted from the table. If you do not specify a WHERE clause, all rows are deleted from the table. The following example that uses the DELETE statement to delete from the orders table all records that do not have an order backlog:

DELETE FROM orders
  WHERE backlog = 'n';

Transaction concepts

Understanding transactions

A transaction is a set of one or more SQL statements that execute as a single operation. In the relational database world, it is also referred to as a unit of work. You can think of a transaction as a sequence of modifications that must be accomplished either completely or not at all. The transaction starts before the first SQL statement is executed from a sequence of SQL statements. The transaction is complete when either all the SQL statements execute successfully, or, in the case of an error, the changes made by the SQL statements are rolled back in sequence. A transaction provides protection against unforeseen failures and offers a programmable way to escape when the program detects a logical error.

You can think of transactions as activities in IDS that are tracked by logical log files. Any changes you made within a transaction are recorded in logical log files. Consider the following before using transactions:

  • Logging must be turned on for the database.
  • There must be a sufficient number of logical log files configured.

Please read the "Database Logging Mode" section in the third installment of this series for detailed information on how to turn on database logging.

In a database that was not created with the LOG MODE ANSI option, you initiate a transaction with a BEGIN WORK statement, and issue a COMMIT WORK statement to end the transaction. You can also use the ROLLBACK WORK statement to cancel all modifications done since BEGIN WORK.

BEGIN WORK

The BEGIN WORK statement starts a transaction. This statement is valid only in a database that supports transaction logging. Each row that an UPDATE, DELETE, or INSERT statement affects during a transaction is locked and remains locked throughout the transaction.

COMMIT WORK

The COMMIT WORK statement validates and terminates a transaction. This statement commits all modifications made to the database from the beginning of a transaction. The COMMIT WORK statement releases all row and table locks imposed during the transaction.

ROLLBACK WORK

The ROLLBACK WORK statement cancels and terminates a transaction. This statement invalidates all changes since the beginning of the transaction by restoring the database to the state that existed before the transaction began. The ROLLBACK WORK statement releases all row and table locks imposed during the transaction.

The WORK keyword is optional in the BEGIN WORK, COMMIT WORK, and ROLLBACK WORK statements.

The following example shows a transaction bound by BEGIN WORK and COMMIT WORK statements:

BEGIN WORK;
DELETE FROM orders WHERE backlog = 'n';
INSERT INTO STATE VALUES ('QB', 'Quebec');
COMMIT WORK;

In this example, first, you are deleting a row from the orders table where the value of the backlog column is n. Then you are inserting a new row in the state table. The transaction guarantees that both operations succeed or else neither succeeds.

If you fail to issue the COMMIT WORK statement, IDS rolls back any modifications that the transaction made to the database when the session ends. If you do not issue a BEGIN WORK statement to start a transaction, each statement executes within its own transaction and is treated as a single-statement transaction. These single-statement transactions do not require either a BEGIN WORK statement or a COMMIT WORK statement.

The type of database logging defines the way you manage transactions in your SQL statements. If the database is using ANSI logging mode, you do not start transactions with BEGIN WORK. Transactions are implicitly started in an ANSI-compliant database. You do use the COMMIT or ROLLBACK statement to end the transaction.


UDRs

User-defined routines

A user-defined routine (UDR) is a routine that you create and register in the system catalog tables and that you invoke within an SQL statement or another routine.

A UDR can be either a function or a procedure. A function is a routine that optionally accepts a set of arguments and returns a set of values. A function can be used in SQL expressions. A procedure is a routine that optionally accepts a set of arguments and does not return any values. A procedure cannot be used in SQL expressions because it does not return a value.

A user-defined routine can be written either using Informix Stored Procedure Language (SPL) or a supported external language, such as C or Java® technology.

Informix Stored Procedure Language (SPL) routines
Stored Procedure Language (SPL) is part of the database server. SPL is simple to use and requires no support outside the database server. SPL provides flow-control extensions to SQL. An SPL routine is a UDR that is written in SPL and SQL. The body of an SPL routine contains SQL statements and flow-control statements for looping and branching. SPL routines are parsed, optimized as far as possible, and then stored in the system catalog tables in executable format. If you have an application that perform some SQL tasks with little or no user interaction, the routine to perform each task can be stored on the database server. There are a couple of significant advantages to using SPL routines:
  • Using SPL routines greatly reduces processing overhead and time. Every time you execute an SQL statement, it is sent from the client to the server, and goes through a parsing and optimizing process before actually performing its operation. With a SPL routine, parsing and optimizing are part of the process of creating the routine, so it executes much faster.
  • Stored procedures reduce network traffic. A complex SQL statement can execute several times throughout the day, which can slow down the network. You can reduce the network traffic significantly by putting the same SQL statement into a stored procedure. In this case, only the statement that invokes the stored procedure goes over the network.
An SPL procedure or function can be used in many different ways:
  • You can call them from triggers to extend the ability of the trigger.
  • They can be embedded within SQL statements or called with the EXECUTE PROCEDURE statement.
  • You can pass parameters as arguments to make them dynamic.
External language routines
An external language routine is a UDR that is written in an external language. The body of an external language routine contains statements for operations such as flow control and looping. Therefore, you must use the appropriate compilation tool to parse and compile an external language routine into an executable format. The database server supports UDRs written in C and in Java technology languages.

External routines are typically used to perform operations on user-defined data types. This tutorial does not discuss external language routines in detail. Please refer to IBM Informix Database Server documentation for details.

Creating SPL routines

SPL routines are the user-defined routines written using SQL and SPL. Before you create a stored procedure, you must make a connection to the database. The create procedure operation performs the parsing, optimization, and conversion to ASCII before it is actually stored in the sysprocedures system catalog table. You can use the CREATE PROCEDURE statement to create a user-defined routine. If you are creating a procedure written in the C or Java technology languages, put the source code in a separate file and use the CREATE PROCEDURE FROM statement.

Listing 14 is a simple example of creating a stored procedure that inserts integers into a column of a table:

Listing 14. Example of creating an SPL procedure
CREATE PROCEDURE insert_rows( num INT )

   DEFINE i INTEGER;
   LET i = 1;

   WHILE i < num
      INSERT INTO table1 (numbers) VALUES (i);
      LET i = i + 1;
   END WHILE;

END PROCEDURE;

In IDS, although you can use CREATE PROCEDURE to write and register an SPL routine that returns one or more values (that is, an SPL function), it is recommended that you use CREATE FUNCTION instead. To register an external function, you must use CREATE FUNCTION.

To create an SPL function that returns one or more values, start with the CREATE FUNCTION keywords and end with the END FUNCTION keywords. Listing 15 shows how to begin and end an SPL function:

Listing 15. Example of creating an SPL function
CREATE FUNCTION read_address (lastname CHAR(15)) -- one argument
   RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15), CHAR(2),
      CHAR(5); -- 6 return values items

   DEFINE p_lname,p_fname, p_city CHAR(15); 
      --define each routine variable
   DEFINE p_add CHAR(20);
   DEFINE p_state CHAR(2);
   DEFINE p_zip CHAR(5);

   SELECT fname, address1, city, state, zipcode
      INTO p_fname, p_add, p_city, p_state, p_zip
      FROM customer
      WHERE lname = lastname;

   RETURN p_fname, lastname, p_add, p_city, p_state, p_zip;
      --6 items
END FUNCTION;

Informix Dynamic Server allows you to create more than one SPL routine with the same name but with different parameters. This feature is known as routine overloading. For example, you can create the following SPL routine in your database with the name read_address.

Listing 16. Example of routine overloading
CREATE FUNCTION read_address (customer_id INT ) -- one argument
   RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2),
      CHAR(5); -- 6 items

   DEFINE p_lname,p_fname,p_lastname,p_city CHAR(15);
      --define each routine variable
   DEFINE p_add CHAR(20);
   DEFINE p_state CHAR(2);
   DEFINE p_zip CHAR(5);

   SELECT fname, lname, address1, city, state, zipcode
      INTO p_fname, p_lastname, p_add, p_city, p_state, p_zip
      FROM customer
      WHERE customer_num = customer_id;

   RETURN p_fname, p_lastname, p_add, p_city, p_state, p_zip;
      --6 items
END FUNCTION;

Due to routine overloading, an SPL routine might not be uniquely identified by its name alone within IDS. However, a routine can be uniquely identified by a specific name. A specific name is a unique identifier that you define in the CREATE PROCEDURE or CREATE FUNCTION statement, in addition to the routine name. A specific name is defined with the SPECIFIC keyword and is unique in the database. Two routines in the same database cannot have the same specific name, even if they have different owners.

Listing 17 shows how to define the specific name in a CREATE FUNCTION statement that creates the read_address function. Let's declare read_address_bylastame as the specific name of the function that looks up address by lastname, and declare the specific name read_address_bycustid for the function that looks up address by customer number.

Listing 17. Example of a function with a specific name
CREATE FUNCTION read_address (lastname CHAR(15)) -- one argument
   RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2),
      CHAR(5)  -- 6 items
SPECIFIC read_address_bylastame;

...... .
...... .
END FUNCTION;
Listing 18. Example of the same function with a different specific name
CREATE FUNCTION read_address       (lastname CHAR(15)) -- one argument
   RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2),
      CHAR(5)  -- 6 items
SPECIFIC read_address_bycustid;

...... .
...... .
END FUNCTION;

Executing SPL routines

You can use EXECUTE PROCEDURE or EXECUTE FUNCTION to execute an SPL routine or external routine. In general, it is best to use EXECUTE PROCEDURE with SPL procedures and EXECUTE FUNCTION with SPL functions. A stored procedure can be called from DB-Access, from within an SPL routine, or from an external routine. SPL routines can execute routines written in external languages, and external language routines can execute SPL routines.

The following example shows the execution of an SPL procedure called insert_rows().

EXECUTE PROCEDURE insert_rows(10);

To execute a function, you need to consider the INTO clause to store the return value. Listing 19 is an example that shows the execution of an SPL function called update_customer(), which takes two arguments as input and uses the INTO clause to specify the variable where the return value is stored:

Listing 19. Example of an SPL function execution
CREATE PROCEDURE get_customer_info(cust_num INT)
   DEFINE lname, fname, city CHAR(15); 
   DEFINE add CHAR(20);
   DEFINE state CHAR(2);
   DEFINE zip CHAR(5);
   EXECUTE FUNCTION read_address(cust_num INT)
INTO fname, lastname, add, city, state, zip;
END PROCEDURE;

You use the CALL statement to execute a UDR from within an SPL routine. The CALL statement is identical in behavior to the EXECUTE PROCEDURE and EXECUTE FUNCTION statements, but you can only use CALL from within an SPL routine. Listing 20 is an example of calling the function read_address from the SPL routine add_numbers:

Listing 20. Example of calling UDR
CREATE PROCEDURE get_customer_info(cust_num INT)
   DEFINE lname, fname, city CHAR(15); 
   DEFINE add CHAR(20);
   DEFINE state CHAR(2);
   DEFINE zip CHAR(5);
   CALL read_address(cust_num) RETURNING fname, lastname, add, city, state, zip;
END PROCEDURE;

Necessary privileges for stored procedures

There are three levels of privileges associated with stored procedures:

  • Database-level privilege
  • Language-level privilege
  • Routine-level privilege

First, you must have the Resource privilege on a database to create any kind of SPL procedures or SPL functions within that database. By default, the Usage privilege on SPL is granted to PUBLIC. Here is an example of how you would grant the Resource privilege on the database to the role named programmers:

GRANT RESOURCE TO programmers;

Second, you need the Usage privilege on the SPL language to create an SPL procedure. The Usage privilege on the SPL language is granted to PUBLIC by default. Only user informix, the DBA, or a user who was granted the Usage privilege WITH GRANT OPTION can grant the Usage privilege on SPL to another user. Here is an example of granting the Usage privilege on the SPL language to the role named programmers:

GRANT USAGE ON LANGUAGE SPL TO programmers;

Last thing you need is routine execution permission. When you register a UDR, you automatically receive the Execute privilege on that routine. The Execute privilege allows you to invoke the UDR. Here is an example of granting the Execute privilege on the add_numbers SPL routine to the role named programmers:

GRANT EXECUTE ON add_numbers TO programmers;

Dropping a stored procedure

You can use the DROP PROCEDURE statement to drop a procedure from the database. However, the only users who can drop a procedure are the owner of the procedure and a user with DBA privilege. You can also use the DROP ROUTINE statement to remove a UDR from the database. Dropping a UDR removes the text and executable versions of the UDR from the database. Here is an example of dropping a stored procedure:

DROP PROCEDURE insert_rows;

You can also include parameter data types after the name of the procedure to identify the procedure, if there is more than one procedure with same name. For example, the following statements can be used to drop the overloaded function read_address:

drop procedure read_address(INT);
drop procedure read_address(CHAR);

If you have declared a specific name when you created the procedure, you can later use that name when you alter, drop, grant privileges, revoke privileges, or update statistics on that procedure.

Here is an example of dropping an overloaded function using a specific name:

drop specific procedure read_address_bylastname;
drop specific procedure read_address_bycustid;

Indexes created with a function

Using functional indexes

IDS also supports using functions to create an index. Creating and using functional indexes can significantly improve the performance of your SELECT queries. An index stores column values in a sorted order. A functional index transforms the data in a column and stores the derived values in sorted order.

Suppose a table separately stores the first name and last name of customers. However, the requirement is to run a query that needs to search the customer's full name (first and last name). In this scenario, you need to concatenate first name and last name together to get the desired result. Here is an example of a query that searches customers with first name starting with L and last name starting with P:

SELECT * FROM customer 
  WHERE TRIM(fname) || " " || TRIM(lname) LIKE 'L% P%';

Performance is negatively impacted when the table is large or when many sessions issue this type of query. A way to improve the performance of this query is to create a functional index on fullname.

Listing 21. Example of a function that concatenates two columns
CREATE FUNCTION fullname ( fname CHAR(15), lname CHAR(15) ) RETURNS VARCHAR(31)
        WITH (NOT VARIANT);

        RETURN TRIM(fname) || " " || TRIM(lname);
END FUNCTION;

Here is how you would create a functional index:

CREATE INDEX fullnameIndex ON customer (fullname (fname,lname) );

Now when a query on fullname is executed, the database server can use the functional index fullnameIndex to determine which rows satisfy the query. IDS ensures that the functional indexes are always consistent with the data in the table by updating them during INSERT, UPDATE, MERGE, and DELETE operations.


Summary

This tutorial has introduced you to Structured Query Language (SQL) and to some of the ways that IDS uses SQL to manipulate data in a relational database. It also covered the fundamentals of SQL, including SQL language elements, Data Manipulation Language (DML), SQL procedures, and user-defined functions.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=447633
ArticleTitle=Informix Dynamic Server 11.50 Fundamentals exam 555 Certification Preparation, Part 5: Using SQL
publish-date=11192009