关于存储过程的创建,求助！！

我在Stored Procedure Builder中建好了一个存储过程，代码如下：
CREATE PROCEDURE DB2ADMIN.Proc1 (  
    LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure 
------------------------------------------------------------------------
BEGIN

   create table zhouhaiming(
   f1 smallint,
   f2 varchar(9),
   f3 char(5);
   insert into zhouhaiming values
   (1,'www','aaa');
END @  
输入窗口显示
DB2ADMIN.PROC1 - Create stored procedure returns -778.

DB2ADMIN.PROC1: 15: [IBM][CLI Driver][DB2/NT] SQL0778N  End label "@" is not the same as the begin label.  LINE NUMBER=15.  SQLSTATE=428D5



DB2ADMIN.PROC1 - Build failed.

DB2ADMIN.PROC1 - Changes rolled back.
去掉END后的@,再build，输出窗口显示
DB2ADMIN.PROC1 - Create stored procedure returns -778.

DB2ADMIN.PROC1: 15: [IBM][CLI Driver][DB2/NT] SQL0778N  End label "@" is not the same as the begin label.  LINE NUMBER=15.  SQLSTATE=428D5



DB2ADMIN.PROC1 - Build failed.

DB2ADMIN.PROC1 - Changes rolled back.
我在Build存储过程以前已经在CLP环境下设好了下列参数，并且已经安装了VC++6.0，也重启了DB2
db2set DB2_SQLROUTINE_COMPILER_PATH="c:\progra~1\micros~4\vc98\bin\vcvars32.bat"
db2set DB2_SQLROUTINE_KEEP_FILES=1
按照资料的说法我没有设DB2_SQLROUTINE_COMPILE_COMMAND参数，用默认值即可

哪位高手能告诉我，为什么我不能创建存储过程吗？

Law 回复于：2002-12-22 13:50:36

你把开始和结束的语句修改一下 ------------------------------------------------------------------------ -- SQL Stored Procedure  ------------------------------------------------------------------------ p1:BEGIN ... ... ... end p1

zhouhaiming 回复于：2002-12-22 18:53:34

把程序改为 CREATE PROCEDURE DB2ADMIN.Proc1 (       LANGUAGE SQL ------------------------------------------------------------------------ -- SQL Stored Procedure  ------------------------------------------------------------------------ P1: BEGIN create table zhouhaiming(   f1 smallint,   f2 varchar(9),   f3 char(5)   ;   insert into zhouhaiming values   (1,'www','aaa'); END P1                               执行Build后输出窗口显示如下 DB2ADMIN.PROC1 - Create stored procedure returns -7032. DB2ADMIN.PROC1: [IBM][CLI Driver][DB2/NT] SQL7032N  SQL procedure "PROC1" not created.  Diagnostic file is "P3413010.log".  SQLSTATE=42904 DB2ADMIN.PROC1 - Build failed. DB2ADMIN.PROC1 - Changes rolled back. 打开P3413010.log文件，如下 *** PREPCOMPILE C:\PROGRA~1\SQLLIB\function\routine\sqlproc\DEVELOP\DB2ADMIN\tmp\P3413010.sqc *** LINE    MESSAGES FOR P3413010.sqc ------  --------------------------------------------------------------------         SQL0060W  The "C" precompiler is in progress.   148   SQL0204N  "DB2ADMIN.ZHOUHAIMING" is an undefined name.         SQL0091W  Precompilation or binding was ended with "0"                    errors and "1" warnings. *** COMPILE C:\PROGRA~1\SQLLIB\function\routine\sqlproc\DEVELOP\DB2ADMIN\tmp\P3413010.c *** @echo off REM set VCV6_DRIVE=c:\Microsoft Visual Studio REM set include=%include%;%VCV6_DRIVE%\VC98\atl\include;%VCV6_DRIVE%\VC98\mfc\include;%VCV6_DRIVE%\VC98\include REM set lib=%lib%;%VCV6_DRIVE%\VC98\mfc\lib;%VCV6_DRIVE%\VC98\lib REM set path=%path%;%VCV6_DRIVE%\Common\Tools\WinNT;%VCV6_DRIVE%\Common\MSDev98\Bin;%VCV6_DRIVE%\Common\Tools;%VCV6_DRIVE%\VC98\bin;%VCV6_DRIVE%\VC98\mfc\lib;%VCV6_DRIVE%\VC98\lib @echo off REM set VCV5_DRIVE=c:\Program Files\devstudio REM set include=%include%;%VCV5_DRIVE%\vc\include;%VCV5_DRIVE%\vc\atl\include;%VCV5_DRIVE%\vc\mfc\include REM set lib=%lib%;%VCV5_DRIVE%\vc\lib;%VCV5_DRIVE%\vc\mfc\lib REM set path=%path%;%VCV5_DRIVE%\sharedide\bin\ide;%VCV5_DRIVE%\sharedide\bin;%VCV5_DRIVE%\vc\bin @echo on set SQLROUTINE_FILENAME=P3413010 set db2path=C:\PROGRA~1\SQLLIB nmake /f C:\PROGRA~1\SQLLIB\function\routine\sqlproc.mak *** C:\PROGRA~1\SQLLIB\function\routine\sqlproc\DEVELOP\DB2ADMIN\tmp\P3413010.def *** LIBRARY P3413010 EXPORTS _pgsjmp@4 pgsjmp=_pgsjmp@4 C:\PROGRA~1\SQLLIB\function\routine\sqlproc\DEVELOP\DB2ADMIN\tmp>set SQLROUTINE_FILENAME=P3413010  C:\PROGRA~1\SQLLIB\function\routine\sqlproc\DEVELOP\DB2ADMIN\tmp>set db2path=C:\PROGRA~1\SQLLIB  C:\PROGRA~1\SQLLIB\function\routine\sqlproc\DEVELOP\DB2ADMIN\tmp>nmake /f C:\PROGRA~1\SQLLIB\function\routine\sqlproc.mak  'nmake' 不是内部或外部命令，也不是可运行的程序 或批处理文件。 ---------------------------------------------------------------------------- SQL7032N  SQL procedure "PROC1" not created.  Diagnostic file is  "P3413010.log".  SQLSTATE=42904

zhouhaiming 回复于：2002-12-22 18:58:53

我用db2 ? SQL7032N查看了一下错误内容，系统显示  SQL7032N SQL procedure "<procedure-name>" not created.           Diagnostic file is "<file-name>". Explanation:  SQL Procedure "<procedure-name>" was not created. One of the following has occurred: o   Support for SQL stored procedures is not installed and     configured on this server. You need to have the DB2     Application Development Client and a C compiler installed on     the server to create SQL procedures.  You may need to set the     DB2 registry variable DB2_SQLROUTINE_COMPILER_PATH to point     to a script or batch file that contains environment settings     for the C compiler on your platform. o   DB2 failed to precompile or compile the SQL stored procedure.     DB2 creates an SQL procedure as a C program that contains     embedded SQL. Errors not found during the inital parsing of     the CREATE PROCEDURE statement can be found during the     precompile or compile stage. For UNIX platforms, the full path of the file that contains diagnostic information is: $DB2PATH/function/routine/sqlproc/ \ $DATABASE/$SCHEMA/tmp/"<file-name>"where $DATABASE represents the name of the database, and $SCHEMA represents the schema name of the SQL procedure. For OS/2 and Windows 32-bit operating systems, the full path of the file that contains diagnostic information is: Û2PATH%\function\routine\sqlproc\ \ ÚTABASE%\%SCHEMA%\tmp\"<file-name>"where ÚTABASE% represents the name of the database, and %SCHEMA% represents the schema name of the SQL procedure. User Response:  Ensure that both a compatible C compiler and a DB2 Application Development Client are installed on the server. If a precompile or compile error occurred, refer to the messages from the precompiler or compiler in the diagnostic file "<file-name>". Ensure that the DB2 registry variable DB2_SQLROUTINE_COMPILER_PATH is set to point to a script or batch file that sets up the C compiler environment.  On a UNIX operating system, for example, you may create a script called "sr_cpath" in the /home/DB2INSTANCE/sqllib/function/routine directory.  To set the DB2 registry variable DB2_SQL_ROUTINE_COMPILER_PATH accordingly, issue the following command:      db2set DB2_SQLROUTINE_COMPILER_PATH = \         "/home/DB2INSTANCE/sqllib/function/ \         routine/sr_cpath" sqlcode:  -7032 sqlstate:  42904 C:\PROGRA~1\SQLLIB\BIN>db2 ? SQL7032N  SQL7032N SQL procedure "<procedure-name>" not created.           Diagnostic file is "<file-name>". Explanation:  SQL Procedure "<procedure-name>" was not created. One of the following has occurred: o   Support for SQL stored procedures is not installed and     configured on this server. You need to have the DB2     Application Development Client and a C compiler installed on     the server to create SQL procedures.  You may need to set the     DB2 registry variable DB2_SQLROUTINE_COMPILER_PATH to point     to a script or batch file that contains environment settings     for the C compiler on your platform. o   DB2 failed to precompile or compile the SQL stored procedure.     DB2 creates an SQL procedure as a C program that contains     embedded SQL. Errors not found during the inital parsing of     the CREATE PROCEDURE statement can be found during the     precompile or compile stage. For UNIX platforms, the full path of the file that contains diagnostic information is: $DB2PATH/function/routine/sqlproc/ \ $DATABASE/$SCHEMA/tmp/"<file-name>"where $DATABASE represents the name of the database, and $SCHEMA represents the schema name of the SQL procedure. For OS/2 and Windows 32-bit operating systems, the full path of the file that contains diagnostic information is: Û2PATH%\function\routine\sqlproc\ \ ÚTABASE%\%SCHEMA%\tmp\"<file-name>"where ÚTABASE% represents the name of the database, and %SCHEMA% represents the schema name of the SQL procedure. User Response:  Ensure that both a compatible C compiler and a DB2 Application Development Client are installed on the server. If a precompile or compile error occurred, refer to the messages from the precompiler or compiler in the diagnostic file "<file-name>". Ensure that the DB2 registry variable DB2_SQLROUTINE_COMPILER_PATH is set to point to a script or batch file that sets up the C compiler environment.  On a UNIX operating system, for example, you may create a script called "sr_cpath" in the /home/DB2INSTANCE/sqllib/function/routine directory.  To set the DB2 registry variable DB2_SQL_ROUTINE_COMPILER_PATH accordingly, issue the following command:      db2set DB2_SQLROUTINE_COMPILER_PATH = \         "/home/DB2INSTANCE/sqllib/function/ \         routine/sr_cpath" sqlcode:  -7032 sqlstate:  42904 应该是DB2_SQLROUTINE_COMPILER_PATH设置错误可是我用db2set -all命令查看发现 [e] DB2PATH=C:\Program Files\SQLLIB [i] DB2_SQLROUTINE_KEEP_FILES=1 [i] DB2_SQLROUTINE_COMPILER_PATH=C:\Progra~1\Micros~4\VC98\Bin\vcvars32.bat [i] DB2INSTPROF=C:\PROGRAM FILES\SQLLIB [g] DB2SYSTEM=ZHOUHAIMING [g] DB2PATH=C:\Program Files\SQLLIB [g] DB2INSTDEF=DB2 [g] DB2COMM=TCPIP,NPIPE [g] DB2ADMINSERVER=DB2DAS00 根本没错呀请高手指点！

mrprogram 回复于：2002-12-22 19:58:16

路径错了

Law 回复于：2002-12-22 22:19:27

刚才忘了仔细看你程序了，你的动态语句，应该用动态sql来执行阿  declare stmt varchar(1024);  set stmt='create table zhouhaiming( f1 smallint, f2 varchar(9), f3 char(5) ;  prepare s1 from stmt;  execute s1;  set stmt='insert into zhouhaiming values (1,'www','aaa')';  prepare s1 from stmt;  execute s1; 试试看，有错误再说

vlife 回复于：2002-12-22 22:27:49

在windows下的存储过程的编译在安装好vc++后，需要改变下面的批处理文件的相应路径： .....\SQLLIB\function\routine\sr_cpath.bat 其实不用安装vc++也是可以的，在安装好db2后有一个选项，注册"VC++附件"，执行就ok了

zhouhaiming 回复于：2002-12-23 13:03:47

作个总结，vlife网友说的对要用IBM的Stored Procedure Builder开发SQL存储过程不仅要设置DB2_SQLROUTINE_COMPILER_PATH和DB2_SQLROUTINE_KEEP_FILES，还要编辑DB2数据库服务器上的<db2_install_path>\function\routine\sr_cpath.bat文件，打开那些注释即可，sr_cpath.bat文件的内容如下： @echo off set VCV6_DRIVE=C:\Program Files\Microsoft Visual Studio set include=%include%;%VCV6_DRIVE%\VC98\atl\include;%VCV6_DRIVE%\VC98\mfc\include;%VCV6_DRIVE%\VC98\include set lib=%lib%;%VCV6_DRIVE%\VC98\mfc\lib;%VCV6_DRIVE%\VC98\lib set path=%path%;%VCV6_DRIVE%\Common\Tools\WinNT;%VCV6_DRIVE%\Common\MSDev98\Bin;%VCV6_DRIVE%\Common\Tools;%VCV6_DRIVE%\VC98\bin;%VCV6_DRIVE%\VC98\mfc\lib;%VCV6_DRIVE%\VC98\lib @echo off REM set VCV5_DRIVE=c:\Program Files\devstudio REM set include=%include%;%VCV5_DRIVE%\vc\include;%VCV5_DRIVE%\vc\atl\include;%VCV5_DRIVE%\vc\mfc\include REM set lib=%lib%;%VCV5_DRIVE%\vc\lib;%VCV5_DRIVE%\vc\mfc\lib REM set path=%path%;%VCV5_DRIVE%\sharedide\bin\ide;%VCV5_DRIVE%\sharedide\bin;%VCV5_DRIVE%\vc\bin