怎么样在DB27.2中写存储过程,使用纯SQL就行了.(200分)

小弟第一次使用DB2的,怎么在DB2中写存储过程,
我知道可以在STORE PROC BUILDER中写,
但是就是出现错误提示:[IBM][CLI Driver][DB2/NT] SQL0901N SQL 语句因为不
严重的系统错误而失败。可处理后续的 SQL 语句。
（原因为 "Prepcompilation/Compilation failed"。） SQLSTATE=58004

我是使用SQL的向导进行的,请大家告诉一下步骤最好了.

请大家指点一下,分好说

DB2.....没用过 ........

你问一下，BLUERAIN，他就是写DB2的
不过他好象不怎么上来

DB2的信息中心里就有详细说明，很简单，你看看：
CREATE TRIGGER

The CREATE TRIGGER statement defines a trigger in the database.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement when the trigger is created must include at least one of the following:

SYSADM or DBADM authority.
ALTER privilege on the table on which the trigger is defined, or ALTERIN privilege on the schema of the table on which the trigger is defined and one of:
IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the trigger does not exist
CREATEIN privilege on the schema, if the schema name of the trigger refers to an existing schema.
If the authorization ID of the statement does not have SYSADM or DBADM authority, the privileges that the authorization ID of the statement holds (without considering PUBLIC or group privileges) must include all of the following as long as the trigger exists:

SELECT privilege on the table on which the trigger is defined, if any transition variables or tables are specified
SELECT privilege on any table or view referenced in the triggered action condition
Necessary privileges to invoke the triggered SQL statements specified.
If a trigger definer can only create the trigger because the definer has SYSADM authority, then the definer is granted explicit DBADM authority for the purpose of creating the trigger.

Syntax

>>-CREATE TRIGGER--trigger-name----+-NO CASCADE BEFORE-+-------->
'-AFTER-------------'

>-----+-INSERT-----------------------------+--ON--table-name---->
+-DELETE-----------------------------+
'-UPDATE--+------------------------+-'
| .-,--------------. |
| V | |
'-OF----column-name---+--'

>-----+----------------------------------------------------------------------+>
| .----------------------------------------------------. |
| V (1) (2) .-AS-. | |
'-REFERENCING-------------------+-OLD--+----+--correlation-name--+--+--'
| .-AS-. |
+-NEW-+----+--correlation-name---+
| .-AS-. |
+-OLD_TABLE-+----+--identifier---+
| .-AS-. |
'-NEW_TABLE-+----+--identifier---'

>-----+-FOR EACH ROW---------------+--MODE DB2SQL--------------->
| (3) |
'--------FOR EACH STATEMENT--'

>-----| triggered-action |-------------------------------------><

triggered-action

|--+-------------------------------+---------------------------->
'-WHEN--(--search-condition--)--'

>-----+-triggered-SQL-statement--------------------------------+-|
| .-------------------------------. |
| V | |
'-BEGIN ATOMIC-----triggered-SQL-statement--;---+---END--'



Notes:


OLD and NEW may only be specified once each.

OLD_TABLE and NEW_TABLE may only be specified once each and only for AFTER triggers.

FOR EACH STATEMENT may not be specified for BEFORE triggers.

Description


trigger-name
Names the trigger. The name, including the implicit or explicit schema name must not identify a trigger already described in the catalog (SQLSTATE 42710). If a two part name is specified, the schema name cannot begin with "SYS" (SQLSTATE 42939).

NO CASCADE BEFORE
Specifies that the associated triggered action is to be applied before any changes caused by the actual update of the subject table are applied to the database. It also specifies that the triggered action of the trigger will not cause other triggers to be activated.

AFTER
Specifies that the associated triggered action is to be applied after the changes caused by the actual update of the subject table are applied to the database.

INSERT
Specifies that the triggered action associated with the trigger is to be executed whenever an INSERT operation is applied to the designated base table.

DELETE
Specifies that the triggered action associated with the trigger is to be executed whenever a DELETE operation is applied to the designated base table.

UPDATE
Specifies that the triggered action associated with the trigger is to be executed whenever an UPDATE operation is applied to the designated base table subject to the columns specified or implied.
If the optional column-name list is not specified, every column of the table is implied. Therefore, omission of the column-name list implies that the trigger will be activated by the update of any column of the table.


OF column-name,...
Each column-name specified must be a column of the base table (SQLSTATE 42703). If the trigger is a BEFORE trigger, the column-name specified may not be a generated column other than the identity column (SQLSTATE 42989). No column-name shall appear more than once in the column-name list (SQLSTATE 42711). The trigger will only be activated by the update of a column identified in the column-name list.

ON table-name
Designates the subject table of the trigger definition. The name must specify a base table or an alias that resolves to a base table (SQLSTATE 42809). The name must not specify a catalog table (SQLSTATE 42832), a summary table (SQLSTATE 42997), a declared temporary table (SQLSTATE 42995), or a nickname (SQLSTATE 42809).

REFERENCING
Specifies the correlation names for the transition variables and the table names for the transition tables. Correlation names identify a specific row in the set of rows affected by the triggering SQL operation. Table names identify the complete set of affected rows. Each row affected by the triggering SQL operation is available to the triggered action by qualifying columns with correlation-names specified as follows.

OLD AS correlation-name
Specifies a correlation name which identifies the row state prior to the triggering SQL operation.

NEW AS correlation-name
Specifies a correlation name which identifies the row state as modified by the triggering SQL operation and by any SET statement in a BEFORE trigger that has already executed.
The complete set of rows affected by the triggering SQL operation is available to the triggered action by using a temporary table name specified as follows.


OLD_TABLE AS identifier
Specifies a temporary table name which identifies the set of affected rows prior to the triggering SQL operation.

NEW_TABLE AS identifier
Specifies a temporary table name which identifies the affected rows as modified by the triggering SQL operation and by any SET statement in a BEFORE trigger that has already executed.
The following rules apply to the REFERENCING clause:

None of the OLD and NEW correlation names and the OLD_TABLE and NEW_TABLE names can be identical (SQLSTATE 42712).
Only one OLD and one NEW correlation-name may be specified for a trigger (SQLSTATE 42613).
Only one OLD_TABLE and one NEW_TABLE identifier may be specified for a trigger (SQLSTATE 42613).
The OLD correlation-name and the OLD_TABLE identifier can only be used if the trigger event is either a DELETE operation or an UPDATE operation (SQLSTATE 42898). If the operation is a DELETE operation, OLD correlation-name captures the value of the deleted row. If it is an UPDATE operation, it captures the value of the row before the UPDATE operation. The same applies to the OLD_TABLE identifier and the set of affected rows.
The NEW correlation-name and the NEW_TABLE identifier can only be used if the trigger event is either an INSERT operation or an UPDATE operation (SQLSTATE 42898). In both operations, the value of NEW captures the new state of the row as provided by the original operation and as modified by any BEFORE trigger that has executed to this point. The same applies to the NEW_TABLE identifier and the set of affected rows.
OLD_TABLE and NEW_TABLE identifiers cannot be defined for a BEFORE trigger (SQLSTATE 42898).
OLD and NEW correlation-names cannot be defined for a FOR EACH STATEMENT trigger (SQLSTATE 42899).
Transition tables cannot be modified (SQLSTATE 42807).
The total of the references to the transition table columns and transition variables in the triggered-action cannot exceed the limit for the number of columns in a table or the sum of their lengths cannot exceed the maximum length of a row in a table (SQLSTATE 54040).
The scope of each correlation-name and each identifier is the entire trigger definition.

FOR EACH ROW
Specifies that the triggered action is to be applied once for each row of the subject table that is affected by the triggering SQL operation.

FOR EACH STATEMENT
Specifies that the triggered action is to be applied only once for the whole statement. This type of trigger granularity cannot be specified for a BEFORE trigger (SQLSTATE 42613). If specified, an UPDATE or DELETE trigger is activated even when no rows are affected by the triggering UPDATE or DELETE statement.

MODE DB2SQL
This clause is used to specify the mode of triggers. This is the only valid mode currently supported.

triggered-action
Specifies the action to be performed when a trigger is activated. A triggered-action is composed of one or several triggered-SQL-statements and by an optional condition for the execution of the triggered-SQL-statements. If there is more than one triggered-SQL-statement in the triggered-action for a given trigger, they must be enclosed within the BEGIN ATOMIC and END keywords, separated by a semi-colon, 83 and are executed in the order they are specified.

WHEN (search-condition)
Specifies a condition that is true, false, or unknown. The search-condition provides a capability to determine whether or not a certain triggered action should be executed.
The associated action is performed only if the specified search condition evaluates as true. If the WHEN clause is omitted, the associated triggered-SQL-statements are always performed.


triggered-SQL-statement
If the trigger is a BEFORE trigger, then a triggered SQL statement must be one of the following (SQLSTATE 42987):
a fullselect 84
a SET transition-variable SQL statement.
a SIGNAL SQLSTATE statement
If the trigger is an AFTER trigger, then a triggered SQL statement must be one of the following (SQLSTATE 42987):

an INSERT SQL statement
a searched UPDATE SQL statement
a searched DELETE SQL statement
a SIGNAL SQLSTATE statement
a fullselect 84
The triggered-SQL-statement cannot reference an undefined transition variable (SQLSTATE 42703) or a declared temporary table (SQLSTATE 42995).

The triggered-SQL-statement in a BEFORE trigger cannot reference a summary table defined with REFRESH IMMEDIATE (SQLSTATE 42997).

The triggered-SQL-statement in a BEFORE trigger cannot reference a generated column, other than the identity column, in the new transition variable (SQLSTATE 42989).

Notes

Adding a trigger to a table that already has rows in it will not cause any triggered actions to be activated. Thus, if the trigger is designed to enforce constraints on the data in the table, those constraints may not be satisfied by the existing rows.
If the events for two triggers occur simultaneously (for example, if they have the same event, activation time, and subject tables), then the first trigger created is the first to execute.
If a column is added to the subject table after triggers have been defined, the following rules apply:
If the trigger is an UPDATE trigger that was specified without an explicit column list, then an update to the new column will cause the activation of the trigger.
The column will not be visible in the triggered action of any previously defined trigger.
The OLD_TABLE and NEW_TABLE transition tables will not contain this column. Thus, the result of performing a "SELECT *" on a transition table will not contain the added column.
If a column is added to any table referenced in a triggered action, the new column will not be visible to the triggered action.
The result of a fullselect specified as a triggered-SQL-statement is not available inside or outside of the trigger.
A before delete trigger defined on a table involved in a cycle of cascaded referential constraints should not include references to the table on which it is defined or any other table modified by cascading during the evaluation of the cycle of referential integrity constraints. The results of such a trigger are data dependent and therefore may not produce consistent results.
In its simplest form, this means that a before delete trigger on a table with a self-referencing referential constraint and a delete rule of CASCADE should not include any references to the table in the triggered-action.

The creation of a trigger causes certain packages to be marked invalid:
If an update trigger without an explicit column list is created, then packages with an update usage on the target table are invalidated.
If an update trigger with a column list is created, then packages with update usage on the target table are only invalidated if the package also has an update usage on at least one column in the column-name list of the CREATE TRIGGER statement.
If an insert trigger is created, packages that have an insert usage on the target table are invalidated.
If a delete trigger is created, packages that have a delete usage on the target table are invalidated.
A package remains invalid until the application program is explicitly bound or rebound, or it is executed and the database manager automatically rebinds it.
Inoperative triggers: An inoperative trigger is a trigger that is no longer available and is therefore never activated. A trigger becomes inoperative if:
A privilege that the creator of the trigger is required to have for the trigger to execute is revoked.
An object such as a table, view or alias, upon which the triggered action is dependent, is dropped.
A view, upon which the triggered action is dependent, becomes inoperative.
An alias that is the subject table of the trigger is dropped.
In practical terms, an inoperative trigger is one in which a trigger definition has been dropped as a result of cascading rules for DROP or REVOKE statements. For example, when an view is dropped, any trigger with a triggered-SQL-statement defined using that view is made inoperative.

When a trigger is made inoperative, all packages with statements performing operations that were activating the trigger will be marked invalid. When the package is rebound (explicitly or implicitly) the inoperative trigger is completely ignored. Similarly, applications with dynamic SQL statements performing operations that were activating the trigger will also completely ignore any inoperative triggers.

The trigger name can still be specified in the DROP TRIGGER and COMMENT ON TRIGGER statements.

An inoperative trigger may be recreated by issuing a CREATE TRIGGER statement using the definition text of the inoperative trigger. This trigger definition text is stored in the TEXT column of SYSCAT.TRIGGERS. Note that there is no need to explicitly drop the inoperative trigger in order to recreate it. Issuing a CREATE TRIGGER statement with the same trigger-name as an inoperative trigger will cause that inoperative trigger to be replaced with a warning (SQLSTATE 01595).

Inoperative triggers are indicated by an X in the VALID column of the SYSCAT.TRIGGERS catalog view.

Errors executing triggers: Errors that occur during the execution of triggered-SQL-statements are returned using SQLSTATE 09000 unless the error is considered severe. If the error is severe, the severe error SQLSTATE is returned. The SQLERRMC field of the SQLCA for non-severe error will include the trigger name, SQLCODE, SQLSTATE and as many tokens as will fit from the tokens of the failure.
A triggered-SQL-statement could be a SIGNAL SQLSTATE statement or contain a RAISE_ERROR function. In both these cases, the SQLSTATE returned is the one specified in the SIGNAL SQLSTATE statement or the RAISE_ERROR condition.

Creating a trigger with a schema name that does not already exist will result in the implicit creation of that schema provided 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.
A value generated by the database manager for an identity column is generated before the execution of any BEFORE triggers. Therefore, the generated identity value is visible to BEFORE triggers.
A value generated by the database manager for a generated by expression column is generated after the execution of all BEFORE triggers.Therefore, the value generated by the expression is not visible to BEFORE triggers.
Triggers and typed tables: A trigger can be attached to a typed table at any level of a table hierarchy. If an SQL statement activates multiple triggers, the triggers will be executed in their creation order, even if they are attached to different tables in the typed table hierarchy.
When a trigger is activated, its transition variables (OLD, NEW, OLD_TABLE and NEW_TABLE) may contain rows of subtables. However, they will contain only columns defined on the table to which they are attached.

Effects of INSERT, UPDATE, and DELETE statements:

Row triggers: When an SQL statement is used to INSERT, UPDATE, or DELETE a table row, it activates row-triggers attached to the most specific table containing the row, and all supertables of that table. This rule is always true, regardless of how the SQL statement accesses the table. For example, when issuing an UPDATE EMP command, some of the updated rows may be in the subtable MGR. For EMP rows, the row-triggers attached to EMP and its supertables are activated. For MGR rows, the row-triggers attached to MGR and its supertables are activated.
Statement triggers: An INSERT, UPDATE, or DELETE statement activates statement-triggers attached to tables (and their supertables) that could be affected by the statement. This rule is always true, regardless of whether any actual rows in these tables were affected. For example, on an INSERT INTO EMP command, statement-triggers for EMP and its supertables are activated. As another example, on either an UPDATE EMP or DELETE EMP command, statement triggers for EMP and its supertables and subtables are activated, even if no subtable rows were updated or deleted. Likewise, a UPDATE ONLY (EMP) or DELETE ONLY (EMP) command will activate statement-triggers for EMP and its supertables, but not statement-triggers for subtables.
Effects of DROP TABLE statements: A DROP TABLE statement does not activate any triggers that are attached to the table being dropped. However, if the dropped table is a subtable, all the rows of the dropped table are considered to be deleted from its supertables. Therefore, for a table T:

Row triggers: DROP TABLE T activates row-type delete-triggers that are attached to all supertables of T, for each row of T.
Statement triggers: DROP TABLE T activates statement-type delete-triggers that are attached to all supertables of T, regardless of whether T contains any rows.
Actions on Views: To predict what triggers are activated by an action on a view, use the view definition to translate that action into an action on base tables. For example:

An SQL statement performs UPDATE V1, where V1 is a typed view with a subview V2. Suppose V1 has underlying table T1, and V2 has underlying table T2. The statement could potentially affect rows in T1, T2, and their subtables, so statement triggers are activated for T1 and T2 and all their subtables and supertables.
An SQL statement performs UPDATE V1, where V1 is a typed view with a subview V2. Suppose V1 is defined as SELECT ... FROM ONLY(T1) and V2 is defined as SELECT ... FROM ONLY(T2). Since the statement cannot affect rows in subtables of T1 and T2, statement triggers are activated for T1 and T2 and their supertables, but not their subtables.
An SQL statement performs UPDATE ONLY(V1), where V1 is a typed view defined as SELECT ... FROM T1. The statement can potentially affect T1 and its subtables. Therefore, statement triggers are activated for T1 and all its subtables and supertables.
An SQL statement performs UPDATE ONLY(V1), where V1 is a typed view defined as SELECT ... FROM ONLY(T1). In this case, T1 is the only table that can be affected by the statement, even if V1 has subviews and T1 has subtables. Therefore, statement triggers are activated only for T1 and its supertables.
Examples

Example 1: Create two triggers that will result in the automatic tracking of the number of employees a company manages. The triggers will interact with the following tables:

EMPLOYEE table with these columns: ID, NAME, ADDRESS, and POSITION.
COMPANY_STATS table with these columns: NBEMP, NBPRODUCT, and REVENUE.
The first trigger increments the number of employees each time a new person is hired; that is, each time a new row is inserted into the EMPLOYEE table:

CREATE TRIGGER NEW_HIRED
AFTER INSERT ON EMPLOYEE
FOR EACH ROW MODE DB2SQL
UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

The second trigger decrements the number of employees each time an employee leaves the company; that is, each time a row is deleted from the table EMPLOYEE:

CREATE TRIGGER FORMER_EMP
AFTER DELETE ON EMPLOYEE
FOR EACH ROW MODE DB2SQL
UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1

Example 2: Create a trigger that ensures that whenever a parts record is updated, the following check and (if necessary) action is taken:

If the on-hand quantity is less than 10% of the maximum stocked quantity, then issue a shipping request ordering the number of items for the affected part to be equal to the maximum stocked quantity minus the on-hand quantity.
The trigger will interact with the PARTS table with these columns: PARTNO, DESCRIPTION, ON_HAND, MAX_STOCKED, and PRICE.

ISSUE_SHIP_REQUEST is a user-defined function that sends an order form for additional parts to the appropriate company.

CREATE TRIGGER REORDER
AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.ON_HAND < 0.10 * N.MAX_STOCKED)
BEGIN ATOMIC
VALUES(ISSUE_SHIP_REQUEST(N.MAX_STOCKED - N.ON_HAND, N.PARTNO));
END

Example 3: Create a trigger that will cause an error when an update occurs that would result in a salary increase greater than ten percent of the current salary.

CREATE TRIGGER RAISE_LIMIT
AFTER UPDATE OF SALARY ON EMPLOYEE
REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (N.SALARY > 1.1 * O.SALARY)
SIGNAL SQLSTATE '75000' ('Salary increase>10%')

Example 4: Consider an application which records and tracks changes to stock prices. The database contains two tables, CURRENTQUOTE and QUOTEHISTORY.

Tables: CURRENTQUOTE (SYMBOL, QUOTE, STATUS)
QUOTEHISTORY (SYMBOL, QUOTE, QUOTE_TIMESTAMP)

When the QUOTE column of CURRENTQUOTE is updated, the new quote should be copied, with a timestamp, to the QUOTEHISTORY table. Also, the STATUS column of CURRENTQUOTE should be updated to reflect whether the stock is:

rising in value;
at a new high for the year;
dropping in value;
at a new low for the year;
steady in value.
CREATE TRIGGER statements that accomplish this are as follows.

Trigger Definition to set the status:
CREATE TRIGGER STOCK_STATUS
NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NEWQUOTE.STATUS =
CASE
WHEN NEWQUOTE.QUOTE >
(SELECT MAX(QUOTE) FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
THEN 'High'
WHEN NEWQUOTE.QUOTE <
(SELECT MIN(QUOTE) FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
THEN 'Low'
WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
THEN 'Rising'
WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE
THEN 'Dropping'
WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
THEN 'Steady'
END;
END

Trigger Definition to record change in QUOTEHISTORY table:
CREATE TRIGGER RECORD_HISTORY
AFTER UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO QUOTEHISTORY
VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP);
END

55555,大家帮帮忙呀,严重打击我的学习信心.

不好意思，刚才发错了，已经改正了。[]
其实写存储过程也很容易，多试两次会了。
另外要检查自己的SQL，是不是写错了？先写一个最简单的SQL试一试。

CREATE PROCEDURE


This statement is used to register a stored procedure with an application 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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

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

SYSADM or DBADM authority
IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the procedure does not exist
CREATEIN privilege on the schema, if the schema name of the procedure refers to an existing schema.
To create a not-fenced stored procedure, the privileges held by the authorization ID of the statement must also include at least one of the following:

CREATE_NOT_FENCED authority on the database
SYSADM or DBADM authority.
To create a fenced stored procedure, no additional authorities or privileges are required.

If the authorization ID has insufficient authority to perform the operation, an error (SQLSTATE 42502) is raised.

Syntax

>>-CREATE PROCEDURE--------------------------------------------->

>----procedure-name--(--+----------------------------------------------+---)->
| .-,---------------------------------------. |
| V .-IN----. | |
'----+-------+---parameter-name--data-type---+-'
+-OUT---+
'-INOUT-'

>----*----+--------------------------+--*----------------------->
'-SPECIFIC--specific-name--'

.- DYNAMIC RESULT SETS 0--------. (1)
>-----+-------------------------------+---------*--------------->
'-DYNAMIC RESULT SETS--integer--'

.-MODIFIES SQL DATA--. .-NOT DETERMINISTIC--.
>-----+--------------------+--*----+--------------------+--*---->
| (2) | '-DETERMINISTIC------'
+-NO SQL-------------+
+-CONTAINS SQL-------+
'-READS SQL DATA-----'

(3)
.-CALLED ON NULL INPUT------.
>----+---------------------------+---*-------------------------->

>-----+-LANGUAGE--+-C-----+--*----| external-procedure-options |--*--+>
| +-JAVA--+ |
| +-COBOL-+ |
| '-OLE---' |
'-LANGUAGE--SQL---*----| SQL-procedure-body |------------------'

>--------------------------------------------------------------><

external-procedure-options

.-FENCED-----.
|---*---EXTERNAL--+----------------------+--*----+------------+->
'-NAME--+-'string'---+-' '-NOT FENCED-'
'-identifier-'

>----*---PARAMETER STYLE--+-DB2DARI------------+--*------------->
| (4) |
+-DB2GENERAL---------+
+-GENERAL------------+
+-GENERAL WITH NULLS-+
+-DB2SQL-------------+
'-JAVA---------------'

.-NO DBINFO--.
>-----+------------------------+--*----+------------+--*--------|
'-PROGRAM TYPE--+-SUB--+-' '-DBINFO-----'
'-MAIN-'

SQL-procedure-body

|---SQL-procedure-statement-------------------------------------|



Notes:


RESULT SETS may be specified in place of DYNAMIC RESULT SETS.

NO SQL is not a valid choice for LANGUAGE SQL.

NULL CALL may be specified in place of CALLED ON NULL INPUT.

DB2GENRL may be specified in place of DB2GENERAL, SIMPLE CALL may be specified in place of GENERAL and SIMPLE CALL WITH NULLS may be specified in place of GENERAL WITH NULLS.

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 the parameters, while of course unique within its schema, need not be unique across schemas.

The a two-part name is specified, the schema-name cannot begin with "SYS". Otherwise, an error (SQLSTATE 42939) is raised.


( 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,

CREATE PROCEDURE SUBWOOFER() ...

No two identically-named procedures within a schema are permitted to have exactly the same number of parameters. Lengths, precisions and scales are not considered in this type comparison. Therefore CHAR(8) and CHAR(35) are considered to be the same type, as are DECIMAL(11,2) and DECIMAL (4,3). There is some further bundling of types that causes them to be treated as the same type for this purpose, such as DECIMAL and NUMERIC. 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 of the procedure are the same even if the data types are not.


IN | OUT | INOUT
Specifies the mode of the parameter.
IN - parameter is input only
OUT - parameter is output only
INOUT - parameter is both input and output

parameter-name
Specifies the name of the parameter.

data-type
Specifies the data type of the parameter.
SQL data type specifications and abbreviations which may be specified in the data-type definition of a CREATE TABLE statement and have a correspondence in the language that is being used to write the procedure may be specified. See the language-specific sections of the Application Development Guide for details on the mapping between the SQL data types and host language data types with respect to stored procedures.
User-defined data types are not supported (SQLSTATE 42601).

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 may 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 of 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 stored procedure. Refer to "Returning Result Sets from Stored Procedures" in the SQL Reference for more information.
The value RESULT SETS may be used as a synonym for DYNAMIC RESULT SETS for backwards and family compatibility.


NO SQL, CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA
Indicates whether the stored procedure issues any SQL statements and, if so, what type.

NO SQL
Indicates that the stored procedure cannot execute any SQL statements (SQLSTATE 38001).

CONTAINS SQL
Indicates that SQL statements that neither read nor modify SQL data can be executed by the stored procedure (SQLSTATE 38004 or 42985). Statements that are not supported in any stored procedure return a different error (SQLSTATE 38003 or 42985).

READS SQL DATA
Indicates that some SQL statements do not modify SQL data can be included in the stored procedure (SQLSTATE 38002 or 42985). Statements that are not supported in any stored procedure return a different error (SQLSTATE 38003 or 42985).

MODIFIES SQL DATA
Indicates that the stored procedure can execute any SQL statement except statements that are not supported in stored procedures (SQLSTATE 38003 or 42985).
The following table indicates whether or not an SQL statement (specified in the first column) is allowed to execute in a stored procedure with the specified SQL data access indication. If an executable SQL statement is encountered in a stored procedure defined with NO SQL, SQLSTATE 38001 is returned. For other executions contexts, SQL statements that are not supported in any context return SQLSTATE 38003. For other SQL statements not allowed in a CONTAINS SQL context, SQLSTATE 38004 is returned and in a READS SQL DATA context, SQLSTATE 38002 is returned. During creation of an SQL procedure, a statement that does not match the SQL data access indication will cause SQLSTATE 42895 to be returned.


Table 21. SQL Statement and SQL Data Access Indication
SQL Statement NO SQL CONTAINS SQL READS SQL DATA MODIFIES SQL DATA
ALTER... N N N Y
BEGIN DECLARE SECTION Y(1) Y Y Y
CALL N Y(4) Y(4) Y(4)
CLOSE CURSOR N N Y Y
COMMENT ON N N N Y
COMMIT N N N N
COMPOUND SQL N Y Y Y
CONNECT(2) N N N N
CREATE N N N Y
DECLARE CURSOR Y(1) Y Y Y
DECLARE GLOBAL TEMPORARY TABLE N Y Y Y
DELETE N N N Y
DESCRIBE N N Y Y
DISCONNECT(2) N N N N
DROP ... N N N Y
END DECLARE SECTION Y(1) Y Y Y
EXECUTE N Y(3) Y(3) Y
EXECUTE IMMEDIATE N Y(3) Y(3) Y
EXPLAIN N N N Y
FETCH N N Y Y
FREE LOCATOR N Y Y Y
FLUSH EVENT MONITOR N N N Y
GRANT ... N N N Y
INCLUDE Y(1) Y Y Y
INSERT N N N Y
LOCK TABLE N Y Y Y
OPEN CURSOR N N Y Y
PREPARE N Y Y Y
REFRESH TABLE N N N Y
RELEASE CONNECTION(2) N N N N
RELEASE SAVEPOINT N N N Y
RENAME TABLE N N N Y
REVOKE ... N N N Y
ROLLBACK N Y Y Y
ROLLBACK TO SAVEPOINT N N N Y
SAVEPOINT N N N Y
SELECT INTO N N Y Y
SET CONNECTION(2) N N N N
SET INTEGRITY N N N Y
SET special register N Y Y Y
UPDATE N N N Y
VALUES INTO N N Y Y
WHENEVER Y(1) Y Y Y


Notes:


Although the NO SQL option implies that no SQL statements can be specified, non-executable statements are not restricted.

Connection management statements are not allowed in any stored procedure execution contexts.

It depends on the statement being executed. The statement specified for the EXECUTE statement must be a statement that is allowed in the context of the particular SQL access level in effect. For example, if the SQL access level in effect is READS SQL DATA, the statement must not be an INSERT, UPDATE, or DELETE.

A CALL statement in a stored procedure can only refer to a stored procedure written in the same programming language as the calling stored procedure.

LANGUAGE
This mandatory clause is used to specify the language interface convention to which the stored procedure body is written.

C
This means the database manager will call the stored procedure as if it were a C procedure. The stored procedure must conform to the C language calling and linkage convention as defined by the standard ANSI C prototype.

JAVA
This means the database manager will call the stored procedure as a method in a Java class.

COBOL
This means the database manager will call the procedure as if it were a COBOL procedure.

OLE
This means the database manager will call the stored procedure as if it were a method exposed by an OLE automation object. The stored-procedure must conform with the OLE automation data types and invocation mechanism. Also, the OLE automation object needs to be implemented as an in-process server (DLL). These restrictions are outlined in the OLE Automation Programmer's Reference.
LANGUAGE OLE is only supported for stored procedures stored in DB2 for Windows 32-bit operating systems.


SQL
The specified SQL-procedure-body includes the statements which define the processing of the stored procedure

EXTERNAL
This clause indicates that the CREATE PROCEDURE statement is being used to register a new procedure based on code written in an external programming language and adhering to the documented linkage conventions and interface.
If NAME clause is not specified "NAME procedure-name" is assumed.


NAME 'string'
This clause identifies the name of the user-written code which implements the procedure being defined.
The 'string' option is a string constant with a maximum of 254 characters. The format used for the string is dependent on the LANGUAGE specified.

For LANGUAGE C:
The string specified is the library name and procedure within the library, which the database manager invokes to execute the stored procedure being CREATEd. The library (and the procedure within the library) do not need to exist when the CREATE PROCEDURE statement is performed. However, when the procedure is called, the library and procedure within the library must exist and be accessible from the database server machine.

>>-'--+-library_id-------+---+-------------+---'---------------><
'-absolute_path_id-' '-!--proc_id--'


The name must be enclosed in single quotes. Extraneous blanks are not permitted within the single quotes.


library_id
Identifies the library name containing the procedure. The database manager will look for the library in the .../sqllib/function/unfenced directory and the .../sqllib/function directory (UNIX-based systems), or .../instance_name/function/unfenced directory and the .../instance_name/function directory (OS/2, Windows 32-bit operating systems as specified by the DB2INSTPROF registry variable), where the database manager will locate the controlling sqllib directory which is being used to run the database manager. For example, the controlling sqllib directory in UNIX-based systems is /u/$DB2INSTANCE/sqllib.
If 'myproc' were the library_id in a UNIX-based system it would cause the database manager to look for the procedure in library /u/production/sqllib/function/unfenced/myfunc and /u/production/sqllib/function/myfunc, provided the database manager is being run from /u/production.

For OS/2, Windows 32-bit operating systems, the database manager will look in the LIBPATH or PATH if the library_id is not found in the function directory, and will be run as fenced.

Stored procedures located in any of these directories do not use any of the registered attributes.


absolute_path_id
Identifies the full path name of the procedure.
In a UNIX-based system, for example, '/u/jchui/mylib/myproc' would cause the database manager to look in /u/jchui/mylib for the myproc procedure.

In OS/2, Windows 32-bit operating systems 'd:/mylib/myproc' would cause the database manager to load the myproc.dll file from the d:/mylib directory.

If an absolute path is specified, the procedure will run as fenced, ignoring the FENCED or NOT FENCED attribute.


! proc_id
Identifies the entry point name of the procedure to be invoked. The ! serves as a delimiter between the library id and the procedure id. If ! proc_id is omitted, the database manager will use the default entry point established when the library was linked.
In a UNIX-based system, for example, 'mymod!proc8' would direct the database manager to look for the library $inst_home_dir/sqllib/function/mymod and to use entry point proc8 within that library.

In OS/2, Windows 32-bit operating systems 'mymod!proc8' would direct the database manager to load the mymod.dll file and call the proc8() procedure in the dynamic link library (DLL).

If the string is not properly formed, an error (SQLSTATE 42878) is raised.

The body of every stored procedure should be in a directory which is mounted and available on every partition of the database.

For LANGUAGE JAVA:
The string specified contains the optional jar file identifier, class identifier and method identifier, which the database manager invokes to execute the stored procedure being CREATEd. The class identifier and method identifier do not need to exist when the CREATE PROCEDURE statement is performed. If a jar_id is specified, it must exist when the CREATE PROCEDURE statement is performed. However, when the procedure is called, the class identifier and the method identifier must exist and be accessible from the database server machine, otherwise an error (SQLSTATE 42884) is raised.

>>-'----+----------+--class_id--+-.-+---method_id--'-----------><
'-jar_id :-' '-!-'


The name must be enclosed in single quotes. Extraneous blanks are not permitted within the single quotes.


jar_id
Identifies the jar identifier given to the jar collection when it was installed in the database. It can be either a simple identifier, or a schema qualified identifier. Examples are 'myJar' and 'mySchema.myJar'.

class_id
Identifies the class identifier of the Java object. If the class is part of a package, the class identifier part must include the complete package prefix, for example, 'myPacks.StoredProcs'. The Java virtual machine will look in directory '../myPacks/StoredProcs/' for the classes. In OS/2 and Windows 32-bit operating systems, the Java virtual machine will look in directory '../myPacks/StoredProcs/'.

method_id
Identifies the method name with the Java class to be invoked.
For LANGUAGE OLE:
The string specified is the OLE programmatic identifier (progid) or class identifier (clsid), and method identifier (method_id), which the database manager invokes to execute the stored procedure being created by the statement. The programmatic identifier or class identifier, and the method identifier do not need to exist when the CREATE PROCEDURE statement is executed. However, when the procedure is used in the CALL statement, the method identifier must exist and be accessible from the database server machine, otherwise an error results (SQLSTATE 42724).

>>-'--+-progid-+---!--method_id--'-----------------------------><
'-clsid--'


The name must be enclosed in single quotes. Extraneous blanks are not permitted within the single quotes.


progid
Identifies the programmatic identifier of the OLE object.
A progid is not interpreted by the database manager, but only forwarded to the OLE automation controller at run time. The specified OLE object must be creatable and support late binding (also known as IDispatch-based binding). By convention, progids have the following format:

<program_name>.<component_name>.<version>

Since it is only a convention, and not a rule, progids may in fact have a different format.


clsid
Identifies the class identifier of the OLE object to create. It can be used as an alternative for specifying a progid in the case that an OLE object is not registered with a progid. The clsid has the form:
{nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn}

where 'n' is an alphanumeric character. A clsid is not interpreted by the database manager but only forwarded to the OLE APIs at run time.


method_id
Identifies the method name of the OLE object to be invoked.

NAME identifier
This identifier specified is an SQL identifier. The SQL identifier is used as the library-id in the string. Unless it is a delimited identifier, the identifier is folded to upper case. If the identifier is qualified with a schema name, the schema name portion is ignored. This form of NAME can only be used with LANGUAGE C.

FENCED or NOT FENCED
This clause specifies whether or not the stored procedure is considered "safe" to run in the database manager operating environment's process or address space (NOT FENCED), or not (FENCED).
If a stored procedure is registered as FENCED, the database manager insulates its internal resources (e.g. data buffers) from access by the procedure. All procedures have the option of running as FENCED or NOT FENCED. In general, a procedure running as FENCED will not perform as well as a similar one running as NOT FENCED.

If the stored procedure is located in .../sqllib/function/unfenced directory and the .../sqllib/function directory (UNIX-based systems), or .../instance_name/function/unfenced directory and the .../instance_name/function directory (OS/2, Windows 32-bit operating systems), then the FENCED or NOT FENCED registered attribute (and every other registered attribute) will be ignored. Note: Use of NOT FENCED for procedures not adequately checked out can compromise the integrity of DB2. DB2 takes some precautions against many of the common types of inadvertent failures that might occur, but cannot guarantee complete integrity when NOT FENCED stored procedures are used.


To change from FENCED to NOT FENCED, the procedure must be re-registered (by first dropping it and then re-creating it). Either SYSADM authority, DBADM authority or a special authority (CREATE_NOT_FENCED) is required to register a stored procedures as NOT FENCED. Only FENCED can be specified for a stored procedure with LANGUAGE OLE.


PARAMETER STYLE
This clause is used to specify the conventions used for passing parameters to and returning the value from stored procedures.

DB2DARI
This means that the stored procedure will use a parameter passing convention that conforms to C language calling and linkage conventions. This can only be specified when LANGUAGE C is used.

DB2GENERAL
This means that the stored procedure will use a parameter passing convention that is defined for use with Java methods. This can only be specified when LANGUAGE JAVA is used.
The value DB2GENRL may be used as a synonym for DB2GENERAL.


GENERAL
This means that the stored procedure will use a parameter passing mechanism where the stored procedure receives the parameters specified on the CALL. The parameters are passed directly as expected by the language, the SQLDA structure is not used. This can only be specified when LANGUAGE C or COBOL is used.
Null indicators are NOT directly passed to the program.

The value SIMPLE CALL may be used as a synonym for GENERAL.


GENERAL WITH NULLS
In addition to the parameters on the CALL statement as specified in GENERAL, another argument is passed to the stored procedure. This additional argument contains a vector of null indicators for each of the parameters on the CALL statement. In C, this would be an array of short ints. This can only be specified when LANGUAGE C or COBOL is used.
The value SIMPLE CALL WITH NULLS may be used as a synonym for GENERAL WITH NULLLS.


DB2SQL
In addition to the parameters on the CALL statement, the following arguments are passed to the stored procedure:
a NULL indicator for each parameter on the CALL statement
the SQLSTATE to be returned to DB2
the qualified name of the stored procedure
the specific name of the stored procedure
the SQL diagnostic string to be returned to DB2
This can only be specified when LANGUAGE C, COBOL or OLE is used.


JAVA
This means that the stored procedure will use a parameter passing convention that conforms to the Java language and SQLJ Routines specification. IN/OUT and OUT parameters will be passed as single entry arrays to facilitate returning values. This can only be specified when LANGUAGE JAVA is used.
PARAMETER STYLE JAVA procedures do not support the DBINFO or PROGRAM TYPE clauses.

Refer to Application Development Guide for details on passing parameters.


PROGRAM TYPE
Specifies whether the stored procedure expects parameters in the style of a main routine or a subroutine.

SUB
The stored procedure expects the parameters to be passed as separate arguments.

MAIN
The stored procedure expects the parameters to be passed as an argument counter, and a vector of arguments (argc, argv). The name of the stored procedure to be invoked must also be "main". Stored procedures of this type must still be built in the same fashion as a shared library as opposed to a stand-alone executable.
The default for PROGRAM TYPE is SUB. PROGRAM TYPE MAIN is only valid for LANGUAGE C or COBOL and PARAMETER STYLE GENERAL, GENERAL WITH NULLS or DB2SQL.


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 stored procedure.


CALLED ON NULL INPUT
CALLED ON NULL INPUT always applies to stored procedures. This means that regardless if any arguments are null, the stored procedure is called. It can return a null value or a normal (non-null) value. Responsibility for testing for null argument values lies with the stored procedure.
The value NULL CALL may be used as a synonym for CALLED ON NULL INPUT for backwards and family compatibility.


NO DBINFO or DBINFO
Specifies whether specific information known by DB2 is passed to the stored procedure when it is invoked as an additional invocation-time argument (DBINFO) or not (NO DBINFO). NO DBINFO is the default. DBINFO is not supported for LANGUAGE OLE (SQLSTATE 42613). It is also not supported for PARAMETER STYLE JAVA, DB2GENERAL, or DB2DARI.
If DBINFO is specified, then a structure is passed to the stored procedure which contains the following information:

Data base name - the name of the currently connected database.
Application ID - unique application ID which is established for each connection to the database.
Application Authorization ID - the application run-time authorization ID.
Code page - identifies the database code page.
Schema name - not applicable to stored procedures.
Table name - not applicable to stored procedures.
Column name - not applicable to stored procedures.
Database version/release - identifies the version, release and modification level of the database server invoking the stored procedure.
Platform - contains the server's platform type.
Table function result column numbers - not applicable to stored procedures.
Please see the Application Development Guide for detailed information on the structure and how it is passed to the stored procedure.


SQL-procedure-body
Specifies the SQL statement that is the body of the SQL procedure. Multiple SQL-procedure-statements may be specified within a compound statement. See SQL Procedures for more information.
Notes

For information on creating the programs for a stored procedure, see the Application Development Guide.
Creating a procedure with a schema name that does not already exist will result in the implicit creation of that schema provided 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.
The settings of the special registers of the caller are inherited by the stored procedure on invocation and restored upon return to the caller. Special registers may be changed within a stored procedure, but these changes do not effect the caller. This is not true for legacy stored procedures (those defined with parameter style DB2DARI or stored in the default library), where the changes made to special registers in a procedure become the settings for the caller.
Examples

Example 1: Create the procedure definition for a stored procedure, written in Java, that is passed a part number and returns the cost of the part and the quantity that are currently available.

CREATE PROCEDURE PARTS_ON_HAND (IN PARTNUM INTEGER,
OUT COST DECIMAL(7,2),
OUT QUANTITY INTEGER)
EXTERNAL NAME 'parts.onhand'
LANGUAGE JAVA PARAMETER STYLE JAVA

Example 2: Create the procedure definition for a stored procedure, written in C, that is passed an assembly number and returns the number of parts that make up the assembly, total part cost and a result set that lists the part numbers, quantity and unit cost of each part.

CREATE PROCEDURE ASSEMBLY_PARTS (IN ASSEMBLY_NUM INTEGER,
OUT NUM_PARTS INTEGER,
OUT COST DOUBLE)
EXTERNAL NAME 'parts!assembly'
DYNAMIC RESULT SETS 1 NOT FENCED
LANGUAGE C PARAMETER STYLE GENERAL

Example 3: 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;

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;
DECLARE EXIT HANDLER FOR NOT FOUND
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

可能是没有指定相应的c/c++编译器。请检查
%DB2_PATH%/function/routine/sr_cpath.bak
如果是VC6/5，把相应的注释去掉就行了

Ok.