SQL processing of DB2 data and user-defined functions

Source: Internet
Author: User
Tags db2 db2 functions scalar

Create and call an SQL statement

SQLProcessIt is the process of compiling the process body with SQL. The process body contains the logic of the SQL process. It can contain variable declaration, conditional processing, flow control statements, and DML. You canCompound statement)Multiple SQL statements are specified. The composite statement combines several statements into an executable block.

When the call is successfulCreate procedure (SQL)Statement, an SQL process is created, which defines the SQL process on the application server. SQL is a simple way to define complex queries or tasks. You can call them as needed.

An easy way to create an SQL statement is to process it in the command line.Program(CLP) scriptCreate procedure (SQL)Statement. For example, if you place the following statement inCreatesqlproc. DB2You can execute this file to create the SQL process:

    1. Connect to the sample database.
    2. Run the following command:
      DB2-Td @-VF createsqlproc. DB2

DB2Command to specify-TDOption flag, which allows the command line handler to use@As the statement termination character (because a semicolon has been used as the statement termination character in the process );-VOption flag for the command line handler to display the command text back to the standard output;-FOption indicates that the command line handler reads the command input from the specified file (instead of the standard input.


Create procedure sales_status (in quota integer, out SQL _state char (5) dynamic result sets 1 Language SQL begin declare sqlstate char (5); declare Rs cursor with return for select sales_person, sum (sales) as total_sales from sales group by sales_person having sum (sales)> quota; open RS; Set SQL _state = sqlstate; end @

This process is called sales_status. It accepts an input parameter.QuotaAnd return the output parameters.SQL _state. There is only one process bodySelectStatement, which returns the name and total sales of the salesperson who has exceeded the specified quota.

Most SQL processes accept at least one input parameter. In our example, the input parameter contains a value (Quota). This value is used inSelectStatement.

Many SQL processes return at least one output parameter. Our example contains an output parameter (SQL _state). This parameter is used to report whether the SQL process is successful or failed. DB2 returnsSqlstateValue, which can be used as the result of an SQL statement. BecauseSqlcodeOrSqlstateThe value belongs to the last SQL statement issued in the process body, and accessing these values will change the subsequent values of these variables (because you need to use SQL statements to access them), you shouldSqlcodeOrSqlstateAssign a value to a locally defined variable and return it through it (for example, in our exampleSQL _stateVariable ).

You can specify zero or more parameters in the SQL process parameter list. Each parameter can be of any of the following three types:

    • InThe parameter passes an input value to the SQL process. This value cannot be modified in the process body.
    • OutThe parameter returns an output value from the SQL process.
    • InoutThe parameter passes an input value to the SQL process and returns an output value from the SQL process.


Zero or more result sets can be returned in the SQL process. In our example, the sales_status process returns a result set. The method to return the result set is:

    1. InDynamic result setsStatement.
    2. Declare a cursor for each returned result set in the process body (UseWith returnClause ).Cursor)Is a named control structure, which is used by an application to point to a specific row in an ordered row set. A cursor is used to retrieve rows from a set of rows.
    3. Open the cursor of each returned result set.
    4. When the SQL process returns, open the cursor.


Variables must be declared at the beginning of the SQL process body. YesStatementA variable should be assigned a unique identifier and specify the SQL data type of the variable. An initial value can also be assigned.

In our SQL process exampleSetClause isFlow ControlClause. In the SQL process body, you can use the following flow control statements, structures, and clauses for conditional processing:

    • CaseThe structure selects an execution path based on the calculation of one or more conditions.
    • ForThe structure executesCodeBlock.
    • Get diagnosticsStatement returns information about the previous SQL statement to an SQL variable.
    • GotoStatement transfers control to a labeled block (one or more statement blocks are identified by a unique SQL name and colon ).
    • IfSelect an execution path based on the condition calculation.ElseifAndElseThe sub-statement allows you to execute branches, or specify the default operations that are performed when other conditions are not met.
    • IterateThe sub-statement passes the flow control to the beginning of a tag loop.
    • LeaveClause causes program control to leave a loop or code block.
    • LoopClause executes a code block multiple timesLeave,IterateOrGotoStatement to exit the loop.
    • RepeatClause repeats a code block until the specified search condition returns true.
    • ReturnThe sub-statement returns control from the SQL process to the caller.
    • SetAssign a value to an output or SQL variable.
    • WhileExecute a code block repeatedly when the specified condition is true.


To successfully create an SQL statement, you must install the DB2 Application Development client on the database server. (For more information about application development client, see the first tutorial in this series .) In the past, the C compiler was used to create the SQL process. This dependency has been eliminated in DB2 Universal Database version 8. All operations previously dependent on the C compiler are currently executed by the bytecode generated by DB2 residing in the virtual machine. For more information about this improvement, see references.

Use SQLCallThe SQL statement is called from the DB2 command line. The called process must be defined in the system catalog. Client Applications written in any supported language can call the SQL process. To call the SQL process sales_status, perform the following steps:

    1. Connect to the sample database.
    2. Issue the following statement:
      DB2 call sales_status (25 ,?)

      Since parentheses have special significance for shell commands on UNIX-based systems, you must add a backslash (\) Characters, or enclose them with double quotation marks:

      DB2 "Call sales_status (25 ,?) "

      If you use the command line Processing Program (CLP) in interactive input modeDB2 =>Enter the prompt), so it does not need to contain double quotation marks.


In this example, value 25 is used as the input parameter.QuotaPass the SQL process and use the question mark (?) As output parameterSQL _state. This process returns the name and total sales volume of each salesperson who has exceeded the specified quota (25. The output example returned by this statement is as follows:

SQL _state: 00000
Sales_person Total_sales
Gounot 50
Lee 91
"Sales_status" return_status: "0"

Back to Top

Create and use SQL user-defined functions

You can create user-defined functions to extend the built-in DB2 functions. For example, you can create a function that computes complex arithmetic expressions or string operations, and then reference these functions in an SQL statement like any existing built-in functions.

Suppose we need a function to return the area of the circle. The input parameter of this function is the radius of the circle. The built-in DB2 function does not have such a function, but you can createUser-Defined SQL scalar functionsTo execute this task, you can reference this function wherever a scalar function is supported in the SQL statement.

Create Function Ca (r double) returns double language SQL contains SQL no external action deterministic return 3.14159 * (R * R );

No external actionClause indicates that this function does not affect the status of objects not managed by the database management program.DeterministicKeyword indicates that this function always returns the same result for a given parameter value. This information is used during query optimization. The easy way to execute this function is to reference it in a query. In the following exampleSysibm. sysdummy1In the cataloguing view (with only one row), execute this query (you can select any query target ):

DB2 select Ca (96.8) as area from sysibm. sysdummy1 area ---------------------- + 2.94374522816000e + 004 1 record (s) selected.

You can also createUser-Defined table functionsIt accepts zero or more input parameters and returns data in the form of a table. Table functions can only be used in SQL statements.FromClause.

Assume that a function is required to return the names and employee numbers of all employees with a specific job. The parameter of the function is the title of the job. The following table function example is used to execute this task:

Create Function jobemployees (job varchar (8) returns table (empno char (6), firstname varchar (12), lastname varchar (15 )) language SQL reads SQL data no external action deterministic return select empno, firstnme, lastname from employee where employee. job = jobemployees. job;

The following query is performed onFromReference the new table function in the clause, and pass the job title 'cler' as the parameter of the function. Keywords required for syntaxAsIntroduce a related name:

 DB2 select * from table (jobemployees ('cler ')) as clerk empno firstname lastname ------ ------------ ------------- 000120 Sean O 'Connell 000230 James Jefferson 000240 Salvatore Marino 000250 Daniel Smith 000260 Sybil Johnson 000270 Maria Perez 6 record (s) selected. 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.