DB2 SQL stored procedure Syntax "Navigator"

Source: Internet
Author: User
Tags savepoint

This article mainly describes the actual operation steps for correctly creating the DB2 SQL stored PROCEDURE (create procedure (SQL) statement). First, we start with the syntax format, at the same time, there is a description of its syntax. The following is the main content of the article.

The syntax format is as follows:

 
 
  1. >>-CREATE PROCEDURE--procedure-name-----------------------------> 
  2. >--+----------------------------------------------------+--*----> 
  3. ’-(--+------------------------------------------+--)-’  
  4. | .-,------------------------------------. |  
  5. | V .-IN----. | |  
  6. ’---+-------+--parameter-name--data-type-+-’  
  7. +-OUT---+  
  8. ’-INOUT-’  
  9. >--+-------------------------+--*-------------------------------> 
  10. ’-SPECIFIC--specific-name-’  
  11. .-DYNAMIC RESULT SETS 0--------. .-MODIFIES SQL DATA-.  
  12. >--+------------------------------+--*--+-------------------+---> 
  13. ’-DYNAMIC RESULT SETS--integer-’ +-CONTAINS SQL------+  
  14. ’-READS SQL DATA----’  
  15. .-NOT DETERMINISTIC-. .-CALLED ON NULL INPUT-.  
  16. >--*--+-------------------+--*--+----------------------+--*-----> 
  17. ’-DETERMINISTIC-----’  
  18. .-INHERIT SPECIAL REGISTERS-. .-7 OLD SAVEPOINT LEVEL-.  
  19. >--+---------------------------+--*--+---------------------+----> 
  20. ’-7 NEW SAVEPOINT LEVEL-’  
  21. .-LANGUAGE SQL-. .-7 EXTERNAL ACTION----.  
  22. >--7 *--+--------------+--*--+--------------------+--*------------> 
  23. ’-7 NO EXTERNAL ACTION-’  
  24. >--+------------------------------+--3 *--------------------------> 
  25. ’-3 PARAMETER CCSID--+-3 ASCII---+-’  
  26. ’-3 UNICODE-’  
  27. >--| SQL-procedure-body |-------------------------------------->< 
  28. SQL-procedure-body: 
  29. |--SQL-procedure-statement--------------------------------------|  

Syntax description

1. procedure-name: name of a stored procedure. In the same database mode, a stored procedure with the same name and number of parameters cannot exist, it does not work even if the parameter type is different.

2. (IN | OUT | INOUT parameter-name data-type,...): input parameter

IN: input parameters

OUT: Output Parameters

INOUT: Used as input and output parameters

Parameter-name: the parameter name, which is a unique identifier in this stored procedure.

Data-type: parameter type, which can receive SQL types and created tables. Long varchar, long vargraphic, DATALINK, REFERENCE, and user-defined types are not supported.

3. SPECIFIC specific-name: the unique name (alias), which can be replaced by the stored procedure name. This SPECIFIC name is used for the dorp stored procedure, or you can add a View to the stored procedure.

But cannot call the stored procedure. If this parameter is not specified, the database automatically generates a name for the yymmddhhmmsshhn timestamp. Aliases are recommended.

4. dynamic result sets integer: specify the maximum number of results returned by the stored procedure. Although there is no return statement in the stored procedure, it can return the result set.

5. contains SQL, reads SQL data, and modifies SQL data: Specifies the SQL access level in the stored procedure.

Contains SQL: indicates that the stored procedure can be executed. Neither SQL data can be read nor SQL data can be modified.

Reads SQL data: indicates that the stored procedure can be executed and the SQL statement can be read, but the SQL DATA cannot be modified.

Modifies SQL data: indicates that the stored procedure can execute all SQL statements. Allows you to add, delete, and modify data in a database.

6. DETERMINISTIC or not deterministic: indicates that the stored procedure is dynamic or non-dynamic. The value returned dynamically is uncertain. The values returned for each non-dynamic stored procedure execution are the same.

7. called on null input: indicates that the stored procedure can be CALLED, regardless of whether all INPUT parameters are NULL, and all OUT or INOUT parameters can return a NULL or non-NULL value. Check whether the parameter is NULL.

8. inherit special registers: indicates that special registers are inherited.

9. old savepoint level or new savepoint level: Create a storage point. The old savepoint level is the default storage point.

10. language SQL: specifies that the Program subject uses the SQL LANGUAGE.

11. external action or no external action: indicates whether the stored procedure executes activities that modify the database status, rather than using the database manager. The default value is

External action. If no external action is specified, the database determines the Best optimization solution.

12. parameter ccsid: Specify the encoding of all output string data. By default, the UNICODE encoding database is parameter ccsid unicode, and other databases are parameter ccsid 3 ASCII by default.

13. SQL-procedure-body: body of the stored procedure

Example 1: generate a DB2 SQL stored procedure and return the average employee salary. returns the amount of employees who have exceeded the average salary. The result set includes the name, position, and salary fields (see the example database sample for db2 ).

 
 
  1. CREATE PROCEDURE MEDIAN_RESULT_SET   
  2. (  
  3. OUT medianSalary DOUBLE  
  4. )   
  5. RESULT SETS 1   
  6. LANGUAGE SQL   
  7. BEGIN   
  8. DECLARE v_numRecords INT DEFAULT 1;   
  9. DECLARE v_counter INT DEFAULT 0;   
  10. DECLARE c1 CURSOR FOR   
  11. SELECT CAST(salary AS DOUBLE)   
  12. FROM staff   
  13. ORDER BY salary;   
  14. DECLARE c2 CURSOR WITH RETURN FOR   
  15. SELECT name, job, CAST(salary AS INTEGER)   
  16. FROM staff   
  17. WHERE salary > medianSalary   
  18. ORDER BY salary;   
  19. DECLARE EXIT HANDLER FOR NOT FOUND   
  20. SET medianSalary = 6666;   
  21. SET medianSalary = 0;   
  22. SELECT COUNT(*) INTO v_numRecords   
  23. FROM STAFF;   
  24. OPEN c1;   
  25. WHILE v_counter < (v_numRecords / 2 + 1)   
  26. DO   
  27. FETCH c1 INTO medianSalary;   
  28. SET v_counterv_counter = v_counter + 1;   
  29. END WHILE;   
  30. CLOSE c1;   
  31. OPEN c2;   
  32. END  

The above content is an introduction to the DB2 SQL stored procedure syntax official authoritative guide, I hope you can gain some benefits.

Related Article

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.