acme1921209
- 浏览: 381165 次
-
文章分类
最新评论
-
小尜：
写的很详细，也很简单，一看就懂。多谢啦。
关于工厂模式和spring的IOC -
fjjiaboming：
代码排版!!!!!
JDBC事务和JTA （XA）事务 -
fjjiaboming：
排版有问题吗? 望交流.
做人有三个层次 -
fjjiaboming：
看来数据库连接池 必须用JNDI 注册在容器 , 对吗?
JDBC事务和JTA （XA）事务 -
fd1803：
SSL工作原理：http://www.wosign.com/B ...
SSL(Server Socket Layer)简介
这个还不错，网上这个文章太多了 
：
背景：DB2的数据库性能很牛X，但是其文档却很差，尤其是开发参考文档，都是英文的，浏览的时候还很不好找，需要上IBM的网站看，网站也出奇的慢，极不方便，让开发人员举步维艰，这也许是IBM DB2的用户少，书少，资料少的原因。本人为了搞明白DB2存SQL的完整语法介绍，找遍了WWW，没有发现一篇完整介绍DB2语法的文章，现我根据自己的理解，将IBM对DB2 SQL存储过程的英文翻译下来，与各位网友进行交流，由于时间的限制，没有一字一句的去翻译，只对最核心的语法说明做了简单的翻译工作。我的E文也很差，错误在所难免，欢迎各位给予斧正！
译著
－－－－－－－－－－－－－－－－－－
创建SQL存储过程（CREATE PROCEDURE (SQL) statement ）
语法格式如下：
>>-CREATE PROCEDURE--procedure-name----------------------------->
>--+----------------------------------------------------+--*---->
'-(--+------------------------------------------+--)-'
| .-,------------------------------------. |
| V .-IN----. | |
'---+-------+--parameter-name--data-type-+-'
+-OUT---+
'-INOUT-'
'-(--+------------------------------------------+--)-'
| .-,------------------------------------. |
| V .-IN----. | |
'---+-------+--parameter-name--data-type-+-'
+-OUT---+
'-INOUT-'
>--+-------------------------+--*------------------------------->
'-SPECIFIC--specific-name-'
'-SPECIFIC--specific-name-'
.-DYNAMIC RESULT SETS 0--------. .-MODIFIES SQL DATA-.
>--+------------------------------+--*--+-------------------+--->
'-DYNAMIC RESULT SETS--integer-' +-CONTAINS SQL------+
'-READS SQL DATA----'
>--+------------------------------+--*--+-------------------+--->
'-DYNAMIC RESULT SETS--integer-' +-CONTAINS SQL------+
'-READS SQL DATA----'
.-NOT DETERMINISTIC-. .-CALLED ON NULL INPUT-.
>--*--+-------------------+--*--+----------------------+--*----->
'-DETERMINISTIC-----'
>--*--+-------------------+--*--+----------------------+--*----->
'-DETERMINISTIC-----'
.-INHERIT SPECIAL REGISTERS-. .-7 OLD SAVEPOINT LEVEL-.
>--+---------------------------+--*--+---------------------+---->
'-7 NEW SAVEPOINT LEVEL-'
>--+---------------------------+--*--+---------------------+---->
'-7 NEW SAVEPOINT LEVEL-'
.-LANGUAGE SQL-. .-7 EXTERNAL ACTION----.
>--7 *--+--------------+--*--+--------------------+--*------------>
'-7 NO EXTERNAL ACTION-'
>--7 *--+--------------+--*--+--------------------+--*------------>
'-7 NO EXTERNAL ACTION-'
>--+------------------------------+--3 *-------------------------->
'-3 PARAMETER CCSID--+-3 ASCII---+-'
'-3 UNICODE-'
'-3 PARAMETER CCSID--+-3 ASCII---+-'
'-3 UNICODE-'
>--| SQL-procedure-body |--------------------------------------><
SQL-procedure-body:
|--SQL-procedure-statement--------------------------------------|
语法说明
1、procedure-name: 存储过程的名字，在同一个数据库的同一模式下，不能存在存储过程名相同参数数目相同的存储过程，即使参数的类型不同也不行。
2、(IN | OUT | INOUT parameter-name data-type,...) ：传入参数
IN：输入参数
OUT：输出参数
INOUT：作为输入输出参数
parameter-name：参数名字，在此存储过程中唯一的标识符。
data-type:参数类型，可以接收SQL类型和创建的表。不支持LONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCE和用户自定义类型。
IN：输入参数
OUT：输出参数
INOUT：作为输入输出参数
parameter-name：参数名字，在此存储过程中唯一的标识符。
data-type:参数类型，可以接收SQL类型和创建的表。不支持LONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCE和用户自定义类型。
3、SPECIFIC specific-name：唯一的特定名称（别名），可以用存储过程名代替，这个特定名称用于dorp存储过程，或者给存储过程添加注视用，但不能调用存储过程。如果不指定，则数据库会自动生成一个yymmddhhmmsshhn时间戳的名字。推荐给出别名。
4、DYNAMIC RESULT SETS integer：指定存储过程返回结果的最大数量。存储过程中虽然没有return语句，但是却能返回结果集。
5、CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA： 指定存储过程中的SQL访问级别
CONTAINS SQL： 表示存储过程可以执行中，既不可读取 SQL 数据，也不可修改 SQL 数据。
READS SQL DATA： 表示存储过程可以执行中，可读取SQL，但不可修改 SQL 数据。
MODIFIES SQL DATA： 表示存储过程可以执行任何 SQL 语句。可以对数据库中的数据进行增加、删除和修改。
CONTAINS SQL： 表示存储过程可以执行中，既不可读取 SQL 数据，也不可修改 SQL 数据。
READS SQL DATA： 表示存储过程可以执行中，可读取SQL，但不可修改 SQL 数据。
MODIFIES SQL DATA： 表示存储过程可以执行任何 SQL 语句。可以对数据库中的数据进行增加、删除和修改。
6、DETERMINISTIC or NOT DETERMINISTIC：表示存储过程是动态或者非动态的。动态的返回的值是不确定的。非动态的存储过程每次执行返回的值是相同的。
7、CALLED ON NULL INPUT：表示可以调用存储过程而不管任何的输入参数是否为NULL，并且，任何的OUT或者INOUT参数可以返回一个NULL或者非空值。检验参数是否为NULL是在过程中进行的。
8、INHERIT SPECIAL REGISTERS：表示继承专用寄存器。
9、OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL：建立存储点。OLD SAVEPOINT LEVEL是默认的存储点。
10、LANGUAGE SQL：指定程序的主体用的是SQL语言。
11、EXTERNAL ACTION or NO EXTERNAL ACTION：表示存储过程是否执行一些改变理数据库状态的活动，而不通过数据库管理器管。默认是 EXTERNAL ACTION。如果指定为NO EXTERNAL ACTION ,则数据库会确定最最佳优化方案。
12、PARAMETER CCSID：指定所有输出字符串数据的编码,默认为UNICODE编码数据库为PARAMETER CCSID UNICODE ，其他的数据库默认为PARAMETER CCSID 3 ASCII。
13、SQL-procedure-body：存储过程的主体
例子1：产生一个SQL存储过程，返回员工的平均薪水. 返回所有员工超过平均薪水的数额，结果集包括name, position, and salary字段（参考数据库为db2的示例数据库sample）。
CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM staff
ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > medianSalary
ORDER BY salary;
SELECT CAST(salary AS DOUBLE)
FROM staff
ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > medianSalary
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords
FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1)
DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;
END
SELECT COUNT(*) INTO v_numRecords
FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1)
DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;
END
－－－－－－－－－－－－－－－－－－－－
原文如下：
CREATE PROCEDURE (SQL) statement
The CREATE PROCEDURE (SQL) statement defines an SQL procedure at the current server.
The CREATE PROCEDURE (SQL) statement defines an SQL procedure at the current server.
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).
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:
The privileges held by the authorization ID of the statement must include at least one of the following:
2 BINDADD privilege on the database, and one of the 2 following: 2 2 2 IMPLICIT_SCHEMA privilege on the database, if the implicit 2 or explicit schema name of the procedure does not exist 2 CREATEIN privilege on the schema, if the schema name of the 2 procedure refers to an existing schema
SYSADM or DBADM authority
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 procedure body.
SYSADM or DBADM authority
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 procedure body.
Syntax
>>-CREATE PROCEDURE--procedure-name----------------------------->
>>-CREATE PROCEDURE--procedure-name----------------------------->
>--+----------------------------------------------------+--*---->
'-(--+------------------------------------------+--)-'
| .-,------------------------------------. |
| V .-IN----. | |
'---+-------+--parameter-name--data-type-+-'
+-OUT---+
'-INOUT-'
'-(--+------------------------------------------+--)-'
| .-,------------------------------------. |
| V .-IN----. | |
'---+-------+--parameter-name--data-type-+-'
+-OUT---+
'-INOUT-'
>--+-------------------------+--*------------------------------->
'-SPECIFIC--specific-name-'
'-SPECIFIC--specific-name-'
.-DYNAMIC RESULT SETS 0--------. .-MODIFIES SQL DATA-.
>--+------------------------------+--*--+-------------------+--->
'-DYNAMIC RESULT SETS--integer-' +-CONTAINS SQL------+
'-READS SQL DATA----'
>--+------------------------------+--*--+-------------------+--->
'-DYNAMIC RESULT SETS--integer-' +-CONTAINS SQL------+
'-READS SQL DATA----'
.-NOT DETERMINISTIC-. .-CALLED ON NULL INPUT-.
>--*--+-------------------+--*--+----------------------+--*----->
'-DETERMINISTIC-----'
>--*--+-------------------+--*--+----------------------+--*----->
'-DETERMINISTIC-----'
.-INHERIT SPECIAL REGISTERS-. .-7 OLD SAVEPOINT LEVEL-.
>--+---------------------------+--*--+---------------------+---->
'-7 NEW SAVEPOINT LEVEL-'
>--+---------------------------+--*--+---------------------+---->
'-7 NEW SAVEPOINT LEVEL-'
.-LANGUAGE SQL-. .-7 EXTERNAL ACTION----.
>--7 *--+--------------+--*--+--------------------+--*------------>
'-7 NO EXTERNAL ACTION-'
>--7 *--+--------------+--*--+--------------------+--*------------>
'-7 NO EXTERNAL ACTION-'
>--+------------------------------+--3 *-------------------------->
'-3 PARAMETER CCSID--+-3 ASCII---+-'
'-3 UNICODE-'
'-3 PARAMETER CCSID--+-3 ASCII---+-'
'-3 UNICODE-'
>--| SQL-procedure-body |--------------------------------------><
SQL-procedure-body:
|--SQL-procedure-statement--------------------------------------|
Description
procedure-name
Names the procedure being defined. It is a qualified or unqualified name that designates a procedure. The unqualified form of procedure-name is an SQL identifier (with a maximum length of 128). 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, must not identify a procedure described in the catalog (SQLSTATE 42723). The unqualified name, together with the number of parameters, is unique within its schema, but does not need to be unique across schemas.
If a two-part name is specified, the schema-name cannot begin with 'SYS'; otherwise, an error is returned (SQLSTATE 42939).
(IN | OUT | INOUT parameter-name data-type,...)
Identifies the parameters of the procedure, and specifies the mode, name, and data type of each parameter. One entry in the list must be specified for each parameter that the procedure will expect.
It is possible to register a procedure that has no parameters. In this case, the parentheses must still be coded, with no intervening data types. For example:
Identifies the parameters of the procedure, and specifies the mode, name, and data type of each parameter. One entry in the list must be specified for each parameter that the procedure will expect.
It is possible to register a procedure that has no parameters. In this case, the parentheses must still be coded, with no intervening data types. For example:
CREATE PROCEDURE SUBWOOFER() ...
No two identically-named procedures within a schema are permitted to have exactly the same number of parameters. A duplicate signature raises an SQL error (SQLSTATE 42723).
No two identically-named procedures within a schema are permitted to have exactly the same number of parameters. A duplicate signature raises an SQL error (SQLSTATE 42723).
For example, given the statements:
CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) ...
CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ...
the second statement will fail because the number of parameters in the procedure is the same, even if the data types are not.
CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ...
the second statement will fail because the number of parameters in the procedure is the same, even if the data types are not.
IN | OUT | INOUT
Specifies the mode of the parameter.
If an error is returned by the procedure, OUT parameters are undefined and INOUT parameters are unchanged.
Specifies the mode of the parameter.
If an error is returned by the procedure, OUT parameters are undefined and INOUT parameters are unchanged.
IN
Identifies the parameter as an input parameter to the procedure. Any changes made to the parameter within the procedure are not available to the calling SQL application when control is returned. The default is IN.
OUT
Identifies the parameter as an output parameter for the procedure.
INOUT
Identifies the parameter as both an input and output parameter for the procedure.
parameter-name
Specifies the name of the parameter. The parameter name must be unique for the procedure (SQLSTATE 42734).
data-type
Specifies the data type of the parameter.
SQL data type specifications and abbreviations that can be specified in the data-type definition of a CREATE TABLE statement, and that have a correspondence in the language that is being used to write the procedure, may be specified.
1 LONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCE, and 1 user-defined structured types are not supported (SQLSTATE 429BB).
SPECIFIC specific-name
Provides a unique name for the instance of the procedure that is being defined. This specific name can be used when dropping the procedure or commenting on the procedure. It can never be used to invoke the procedure. 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 procedure instance that exists at the application server; otherwise an error (SQLSTATE 42710) is raised.
The specific-name can be the same as an existing procedure-name.
Identifies the parameter as an input parameter to the procedure. Any changes made to the parameter within the procedure are not available to the calling SQL application when control is returned. The default is IN.
OUT
Identifies the parameter as an output parameter for the procedure.
INOUT
Identifies the parameter as both an input and output parameter for the procedure.
parameter-name
Specifies the name of the parameter. The parameter name must be unique for the procedure (SQLSTATE 42734).
data-type
Specifies the data type of the parameter.
SQL data type specifications and abbreviations that can be specified in the data-type definition of a CREATE TABLE statement, and that have a correspondence in the language that is being used to write the procedure, may be specified.
1 LONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCE, and 1 user-defined structured types are not supported (SQLSTATE 429BB).
SPECIFIC specific-name
Provides a unique name for the instance of the procedure that is being defined. This specific name can be used when dropping the procedure or commenting on the procedure. It can never be used to invoke the procedure. 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 procedure instance that exists at the application server; otherwise an error (SQLSTATE 42710) is raised.
The specific-name can be the same as an existing procedure-name.
If no qualifier is specified, the qualifier that was used for procedure-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier for procedure-name, or an error (SQLSTATE 42882) is raised.
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, SQLyymmddhhmmsshhn.
DYNAMIC RESULT SETS integer
Indicates the estimated upper bound of returned result sets for the procedure.
CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA
Indicates the level of data access for SQL statements included in the procedure.
CONTAINS SQL
Indicates that SQL statements that neither read nor modify SQL data can be executed by the procedure (SQLSTATE 38004 or 42985). Statements that are not supported in any procedure return a different error (SQLSTATE 38003 or 42985).
READS SQL DATA
Indicates that some SQL statements that do not modify SQL data can be included in the procedure (SQLSTATE 38002 or 42985). Statements that are not supported in any procedure return a different error (SQLSTATE 38003 or 42985).
MODIFIES SQL DATA
Indicates that the procedure can execute any SQL statement except statements that are not supported in procedures (SQLSTATE 38003 or 42985).
DETERMINISTIC or NOT DETERMINISTIC
This clause specifies whether the procedure always returns the same results for given argument values (DETERMINISTIC) or whether the procedure depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC procedure must always return the same result from successive invocations with identical inputs.
This clause currently does not impact processing of the procedure.
Indicates the estimated upper bound of returned result sets for the procedure.
CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA
Indicates the level of data access for SQL statements included in the procedure.
CONTAINS SQL
Indicates that SQL statements that neither read nor modify SQL data can be executed by the procedure (SQLSTATE 38004 or 42985). Statements that are not supported in any procedure return a different error (SQLSTATE 38003 or 42985).
READS SQL DATA
Indicates that some SQL statements that do not modify SQL data can be included in the procedure (SQLSTATE 38002 or 42985). Statements that are not supported in any procedure return a different error (SQLSTATE 38003 or 42985).
MODIFIES SQL DATA
Indicates that the procedure can execute any SQL statement except statements that are not supported in procedures (SQLSTATE 38003 or 42985).
DETERMINISTIC or NOT DETERMINISTIC
This clause specifies whether the procedure always returns the same results for given argument values (DETERMINISTIC) or whether the procedure depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC procedure must always return the same result from successive invocations with identical inputs.
This clause currently does not impact processing of the procedure.
CALLED ON NULL INPUT
CALLED ON NULL INPUT always applies to procedures. This means that the procedure is called regardless of whether any arguments are null. Any OUT or INOUT parameter can return a null value or a normal (non-null) value. Responsibility for testing for null argument values lies with the procedure.
INHERIT SPECIAL REGISTERS
This optional clause specifies that updatable special registers in the procedure will inherit their initial values from the environment of the invoking statement. For a routine invoked in a nested object (for example a trigger or view), the initial values are inherited from the runtime environment (not inherited from the object definition).
No changes to the special registers are passed back to the caller of the procedure.
CALLED ON NULL INPUT always applies to procedures. This means that the procedure is called regardless of whether any arguments are null. Any OUT or INOUT parameter can return a null value or a normal (non-null) value. Responsibility for testing for null argument values lies with the procedure.
INHERIT SPECIAL REGISTERS
This optional clause specifies that updatable special registers in the procedure will inherit their initial values from the environment of the invoking statement. For a routine invoked in a nested object (for example a trigger or view), the initial values are inherited from the runtime environment (not inherited from the object definition).
No changes to the special registers are passed back to the caller of the procedure.
Non-updatable special registers, such as the datetime special registers, reflect a property of the statement currently executing, and are therefore set to their default values.
7 7 OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL 7 Specifies whether or not this procedure establishes a 7 new savepoint level for savepoint names and effects. 7 OLD SAVEPOINT LEVEL is the default behavior. 7 For more information about savepoint levels, see the "Rules" 7 section in the description of the SAVEPOINT statement. 7 LANGUAGE SQL
This clause is used to specify that the procedure body is written in the SQL language. 7 7 EXTERNAL ACTION or NO EXTERNAL ACTION 7 Specifies whether the procedure takes some action that changes 7 the state of an object not managed by the database manager (EXTERNAL 7 ACTION), or not (NO EXTERNAL ACTION). 7 The default is EXTERNAL ACTION. 7 If NO EXTERNAL ACTION is specified, the system can use certain 7 optimizations that assume the procedure has no external impact. 7 3 3 PARAMETER CCSID 3 Specifies the encoding scheme to use for all string data 3 passed into and out of the procedure. 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 SQL-procedure-body
Specifies the SQL statement that is the body of the SQL procedure. Multiple SQL-procedure-statements can be specified within a procedure-compound-statement. See SQL-procedure-statement in the description of the Compound SQL (Procedure) statement.
7 7 Rules 7 7 A procedure that is called from within a dynamic compound 7 statement will execute as if it were created specifying NEW SAVEPOINT 7 LEVEL, even if OLD SAVEPOINT LEVEL was specified or defaulted to when 7 the procedure was created.
Notes
Creating a procedure with a schema name that does not already exist will result in the implicit creation of that schema, provided that the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
Privileges
The definer of a procedure always receives the EXECUTE privilege WITH GRANT OPTION on the procedure, as well as the right to drop the procedure.
This clause is used to specify that the procedure body is written in the SQL language. 7 7 EXTERNAL ACTION or NO EXTERNAL ACTION 7 Specifies whether the procedure takes some action that changes 7 the state of an object not managed by the database manager (EXTERNAL 7 ACTION), or not (NO EXTERNAL ACTION). 7 The default is EXTERNAL ACTION. 7 If NO EXTERNAL ACTION is specified, the system can use certain 7 optimizations that assume the procedure has no external impact. 7 3 3 PARAMETER CCSID 3 Specifies the encoding scheme to use for all string data 3 passed into and out of the procedure. 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 SQL-procedure-body
Specifies the SQL statement that is the body of the SQL procedure. Multiple SQL-procedure-statements can be specified within a procedure-compound-statement. See SQL-procedure-statement in the description of the Compound SQL (Procedure) statement.
7 7 Rules 7 7 A procedure that is called from within a dynamic compound 7 statement will execute as if it were created specifying NEW SAVEPOINT 7 LEVEL, even if OLD SAVEPOINT LEVEL was specified or defaulted to when 7 the procedure was created.
Notes
Creating a procedure with a schema name that does not already exist will result in the implicit creation of that schema, provided that the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
Privileges
The definer of a procedure always receives the EXECUTE privilege WITH GRANT OPTION on the procedure, as well as the right to drop the procedure.
Compatibilities
For compatibility with DB2 UDB for OS/390 and z/OS:
The following syntax is accepted as the default behavior:
ASUTIME NO LIMIT
COMMIT ON RETURN NO
NO COLLID
STAY RESIDENT NO
For compatibility with previous versions of DB2:
RESULT SETS can be specified in place of DYNAMIC RESULT SETS.
NULL CALL can be specified in place of CALLED ON NULL INPUT.
Examples
Example 1: Create an SQL procedure that returns the median staff salary. Return a result set containing the name, position, and salary of all employees who earn more than the median salary.
For compatibility with DB2 UDB for OS/390 and z/OS:
The following syntax is accepted as the default behavior:
ASUTIME NO LIMIT
COMMIT ON RETURN NO
NO COLLID
STAY RESIDENT NO
For compatibility with previous versions of DB2:
RESULT SETS can be specified in place of DYNAMIC RESULT SETS.
NULL CALL can be specified in place of CALLED ON NULL INPUT.
Examples
Example 1: Create an SQL procedure that returns the median staff salary. Return a result set containing the name, position, and salary of all employees who earn more than the median salary.
CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM staff
ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > medianSalary
ORDER BY salary;
SELECT CAST(salary AS DOUBLE)
FROM staff
ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > medianSalary
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords
FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1)
DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;
END
Related reference
SAVEPOINT statement
Compound SQL (Procedure) statement
SQL statements allowed in routines
Special registers
Related samples
SQL procedures
basecase.db2 -- To create the UPDATE_SALARY SQL procedure
nestcase.db2 -- To create the BUMP_SALARY SQL procedure
nestedsp.db2 -- To create the OUT_AVERAGE, OUT_MEDIAN and MAX_SALARY SQL procedures
rsultset.db2 -- To register and create the MEDIAN_RESULT_SET SQL procedure
This topic can be found in: SQL Reference, Volume 2.
SELECT COUNT(*) INTO v_numRecords
FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1)
DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;
END
Related reference
SAVEPOINT statement
Compound SQL (Procedure) statement
SQL statements allowed in routines
Special registers
Related samples
SQL procedures
basecase.db2 -- To create the UPDATE_SALARY SQL procedure
nestcase.db2 -- To create the BUMP_SALARY SQL procedure
nestedsp.db2 -- To create the OUT_AVERAGE, OUT_MEDIAN and MAX_SALARY SQL procedures
rsultset.db2 -- To register and create the MEDIAN_RESULT_SET SQL procedure
This topic can be found in: SQL Reference, Volume 2.
分享到：
发表评论
-
考试成绩
2011-03-19 11:56 0uuuuuuuu -
db2cat 命令
2009-06-24 21:53 1935今天无意看到一个命令：db2cat(系统目录分析). 现把测 ... -
关于db2分区特性的一点心得
2008-10-29 00:31 2395最近了解了下db2的分区特性，发现不是想象中的那样强大，而且 ... -
痛饮咖啡，熟读手册，方可为DBA
2008-10-25 16:09 1207嫦娥上天了。为了庆祝 ... -
关于db2diag.log里面ZRC和ecf的说明
2008-09-28 16:41 4915今天同事给了个db2diag。log文件给分析下 ，对其中的e ... -
关于db2dart dbname /dhwm /tsi 的测试
2008-08-08 01:25 1870下面是db2dart database /dhwm /tsi ... -
db2 初级证书:730.731
2008-07-01 21:29 1303一个偶然的机会,获得了 这2个证书.要学的很还多 db os ... -
关于db2的OLAP的一些函数
2007-07-21 17:00 1384看到人家说OLAP函数的强大，到网上搜了一般。看了下面2个文章 ... -
db2学习笔记（二）
2007-07-15 21:22 2113表空间方面： 1.创建数据库的时候，默认会有3个页大小为4k的 ... -
创新性应用-王涛 （关于db2）转载
2007-07-14 15:09 1234原文： http://blog.csdn.net/best_d ... -
DB2资料(REDBOOK) 转载
2007-07-11 15:16 2428原文为：http://bbs.chinaunix.net/ar ... -
不管怎么强调sortheap的重要性都不为过
2007-07-10 00:14 2787今天看了一篇文章，觉得不管怎么强调sortheap的重要性都不 ... -
db2学习代码例子（代码为转载网上）
2007-07-05 14:31 2194搞db2也2年了，一直都没搞过存储过程，最近想学下，到网上 ... -
DB2 存储过程开发最佳实践（转载）
2007-07-04 17:00 6215这个文章对初学者理解 ... -
java调用db2存储过程例子（新手用，熟悉的就不用看了）
2007-07-04 14:15 10913搜索java 调用db2（版本为8.2）存储过程 没几个文章能 ... -
最近db2学习笔记（06.29--07.03）
2007-07-03 20:03 4139有点乱 ，我认为重要的 ... -
关于DB2数据库的ADM11003E和ADM0501C错误的处理(转载)
2007-06-28 10:55 3921补充说明：对于第一个错误，如果你确信你的系统没有使用存储过 ... -
JDBC的隔离级别研究
2007-04-03 18:37 2304在通过JDBC对数据库进行并发访问时，为了解决并发之间的锁的控 ... -
在JBoss中配置DB2的数据源
2007-03-30 17:34 2405在用myeclipse+db2学习hibernate时，老报 ... -
编写高性能的mysql语法
2007-03-27 15:18 1203...
相关推荐
DB2 SQL存储过程语法官方权威指南,介绍DB2中存储过程的基本语法
DB2 SQL存储过程语法官方权威指南(翻译)
IBM DB2 SQL存储过程语法官方权威指南，初学者的查阅手册。
DB2+SQL存储过程语法官方权威指南，打好基础，从基本学起
db2 存储过程 db2 存储过程 db2 存储过程
db2存储过程、触发器等指南
DB2 存储过程 SQL DB2 SQL存储过程基础 SQL
DB2存储过程语法大全 存储过程及其类型 动态SQL语句
DB2 sql 存储过程基础,希望有更多的DB2存储过程资料共享
DB2 SQL 语法 速查
DB2 存储过程的基本语法，简单 易学。
DB2存储过程开发权威指南．存储过程学习速成教程
IBM Informix SQL 指南：语法 版本9.4 sql语法学习指南，Ibm官方用书
DB2中SQL语句的简单用法，对于初学者的作用还是很明显的，可以了解到DB2中SQL语句的基本用法。
DB2存储过程标准语法格式及各参数的详细介绍(附实例) [内容来自互联网]
通过学习DB2的SQL大全，可以掌握DB2数据库相关函数用法。
学习DB2基础知识的好材料，是熟练掌握SQL语法的必备
讲述了DB2的SQL语法，函数功能，语句块等。