Download Up and Running DB2 on Linux
Transcript
6899ch08.fm
Draft Document for Review April 17, 2008 2:10 pm
Example 8-4 Use the UDF in a select statement
db2inst1@puget:~> db2 "select empno, firstnme, lastname, salary_diff(empno) as
salary_diff from employee where empno in ('000110','000070')"
EMPNO
-----000110
000070
FIRSTNME
-----------VINCENZO
EVA
LASTNAME
SALARY_DIFF
--------------- ----------LUCCHESSI
-25000.00
PULASKI
10000.00
2 record(s) selected.
Note: More information about UDFs can be found in Developing User-defined
Routines (SQL and External), SC23-5855-00.
8.2.3 Stored procedures
A stored procedure helps to reduce unnecessary data transfer over the network
between client and server. The SQL statements and definitions are stored on the
database server. The client sends only the necessary data over the network to
call the stored procedure and gets the results sent back. This helps to improve
the overall performance of client server applications.
Like UDFs, stored procedures are very useful to put common business logic into
a central place and to make it accessible to any client.
The CREATE PROCEDURE DDL statement defines a procedure within an
application server. There are two types of procedures: external procedures,
which are written in a programming language; and SQL procedures, which are
written in SQL. Stored procedures written in other languages than SQL are
handled very similar like UDFs written in other languages. Chapter 8.3.3,
“C/C++” on page 432 and Chapter 8.3.4, “Java” on page 440 discuss UDFs
written in C and Java.
Example 8-5 on page 424 shows an SQL procedure that calculates the new
salary and bonus for an employee. The input is an employee number and rating,
and the result is to update the table EMPLOYEE.
The file basecase.db2 is part of the DB2 samples and can be found in <instance
home>/sqllib/samples/sqlproc.
Note: The CREATE PROCEDURE statement is explained in more detail in
the Information Center in Database fundamentals → SQL → Statements →
CREATE PROCEDURE and in SQL Reference, Volume 2, SC10-4250.
Chapter 8. Application development
423