SQL Keys

 A primary key is a column which uniquely identifies the records in a table. In a broad sense, a primary key is the mixture of a unique key and an index: A collumn with a primary key is indexed to deliver a faster query, and doesn't allow duplicate values to ensure specific data.

Most programmers recommend all tables having a primary key (and only one) to enhance the speed of queries and overall database performance.

An example of a primary key may be found in a table named "departments," which might have a collumn named "department_number" that uniquely identifies each department in the table with a number.

A foreign key is a column (the child collumn) in a table which has a corresponding relationship and a dependency on another collumn (the parent collumn) that is usually in a different table. Parent collumns can have multiple child collumns, but a child collumn can only have one parent collumn.

The child collumn is the collumn with the foreign key; the parent collumn does not have the foreign key "set" on it, but most databases require the parent collumn to be indexed.

Foreign keys are made to link data across multiple tables. A child collumn cannot have a record that its parent collumn does not have.

Say a table named "employees" has 20 employees (rows) in it. There are 4 departments in the "departments" table. All 20 employees must belong to a department, so a collumn in the "employees" table named "department" would point to the primary key in the "departments" table using a foreign key. Now all employees must belong to a department as specified by the "departments" table. If a department isn't specified in the "departments" table, the employee cannot be assigned to it.

A candidate key would be any key which could be used as the primary key, which means that the combination of the columns, or just the single column would create a unique key. You would then need to determine which of these candidate keys would work best as your primary key.

Using Cross Joins

Many SQL books and tutorials recommend that you “avoid cross joins” or “beware of Cartesian products” when writing your SELECT statements, which occur when you don't express joins between your tables.  It’s true that you need to ensure that your join conditions are adequately stated so that you don’t accidentally produce this effect, but it is not true that you should avoid these types of joins in every situation.
Cross Joins produce results that consist of every combination of rows from two or more tables.  That means if table A has 3 rows and table B has 2 rows, a CROSS JOIN will result in 6 rows.  There is no relationship established between the two tables – you literally just produce every possible combination.
The danger here, of course, is that if you have table A with 10,000 rows and Table B with 30,000 rows, and you accidentally create the product of these two tables, you will end up with a 300,000,000 row result -- probably not a good idea.  (Though it is great for creating test data and the like.)
So, how can this ever be useful?  Actually, if you do lots of report writing in SQL, a CROSS JOIN can be your best friend.
Suppose you need to write a report that returns total sales for each Store and each Product.  You might come up with this:

SELECT Store, Product, SUM(Sales) as TotalSales
FROM Sales
GROUP BY Store, Product

Easy enough – except when the requirement states “show $0 if a store had no sales of a particular product”.  The above query won’t do that – it returns no rows at all if a store had no sales for a particular product. 
The solution?  Well, hopefully in your database you have a table of Stores and a table of Products.  A cross join of the two results will return 1 row per combination of Store and Product:

SELECT S.Store, P.Product
FROM Stores S
CROSS JOIN Products P

That result is the perfect starting point for the results we wish to return -- now we just need to return the sales for each combination.  We already have written that in our first attempt, so now we just need to combine the two:

SELECT S.Store, P.Product, ISNULL(C.TotalSales,0) as TotalSales
FROM Stores S
CROSS JOIN Products P
LEFT OUTER JOIN
    (SELECT Store, Product, SUM(Sales) as TotalSales
     FROM Sales
     GROUP BY Store, Product) C
ON
  S.Store = C.Store AND
  P.Product = C.Product

The SELECT is derived logically from our requirements.  We start by considering all combinations of stores and products, and from there we show any matching sales data.  Our primary, driving rowset is actually not the transaction table, but rather the cross join of two entity tables!  It might seem very counter intuitive if you haven't approached the problem from this angle before, but it leads to very simple and elegant ways to solve rather complicated problems using SQL.
The solution uses what I call “the report writers magic formula”:

(A x B ) -> (C)

In my made up notation, the above reads “A cross joined with B, left outer joined to C ”.  A and B represent master tables of entities in your database, and C represents a summarized derived table of a transactional table in your database.
Some important things to note:

• All criteria for the transactions, such as date ranges and/or transaction types, need to be done in the inner transaction summary query. 
• The summarized transactional sub-query needs to be properly grouped so that it returns 1 row per combination of A and B.  Typically, this means that if the PK of table A is “A_ID” and the PK is table B is “B_ID”, then the derived table C should be grouped by A_ID, B_ID.
• All criteria that determines which entities to show on your report – i.e., certain regions or only “active” products – should be done on the outer query.

Take the previous SELECT statement, for example: Note that the inner SELECT is grouped by Product and Store, which ensures that we return 1 row per combination of Product/Store -- which perfectly matches what the cross join creates.  If we wanted to show only data for 2005, we would put the filter on the TransactionDate column within the inner SELECT (since that is the part of the statement in which we collect and summarize our transactions), but if we want only ProductID #23, we do that in the outer SELECT (since that is where we determine the population of Stores and Products to return):

SELECT S.Store, P.Product, ISNULL(C.TotalSales,0) as TotalSales
FROM Stores S
CROSS JOIN Products P
LEFT OUTER JOIN
    (SELECT Store, Product, SUM(Sales) as TotalSales
     FROM Sales
     WHERE TransactionDate between '1/1/2005' and '12/31/2005'
     GROUP BY Store, Product) C
ON
  S.Store = C.Store AND
  P.Product = C.Product
WHERE
  P.Product = 23

The CROSS JOIN technique can apply to many situations – to return total labor cost by office by month, even if month X has no labor cost, you can do a cross join of Offices with a table of all months.   Another classic example is showing all GL transactions for a specific set of companies and accounts, returning all accounts and companies even when they have no activity.
The important thing is to practice with very small sets of sample data until you get a feel for how it works.  Also, you should explicitly state CROSS JOIN in your SELECT so that it is very clear that you intend for this to happen and it is not the result of missing joins. 

Cursors in SQL procedures

In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.
A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

To use cursors in SQL procedures, you need to do the following:

1. Declare a cursor that defines a result set.
2. Open the cursor to establish the result set.
3. Fetch the data into local variables as needed from the cursor, one row at a time.
4. Close the cursor when done

To work with cursors you must use the following SQL statements:

• DECLARE CURSOR
• OPEN
• FETCH
• CLOSE

The following example demonstrates the basic use of a read-only cursor within an SQL procedure:

CREATE PROCEDURE sum_salaries(OUT sum INTEGER) 
  LANGUAGE SQL
  BEGIN
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE p_sum INTEGER;
    DECLARE p_sal INTEGER;
    DECLARE c CURSOR FOR SELECT SALARY FROM EMPLOYEE;
 
     SET p_sum = 0;

     OPEN c;

     FETCH FROM c INTO p_sal;

     WHILE(SQLSTATE = '00000') DO
        SET p_sum = p_sum + p_sal;
        FETCH FROM c INTO p_sal; 
     END WHILE;

     CLOSE c;

     SET sum = p_sum;

  END%

Here is a more complex example of use of a cursor within an SQL procedure. This example demonstrates the combined use of a cursor and SQL PL statements.


............................................................................................................................................................

General concepts

In this article, I want to tell you how to create and use server side cursors and how you can optimize a cursor performance.
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable. The server side cursors were first added in the SQL Server 6.0 release and are now supported in all editions of SQL Server 7.0 and SQL Server 2000.
Before using cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close cursor and deallocate it to release SQL Server resources.

Declaring a Cursor

Before using cursor, you first must declare the cursor, i.e. define its scrolling behavior and the query used to build the result set on which the cursor operates. To declare cursor, you can use a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions.

SQL-92 Syntax

This is SQL-92 Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]

where
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
INSENSITIVE - Specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor.
SCROLL - Specifies that cursor can fetch data in all directions, not only sequentially until the end of the result set. If this argument is not specified, FETCH NEXT is the only fetch option supported.
select_statement - The standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords.
READ ONLY - Specifies that cursor cannot be updated.
UPDATE [OF column_name [,...n]] - Specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.

Cursor Options Compatibility

	INSENSITIVE	SCROLL	READ ONLY	UPDATE
INSENSITIVE		Yes	Yes	No
SCROLL	Yes		Yes	Yes
READ ONLY	Yes	Yes		No
UPDATE	No	Yes	No

Transact-SQL Extended Syntax

This is Transact-SQL Extended Syntax:

DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR select_statement [FOR UPDATE [OF column_name [,...n]]]

where
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
LOCAL - Specifies that cursor can be available only in the batch, stored procedure, or trigger in which the cursor was created. The LOCAL cursor will be implicitly deallocated when the batch, stored procedure, or trigger terminates.
GLOBAL - Specifies that cursor is global to the connection. The GLOBAL cursor will be implicitly deallocated at disconnect.
FORWARD_ONLY - Specifies that cursor can only fetch data sequentially from the first to the last row. FETCH NEXT is the only fetch option supported.
STATIC - Specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor.
KEYSET - Specifies that cursor uses the set of keys that uniquely identify the cursor's rows (keyset), so that the membership and order of rows in the cursor are fixed when the cursor is opened. SQL Server uses a table in tempdb to store keyset. The KEYSET cursor allows updates nonkey values from being made through this cursor, but inserts made by other users are not visible. Updates nonkey values made by other users are visible as the owner scrolls around the cursor, but updates key values made by other users are not visible. If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2.
DYNAMIC - Specifies that cursor reflects all data changes made to the base tables as you scroll around the cursor. FETCH ABSOLUTE option is not supported with DYNAMIC cursor.
FAST_FORWARD - Specifies that cursor will be FORWARD_ONLY and READ_ONLY cursor. The FAST_FORWARD cursors produce the least amount of overhead on SQL Server.
READ ONLY - Specifies that cursor cannot be updated.
SCROLL_LOCKS - Specifies that cursor will lock the rows as they are read into the cursor to ensure that positioned updates or deletes made through the cursor will be succeed.
OPTIMISTIC - Specifies that cursor does not lock rows as they are read into the cursor. So, the positioned updates or deletes made through the cursor will not succeed if the row has been updated outside the cursor since this row was read into the cursor.
TYPE_WARNING - Specifies that if the cursor will be implicitly converted from the requested type to another, a warning message will be sent to the client.
select_statement - The standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords.
UPDATE [OF column_name [,...n]] - Specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.

Cursor Options Compatibility

	LOCAL	GLOBAL	FORWARD ONLY	STATIC	KEYSET	DYNAMIC	FAST FORWARD	READ ONLY	SCROLL LOCKS	OPTIMISTIC	TYPE WARNING	UPDATE
LOCAL		No	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
GLOBAL	No		Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
FORWARD_ONLY	Yes	Yes		Yes	Yes	Yes	No	Yes	Yes	Yes	Yes	Yes
STATIC	Yes	Yes	Yes		No	No	No	Yes	No	Yes	Yes	No
KEYSET	Yes	Yes	Yes	No		No	No	Yes	Yes	Yes	Yes	Yes
DYNAMIC	Yes	Yes	Yes	No	No		No	Yes	Yes	Yes	Yes	Yes
FAST_FORWARD	Yes	Yes	No	No	No	No		Yes	No	No	Yes	No
READ_ONLY	Yes	Yes	Yes	Yes	Yes	Yes	Yes		No	No	Yes	No
SCROLL_LOCKS	Yes	Yes	Yes	No	Yes	Yes	No	No		No	Yes	Yes
OPTIMISTIC	Yes	Yes	Yes	Yes	Yes	Yes	No	No	No		Yes	Yes
TYPE_WARNING	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes		Yes
UPDATE	Yes	Yes	Yes	No	Yes	Yes	No	No	Yes	Yes	Yes

Opening a Cursor

Once a cursor has been declared, you must open it to fetch data from it. To open a cursor, you can use the following syntax:

OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}

where
GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be opened; otherwise, the global cursor will be opened.
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - The name of a cursor variable that references a cursor.
After a cursor is opening, you can determine the number of rows that were found by the cursor. To get this number, you can use @@CURSOR_ROWS scalar function.

Fetching a Cursor

Once a cursor has been opened, you can fetch from it row by row and make multiple operations on the currently active row in the cursor. To fetch from a cursor, you can use the following syntax:

FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar} ] FROM ] { { [GLOBAL] cursor_name } | @cursor_variable_name} [INTO @variable_name[,...n] ]

where
NEXT - The default cursor fetch option. FETCH NEXT returns the next row after the current row.
PRIOR - Returns the prior row before the current row.
FIRST - Returns the first row in the cursor.
LAST - Returns the last row in the cursor.
ABSOLUTE {n \| @nvar} - Returns the nth row in the cursor. If a positive number was specified, the rows are counted from the top of the data set; if 0 was specified, no rows are returned; if a negative number was specified, the number of rows will be counted from the bottom of the data set.
RELATIVE {n \| @nvar} - Returns the nth row in the cursor relative to the current row. If a positive number was specified, returns the nth row beyond the current row; if a negative number was specified, returns the nth row prior the current row; if 0 was specified, returns the current row.
GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be fetched; otherwise, the global cursor will be fetched.
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - The name of a cursor variable that references a cursor.
INTO @variable_name[,...n] - Allows data returned from the cursor to be held in temporary variables. The type of variables must match the type of columns in the cursor select list or support implicit conversion. The number of variables must match the number of columns in the cursor select list.

Closing a Cursor

When you have finished working with a cursor, you can close it to release any resources and locks that SQL Server may have used while the cursor was open.
To close a cursor, you can use the following syntax:

CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name }

where
GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be closed; otherwise, the global cursor will be closed.
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - The name of a cursor variable that references a cursor.
Note. If you have closed a cursor, but have not deallocated it, you can open it again when needed.

Deallocating a Cursor

When you have finished working with a cursor and want to completely release SQL Server resources that were used by a cursor, you can deallocate a cursor.
To deallocate a cursor, you can use the following syntax:

DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name}

where
GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be deallocated; otherwise, the global cursor will be deallocated.
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - The name of a cursor variable that references a cursor.
Note. Deallocating a cursor completely removes all cursor references. So, after a cursor is deallocated, it no longer can be opened.


.................................................................................................................................................................


ProblemIn my T-SQL code I always use set based operations.  I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing.  I know cursors exist but I am not sure how to use them.  Can you provide some cursor examples?  Can you give any guidance on when to use cursors?  I assume Microsoft created them for a reason so they must have a place where they can be used in an efficient manner.
SolutionIn some circles cursors are never used, in others they are a last resort and in other groups they are used regularly.  In each of these camps they have different reasons for their stand on cursor usage.  Regardless of your stand on cursors they probably have a place in particular circumstances and not in others.  So it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor based processing is appropriate or not.  To get started let's do the following:

• Look at an example cursor
• Break down the components of the cursor
• Provide additional cursor examples
• Analyze the pros and cons of cursor usage

Example Cursor
Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name  DECLARE @path VARCHAR(256) -- path for backup files  DECLARE @fileName VARCHAR(256) -- filename for backup  DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:\Backup\'  SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR  SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')  OPEN db_cursor   FETCH NEXT FROM db_cursor INTO @name   WHILE @@FETCH_STATUS = 0   BEGIN          SET @fileName = @path + @name + '_' + @fileDate + '.BAK'         BACKUP DATABASE @name TO DISK = @fileName         FETCH NEXT FROM db_cursor INTO @name   END   CLOSE db_cursor   DEALLOCATE db_cursor

Cursor Components
Based on the example above, cursors include these components:

• DECLARE statements - Declare variables used in the code block
• SET\SELECT statements - Initialize the variables to a specific value
• DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
  • NOTE - There are an equal number of variables in the DECLARE <cursor_name> CURSOR FOR statement as there are in the SELECT statement.  This could be 1 or many variables and associated columns.
• OPEN statement - Open the cursor to begin data processing
• FETCH NEXT statements - Assign the specific values from the cursor to the variables
  • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
• WHILE statement - Condition to begin and continue data processing
• BEGIN...END statement - Start and end of the code block
  • NOTE - Based on the data processing multiple BEGIN...END statements can be used
• Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE statement - Destroys the cursor

Additional Cursor Examples
In the example above backups are issued via a cursor, check out these other tips that leverage cursor based logic:

• Managing SQL Server Database Fragmentation
• SQL Server script to rebuild all indexes for all tables and all databases
• SQL Server Index Analysis Script for All Indexes on All Tables
• Standardize your SQL Server data with this text lookup and replace function
• Automating Transaction Log Backups for All SQL Server Databases
• Searching and finding a string value in all columns in a SQL Server table
• Scripting SQL Server Database Objects Using DMO (Distributed Management Objects)
• Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server
• Capacity Planning for SQL Server 2000 Database Storage
• Automate Restoration of Log Shipping Databases for Failover in SQL Server 2000
• Determining space used for each table in a SQL Server database
• Auditing Windows Groups from SQL Server
• SQL Server Find and Replace Values in All Tables and All Text Columns
• Easing the SQL Server Database Capacity Planning Burden
• Index Metadata and Statistics Update Date for SQL Server

Cursor Analysis
The analysis below is intended to serve as insight into various scenarios where cursor based logic may or may not be beneficial:

• Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions.  Our team has run into a third party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence.  Typically, SET based logic is more than feasible and cursors are rarely needed.
• Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed.  However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values.  We have had the same experience when needing to aggregate data for downstream processes, a cursor based approach was quick to develop and performed in an acceptable manner to meet the need.
• Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
• Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor based logic, but other system based objects exist to fulfill the need.  In some of those circumstances, cursors are used to complete the process.
• Large data sets - With large data sets you could run into any one or more of the following:
  • Cursor based logic may not scale to meet the processing needs.
  • With large set based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues.  As such, a cursor based approach may meet the need.
  • Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
  • If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed.  All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
  • SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
  • Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a checkpoint or marking each row with the cursor.  However, with a set based approach that may not be the case until an entire set of data is completed.  As such, troubleshooting the row with the problem may be more difficult.

Cursor Alternatives
Below outlines alternatives to cursor based logic which could meet the same needs:

• Set based logic
• SQL Server Integration Services (SSIS) or Data Transformation Services (SSIS)
• WHILE loop
• COALSCE
  • The Many Uses of Coalesce in SQL Server
• sp_MSforeachdb
  • Run The Same SQL Command Against All SQL Server Databases
• sp_MSforeachtable
  • Here is a tip with example code - http://www.mssqltips.com/tip.asp?tip=1274
• CASE expression
  • Using the CASE expression instead of dynamic SQL in SQL Server
• Repeat a batch with the GO command
  • Executing a T-SQL batch multiple times using GO

Next Steps

• When you are faced with a data processing decision determine where you stand with SQL Server cursor usage.  They may or may not have a place in your application or operational processes.  There are many ways to complete a task, so using a cursor could be a reasonable alternative or not.  You be the judge.
• If you run into issues with another coding technique and need to get something done quickly, using a cursor may be a viable alternative.  It may take longer to process the data, but the coding time might be much less.  If you have a one time process or nightly processing, this could do the trick.
• If cursors are shunned in your environment, be sure to select another viable alternative.  Just be sure the process will not cause other issues.  As an example, if a cursor is used and millions of rows are processed will this potentially flush all of the data from cache and cause further contention?  Or with a large data set will the data be paged to disk or written to a temporary directory?
• As you evaluate a cursor based approach versus other alternatives make a fair comparison of the techniques in terms of time, contention and resources needed.  Hopefully these factors will drive you to the proper technique.

...........................................................................................................................................................................

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Login to rate.

• May 14, 2004
• By Ali Sufyan
• 
• Send Email »
• More Articles »

Font Size

In a relational dtaabse, operations are carried out on a complete set of rows in a table. The SELECT statement returns all the rows that satisfy the condition in the WHERE clause of the statement. This complete set of rows that the SELECT statement returns is called a resultset.
Some applications, such as interactive and online applications, cannot work effectively with the entire set as a unit. These applications need a mechanism to work with one row, or a small block of rows at a time.
For example, consider that you want to see a list of soaps available in a shop. In technical terms, the user wants selected records from the table. Once these records are available, you also would like to see all the records one by one.
A cursor is a database object that applications use to manipulate data by rows instead of recordsets. You can use cursors to perform multiple operations in a row-by-row manner, against the resultset. You can do this with or without returning to the original table. In other words, cursors conceptually return a resultset based on tables within a database.
You can use the cursors to do the following:

• Allow positioning at specific rows of the resultset.
• Retrieve a single row, or set of rows, from the current positions in the resultset.
• Support data modifications to the rows at the current position in the resultset.
• Support different levels of visibility to changes that others users make to the database data that is presented in the resultset.
• Provide T-SQL statements in scripts, stored procedures, and triggers, to access the data in the resultset.

Creating a Cursor

• Post a comment
• Email Article
• Print Article
• Share Articles
  • Digg
  • del.icio.us
  • Newsvine
  • Facebook
  • Google
  • LinkedIn
  • MySpace
  • Reddit
  • Slashdot
  • StumbleUpon
  • Technorati
  • Twitter
  • Windows Live
  • YahooBuzz
  • FriendFeed

You can use the following two methods to create a cursor in SQL Server:

1. The T-SQL language, which supports the syntax for using cursors modelled after the Sql-92 cursor syntax.
2. Database application programming interface.

In this article, we will focus on T-SQl cursors. The syntax of T-SQL cursors and API cursors is different, but they follow a common sequence of steps.
Follow these steps to create a cursor:

1. Associate a cursor with a resultSet of a T-SQL statement, and define the characteristics of the cursor, such as how the rows are going to be retrieved, and so forth.
2. Execute the T-SQL statement to populate the cursor.
3. Retrieve the rows in the cursor. The operation to retrieve one row or a set of rows is called fetch. Scrolling is a series of fetch operations to retrieve the rows in a backward or forward direction.
4. You can also perform the modifications on a row at the cursor position.
5. Close the cursor.

Now We Shall See the Syntax

1. Use the DECLARE statement to create the cursor. It contains a SQL statement to include the records from the table.

DECLARE <Cursor_Name> CURSOR
FOR
<Select Statement>

2. After creating the cursor, you will open it. Use the OPEN statement to make the cursor accessible.

OPEN <Cursor_name>

3. Use the FETCH statement to obtain the records from the cursor for further processing.

FETCH <Cursor_name>

4. Use the CLOSE statement to temporarily close the cursor when it is not required. This statement releases the current resultset to close an open cursor. You have to re-open the cursor to fetch the rows.

CLOSE <Cursor_Name>

5. When you no longer require the cursor, you can use the DEALLOCATE statement to remove its reference.

DEALLOCATE <Cursor_Name>

Fetching and Scrolling Through a Cursor

When you open a cursor, the current row pointer in the cursor is logically before the first row. T-SQL cursors can fetch one row at a time. The options for FETCH operations are as follows:

Option	Description
FETCH FIRST	Fetches the first row in the cursor
FETCH NEXT	Fetches the row after the previously fetched row
FETCH PRIOR	Fetches the row before the previously fetched row
FETCH LAST	Fetches the last row in the cursor
FETCH ABSOLUTE n	If n is a positive integer, it fetches the nth row in a cursor. If n is a negative integer, it fetches the nth row before the last row. If n is 0, no row is fetched.
FETCH RELATIVE n	If n is positive, it fetches the nth row from the previously fetched row. If n is negative, it fetches the nth row before the previously fetched row.If n is 0, the same row is fetched again.

By default, the FETCH NEXT option works. To use other options, you must include certain options in the DECLARE statement while creating the cursors.

DECLARE Statement Options

You can add the following attributes to the cursor to enhance its scrollability of a cursor. The attributes also are explained in the following list.

DECLARE <Cursor_Name> CURSOR
[LOCAL | GLOBAL]
[FORWARD ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR <Selecting Statements>
[FOR UPDATE [OF Column_name[,....N]]]

• LOCAL: Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is valid only within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.
  
• GLOBAL: Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.
• FORWARD_ONLY: Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified, the other cannot be specified.
  
• STATIC: Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.
• KEYSET: Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor). If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.
  
• DYNAMIC: Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.
• FAST_FORWARD: Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified, the other cannot be specified.
• READ_ONLY: Prevents updates from being made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.
• SCROLL_LOCKS: Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. Microsoft SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.
• OPTIMISTIC: Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.
• TYPE_WARNING: Specifies that a warning message is sent to the client if the cursor is implicitly converted from the requested type to another. select_statement is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within a select_statement of a cursor declaration. SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type. For more information, see Implicit Cursor Conversions.
  UPDATE [OF column_name [,...n]] defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.
  

Example 1

Declare Flight_cursor CURSOR SCROLL
FOR SELECT * from flight Order By Aircraft_code
Open Flight_Cursor
Fetch First From Flight_Cursor
While @@Fetch_Status =0
BEGIN
Fetch NExt from Flight_Cursor
End

Explanation of Example 1

This simply creates a cursor that selects all records from a flight table, then it opens the cursor. Then, it fetches the first record from the cursor and it moves the cursor ahead until the last executed cursor FETCH statement returns 0.
@@FETCH_STATUS returns an integer for the last executed cursor FETCH statement. @@CURSOR_ROWS returns the number of qualifying rows that are present in the currently open cursor.

Example 2

Declare @@counter int
set @@counter=0
Declare @@ProductID int
Declare @@ProductName varchar(30)
Declare @@Qty int

Declare special cursor
local Scroll Keyset Optimistic
For
Select * from Products
Open special    /* Opening the cursor */
fetch Absolute 1 from special
into @@ProductID,@@ProductName,@@Qty

while @@fetch_Status<>-1
begin
fetch next from special
into @@ProductID,@@ProductName,@@Qty
Print @@ProductiD
Print @@ProductName
print @@Qty

/*set @@ProductName=(Select Prod_Name from special)*/
Update Products set Prod_Name= @@counter

set @@counter=@@counter+1
end
close special
Deallocate special

Explanation of Example 2

In this cursor, we are fetching the values from the recordSet in set of variables. We further use them to update the record. CLOSING and DEALLOCATING is a good practice; it increase performance and security.

SQL Functions

SQL Functions 

Advantages of UDFs

So why are UDFs important? What can you do with UDFs that you cannot with stored procedures? Well, UDFs are not functionally stronger than stored procedures (in fact, UDFs have many limitations, which we will examine shortly). However, UDFs do provide certain advantages, discussed in the following sections.

 

SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user-defined functions written in PL/SQL.
If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, then Oracle attempts to convert the argument to the expected datatype before performing the SQL function. If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, and REPLACE.
In the syntax diagrams for SQL functions, arguments are indicated by their datatypes. When the parameter function appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values.

Note: When you apply SQL functions to LOB columns, Oracle Database creates temporary LOBs during SQL and PL/SQL processing. You should ensure that temporary tablespace quota is sufficient for storing these temporary LOBs for your application.

See Also: "User-Defined Functions " for information on user functions and "Data Conversion " for implicit conversion of datatypes Oracle Text Reference for information on functions used with Oracle Text Oracle Data Mining Application Developer's Guide for information on frequent itemset functions used with Oracle Data Mining

The syntax showing the categories of functions follows:

function::=

Description of the illustration function.gif


single_row_function::=

Description of the illustration single_row_function.gif

The sections that follow list the built-in SQL functions in each of the groups illustrated in the preceding diagrams except user-defined functions. All of the built-in SQL functions are then described in alphabetical order.

See Also: "User-Defined Functions " and CREATE FUNCTION

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.

Numeric Functions

Numeric functions accept numeric input and return numeric values. Most numeric functions that return NUMBER values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits. The numeric functions are:

ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET

Character Functions Returning Character Values

Character functions that return character values return values of the same datatype as the input argument. The length of the value returned by the function is limited by the maximum length of the datatype returned.

• For functions that return CHAR or VARCHAR2, if the length of the return value exceeds the limit, then Oracle Database truncates it and returns the result without an error message.
• For functions that return CLOB values, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data.

The character functions that return character values are:

CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER

Character Functions Returning Number Values

Character functions that return number values can take as their argument any character datatype.
The character functions that return number values are:

ASCII
INSTR
LENGTH
REGEXP_INSTR

Datetime Functions

Datetime functions operate on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.
Some of the datetime functions were designed for the Oracle DATE datatype (ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY). If you provide a timestamp value as their argument, Oracle Database internally converts the input type to a DATE value and returns a DATE value. The exceptions are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at all.
The remaining datetime functions were designed to accept any of the three types of data (date, timestamp, and interval) and to return a value of one of these types.
The datetime functions are:

ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_DSINTERVAL
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET

Conversion Functions

Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype. The SQL conversion functions are:

ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_DSINTERVAL
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR

Collection Functions

The collection functions operate on nested tables and varrays. The SQL collection functions are:

CARDINALITY
COLLECT
POWERMULTISET
POWERMULTISET_BY_CARDINALITY
SET

Miscellaneous Single-Row Functions

The following single-row functions do not fall into any of the other single-row function categories:

BFILENAME
COALESCE
CV
DECODE
DEPTH
DUMP
EMPTY_BLOB, EMPTY_CLOB
EXISTSNODE
EXTRACT (XML)
EXTRACTVALUE
GREATEST
LEAST
LNNVL
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NULLIF
NVL
NVL2
ORA_HASH
PATH
PRESENTNNV
PRESENTV
PREVIOUS
SYS_CONNECT_BY_PATH
SYS_CONTEXT
SYS_DBURIGEN
SYS_EXTRACT_UTC
SYS_GUID
SYS_TYPEID
SYS_XMLAGG
SYS_XMLGEN
UID
UPDATEXML
USER
USERENV
VSIZE
XMLAGG
XMLCOLATTVAL
XMLCONCAT
XMLFOREST
XMLSEQUENCE
XMLTRANSFORM

Aggregate Functions

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.

See Also: "Using the GROUP BY Clause: Examples" and the "HAVING Clause " for more information on the GROUP BY clause and HAVING clauses in queries and subqueries

Many (but not all) aggregate functions that take a single argument accept these clauses:

• DISTINCT causes an aggregate function to consider only distinct values of the argument expression.
• ALL causes an aggregate function to consider all values, including all duplicates.

For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither, then the default is ALL.
All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.
You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments in the sample schema hr:

SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;

AVG(MAX(SALARY))
----------------
           10925

This calculation evaluates the inner aggregate (MAX(salary)) for each group defined by the GROUP BY clause (department_id), and aggregates the results again.
The aggregate functions are:

AVG
COLLECT
CORR
CORR_*
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
MAX
MEDIAN
MIN
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
REGR_ (Linear Regression) Functions
STATS_BINOMIAL_TEST
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_*
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE

Analytic Functions

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

analytic_function::=

Description of the illustration analytic_function.gif


analytic_clause::=

Description of the illustration analytic_clause.gif


query_partition_clause::=

Description of the illustration query_partition_clause.gif


order_by_clause::=

Description of the illustration order_by_clause.gif


windowing_clause ::=

Description of the illustration windowing_clause.gif

The semantics of this syntax are discussed in the sections that follow.

analytic_function
Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).

arguments
Analytic functions take 0 to 3 arguments. The arguments can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence and implicitly converts the remaining arguments to that datatype. The return type is also that datatype, unless otherwise noted for an individual function.

See Also: "Numeric Precedence " for information on numeric precedence and Table 2-11, "Implicit Type Conversion Matrix" for more information on implicit conversion

analytic_clause
Use OVER analytic_clause to indicate that the function operates on a query result set. That is, it is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.

Notes on the analytic_clause:

• You cannot specify any analytic function in any part of the analytic_clause. That is, you cannot nest analytic functions. However, you can specify an analytic function in a subquery and compute another analytic function over it.
• You can specify OVER analytic_clause with user-defined analytic functions as well as built-in analytic functions. See CREATE FUNCTION .

query_partition_clause
Use the PARTITION BY clause to partition the query result set into groups based on one or more value_expr. If you omit this clause, then the function treats all rows of the query result set as a single group.
To use the query_partition_clause in an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in the model_column_clauses) or a partitioned outer join (in the outer_join_clause), use the lower branch of the syntax (with parentheses).
You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys.
If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause, then the function computations are parallelized as well.
Valid values of value_expr are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.

order_by_clause
Use the order_by_clause to specify how data is ordered within a partition. For all analytic functions except PERCENTILE_CONT and PERCENTILE_DISC (which take only a single key), you can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.
Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.
Whenever the order_by_clause results in identical values for multiple rows, the function returns the same result for each of those rows. Please refer to the analytic example for SUM for an illustration of this behavior.

Restriction on the ORDER BY Clause
When used in an analytic function, the order_by_clause must take an expression (expr). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.

ASC | DESC
Specify the ordering sequence (ascending or descending). ASC is the default.

NULLS FIRST | NULLS LAST
Specify whether returned rows containing nulls should appear first or last in the ordering sequence.
NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.
Analytic functions always operate on rows in the order specified in the order_by_clause of the function. However, the order_by_clause of the function does not guarantee the order of the result. Use the order_by_clause of the query to guarantee the final result ordering.

See Also: order_by_clause of SELECT for more information on this clause

windowing_clause
Some analytic functions allow the windowing_clause. In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*).

ROWS | RANGE
These keywords define for each row a window (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window moves through the query result set or partition from top to bottom.

• ROWS specifies the window in physical units (rows).
• RANGE specifies the window as a logical offset.

You cannot specify this clause unless you have specified the order_by_clause.
The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the order_by_clause to achieve this unique ordering.

BETWEEN ... AND
Use the BETWEEN ... AND clause to specify a start point and end point for the window. The first expression (before AND) defines the start point and the second expression (after AND) defines the end point.
If you omit BETWEEN and specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.

UNBOUNDED PRECEDING
Specify UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.

UNBOUNDED FOLLOWING
Specify UNBOUNDED FOLLOWING to indicate that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification.

CURRENT ROW
As a start point, CURRENT ROW specifies that the window begins at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the end point cannot be value_expr PRECEDING.
As an end point, CURRENT ROW specifies that the window ends at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the start point cannot be value_expr FOLLOWING.

value_expr PRECEDING or value_expr FOLLOWING
For RANGE or ROW:

• If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING.
• If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING.

If you are defining a logical window defined by an interval of time in numeric format, then you may need to use conversion functions.

See Also: NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into intervals

If you specified ROWS:

• value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.
• If value_expr is part of the start point, then it must evaluate to a row before the end point.

If you specified RANGE:

• value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. Please refer to "Literals " for information on interval literals.
• You can specify only one expression in the order_by_clause
• If value_expr evaluates to a numeric value, then the ORDER BY expr must be a numeric or DATE datatype.
• If value_expr evaluates to an interval value, then the ORDER BY expr must be a DATE datatype.

If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Analytic functions are commonly used in data warehousing environments. In the list of analytic functions that follows, functions followed by an asterisk (*) allow the full syntax, including the windowing_clause.

AVG *
CORR *
COVAR_POP *
COVAR_SAMP *
COUNT *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
MAX *
MIN *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *

See Also: Oracle Data Warehousing Guide for more information on these functions and for scenarios illustrating their use

Object Reference Functions

Object reference functions manipulate REFs, which are references to objects of specified object types. The object reference functions are:

DEREF
MAKE_REF
REF
REFTOHEX
VALUE

See Also: Oracle Database Concepts for more information about REFs

Model Functions

Model functions are relevant only for interrow calculations and can be used only in the model_clause of the SELECT statement. They are nonrecursive. The model functions are:

CV
ITERATION_NUMBER
PRESENTNNV
PRESENTV
PREVIOUS


...............................................................................................................................................................................................

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

---

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.
Useful scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

Tip: The aggregate functions and the scalar functions will be explained in details in the next chapters.
......................................................................................................................................................................

CREATE FUNCTION (SQL Scalar, Table, or Row) statement

The CREATE FUNCTION (SQL Scalar, Table, or Row) statement is used to define a user-defined SQL scalar, table, or row function. A scalar function returns a single value each time it is invoked, and is generally valid wherever an SQL expression is valid. A table function can be used in a FROM clause and returns a table. A row function can be used as a transform function and returns a row.

Invocation This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization The privileges held by the authorization ID of the statement must include at least one of the following:

• For each table, view, or nickname identified in any fullselect:
  • CONTROL privilege on that table, view, or nickname, or
  • SELECT privilege on that table, view, or nickname
  and at least one of the following:
  • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the function does not exist
  • CREATEIN privilege on the schema, if the schema name of the function refers to an existing schema
• SYSADM or DBADM authority

Group privileges other than PUBLIC are not considered for any table or view specified in the CREATE FUNCTION statement.
Authorization requirements of the data source for the table or view referenced by the nickname are applied when the function is invoked. The authorization ID of the connection can be mapped to a different remote authorization ID.
If a function definer can only create the function because the definer has SYSADM authority, the definer is granted implicit DBADM authority for the purpose of creating the function.
If the authorization ID of the statement does not have SYSADM or DBADM authority, the privileges held by the authorization ID of the statement must also include all of the privileges necessary to invoke the SQL statements that are specified in the function body.

Syntax

>>-CREATE FUNCTION--function-name------------------------------->

>--(--+--------------------------------+--)--*------------------>
      | .-,--------------------------. |
      | V                            | |
      '---parameter-name--data-type1-+-'

>--RETURNS--+-data-type2-----------------+--*------------------->
            '-+-ROW---+--| column-list |-'
              '-TABLE-'

                                   .-LANGUAGE SQL-.
>--+-------------------------+--*--+--------------+--3 *---------->
   '-SPECIFIC--specific-name-'

                                        .-NOT DETERMINISTIC-.
>--+------------------------------+--*--+-------------------+--->
   '-3 PARAMETER CCSID--+-3 ASCII---+-'     '-DETERMINISTIC-----'
                      '-3 UNICODE-'

      .-EXTERNAL ACTION----.     .-READS SQL DATA---------.
>--*--+--------------------+--*--+------------------------+----->
      '-NO EXTERNAL ACTION-'     +-CONTAINS SQL-----------+
                                 |                   (1)  |
                                 '-4 MODIFIES SQL DATA------'

      .-STATIC DISPATCH-.     .-CALLED ON NULL INPUT-.
>--*--+-----------------+--*--+----------------------+--*------->

   .-INHERIT SPECIAL REGISTERS-.
>--+---------------------------+--*----------------------------->

>--+----------------------------------------------------+------->
   |                                               (2)  |
   '-PREDICATES--(--| predicate-specification |--)------'

   .-7 INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST-.
>--+----------------------------------------------+------------->
   '-7 INHERIT ISOLATION LEVEL WITH LOCK REQUEST----'

>--| SQL-function-body |---------------------------------------><

column-list:

      .-,-----------------------.
      V                         |
|--(----column-name--data-type3-+--)----------------------------|

SQL-function-body:

|--+-RETURN Statement-----------+-------------------------------|
   '-dynamic-compound-statement-'

Notes:

1. 4 Valid only if RETURNS specifies a table (TABLE column-list)
2. Valid only if RETURNS specifies a scalar result (data-type2)

Description

function-name

Names the function being defined. It is a qualified or unqualified name that designates a function. The unqualified form of function-name is an SQL identifier (with a maximum length of 18). In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifiers, together with the number of parameters and the data type of each parameter (without regard for any length, precision or scale attributes of the data type) must not identify a function described in the catalog (SQLSTATE 42723). The unqualified name, together with the number and data types of the parameters, while of course unique within its schema, need not be unique across schemas. If a two-part name is specified, the schema-name cannot begin with 'SYS' (SQLSTATE 42939). A number of names used as keywords in predicates are reserved for system use, and cannot be used as a function-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH, and the comparison operators. The same name can be used for more than one function if there is some difference in the signature of the functions. Although there is no prohibition against it, an external user-defined table function should not be given the same name as a built-in function.

parameter-name

A name that is distinct from the names of all other parameters in this function.

data-type1

Specifies the data type of the parameter:

• SQL data type specifications and abbreviations that may be specified in the data-type1 definition of a CREATE TABLE statement.
• REF may be specified, but that REF is unscoped. The system does not attempt to infer the scope of the parameter or result. Inside the body of the function, a reference type can be used in a dereference operation only by first casting it to have a scope. Similarly, a reference returned by an SQL function can be used in a dereference operation only by first casting it to have a scope.
• LONG VARCHAR and LONG VARGRAPHIC data types may not be used (SQLSTATE 42815).

RETURNS

This mandatory clause identifies the type of output of the function.

data-type2

Specifies the data type of the output. In this statement, exactly the same considerations apply as for the parameters of SQL functions described above under data-type1 for function parameters.

ROW column-list

Specifies that the output of the function is a single row. If the function returns more than one row, an error is raised (SQLSTATE 21505). The column-list must include at least two columns (SQLSTATE 428F0). A row function can only be used as a transform function for a structured type (having one structured type as its parameter and returning only base types).

TABLE column-list

Specifies that the output of the function is a table.

column-list

The list of column names and data types returned for a ROW or TABLE function

column-name

Specifies the name of this column. The name cannot be qualified and the same name cannot be used for more than one column of the row.

data-type3

Specifies the data type of the column, and can be any data type supported by a parameter of the SQL function.

SPECIFIC specific-name

Provides a unique name for the instance of the function that is being defined. This specific name can be used when sourcing on this function, dropping the function, or commenting on the function. It can never be used to invoke the function. The unqualified form of specific-name is an SQL identifier (with a maximum length of 18). The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifier, must not identify another function instance that exists at the application server; otherwise an error is raised (SQLSTATE 42710). The specific-name may be the same as an existing function-name.
If no qualifier is specified, the qualifier that was used for function-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier of function-name or an error is raised (SQLSTATE 42882).
If specific-name is not specified, a unique name is generated by the database manager. The unique name is SQL followed by a character timestamp, SQLyymmddhhmmssxxx.

LANGUAGE SQL

Specifies that the function is written using SQL.

3 3

PARAMETER CCSID

3

Specifies the encoding scheme to use for all string data 3 passed into and out of the function. 3 If the PARAMETER CCSID clause is not specified, the default is 3 PARAMETER CCSID UNICODE for Unicode databases, and PARAMETER CCSID 3 ASCII for all other databases. 3 3

3

ASCII

3

Specifies that string data is encoded in the database 3 code page. 3 If the database is a Unicode database, PARAMETER CCSID ASCII cannot 3 be specified (SQLSTATE 56031). 3

3

UNICODE

3

Specifies that character data is in UTF-8, and that graphic 3 data is in UCS-2. 3 If the database is not a Unicode database, PARAMETER CCSID UNICODE 3 cannot be specified (SQLSTATE 56031). 3

3

3

DETERMINISTIC or NOT DETERMINISTIC

This optional clause specifies whether the function always returns the same results for given argument values (DETERMINISTIC) or whether the function depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC function must always return the same table from successive invocations with identical inputs. Optimizations taking advantage of the fact that identical inputs always produce the same results are prevented by specifying NOT DETERMINISTIC. NOT DETERMINISTIC must be explicitly or implicitly specified if the body of the function accesses a special register or calls another non-deterministic function (SQLSTATE 428C2).

NO EXTERNAL ACTION or EXTERNAL ACTION

This optional clause specifies whether or not the function takes some action that changes the state of an object not managed by the database manager. By specifying NO EXTERNAL ACTION, the system can use certain optimizations that assume functions have no external impacts. EXTERNAL ACTION must be explicitly or implicitly specified if the body of the function calls another function that has an external action (SQLSTATE 428C2).

4 4

CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA

4

Indicates what type of SQL statements can be executed. 4 4

4

CONTAINS SQL

4

Indicates that SQL statements that neither read nor modify SQL data 4 can be executed by the function (SQLSTATE 42985). 4

4

READS SQL DATA

4

Indicates that SQL statements that do not modify SQL 4 data can be executed by the function (SQLSTATE 42985). 4

4 4

MODIFIES SQL DATA

4

Indicates that all SQL statements supported in 4 dynamic-compound-statement can be executed by the function. 4

4 4

4

STATIC DISPATCH

This optional clause indicates that at function resolution time, DB2 chooses a function based on the static types (declared types) of the parameters of the function.

CALLED ON NULL INPUT

This clause indicates that the function is called regardless of whether any of its arguments are null. It can return a null value or a non-null value. Responsibility for testing null argument values lies with the user-defined function. The phrase NULL CALL may be used in place of CALLED ON NULL INPUT.

INHERIT SPECIAL REGISTERS

This optional clause indicates that updatable special registers in the function will inherit their initial values from the environment of the invoking statement. For a function that is invoked in the select-statement of a cursor, the initial values are inherited from the environment when the cursor is opened. For a routine that is invoked in a nested object (for example, a trigger or a view), the initial values are inherited from the runtime environment (not the object definition). No changes to the special registers are passed back to the caller of the function.
Some special registers, such as the datetime special registers, reflect a property of the statement currently executing, and are therefore never inherited from the caller.

PREDICATES

For predicates using this function, this clause identifies those that can exploit the index extensions, and can use the optional SELECTIVITY clause for the predicate's search condition. If the PREDICATES clause is specified, the function must be defined as DETERMINISTIC with NO EXTERNAL ACTION (SQLSTATE 42613). 3 If the PREDICATES clause is specified, and the database 3 is not a Unicode database, PARAMETER CCSID UNICODE must not be 3 specified (SQLSTATE 42613).

predicate-specification

For details on predicate specification, see "CREATE FUNCTION (External Scalar)".

7 7

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST or INHERIT ISOLATION LEVEL WITH LOCK REQUEST

7

Specifies whether or not a lock request can be associated with 7 the isolation-clause of the statement when the function inherits the 7 isolation level of the statement that invokes the function. 7 The default is INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST. 7 7

7

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST

7

Specifies that, as the function inherits the isolation level of 7 the invoking statement, it cannot be invoked in the context of an SQL 7 statement which includes a lock-request-clause as part of a specified 7 isolation-clause (SQLSTATE 42601). 7

7

INHERIT ISOLATION LEVEL WITH LOCK REQUEST

7

Specifies that, as the function inherits the isolation level of 7 the invoking statement, it also inherits the specified 7 lock-request-clause. 7

7

7

SQL-function-body

Specifies the body of the function. Parameter names can be referenced in the SQL-function-body. Parameter names may be qualified with the function name to avoid ambiguous references. If the SQL-function-body is a dynamic compound statement, it must contain at least one RETURN statement, and a RETURN statement must be executed when the function is called (SQLSTATE 42632). If the function is a table or row function, it can contain only one RETURN statement, which must be the last statement in the dynamic compound statement (SQLSTATE 429BD).

Notes

• Resolution of function calls inside the function body is done according to the function path that is effective for the CREATE FUNCTION statement and does not change after the function is created.
• If an SQL function contains multiple references to any of the date or time special registers, all references return the same value, and it will be the same value returned by the register invocation in the statement that called the function.
• The body of an SQL function cannot contain a recursive call to itself or to another function or method that calls it, since such a function could not exist to be called.
• The following rules are enforced by all statements that create functions or methods:
  • A function may not have the same signature as a method (comparing the first parameter-type of the function with the subject-type of the method).
  • A function and a method may not be in an overriding relationship. That is, if the function were a method with its first parameter as subject, it must not override, or be overridden by, another method. For more information about overriding methods, see the "CREATE TYPE (Structured)" statement.
  • Because overriding does not apply to functions, it is permissible for two functions to exist such that, if they were methods, one would override the other.
  For the purpose of comparing parameter-types in the above rules:
  
  • Parameter-names, lengths, AS LOCATOR, and FOR BIT DATA are ignored.
  • A subtype is considered to be different from its supertype.
• Table access restrictions If a function is defined as READS SQL DATA, no statement in the function can access a table that is being modified by the statement that invoked the function (SQLSTATE 57053). For example, suppose the user-defined function BONUS() is defined as READS SQL DATA. If the statement UPDATE EMPLOYEE SET SALARY = SALARY + BONUS(EMPNO) is invoked, no SQL statement in the BONUS function can read from the EMPLOYEE table.
  7 If a function defined with MODIFIES SQL DATA contains 7 nested CALL statements, read access to the tables being modified by 7 the function (by either the function definition or the statement that 7 invoked the function) is not allowed (SQLSTATE 57053).
  
• Privileges The definer of a function always receives the EXECUTE privilege on the function, as well as the right to drop the function. The definer of a function is also given the WITH GRANT OPTION on the function if the definer has WITH GRANT OPTION on all privileges required to define the function, or if the definer has SYSADM or DBADM authority.
  The definer of a function only acquires privileges if the privileges from which they are derived exist at the time the function is created. The definer must have these privileges either directly, or because PUBLIC has the privileges. Privileges held by groups of which the function definer is a member are not considered. When using the function, the connected user's authorization ID must have the valid privileges on the table or view that the nickname references at the data source.
  
• Compatibilities
  • 3 For compatibility with DB2 UDB for OS/390 and z/OS: 3 3
    3
    • The following syntax is accepted as the default behavior: 3 3
      3
      • CCSID UNICODE in a Unicode database
      3
      • CCSID ASCII in a non-Unicode database
  • For compatibility with previous versions of DB2:
    • NULL CALL can be specified in place of CALLED ON NULL INPUT

Examples Example 1: Define a scalar function that returns the tangent of a value using the existing sine and cosine functions.

CREATE FUNCTION TAN (X DOUBLE)
     RETURNS DOUBLE
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN SIN(X)/COS(X)       

Example 2: Define a transform function for the structured type PERSON.

CREATE FUNCTION FROMPERSON (P PERSON)
     RETURNS ROW (NAME VARCHAR(10), FIRSTNAME VARCHAR(10))
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN VALUES (P..NAME, P..FIRSTNAME) 

Example 3: Define a table function that returns the employees in a specified department number.

CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
     RETURNS TABLE (EMPNO CHAR(6),
                    LASTNAME VARCHAR(15),
                    FIRSTNAME VARCHAR(12))
     LANGUAGE SQL
     READS SQL DATA
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN
       SELECT EMPNO, LASTNAME, FIRSTNME
         FROM EMPLOYEE
         WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO 

Example 4: Define a scalar function that reverses a string.

CREATE FUNCTION REVERSE(INSTR VARCHAR(4000))
     RETURNS VARCHAR(4000)
     DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
     BEGIN ATOMIC
     DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT '';
     DECLARE LEN INT;
     IF INSTR IS NULL THEN
     RETURN NULL;
     END IF;
     SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR));
     WHILE LEN > 0 DO
     SET (REVSTR, RESTSTR, LEN) 
       = (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR, 
       SUBSTR(RESTSTR, 2, LEN - 1),
       LEN - 1);
     END WHILE;
     RETURN REVSTR;
   END 

4 Example 4: 4 Define the table function from Example 4 with auditing. 4
4

CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
4      RETURNS TABLE (EMPNO CHAR(6),
4                     LASTNAME VARCHAR(15),
4                     FIRSTNAME VARCHAR(12))
4      LANGUAGE SQL
4      MODIFIES SQL DATA
4      NO EXTERNAL ACTION
4      DETERMINISTIC
4      BEGIN ATOMIC
4        INSERT INTO AUDIT 
4        VALUES (USER, 
4                'Table: EMPLOYEE Prd: DEPTNO = ' CONCAT DEPTNO);
4        RETURN
4          SELECT EMPNO, LASTNAME, FIRSTNME
4            FROM EMPLOYEE
4            WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO
4      END 

.........................................................................................................................................................................................................................